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

@@ -21,20 +21,65 @@ def main():
     pkgbaselist_header = "# AUR package base list, generated on " + datestr
     userlist_header = "# AUR user name list, generated on " + datestr
 
+    columns = ("Packages.ID, PackageBaseID, Packages.Name, "
+               "Version, Description, URL")
+    cur = conn.execute(f"SELECT {columns} FROM Packages "
+                       "INNER JOIN PackageBases "
+                       "ON PackageBases.ID = Packages.PackageBaseID "
+                       "WHERE PackageBases.PackagerUID IS NOT NULL")
+    results = cur.fetchall()
+
     with gzip.open(packagesfile, "w") as f:
         f.write(bytes(pkglist_header + "\n", "UTF-8"))
-        cur = conn.execute("SELECT Packages.Name FROM Packages " +
-                           "INNER JOIN PackageBases " +
-                           "ON PackageBases.ID = Packages.PackageBaseID " +
-                           "WHERE PackageBases.PackagerUID IS NOT NULL")
-        f.writelines([bytes(x[0] + "\n", "UTF-8") for x in cur.fetchall()])
+        f.writelines([bytes(x[2] + "\n", "UTF-8") for x in results])
 
     with gzip.open(packagesmetafile, "wt") as f:
-        cur = conn.execute("SELECT * FROM Packages")
+        """ 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.
+
+        The output "mapping" json key points to a dictionary of Package
+        name key -> "data"-list index pairs. This provides users of
+        the meta archive a way to perform O(1) searches based on a
+        package name, while still providing a sequential list for
+        loopability.
+
+        i = json_data["mapping"]["package_name"]
+        package_data = json_data["data"][i]
+
+        name = package_data.get("Name")
+        version = package_data.get("Version")
+
+        Example:
+            {
+                "data": [
+                    {
+                        "ID": 123,
+                        "Name": "package_name",
+                        "PackageBaseID": 234,
+                        "Version": "0.1.1",
+                        "Description": "Some description...",
+                        "URL": "https://some.url"
+                    },
+                    ...
+                ],
+                "mapping": {
+                    "package_name": 0,
+                    ...
+                }
+            }
+        """
         json.dump({
-            "warning": "This is a experimental! It can be removed or modified without warning!",
-            "columns": [d[0] for d in cur.description],
-            "data": cur.fetchall()
+            "warning": ("This is a experimental! It can be removed "
+                        "or modified without warning!"),
+            "mapping": {
+                result[2]: i
+                for i, result in enumerate(results)
+            },
+            "data": [{
+                column[0]: result[i]
+                for i, column in enumerate(cur.description)
+            } for result in results]
         }, f)
 
     with gzip.open(pkgbasefile, "w") as f: