[aur-dev] [PATCH 1/4] Add popupdate script
This adds a simple script that can be used to recalculate the number of votes per package. It can be used in a cronjob. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org> --- scripts/popupdate.py | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) create mode 100755 scripts/popupdate.py diff --git a/scripts/popupdate.py b/scripts/popupdate.py new file mode 100755 index 0000000..489cd41 --- /dev/null +++ b/scripts/popupdate.py @@ -0,0 +1,27 @@ +#!/usr/bin/python3 + +import configparser +import mysql.connector +import os + +docroot = os.path.dirname(os.path.realpath(__file__)) + "/../web/html/" + +config = configparser.RawConfigParser() +config.read(os.path.dirname(os.path.realpath(__file__)) + "/../conf/config") + +aur_db_host = config.get('database', 'host') +aur_db_name = config.get('database', 'name') +aur_db_user = config.get('database', 'user') +aur_db_pass = config.get('database', 'password') +aur_db_socket = config.get('database', 'socket') + +db = mysql.connector.connect(host=aur_db_host, user=aur_db_user, + passwd=aur_db_pass, db=aur_db_name, + unix_socket=aur_db_socket, buffered=True) +cur = db.cursor() + +cur.execute("UPDATE PackageBases SET NumVotes = (SELECT COUNT(*) FROM " + + "PackageVotes WHERE PackageVotes.PackageBaseID = PackageBases.ID)") + +db.commit() +db.close() -- 2.4.2
Create a new field Popularity in the PackageBases table. The field is updated by the popupdate script and reflects the popularity of a package. Popularity is the sum of all votes with each vote being weighted with a factor of 0.98 per day since its creation. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org> --- schema/aur-schema.sql | 1 + scripts/popupdate.py | 5 +++++ upgrading/4.0.0.txt | 9 ++++++++- 3 files changed, 14 insertions(+), 1 deletion(-) diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql index 5641d0d..1935cf0 100644 --- a/schema/aur-schema.sql +++ b/schema/aur-schema.sql @@ -102,6 +102,7 @@ CREATE TABLE PackageBases ( Name VARCHAR(255) NOT NULL, CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1, NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0, + Popularity DECIMAL(6,2) UNSIGNED NOT NULL DEFAULT 0, OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL, SubmittedTS BIGINT UNSIGNED NOT NULL, ModifiedTS BIGINT UNSIGNED NOT NULL, diff --git a/scripts/popupdate.py b/scripts/popupdate.py index 489cd41..f4c99ca 100755 --- a/scripts/popupdate.py +++ b/scripts/popupdate.py @@ -23,5 +23,10 @@ cur = db.cursor() 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)) " + + "FROM PackageVotes WHERE PackageVotes.PackageBaseID = " + + "PackageBases.ID AND NOT VoteTS IS NULL)") + db.commit() db.close() diff --git a/upgrading/4.0.0.txt b/upgrading/4.0.0.txt index ed39c9f..aa1e535 100644 --- a/upgrading/4.0.0.txt +++ b/upgrading/4.0.0.txt @@ -31,4 +31,11 @@ CREATE TABLE PackageComaintainers ( ) ENGINE = InnoDB; ---- -5. (optional) Setup cgit to browse the Git repositories via HTTP. +5. Add a field for the package base popularity to the PackageBases table: + +---- +ALTER TABLE PackageBases + ADD COLUMN Popularity DECIMAL(6,2) UNSIGNED NOT NULL DEFAULT 0; +---- + +6. (optional) Setup cgit to browse the Git repositories via HTTP. -- 2.4.2
Display the popularity index (weighted votes) in the package search results. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org> --- web/lib/pkgfuncs.inc.php | 7 +++++-- web/template/pkg_search_form.php | 1 + web/template/pkg_search_results.php | 2 ++ 3 files changed, 8 insertions(+), 2 deletions(-) diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php index a5f24ba..f527578 100644 --- a/web/lib/pkgfuncs.inc.php +++ b/web/lib/pkgfuncs.inc.php @@ -545,8 +545,8 @@ function pkg_search_page($SID="") { $q_select .= "Users.Username AS Maintainer, PackageCategories.Category, Packages.Name, Packages.Version, Packages.Description, - PackageBases.NumVotes, Packages.ID, Packages.PackageBaseID, - PackageBases.OutOfDateTS "; + PackageBases.NumVotes, PackageBases.Popularity, Packages.ID, + Packages.PackageBaseID, PackageBases.OutOfDateTS "; $q_from = "FROM Packages LEFT JOIN PackageBases ON (PackageBases.ID = Packages.PackageBaseID) @@ -663,6 +663,9 @@ function pkg_search_page($SID="") { case 'v': $q_sort .= "NumVotes " . $order . ", "; break; + case 'p': + $q_sort .= "Popularity " . $order . ", "; + break; case 'w': if ($SID) { $q_sort .= "Voted " . $order . ", "; diff --git a/web/template/pkg_search_form.php b/web/template/pkg_search_form.php index 7428250..b4b88bd 100644 --- a/web/template/pkg_search_form.php +++ b/web/template/pkg_search_form.php @@ -21,6 +21,7 @@ $sortby = array( 'n' => __('Name'), 'c' => __('Category'), 'v' => __('Votes'), + 'p' => __('Popularity'), 'w' => __('Voted'), 'o' => __('Notify'), 'm' => __('Maintainer'), diff --git a/web/template/pkg_search_results.php b/web/template/pkg_search_results.php index 7ab3eb9..5289b78 100644 --- a/web/template/pkg_search_results.php +++ b/web/template/pkg_search_results.php @@ -36,6 +36,7 @@ if (!$result): ?> <th><a href="?<?= mkurl('SB=n&SO=' . $SO_next) ?>"><?= __("Name") ?></a></th> <th><?= __("Version") ?></th> <th><a href="?<?= mkurl('SB=v&SO=' . $SO_next) ?>"><?= __("Votes") ?></a></th> + <th><a href="?<?= mkurl('SB=p&SO=' . $SO_next) ?>"><?= __("Popularity") ?></a></th> <?php if ($SID): ?> <th><a href="?<?= mkurl('SB=w&SO=' . $SO_next) ?>"><?= __("Voted") ?></a></th> <th><a href="?<?= mkurl('SB=o&SO=' . $SO_next) ?>"><?= __("Notify") ?></a></th> @@ -55,6 +56,7 @@ if (!$result): ?> <td><a href="<?= htmlspecialchars(get_pkg_uri($row["Name"]), ENT_QUOTES); ?>"><?= htmlspecialchars($row["Name"]) ?></a></td> <td<?php if ($row["OutOfDateTS"]): ?> class="flagged"<?php endif; ?>><?= htmlspecialchars($row["Version"]) ?></td> <td><?= $row["NumVotes"] ?></td> + <td><?= $row["Popularity"] ?></td> <?php if ($SID): ?> <td> <?php if (isset($row["Voted"])): ?> -- 2.4.2
Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org> --- web/html/packages.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/web/html/packages.php b/web/html/packages.php index 76f7563..75a574e 100644 --- a/web/html/packages.php +++ b/web/html/packages.php @@ -89,7 +89,7 @@ if (isset($pkgid)) { } } else { if (!isset($_GET['K']) && !isset($_GET['SB'])) { - $_GET['SB'] = 'v'; + $_GET['SB'] = 'p'; $_GET['SO'] = 'd'; } if (isset($_COOKIE["AURSID"])) { -- 2.4.2
participants (1)
-
Lukas Fleischer