Esempio n. 1
0
function prep_DB_content()
{
    global $databaseConnection;
    $admin_role_id = 1;
    create_tables($databaseConnection);
    create_roles($databaseConnection, $admin_role_id);
    create_admin($databaseConnection, $admin_role_id);
}
function prep_DB_content()
{
    global $db;
    $admin_role_id = 1;
    create_tables($db);
    create_roles($db, $admin_role_id);
    create_admin($db, $admin_role_id);
}
Esempio n. 3
0
function setup_db()
{
    global $install;
    $install->title("Website Installation - Setup Database");
    echo '<div class="row">';
    echo '<div class="col-sm-12">';
    create_tables();
    create_roles();
    create_admin();
    install_finalise();
    echo '</div>';
    echo '</div>';
}
Esempio n. 4
0
function check_admin()
{
    $nickname = $_POST['nickname'];
    $email = $_POST['email'];
    $pass1 = $_POST['pass1'];
    $pass2 = $_POST['pass2'];
    if (!$nickname || !$email || !$pass1 || !$pass2) {
        return array('error' => true, 'message' => LANG_ADMIN_ERROR);
    }
    if (!preg_match("/^([a-zA-Z0-9\\._-]+)@([a-zA-Z0-9\\._-]+)\\.([a-zA-Z]{2,4})\$/i", $email)) {
        return array('error' => true, 'message' => LANG_ADMIN_EMAIL_ERROR);
    }
    if ($pass1 != $pass2) {
        return array('error' => true, 'message' => LANG_ADMIN_PASS_ERROR);
    }
    create_admin($nickname, $email, $pass1);
    return array('error' => false);
}
Esempio n. 5
0
<?php

include_once __DIR__ . '/../config.php';
include_once __DIR__ . '/../common/constants.php';
if (defined("ADMIN_USER") && ADMIN_USER != "" && (!isset($_SERVER['PHP_AUTH_USER']) || $_SERVER['PHP_AUTH_USER'] != ADMIN_USER)) {
    die("Go away, you evil hacker!");
}
include_once __DIR__ . '/query_manager.php';
include_once __DIR__ . '/../common/functions.php';
include_once __DIR__ . '/../common/upgrade.php';
include_once __DIR__ . '/../capture/common/functions.php';
create_admin();
create_error_logs();
$captureroles = unserialize(CAPTUREROLES);
$querybins = getBins();
$activePhrases = getNrOfActivePhrases();
$activeGeoboxes = getNrOfActiveGeoboxes();
$activeUsers = getNrOfActiveUsers();
$lastRateLimitHit = getLastRateLimitHit();
?>

<html>
    <head>
        <title>DMI-TCAT query manager</title>
        <meta charset='<?php 
echo mb_internal_encoding();
?>
'>
        <style type="text/css">

            body,html { font-family:Arial, Helvetica, sans-serif; font-size:12px; }
Esempio n. 6
0
<?php

