From 4c15f02ecb01e5d31ee575ead72c5b33db731032 Mon Sep 17 00:00:00 2001 From: Lukas Fleischer Date: Tue, 1 Mar 2016 08:45:46 +0100 Subject: popupdate: Avoid NULL entries in the popularity field The popularity field is declared as "NOT NULL" in the database schema. Fix the popularity update query such that it uses a popularity of 0.0 instead of NULL for packages with no votes. Signed-off-by: Lukas Fleischer --- scripts/popupdate.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/scripts/popupdate.py b/scripts/popupdate.py index 2aa8a54..f3ba513 100755 --- a/scripts/popupdate.py +++ b/scripts/popupdate.py @@ -22,7 +22,7 @@ cur.execute("UPDATE PackageBases SET NumVotes = (SELECT COUNT(*) FROM " + "PackageVotes WHERE PackageVotes.PackageBaseID = PackageBases.ID)") cur.execute("UPDATE PackageBases SET Popularity = (" + - "SELECT SUM(POWER(0.98, (UNIX_TIMESTAMP() - VoteTS) / 86400)) " + + "SELECT COALESCE(SUM(POWER(0.98, (UNIX_TIMESTAMP() - VoteTS) / 86400)), 0.0) " + "FROM PackageVotes WHERE PackageVotes.PackageBaseID = " + "PackageBases.ID AND NOT VoteTS IS NULL)") -- cgit v1.2.3-54-g00ecf From 42f5c405c0280b6216028d0513e29c306e388426 Mon Sep 17 00:00:00 2001 From: Lukas Fleischer Date: Tue, 1 Mar 2016 18:30:48 +0100 Subject: aurjson: Remove stray GROUP BY clause The IDs of packages are unique, so there is no need to group search results by package ID. Note that the GROUP BY statement in question was introduced in commit 3447dfc (Support versioned RPC queries, 2014-04-28) for no apparent reason and could even lead to errors in various DBMS. Signed-off-by: Lukas Fleischer --- web/lib/aurjson.class.php | 1 - 1 file changed, 1 deletion(-) diff --git a/web/lib/aurjson.class.php b/web/lib/aurjson.class.php index 7f9b5f2..3bd9179 100644 --- a/web/lib/aurjson.class.php +++ b/web/lib/aurjson.class.php @@ -266,7 +266,6 @@ class AurJSON { "ON Licenses.ID = PackageLicenses.LicenseID " . "WHERE ${where_condition} " . "AND PackageBases.PackagerUID IS NOT NULL " . - "GROUP BY Packages.ID " . "LIMIT $max_results"; } elseif ($this->version >= 2) { if ($this->version == 2 || $this->version == 3) { -- cgit v1.2.3-54-g00ecf