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; }