mkpkglists improvements

This overlaid MR changes a couple things, namely the format of the meta package archive and only updating archives when needed.

Update When Needed:

We now use the information_schema.tables AUTO_INCREMENT column to keep track of a "serial number" that can be used to tell us whether we need to update or not.

Metadata Format:

    The SQL logic in this file for package metadata now exactly
    reflects RPC's search logic, without searching for specific
    packages.

    Two command line arguments are available:

        --extended | Include License, Keywords and Groups fields

    When one of these arguments are passed, the script will create
    a packages-meta-ext-v1.json.gz, configured via packagesmetaextfile.

    Archive JSON is in the following format: line-separated package objects
    enclosed in a list:

        [
        {...},
        {...},
        {...}
        ]

    This allows users to stream the archive and process packages
    line-by-line, during the local storage process.

    Signed-off-by: Kevin Morris <kevr@0cost.org>

aurweb/scripts/mkpkglists.py

@@ -4,7 +4,10 @@ import datetime
 import gzip
 import json
 import os
+import sys
 
+from collections import defaultdict
+from decimal import Decimal
 from typing import Tuple
 
 import aurweb.config
@@ -53,6 +56,67 @@ def update_state(state: str, update_time: int) -> None:
         f.write(str(update_time))
 
 
+TYPE_MAP = {
+    "depends": "Depends",
+    "makedepends": "MakeDepends",
+    "checkdepends": "CheckDepends",
+    "optdepends": "OptDepends",
+    "conflicts": "Conflicts",
+    "provides": "Provides",
+    "replaces": "Replaces",
+    "groups": "Groups",
+    "license": "License",
+    "keyword": "Keywords"
+}
+
+
+def get_extended_fields(package_id: int, pkgbase_id: int):
+    query = """
+    SELECT DependencyTypes.Name AS Type,
+    PackageDepends.DepName AS Name,
+    PackageDepends.DepCondition AS Cond
+    FROM PackageDepends
+    LEFT JOIN DependencyTypes
+    ON DependencyTypes.ID = PackageDepends.DepTypeID
+    WHERE PackageDepends.PackageID = ?
+    UNION SELECT RelationTypes.Name AS Type,
+    PackageRelations.RelName AS Name,
+    PackageRelations.RelCondition AS Cond
+    FROM PackageRelations
+    LEFT JOIN RelationTypes
+    ON RelationTypes.ID = PackageRelations.RelTypeID
+    WHERE PackageRelations.PackageID = ?
+    UNION SELECT 'groups' AS Type, Groups.Name, '' AS Cond
+    FROM Groups INNER JOIN PackageGroups
+    ON PackageGroups.PackageID = ?
+    AND PackageGroups.GroupID = Groups.ID
+    UNION SELECT 'license' AS Type, Licenses.Name, '' as Cond
+    FROM Licenses INNER JOIN PackageLicenses
+    ON PackageLicenses.PackageID = ?
+    AND PackageLicenses.LicenseID = Licenses.ID
+    UNION SELECT 'keyword' AS Type, PackageKeywords.Keyword AS Name, '' as Cond
+    FROM PackageKeywords WHERE PackageBaseID = ?
+    """
+
+    conn = aurweb.db.Connection()
+
+    args = [package_id] * 4 + [pkgbase_id]
+    cursor = conn.execute(query, args)
+
+    data = defaultdict(list)
+    data["License"] = []
+    data["Keywords"] = []
+    for result in cursor.fetchall():
+        key = TYPE_MAP.get(result[0])
+        output = result[1]
+        if result[2]:
+            output += result[2]
+        data[key].append(output)
+
+    conn.close()
+    return data
+
+
 def main():
     conn = aurweb.db.Connection()
 
@@ -64,11 +128,23 @@ def main():
     updated, update_time = should_update(packages_state, "Packages")
     if not updated:
         print("Updating Packages...")
-        columns = ("Packages.ID, PackageBaseID, Packages.Name, "
-                   "Version, Description, URL")
+
+        # Query columns; copied from RPC.
+        columns = ("Packages.ID, Packages.Name, "
+                   "PackageBases.ID AS PackageBaseID, "
+                   "PackageBases.Name AS PackageBase, "
+                   "Version, Description, URL, NumVotes, "
+                   "Popularity, OutOfDateTS AS OutOfDate, "
+                   "Users.UserName AS Maintainer, "
+                   "SubmittedTS AS FirstSubmitted, "
+                   "ModifiedTS AS LastModified")
+
+        # Perform query.
         cur = conn.execute(f"SELECT {columns} FROM Packages "
-                           "INNER JOIN PackageBases "
+                           "LEFT JOIN PackageBases "
                            "ON PackageBases.ID = Packages.PackageBaseID "
+                           "LEFT JOIN Users "
+                           "ON PackageBases.MaintainerUID = Users.ID "
                            "WHERE PackageBases.PackagerUID IS NOT NULL")
 
         # Store JSON-data in `output`, which can be reused for the
@@ -76,28 +152,34 @@ def main():
         output = dict()
         with gzip.open(packagesmetafile, "wt") as f:
             """ The output "data" json key points to a list of dictionaries,
-            each representing a single result, filled with column names as
-            keys and column values as values.
+            each representing a single result. All items are produced in
+            the same format that RPC type=search uses.
 
             Example:
                 {
-                    "data": [
-                        {
-                            "ID": 123,
-                            "Name": "package_name",
-                            "PackageBaseID": 234,
-                            "Version": "0.1.1",
-                            "Description": "Some description...",
-                            "URL": "https://some.url"
-                        },
-                        ...
-                    ]
+                    "warning": "...",
+                    "data": [{...}, {...}, ...]
                 }
             """
-            output = [{
-                column[0]: result[i]
-                for i, column in enumerate(cur.description)
-            } for result in cur.fetchall()]
+
+            def is_decimal(column):
+                """ Check if an SQL column is of decimal.Decimal type. """
+                if isinstance(column, Decimal):
+                    return float(column)
+                return column
+
+            output = []
+            for result in cur.fetchall():
+
+                item = {
+                    column[0]: is_decimal(result[i])
+                    for i, column in enumerate(cur.description)
+                }
+                if sys.argv[1] == "--extended":
+                    extended_fields = get_extended_fields(result[0], result[2])
+                    item.update(extended_fields)
+                output.append(item)
+
             json.dump({
                 "warning": ("This is a experimental! It can be removed "
                             "or modified without warning!"),