mass-reference-tests.py

import datetime
import sqlite3
import urllib.request
import json
import sys

pending = set()
with urllib.request.urlopen(
    "https://autopkgtest.ubuntu.com/queued.json"
) as queued_json:
    queued = json.load(queued_json)

    for context in ["ubuntu", "huge"]:
        for arch in queued["arches"]:
            for request in queued["queues"][context]["noble"][arch][
                "requests"
            ]:
                pkg, args = request.split(None, 1)
                args = json.loads(args)
                if args["triggers"] == ["migration-reference/0"]:
                    pending.add((pkg, arch))

c = sqlite3.connect("autopkgtest.db")
print(
    "# created by https://gist.github.com/julian-klode/eb730e8cfa7377eb8dddaca86c1e8143 at",
    datetime.datetime.now(),
)
print("# already pending migration-reference/0 tests =", len(pending))
for package, arch, cmd in c.execute(
    # Directly build a query to run a migration-reference test in the huge queue.
    "SELECT test.package, test.arch, 'run-autopkgtest -s noble --bulk --trigger=migration-reference/0 -a ' || test.arch || ' ' || test.package "
    # We are querying over all packages still in the release pocket (aka in current_version), and any tests and results for them.
    "FROM current_version "
    "JOIN test ON test.package = current_version.package AND test.release = current_version.release "
    "JOIN result ON test.id == result.test_id "
    # We need to have at least one passing test result in noble, otherwise little point trying migration-reference
    "WHERE test.release = 'noble' AND result.exitcode = 0 "
    # Have we run a test for this in noble on any architecture since we started the time_t transition? We don't want to test unrelated stuff just yet
    "      AND test.package IN (SELECT package FROM test JOIN result ON test.id = result.test_id WHERE release = 'noble' AND run_id >= '20240225')"
    # We already have a migration-reference/0 test scheduled after the mass migration (2024-03-28 13:00 UTC)
    "      AND (test.package, test.arch) NOT IN (SELECT package, arch FROM test JOIN result ON test.id = result.test_id WHERE release = 'noble' AND run_id >= '20240328_13' AND triggers = 'migration-reference/0')"
    # Avoid duplicates and get nice filtered output
    "GROUP BY test.package, test.arch ORDER BY test.package, test.arch;"
):
    if (package, arch) not in pending:
        print(cmd)