"""Exercise the pricing correction against an isolated local PostgreSQL cluster.""" import argparse import json import socket import subprocess import tempfile from pathlib import Path parser=argparse.ArgumentParser() parser.add_argument('--sources',type=Path,required=True) parser.add_argument('--postgres-bin',type=Path,required=True) args=parser.parse_args() repo=Path(__file__).resolve().parent.parent folder=Path(tempfile.mkdtemp(prefix='pricing-migration-',dir=repo/'.tools')) bin=args.postgres_bin with socket.socket() as sock: sock.bind(('127.0.0.1',0));port=sock.getsockname()[1] def run(command): # PostgreSQL's Windows child can inherit stdout pipes and keep communicate() # waiting after pg_ctl exits. A file avoids inheriting a pipe reader. with tempfile.TemporaryFile(mode='w+b') as output: subprocess.run([str(x) for x in command],check=True,stdout=output,stderr=output,timeout=90) output.seek(0) return output.read().decode('utf-8',errors='replace') def sql_literal(value): return "'"+value.replace("'","''")+"'" def sql_json(value): return sql_literal(json.dumps(value,ensure_ascii=False,separators=(',',':')))+'::jsonb' rows=json.loads((args.sources/'pricing-corrections.json').read_text(encoding='utf8')) changed=[r for r in rows if r['state']=='修正'] sql=['CREATE TABLE model_providers(id text PRIMARY KEY,code text,deleted_at timestamptz);', 'CREATE TABLE model_catalog(id text PRIMARY KEY,provider_id text,upstream_model text,pricing jsonb,status text,deleted_at timestamptz,updated_at timestamptz);'] for provider in sorted({r['provider'] for r in rows}): sql.append(f'INSERT INTO model_providers VALUES ({sql_literal(provider)},{sql_literal(provider)},NULL);') for row in rows: sql.append('INSERT INTO model_catalog VALUES ('+','.join([sql_literal(row['id']),sql_literal(row['provider']),sql_literal(row['model']),sql_json(row['oldPricing']),sql_literal(row['status']),'NULL','now()'])+');') sample=changed[0] sql.append('INSERT INTO model_catalog SELECT \'manual-edit\',provider_id,upstream_model,\'{"input":123,"unit":"CNY / 1M tokens"}\'::jsonb,\'disabled\',NULL,now() FROM model_catalog WHERE id='+sql_literal(sample['id'])+';') sql.append('CREATE TEMP TABLE before_rows AS SELECT * FROM model_catalog;') migration=(repo/'control-plane-java/src/main/resources/db/migration/V34__reconcile_source_currency_and_retail_discounts.sql').read_text(encoding='utf8') sql.append(migration) for row in rows: expected=row['newPricing'] if row['state']=='修正' else row['oldPricing'] sql.append('DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM model_catalog WHERE id='+sql_literal(row['id'])+' AND pricing='+sql_json(expected)+') THEN RAISE EXCEPTION \'Incorrect migrated price\'; END IF; END $$;') sql.append("DO $$ BEGIN IF EXISTS (SELECT 1 FROM model_catalog m JOIN before_rows b USING(id) WHERE m.status<>b.status) OR NOT EXISTS (SELECT 1 FROM model_catalog WHERE id='manual-edit' AND pricing->>'input'='123') THEN RAISE EXCEPTION 'Manual edit or status was changed'; END IF; END $$;") sql.append('CREATE TEMP TABLE first_pass AS SELECT * FROM model_catalog;') sql.append(migration) sql.append("DO $$ BEGIN IF EXISTS (SELECT 1 FROM model_catalog m JOIN first_pass f USING(id) WHERE m.pricing IS DISTINCT FROM f.pricing OR m.updated_at IS DISTINCT FROM f.updated_at) THEN RAISE EXCEPTION 'Not idempotent'; END IF; END $$;") sql.append("SELECT 'Pricing migration verified' AS result;") script=folder/'verify.sql';script.write_text('\n'.join(sql),encoding='utf8') run([bin/'initdb.exe','-D',folder/'data','-U','pricing_test','-A','trust','--encoding=UTF8','--locale=C']) try: run([bin/'pg_ctl.exe','-D',folder/'data','-l',folder/'postgres.log','-o',f'-p {port} -h 127.0.0.1','-w','start']) out=run([bin/'psql.exe','-h','127.0.0.1','-p',str(port),'-U','pricing_test','-d','postgres','-v','ON_ERROR_STOP=1','-f',script]) print(f'PASS: {len(changed)} corrections; {len(rows)} prices checked; manual-edit guard, status preservation and idempotence verified.') finally: run([bin/'pg_ctl.exe','-D',folder/'data','-w','stop','-m','fast'])