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