0549a7574450f649cf2444a62876a2db1cca47fe braney Tue Sep 1 12:18:12 2026 -0700 redmineCli: add a sql subcommand for read-only reporting queries Questions that span many tickets took hundreds of API calls. They are one join in SQL. The subcommand runs a query against the Redmine database and prints an aligned table, or TSV, or JSON. redmineCli sql --tables redmineCli sql --describe issues redmineCli sql "select id, subject from issues limit 5" It sends read-only statements only, and multi-statement support is off so a semicolon cannot smuggle in a second statement. Credential columns are never printed. --timeout sets max_statement_time, default 30 seconds, so a bad join does not sit on the server. --limit caps printed rows at 500. Credentials come from ~/.hg.conf. read_api_key now shares one config reader with the new code instead of parsing the file itself. No RM. diff --git src/utils/redmineCli src/utils/redmineCli index bd685c77d9a..ca4f4f4396b 100755 --- src/utils/redmineCli +++ src/utils/redmineCli @@ -1,1539 +1,1815 @@ #!/usr/bin/env python3 """Comprehensive Redmine CLI for the UCSC Genome Browser team. Reads the Redmine API key from ~/.hg.conf (redmine.apiKey=...). Usage: redmineCli show 33571 redmineCli list --project maillists --status open --limit 10 redmineCli create --subject "Bug report" --description "Details here" redmineCli create --project genomebrowser --tracker 11 --subject "New track" \ --description "Details here" --assemblies hg38 # Track tracker requires Assemblies redmineCli build-patch --subject "v500 build patch: ..." --description "..." \ --developer braney --commit-id abc123 --files src/hg/js/hgTracks.js \ --cgis hgTracks --test-case "..." --reviewer hiram \ --target-version 500 --relates 27113 redmineCli comment 33571 --message "Adding a note" redmineCli update 33571 --status 1 --assigned-to lou --note "Reopening" redmineCli update 33571 --description-file newDescription.txt redmineCli update 33571 --release-log-url "../cgi-bin/hgTrackUi?db=hg38&g=myTrack" redmineCli update 33571 --custom-field 46="some value" redmineCli attach 33571 screenshot.png --note "See attached" redmineCli note 20460 85 # show note-85 from ticket 20460 redmineCli relate 10316 15336 30368 # relate tickets to each other redmineCli related 34446 # list a ticket's related tickets redmineCli related 34446 --add 12345 6789 # add relations to a ticket redmineCli watch 37339 lou braney # add watchers to a ticket redmineCli users # list user names and IDs + redmineCli sql --tables # list Redmine database tables + redmineCli sql --describe issues # show one table's columns + redmineCli sql "select id, subject from issues where project_id=1 limit 5" + redmineCli sql --file report.sql --tsv # run a saved query, tab-separated """ import argparse +import decimal import json import mimetypes import os import re import sys import tempfile import urllib.error import urllib.parse import urllib.request -from datetime import datetime +from datetime import date, datetime # --------------------------------------------------------------------------- # Constants # --------------------------------------------------------------------------- DEFAULT_REDMINE = "https://redmine.gi.ucsc.edu" DEFAULT_PROJECT = "maillists" TRACKER_MLQ = 7 TRACKER_BUILD_PATCH = 36 PRIORITY_UNPRIORITIZED = 12 PRIORITY_URGENT = 6 STATUS_NEW = 1 PROJECT_GB = "genomebrowser" +# --- Read-only SQL against the Redmine database ---------------------------- +# Credentials come from ~/.hg.conf: redmine.db.user and redmine.db.password if +# present, otherwise db.user and db.password. redmine.db.host, redmine.db.name +# and redmine.db.port are optional and default to the constants below. + +DEFAULT_DB_HOST = "redmine.gi.ucsc.edu" +DEFAULT_DB_NAME = "redmine" +DEFAULT_DB_PORT = 3306 + +# Rows printed before output is cut off. --limit 0 turns the cap off. +SQL_DEFAULT_LIMIT = 500 + +# Seconds before the server kills a query. These run against the live +# production database, so a bad join must not sit there holding resources. +SQL_MAX_STATEMENT_TIME = 30 + +# Statement verbs cmd_sql will send. This is a guard rail against a typo, not +# a security boundary. The password on disk would let any other client run the +# same statement, so read-only is enforced by granting the account SELECT and +# nothing else. +SQL_ALLOWED_VERBS = ("select", "show", "describe", "desc", "explain", "with") + +# The hgcat grant covers the whole redmine schema, so these are readable. The +# checks below keep a careless query from putting credentials on a terminal or +# into a log. They are not a security boundary: the same account can read the +# same rows from any other MySQL client. --allow-sensitive turns them off. +# +# Tables that hold nothing but credentials. Matched against the query text. +SQL_SECRET_TABLES = ("tokens",) + +# Columns never worth printing, wherever they are selected from. Checked +# against the result columns, so "select * from users" is caught too. +SQL_SECRET_COLUMNS = ("hashed_password", "salt", "twofa_scheme", + "twofa_totp_key", "twofa_totp_last_used_at") + # Redmine display name of the build-meister account. QA assigns a Build Patch # ticket to this user to hand it over for patching; patch-queue gates on it. BUILD_MEISTER_NAME = "Build Meister" # Name -> Redmine tracker ID mapping (case-insensitive lookup in resolve_tracker) TRACKER_IDS = { "bug": 21, "feature": 23, "track": 11, "hub": 45, "data sets": 46, "datasets": 46, "to do": 10, "todo": 10, "docs": 25, "assembly": 24, "process": 12, "meeting": 28, "cr": 26, "cgi build": 33, "mlq": 7, "mlq off list": 15, "suggestion box": 44, "github": 48, "information": 35, "info": 35, "build patch": 36, "release": 47, "housekeeping": 49, } # Name -> Redmine status ID mapping (case-insensitive lookup in resolve_status) STATUS_IDS = { "new": 1, "looking for dev": 37, "snoozed": 34, "limbo": 36, "researching/exploratory": 35, "researching": 35, "exploratory": 35, "masked 2bit file": 29, "initial sequence": 25, "minimal browser": 26, "docs in progress": 27, "on deck": 33, "in progress": 2, "stalled": 13, "qa ready": 10, "qa": 10, "available": 30, "loaded": 8, "resolved": 3, "written": 15, "reviewing": 11, "approved": 16, "bounced": 24, "feedback": 4, "patched": 22, "cgi-ready": 20, "cgi-ready-open-issues": 21, "hibernating": 32, "preview1": 17, "preview2": 18, "final build": 19, "rejected": 6, "verified": 23, "released": 12, "closed": 5, "reopened": 31, } CF_CATEGORY = 28 CF_EMAIL = 40 CF_MLM = 9 # Track ticket custom fields CF_RELEASE_LOG_TEXT = 48 CF_RELEASE_LOG_URL = 46 CF_RELEASED_TO_RR = 47 CF_FILE_LIST = 43 CF_TABLE_LIST = 44 CF_ASSEMBLIES = 2 # Build Patch ticket custom fields (all but Post-mortem are required by the tracker) CF_DEVELOPER = 22 CF_COMMIT_ID = 23 CF_FILES_CHANGED = 24 CF_CGIS_RETEST = 25 CF_TEST_CASE = 27 CF_CODE_REVIEWER = 31 CF_POST_MORTEM = 61 # Name -> Redmine user ID mapping (short names and full names) USER_IDS = { "ana": 174, "ana benet": 174, "angie": 34, "angie hinrichs": 34, "ann": 3, "ann zweig": 3, "bob": 45, "bob kuhn": 45, "blee": 122, "brian lee": 122, "braney": 31, "brian raney": 31, "build": 197, "build meister": 197, "cath": 155, "cath tyner": 155, "charlie": 186, "charlie vaske": 186, "chin": 25, "chin li": 25, "chris": 152, "chris eisenhart": 152, "christopher": 156, "christopher lee": 156, "clay": 161, "clay fischer": 161, "cricket": 29, "cricket sloan": 29, "daniel": 172, "daniel schmelter": 172, "dev": 157, "dev team": 157, "donna": 4, "donna karolchik": 4, "erich": 52, "erich weiler": 52, "galt": 28, "galt barber": 28, "gautomation": 195, "genome automation": 195, "gadmin": 71, "genome browser admin": 71, "gerardo": 179, "gerardo perez": 179, "gera": 179, "haifang": 165, "haifang telc": 165, "hiram": 24, "hiram clawson": 24, "jairo": 163, "jairo navarro": 163, "jason": 180, "jason fernandes": 180, "jeltje": 184, "jeltje van baren": 184, "jim": 44, "jim kent": 44, "johannes": 190, "johannes birgmeier": 190, "jonathan": 142, "jonathan casper": 142, "jorge": 5, "jorge garcia": 5, "kate": 33, "kate rosenbloom": 33, "lou": 171, "lou nassar": 171, "marc": 183, "marc perry": 183, "markd": 7, "mark diekhans": 7, "matt": 150, "matt speir": 150, "max": 100, "max haeussler": 100, "melissa": 27, "melissa cline": 27, "pauline": 16, "pauline fujita": 16, "qa": 99, "qa team": 99, "rachel": 41, "rachel harte": 41, "ward": 196, "ward en": 196, } ATTRIBUTION = "**From Claude:**\n\n" # --------------------------------------------------------------------------- # Shared helpers # --------------------------------------------------------------------------- _BOOL_TRUE = {"1", "true", "yes", "on"} _BOOL_FALSE = {"0", "false", "no", "off"} def _validate_bool(value, field_name): """Validate a checkbox custom-field value; return canonical "0" or "1".""" norm = str(value).strip().lower() if norm in _BOOL_TRUE: return "1" if norm in _BOOL_FALSE: return "0" sys.exit(f"Error: {field_name}: expected one of " f"0/1/true/false/yes/no/on/off, got: {value!r}") # CF ID -> (validator, friendly name). Used both for named flags and to gate # the --custom-field ID=VALUE escape hatch when ID matches a known typed field. FIELD_VALIDATORS = { CF_RELEASED_TO_RR: (_validate_bool, "Released To RR"), } -def read_api_key(conf_path="~/.hg.conf"): - """Read redmine.apiKey from ~/.hg.conf.""" +def read_conf(conf_path="~/.hg.conf"): + """Parse a key=value config file into a dict, skipping comments and blanks.""" conf_path = os.path.expanduser(conf_path) + values = {} with open(conf_path) as f: for line in f: line = line.strip() - if line.startswith("redmine.apiKey="): - return line.split("=", 1)[1] - sys.exit("Error: redmine.apiKey not found in " + conf_path) + if not line or line.startswith("#") or "=" not in line: + continue + key, value = line.split("=", 1) + values[key.strip()] = value.strip() + return values + + +def read_api_key(conf_path="~/.hg.conf"): + """Read redmine.apiKey from ~/.hg.conf.""" + api_key = read_conf(conf_path).get("redmine.apiKey") + if not api_key: + sys.exit("Error: redmine.apiKey not found in " + os.path.expanduser(conf_path)) + return api_key def api_get(base_url, path, api_key): """GET a JSON endpoint from Redmine.""" url = base_url.rstrip("/") + path req = urllib.request.Request(url) req.add_header("X-Redmine-API-Key", api_key) req.add_header("Accept", "application/json") try: with urllib.request.urlopen(req, timeout=30) as resp: return json.loads(resp.read()) except urllib.error.HTTPError as e: body = e.read().decode("utf-8", errors="replace")[:500] sys.exit(f"Error: HTTP {e.code} GET {url}: {body}") except urllib.error.URLError as e: sys.exit(f"Error: {e.reason} connecting to {url}") def api_post(base_url, path, api_key, data): """POST JSON to Redmine. Returns parsed JSON response (or None if empty).""" url = base_url.rstrip("/") + path body = json.dumps(data).encode("utf-8") req = urllib.request.Request(url, data=body, method="POST") req.add_header("X-Redmine-API-Key", api_key) req.add_header("Content-Type", "application/json") try: with urllib.request.urlopen(req, timeout=30) as resp: raw = resp.read() return json.loads(raw) if raw else None except urllib.error.HTTPError as e: resp_body = e.read().decode("utf-8", errors="replace")[:500] sys.exit(f"Error: HTTP {e.code} POST {url}: {resp_body}") except urllib.error.URLError as e: sys.exit(f"Error: {e.reason} connecting to {url}") def api_put(base_url, path, api_key, data): """PUT JSON to Redmine. Returns True on success.""" url = base_url.rstrip("/") + path body = json.dumps(data).encode("utf-8") req = urllib.request.Request(url, data=body, method="PUT") req.add_header("X-Redmine-API-Key", api_key) req.add_header("Content-Type", "application/json") try: with urllib.request.urlopen(req, timeout=30) as resp: return True except urllib.error.HTTPError as e: resp_body = e.read().decode("utf-8", errors="replace")[:500] sys.exit(f"Error: HTTP {e.code} PUT {url}: {resp_body}") except urllib.error.URLError as e: sys.exit(f"Error: {e.reason} connecting to {url}") def api_upload(base_url, api_key, filename, file_data): """Upload binary file to Redmine, returns upload token.""" encoded_name = urllib.parse.quote(filename) url = f"{base_url.rstrip('/')}/uploads.json?filename={encoded_name}" req = urllib.request.Request(url, data=file_data, method="POST") req.add_header("X-Redmine-API-Key", api_key) req.add_header("Content-Type", "application/octet-stream") try: with urllib.request.urlopen(req, timeout=60) as resp: return json.loads(resp.read())["upload"]["token"] except urllib.error.HTTPError as e: resp_body = e.read().decode("utf-8", errors="replace")[:500] sys.exit(f"Error: HTTP {e.code} uploading {filename}: {resp_body}") except urllib.error.URLError as e: sys.exit(f"Error: {e.reason} uploading {filename}") def format_date(iso_str): """Format an ISO date string nicely.""" dt = datetime.fromisoformat(iso_str.replace("Z", "+00:00")) return dt.strftime("%Y-%m-%d %H:%M UTC") def redmine_textile_to_md(text): """Convert common Redmine textile/wiki markup to Markdown.""" if not text: return "" text = re.sub(r'(?<!\w)\*(\S.*?\S)\*(?!\w)', r'**\1**', text) text = re.sub(r'(?<!\w)_(\S.*?\S)_(?!\w)', r'*\1*', text) text = re.sub(r'@([^@\n]+)@', r'`\1`', text) text = re.sub(r'<pre>\s*', '\n```\n', text) text = re.sub(r'\s*</pre>', '\n```\n', text) for i in range(1, 7): text = re.sub(rf'^h{i}\.\s*', '#' * i + ' ', text, flags=re.MULTILINE) text = re.sub(r'!([^!\n]+\.(png|jpg|jpeg|gif))!', r'', text, flags=re.IGNORECASE) text = re.sub(r'"([^"]+)":(\S+)', r'[\1](\2)', text) return text def resolve_user(name_or_id): """Resolve a user name or numeric ID to a Redmine user ID.""" if name_or_id.isdigit(): return int(name_or_id) key = name_or_id.lower().strip() if key in USER_IDS: return USER_IDS[key] sys.exit(f"Error: unknown user '{name_or_id}'. " "Run 'redmineCli users' to see available names and IDs.") def resolve_status(name_or_id): """Resolve a status name to a Redmine status ID. Accepts name or numeric ID.""" if str(name_or_id).isdigit(): return int(name_or_id) key = str(name_or_id).lower().strip() if key in STATUS_IDS: return STATUS_IDS[key] sys.exit(f"Error: unknown status '{name_or_id}'. Known statuses: " + ", ".join(sorted(k for k in STATUS_IDS if " " in k or not any( k2 != k and STATUS_IDS[k2] == STATUS_IDS[k] for k2 in STATUS_IDS)))) def resolve_version(name_or_id, project_id, base_url, api_key): """Resolve a version name or numeric ID to a Redmine fixed_version_id. Name lookup is tried first (case-insensitive, exact match preferred, else unique substring). Only falls through to treating the input as a literal ID if it is purely digits and no name matched. This matters because Redmine version names are often numeric (e.g. "497") and differ from their internal IDs. """ key = str(name_or_id).lower().strip() data = api_get(base_url, f"/projects/{project_id}/versions.json", api_key) versions = data.get("versions", []) for v in versions: if v["name"].lower() == key: return v["id"] matches = [v for v in versions if key in v["name"].lower()] if len(matches) == 1: return matches[0]["id"] if len(matches) > 1: names = ", ".join(v["name"] for v in matches) sys.exit(f"Error: ambiguous version '{name_or_id}': matches {names}") if str(name_or_id).isdigit(): return int(name_or_id) all_names = ", ".join(v["name"] for v in versions) or "(none)" sys.exit(f"Error: unknown version '{name_or_id}'. " f"Available for project {project_id}: {all_names}") def resolve_tracker(name_or_id): """Resolve a tracker name to a Redmine tracker ID. Accepts name or numeric ID.""" if str(name_or_id).isdigit(): return int(name_or_id) key = str(name_or_id).lower().strip() if key in TRACKER_IDS: return TRACKER_IDS[key] sys.exit(f"Error: unknown tracker '{name_or_id}'. Known trackers: " + ", ".join(sorted(set(TRACKER_IDS.keys())))) def prepend_attribution(text): """Prepend 'From Claude:' attribution to text for write operations. Idempotent: if the text already begins with a 'From Claude:' attribution line (e.g. '**From Claude:**', '***From Claude:***'), return it unchanged so the header is not duplicated when Claude models include it in the body. """ if text and re.match(r'^\s*\*+\s*From Claude:?\s*\*+', text, re.IGNORECASE): return text return ATTRIBUTION + text def strip_emoji(text): """Strip 4-byte Unicode (emoji) that Redmine's MySQL may reject.""" if not text: return text return re.sub(r'[\U00010000-\U0010FFFF]', '', text) def read_text_input(direct, from_file): """Read text from --message/--description or --message-file/--description-file.""" if from_file: if from_file == "-": return sys.stdin.read() with open(from_file) as f: return f.read() return direct def make_url(base_url, ticket_id): """Build the web URL for a ticket.""" return f"{base_url.rstrip('/')}/issues/{ticket_id}" def format_details(details): """Format journal detail changes (status changes, assignments, etc.).""" lines = [] for d in details: prop = d.get("property", "") name = d.get("name", "") old = d.get("old_value", "") new = d.get("new_value", "") if prop == "attr": if name == "status_id": lines.append(f" - Status changed: {old} -> {new}") elif name == "assigned_to_id": lines.append(f" - Assignee changed: {old} -> {new}") elif name == "done_ratio": lines.append(f" - Progress: {old}% -> {new}%") else: lines.append(f" - {name}: {old} -> {new}") elif prop == "attachment": lines.append(f" - Attached: {new}") return "\n".join(lines) # --------------------------------------------------------------------------- # Subcommand: show # --------------------------------------------------------------------------- def cmd_show(args): """Display a single ticket in Markdown.""" data = api_get(args.base_url, f"/issues/{args.ticket_id}.json?include=journals,attachments", args.api_key) issue = data["issue"] attachments = {a["id"]: a for a in issue.get("attachments", [])} attach_by_name = {a["filename"]: a for a in issue.get("attachments", [])} dl_dir = None if args.images or args.download_all: dl_dir = tempfile.mkdtemp(prefix=f"redmine_{args.ticket_id}_") print(f"<!-- Attachments downloaded to: {dl_dir} -->", file=sys.stderr) def resolve_images(text): if not text: return text def replace_img(m): fname = m.group(1) if fname in attach_by_name: a = attach_by_name[fname] if dl_dir: local = os.path.join(dl_dir, fname) if not os.path.exists(local): download_file(a["content_url"], local, args.api_key) print(f" Downloaded: {local}", file=sys.stderr) return f"" else: return f"" return m.group(0) return re.sub(r'!\[image\]\(([^)]+\.(png|jpg|jpeg|gif))\)', replace_img, text, flags=re.IGNORECASE) out = [] out.append(f"# #{issue['id']}: {issue['subject']}") out.append("") out.append(f"- **Project:** {issue['project']['name']}") out.append(f"- **Tracker:** {issue['tracker']['name']}") out.append(f"- **Status:** {issue['status']['name']}") out.append(f"- **Priority:** {issue['priority']['name']}") out.append(f"- **Author:** {issue['author']['name']}") if issue.get("assigned_to"): out.append(f"- **Assigned to:** {issue['assigned_to']['name']}") if issue.get("fixed_version"): out.append(f"- **Target version:** {issue['fixed_version']['name']}") out.append(f"- **Created:** {format_date(issue['created_on'])}") out.append(f"- **Updated:** {format_date(issue['updated_on'])}") if issue.get("closed_on"): out.append(f"- **Closed:** {format_date(issue['closed_on'])}") out.append(f"- **URL:** {make_url(args.base_url, issue['id'])}") # Custom fields for cf in issue.get("custom_fields", []): if cf.get("value"): out.append(f"- **{cf['name']}:** {cf['value']}") out.append("") if attachments: out.append("## Attachments") out.append("") for a in issue["attachments"]: out.append(f"- [{a['filename']}]({a['content_url']}) " f"({a['filesize']} bytes, {a['author']['name']}, " f"{format_date(a['created_on'])})") out.append("") # Related tickets (shown by default; --no-related suppresses the extra API calls). if not getattr(args, "no_related", False): rel_lines = related_lines(args.base_url, args.ticket_id, args.api_key) if rel_lines: out.append("## Related tickets") out.append("") out.extend(rel_lines) out.append("") out.append("## Description") out.append("") desc = redmine_textile_to_md(issue.get("description", "")) desc = resolve_images(desc) out.append(desc) out.append("") journals = issue.get("journals", []) if journals: out.append("---") out.append("## Discussion") out.append("") for j in journals: notes = j.get("notes", "") details = j.get("details", []) if not notes and not details: continue user = j["user"]["name"] date = format_date(j["created_on"]) out.append(f"### {user} — {date}") out.append("") if details: detail_text = format_details(details) if detail_text: out.append(detail_text) out.append("") if notes: md_notes = redmine_textile_to_md(notes) md_notes = resolve_images(md_notes) out.append(md_notes) out.append("") out.append("---") out.append("") if dl_dir: for a in issue["attachments"]: is_image = a.get("content_type", "").startswith("image/") if args.download_all or is_image: local = os.path.join(dl_dir, a["filename"]) if not os.path.exists(local): download_file(a["content_url"], local, args.api_key) print(f" Downloaded: {local}", file=sys.stderr) print("\n".join(out)) def download_file(url, dest_path, api_key): """Download a file with API key auth.""" req = urllib.request.Request(url) req.add_header("X-Redmine-API-Key", api_key) with urllib.request.urlopen(req, timeout=30) as resp: with open(dest_path, "wb") as f: f.write(resp.read()) # --------------------------------------------------------------------------- # Subcommand: list # --------------------------------------------------------------------------- def cmd_patch_queue(args): """Report which Build Patch tickets are ready for the build meister to patch. This is the machine-readable gate behind `autoBuild.sh patchtickets`. It encodes the handoff point in the build-patch process: a second developer reviews, QA tests on hgwdev and sets the ticket to Approved, then QA assigns the build meister. So "ready to patch" means status Approved AND assigned to the build meister, for the release being built, with a usable commit hash. Code Review Status is reported but NOT enforced. In practice QA approves and hands over tickets whose Code Review Status was never moved off New, so blocking on it would reject nearly everything. It comes back as a warning instead, which is the useful behavior: the patch still runs, and the inconsistency is visible. TSV output is one row per ticket: verdict, id, comma-separated commit hashes, subject, notes -- notes LAST because it is the only field that is routinely empty, and a tab-delimited reader that treats tabs as whitespace (bash `read` does) collapses an empty field in the middle and shifts everything after it. Verdict is ELIGIBLE or BLOCKED. Commit hashes are only lifted out of the Commit ID field here -- whether they exist in git, are merge commits, or are already on the branch is git's business, and autoBuild.sh/cherryPickCommits.csh check all three before applying. """ params = { "project_id": PROJECT_GB, "tracker_id": TRACKER_BUILD_PATCH, "status_id": args.status, "limit": str(args.limit), "sort": "id:asc", } query = urllib.parse.urlencode(params) data = api_get(args.base_url, f"/issues.json?{query}", args.api_key) issues = data.get("issues", []) rows = [] if args.tickets: wanted = [] for t in args.tickets: try: wanted.append(int(str(t).lstrip("#"))) except ValueError: sys.exit(f"error: '{t}' is not a ticket number") found = {i["id"] for i in issues} issues = [i for i in issues if i["id"] in set(wanted)] for missing in [w for w in wanted if w not in found]: rows.append(("BLOCKED", missing, "", f"not an open Build Patch ticket in {PROJECT_GB} " f"(status filter: {args.status})", "")) target = args.target_version.lstrip("vV") if args.target_version else None for iss in issues: cf = {c["name"]: (c.get("value") or "") for c in iss.get("custom_fields", [])} version = (iss.get("fixed_version") or {}).get("name", "") status = iss["status"]["name"] assignee = (iss.get("assigned_to") or {}).get("name", "") review = cf.get("Code Review Status", "") commits = re.findall(r"\b[0-9a-f]{7,40}\b", cf.get("Commit ID", "").lower()) blockers = [] if target and version.lstrip("vV") != target: blockers.append("target version is '%s', not %s" % (version or "unset", target)) if status != "Approved": blockers.append("status is '%s', not Approved" % status) if assignee != BUILD_MEISTER_NAME: blockers.append("assigned to '%s', not %s" % (assignee or "nobody", BUILD_MEISTER_NAME)) if not commits: blockers.append("Commit ID field has no usable commit hash (value: '%s')" % cf.get("Commit ID", "")) if blockers: rows.append(("BLOCKED", iss["id"], ",".join(commits), "; ".join(blockers), iss["subject"])) else: note = "" if review != "Approved": note = ("WARNING: Code Review Status is '%s', not Approved" % (review or "unset")) rows.append(("ELIGIBLE", iss["id"], ",".join(commits), note, iss["subject"])) if args.tsv: for verdict, tid, commits, note, subject in rows: print("\t".join([verdict, str(tid), commits, subject, note])) return if not rows: print("No Build Patch tickets matched.") return out = ["| Verdict | # | Commit(s) | Subject | Notes |", "|---------|---|-----------|---------|-------|"] for verdict, tid, commits, note, subject in rows: out.append("| %s | %s | %s | %s | %s |" % (verdict, tid, commits or "—", subject[:50].replace("|", "\\|") or "—", note.replace("|", "\\|") or "—")) eligible = sum(1 for r in rows if r[0] == "ELIGIBLE") out.append("") out.append("%d of %d ready to patch%s." % (eligible, len(rows), " for v%s" % target if target else "")) print("\n".join(out)) def cmd_list(args): """List/search tickets with filters.""" params = { "project_id": args.project, "limit": str(args.limit), "offset": str(args.offset), "sort": args.sort, } if args.status: params["status_id"] = args.status if args.assigned_to: if args.assigned_to.lower() == "me": params["assigned_to_id"] = "me" else: params["assigned_to_id"] = str(resolve_user(args.assigned_to)) if args.tracker: params["tracker_id"] = resolve_tracker(args.tracker) if args.search: params["subject"] = f"~{args.search}" if args.target_version: params["fixed_version_id"] = resolve_version( args.target_version, args.project, args.base_url, args.api_key) query = urllib.parse.urlencode(params) data = api_get(args.base_url, f"/issues.json?{query}", args.api_key) issues = data.get("issues", []) total = data.get("total_count", 0) if not issues: print("No issues found.") return out = [] out.append("| # | Status | Assignee | Subject |") out.append("|---|--------|----------|---------|") for iss in issues: tid = iss["id"] status = iss["status"]["name"] assignee = iss.get("assigned_to", {}).get("name", "—") subject = iss["subject"][:60].replace("|", "\\|") out.append(f"| {tid} | {status} | {assignee} | {subject} |") out.append("") start = args.offset + 1 end = args.offset + len(issues) out.append(f"{total} issues total (showing {start}-{end})") print("\n".join(out)) # --------------------------------------------------------------------------- # Subcommand: create # --------------------------------------------------------------------------- def cmd_create(args): """Create a new Redmine ticket.""" description = read_text_input(args.description, args.description_file) if not description: sys.exit("Error: --description or --description-file is required") description = strip_emoji(prepend_attribution(description)) subject = strip_emoji(args.subject) issue_data = { "issue": { "project_id": args.project, "subject": subject, "description": description, "tracker_id": resolve_tracker(args.tracker), "priority_id": args.priority, "status_id": resolve_status(args.status), } } custom_fields = [] if args.category: custom_fields.append({"id": CF_CATEGORY, "value": args.category}) if args.email: custom_fields.append({"id": CF_EMAIL, "value": args.email}) if args.mlm: custom_fields.append({"id": CF_MLM, "value": args.mlm}) if getattr(args, "assemblies", None) is not None: custom_fields.append({"id": CF_ASSEMBLIES, "value": args.assemblies}) for cf_spec in (getattr(args, "custom_field", None) or []): if "=" not in cf_spec: sys.exit(f"Error: --custom-field must be ID=VALUE, got: {cf_spec}") cf_id, cf_val = cf_spec.split("=", 1) if not cf_id.isdigit(): sys.exit(f"Error: custom field ID must be numeric, got: {cf_id}") cf_id = int(cf_id) if cf_id in FIELD_VALIDATORS: validator, name = FIELD_VALIDATORS[cf_id] cf_val = validator(cf_val, f"--custom-field {cf_id} ({name})") custom_fields.append({"id": cf_id, "value": cf_val}) if custom_fields: issue_data["issue"]["custom_fields"] = custom_fields if args.assigned_to: issue_data["issue"]["assigned_to_id"] = resolve_user(args.assigned_to) result = api_post(args.base_url, "/issues.json", args.api_key, issue_data) ticket_id = result["issue"]["id"] print(f"Created #{ticket_id}: {make_url(args.base_url, ticket_id)}") # --------------------------------------------------------------------------- # Subcommand: build-patch # --------------------------------------------------------------------------- def cmd_build_patch(args): """Create a Build Patch ticket with every field the process requires. Encodes the team's Build Patch process so the required fields cannot be forgotten: GB project, Build Patch tracker, Urgent priority, New status, no assignee (QA claims it), and the five required custom fields (Commit ID, Files Changed, CGIs to retest, Test Case, Suggested Code Reviewer). Relates the ticket to the bug it fixes and, unless --no-default-watchers is given, adds the QA Team, the build meister, and the suggested code reviewer as watchers. """ description = read_text_input(args.description, args.description_file) if not description: sys.exit("Error: --description or --description-file is required") description = strip_emoji(prepend_attribution(description)) custom_fields = [ {"id": CF_DEVELOPER, "value": str(resolve_user(args.developer))}, {"id": CF_COMMIT_ID, "value": args.commit_id}, {"id": CF_FILES_CHANGED, "value": args.files}, {"id": CF_CGIS_RETEST, "value": args.cgis}, {"id": CF_TEST_CASE, "value": args.test_case}, {"id": CF_CODE_REVIEWER, "value": str(resolve_user(args.reviewer))}, ] if args.post_mortem: custom_fields.append({"id": CF_POST_MORTEM, "value": args.post_mortem}) issue_data = { "issue": { "project_id": args.project, "subject": strip_emoji(args.subject), "description": description, "tracker_id": TRACKER_BUILD_PATCH, "priority_id": PRIORITY_URGENT, "status_id": STATUS_NEW, "fixed_version_id": resolve_version( args.target_version, args.project, args.base_url, args.api_key), "custom_fields": custom_fields, } } result = api_post(args.base_url, "/issues.json", args.api_key, issue_data) ticket_id = result["issue"]["id"] print(f"Created Build Patch #{ticket_id}: {make_url(args.base_url, ticket_id)}") # Relate to the bug(s) this patch fixes. for bug_id in (args.relates or []): data = {"relation": {"issue_to_id": int(bug_id), "relation_type": "relates"}} try: api_post(args.base_url, f"/issues/{ticket_id}/relations.json", args.api_key, data) print(f" Related #{ticket_id} <-> #{bug_id}") except SystemExit as e: if "422" in str(e): print(f" Already related: #{ticket_id} <-> #{bug_id}") else: raise # Watchers: QA Team + build meister + the suggested code reviewer by # default, plus any extras. The process requires QA Team on every Build # Patch from the start, and the reviewer has to set Code Review Status # before QA can test, so they need the notification too. watchers = [] if args.no_default_watchers else ["qa", "build", args.reviewer] watchers += (args.watch or []) seen = set() for name in watchers: uid = resolve_user(name) if uid in seen: continue seen.add(uid) try: api_post(args.base_url, f"/issues/{ticket_id}/watchers.json", args.api_key, {"user_id": uid}) print(f" Added watcher {name} (user {uid}) to #{ticket_id}") except SystemExit as e: if "422" in str(e): print(f" {name} is already watching #{ticket_id}") else: raise # --------------------------------------------------------------------------- # Subcommand: comment # --------------------------------------------------------------------------- def cmd_comment(args): """Add a comment to an existing ticket.""" message = read_text_input(args.message, args.message_file) if not message: sys.exit("Error: --message or --message-file is required") message = strip_emoji(prepend_attribution(message)) issue = {"notes": message} if args.private: issue["private_notes"] = True data = {"issue": issue} api_put(args.base_url, f"/issues/{args.ticket_id}.json", args.api_key, data) label = "private comment" if args.private else "Commented" print(f"{label} on #{args.ticket_id}: {make_url(args.base_url, args.ticket_id)}") # --------------------------------------------------------------------------- # Subcommand: update # --------------------------------------------------------------------------- def cmd_update(args): """Update fields on an existing ticket.""" issue_data = {} if args.status is not None: issue_data["status_id"] = resolve_status(args.status) if args.assigned_to is not None: if args.assigned_to == "": issue_data["assigned_to_id"] = "" else: issue_data["assigned_to_id"] = resolve_user(args.assigned_to) if args.priority is not None: issue_data["priority_id"] = args.priority if args.subject is not None: issue_data["subject"] = strip_emoji(args.subject) description = read_text_input(args.description, args.description_file) if description is not None: # An explicit empty string clears the description; anything else gets # the same attribution treatment as create, which is idempotent so # rewriting a description Claude already wrote will not double it up. if description == "": issue_data["description"] = "" else: issue_data["description"] = strip_emoji( prepend_attribution(description)) if args.target_version is not None: if args.target_version == "": issue_data["fixed_version_id"] = "" else: issue = api_get(args.base_url, f"/issues/{args.ticket_id}.json", args.api_key) project_id = issue["issue"]["project"]["id"] issue_data["fixed_version_id"] = resolve_version( args.target_version, project_id, args.base_url, args.api_key) custom_fields = [] if args.category is not None: custom_fields.append({"id": CF_CATEGORY, "value": args.category}) if args.mlm is not None: custom_fields.append({"id": CF_MLM, "value": args.mlm}) if args.release_log_text is not None: custom_fields.append({"id": CF_RELEASE_LOG_TEXT, "value": args.release_log_text}) if args.release_log_url is not None: custom_fields.append({"id": CF_RELEASE_LOG_URL, "value": args.release_log_url}) if args.released_to_rr is not None: val = _validate_bool(args.released_to_rr, "--released-to-rr") custom_fields.append({"id": CF_RELEASED_TO_RR, "value": val}) if args.file_list is not None: custom_fields.append({"id": CF_FILE_LIST, "value": args.file_list}) if args.file_list_add: existing = api_get(args.base_url, f"/issues/{args.ticket_id}.json", args.api_key)["issue"] current = "" for cf in existing.get("custom_fields", []): if cf["id"] == CF_FILE_LIST: current = cf.get("value") or "" break lines = [l for l in current.splitlines() if l.strip()] for path in args.file_list_add: if path not in lines: lines.append(path) custom_fields.append({"id": CF_FILE_LIST, "value": "\r\n".join(lines)}) if args.table_list is not None: custom_fields.append({"id": CF_TABLE_LIST, "value": args.table_list}) if args.assemblies is not None: custom_fields.append({"id": CF_ASSEMBLIES, "value": args.assemblies}) for cf_spec in (args.custom_field or []): if "=" not in cf_spec: sys.exit(f"Error: --custom-field must be ID=VALUE, got: {cf_spec}") cf_id, cf_val = cf_spec.split("=", 1) if not cf_id.isdigit(): sys.exit(f"Error: custom field ID must be numeric, got: {cf_id}") cf_id = int(cf_id) if cf_id in FIELD_VALIDATORS: validator, name = FIELD_VALIDATORS[cf_id] cf_val = validator(cf_val, f"--custom-field {cf_id} ({name})") custom_fields.append({"id": cf_id, "value": cf_val}) if custom_fields: issue_data["custom_fields"] = custom_fields note = read_text_input(args.note, args.note_file) if note: issue_data["notes"] = strip_emoji(prepend_attribution(note)) if args.private: issue_data["private_notes"] = True elif args.private: sys.exit("Error: --private requires --note or --note-file") if not issue_data: sys.exit("Error: no fields to update. Provide at least one of: " "--status, --assigned-to, --priority, --subject, " "--description, --description-file, " "--target-version, --category, --mlm, " "--release-log-text, --release-log-url, " "--released-to-rr, --file-list, --file-list-add, " "--table-list, --assemblies, --custom-field, --note") data = {"issue": issue_data} api_put(args.base_url, f"/issues/{args.ticket_id}.json", args.api_key, data) print(f"Updated #{args.ticket_id}: {make_url(args.base_url, args.ticket_id)}") # --------------------------------------------------------------------------- # Subcommand: attach # --------------------------------------------------------------------------- def cmd_attach(args): """Upload an attachment to a ticket.""" filepath = args.file if not os.path.isfile(filepath): sys.exit(f"Error: file not found: {filepath}") filename = args.filename or os.path.basename(filepath) with open(filepath, "rb") as f: file_data = f.read() token = api_upload(args.base_url, args.api_key, filename, file_data) content_type = mimetypes.guess_type(filename)[0] or "application/octet-stream" issue_data = { "uploads": [{"token": token, "filename": filename, "content_type": content_type}] } if args.description: issue_data["uploads"][0]["description"] = args.description if args.note: issue_data["notes"] = strip_emoji(prepend_attribution(args.note)) if args.private: issue_data["private_notes"] = True elif args.private: sys.exit("Error: --private requires --note") data = {"issue": issue_data} api_put(args.base_url, f"/issues/{args.ticket_id}.json", args.api_key, data) print(f"Attached {filename} to #{args.ticket_id}: " f"{make_url(args.base_url, args.ticket_id)}") # --------------------------------------------------------------------------- # Subcommand: users # --------------------------------------------------------------------------- def cmd_users(args): """List project members and their Redmine user IDs.""" users = [] offset = 0 limit = 100 while True: data = api_get(args.base_url, f"/projects/{args.project}/memberships.json?limit={limit}&offset={offset}", args.api_key) for m in data.get("memberships", []): if "user" not in m: continue users.append((m["user"]["name"], m["user"]["id"])) total = data.get("total_count", 0) offset += limit if offset >= total: break users.sort(key=lambda x: x[0].lower()) # Count first-name usage to detect collisions first_counts = {} for name, uid in users: first = name.split()[0].lower() first_counts[first] = first_counts.get(first, 0) + 1 out = [] out.append("| Short | Name | ID |") out.append("|-------|------|----|") for name, uid in users: parts = name.split() first = parts[0].lower() if first_counts[first] == 1: short = first elif len(parts) >= 2 and parts[0].isalpha() and parts[-1].isalpha(): short = (parts[0][0] + parts[-1]).lower() else: short = first out.append(f"| {short} | {name} | {uid} |") print("\n".join(out)) # --------------------------------------------------------------------------- # Subcommand: relate # --------------------------------------------------------------------------- def cmd_relate(args): """Create 'relates' relations between tickets.""" ticket_ids = args.ticket_ids if len(ticket_ids) < 2: sys.exit("Error: need at least two ticket IDs to relate") relation_type = args.type created = 0 skipped = 0 # Relate each pair: for N tickets, relate ticket[0] to all others, # then ticket[1] to all after it, etc. Redmine relations are bidirectional # so we only need to create them in one direction. for i in range(len(ticket_ids)): for j in range(i + 1, len(ticket_ids)): data = { "relation": { "issue_to_id": int(ticket_ids[j]), "relation_type": relation_type, } } try: api_post(args.base_url, f"/issues/{ticket_ids[i]}/relations.json", args.api_key, data) created += 1 print(f" Related #{ticket_ids[i]} <-> #{ticket_ids[j]}") except SystemExit as e: # Duplicate relation returns 422; treat as skip if "422" in str(e): skipped += 1 print(f" Already related: #{ticket_ids[i]} <-> #{ticket_ids[j]}") else: raise print(f"Done: {created} created, {skipped} already existed") # --------------------------------------------------------------------------- # Related-ticket helpers (shared by `show` and `related`) # --------------------------------------------------------------------------- def fetch_relations(base_url, ticket_id, api_key): """Return a list of (other_ticket_id, relation_type) for a ticket's relations. The relation JSON names both ends (issue_id, issue_to_id); the "other" end is whichever one is not this ticket.""" data = api_get(base_url, f"/issues/{ticket_id}.json?include=relations", api_key) tid = int(ticket_id) rels = [] for r in data["issue"].get("relations", []): other = r["issue_to_id"] if r["issue_id"] == tid else r["issue_id"] rels.append((other, r.get("relation_type", "relates"))) # Stable order: by relation type, then ticket id. rels.sort(key=lambda t: (t[1], t[0])) return rels def summarize_issue(base_url, ticket_id, api_key): """Return {id, subject, status, tracker} for a ticket, or None if it can't be read (e.g. deleted or no permission).""" try: d = api_get(base_url, f"/issues/{ticket_id}.json", api_key)["issue"] except SystemExit: return None return {"id": d["id"], "subject": d.get("subject", ""), "status": d.get("status", {}).get("name", ""), "tracker": d.get("tracker", {}).get("name", "")} def related_lines(base_url, ticket_id, api_key): """Build markdown bullet lines describing a ticket's related tickets, one per line with tracker/status and subject. Returns [] if there are none.""" rels = fetch_relations(base_url, ticket_id, api_key) if not rels: return [] lines = [] for other_id, rtype in rels: info = summarize_issue(base_url, other_id, api_key) if info: lines.append(f"- {rtype} #{other_id} [{info['tracker']}/{info['status']}] " f"{info['subject']}") else: lines.append(f"- {rtype} #{other_id} (not readable)") return lines # --------------------------------------------------------------------------- # Subcommand: related # --------------------------------------------------------------------------- def cmd_related(args): """List a ticket's related tickets, or add new relations to it.""" if args.add: # Add each --add ticket as a relation of the main ticket (idempotent, like `relate`). created = skipped = 0 for other in args.add: data = {"relation": {"issue_to_id": int(other), "relation_type": args.type}} try: api_post(args.base_url, f"/issues/{args.ticket_id}/relations.json", args.api_key, data) created += 1 print(f" Related #{args.ticket_id} <-> #{other} ({args.type})") except SystemExit as e: if "422" in str(e): skipped += 1 print(f" Already related: #{args.ticket_id} <-> #{other}") else: raise print(f"Done: {created} created, {skipped} already existed") return # No --add: list (pull) the related tickets. lines = related_lines(args.base_url, args.ticket_id, args.api_key) if not lines: print(f"#{args.ticket_id} has no related tickets.") return print(f"# Related tickets for #{args.ticket_id}") print("") print("\n".join(lines)) # --------------------------------------------------------------------------- # Subcommand: watch # --------------------------------------------------------------------------- def cmd_watch(args): """Add watchers to a ticket.""" ticket_id = args.ticket_id for name in args.users: user_id = resolve_user(name) data = {"user_id": user_id} try: api_post(args.base_url, f"/issues/{ticket_id}/watchers.json", args.api_key, data) print(f" Added watcher {name} (user {user_id}) to #{ticket_id}") except SystemExit as e: if "422" in str(e): print(f" {name} is already watching #{ticket_id}") else: raise print(f"Done: {make_url(args.base_url, ticket_id)}") # --------------------------------------------------------------------------- # Subcommand: note # --------------------------------------------------------------------------- def cmd_note(args): """Display a specific note from a ticket.""" data = api_get(args.base_url, f"/issues/{args.ticket_id}.json?include=journals", args.api_key) issue = data["issue"] journals = issue.get("journals", []) # Redmine API may return journals newest-first; sort by created_on # to match the web UI's #note-N numbering (note-1 = oldest). journals.sort(key=lambda j: j["created_on"]) note_num = args.note_number if note_num < 1 or note_num > len(journals): sys.exit(f"Error: note-{note_num} does not exist. " f"Ticket #{args.ticket_id} has {len(journals)} journal entries.") j = journals[note_num - 1] user = j["user"]["name"] date = format_date(j["created_on"]) notes = j.get("notes", "") details = j.get("details", []) out = [] out.append(f"# #{issue['id']} note-{note_num}: {user} — {date}") out.append(f"URL: {make_url(args.base_url, issue['id'])}#note-{note_num}") out.append("") if details: detail_text = format_details(details) if detail_text: out.append(detail_text) out.append("") if notes: out.append(redmine_textile_to_md(notes)) if not notes and not details: out.append("(empty journal entry)") print("\n".join(out)) +# --------------------------------------------------------------------------- +# sql +# --------------------------------------------------------------------------- + +def import_pymysql(): + """Import pymysql, preferring the copy bundled in the kent tree.""" + try: + import pymysql + return pymysql + except ImportError: + pass + pyLib = os.path.join(os.path.dirname(os.path.realpath(__file__)), + "..", "hg", "pyLib") + sys.path.insert(0, os.path.normpath(pyLib)) + try: + import pymysql + return pymysql + except ImportError: + sys.exit("Error: cannot import pymysql. Expected the copy bundled in " + "the kent tree at src/hg/pyLib.") + + +def sql_connect(args): + """Open a connection to the Redmine database using ~/.hg.conf credentials.""" + pymysql = import_pymysql() + conf = read_conf(args.conf) + # Prefer a dedicated redmine.db.* account. Fall back to the db.* account, + # which is the same shared read-only login on both servers, so the password + # does not have to be stored twice. + user = conf.get("redmine.db.user") or conf.get("db.user") + password = conf.get("redmine.db.password") or conf.get("db.password") + if not user or not password: + sys.exit( + "Error: no database credentials in " + os.path.expanduser(args.conf) + + "\nAdd redmine.db.user and redmine.db.password (the file must be " + "mode 600).\nredmine.db.host, redmine.db.name and redmine.db.port " + "are optional.") + database = args.database or conf.get("redmine.db.name", DEFAULT_DB_NAME) + try: + # Multi-statement support stays off, so a query cannot smuggle in a + # second statement after a semicolon. + return pymysql.connect( + host=conf.get("redmine.db.host", DEFAULT_DB_HOST), + port=int(conf.get("redmine.db.port", DEFAULT_DB_PORT)), + user=user, password=password, database=database, + charset="utf8mb4", connect_timeout=10) + except Exception as e: + sys.exit(f"Error: cannot connect to the Redmine database: {e}") + + +def sql_first_verb(sql): + """Return the first SQL keyword, skipping leading comments and blank lines.""" + for line in sql.splitlines(): + line = line.strip() + if not line or line.startswith("--") or line.startswith("#"): + continue + word = line.lstrip("(").split(None, 1) + return word[0].lower() if word else "" + return "" + + +def sql_cell(value, max_width=0): + """Render one result value as a single line of display text.""" + if value is None: + text = "NULL" + elif isinstance(value, datetime): + text = value.isoformat(sep=" ") + elif isinstance(value, date): + text = value.isoformat() + elif isinstance(value, bytes): + text = value.decode("utf-8", errors="replace") + else: + text = str(value) + text = " ".join(text.split()) + if max_width and len(text) > max_width: + text = text[:max_width - 3] + "..." + return text + + +def sql_json_default(value): + """Render values json.dumps cannot handle on its own.""" + if isinstance(value, (date, datetime)): + return value.isoformat() + if isinstance(value, bytes): + return value.decode("utf-8", errors="replace") + if isinstance(value, decimal.Decimal): + return float(value) + return str(value) + + +def sql_print_table(columns, rows, max_width): + """Print rows as a column-aligned text table.""" + widths = [len(c) for c in columns] + cells = [] + for row in rows: + out = [sql_cell(v, max_width) for v in row] + for i, text in enumerate(out): + widths[i] = max(widths[i], len(text)) + cells.append(out) + print(" ".join(c.ljust(w) for c, w in zip(columns, widths)).rstrip()) + print(" ".join("-" * w for w in widths)) + for out in cells: + print(" ".join(t.ljust(w) for t, w in zip(out, widths)).rstrip()) + + +def sql_check_secret_tables(sql): + """Refuse a query naming a table that holds only credentials.""" + for table in SQL_SECRET_TABLES: + if re.search(r"\b%s\b" % re.escape(table), sql, re.IGNORECASE): + sys.exit(f"Error: refusing to query the '{table}' table, which holds " + "Redmine API keys and session tokens.\n" + "Pass --allow-sensitive if you really mean to read it.") + + +def sql_check_secret_columns(columns): + """Refuse to print credential columns. Called before any row is fetched.""" + hits = [c for c in columns if c.lower() in SQL_SECRET_COLUMNS] + if hits: + sys.exit("Error: refusing to print the column(s) " + ", ".join(hits) + + ", which hold password hashes or two-factor seeds.\n" + "Name the columns you want instead of using *, or pass " + "--allow-sensitive.") + + +def cmd_sql(args): + """Run a read-only SQL query against the Redmine database.""" + if args.tables: + sql = "show tables" + elif args.describe: + sql = "describe `%s`" % args.describe.replace("`", "") + else: + sql = read_text_input(args.query, args.file) + if sql is None and not sys.stdin.isatty(): + sql = sys.stdin.read() + if not sql or not sql.strip(): + sys.exit("Error: no query given. Pass SQL as an argument, with " + "--file, or on stdin.") + sql = sql.strip().rstrip(";").strip() + if args.limit < 0: + args.limit = 0 + + verb = sql_first_verb(sql) + if verb not in SQL_ALLOWED_VERBS: + sys.exit(f"Error: refusing to run a '{verb}' statement. redmineCli sql " + "runs read-only queries only: " + + ", ".join(v.upper() for v in SQL_ALLOWED_VERBS) + ".") + if not args.allow_sensitive: + sql_check_secret_tables(sql) + if args.explain and verb in ("select", "with"): + sql = "explain " + sql + + conn = sql_connect(args) + try: + with conn.cursor() as cur: + # Kill a runaway query rather than let it hold production resources. + cur.execute("set session max_statement_time=%f" % float(args.timeout)) + cur.execute(sql) + if cur.description is None: + sys.exit("Error: that statement returned no result set.") + columns = [d[0] for d in cur.description] + if not args.allow_sensitive: + sql_check_secret_columns(columns) + # Fetch one extra row so we can tell "exactly at the cap" from + # "there was more". + rows = cur.fetchmany(args.limit + 1) if args.limit else cur.fetchall() + except SystemExit: + raise + except Exception as e: + sys.exit(f"Error: query failed: {e}") + finally: + conn.close() + + truncated = bool(args.limit) and len(rows) > args.limit + if truncated: + rows = rows[:args.limit] + + if args.json: + print(json.dumps([dict(zip(columns, r)) for r in rows], + indent=2, default=sql_json_default)) + elif args.tsv: + print("\t".join(columns)) + for row in rows: + print("\t".join(sql_cell(v) for v in row)) + elif rows: + sql_print_table(columns, rows, args.max_width) + + # Flush before the stderr footer, or a piped stdout arrives after it. + sys.stdout.flush() + count = len(rows) + note = f"\n{count} row{'' if count == 1 else 's'}" + if truncated: + note += f" (cut off at --limit {args.limit}; use --limit 0 for all)" + sys.stderr.write(note + "\n") + + # --------------------------------------------------------------------------- # Argument parsing # --------------------------------------------------------------------------- def build_parser(): parser = argparse.ArgumentParser( prog="redmineCli", description="Redmine CLI for the UCSC Genome Browser team") parser.add_argument("--redmine", default=DEFAULT_REDMINE, help="Redmine base URL (default: %(default)s)") parser.add_argument("--conf", default="~/.hg.conf", help="Config file with redmine.apiKey (default: %(default)s)") sub = parser.add_subparsers(dest="command", required=True) # show p_show = sub.add_parser("show", help="Display a ticket in Markdown, optionally download attachments") p_show.add_argument("ticket_id", help="Ticket ID number") p_show.add_argument("--images", action="store_true", help="Download images to a temp directory") p_show.add_argument("--download-all", dest="download_all", action="store_true", help="Download all attachments to a temp directory") p_show.add_argument("--no-related", dest="no_related", action="store_true", help="Don't fetch and show related tickets (faster)") # list p_list = sub.add_parser("list", help="List/search tickets") p_list.add_argument("--project", default=DEFAULT_PROJECT, help="Project identifier (default: %(default)s)") p_list.add_argument("--status", default="open", help="Status filter: open, closed, * (default: %(default)s)") p_list.add_argument("--assigned-to", dest="assigned_to", help="Assignee name or 'me'") p_list.add_argument("--tracker", help="Tracker name or ID") p_list.add_argument("--target-version", dest="target_version", help="Target version name or ID (e.g. 503)") p_list.add_argument("--search", help="Search in subject") p_list.add_argument("--limit", type=int, default=25, help="Max results (default: %(default)s)") p_list.add_argument("--offset", type=int, default=0, help="Pagination offset (default: %(default)s)") p_list.add_argument("--sort", default="updated_on:desc", help="Sort field:direction (default: %(default)s)") # patch-queue p_pq = sub.add_parser("patch-queue", help="Report which Build Patch tickets are ready to patch") p_pq.add_argument("--target-version", dest="target_version", help="Only accept tickets targeting this release (e.g. 502)") p_pq.add_argument("--tickets", nargs="+", metavar="ID", help="Restrict to these ticket numbers (default: all open Build Patches)") p_pq.add_argument("--status", default="open", help="Status filter passed to Redmine: open, closed, * (default: %(default)s)") p_pq.add_argument("--limit", type=int, default=100, help="Max tickets to examine (default: %(default)s)") p_pq.add_argument("--tsv", action="store_true", help="Machine-readable output: verdict/id/commits/notes/subject") # create p_create = sub.add_parser("create", help="Create a new ticket") p_create.add_argument("--subject", required=True, help="Ticket subject") p_create.add_argument("--description", help="Ticket description") p_create.add_argument("--description-file", dest="description_file", help="Read description from file (- for stdin)") p_create.add_argument("--project", default=DEFAULT_PROJECT, help="Project (default: %(default)s)") p_create.add_argument("--tracker", default=TRACKER_MLQ, help="Tracker name or ID (e.g. 'To Do' or 10; default: %(default)s)") p_create.add_argument("--priority", type=int, default=PRIORITY_UNPRIORITIZED, help="Priority ID (default: %(default)s)") p_create.add_argument("--status", default=STATUS_NEW, help="Status name or ID (e.g. 'New' or 1; default: %(default)s)") p_create.add_argument("--assigned-to", dest="assigned_to", help="Assignee name or user ID") p_create.add_argument("--category", help="MLQ Category (custom field)") p_create.add_argument("--email", help="Sender email (custom field)") p_create.add_argument("--mlm", help="MLM name (custom field)") p_create.add_argument("--assemblies", help="Assemblies (custom field, e.g. 'hg38')") p_create.add_argument("--custom-field", dest="custom_field", action="append", metavar="ID=VALUE", help="Set an arbitrary custom field by numeric ID " "(repeatable). Use for required fields on trackers " "like Track that would otherwise 422.") # build-patch p_bp = sub.add_parser("build-patch", help="Create a Build Patch ticket (GB, Urgent, all required fields set)") p_bp.add_argument("--subject", required=True, help="Ticket subject") p_bp.add_argument("--description", help="What is broken and why it needs a patch") p_bp.add_argument("--description-file", dest="description_file", help="Read description from file (- for stdin)") p_bp.add_argument("--developer", required=True, help="Developer who caused the bug (name or user ID)") p_bp.add_argument("--commit-id", dest="commit_id", required=True, help="Fix commit hash; list all hashes if several commits fix it") p_bp.add_argument("--files", required=True, help="Full paths of the source files changed") p_bp.add_argument("--cgis", required=True, help="CGIs that could be affected and need retesting") p_bp.add_argument("--test-case", dest="test_case", required=True, help="Step-by-step to reproduce both the failure and the fix") p_bp.add_argument("--reviewer", required=True, help="Suggested code reviewer (name or user ID); also added as a watcher") p_bp.add_argument("--target-version", dest="target_version", required=True, help="Release number being built (e.g. 500)") p_bp.add_argument("--post-mortem", dest="post_mortem", help="Optional plain-language what-happened / how-to-prevent note") p_bp.add_argument("--relates", nargs="+", metavar="BUG_ID", help="Bug ticket(s) this patch fixes; related on creation") p_bp.add_argument("--watch", nargs="+", metavar="NAME", help="Extra watchers beyond the QA Team + build meister + reviewer defaults") p_bp.add_argument("--no-default-watchers", dest="no_default_watchers", action="store_true", help="Do not auto-add the QA Team, build meister, and reviewer as watchers") p_bp.add_argument("--project", default=PROJECT_GB, help="Project (default: %(default)s)") # comment p_comment = sub.add_parser("comment", help="Add a comment to a ticket") p_comment.add_argument("ticket_id", help="Ticket ID number") p_comment.add_argument("--message", help="Comment text") p_comment.add_argument("--message-file", dest="message_file", help="Read comment from file (- for stdin)") p_comment.add_argument("--private", action="store_true", help="Mark this comment as a private note " "(only visible to project members with permission)") # update p_update = sub.add_parser("update", help="Update ticket fields") p_update.add_argument("ticket_id", help="Ticket ID number") p_update.add_argument("--status", help="New status name or ID (e.g. 'QA Ready' or 10)") p_update.add_argument("--assigned-to", dest="assigned_to", help="Assignee name/ID (empty string to clear)") p_update.add_argument("--priority", type=int, help="New priority ID") p_update.add_argument("--subject", help="New subject") p_update.add_argument("--description", help="Replace the ticket description (empty string " "clears it)") p_update.add_argument("--description-file", dest="description_file", help="Read new description from file (- for stdin)") p_update.add_argument("--target-version", dest="target_version", help="Target version name or ID (empty string to clear)") p_update.add_argument("--category", help="MLQ Category") p_update.add_argument("--mlm", help="MLM name") p_update.add_argument("--release-log-text", dest="release_log_text", help="Release Log Text (custom field)") p_update.add_argument("--release-log-url", dest="release_log_url", help="Release Log URL (custom field)") p_update.add_argument("--released-to-rr", dest="released_to_rr", help="Released to RR (checkbox: 0/1/true/false/yes/no/on/off)") p_update.add_argument("--file-list", dest="file_list", help="File List (custom field, overwrites existing value)") p_update.add_argument("--file-list-add", dest="file_list_add", action="append", metavar="PATH", help="Append PATH to the File List custom field " "(repeatable; idempotent, skips paths already present)") p_update.add_argument("--table-list", dest="table_list", help="Table List (custom field)") p_update.add_argument("--assemblies", help="Assemblies (custom field)") p_update.add_argument("--custom-field", dest="custom_field", action="append", metavar="ID=VALUE", help="Set arbitrary custom field by ID (repeatable)") p_update.add_argument("--note", help="Comment to include with update") p_update.add_argument("--note-file", dest="note_file", help="Read note from file (- for stdin)") p_update.add_argument("--private", action="store_true", help="Mark the accompanying note as a private note " "(requires --note or --note-file)") # attach p_attach = sub.add_parser("attach", help="Upload an attachment") p_attach.add_argument("ticket_id", help="Ticket ID number") p_attach.add_argument("file", help="File path to upload") p_attach.add_argument("--filename", help="Override filename") p_attach.add_argument("--description", help="Attachment description") p_attach.add_argument("--note", help="Comment to add with attachment") p_attach.add_argument("--private", action="store_true", help="Mark the accompanying note as a private note " "(requires --note)") # users p_users = sub.add_parser("users", help="List project members and their user IDs") p_users.add_argument("--project", default=DEFAULT_PROJECT, help="Project identifier (default: %(default)s)") # relate p_relate = sub.add_parser("relate", help="Create relations between tickets") p_relate.add_argument("ticket_ids", nargs="+", help="Two or more ticket IDs to relate") p_relate.add_argument("--type", default="relates", help="Relation type: relates, duplicates, duplicated, blocks, " "blocked, precedes, follows, copied_to, copied_from " "(default: %(default)s)") # related p_related = sub.add_parser("related", help="List a ticket's related tickets, or add relations to it") p_related.add_argument("ticket_id", help="Ticket ID number") p_related.add_argument("--add", nargs="+", metavar="ID", help="Ticket IDs to relate to this ticket (omit to just list)") p_related.add_argument("--type", default="relates", help="Relation type used with --add: relates, duplicates, " "duplicated, blocks, blocked, precedes, follows, copied_to, " "copied_from (default: %(default)s)") # watch p_watch = sub.add_parser("watch", help="Add watchers to a ticket") p_watch.add_argument("ticket_id", help="Ticket ID number") p_watch.add_argument("users", nargs="+", help="User names or IDs to add as watchers") # note p_note = sub.add_parser("note", help="Display a specific note from a ticket") p_note.add_argument("ticket_id", help="Ticket ID number") p_note.add_argument("note_number", type=int, help="Note number (as shown in Redmine URL #note-N)") + # sql + p_sql = sub.add_parser("sql", + help="Run a read-only SQL query against the Redmine database") + p_sql.add_argument("query", nargs="?", + help="SQL to run; omit to read it from --file or stdin") + p_sql.add_argument("--file", help="Read the query from a file ('-' for stdin)") + p_sql.add_argument("--tables", action="store_true", + help="List the tables in the database") + p_sql.add_argument("--describe", metavar="TABLE", + help="Show one table's columns") + p_sql.add_argument("--allow-sensitive", dest="allow_sensitive", + action="store_true", + help="Allow reading credential tables and columns " + "(tokens, password hashes, two-factor seeds)") + p_sql.add_argument("--explain", action="store_true", + help="Prefix the query with EXPLAIN instead of running it") + p_sql.add_argument("--database", help="Schema name (default: redmine.db.name " + "from the conf file, else %s)" % DEFAULT_DB_NAME) + p_sql.add_argument("--tsv", action="store_true", help="Tab-separated output") + p_sql.add_argument("--json", action="store_true", help="JSON output") + p_sql.add_argument("--limit", type=int, default=SQL_DEFAULT_LIMIT, + help="Most rows to print, 0 for all (default: %(default)s)") + p_sql.add_argument("--max-width", dest="max_width", type=int, default=80, + help="Cut off wide cells in table output, 0 for no limit " + "(default: %(default)s)") + p_sql.add_argument("--timeout", type=float, default=SQL_MAX_STATEMENT_TIME, + help="Seconds before the server kills the query " + "(default: %(default)s)") + return parser # --------------------------------------------------------------------------- # Main # --------------------------------------------------------------------------- def main(): parser = build_parser() args = parser.parse_args() args.api_key = read_api_key(args.conf) args.base_url = args.redmine commands = { "show": cmd_show, "list": cmd_list, "patch-queue": cmd_patch_queue, "create": cmd_create, "build-patch": cmd_build_patch, "comment": cmd_comment, "update": cmd_update, "attach": cmd_attach, "users": cmd_users, "relate": cmd_relate, "related": cmd_related, "watch": cmd_watch, "note": cmd_note, + "sql": cmd_sql, } commands[args.command](args) if __name__ == "__main__": main()