/** * Fixes the bad rows in the comments_index table by marking them with deleted=1. * * @param DatabaseMysql $db the db handle * @param string $dbname the database name */ public function fixCommentsIndex(DatabaseMysql $db, $dbname) { printf("%s: fixing bad comments_index rows...\n", $dbname); $sql = <<<SQL \t\tCREATE TEMPORARY TABLE temporary_bad_comments_index_records AS ( \t\t\tSELECT parent_page_id, first_rev_id, removed, deleted, archived \t\t\tFROM comments_index \t\t\tLEFT JOIN revision ON ( \t\t\t\trevision.rev_id = comments_index.first_rev_id \t\t\t) \t\t\tWHERE comments_index.removed = 0 AND \t\t\t\tcomments_index.deleted = 0 AND \t\t\t\tcomments_index.archived = 0 AND \t\t\t\trevision.rev_id IS NULL \t\t); SQL; $db->query($sql); $row = $db->fetchObject($ret); $sql = <<<SQL \t\tUPDATE comments_index, temporary_bad_comments_index_records \t\t\tSET comments_index.deleted = 1 \t\t\tWHERE comments_index.parent_page_id = temporary_bad_comments_index_records.parent_page_id AND \t\t\t\tcomments_index.first_rev_id = temporary_bad_comments_index_records.first_rev_id; SQL; $db->query($sql); $row = $db->fetchObject($ret); }
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); } } }
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); } } }
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"; }
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"; }