public static function run(DatabaseMysql $db, $dbname, $test = false, $verbose = false)
{
// Don't process the video wiki
if ($dbname == 'video151') {
return true;
}
$str_sql = <<<SQL
update video_info, image
set video_id = substring_index(substring_index(SUBSTRING_INDEX(img_metadata, 's:7:"videoId";', -1), '";', 1),':"', -1)
where premium = 0
and video_title = img_name
and video_title is not null
and img_metadata like '%s:7:"videoId";s:%'
and video_id = ''
SQL;
$int_sql = <<<SQL
update video_info, image
set video_id = substring_index(SUBSTRING_INDEX(img_metadata, 's:7:"videoId";i:', -1), ';', 1)
where premium = 0
and video_title = img_name
and video_title is not null
and img_metadata like '%s:7:"videoId";i:%'
SQL;
if ($verbose) {
echo "Running on {$dbname}:\n\t{$str_sql}\n\t{$int_sql}\n";
}
if (empty($test)) {
$res = $db->query($str_sql);
$res = $db->query($int_sql);
}
}
/**
* If a database connection has already been established, it returns that
* connection. Otherwise, it establishes one, and returns that.
* @global string $wgContributionTrackingDBserver : DB Server name, defined
* in ContributionTracking.php
* @global string $wgContributionTrackingDBname : Database name, defined in
* ContributionTracking.php
* @global string $wgContributionTrackingDBuser : Database user, defined in
* ContributionTracking.php
* @global string $wgContributionTrackingDBpassword : Database password,
* defined in ContributionTracking.php
* @staticvar DatabaseMysql $db
* @return DatabaseMysql The established database connection
*/
static function contributionTrackingConnection()
{
global $wgContributionTrackingDBserver, $wgContributionTrackingDBname;
global $wgContributionTrackingDBuser, $wgContributionTrackingDBpassword;
static $db;
if (!$db) {
$db = new DatabaseMysql($wgContributionTrackingDBserver, $wgContributionTrackingDBuser, $wgContributionTrackingDBpassword, $wgContributionTrackingDBname);
$db->query("SET names utf8");
}
return $db;
}
public static function isExist(DatabaseMysql $db, $verbose = false, $dryRun = false, $params = array())
{
echo "Wiki: {$params['dbname']} (ID:{$params['cityId']})\n";
if (!$db->tableExists('image')) {
echo "ERROR: {$params['dbname']} (ID:{$params['cityId']}): image table not exist.\n\n";
return;
}
if ($params['dbname'] == F::app()->wg->WikiaVideoRepoDBName) {
echo "SKIP: {$params['dbname']} (ID:{$params['cityId']})\n\n";
return;
}
$row = $db->selectRow('image', '1', array('img_media_type' => 'video', 'img_size' => '66162', 'img_sha1' => 'm03a6fnvxhk8oj5kgnt11t6j7phj5nh', "img_timestamp >= '20131029'", "img_timestamp <= '20131110'"), __METHOD__);
$result = $row ? "FOUND" : "NOT FOUND";
echo "{$params['dbname']} (ID:{$params['cityId']}): default thumbnail " . $result . " \n";
}
/**
* Used by runOnCluster to Normalize video titles and metadata
*
* @param DatabaseMysql $db
* @param bool $test
* @param bool $verbose
* @param array $params
*/
public static function run(DatabaseMysql $db, $test, $verbose = false, $params)
{
if (!$db->tableExists('image') || !$db->tableExists('video_info')) {
echo "ERROR: {$params['dbname']} (ID:{$params['cityId']}): image/video_info table does not exist.\n";
return;
}
foreach (self::getVideoRows($db) as $video) {
$originalName = $video->img_name;
if (\UtfNormal::quickIsNFC($originalName)) {
if ($verbose) {
echo "Already in NFC form; skipping: {$originalName}\n";
}
continue;
}
self::updateVideo($video, $db, $test, $verbose);
}
}
/**
* @param GWTWiki $gwtWikiObject
* @throws GWTException
*/
public function updateWiki(GWTWiki $gwtWikiObject)
{
$userId = $gwtWikiObject->getUserId();
$res = $this->databaseConnection->update("webmaster_sitemaps", array("user_id" => $userId, "upload_date" => $gwtWikiObject->getUploadDate(), "page_count" => $gwtWikiObject->getPageCount()), array("wiki_id" => $gwtWikiObject->getWikiId()));
if (!$res) {
throw new GWTException("Failed to update " . $gwtWikiObject->getUserId() . " " . $gwtWikiObject->getWikiId());
}
}
function tableName($name)
{
switch ($name) {
case 'interwiki':
return ForgeConfig::get('sys_dbname') . '.plugin_mediawiki_interwiki';
default:
return parent::tableName($name);
}
}
public static function run(DatabaseMysql $db, $test = false, $verbose = false, $params = null)
{
$dbname = $params['dbname'];
// Don't process the video wiki
if ($dbname == 'video151') {
return true;
}
// Get all suggestion data in this wiki
$sql = <<<SQL
select page_id, props
from page_wikia_props
where propname = 19
SQL;
if ($verbose) {
echo "Running on {$dbname}\n";
}
if (empty($test)) {
$res = $db->query($sql);
// Loop through all the data
while ($row = $db->fetchRow($res)) {
// The prop field should be a serialized array
$data = unserialize($row['props']);
if (empty($data)) {
continue;
}
if (count($data) == 0) {
continue;
}
foreach ($data as $item) {
// If this is not an array, the data is bad
if (empty($item['title'])) {
file_put_contents('/tmp/badData.log', $dbname . "\n", FILE_APPEND);
echo "\t{$dbname} - BAD DATA\n";
// Exit immediately, we don't need to run the rest
return true;
}
}
}
}
}
public static function run(DatabaseMysql $db, $dbname, $test = false, $verbose = false)
{
global $titleInfo;
// Don't process the video wiki
if ($dbname == 'video151') {
return true;
}
$sql = "select video_title as title, provider from video_info where premium = 1 and (video_id = '' or provider is null)";
$result = $db->query($sql);
$numRows = 0;
$numFound = 0;
$update = array();
while ($row = $db->fetchObject($result)) {
$numRows++;
// echo "Checking ".$row->title." from ".$row->provider."\n";
if (isset($titleInfo[$row->title])) {
$numFound++;
$info = $titleInfo[$row->title];
$provider = $row->provider ? null : $info['provider'];
$update[] = [$row->title, $provider, $info['id']];
}
}
$db->freeResult($result);
if ($numRows) {
echo "[{$dbname}] Found video IDs for {$numFound} of {$numRows} videos\n";
}
foreach ($update as $info) {
list($title, $provider, $id) = $info;
$sql = "update video_info\n set video_id='{$id}' " . ($provider ? ", provider=" . $db->addQuotes($provider) : '') . ' ' . "where video_title = " . $db->addQuotes($title);
if ($verbose) {
echo "Running SQL on {$dbname}: {$sql}\n";
}
if (empty($test)) {
$db->query($sql);
}
}
}
/**
* 1. go through all wikis which are marked for closing and check which one
* want to have images packed.
*
* 2. pack images, send them via rsync to target server,
*
* 3. mark in city_list.city_flags that images are sent,
*
* 4. remove images
*
* @access public
*/
public function execute()
{
global $wgUploadDirectory, $wgDBname;
if (!isset($this->mOptions['wiki_id'])) {
echo "Wiki Id is not valid";
die(1);
}
$where = array("city_id" => intval($this->mOptions['wiki_id']));
$dbr = WikiFactory::db(DB_SLAVE);
$row = $dbr->selectRow(array("city_list"), array("city_id", "city_flags", "city_dbname", "city_url", "city_public"), $where, __METHOD__);
if (is_object($row)) {
/**
* reasonable defaults for wikis and some presets
*/
$hide = false;
$xdumpok = true;
$newFlags = 0;
$dbname = $row->city_dbname;
$folder = WikiFactory::getVarValueByName("wgUploadDirectory", $row->city_id);
$cluster = WikiFactory::getVarValueByName("wgDBcluster", $row->city_id);
/**
* safety check, if city_dbname is not unique die with message
*/
$check = $dbr->selectRow(array("city_list"), array("count(*) as count"), array("city_dbname" => $dbname), __METHOD__, array("GROUP BY" => "city_dbname"));
if ($check->count > 1) {
echo "{$dbname} is not unique. Check city_list and rerun script";
die(1);
}
Wikia::log(__CLASS__, "info", "city_id={$row->city_id} city_url={$row->city_url} city_dbname={$dbname} city_flags={$row->city_flags} city_public={$row->city_public}");
Wikia::log(__CLASS__, "info", "removing folder {$folder}");
if (is_dir($wgUploadDirectory)) {
/**
* what should we use here?
*/
$cmd = "rm -rf {$folder}";
wfShellExec($cmd, $retval);
if ($retval) {
/**
* info removing folder was not possible
*/
}
}
/**
* clear wikifactory tables, condition for city_public should
* be always true there but better safe than sorry
*/
$dbw = WikiFactory::db(DB_MASTER);
$dbw->delete("city_list", array("city_public" => array(0, -1), "city_id" => $row->city_id), __METHOD__);
Wikia::log(__CLASS__, "info", "{$row->city_id} removed from WikiFactory tables");
/**
* drop database, get db handler for proper cluster
*/
global $wgDBadminuser, $wgDBadminpassword;
$centralDB = empty($cluster) ? "wikicities" : "wikicities_{$cluster}";
/**
* get connection but actually we only need info about host
*/
$local = wfGetDB(DB_MASTER, array(), $centralDB);
$server = $local->getLBInfo('host');
$dbw = new DatabaseMysql($server, $wgDBadminuser, $wgDBadminpassword, $centralDB);
$dbw->begin();
$dbw->query("DROP DATABASE `{$row->city_dbname}`");
$dbw->commit();
Wikia::log(__CLASS__, "info", "{$row->city_dbname} dropped from cluster {$cluster}");
}
}
public static function lvsUpdateStatus(DatabaseMysql $db, $verbose = false, $dryRun = false, $params = array())
{
echo "Wiki: {$params['dbname']} (ID:{$params['cityId']})\n";
if (!$db->tableExists('page_wikia_props')) {
echo "ERROR: {$params['dbname']} (ID:{$params['cityId']}): page_wikia_props table not exist.\n\n";
return;
}
if ($params['dbname'] == F::app()->wg->WikiaVideoRepoDBName) {
echo "SKIP: {$params['dbname']} (ID:{$params['cityId']})\n\n";
return;
}
$limit = 5000;
$total = 0;
$kept = 0;
$swapped = 0;
$swappedExact = 0;
$suggestions = 0;
$totalAffected = 0;
$statusInfo = WPP_LVS_STATUS_INFO;
$statusSuggest = WPP_LVS_SUGGEST;
$status = WPP_LVS_STATUS;
$sqls[] = <<<SQL
\t\t\t\tSELECT p1.page_id, p1.props as suggestions,
\t\t\t\t\tsubstring(p2.props, locate('"status";i:', p2.props)+11, 1) status
\t\t\t\tFROM page_wikia_props p1
\t\t\t\tLEFT JOIN page_wikia_props p2 ON p1.page_id = p2.page_id AND p2.propname = {$statusInfo}
\t\t\t\tWHERE p1.propname = {$statusSuggest}
\t\t\t\tORDER by p1.page_id
\t\t\t\tLIMIT {$limit}
SQL;
$sqls[] = <<<SQL
\t\t\t\tSELECT p1.page_id, '' as suggestions,
\t\t\t\t\tsubstring(p1.props, locate('"status";i:', p1.props)+11, 1) status
\t\t\t\tFROM page_wikia_props p1
\t\t\t\tLEFT JOIN page_wikia_props p2 ON p1.page_id = p2.page_id AND p2.propname = {$statusSuggest}
\t\t\t\tWHERE p1.propname = {$statusInfo} AND p2.page_id is null
\t\t\t\tORDER by p1.page_id
\t\t\t\tLIMIT {$limit}
SQL;
foreach ($sqls as $sql) {
echo "SQL: {$sql}\n";
do {
$result = $db->query($sql, __METHOD__);
$pages = $result->numRows();
echo "Total Pages: {$pages}\n";
$cnt = 1;
$total = $total + $pages;
while ($row = $db->fetchObject($result)) {
$pageId = $row->page_id;
echo "\tPage ID {$pageId} [{$cnt} of {$pages}]: ";
$flags = array();
$statusList = array();
// video with suggestions
if (!empty($row->suggestions)) {
$statusList[] = "STATUS_SWAPPABLE";
$flags[] = LicensedVideoSwapHelper::STATUS_SWAPPABLE;
$suggestions++;
}
// kept video
if (!empty($row->status) && $row->status == 1) {
$statusList[] = "STATUS_KEEP";
$flags[] = LicensedVideoSwapHelper::STATUS_KEEP;
$kept++;
}
// swapped video
if (!empty($row->status) && $row->status == 2) {
$statusList[] = "STATUS_SWAP";
$flags[] = LicensedVideoSwapHelper::STATUS_SWAP;
$swapped++;
}
// swapped video with exact match
if (!empty($row->status) && $row->status == 3) {
$statusList[] = "STATUS_SWAP";
$statusList[] = "STATUS_EXACT ";
$flags[] = LicensedVideoSwapHelper::STATUS_SWAP;
$flags[] = LicensedVideoSwapHelper::STATUS_EXACT;
$swappedExact++;
}
$props = implode('|', $flags);
echo implode(', ', $statusList) . " ( {$props} ) .... ";
$sqlInsert = <<<SQL
\t\t\t\t\t\tINSERT INTO page_wikia_props (page_id, propname, props)
\t\t\t\t\t\tVALUES ({$pageId}, {$status}, ({$props}))
\t\t\t\t\t\tON DUPLICATE KEY UPDATE props = (props | {$props})
SQL;
if ($dryRun) {
$affected = 1;
} else {
$db->query($sqlInsert, __METHOD__);
$affected = $db->affectedRows();
}
echo "{$affected} affected.\n";
$totalAffected += $affected;
$cnt++;
}
} while ($pages == $limit);
echo "\n";
}
echo "{$params['dbname']} (ID:{$params['cityId']}): Total Pages: {$total}, Kept Videos: {$kept}, Swapped Videos: {$swapped}, ";
echo "Swapped Videos with Exact Match: {$swappedExact}, Videos with Suggestions: {$suggestions}, Affected: {$totalAffected}\n\n";
}
/**
* 1. go through all wikis which are marked for closing and check which one
* want to have images packed.
*
* 2. pack images, send them via rsync to target server,
*
* 3. mark in city_list.city_flags that images are sent,
*
* 4. remove images
*
* @access public
*/
public function execute()
{
global $wgUploadDirectory, $wgDBname, $IP;
$first = isset($this->mOptions["first"]) ? true : false;
$sleep = isset($this->mOptions["sleep"]) ? $this->mOptions["sleep"] : 15;
$condition = array("ORDER BY" => "city_id");
$this->info('start', ['first' => $first, 'limit' => $this->mOptions["limit"] ?: false]);
/**
* if $first is set skip limit checking
*/
if (!$first) {
if (isset($this->mOptions["limit"]) && is_numeric($this->mOptions["limit"])) {
$condition["LIMIT"] = $this->mOptions["limit"];
}
}
$timestamp = wfTimestamp(TS_DB, strtotime(sprintf("-%d days", self::CLOSE_WIKI_DELAY)));
$dbr = WikiFactory::db(DB_SLAVE);
$sth = $dbr->select(array("city_list"), array("city_id", "city_flags", "city_dbname", "city_url", "city_public"), array("city_public" => array(0, -1), "city_flags <> 0 && city_flags <> 32", "city_last_timestamp < '{$timestamp}'"), __METHOD__, $condition);
$this->info('wikis to remove', ['wikis' => $sth->numRows()]);
while ($row = $dbr->fetchObject($sth)) {
/**
* reasonable defaults for wikis and some presets
*/
$hide = false;
$xdumpok = true;
$newFlags = 0;
$dbname = $row->city_dbname;
$cityid = $row->city_id;
$folder = WikiFactory::getVarValueByName("wgUploadDirectory", $cityid);
$cluster = WikiFactory::getVarValueByName("wgDBcluster", $cityid);
/**
* safety check, if city_dbname is not unique die with message
*/
$check = $dbr->selectRow(array("city_list"), array("count(*) as count"), array("city_dbname" => $dbname), __METHOD__, array("GROUP BY" => "city_dbname"));
if ($check->count > 1) {
echo "{$dbname} is not unique. Check city_list and rerun script";
die(1);
}
$this->log("city_id={$row->city_id} city_url={$row->city_url} city_dbname={$dbname} city_flags={$row->city_flags} city_public={$row->city_public}");
/**
* request for dump on remote server (now hardcoded for Iowa)
*/
if ($row->city_flags & WikiFactory::FLAG_HIDE_DB_IMAGES) {
$hide = true;
}
if ($row->city_flags & WikiFactory::FLAG_CREATE_DB_DUMP) {
$this->log("Dumping database on remote host");
list($remote) = explode(":", $this->mTarget, 2);
$script = $hide ? "--script='../extensions/wikia/WikiFactory/Dumps/runBackups.php --both --id={$cityid} --tmp --s3'" : "--script='../extensions/wikia/WikiFactory/Dumps/runBackups.php --both --id={$cityid} --hide --tmp --s3'";
$cmd = array("/usr/wikia/backend/bin/run_maintenance", "--id=177", $script);
$cmd = '/usr/wikia/backend/bin/run_maintenance --id=177 ' . wfEscapeShellArg($script);
$this->log($cmd);
$output = wfShellExec($cmd, $retval);
$xdumpok = empty($retval) ? true : false;
/**
* reset flag
*/
$newFlags = $newFlags | WikiFactory::FLAG_CREATE_DB_DUMP | WikiFactory::FLAG_HIDE_DB_IMAGES;
}
if ($row->city_flags & WikiFactory::FLAG_CREATE_IMAGE_ARCHIVE) {
if ($dbname && $folder) {
$source = $this->tarFiles($folder, $dbname, $cityid);
if ($source) {
$retval = DumpsOnDemand::putToAmazonS3($source, !$hide, MimeMagic::singleton()->guessMimeType($source));
if ($retval > 0) {
$this->log("putToAmazonS3 command failed.");
echo "Can't copy images to remote host. Please, fix that and rerun";
die(1);
} else {
$this->log("{$source} copied to S3 Amazon");
unlink($source);
$newFlags = $newFlags | WikiFactory::FLAG_CREATE_IMAGE_ARCHIVE | WikiFactory::FLAG_HIDE_DB_IMAGES;
}
} else {
/**
* actually it's better to die than remove
* images later without backup
*/
echo "Can't copy images to remote host. Source {$source} is not defined";
}
}
}
if ($row->city_flags & WikiFactory::FLAG_DELETE_DB_IMAGES || $row->city_flags & WikiFactory::FLAG_FREE_WIKI_URL) {
/**
* clear wikifactory tables, condition for city_public should
* be always true there but better safe than sorry
*/
WikiFactory::copyToArchive($row->city_id);
$dbw = WikiFactory::db(DB_MASTER);
$dbw->delete("city_list", array("city_public" => array(0, -1), "city_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from WikiFactory tables");
/**
* remove records from dataware
*/
global $wgExternalDatawareDB;
$datawareDB = wfGetDB(DB_MASTER, array(), $wgExternalDatawareDB);
$datawareDB->delete("pages", array("page_wikia_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from pages table");
/**
* remove images from D.I.R.T.
*/
$datawareDB->delete("image_review", array("wiki_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from image_review table");
$datawareDB->delete("image_review_stats", array("wiki_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from image_review_stats table");
$datawareDB->delete("image_review_wikis", array("wiki_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from image_review_wikis table");
$datawareDB->commit();
/**
* drop database, get db handler for proper cluster
*/
global $wgDBadminuser, $wgDBadminpassword;
$centralDB = empty($cluster) ? "wikicities" : "wikicities_{$cluster}";
/**
* get connection but actually we only need info about host
*/
$local = wfGetDB(DB_MASTER, array(), $centralDB);
$server = $local->getLBInfo('host');
try {
$dbw = new DatabaseMysql($server, $wgDBadminuser, $wgDBadminpassword, $centralDB);
$dbw->begin();
$dbw->query("DROP DATABASE `{$row->city_dbname}`");
$dbw->commit();
$this->log("{$row->city_dbname} dropped from cluster {$cluster}");
} catch (Exception $e) {
$this->log("{$row->city_dbname} database drop failed! {$e->getMessage()}");
$this->info('drop database', ['cluster' => $cluster, 'dbname' => $row->city_dbname, 'exception' => $e, 'server' => $server]);
}
/**
* update search index
*/
$indexer = new Wikia\Search\Indexer();
$indexer->deleteWikiDocs($row->city_id);
$this->log("Wiki documents removed from index");
/**
* there is nothing to set because row in city_list doesn't
* exists
*/
$newFlags = false;
}
/**
* reset flags, if database was dropped and data were removed from
* WikiFactory tables it will return false anyway
*/
if ($newFlags) {
WikiFactory::resetFlags($row->city_id, $newFlags);
}
$this->info('closed', ['cluster' => $cluster, 'city_id' => (int) $cityid, 'dbname' => $dbname]);
/**
* just one?
*/
if ($first) {
break;
}
sleep($sleep);
}
}
function &streamingSlave($db)
{
global $wgDBname;
$stream = new DatabaseMysql($db->mServer, $db->mUser, $db->mPassword, $wgDBname);
$stream->bufferResults(false);
$timeout = 3600 * 24;
$stream->query("SET net_read_timeout={$timeout}");
$stream->query("SET net_write_timeout={$timeout}");
return $stream;
}
public static function run(DatabaseMysql $db, $test = false, $verbose = false, $params)
{
$dbname = $params['dbname'];
// Don't bother getting stats for the video wiki
if ($dbname == 'video151') {
return;
}
// Get total local videos
$sql = 'SELECT COUNT(*) as local_count FROM video_info WHERE premium = 0';
$result = $db->query($sql);
$local_count = 0;
while ($row = $db->fetchObject($result)) {
$local_count = $row->local_count;
}
// Get number of matching videos and total number of matches
$sql = 'SELECT page_id, props
FROM page_wikia_props
WHERE propname = ' . WPP_LVS_SUGGEST;
$result = $db->query($sql);
$num_matching = 0;
$total_matches = 0;
$countedPages = array();
while ($row = $db->fetchObject($result)) {
$info = unserialize($row->props);
$num_matching++;
$total_matches += count($info);
$countedPages[$row->page_id] = 1;
}
$sql = 'SELECT page_id, props
FROM page_wikia_props
WHERE propname = ' . WPP_LVS_STATUS_INFO;
$result = $db->query($sql);
/*
* Constants made available by LicensedVideoSwapHelper
const STATUS_KEEP = 1; // set bit to 1 = kept video
const STATUS_SWAP = 2; // set bit to 1 = swapped video
const STATUS_EXACT = 4; // set bit to 0 = normal swap, 1 = swap with an exact match
const STATUS_SWAPPABLE = 8; // set bit to 1 = video with suggestions
const STATUS_NEW = 16; // set bit to 1 = video with new suggestions
const STATUS_FOREVER = 32; // set bit to 1 = no more matches
*/
$num_keeps = 0;
$num_swaps = 0;
while ($row = $db->fetchObject($result)) {
$info = unserialize($row->props);
if ($info['status'] & LicensedVideoSwapHelper::STATUS_KEEP) {
$num_keeps++;
} else {
if ($info['status'] & LicensedVideoSwapHelper::STATUS_SWAP) {
$num_swaps++;
}
}
// If this page wasn't counted above as having or more suggestions,
// count it here. This can happen if the suggestions get cleared out
// after the video has been kept/swapped
if (!array_key_exists($row->page_id, $countedPages)) {
$num_matching++;
}
}
$url = WikiFactory::DBtoUrl($dbname);
echo "{$dbname},{$url},{$local_count},{$num_matching},{$total_matches},{$num_keeps},{$num_swaps}\n";
}
public static function run(DatabaseMysql $db, $test = false, $verbose = false, $params)
{
$dbname = $params['dbname'];
// Get all pages which have a status record with the swappable
// bit turned on, but do not have a corresponding suggestions record
$sql = "SELECT page_id\n\t\t\t\tFROM page_wikia_props\n\t\t\t\tWHERE propname = " . WPP_LVS_STATUS . "\n\t\t\t\tAND props & " . LicensedVideoSwapHelper::STATUS_SWAPPABLE . " != 0\n\t\t\t\tAND page_id not in (\n\t\t\t\t\tSELECT page_id\n\t\t\t\t\tFROM page_wikia_props\n\t\t\t\t\tWHERE propname = " . WPP_LVS_SUGGEST . ")";
$result = $db->query($sql);
$pagesWithoutSuggestions = array();
while ($row = $db->fetchObject($result)) {
$pagesWithoutSuggestions[] = $row->page_id;
}
// Turn off the swappable bit in the props column for any page_ids found in the previous query
foreach ($pagesWithoutSuggestions as $page_id) {
if (!$test) {
$db->query("UPDATE page_wikia_props SET props=props & ~" . LicensedVideoSwapHelper::STATUS_SWAPPABLE . " WHERE page_id = " . $page_id . " AND propname = " . WPP_LVS_STATUS);
}
if ($verbose) {
echo "Found status record in {$dbname} without suggestion record. Turning off swappable bit for page_id: {$page_id}\n";
}
}
// Get all pages which have a suggestion record, but do not have a status record with the swappable bit turned on
$sql = "SELECT page_id\n\t\t\t\tFROM page_wikia_props\n\t\t\t\tWHERE propname = " . WPP_LVS_SUGGEST . "\n\t\t\t\tAND page_id not in (\n\t\t\t\t\tSELECT page_id\n\t\t\t\t\tFROM page_wikia_props\n\t\t\t\t\tWHERE propname = " . WPP_LVS_STATUS . "\n\t\t\t\t\tAND props & " . LicensedVideoSwapHelper::STATUS_SWAPPABLE . " != 0)";
$result = $db->query($sql);
$suggestionsWithoutSwappableBit = array();
while ($row = $db->fetchObject($result)) {
$suggestionsWithoutSwappableBit[] = $row->page_id;
}
// Turn on the swappable bit in the props column for any page_ids found in the previous query
foreach ($suggestionsWithoutSwappableBit as $page_id) {
// First make sure that a status record actually exists for this video
$result = $db->query("SELECT page_id from page_wikia_props where page_id = {$page_id} and propname = " . WPP_LVS_STATUS);
if (!$test) {
// if not, create it first
if (!$db->fetchObject($result)) {
$db->query("INSERT INTO page_wikia_props (page_id, propname, props) values ({$page_id}, " . WPP_LVS_STATUS . ", " . LicensedVideoSwapHelper::STATUS_SWAPPABLE . ")");
} else {
$db->query("UPDATE page_wikia_props SET props=props | " . LicensedVideoSwapHelper::STATUS_SWAPPABLE . " WHERE page_id = " . $page_id . " AND propname = " . WPP_LVS_STATUS);
}
}
if ($verbose) {
echo "Suggestion record found in {$dbname} without swappable bit turned on in status record. Turning on swappable for {$page_id}\n";
}
}
// Finally, make sure that all pages listed in the page_wikia_props table actually exist in the page table.
// If they do not, delete them.
$result = $db->query("SELECT pp.page_id FROM page_wikia_props pp LEFT JOIN page p ON pp.page_id=p.page_id WHERE p.page_id IS NULL");
$page_ids = array();
while ($row = $db->fetchObject($result)) {
$page_ids[] = $row->page_id;
}
// Send MySQL pages to be deleted in batches of 100
foreach (array_chunk($page_ids, 100) as $chunk) {
if ($verbose) {
echo "Deleted pages found in {$dbname}. Deleting corresponding LVS rows from page_wikia_props\n";
}
if (!$test and !empty($chunk)) {
$db->query("DELETE FROM page_wikia_props WHERE page_id IN (" . implode(",", $chunk) . ") and propname between " . WPP_LVS_STATUS_INFO . " and " . WPP_LVS_STATUS);
}
}
}
/**
* 1. go through all wikis which are marked for closing and check which one
* want to have images packed.
*
* 2. pack images, send them via rsync to target server,
*
* 3. mark in city_list.city_flags that images are sent,
*
* 4. remove images
*
* @access public
*/
public function execute()
{
global $wgUploadDirectory, $wgDBname, $wgSolrIndexer, $IP;
$first = isset($this->mOptions["first"]) ? true : false;
$sleep = isset($this->mOptions["sleep"]) ? $this->mOptions["sleep"] : 1;
$condition = array("ORDER BY" => "city_id");
/**
* if $first is set skip limit checking
*/
if (!$first) {
if (isset($this->mOptions["limit"]) && is_numeric($this->mOptions["limit"])) {
$condition["LIMIT"] = $this->mOptions["limit"];
}
}
$timestamp = wfTimestamp(TS_DB, strtotime(sprintf("-%d days", self::CLOSE_WIKI_DELAY)));
$dbr = WikiFactory::db(DB_SLAVE);
$sth = $dbr->select(array("city_list"), array("city_id", "city_flags", "city_dbname", "city_url", "city_public"), array("city_public" => array(0, -1), "city_flags <> 0 && city_flags <> 32", "city_last_timestamp < '{$timestamp}'"), __METHOD__, $condition);
while ($row = $dbr->fetchObject($sth)) {
/**
* reasonable defaults for wikis and some presets
*/
$hide = false;
$xdumpok = true;
$newFlags = 0;
$dbname = $row->city_dbname;
$folder = WikiFactory::getVarValueByName("wgUploadDirectory", $row->city_id);
$cluster = WikiFactory::getVarValueByName("wgDBcluster", $row->city_id);
/**
* safety check, if city_dbname is not unique die with message
*/
$check = $dbr->selectRow(array("city_list"), array("count(*) as count"), array("city_dbname" => $dbname), __METHOD__, array("GROUP BY" => "city_dbname"));
if ($check->count > 1) {
echo "{$dbname} is not unique. Check city_list and rerun script";
die(1);
}
$this->log("city_id={$row->city_id} city_url={$row->city_url} city_dbname={$dbname} city_flags={$row->city_flags} city_public={$row->city_public}");
/**
* request for dump on remote server (now hardcoded for Iowa)
*/
if ($row->city_flags & WikiFactory::FLAG_HIDE_DB_IMAGES) {
$hide = true;
}
if ($row->city_flags & WikiFactory::FLAG_CREATE_DB_DUMP) {
$this->log("Dumping database on remote host");
list($remote) = explode(":", $this->mTarget, 2);
$cmd = array("SERVER_ID=177", "php", "{$IP}/extensions/wikia/WikiFactory/Dumps/runBackups.php", "--conf /usr/wikia/conf/current/iowa.wiki.factory/LocalSettings.php", "--both", "--id={$row->city_id}");
if ($hide) {
$cmd[] = "--hide";
}
$dump = wfEscapeShellArg("/usr/bin/ssh", $remote, implode(" ", $cmd));
$this->log($dump);
$output = wfShellExec($dump, $retval);
$xdumpok = empty($retval) ? true : false;
/**
* reset flag
*/
$newFlags = $newFlags | WikiFactory::FLAG_CREATE_DB_DUMP | WikiFactory::FLAG_HIDE_DB_IMAGES;
}
if ($row->city_flags & WikiFactory::FLAG_CREATE_IMAGE_ARCHIVE) {
if ($dbname && $folder) {
$source = $this->tarFiles($folder, $dbname);
$target = DumpsOnDemand::getUrl($dbname, "images.tar", $this->mTarget);
if ($hide) {
/**
* different path for hidden dumps
*/
$target = str_replace("dumps", "dumps-hidden", $target);
}
if ($source && $target) {
$cmd = wfEscapeShellArg("/usr/bin/rsync", "-axpr", "--quiet", "--owner", "--group", "--chmod=g+w", $source, escapeshellcmd($target));
$output = wfShellExec($cmd, $retval);
if ($retval > 0) {
$this->log("{$cmd} command failed.");
/**
* creating directory attempt
*/
list($remote, $path) = explode(":", $target, 2);
$mkdir = wfEscapeShellArg("/usr/bin/ssh", $remote, escapeshellcmd("mkdir -p " . dirname($path)));
$output = wfShellExec($mkdir, $retval);
if ($retval == 0) {
$this->log(dirname($path) . " created on {$remote}");
$output = wfShellExec($cmd, $retval);
if ($retval == 0) {
$this->log("{$source} copied to {$target}");
unlink($source);
/**
* reset flag
*/
$newFlags = $newFlags | WikiFactory::FLAG_CREATE_IMAGE_ARCHIVE | WikiFactory::FLAG_HIDE_DB_IMAGES;
}
} else {
/**
* actually it's better to die than remove
* images later without backup
*/
echo "Can't copy images to remote host. Please, fix that and rerun";
die(1);
}
} else {
$this->log("{$source} copied to {$target}");
unlink($source);
$newFlags = $newFlags | WikiFactory::FLAG_CREATE_IMAGE_ARCHIVE | WikiFactory::FLAG_HIDE_DB_IMAGES;
}
} else {
/**
* actually it's better to die than remove
* images later without backup
*/
echo "Can't copy images to remote host. Source {$source} and target {$target} is not defined";
die(1);
}
}
}
if ($row->city_flags & WikiFactory::FLAG_DELETE_DB_IMAGES || $row->city_flags & WikiFactory::FLAG_FREE_WIKI_URL) {
$this->log("removing folder {$folder}");
if (is_dir($wgUploadDirectory)) {
/**
* what should we use here?
*/
$cmd = "rm -rf {$folder}";
wfShellExec($cmd, $retval);
if ($retval) {
/**
* info removing folder was not possible
*/
}
/**
* clear wikifactory tables, condition for city_public should
* be always true there but better safe than sorry
*/
WikiFactory::copyToArchive($row->city_id);
$dbw = WikiFactory::db(DB_MASTER);
$dbw->delete("city_list", array("city_public" => array(0, -1), "city_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from WikiFactory tables");
/**
* remove records from dataware
*/
global $wgExternalDatawareDB;
$datawareDB = wfGetDB(DB_MASTER, array(), $wgExternalDatawareDB);
$datawareDB->delete("pages", array("page_wikia_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from pages table");
/**
* remove images from D.I.R.T.
*/
$datawareDB->delete("image_review", array("wiki_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from image_review table");
$datawareDB->delete("image_review_stats", array("wiki_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from image_review_stats table");
$datawareDB->delete("image_review_wikis", array("wiki_id" => $row->city_id), __METHOD__);
$this->log("{$row->city_id} removed from image_review_wikis table");
$datawareDB->commit();
/**
* drop database, get db handler for proper cluster
*/
global $wgDBadminuser, $wgDBadminpassword;
$centralDB = empty($cluster) ? "wikicities" : "wikicities_{$cluster}";
/**
* get connection but actually we only need info about host
*/
$local = wfGetDB(DB_MASTER, array(), $centralDB);
$server = $local->getLBInfo('host');
$dbw = new DatabaseMysql($server, $wgDBadminuser, $wgDBadminpassword, $centralDB);
$dbw->begin();
$dbw->query("DROP DATABASE `{$row->city_dbname}`");
$dbw->commit();
$this->log("{$row->city_dbname} dropped from cluster {$cluster}");
/**
* update search index
*/
$cmd = sprintf("curl %s -H \"Content-Type: text/xml\" --data-binary '<delete><query>wid:%d</query></delete>' > /dev/null 2> /dev/null", $wgSolrIndexer, $row->city_id);
wfShellExec($cmd, $retval);
$this->log("search index removed from {$wgSolrIndexer}");
/**
* there is nothing to set because row in city_list doesn't
* exists
*/
$newFlags = false;
}
}
/**
* reset flags, if database was dropped and data were removed from
* WikiFactory tables it will return false anyway
*/
if ($newFlags) {
WikiFactory::resetFlags($row->city_id, $newFlags);
}
/**
* just one?
*/
if ($first) {
break;
}
sleep($sleep);
}
}
/**
* Check to see if the comments_index table exists.
*
* @param DatabaseMysql $db the db handle
*/
public static function commentsIndexExists(DatabaseMysql $db)
{
return $db->query("SHOW TABLES LIKE 'comments_index'")->numRows() > 0;
}
/**
* @return int
*/
private function generateUserId()
{
$res = $this->databaseConnection->select("webmaster_user_accounts", array('max(user_id) as maxid'), array(), __METHOD__);
return (int) $res->fetchObject()->maxid + 1;
}
