openqa-db_query_last_use_of_module

#!/usr/bin/env python

"""Helper script to find where a test module I want to change has been run last.

As a test developer I would like to know where the test module I want to
change is triggered so that I could identify good candidates for validation
acceptance criteria.
"""

import argparse
import logging
import subprocess
import sys

logging.basicConfig()
log = logging.getLogger(sys.argv[0] if __name__ == "__main__" else __name__)

MAX_VERBOSE = 4


class CustomFormatter(argparse.ArgumentDefaultsHelpFormatter, argparse.RawDescriptionHelpFormatter):
    """Preserve multi-line __doc__ and provide default arguments in help strings."""


def parse_args() -> argparse.Namespace:
    """Parse the command line: module, age, limit, openQA host, dry-run and verbosity."""
    parser = argparse.ArgumentParser(description=__doc__, formatter_class=CustomFormatter)
    parser.add_argument(
        "-v",
        "--verbose",
        help="Increase verbosity level, specify multiple times to increase verbosity",
        action="count",
        default=1,
    )
    parser.add_argument("--openqa-host", default="openqa.opensuse.org", help="The openQA host to act on")
    parser.add_argument("--module", help="The failing module to look for")
    parser.add_argument("--dry-run", action="store_true", help="Do not do any action on the host")
    parser.add_argument("--limit", type=int, default=30, help="How many results to return")
    parser.add_argument("--age", default="90 days", help="How far to look back in history")
    args = parser.parse_args()
    verbose_to_log = {0: logging.CRITICAL, 1: logging.ERROR, 2: logging.WARNING, 3: logging.INFO, 4: logging.DEBUG}
    logging_level = logging.DEBUG if args.verbose > MAX_VERBOSE else verbose_to_log[args.verbose]
    log.setLevel(logging_level)
    return args


def call(cmds: list[str], *, dry_run: bool = False) -> int:
    """Run cmds, or echo them as simulated when dry_run is set."""
    log.debug("call: %s", cmds)
    return subprocess.call((["echo", "Simulating: "] if dry_run else []) + cmds)


def main() -> None:
    """Query the openQA database over ssh for the latest jobs that ran a module."""
    args = parse_args()
    sql_query = f"select distinct distri,version,flavor,test,arch,machine from jobs where id in (select job_id from job_modules where (name='{args.module}')and t_updated > now() - interval '{args.age}') limit {args.limit};"
    call(
        ["ssh", args.openqa_host, f'sudo -u geekotest psql -t --command="{sql_query}" openqa'],
        dry_run=args.dry_run,
    )


if __name__ == "__main__":
    main()