session_start();
require '../../app/Autoloader.class.php';
App\Autoloader::register();
$config = App\Config::getInstance();
$bdd = App\database\Database::getInstance_bdd($config->get("db_name"), $config->get("db_user"), $config->get("db_pass"), $config->get("db_host"));
require '../../functions/functions.php';
if (isset($_POST['login']) && isset($_POST['email']) && isset($_POST['password']) && isset($_POST['rank'])) {
    if (create_admin($bdd, $_POST['login'], $_POST['email'], $_POST['password'], $_POST['rank'])) {
        $_SESSION['alert'] = "Success";
        header("Location: ../index.php");
        die;
    } else {
        $_SESSION['alert'] = "Error";
        header("Location: ../index.php?pg=create_admin");
        die;
    }
} else {
    $_SESSION['alert'] = "Error";
    header("Location: ../index.php?pg=create_admin");
    die;
}
Esempio n. 7
0
function querybinsPhpToDb()
{
    print "Migrating query bin definitions to new query manager." . PHP_EOL;
    create_admin();
    if (file_exists('../querybins.php')) {
        print "importing from querybins.php" . PHP_EOL;
        include '../querybins.php';
        if (isset($querybins)) {
            binsToDb($querybins, 'track');
        }
        $querybins = false;
    }
    if (file_exists('../followbins.php')) {
        print "importing from followbins.php" . PHP_EOL;
        include '../followbins.php';
        if (isset($querybins)) {
            binsToDb($querybins, 'follow');
        }
        $querybins = false;
    }
    if (file_exists('../querybins.php')) {
        print "importing query archives" . PHP_EOL;
        include '../querybins.php';
        if (isset($queryarchives)) {
            binsToDb($queryarchives, 'track');
        }
    }
    // retrieve other tables
    $dbh = pdo_connect();
    $rec = $dbh->prepare("SELECT querybin FROM tcat_query_bins");
    $existingTables = array();
    if ($rec->execute() && $rec->rowCount() > 0) {
        $existingTables = $rec->fetchAll(PDO::FETCH_COLUMN);
    }
    $rec = $dbh->prepare("SHOW TABLES LIKE '%_tweets'");
    if ($rec->execute() && $rec->rowCount() > 0) {
        print "Checking to see whether other querybins need to be imported" . PHP_EOL;
        $otherbins = $onepercentbins = $userbins = array();
        while ($res = $rec->fetch()) {
            $binname = str_replace("_tweets", "", $res[0]);
            if (array_search($binname, $existingTables) === false) {
                $phrases = "";
                if (strstr($binname, "user_") !== false) {
                    $sql = "SELECT DISTINCT(from_user_id) FROM " . $binname . "_tweets";
                    $rec2 = $dbh->prepare($sql);
                    if ($rec2->execute() && $rec2->rowCount() > 0) {
                        $phrases = implode(",", $rec2->fetchAll(PDO::FETCH_COLUMN));
                    }
                    $userbins[$binname] = $phrases;
                } elseif (strstr($binname, "sample_")) {
                    $onepercentbins[$binname] = $phrases;
                } else {
                    $otherbins[$binname] = $phrases;
                }
            }
        }
        if (!empty($userbins)) {
            binsToDb($userbins, "follow");
        }
        if (!empty($onepercentbins)) {
            binsToDb($onepercentbins, "onepercent");
        }
        if (!empty($otherbins)) {
            binsToDb($otherbins, "other");
        }
    }
    print "Moved querybins successfully" . PHP_EOL . PHP_EOL;
    print "Now verify whether all looks fine in the query manager (BASE_URL/capture/index.php) and in the analysis interface (BASE_URL/analysis/index.php). If it all checks out, you can remove dmi-tcat/querybins.php and dmi-tcat/followbins.php. Enter 'ok' when done." . PHP_EOL;
    if (trim(fgets(fopen("php://stdin", "r"))) != 'ok') {
        die('Abort' . PHP_EOL);
    }
    return true;
}
Esempio n. 8
0
    echo "Table play_in created successfully.<br />";
} else {
    echo "<span style=\"color: red;\">Error creating table play_in</span><br />";
}
if ($bdd->query("CREATE TABLE `webchat_users` (`id` int(10) unsigned NOT NULL auto_increment,`name` varchar(16) NOT NULL,`gravatar` varchar(32) NOT NULL,`last_activity` timestamp NOT NULL default CURRENT_TIMESTAMP,PRIMARY KEY (`id`),UNIQUE KEY `name` (`name`),KEY `last_activity` (`last_activity`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8")) {
    echo "Table play_in created successfully.<br />";
} else {
    echo "<span style=\"color: red;\">Error creating table play_in</span><br />";
}
if ($bdd->query("INSERT INTO `Administrators_ranks` VALUES ('', 'Dieu'), ('', 'Prophette'), ('', 'Ange')")) {
    echo "Insert into administrators_ranks is good.<br />";
} else {
    echo "<span style=\"color: red;\">Error when insert into administrators_ranks</span><br />";
}
if ($bdd->query("INSERT INTO `Players_grades` VALUES ('', 'Amiral', '4000', '2147483647'), ('', 'Capitaine', '3300', '4000'), ('', 'Commandeur', '2500', '3300'), ('', 'Lieutenant Commandeur', '1800', '2500'), ('', 'Lieutenant', '1200', '1800'), ('', 'Sous-Lieutenant', '700', '1200'), ('', 'Enseigne', '300', '700'), ('', 'Cadet', '0', '300')")) {
    echo "Insert into players_grades is good.<br />";
} else {
    echo "<span style=\"color: red;\">Error when insert into players_grades</span><br />";
}
if ($bdd->query("INSERT INTO `Type_of_games` VALUES ('', 'Simple', '2', '500')")) {
    echo "Insert into type_of_games is good.<br />";
} else {
    echo "<span style=\"color: red;\">Error when insert into type_of_games</span><br />";
}
create_admin($bdd, "root", "*****@*****.**", "root", '1');
// if ($bdd->query("INSERT INTO `Players` VALUES ('', 'root', '*****@*****.**', 'root', 0, 2), ('', 'tcarmet', '*****@*****.**', '123456', 0, 2)"))
//     echo "Insert into players is good.<br />";
// else
//     echo "<span style=\"color: red;\">Error when insert into players</span><br />";
// create_user($bdd, "tcoppin", "*****@*****.**", "qwerty");
// create_user($bdd, "tcarmet", "*****@*****.**", "123456");
Esempio n. 9
0
        # "create admin" form submitted
        list($pw_check_error, $pw_check_result) = check_setup_password(safepost('setup_password'));
        if ($pw_check_result != 'pass_OK') {
            $error += 1;
            $setupMessage = $pw_check_result;
        }
        if ($error == 0 && $pw_check_result == 'pass_OK') {
            // XXX need to ensure domains table includes an 'ALL' entry.
            $table_domain = table_by_key('domain');
            $r = db_query("SELECT * FROM {$table_domain} WHERE domain = 'ALL'");
            if ($r['rows'] == 0) {
                db_insert('domain', array('domain' => 'ALL', 'description' => '', 'transport' => ''));
                // all other fields should default through the schema.
            }
            $values = array('username' => safepost('username'), 'password' => safepost('password'), 'password2' => safepost('password2'), 'superadmin' => 1, 'domains' => array(), 'active' => 1);
            list($error, $setupMessage, $errormsg) = create_admin($values);
            if ($error != 0) {
                $tUsername = htmlentities($values['username']);
            } else {
                $setupMessage .= "<p>You are done with your basic setup. ";
                $setupMessage .= "<p><b>You can now <a href='login.php'>login to PostfixAdmin</a> using the account you just created.</b>";
            }
        }
    }
    if ($setuppw == "" || $setuppw == "changeme" || safeget("lostpw") == 1 || $lostpw_error != 0) {
        # show "create setup password" form
        ?>

<div class="standout"><?php 
        print $setupMessage;
        ?>
Esempio n. 10
0
function create_new_bin($params)
{
    global $captureroles, $now;
    $bin_name = trim($params["newbin_name"]);
    if (table_exists($bin_name) != 0) {
        echo '{"msg":"Query bin [' . $bin_name . '] already exists. Please change your bin name."}';
        return;
    }
    $type = $params['type'];
    if (array_search($type, $captureroles) === false && ($type !== 'geotrack' || array_search('track', $captureroles) === false)) {
        echo '{"msg":"This capturing type is not defined in the config file"}';
        return;
    }
    $comments = trim($params['newbin_comments']);
    // check whether the main query management tables are there, if not, create
    create_admin();
    $dbh = pdo_connect();
    // if one percent check whether there already is an active onepercent bin
    if ($type == "onepercent") {
        $sql = "SELECT querybin FROM tcat_query_bins WHERE type = 'onepercent' AND active = 1";
        $rec = $dbh->prepare($sql);
        if ($rec->execute() && $rec->rowCount() > 0) {
            echo '{"msg":"You can only have one active one percent stream at the same time"}';
            return;
        }
    }
    // populate tcat_query_bin table
    $sql = "INSERT INTO tcat_query_bins (querybin,type,active,comments) VALUES (:querybin, :type, '1', :comments);";
    $insert_querybin = $dbh->prepare($sql);
    $insert_querybin->bindParam(':querybin', $bin_name, PDO::PARAM_STR);
    $insert_querybin->bindParam(':type', $type, PDO::PARAM_STR);
    $insert_querybin->bindParam(':comments', $comments, PDO::PARAM_STR);
    $insert_querybin->execute();
    $lastbinid = $dbh->lastInsertId();
    // insert a period
    $sql = "INSERT INTO tcat_query_bins_periods (querybin_id,starttime,endtime) VALUES ('" . $lastbinid . "','{$now}','0000-00-00 00:00:00')";
    $insert_periods = $dbh->prepare($sql);
    $insert_periods->execute();
    $e = create_bin($bin_name);
    if ($e !== TRUE) {
        logit('controller.log', 'Failed to create database tables for bin ' . $bin_name . '. The error message was ' . $e);
        echo '{"msg":"Failed to create database tables. Please read the controller.log file for details"}';
        return;
    }
    if ($type == "track" || $type == "geotrack") {
        if ($type == "track") {
            $phrases = explode(",", $params["newbin_phrases"]);
            $phrases = array_trim_and_unique($phrases);
        } elseif ($type == "geotrack") {
            $phrases = get_phrases_from_geoquery($params["newbin_phrases"]);
        }
        // populate the phrases and connector tables
        foreach ($phrases as $phrase) {
            $phrase = str_replace("\"", "'", $phrase);
            $sql = "SELECT distinct(id) FROM tcat_query_phrases WHERE phrase = :phrase";
            $check_phrase = $dbh->prepare($sql);
            $check_phrase->bindParam(":phrase", $phrase, PDO::PARAM_STR);
            $check_phrase->execute();
            if ($check_phrase->rowCount() > 0) {
                $results = $check_phrase->fetch();
                $inid = $results['id'];
            } else {
                $sql = "INSERT INTO tcat_query_phrases (phrase) VALUES (:phrase)";
                $insert_phrase = $dbh->prepare($sql);
                $insert_phrase->bindParam(":phrase", $phrase, PDO::PARAM_STR);
                $insert_phrase->execute();
                $inid = $dbh->lastInsertId();
            }
            $sql = "INSERT INTO tcat_query_bins_phrases (phrase_id,querybin_id,starttime,endtime) VALUES ('" . $inid . "','" . $lastbinid . "','{$now}','0000-00-00 00:00:00')";
            $insert_connect = $dbh->prepare($sql);
            $insert_connect->execute();
        }
    } elseif ($type == "follow") {
        $users = explode(",", $params["newbin_users"]);
        $users = array_trim_and_unique($users);
        foreach ($users as $user) {
            // populate the users and connector tables
            $sql = "INSERT IGNORE INTO tcat_query_users (id) VALUES (:user_id)";
            $insert_phrase = $dbh->prepare($sql);
            $insert_phrase->bindParam(":user_id", $user, PDO::PARAM_INT);
            $insert_phrase->execute();
            // the user id can already exist here, but this 'error' will be ignored
            $sql = "INSERT INTO tcat_query_bins_users (user_id,querybin_id,starttime,endtime) VALUES ('" . $user . "','" . $lastbinid . "','{$now}','0000-00-00 00:00:00')";
            $insert_connect = $dbh->prepare($sql);
            $insert_connect->execute();
        }
    }
    if (web_reload_config_role($type)) {
        echo '{"msg":"The new query bin has been created"}';
    } else {
        echo '{"msg":"The new query bin has been created but the ' . $type . ' script could NOT be restarted"}';
    }
    $dbh = false;
}
Esempio n. 11
0
        if (isset($_POST['fUsername'])) {
            $fUsername = escape_string($_POST['fUsername']);
        }
        if (isset($_POST['fPassword'])) {
            $fPassword = escape_string($_POST['fPassword']);
        }
        if (isset($_POST['fPassword2'])) {
            $fPassword2 = escape_string($_POST['fPassword2']);
        }
        // XXX need to ensure domains table includes an 'ALL' entry.
        $r = db_query("SELECT * FROM domain WHERE domain = 'ALL'");
        if ($r['rows'] == 0) {
            db_insert('domain', array('domain' => 'ALL'));
            // all other fields should default through the schema.
        }
        list($error, $tMessage, $pAdminCreate_admin_username_text, $pAdminCreate_admin_password_text) = create_admin($fUsername, $fPassword, $fPassword2, array('ALL'), TRUE);
        if ($error != 0) {
            if (isset($_POST['fUsername'])) {
                $tUsername = escape_string($_POST['fUsername']);
            }
        } else {
            print "<p><b>{$tMessage}</b></p>";
            echo "<p><b>You can now log in to Postfix Admin.</b></p>";
        }
    }
    if ($_SERVER['REQUEST_METHOD'] == "GET" || $error != 0) {
        ?>

<div id="edit_form">
<form name="create_admin" method="post">
<table>
Esempio n. 12
0
    $tDomains = array();
}
if ($_SERVER['REQUEST_METHOD'] == "POST") {
    if (isset($_POST['fUsername'])) {
        $fUsername = escape_string($_POST['fUsername']);
    }
    if (isset($_POST['fPassword'])) {
        $fPassword = escape_string($_POST['fPassword']);
    }
    if (isset($_POST['fPassword2'])) {
        $fPassword2 = escape_string($_POST['fPassword2']);
    }
    $fDomains = array();
    if (!empty($_POST['fDomains'])) {
        $fDomains = $_POST['fDomains'];
    }
    list($error, $tMessage, $pAdminCreate_admin_username_text, $pAdminCreate_admin_password_text) = create_admin($fUsername, $fPassword, $fPassword2, $fDomains);
    if ($error != 0) {
        if (isset($_POST['fUsername'])) {
            $tUsername = escape_string($_POST['fUsername']);
        }
        if (isset($_POST['fDomains'])) {
            $tDomains = $_POST['fDomains'];
        }
    }
}
include "templates/header.php";
include "templates/menu.php";
include "templates/admin_create-admin.php";
include "templates/footer.php";
/* vim: set expandtab softtabstop=3 tabstop=3 shiftwidth=3: */
Esempio n. 13
0
function tracker_run()
{
    global $dbuser, $dbpass, $database, $hostname, $tweetQueue;
    // We need the tcat_status table
    create_error_logs();
    // We need the tcat_captured_phrases table
    create_admin();
    $tweetQueue = new TweetQueue();
    $tweetQueue->setoption('replace', false);
    if (defined('USE_INSERT_DELAYED') && USE_INSERT_DELAYED) {
        $tweetQueue->setoption('delayed', true);
    }
    if (defined('DISABLE_INSERT_IGNORE') && DISABLE_INSERT_IGNORE) {
        $tweetQueue->setoption('ignore', false);
    } else {
        $tweetQueue->setoption('ignore', true);
    }
    if (!defined("CAPTURE")) {
        /* logged to no file in particular, because we don't know which one. this should not happen. */
        error_log("tracker_run() called without defining CAPTURE. have you set up config.php ?");
        die;
    }
    $roles = unserialize(CAPTUREROLES);
    if (!in_array(CAPTURE, $roles)) {
        /* incorrect script execution, report back error to user */
        error_log("tracker_run() role " . CAPTURE . " is not configured to run");
        die;
    }
    // log execution environment
    $phpstring = phpversion() . " in mode " . php_sapi_name() . " with extensions ";
    $extensions = get_loaded_extensions();
    $first = true;
    foreach ($extensions as $ext) {
        if ($first) {
            $first = false;
        } else {
            $phpstring .= ',';
        }
        $phpstring .= "{$ext}";
    }
    $phpstring .= " (ini file: " . php_ini_loaded_file() . ")";
    logit(CAPTURE . ".error.log", "running php version {$phpstring}");
    // install the signal handler
    if (function_exists('pcntl_signal')) {
        // tick use required as of PHP 4.3.0
        declare (ticks=1);
        // See signal method discussion:
        // http://darrendev.blogspot.nl/2010/11/php-53-ticks-pcntlsignal.html
        logit(CAPTURE . ".error.log", "installing term signal handler for this script");
        // setup signal handlers
        pcntl_signal(SIGTERM, "capture_signal_handler_term");
    } else {
        logit(CAPTURE . ".error.log", "your php installation does not support signal handlers. graceful reload will not work");
    }
    // sanity check for geo bins functions
    if (geophp_sane()) {
        logit(CAPTURE . ".error.log", "geoPHP library is fully functional");
    } elseif (geobinsActive()) {
        logit(CAPTURE . ".error.log", "refusing to track until geobins are stopped or geo is functional");
        exit(1);
    } else {
        logit(CAPTURE . ".error.log", "geoPHP functions are not yet available, see documentation for instructions");
    }
    global $rl_current_record, $rl_registering_minute;
    global $last_insert_id;
    global $tracker_started_at;
    $rl_current_record = 0;
    // how many tweets have been ratelimited this MINUTE?
    $rl_registering_minute = get_current_minute();
    // what is the minute we are registering (as soon as the current minute differs from this, we insert our record in the database)
    $last_insert_id = -1;
    // needed to make INSERT DELAYED work, see the function database_activity()
    $tracker_started_at = time();
    // the walltime when this script was started
    global $twitter_consumer_key, $twitter_consumer_secret, $twitter_user_token, $twitter_user_secret, $lastinsert;
    $pid = getmypid();
    logit(CAPTURE . ".error.log", "started script " . CAPTURE . " with pid {$pid}");
    $lastinsert = time();
    $procfilename = __DIR__ . "/../../proc/" . CAPTURE . ".procinfo";
    if (file_put_contents($procfilename, $pid . "|" . time()) === FALSE) {
        logit(CAPTURE . ".error.log", "cannot register capture script start time (file \"{$procfilename}\" is not WRITABLE. make sure the proc/ directory exists in your webroot and is writable by the cron user)");
        die;
    }
    $networkpath = isset($GLOBALS["HOSTROLE"][CAPTURE]) ? $GLOBALS["HOSTROLE"][CAPTURE] : 'https://stream.twitter.com/';
    // prepare queries
    if (CAPTURE == "track") {
        // check for geolocation bins
        $locations = geobinsActive() ? getActiveLocationsImploded() : false;
        // assemble query
        $querylist = getActivePhrases();
        if (empty($querylist) && !geobinsActive()) {
            logit(CAPTURE . ".error.log", "empty query list, aborting!");
            return;
        }
        $method = $networkpath . '1.1/statuses/filter.json';
        $track = implode(",", $querylist);
        $params = array();
        if (geobinsActive()) {
            $params['locations'] = $locations;
        }
        if (!empty($querylist)) {
            $params['track'] = $track;
        }
    } elseif (CAPTURE == "follow") {
        $querylist = getActiveUsers();
        if (empty($querylist)) {
            logit(CAPTURE . ".error.log", "empty query list, aborting!");
            return;
        }
        $method = $networkpath . '1.1/statuses/filter.json';
        $params = array("follow" => implode(",", $querylist));
    } elseif (CAPTURE == "onepercent") {
        $method = $networkpath . '1.1/statuses/sample.json';
        $params = array('stall_warnings' => 'true');
    }
    logit(CAPTURE . ".error.log", "connecting to API socket");
    $tmhOAuth = new tmhOAuth(array('consumer_key' => $twitter_consumer_key, 'consumer_secret' => $twitter_consumer_secret, 'token' => $twitter_user_token, 'secret' => $twitter_user_secret, 'host' => 'stream.twitter.com'));
    $tmhOAuth->request_settings['headers']['Host'] = 'stream.twitter.com';
    if (CAPTURE == "track" || CAPTURE == "follow") {
        logit(CAPTURE . ".error.log", "connecting - query " . var_export($params, 1));
    } elseif (CAPTURE == "onepercent") {
        logit(CAPTURE . ".error.log", "connecting to sample stream");
    }
    $capturebucket = array();
    $tmhOAuth->streaming_request('POST', $method, $params, 'tracker_streamCallback', array('Host' => 'stream.twitter.com'));
    // output any response we get back AFTER the Stream has stopped -- or it errors
    logit(CAPTURE . ".error.log", "stream stopped - error " . var_export($tmhOAuth, 1));
    logit(CAPTURE . ".error.log", "processing buffer before exit");
    processtweets($capturebucket);
}
Esempio n. 14
0
     SmartyValidate::register_form('install', true);
     SmartyValidate::register_validator('v_admin_user', 'admin_user:!^\\w{4,25}$!', 'isRegExp', false, false, 'trim', 'install');
     SmartyValidate::register_validator('v_admin_name', 'admin_name', 'notEmpty', false, false, 'trim', 'install');
     SmartyValidate::register_validator('v_admin_password', 'admin_password:6:25', 'isLength', false, false, 'trim', 'install');
     SmartyValidate::register_validator('v_admin_passwordc', 'admin_password:admin_passwordc', 'isEqual', true, false, 'trim', 'install');
     SmartyValidate::register_validator('v_admin_email', 'admin_email', 'isEmail', false, false, 'trim', 'install');
 } else {
     if ($_POST['submit'] == 'next') {
         SmartyValidate::connect($tpl);
         if (SmartyValidate::is_valid($_POST, 'install')) {
             $admin_details = array();
             $admin_details['admin_user'] = $_POST['admin_user'];
             $admin_details['admin_name'] = $_POST['admin_name'];
             $admin_details['admin_password'] = $_POST['admin_password'];
             $admin_details['admin_email'] = $_POST['admin_email'];
             if (create_admin($admin_details)) {
                 SmartyValidate::disconnect();
                 $step++;
                 @header('Location: index.php?step=' . $step);
                 @exit;
             }
         } elseif ($_POST['submit'] == 'back') {
             SmartyValidate::disconnect();
             $step--;
             @header('Location: index.php?step=' . $step);
             @exit;
         }
     }
 }
 $tpl->assign($_SESSION['values']);
 break;
