/**
* 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";
}
