if (dbserver_has_utf8mb4_support() == false) {
die("DMI-TCAT requires at least MySQL version 5.5.3 - please upgrade your server\n");
}
$archive_dbh = new PDO("mysql:host={$dbhost_from};dbname=" . $dbdatabase_from, $dbuser_from, $dbpass_from, array(PDO::MYSQL_ATTR_INIT_COMMAND => "set sql_mode='ALLOW_INVALID_DATES'"));
$archive_dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh = new PDO("mysql:host={$dbhost_to};dbname=" . $dbdatabase_to, $dbuser_to, $dbpass_to, array(PDO::MYSQL_ATTR_INIT_COMMAND => "set sql_mode='ALLOW_INVALID_DATES'"));
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if (empty($bin_name)) {
die("bin_name not set\n");
}
$querybin_id = queryManagerBinExists($bin_name);
// Old table, importing tweets from here
$query = $archive_dbh->prepare("SELECT * FROM " . $ytk_table);
$query->execute();
// create new tables
create_bin($bin_name, $dbh);
queryManagerCreateBinFromExistingTables($bin_name, $querybin_id, 'import ytk', $queries);
// insert old data in new tables
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
$txt = $row['text'];
$hashtags = $urls = $mentions = $t = array();
$t["id"] = $row['id'];
$t["text"] = $txt;
$t["created_at"] = date("Y-m-d H:i:s", strtotime($row["created_at"]));
$t["from_user_name"] = $row['from_user'];
$t["from_user_id"] = $row['from_user_id'];
$t["from_user_lang"] = $row['iso_language_code'];
$t["from_user_tweetcount"] = null;
$t["from_user_followercount"] = null;
$t["from_user_friendcount"] = null;
$t["from_user_realname"] = null;
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;
}
