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