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);
}
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>';
}
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);
}
<?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; }
<?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;
}
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;
}
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");
# "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;
?>
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;
}
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>
$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: */
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);
}
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;
/**
* 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);
}
}