diff options
Diffstat (limited to 'bin/maintenance.php')
| -rwxr-xr-x | bin/maintenance.php | 174 |
1 files changed, 121 insertions, 53 deletions
diff --git a/bin/maintenance.php b/bin/maintenance.php index 811421d..8269fe8 100755 --- a/bin/maintenance.php +++ b/bin/maintenance.php @@ -172,8 +172,6 @@ switch ($action) { $total_counts[$row['addon_id']] = ($row['total_count'] > 0) ? $row['total_count'] : 0; } - echo 'Updating seven day counts in `main` ...'."\n"; - foreach ($total_counts as $id => $total_count) { $total_count_update_sql = " UPDATE `addons` SET `totaldownloads`='{$total_count}' WHERE `id`='{$id}' @@ -268,26 +266,22 @@ switch ($action) { echo 'Starting review total updates...'."\n"; $reviews_sql = " - SELECT - versions.addon_id as addon_id, - count(DISTINCT(reviews.user_id)) as count - FROM reviews - INNER JOIN versions ON reviews.version_id = versions.id - WHERE reviews.reply_to IS NULL - GROUP BY versions.addon_id + UPDATE addons AS a + INNER JOIN ( + SELECT + versions.addon_id as addon_id, + COUNT(*) as count + FROM reviews + INNER JOIN versions ON reviews.version_id = versions.id + WHERE reviews.reply_to IS NULL + AND reviews.rating > 0 + GROUP BY versions.addon_id + ) AS c ON (a.id = c.addon_id) + SET a.totalreviews = c.count "; - $reviews_result = $db->query($reviews_sql); - $affected_rows = mysql_num_rows($reviews_result); - - while ($row = mysql_fetch_array($reviews_result)) { - if ($db->query("UPDATE addons SET totalreviews = {$row['count']} WHERE id = {$row['addon_id']}", true)) { - echo "Review total updated to {$row['count']} updated for addon {$row['addon_id']}\n"; - } else { - echo "Review total update failed for addon {$row['addon_id']}\n"; - } - } + $affected_rows = mysql_affected_rows($reviews_result); break; @@ -297,45 +291,65 @@ switch ($action) { * Get average ratings and update addons table. */ case 'ratings': - echo 'Starting average ratings updates...'."\n"; - $avg_sql = "SELECT SUM(rating)/count(rating) as avg from reviews WHERE rating > 0 AND reply_to IS NULL"; - $avg_result = $db->query($avg_sql); - $row= mysql_fetch_array($avg_result); - $_smoothfunc = $row['avg']; - + echo 'Updating average ratings...'."\n"; $rating_sql = " - SELECT - addon_id,(sum(latest_rating)+$_smoothfunc)/(count(latest_rating) + 1) AS avg - FROM ( - SELECT - v.addon_id, - SUBSTRING_INDEX( GROUP_CONCAT( r.rating ORDER BY r.created DESC SEPARATOR ';'), ';', 1 ) AS latest_rating, - SUBSTRING_INDEX( GROUP_CONCAT( r.created ORDER BY r.created DESC SEPARATOR ';'), ';', 1 ) AS latest_created - FROM - reviews r - INNER JOIN - versions v ON r.version_id = v.id - WHERE - r.rating > 0 AND - r.reply_to IS NULL - GROUP BY r.user_id - ORDER BY latest_created DESC - ) AS t1 - GROUP BY addon_id + UPDATE addons AS a + INNER JOIN ( + SELECT + versions.addon_id as addon_id, + AVG(rating) as avg_rating + FROM reviews + INNER JOIN versions ON reviews.version_id = versions.id + WHERE reviews.reply_to IS NULL + AND reviews.rating > 0 + GROUP BY versions.addon_id + ) AS c ON (a.id = c.addon_id) + SET a.averagerating = ROUND(c.avg_rating, 2) "; - $rating_result = $db->query($rating_sql); - $affected_rows = mysql_num_rows($rating_result); + echo 'Updating bayesian ratings...'."\n"; + // get average review count and average rating + $rows = $db->query(" + SELECT AVG(a.cnt) AS avg_cnt + FROM ( + SELECT COUNT(*) AS cnt + FROM reviews AS r + INNER JOIN versions AS v ON (r.version_id = v.id) + WHERE reply_to IS NULL + AND rating > 0 + GROUP BY v.addon_id + ) AS a + "); + $row = mysql_fetch_array($rows); + $avg_num_votes = $row['avg_cnt']; + + $rows = $db->query(" + SELECT AVG(a.addon_rating) AS avg_rating + FROM ( + SELECT AVG(rating) AS addon_rating + FROM reviews AS r + INNER JOIN versions AS v ON (r.version_id = v.id) + WHERE reply_to IS NULL + AND rating > 0 + GROUP BY v.addon_id + ) AS a + "); + $row = mysql_fetch_array($rows); + $avg_rating = $row['avg_rating']; + + // calculate and store bayesian rating + $rating_sql = " + UPDATE addons AS a + SET a.bayesianrating = + IF (a.totalreviews > 0, ( + ( ({$avg_num_votes} * {$avg_rating}) + (a.totalreviews * a.averagerating) ) / + ({$avg_num_votes} + a.totalreviews) + ), 0) + "; + $rating_result = $db->query($rating_sql); - while ($row = mysql_fetch_array($rating_result)) { - $_avg = round($row['avg'],2); - if ($db->query("UPDATE addons SET averagerating = '{$_avg}' WHERE id = {$row['addon_id']}", true)) { - echo "Rating average updated to {$_avg} updated for addon {$row['addon_id']}\n"; - } else { - echo "Rating average update failed for addon {$row['addon_id']}\n"; - } - } + $affected_rows = mysql_affected_rows(); break; @@ -360,6 +374,60 @@ switch ($action) { /** + * Update addon-collection download totals. + */ + case 'addons_collections_total': + echo "Starting addon_collection totals update...\n"; + $addons_collections_total = " + UPDATE + addons_collections AS ac + INNER JOIN ( + SELECT + stats.addon_id as addon_id, + stats.collection_id as collection_id, + SUM(stats.count) AS sum + FROM + stats_addons_collections_counts AS stats + GROUP BY + stats.addon_id, stats.collection_id + ) AS j ON (ac.addon_id = j.addon_id AND + ac.collection_id = j.collection_id) + SET + ac.downloads = j.sum + "; + $db->query($addons_collections_sql); + $affected_rows = mysql_affected_rows(); + break; + + + + /** + * Update collection downloads total. + */ + case 'collections_total': + echo "Starting collection totals update...\n"; + $collections_sql = " + UPDATE + collections AS c + INNER JOIN ( + SELECT + stats.collection_id AS collection_id, + SUM(stats.count) AS sum + FROM + stats_collections_counts AS stats + GROUP BY + stats.collection_id + ) AS j ON (c.id = j.collection_id) + SET + c.downloads = j.sum + "; + $db->query($collections_sql); + $affected_rows = mysql_affected_rows(); + break; + + + + /** * Unknown command. */ default: |