Spaces:
Running
Running
File size: 12,333 Bytes
f689c7d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 | """Read-only shared Flyway planning. This neither migrates nor reserves a version."""
import argparse
from datetime import datetime, timezone
import hashlib
import json
import os
from pathlib import Path
import re
import subprocess
import sys
import zlib
from urllib.parse import parse_qs, unquote, urlparse
from sql_policy import SqlPolicyError, validate as validate_sql
BUNDLE = "taskpilot-app/src/main/resources/db/reconciled"
ROOT = Path(__file__).resolve().parents[2]
NAME = re.compile(r"V([1-9][0-9]*)__[a-z0-9][a-z0-9_]*\.sql\Z")
QUERY = """
BEGIN READ ONLY ISOLATION LEVEL REPEATABLE READ;
SET LOCAL statement_timeout='15s';
SELECT json_build_object(
'rows', (SELECT json_agg(json_build_object(
'version',version,'type',type,'script',script,'checksum',checksum,'success',success)
ORDER BY installed_rank) FROM taskpilot_migrations.flyway_schema_history_v2),
'baseline_comment',obj_description('taskpilot_migrations.flyway_schema_history_v2'::regclass));
COMMIT;
"""
class CheckError(Exception):
pass
def normalized(sql):
return sql.replace("\r\n", "\n")
def flyway_checksum(sql):
# Flyway 11.3.2 ChecksumCalculator: first-line BOM removed, UTF-8 CRC32
# over BufferedReader lines without CR/LF, converted to signed Java int.
value = zlib.crc32(sql.removeprefix("\ufeff").replace("\r", "").replace("\n", "").encode("utf-8"))
return value if value < 2**31 else value - 2**32
def migrations(files):
found = {}
for filename, sql in files.items():
match = NAME.fullmatch(filename)
if not match:
raise CheckError("INVALID_MIGRATION_NAME: use flat V<number>__lowercase_description.sql files")
version = int(match[1])
if version in found:
raise CheckError(f"DUPLICATE_VERSION: V{version}")
found[version] = (filename, normalized(sql))
return found
def plan(local_files, main_files, history):
local, main = migrations(local_files), migrations(main_files)
if not {41, 42}.issubset(main):
raise CheckError("RECOVERY_CHAIN_MISSING: V41/V42 must remain present")
for version, accepted in main.items():
if local.get(version) != accepted:
raise CheckError(f"ACCEPTED_MIGRATION_CHANGED: V{version}; update the branch, never rewrite accepted SQL")
for collection in [main, local]:
versions = sorted(collection)
if versions[0] != 41 or any(b != a + 1 for a, b in zip(versions, versions[1:])):
raise CheckError("NON_SEQUENTIAL_VERSION: update main and coordinate the next number")
for version in sorted(set(local) - set(main)):
try:
validate_sql(local[version][1])
except SqlPolicyError as error:
raise CheckError(f"MIGRATION_SQL_POLICY: V{version}: {error}") from None
deployed = None
if history is not None:
rows = history.get("rows") or []
seen = []
for row in rows:
version = str(row.get("version", ""))
if not re.fullmatch(r"[1-9][0-9]*", version) or row.get("success") is not True:
raise CheckError("INVALID_SHARED_HISTORY: failed or unexpected version")
version = int(version)
if version not in main:
raise CheckError("SHARED_HISTORY_NOT_IN_MAIN: do not renumber already-applied shared migrations")
if row.get("type") == "BASELINE" and version == 41 and not seen:
expected = "TaskPilot reviewed baseline SHA-256:" + hashlib.sha256(main[41][1].encode("utf-8")).hexdigest()
if row.get("checksum") is not None or history.get("baseline_comment") != expected:
raise CheckError("UNBOUND_BASELINE: use the recovery runbook")
elif row.get("type") != "SQL" or row.get("checksum") is None:
raise CheckError("INVALID_SHARED_HISTORY: expected a successful SQL migration")
elif row.get("script") != main[version][0]:
raise CheckError("SHARED_HISTORY_NOT_IN_MAIN: applied filename conflicts with main")
elif row["checksum"] != flyway_checksum(main[version][1]):
raise CheckError(f"SHARED_CHECKSUM_MISMATCH: V{version}; investigate instead of repair/renumber")
seen.append(version)
if not seen or seen[0] != 41 or 42 not in seen or any(b != a + 1 for a, b in zip(seen, seen[1:])):
raise CheckError("INVALID_SHARED_HISTORY: incomplete or unordered recovery history")
deployed = seen[-1]
return {
"checked_at_utc": datetime.now(timezone.utc).isoformat(),
"database_checked": history is not None,
"deployed_version": deployed,
"main_version": max(main),
"local_version": max(local),
"next_version": max(max(main), max(local), deployed or 0) + 1 if history is not None else None,
"new_local_versions": sorted(set(local) - set(main)),
"merged_not_deployed": [v for v in sorted(main) if deployed is not None and v > deployed],
"number_reserved": False,
"flyway_validate_run": False,
}
def git(root, *args):
try:
result = subprocess.run(["git", *args], cwd=root, capture_output=True,
timeout=45, env={**os.environ, "GIT_TERMINAL_PROMPT": "0", "GCM_INTERACTIVE": "never"})
except (OSError, subprocess.TimeoutExpired):
raise CheckError("GIT_UNAVAILABLE_OR_TIMED_OUT") from None
if result.returncode:
raise CheckError("GIT_CHECK_FAILED: verify remote access and the requested base ref")
return result.stdout.decode("utf-8-sig")
def sources(root, base_ref):
legacy = "taskpilot-app/src/main/resources/db/migration"
changed = git(root, "diff", "--name-only", base_ref, "--", legacy).splitlines()
changed += git(root, "ls-files", "--others", "--exclude-standard", "--", legacy).splitlines()
if changed:
raise CheckError("LEGACY_MIGRATIONS_CHANGED: add new SQL only in db/reconciled")
main = {}
for path in git(root, "ls-tree", "-r", "--name-only", base_ref, "--", BUNDLE).splitlines():
filename=path.removeprefix(BUNDLE + "/")
if not NAME.fullmatch(filename):
raise CheckError("UNSUPPORTED_MIGRATION_ARTIFACT: accepted bundle must contain flat versioned SQL only")
main[filename] = git(root, "show", base_ref + ":" + path)
local = {}
for path in (root / BUNDLE).iterdir():
if path.is_symlink():
raise CheckError("MIGRATION_SYMLINK_NOT_SUPPORTED")
if not path.is_file() or not NAME.fullmatch(path.name):
raise CheckError("UNSUPPORTED_MIGRATION_ARTIFACT: only flat V<number>__description.sql files are supported")
local[path.relative_to(root / BUNDLE).as_posix()] = path.read_text(encoding="utf-8-sig")
return local, main
def connection_parts(url):
try:
parsed = urlparse(url.removeprefix("jdbc:"))
if parsed.scheme != "postgresql" or not parsed.hostname or not parsed.path.strip("/"):
raise ValueError()
if parsed.username or parsed.password or any(k in parse_qs(parsed.query) for k in ["user", "password"]):
raise ValueError()
return parsed.hostname.lower(), parsed.port or 5432, unquote(parsed.path.lstrip("/")), parse_qs(parsed.query)
except ValueError:
raise CheckError("INVALID_DB_URL: use a PostgreSQL URL with credentials in separate variables") from None
def target_id(url, username):
host, port, database, _ = connection_parts(url)
if host.endswith(".pooler.supabase.com") and port == 6543:
port = 5432
return hashlib.sha256(f"{host}\n{port}\n{database}\n{username}".encode("utf-8")).hexdigest()
def credentials(env_file):
values = {}
if env_file.exists():
for line in env_file.read_text(encoding="utf-8-sig").splitlines():
line = line.strip().removeprefix("export ")
if line and not line.startswith("#") and "=" in line:
key, value = line.split("=", 1)
value = value.strip()
if len(value) >= 2 and value[0] == value[-1] and value[0] in "\"'":
value = value[1:-1]
values[key.strip()] = value
values.update(os.environ)
result = {"DB_" + k: values.get("MIGRATION_SHARED_DB_" + k, values.get("DB_" + k))
for k in ["URL", "USERNAME", "PASSWORD"]}
if not all(result.values()):
raise CheckError("MISSING_DB_CREDENTIALS: configure MIGRATION_SHARED_DB_* or DB_* in local environment/.env")
return result
def read_shared(creds, expected_target):
if target_id(creds["DB_URL"], creds["DB_USERNAME"]) != expected_target:
raise CheckError("WRONG_SHARED_DATABASE: configured target does not match the repository fingerprint")
host, port, database, query = connection_parts(creds["DB_URL"])
sslmode = query.get("sslmode", ["require"])[0]
if sslmode not in {"disable", "allow", "prefer", "require", "verify-ca", "verify-full"}:
raise CheckError("INVALID_SSL_MODE")
env = {**os.environ, "PGHOST": host, "PGPORT": str(port), "PGDATABASE": database,
"PGUSER": creds["DB_USERNAME"], "PGPASSWORD": creds["DB_PASSWORD"],
"PGSSLMODE": sslmode, "PGCONNECT_TIMEOUT": "10",
"PGOPTIONS": "-c default_transaction_read_only=on -c statement_timeout=15000 -c lock_timeout=3000"}
for key in ["PGSERVICE", "PGSERVICEFILE", "PGHOSTADDR"]:
env.pop(key, None)
try:
result = subprocess.run(["psql", "-X", "-q", "-A", "-t", "--no-password", "-v", "ON_ERROR_STOP=1"],
input=QUERY, env=env, capture_output=True, text=True, encoding="utf-8", timeout=25)
except (OSError, subprocess.TimeoutExpired):
raise CheckError("PSQL_UNAVAILABLE_OR_TIMED_OUT: install PostgreSQL client and verify shared access") from None
if result.returncode:
# Raw libpq messages can include URLs and credentials; never echo stderr.
raise CheckError("SHARED_DATABASE_QUERY_FAILED: verify access and active recovery history; no changes made")
try:
return json.loads(result.stdout.strip())
except (ValueError, TypeError):
raise CheckError("INVALID_SHARED_HISTORY_RESPONSE") from None
def main():
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument("--offline", action="store_true", help="Git-only CI checks; cannot report the live next version")
parser.add_argument("--base-ref", default="origin/main", help="Accepted migration source (CI: exact PR base SHA)")
parser.add_argument("--no-fetch", action="store_true", help="Use an already-fetched ref; live recommendation may be stale")
parser.add_argument("--env-file", type=Path, default=ROOT / ".env", help="Local credentials file; never printed")
args = parser.parse_args()
try:
if not args.no_fetch:
git(ROOT, "fetch", "--no-tags", "origin", "main:refs/remotes/origin/main")
local, accepted = sources(ROOT, args.base_ref)
plan(local, accepted, None) # Reject source collisions before connecting.
live = None
if not args.offline:
config = json.loads((ROOT / "tools/flyway-team/shared-target.json").read_text(encoding="utf-8"))
live = read_shared(credentials(args.env_file), config["target_sha256"])
result = plan(local, accepted, live)
result["base_ref"] = args.base_ref
result["base_commit"] = git(ROOT, "rev-parse", "--verify", args.base_ref).strip()
result["remote_refreshed"] = not args.no_fetch
result["notice"] = "Candidate only, not a reservation. Coordinate via PR and recheck before merge. This is not Flyway validate."
if args.offline:
result["notice"] = "OFFLINE: shared DB not checked; no live next version assigned. " + result["notice"]
print(json.dumps(result, ensure_ascii=True, indent=2))
return 0
except CheckError as error:
print(json.dumps({"ok": False, "error": str(error), "next_version": None}), file=sys.stderr)
return 2
except (OSError, ValueError, KeyError):
print(json.dumps({"ok": False, "error": "CHECK_CONFIGURATION_FAILED", "next_version": None}), file=sys.stderr)
return 2
if __name__ == "__main__":
sys.exit(main())
|