Esempio n. 15
0
/**
 * Check for possible upgrades to the TCAT database.
 *
 * This function has two modes. In dry run mode, it tests whether the TCAT (mysql) database
 * is out-of-date. 
 * In normal mode, it will execute upgrades to the TCAT database. The upgrade script is intended to
 * be run from the command-line and allows for user-interaction. A special 'non-interactive'
 * option allows upgrades to be performed automatically (by cron). Even more refined behaviour
 * can be performed by setting the aulevel parameter.
 *
 * @param boolean $dry_run       Enable dry run mode.
 * @param boolean $interactive   Enable interactive mode.
 * @param integer $aulevel       Auto-upgrade level (0, 1 or 2)
 * @param string  $single        Restrict upgrades to a single bin
 *
 * @return array in dry run mode, ie. an associational array with two boolean keys for 'suggested' and 'required'; otherwise void
 */
function upgrades($dry_run = false, $interactive = true, $aulevel = 2, $single = null)
{
    global $database;
    global $all_bins;
    $all_bins = get_all_bins();
    $dbh = pdo_connect();
    $logtarget = $interactive ? "cli" : "controller.log";
    // Tracker whether an update is suggested, or even required during a dry run.
    // These values are ONLY tracked when doing a dry run; do not use them for CLI feedback.
    $suggested = false;
    $required = false;
    // Check if we have the tcat_status table.
    // Do not create it on-the-fly here. We want this done on the capture side.
    $query = "SHOW TABLES LIKE 'tcat_status'";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $results = $rec->fetchAll(PDO::FETCH_COLUMN);
    if (count($results)) {
        $have_tcat_status = true;
    } else {
        $have_tcat_status = false;
    }
    // 29/08/2014 Alter tweets tables to add new fields, ex. 'possibly_sensitive'
    $query = "SHOW TABLES";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $results = $rec->fetchAll(PDO::FETCH_COLUMN);
    $ans = '';
    if ($interactive == false) {
        // require auto-upgrade level 1 or higher
        if ($aulevel > 0) {
            $ans = 'a';
        } else {
            $ans = 'SKIP';
        }
    }
    if ($ans !== 'SKIP') {
        foreach ($results as $k => $v) {
            if (!preg_match("/_tweets\$/", $v)) {
                continue;
            }
            if ($single && $v !== $single . '_tweets') {
                continue;
            }
            $query = "SHOW COLUMNS FROM {$v}";
            $rec = $dbh->prepare($query);
            $rec->execute();
            $columns = $rec->fetchAll(PDO::FETCH_COLUMN);
            $update = TRUE;
            foreach ($columns as $i => $c) {
                if ($c == 'from_user_withheld_scope') {
                    $update = FALSE;
                    break;
                }
            }
            if ($update && $dry_run) {
                $suggested = true;
                $update = false;
            }
            if ($update) {
                if ($ans !== 'a') {
                    $ans = cli_yesnoall("Add new columns and indexes (ex. possibly_sensitive) to table {$v}", 1, '639a0b93271eafca98c02e5a01968572d4435191');
                }
                if ($ans == 'a' || $ans == 'y') {
                    logit($logtarget, "Adding new columns (ex. possibly_sensitive) to table {$v}");
                    $definitions = array("`from_user_withheld_scope` varchar(32)", "`from_user_favourites_count` int(11)", "`from_user_created_at` datetime", "`possibly_sensitive` tinyint(1)", "`truncated` tinyint(1)", "`withheld_copyright` tinyint(1)", "`withheld_scope` varchar(32)");
                    $query = "ALTER TABLE " . quoteIdent($v);
                    $first = TRUE;
                    foreach ($definitions as $subpart) {
                        if (!$first) {
                            $query .= ", ";
                        } else {
                            $first = FALSE;
                        }
                        $query .= " ADD COLUMN {$subpart}";
                    }
                    // and add indexes
                    $query .= ", ADD KEY `from_user_created_at` (`from_user_created_at`)" . ", ADD KEY `from_user_withheld_scope` (`from_user_withheld_scope`)" . ", ADD KEY `possibly_sensitive` (`possibly_sensitive`)" . ", ADD KEY `withheld_copyright` (`withheld_copyright`)" . ", ADD KEY `withheld_scope` (`withheld_scope`)";
                    $rec = $dbh->prepare($query);
                    $rec->execute();
                }
            }
        }
    }
    // 16/09/2014 Create a new withheld table for every bin
    foreach ($all_bins as $bin) {
        if ($single && $bin !== $single) {
            continue;
        }
        $exists = false;
        foreach ($results as $k => $v) {
            if ($v == $bin . '_places') {
                $exists = true;
            }
        }
        if (!$exists && $dry_run) {
            $suggested = true;
            $exists = true;
        }
        if (!$exists) {
            $create = $bin . '_withheld';
            logit($logtarget, "Creating new table {$create}");
            $sql = "CREATE TABLE IF NOT EXISTS " . quoteIdent($create) . " (\n                    `id` int(11) NOT NULL AUTO_INCREMENT,\n                    `tweet_id` bigint(20) NOT NULL,\n                    `user_id` bigint(20),\n                    `country` char(5),\n                        PRIMARY KEY (`id`),\n                                KEY `user_id` (`user_id`),\n                                KEY `tweet_id` (`user_id`),\n                                KEY `country` (`country`)\n                    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4";
            $create_withheld = $dbh->prepare($sql);
            $create_withheld->execute();
        }
    }
    // 16/09/2014 Create a new places table for every bin
    foreach ($all_bins as $bin) {
        if ($single && $bin !== $single) {
            continue;
        }
        $exists = false;
        foreach ($results as $k => $v) {
            if ($v == $bin . '_places') {
                $exists = true;
            }
        }
        if (!$exists && $dry_run) {
            $suggested = true;
            $exists = true;
        }
        if (!$exists) {
            $create = $bin . '_places';
            logit($logtarget, "Creating new table {$create}");
            $sql = "CREATE TABLE IF NOT EXISTS " . quoteIdent($create) . " (\n                    `id` varchar(32) NOT NULL,\n                    `tweet_id` bigint(20) NOT NULL,\n                        PRIMARY KEY (`id`, `tweet_id`)\n                    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4";
            $create_places = $dbh->prepare($sql);
            $create_places->execute();
        }
    }
    // 23/09/2014 Set global database collation to utf8mb4
    $query = "show variables like \"character_set_database\"";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $results = $rec->fetch(PDO::FETCH_ASSOC);
    $character_set_database = isset($results['Value']) ? $results['Value'] : 'unknown';
    $query = "show variables like \"collation_database\"";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $results = $rec->fetch(PDO::FETCH_ASSOC);
    $collation_database = isset($results['Value']) ? $results['Value'] : 'unknown';
    if ($character_set_database == 'utf8' && ($collation_database == 'utf8_general_ci' || $collation_database == 'utf8_unicode_ci')) {
        if ($dry_run) {
            $suggested = true;
        } else {
            $skipping = false;
            if (!$single) {
                $ans = '';
                if ($interactive == false) {
                    // require auto-upgrade level 1 or higher
                    if ($aulevel > 0) {
                        $ans = 'a';
                    } else {
                        $skipping = true;
                    }
                } else {
                    $ans = cli_yesnoall("Change default database character to utf8mb4", 1, '639a0b93271eafca98c02e5a01968572d4435191');
                }
                if ($ans == 'y' || $ans == 'a') {
                    logit($logtarget, "Converting database character set from utf8 to utf8mb4");
                    $query = "ALTER DATABASE {$database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
                    $rec = $dbh->prepare($query);
                    $rec->execute();
                } else {
                    $skipping = true;
                }
            }
            if ($interactive == false) {
                // conversion per bin requires auto-upgrade level 2
                if ($aulevel > 1) {
                    $skipping = false;
                } else {
                    $skipping = true;
                }
            }
            if (!$skipping) {
                $query = "SHOW TABLES";
                $rec = $dbh->prepare($query);
                $rec->execute();
                $results = $rec->fetchAll(PDO::FETCH_COLUMN);
                $ans = '';
                if ($interactive == false) {
                    $ans = 'a';
                }
                foreach ($results as $k => $v) {
                    if (preg_match("/_places\$/", $v) || preg_match("/_withheld\$/", $v)) {
                        continue;
                    }
                    if ($single && $v !== $single . '_tweets' && $v !== $single . '_hashtags' && $v !== $single . '_mentions' && $v !== $single . '_urls') {
                        continue;
                    }
                    if ($interactive && $ans !== 'a') {
                        $ans = cli_yesnoall("Convert table {$v} character set utf8 to utf8mb4", 2, '639a0b93271eafca98c02e5a01968572d4435191');
                    }
                    if ($ans == 'y' || $ans == 'a') {
                        logit($logtarget, "Converting table {$v} character set utf8 to utf8mb4");
                        $query = "ALTER TABLE {$v} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
                        $rec = $dbh->prepare($query);
                        $rec->execute();
                        $query = "ALTER TABLE {$v} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
                        $rec = $dbh->prepare($query);
                        $rec->execute();
                        logit($logtarget, "Repairing and optimizing table {$v}");
                        $query = "REPAIR TABLE {$v}";
                        $rec = $dbh->prepare($query);
                        $rec->execute();
                        $query = "OPTIMIZE TABLE {$v}";
                        $rec = $dbh->prepare($query);
                        $rec->execute();
                    }
                }
            }
        }
    }
    // 24/02/2015 remove media_type, photo_size_width and photo_size_height fields from _urls table
    //            create media table
    $query = "SHOW TABLES";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $results = $rec->fetchAll(PDO::FETCH_COLUMN);
    foreach ($results as $k => $v) {
        if (!preg_match("/_urls\$/", $v)) {
            continue;
        }
        if ($single && $v !== $single . '_urls') {
            continue;
        }
        $query = "SHOW COLUMNS FROM {$v}";
        $rec = $dbh->prepare($query);
        $rec->execute();
        $columns = $rec->fetchAll(PDO::FETCH_COLUMN);
        $update_remove = FALSE;
        foreach ($columns as $i => $c) {
            if ($c == 'photo_size_width') {
                $update_remove = TRUE;
                break;
            }
        }
        if ($update_remove) {
            $suggested = true;
            $update_remove = false;
        }
        if ($update_remove) {
            logit($logtarget, "Removing columns media_type, photo_size_width and photo_size_height from table {$v}");
            $query = "ALTER TABLE " . quoteIdent($v) . " DROP COLUMN `media_type`," . " DROP COLUMN `photo_size_width`," . " DROP COLUMN `photo_size_height`";
            $rec = $dbh->prepare($query);
            $rec->execute();
            // NOTE: column url_is_media_upload has been deprecated, but will not be removed because it signifies an older structure
        }
        $mediatable = preg_replace("/_urls\$/", "_media", $v);
        if (!in_array($mediatable, array_values($results))) {
            if ($dry_run) {
                $suggested = true;
            } else {
                logit($logtarget, "Creating table {$mediatable}");
                $query = "CREATE TABLE IF NOT EXISTS " . quoteIdent($mediatable) . " (\n                    `id` bigint(20) NOT NULL,\n                    `tweet_id` bigint(20) NOT NULL,\n                    `url` varchar(2048),\n                    `url_expanded` varchar(2048),\n                    `media_url_https` varchar(2048),\n                    `media_type` varchar(32),\n                    `photo_size_width` int(11),\n                    `photo_size_height` int(11),\n                    `photo_resize` varchar(32),\n                    `indice_start` int(11),\n                    `indice_end` int(11),\n                    PRIMARY KEY (`id`, `tweet_id`),\n                            KEY `media_url_https` (`media_url_https`),\n                            KEY `media_type` (`media_type`),\n                            KEY `photo_size_width` (`photo_size_width`),\n                            KEY `photo_size_height` (`photo_size_height`),\n                            KEY `photo_resize` (`photo_resize`)\n                    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4";
                $rec = $dbh->prepare($query);
                $rec->execute();
            }
        }
        if ($update_remove && $dry_run == false) {
            logit($logtarget, "Please run the upgrade-media.php script to lookup media data for Tweets in your bins.");
        }
    }
    // 03/03/2015 Add comments column
    $query = "SHOW COLUMNS FROM tcat_query_bins";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $columns = $rec->fetchAll(PDO::FETCH_COLUMN);
    $update = TRUE;
    foreach ($columns as $i => $c) {
        if ($c == 'comments') {
            $update = FALSE;
            break;
        }
    }
    if ($update && $dry_run) {
        $suggested = true;
        $update = false;
    }
    if ($update) {
        logit($logtarget, "Adding new comments column to table tcat_query_bins");
        $query = "ALTER TABLE tcat_query_bins ADD COLUMN `comments` varchar(2048) DEFAULT NULL";
        $rec = $dbh->prepare($query);
        $rec->execute();
    }
    // 17/04/2015 Change column to user_id to BIGINT in tcat_query_bins_users
    $query = "SHOW FULL COLUMNS FROM tcat_query_bins_users";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $results = $rec->fetchAll();
    $update = FALSE;
    foreach ($results as $result) {
        if ($result['Field'] == 'user_id' && !preg_match("/bigint/", $result['Type'])) {
            $update = TRUE;
            break;
        }
    }
    if ($update) {
        $suggested = true;
        $required = true;
        // this is a bugfix, therefore required
        if ($dry_run == false) {
            // in non-interactive mode we always execute, because the complexity level is: trivial
            if ($interactive) {
                $ans = cli_yesnoall("Change column type for user_id in table tcat_query_bins_users to BIGINT", 0, 'n/a');
                if ($ans != 'a' && $ans != 'y') {
                    $update = false;
                }
            }
            if ($update) {
                logit($logtarget, "Changing column type for column user_id in table tcat_query_bins_users");
                $query = "ALTER TABLE tcat_query_bins_users MODIFY `user_id` BIGINT NULL";
                $rec = $dbh->prepare($query);
                $rec->execute();
            }
        }
    }
    // 13/08/2015 Use original retweet text for all truncated tweets & original/cached user for all retweeted tweets
    $ans = '';
    if ($interactive == false) {
        // require auto-upgrade level 2
        if ($aulevel > 1) {
            $ans = 'a';
        } else {
            $ans = 'SKIP';
        }
    }
    /* Skip the test during a dry-run if an upgrade has already been suggested, or when the auto-upgrade level is not high enough. */
    if ($ans != 'SKIP' && ($suggested == false && $required == false || $dry_run == false)) {
        /*
         * After n seconds of testing and no positive results, we assume the bins do not require updating.
         * Unfortunately MySQL versions below 5.7.4 do not allow us to specify a timeout per query.
         */
        $total_test_time = 5;
        $t1 = time();
        foreach ($all_bins as $bin) {
            if ($single && $bin !== $single) {
                continue;
            }
            /*
             * Look for any tweets that have different length than pseudocode: length("RT @originaluser: "******"RT @retweetsuser: "******"select exists ( select 1 from " . $bin . "_tweets A inner join " . $bin . "_tweets B on A.retweet_id = B.id where LENGTH(A.text) != LENGTH(B.text) + LENGTH(B.from_user_name) + LENGTH('RT @: ') or substr(A.text, position('@' in A.text) + 1, position(': ' in A.text) - 5) != B.from_user_name limit 1 ) as `exists`";
            $rec = $dbh->prepare($tester);
            $rec->execute();
            $res = $rec->fetch(PDO::FETCH_ASSOC);
            if ($res['exists'] === 1) {
                if ($dry_run) {
                    $suggested = true;
                    break;
                } else {
                    if ($interactive && $ans !== 'a') {
                        $ans = cli_yesnoall("Use the original retweet text and username for truncated tweets in bin {$bin} - this will ALTER tweet contents", 2, 'n/a');
                    }
                    if ($ans == 'y' || $ans == 'a') {
                        logit($logtarget, "Using original retweet text and username for tweets in bin {$bin}");
                        /* Note: original tweet may have been length 140 and truncated retweet may have length 140,
                         * therefore we need to check for more than just length. Here we update everything with length >= 140 and ending with '...' */
                        $fixer = "update {$bin}" . "_tweets A inner join " . $bin . "_tweets B on A.retweet_id = B.id set A.text = CONCAT('RT @', B.from_user_name, ': ', B.text) where (length(A.text) >= 140 and A.text like '%…') or substr(A.text, position('@' in A.text) + 1, position(': ' in A.text) - 5) != B.from_user_name";
                        $rec = $dbh->prepare($fixer);
                        $rec->execute();
                    }
                }
            }
            $t2 = time();
            if ($t2 - $t1 > $total_test_time) {
                break;
            }
        }
    }
    // 22/01/2016 Remove AUTO_INCREMENT from primary key in tcat_query_users
    $query = "SHOW FULL COLUMNS FROM tcat_query_users";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $results = $rec->fetchAll();
    $update = FALSE;
    foreach ($results as $result) {
        if ($result['Field'] == 'id' && preg_match("/auto_increment/", $result['Extra'])) {
            $update = TRUE;
            break;
        }
    }
    if ($update) {
        $suggested = true;
        $required = false;
        if ($dry_run == false) {
            // in non-interactive mode we always execute, because the complexity level is: trivial
            if ($interactive) {
                $ans = cli_yesnoall("Remove AUTO_INCREMENT from primary key in tcat_query_users", 0, 'b11f11cbfb302e32f8db5dd1e883a16e7b2b0c67');
                if ($ans != 'a' && $ans != 'y') {
                    $update = false;
                }
            }
            if ($update) {
                logit($logtarget, "Removing AUTO_INCREMENT from primary key in tcat_query_users");
                $query = "ALTER TABLE tcat_query_users MODIFY `id` BIGINT NOT NULL";
                $rec = $dbh->prepare($query);
                $rec->execute();
            }
        }
    }
    // 01/02/2016 Alter tweets tables to add new fields, ex. 'quoted_status_id'
    $query = "SHOW TABLES";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $results = $rec->fetchAll(PDO::FETCH_COLUMN);
    $ans = '';
    if ($interactive == false) {
        // require auto-upgrade level 2
        if ($aulevel > 1) {
            $ans = 'a';
        } else {
            $ans = 'SKIP';
        }
    }
    if ($ans !== 'SKIP') {
        foreach ($results as $k => $v) {
            if (!preg_match("/_tweets\$/", $v)) {
                continue;
            }
            if ($single && $v !== $single . '_tweets') {
                continue;
            }
            $query = "SHOW COLUMNS FROM {$v}";
            $rec = $dbh->prepare($query);
            $rec->execute();
            $columns = $rec->fetchAll(PDO::FETCH_COLUMN);
            $update = TRUE;
            foreach ($columns as $i => $c) {
                if ($c == 'quoted_status_id') {
                    $update = FALSE;
                    break;
                }
            }
            if ($update && $dry_run) {
                $suggested = true;
                $update = false;
            }
            if ($update) {
                if ($ans !== 'a') {
                    $ans = cli_yesnoall("Add new columns and indexes (ex. quoted_status_id) to table {$v}", 2, '6b6c7ac716a9e179a2ea3e528c9374b94abdada6');
                }
                if ($ans == 'a' || $ans == 'y') {
                    logit($logtarget, "Adding new columns (ex. quoted_status_id) to table {$v}");
                    $definitions = array("`quoted_status_id` bigint");
                    $query = "ALTER TABLE " . quoteIdent($v);
                    $first = TRUE;
                    foreach ($definitions as $subpart) {
                        if (!$first) {
                            $query .= ", ";
                        } else {
                            $first = FALSE;
                        }
                        $query .= " ADD COLUMN {$subpart}";
                    }
                    // and add indexes
                    $query .= ", ADD KEY `quoted_status_id` (`quoted_status_id`)";
                    $rec = $dbh->prepare($query);
                    $rec->execute();
                }
            }
        }
    }
    // 05/04/2016 Re-assemble historical TCAT ratelimit information to keep appropriate interval records (see the discussion on Github: https://github.com/digitalmethodsinitiative/dmi-tcat/issues/168)
    // First test if a reconstruction is neccessary
    $already_updated = true;
    $now = null;
    // this variable will store the moment the new gauge behaviour became effective
    if ($have_tcat_status) {
        $sql = "select value, unix_timestamp(value) as value_unix from tcat_status where variable = 'ratelimit_format_modified_at'";
        $rec = $dbh->prepare($sql);
        if ($rec->execute() && $rec->rowCount() > 0) {
            while ($res = $rec->fetch()) {
                $now = $res['value'];
                $now_unix = $res['value_unix'];
            }
        }
        $sql = "select value from tcat_status where variable = 'ratelimit_database_rebuild' and value > 0";
        $rec = $dbh->prepare($sql);
        if (!$rec->execute() || $rec->rowCount() == 0) {
            $already_updated = false;
        }
        $bin_mysqldump = $bin_gzip = null;
        if ($already_updated == false) {
            $bin_mysqldump = get_executable("mysqldump");
            if ($bin_mysqldump === null) {
                logit($logtarget, "The mysqldump binary appears to be missing. Did you install the MySQL client utilities? Some upgrades will not work without this utility.");
                $already_updated = true;
            }
            $bin_gzip = get_executable("gzip");
            if ($bin_gzip === null) {
                logit($logtarget, "The gzip binary appears to be missing. Please lookup this utility in your software repository. Some upgrades will not work without this utility.");
                $already_updated = true;
            }
        }
    } else {
        // The upgrade script will cause active tracking roles to restart; which may take up to a minute. Afterwards, we can expect the tcat_status table to exist and
        // to have started recording timezone, gap and ratelimit information in the new gauge style. Because it really neccessary to wait for the tracking roles to behave correctly,
        // we decide to skip this upgrade step and inform the user.
        logit($logtarget, "Your tracking roles are being restarted now (in the background) to record timezone, ratelimit and gap information in a newer style.");
        logit($logtarget, "Afterwards will we be able to re-assemble historical ratelimit and gap information, and new export modules can become available.");
        logit($logtarget, "Please wait at least one minute and then run this script again.");
    }
    if (!$already_updated && $now != null) {
        $suggested = true;
        $required = true;
        // this is a bugfix, therefore required
        if ($dry_run == false) {
            $ans = '';
            if ($interactive == false) {
                // require auto-upgrade level 2
                if ($aulevel > 1) {
                    $ans = 'a';
                } else {
                    $ans = 'SKIP';
                }
            } else {
                $ans = cli_yesnoall("Re-assemble historical TCAT tweet time zone, ratelimit and gap information to keep appropriate records. It will take quite a while on long-running servers, though the majority of operations are non-blocking. If you have some very big bins (with 70+ million tweets inside them), you may wish to explore the USE_INSERT_DELAYED option in config.php and restart your trackers before running this upgrade. The upgrade procedure will temporarily render bins being upgraded invisible in the front-end.", 2);
            }
            if ($ans == 'y' || $ans == 'a') {
                global $dbuser, $dbpass, $database, $hostname;
                putenv('MYSQL_PWD=' . $dbpass);
                /* this avoids having to put the password on the command-line */
                // We need functioning timezone tables for this upgrade step
                if (import_mysql_timezone_data() == FALSE) {
                    logit($logtarget, "ERROR -----");
                    logit($logtarget, "ERROR - Your MySQL server is unfortunately missing timezone data which is needed to perform this upgrade step.");
                    logit($logtarget, "ERROR - This is usually caused by having a non-root user connecting to the database server.");
                    logit($logtarget, "ERROR - Your current configuration is secure and actually the recommended one (as it is also set-up this way by the TCAT auto-installer).");
                    logit($logtarget, "ERROR - But you will now have to perform a single superuser (sudo) command manually.");
                    logit($logtarget, "ERROR -");
                    logit($logtarget, "ERROR - For Debian or Ubuntu systems, become root (using sudo su) and execute the following command:");
                    logit($logtarget, "ERROR -");
                    logit($logtarget, "ERROR - /usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql --defaults-file=/etc/mysql/debian.cnf --force -u debian-sys-maint mysql");
                    logit($logtarget, "ERROR -");
                    logit($logtarget, "ERROR - (you can safely ignore the line: Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.')");
                    logit($logtarget, "ERROR -");
                    logit($logtarget, "ERROR - For all other operating systems, please read the MySQL instructions here: http://dev.mysql.com/doc/refman/5.7/en/mysql-tzinfo-to-sql.html");
                    logit($logtarget, "ERROR -----");
                    logit($logtarget, "Discontinuing this upgrade step until the issue has been resolved.");
                } else {
                    // First make sure the historical tweet data is correct
                    $query = "SHOW TABLES";
                    $rec = $dbh->prepare($query);
                    $rec->execute();
                    $results = $rec->fetchAll(PDO::FETCH_COLUMN);
                    foreach ($results as $k => $tweets_table) {
                        if (!preg_match("/_tweets\$/", $tweets_table)) {
                            continue;
                        }
                        $badzone = 'Europe/London';
                        logit($logtarget, "Fixing timezone for created_at field in table '{$tweets_table}' ..");
                        if (TCAT_CONFIG_DEPRECATED_TIMEZONE && TCAT_CONFIG_DEPRECATED_TIMEZONE_CONFIGURED) {
                            $badzone = TCAT_CONFIG_DEPRECATED_TIMEZONE_CONFIGURED;
                        }
                        /*
                         * NOTE: The MySQL native function CONVERT_TZ(datetimestring, 'badtimezone', 'UTC') helps to undo the bug described
                         *       Here:  https://github.com/digitalmethodsinitiative/dmi-tcat/issues/197
                         *       And here: https://github.com/digitalmethodsinitiative/dmi-tcat/pull/194
                         */
                        $sql = "SELECT id FROM `{$tweets_table}` WHERE CONVERT_TZ(created_at, '{$badzone}', 'UTC') <= '{$now}' ORDER BY CONVERT_TZ(created_at, '{$badzone}', 'UTC') DESC LIMIT 1";
                        logit($logtarget, "{$sql}");
                        $rec2 = $dbh->prepare($sql);
                        $rec2->execute();
                        $results2 = $rec2->fetch(PDO::FETCH_ASSOC);
                        $max_id = $results2['id'];
                        if (is_null($max_id)) {
                            logit($logtarget, "Table is either empty or does not need to be fixed. Skipping.");
                            continue;
                        }
                        $dbh->beginTransaction();
                        $binname = preg_replace("/_tweets\$/", "", $tweets_table);
                        $orig_access = TCAT_QUERYBIN_ACCESS_OK;
                        if (in_array($binname, $all_bins)) {
                            $sql = "SELECT `access` FROM tcat_query_bins WHERE querybin = :querybin";
                            logit($logtarget, "{$sql}");
                            $rec2 = $dbh->prepare($sql);
                            $rec2->bindParam(":querybin", $binname, PDO::PARAM_STR);
                            $rec2->execute();
                            $results2 = $rec2->fetch(PDO::FETCH_ASSOC);
                            $orig_access = $results2['access'];
                            $sql = "UPDATE tcat_query_bins SET access = " . TCAT_QUERYBIN_ACCESS_WRITEONLY . " WHERE querybin = :querybin";
                            logit($logtarget, "{$sql}");
                            $rec2 = $dbh->prepare($sql);
                            $rec2->bindParam(":querybin", $binname, PDO::PARAM_STR);
                            $rec2->execute();
                        }
                        $dbh->commit();
                        $dbh->beginTransaction();
                        $sql = "UPDATE `{$tweets_table}` SET created_at = CONVERT_TZ(created_at, '{$badzone}', 'UTC') WHERE id <= {$max_id}";
                        logit($logtarget, "{$sql}");
                        $rec2 = $dbh->prepare($sql);
                        $rec2->execute();
                        if (in_array($binname, $all_bins)) {
                            $sql = "UPDATE tcat_query_bins SET access = :access WHERE querybin = :querybin";
                            logit($logtarget, "{$sql}");
                            $rec2 = $dbh->prepare($sql);
                            $rec2->bindParam(":access", $orig_access, PDO::PARAM_INT);
                            $rec2->bindParam(":querybin", $binname, PDO::PARAM_STR);
                            $rec2->execute();
                        }
                        $dbh->commit();
                    }
                    // Start working on the gaps and ratelimit tables
                    $ts = time();
                    logit($logtarget, "Backuping existing tcat_error_ratelimit and tcat_error_gap information to your system's temporary directory.");
                    $targetfile = sys_get_temp_dir() . "/tcat_error_ratelimit_and_gap_{$ts}.sql";
                    if (!file_exists($targetfile)) {
                        $cmd = "{$bin_mysqldump} --default-character-set=utf8mb4 -u{$dbuser} -h {$hostname} {$database} tcat_error_ratelimit tcat_error_gap > " . $targetfile;
                        system($cmd, $retval);
                    } else {
                        $retval = 1;
                        // failure
                    }
                    if ($retval != 0) {
                        logit($logtarget, "I couldn't create a backup at {$targetfile} - perhaps the backup already exists? Aborting this upgrade step.");
                    } else {
                        logit($logtarget, $cmd);
                        $cmd = "{$bin_gzip} {$targetfile}";
                        logit($logtarget, $cmd);
                        system($cmd);
                        logit($logtarget, "Backup placed here - you may want to store it somewhere else: " . $targetfile . '.gz');
                        // Fix issue described here https://github.com/digitalmethodsinitiative/dmi-tcat/issues/197
                        $sql = "SELECT id FROM tcat_error_ratelimit WHERE CONVERT_TZ(end, '{$badzone}', 'UTC') < '{$now}' ORDER BY CONVERT_TZ(end, '{$badzone}', 'UTC') DESC LIMIT 1";
                        logit($logtarget, "{$sql}");
                        $rec2 = $dbh->prepare($sql);
                        $rec2->execute();
                        $results2 = $rec2->fetch(PDO::FETCH_ASSOC);
                        $max_id = $results2['id'];
                        if (is_null($max_id)) {
                            $max_id = 0;
                            // table is empty.
                        }
                        $dbh->beginTransaction();
                        $sql = "UPDATE tcat_error_ratelimit SET start = CONVERT_TZ(start, '{$badzone}', 'UTC'), end = CONVERT_TZ(end, '{$badzone}', 'UTC') WHERE id <= {$max_id}";
                        logit($logtarget, "{$sql}");
                        $rec2 = $dbh->prepare($sql);
                        $rec2->execute();
                        $dbh->commit();
                        /*
                         * First part: rate limits
                         */
                        /*
                         * Strategy:
                         *
                         * As recording of ratelimit continues in tcat_error_ratelimit, we build a tcat_error_ratelimit_upgrade table.
                         * For the entire timespan _before_ the new gauge behaviour became effective, we do a minute-interval reconstruction in this temporary upgrade table.
                         * Finally we throw away existing tcat_error_ratelimit entries from this era and insert the ones from our temporary table.
                         *
                         */
                        $sql = "create temporary table if not exists tcat_error_ratelimit_upgrade ( id bigint, `type` varchar(32), start datetime not null, end datetime not null, tweets bigint not null, primary key(id, type), index(type), index(start), index(end) ) ENGINE=MyISAM";
                        $rec = $dbh->prepare($sql);
                        $rec->execute();
                        $sql = "select unix_timestamp(min(start)) as beginning_unix from tcat_error_ratelimit";
                        $rec = $dbh->prepare($sql);
                        $rec->execute();
                        $results = $rec->fetch(PDO::FETCH_ASSOC);
                        $beginning_unix = $results['beginning_unix'];
                        if (is_null($beginning_unix)) {
                            $difference_minutes = 0;
                        } else {
                            $difference_minutes = round($now_unix / 60 - $beginning_unix / 60 + 1);
                        }
                        logit($logtarget, "We have ratelimit information on this server for the past {$difference_minutes} minutes.");
                        logit($logtarget, "Reconstructing the rate limit for these now.");
                        // If we have an end before a start time, we are sure we cannot trust any minute measurements before this occurence.
                        // This situation (end < start) was related to a bug in the toDateTime() function, which formatted the minute-part wrong.
                        $sql = "select max(start) as time_fixed_dateformat, unix_timestamp(max(start)) as timestamp_fixed_dateformat from tcat_error_ratelimit where end < start";
                        $rec = $dbh->prepare($sql);
                        $rec->execute();
                        $results = $rec->fetch(PDO::FETCH_ASSOC);
                        $ratelimit_time_fixed_dateformat = $results['time_fixed_dateformat'];
                        $ratelimit_timestamp_fixed_dateformat = $results['timestamp_fixed_dateformat'];
                        $sql = "select max(start) as time_fixed_dateformat, unix_timestamp(max(start)) as timestamp_fixed_dateformat from tcat_error_gap where end < start";
                        $rec = $dbh->prepare($sql);
                        $rec->execute();
                        $results = $rec->fetch(PDO::FETCH_ASSOC);
                        $gap_time_fixed_dateformat = $results['time_fixed_dateformat'];
                        $gap_timestamp_fixed_dateformat = $results['timestamp_fixed_dateformat'];
                        // Compare query results and pick earliest possible moment of distrust.
                        if (!$ratelimit_time_fixed_dateformat && !$gap_time_fixed_dateformat) {
                            // Assume all measurements where hour-based until now.
                            logit($logtarget, "Dateformat fix not found");
                            $time_fixed_dateformat = $now;
                            $timestamp_fixed_dateformat = $now_unix;
                        } elseif (!$ratelimit_time_fixed_dateformat) {
                            // We have only information in the gap table
                            logit($logtarget, "Dateformat fix solely in tcat_error_gap");
                            $time_fixed_dateformat = $gap_time_fixed_dateformat;
                            $timestamp_fixed_dateformat = $gap_timestamp_fixed_dateformat;
                        } elseif (!$gap_time_fixed_dateformat) {
                            // We have only information in the ratelimit table
                            logit($logtarget, "Dateformat fix solely in tcat_error_ratelimit");
                            $time_fixed_dateformat = $ratelimit_time_fixed_dateformat;
                            $timestamp_fixed_dateformat = $ratelimit_timestamp_fixed_dateformat;
                        } else {
                            // Compare table information
                            if ($gap_timestamp_fixed_dateformat > $ratelimit_timestamp_fixed_dateformat) {
                                logit($logtarget, "Dateformat fix learned from tcat_error_gap");
                                $time_fixed_dateformat = $gap_time_fixed_dateformat;
                                $timestamp_fixed_dateformat = $gap_timestamp_fixed_dateformat;
                            } else {
                                logit($logtarget, "Dateformat fix learned from tcat_error_ratelimit");
                                $time_fixed_dateformat = $ratelimit_time_fixed_dateformat;
                                $timestamp_fixed_dateformat = $ratelimit_timestamp_fixed_dateformat;
                            }
                        }
                        logit($logtarget, "Dateformat fix found at '{$time_fixed_dateformat}'");
                        logit($logtarget, "Processing everything before MySQL date {$now}");
                        // Zero all minutes until the beginning of our capture era, for roles track and follow
                        for ($i = 1; $i <= $difference_minutes; $i++) {
                            $sql = "insert into tcat_error_ratelimit_upgrade ( id, `type`, `start`, `end`, `tweets` ) values ( {$i}, 'track',\n                                                date_sub( date_sub('{$now}', interval {$i} minute), interval second(date_sub('{$now}', interval {$i} minute)) second ),\n                                                date_sub( date_sub('{$now}', interval " . ($i - 1) . " minute), interval second(date_sub('{$now}', interval " . ($i - 1) . " minute)) second ),\n                                                0 )";
                            $rec = $dbh->prepare($sql);
                            $rec->execute();
                            $sql = "insert into tcat_error_ratelimit_upgrade ( id, `type`, `start`, `end`, `tweets` ) values ( {$i}, 'follow',\n                                                date_sub( date_sub('{$now}', interval {$i} minute), interval second(date_sub('{$now}', interval {$i} minute)) second ),\n                                                date_sub( date_sub('{$now}', interval " . ($i - 1) . " minute), interval second(date_sub('{$now}', interval " . ($i - 1) . " minute)) second ),\n                                                0 )";
                            $rec = $dbh->prepare($sql);
                            $rec->execute();
                            if ($i % ($difference_minutes / 100) == 0) {
                                logit($logtarget, "Creating temporary table " . round($i / $difference_minutes * 100) . "% completed");
                            }
                        }
                        logit($logtarget, "Building a new ratelimit table in temporary space");
                        $roles = array('track', 'follow');
                        foreach ($roles as $role) {
                            logit($logtarget, "Handle rate limits for role {$role}");
                            /*
                             * Start reading the tcat_error_ratelimit table for the role we are working on. We are using the 'start' column because it contains sufficient information.
                             */
                            $sql = "select id,\n                                           `type` as role,\n                                           date_format(start, '%k') as measure_hour,\n                                           date_format(start, '%i') as measure_minute,\n                                           tweets as incr_record from tcat_error_ratelimit where `type` = '{$role}'\n                                           order by id desc";
                            $rec = $dbh->prepare($sql);
                            $rec->execute();
                            $consolidate_hour = -1;
                            // the hour we are working on to consolidate our data
                            $consolidate_minute = -1;
                            // the minute we are working on to consolidate our data
                            $consolidate_max_id = -1;
                            // the maximum tcat_error_ratelimit ID within the consolidation timeframe
                            while ($res = $rec->fetch()) {
                                // measure_minute will contain the minute we are reading from the table (remember: backwards in time)
                                $measure_minute = ltrim($res['measure_minute']);
                                if ($measure_minute == '') {
                                    $measure_minute = 0;
                                }
                                // measure_hour will contain the minute we are reading from the table (again: backwards in time)
                                $measure_hour = $res['measure_hour'];
                                if ($measure_minute != $consolidate_minute || $measure_hour != $consolidate_hour) {
                                    /* 
                                     * We are reading a new entry not inside our consolidation frame (which has the resolution of an hour or minute)
                                     * We will consolidate our data, unless we are at the first row.
                                     */
                                    if ($consolidate_minute == -1) {
                                        // first row received
                                        $consolidate_minute = $measure_minute;
                                        $consolidate_hour = $measure_hour;
                                        $consolidate_max_id = $res['id'];
                                    } else {
                                        $controller_restart_detected = false;
                                        /*
                                         * The SQL query below reads the MIN and MAX recorded tweets values for our interval.
                                         *
                                         * It additionally checks to detect controller resets. Whenever the controller resets itself, because of a crash or server reboot,
                                         * the incremental counter will jump to zero. This SQL query recognizes this sudden jump by explicitely verifying the order.
                                         *
                                         * Note: this query uses max(start) to determine the start parameter to pass to the smoothing function. If we would've used min(start),
                                         * we inadvertently include the start column of the NEXT row, and that's not our intention. Because we are using max(start), it is
                                         * possible that the difference in minutes between the 'start' and 'end' becomes less than 1 minute. Our smoother function is
                                         * aware of this.
                                         *
                                         */
                                        $sql = "select max(tweets) as record_max,\n                                                       min(tweets) as record_min,\n                                                       max(start) as start, unix_timestamp(max(start)) as start_unix,\n                                                       max(end) as end, unix_timestamp(max(end)) as end_unix\n                                                from tcat_error_ratelimit where `type` = '{$role}' and\n                                                       id >= " . $res['id'] . " and\n                                                       id <= {$consolidate_max_id} and\n                                                            ( select tweets from tcat_error_ratelimit where id = {$consolidate_max_id} ) >\n                                                            ( select tweets from tcat_error_ratelimit where id = " . $res['id'] . " )";
                                        $rec2 = $dbh->prepare($sql);
                                        $rec2->execute();
                                        // our query will always return a non-empty result, because min()/max() always produce a row (with a possible NULL as value)
                                        while ($res2 = $rec2->fetch()) {
                                            if ($res2['record_max'] == null) {
                                                // The order is NOT incremental.
                                                $controller_restart_detected = true;
                                            }
                                            $record_max = $res2['record_max'];
                                            $record_min = $res2['record_min'];
                                            $record = $record_max - $record_min;
                                            if ($controller_restart_detected) {
                                            } elseif ($record >= 0) {
                                                ratelimit_smoother($dbh, $timestamp_fixed_dateformat, $role, $res2['start'], $res2['end'], $res2['start_unix'], $res2['end_unix'], $record);
                                            }
                                        }
                                        $consolidate_minute = $measure_minute;
                                        $consolidate_hour = $measure_hour;
                                        $consolidate_max_id = $res['id'];
                                    }
                                }
                            }
                            if ($consolidate_minute != -1) {
                                // we consolidate the last minute
                                $sql = "select max(tweets) as record_max,\n                                               min(tweets) as record_min,\n                                               min(start) as start, unix_timestamp(min(start)) as start_unix,\n                                               max(end) as end, unix_timestamp(max(end)) as end_unix\n                                        from tcat_error_ratelimit where `type` = '{$role}' and\n                                               id <= {$consolidate_max_id}";
                                $rec2 = $dbh->prepare($sql);
                                $rec2->execute();
                                while ($res2 = $rec2->fetch()) {
                                    $record_max = $res2['record_max'];
                                    $record_min = $res2['record_min'];
                                    $record = $record_max - $record_min;
                                    if ($record > 0) {
                                        ratelimit_smoother($dbh, $timestamp_fixed_dateformat, $role, $res2['start'], $res2['end'], $res2['start_unix'], $res2['end_unix'], $record);
                                    }
                                }
                            }
                        }
                        // By using a TRANSACTION block here, we ensure the tcat_error_ratelimit will not end up in an undefined state
                        $dbh->beginTransaction();
                        $sql = "delete from tcat_error_ratelimit where start < '{$now}' or end < '{$now}'";
                        $rec = $dbh->prepare($sql);
                        logit($logtarget, "Removing old records from tcat_error_ratelimit");
                        $rec->execute();
                        $sql = "insert into tcat_error_ratelimit ( `type`, start, end, tweets ) select `type`, start, end, tweets from tcat_error_ratelimit_upgrade order by start asc";
                        $rec = $dbh->prepare($sql);
                        logit($logtarget, "Inserting new records into tcat_error_ratelimit");
                        $rec->execute();
                        /*
                         * The next operation will break the tie between the ascending order of the ID primary key, and the datetime columns start and end. This is not a problem per se.
                         * Rebuilding that order is feasible, but we shouldn't re-run this upgrade step anyway and this will never be presented as an option to the user.
                         * If something goes wrong, restore the original table from the backup instead.
                         */
                        $sql = "insert into tcat_status ( variable, value ) values ( 'ratelimit_database_rebuild', '1' )";
                        $rec = $dbh->prepare($sql);
                        $rec->execute();
                        $dbh->commit();
                        logit($logtarget, "Rebuilding of tcat_error_ratelimit has finished");
                        $sql = "drop table tcat_error_ratelimit_upgrade";
                        $rec = $dbh->prepare($sql);
                        $rec->execute();
                        /*
                         * Second part: gaps
                         *
                         * Notice we do all datetime functions in native MySQL. This may appear to be cumbersome but is has the advantage of having to do a minimal ammount of datetime conversions,
                         * and being able to mostly ignore the system clock (OS), with the single exception of the reduce_gap_size() function.
                         * The gap table is not big and this upgrade step should maximally take several hours on long-running servers.
                         */
                        logit($logtarget, "Now rebuilding tcat_error_gap table");
                        // Fix issue described here https://github.com/digitalmethodsinitiative/dmi-tcat/issues/197
                        $sql = "SELECT id FROM tcat_error_gap WHERE CONVERT_TZ(end, '{$badzone}', 'UTC') < '{$now}' ORDER BY CONVERT_TZ(end, '{$badzone}', 'UTC') DESC LIMIT 1";
                        logit($logtarget, "{$sql}");
                        $rec2 = $dbh->prepare($sql);
                        $rec2->execute();
                        $results2 = $rec2->fetch(PDO::FETCH_ASSOC);
                        $max_id = $results2['id'];
                        if (is_null($max_id)) {
                            $max_id = 0;
                            // table is empty.
                        }
                        $dbh->beginTransaction();
                        $sql = "UPDATE tcat_error_gap SET start = CONVERT_TZ(start, '{$badzone}', 'UTC'), end = CONVERT_TZ(end, '{$badzone}', 'UTC') WHERE id <= {$max_id}";
                        logit($logtarget, "{$sql}");
                        $rec2 = $dbh->prepare($sql);
                        $rec2->execute();
                        $dbh->commit();
                        $existing_roles = array('track', 'follow', 'onepercent');
                        foreach ($existing_roles as $type) {
                            $time_begin_gap = $timestamp_begin_gap = null;
                            // Note: 1970-01-01 is the Unix timestamp for NULL. It was written to the database whenever there was a gap with an 'unknown' start time,
                            // due to the fact that there was no proc/ information available to the controller. This behaviour has changed.
                            $sql = "select min(start) as time_begin_gap, unix_timestamp(min(start)) as timestamp_begin_gap FROM tcat_error_gap where type = '{$type}' and start > '1970-01-01 01:01:00'";
                            $rec = $dbh->prepare($sql);
                            $rec->execute();
                            if ($rec->execute() && $rec->rowCount() > 0) {
                                while ($row = $rec->fetch(PDO::FETCH_ASSOC)) {
                                    $time_begin_gap = $row['time_begin_gap'];
                                    $timestamp_begin_gap = $row['timestamp_begin_gap'];
                                }
                            }
                            if (!$now || !$now_unix || !$time_begin_gap || !$timestamp_begin_gap) {
                                logit($logtarget, "Nothing to do for role {$type}");
                                continue;
                            }
                            $difference_minutes = round($now_unix / 60 - $timestamp_begin_gap / 60 + 1);
                            logit($logtarget, "For role {$type}, we have gap information on this server for the past {$difference_minutes} minutes.");
                            $gaps = array();
                            $sql = "select * from tcat_error_gap where type = '{$type}' and start > '1970-01-01 01:01:00' and end < '{$now}' order by id, start asc";
                            $rec = $dbh->prepare($sql);
                            $rec->execute();
                            $ignore_start = $already_recorded_until = null;
                            $trust_minute_measurement = false;
                            while ($row = $rec->fetch(PDO::FETCH_ASSOC)) {
                                if ($row['start'] == $ignore_start) {
                                    continue;
                                }
                                // If we are being told about a gap we already know, skip it
                                if ($already_recorded_until) {
                                    $sql2 = "select '" . $row['start'] . "' > '{$already_recorded_until}'";
                                    $rec2 = $dbh->prepare($sql2);
                                    $rec2->execute();
                                    $later_in_time = $rec2->fetchColumn();
                                    if ($later_in_time != '1') {
                                        // Not registering the gap starting at $row['start'] here, because it is already accounted for.
                                        continue;
                                    }
                                }
                                // If we know for a fact the minute measurement is accurate, we work with that precision, and otherwise
                                // we try to attain it by searching the real capture data.
                                if (!$trust_minute_measurement) {
                                    $sql2 = "select '" . $row['start'] . "' > '{$time_fixed_dateformat}'";
                                    $rec2 = $dbh->prepare($sql2);
                                    $rec2->execute();
                                    $later_in_time = $rec2->fetchColumn();
                                    if ($later_in_time == '1') {
                                        $trust_minute_measurement = true;
                                    }
                                }
                                // The controller could create repeated rows with the same 'start' value if it didn't manage to boot up a role
                                // The next query recognizes this.
                                $sql2 = "select max(end) as max_end from tcat_error_gap where type = '{$type}' and start = '" . $row['start'] . "'";
                                $rec2 = $dbh->prepare($sql2);
                                $rec2->execute();
                                $max_end = null;
                                while ($row2 = $rec2->fetch(PDO::FETCH_ASSOC)) {
                                    $max_end = $row2['max_end'];
                                    break;
                                }
                                if ($max_end) {
                                    // Example: '2016-04-19 03:12:44'
                                    if (preg_match("/^(\\d{4})-(\\d{2})-(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2})\$/", $max_end, $matches_end) && preg_match("/^(\\d{4})-(\\d{2})-(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2})\$/", $row['start'], $matches_start)) {
                                        if (!$trust_minute_measurement) {
                                            // Drop a distrusted minute measurement due to previous dateformat bug
                                            // This first defines the gap as wide as possible (with an hourly precision). Afterwards we prune it by searching the real capture data.
                                            $matches_start[5] = '00';
                                            // minutes start
                                            $matches_start[6] = '00';
                                            // seconds start
                                            $matches_end[5] = '59';
                                            // minutes end
                                            $matches_end[6] = '59';
                                            // seconds end
                                            $new_start = $matches_start[1] . '-' . $matches_start[2] . '-' . $matches_start[3] . ' ' . $matches_start[4] . ':' . $matches_start[5] . ':' . $matches_start[6];
                                            $new_end = $matches_end[1] . '-' . $matches_end[2] . '-' . $matches_end[3] . ' ' . $matches_end[4] . ':' . $matches_end[5] . ':' . $matches_end[6];
                                            // Now attempt to reduce the gap size
                                            $reduced = reduce_gap_size($type, $new_start, $new_end);
                                            if (is_null($reduced)) {
                                                logit($logtarget, "Erroneous gap report for role {$type} from '" . $new_start . "' to '" . $new_end . "'");
                                            } else {
                                                $new_start = $reduced['shrunk_start'];
                                                $new_end = $reduced['shrunk_end'];
                                            }
                                        } else {
                                            // Just copy both complete strings
                                            $new_start = $matches_start[0];
                                            $new_end = $matches_end[0];
                                            //                                    logit($logtarget, "We trust the next recorded gap without search");
                                        }
                                        // logit($logtarget, "Detected possible gap from '" . $new_start . "' to '" . $new_end . "' - now investigating");
                                        logit($logtarget, "Recording gap for role {$type} from '" . $new_start . "' to '" . $new_end . "'");
                                        $duplicate = false;
                                        foreach ($gaps as $gap) {
                                            if ($gap['start'] == $new_start && $gap['end'] == $new_end) {
                                                $duplicate = true;
                                            }
                                        }
                                        if (!$duplicate) {
                                            $gap = array('start' => $new_start, 'end' => $new_end);
                                            $gaps[] = $gap;
                                        }
                                        $ignore_start = $row['start'];
                                        $already_recorded_until = $new_end;
                                    }
                                }
                            }
                            // By using a TRANSACTION block here, we ensure the tcat_error_gap will not end up in an undefined state
                            $dbh->beginTransaction();
                            $sql = "delete from tcat_error_gap where type = '{$type}' and end <= '{$now}'";
                            $rec = $dbh->prepare($sql);
                            $rec->execute();
                            // Knit gap timespans togheter if they are absolutely sequential.
                            $newgaps = array();
                            $first = true;
                            $previous_start = $previous_end = null;
                            foreach ($gaps as $gap) {
                                if ($first) {
                                    $previous_start = $gap['start'];
                                    $previous_end = $gap['end'];
                                    $first = false;
                                    continue;
                                }
                                $sql = "select timediff('" . $gap['start'] . "', '{$previous_end}') as difference";
                                $rec = $dbh->prepare($sql);
                                $rec->execute();
                                $difference = null;
                                while ($row = $rec->fetch(PDO::FETCH_ASSOC)) {
                                    $difference = $row['difference'];
                                    break;
                                }
                                // This one second difference will be produced by us widening an hourly measurement as much as possible
                                // (and not finding any real tweet data to shrink it)
                                if ($gap !== end($gaps) && isset($difference) && $difference == '00:00:01') {
                                    // Keep on knittin'
                                    $previous_end = $gap['end'];
                                } else {
                                    $newgaps[] = array('start' => $previous_start, 'end' => $previous_end);
                                    $previous_start = $gap['start'];
                                    $previous_end = $gap['end'];
                                }
                            }
                            // The knitting won't produce a result in a situation with only a single gap record
                            if (count($gaps) > 1) {
                                $gaps = $newgaps;
                            }
                            foreach ($gaps as $gap) {
                                $sql = "insert into tcat_error_gap ( `type`, `start`, `end` ) values ( '{$type}', '" . $gap['start'] . "', '" . $gap['end'] . "' )";
                                $rec = $dbh->prepare($sql);
                                $rec->execute();
                            }
                            // The final step is to prune tcap_error_gap to remove all gaps lesser than IDLETIME
                            if (!defined('IDLETIME')) {
                                define('IDLETIME', 600);
                            }
                            if (!defined('IDLETIME_FOLLOW')) {
                                define('IDLETIME_FOLLOW', IDLETIME);
                            }
                            if ($type == 'follow') {
                                $idletime = IDLETIME_FOLLOW;
                            } else {
                                $idletime = IDLETIME;
                            }
                            $sql = "delete from tcat_error_gap where time_to_sec(timediff(end,start)) < {$idletime}";
                            $rec = $dbh->prepare($sql);
                            $rec->execute();
                            $dbh->commit();
                        }
                        logit($logtarget, "Rebuilding of tcat_error_gap has finished");
                        // For ratelimit exports to function, we want to have the tcat_captured_phrases table
                        // As we have not recorded this (yet), we need to reconstruct it from the previous phrases and detect which tweets contain which queries.
                        logit($logtarget, "Creating the tcat_captured_phrases table");
                        create_admin();
                        $trackbins = get_track_bin_phrases();
                        foreach ($trackbins as $querybin => $phrases) {
                            logit($logtarget, "Extracting keyword phrase matches from querybin {$querybin} and inserting into tcat_captured_phrases ..");
                            foreach ($phrases as $phrase => $phrase_id) {
                                if (substr($phrase, 0, 1) !== "'" && strpos($phrase, ' ') !== false) {
                                    // The user intends a AND match here, such as: [ scottish AND independence ]
                                    // Both words should be in the tweet, but not neccessarily next to each other ( according to the documentation: https://github.com/digitalmethodsinitiative/dmi-tcat/wiki/FAQ#keyword-track )
                                    $subphrases = explode(' ', $phrase);
                                    $sql = "REPLACE INTO tcat_captured_phrases ( tweet_id, phrase_id, created_at ) SELECT id, {$phrase_id}, created_at FROM " . quoteIdent($querybin . "_tweets") . " WHERE text REGEXP ?";
                                    if (count($subphrases) > 1) {
                                        $sql .= str_repeat(" AND text REGEXP ?", count($subphrases) - 1);
                                    }
                                    $rec = $dbh->prepare($sql);
                                    $i = 1;
                                    foreach ($subphrases as $subphrase) {
                                        $regexp = "[[:<:]]" . $subphrase . "[[:>:]]";
                                        $rec->bindParam($i, $regexp, PDO::PARAM_STR);
                                        $i++;
                                    }
                                    $rec->execute();
                                } else {
                                    // The user intends an exact string match here, such as: [ 'scottish independence' ]
                                    // Or the keyword string is a simple, single-word phrase
                                    $phrasematch = str_replace("'", "", $phrase);
                                    // replace any occurances of the quoting character
                                    $sql = "REPLACE INTO tcat_captured_phrases ( tweet_id, phrase_id, created_at ) SELECT id, {$phrase_id}, created_at FROM " . quoteIdent($querybin . "_tweets") . " WHERE text REGEXP :regexp";
                                    $rec = $dbh->prepare($sql);
                                    $regexp = "[[:<:]]" . $phrasematch . "[[:>:]]";
                                    $rec->bindParam(":regexp", $regexp, PDO::PARAM_STR);
                                    $rec->execute();
                                }
                                // logit($logtarget, $sql);    // print SQL statements for debugging purposes
                            }
                        }
                        // Indicate to the analytics panel we have fully executed this upgrade step and export functions can become available
                        $sql = "update tcat_status set value = 2 where variable = 'ratelimit_database_rebuild'";
                        $rec = $dbh->prepare($sql);
                        $rec->execute();
                        logit($logtarget, "Phrases table successfully built. Gap and ratelimit export features have now been unlocked.");
                    }
                }
            }
        }
    }
    // 24/05/2016 Fix index of tweet_id on withheld tables
    $query = "SHOW TABLES";
    $rec = $dbh->prepare($query);
    $rec->execute();
    $results = $rec->fetchAll(PDO::FETCH_COLUMN);
    $ans = '';
    if ($interactive == false) {
        // require auto-upgrade level 0
        if ($aulevel >= 0) {
            $ans = 'a';
        } else {
            $ans = 'SKIP';
        }
    }
    if ($ans !== 'SKIP') {
        foreach ($results as $k => $v) {
            if (!preg_match("/_withheld\$/", $v)) {
                continue;
            }
            if ($single && $v !== $single . '_withheld') {
                continue;
            }
            $query = "SHOW INDEXES FROM {$v}";
            $rec = $dbh->prepare($query);
            $rec->execute();
            $indexes = $rec->fetchAll();
            $update = FALSE;
            foreach ($indexes as $index) {
                if ($index['Key_name'] == 'tweet_id' && $index['Column_name'] == 'user_id') {
                    $update = TRUE;
                    break;
                }
            }
            if ($update && $dry_run) {
                $suggested = true;
            }
            if ($update && $dry_run == false) {
                if ($ans !== 'a') {
                    $ans = cli_yesnoall("Fixing index of tweet_id on table {$v}", 0, '2f1c585fac9e2646951bb44f61e864f4488a37e6');
                }
                if ($ans == 'a' || $ans == 'y') {
                    logit($logtarget, "Fixing index of tweet_id on table {$v}");
                    $query = "ALTER TABLE " . quoteIdent($v) . " DROP KEY `tweet_id`, ADD KEY `tweet_id` (`tweet_id`)";
                    $rec = $dbh->prepare($query);
                    $rec->execute();
                }
            }
        }
    }
    // End of upgrades
    if ($required == true && $suggested == true) {
        $required = true;
        $suggested = false;
        // only return the strongest option
    }
    if ($dry_run) {
        return array('suggested' => $suggested, 'required' => $required);
    }
}