207 lines
6.7 KiB
JavaScript
207 lines
6.7 KiB
JavaScript
import { createHash } from "node:crypto";
|
|
import { readFile } from "node:fs/promises";
|
|
import path from "node:path";
|
|
import process from "node:process";
|
|
import { fileURLToPath } from "node:url";
|
|
|
|
const scriptDirectory = path.dirname(fileURLToPath(import.meta.url));
|
|
const backendDirectory = path.resolve(scriptDirectory, "..");
|
|
const upPath = path.join(
|
|
backendDirectory,
|
|
"migrations",
|
|
"001_create_condon_schema.up.sql"
|
|
);
|
|
const downPath = path.join(
|
|
backendDirectory,
|
|
"migrations",
|
|
"001_create_condon_schema.down.sql"
|
|
);
|
|
const legacyUpPath = path.join(
|
|
backendDirectory,
|
|
"migrations",
|
|
"002_legacy_import_and_bookings.up.sql"
|
|
);
|
|
const legacyDownPath = path.join(
|
|
backendDirectory,
|
|
"migrations",
|
|
"002_legacy_import_and_bookings.down.sql"
|
|
);
|
|
const deleteUpPath = path.join(
|
|
backendDirectory,
|
|
"migrations",
|
|
"003_delete_usage_record.up.sql"
|
|
);
|
|
const deleteDownPath = path.join(
|
|
backendDirectory,
|
|
"migrations",
|
|
"003_delete_usage_record.down.sql"
|
|
);
|
|
|
|
const [upSql, downSql, legacyUpSql, legacyDownSql, deleteUpSql, deleteDownSql] = await Promise.all([
|
|
readFile(upPath, "utf8"),
|
|
readFile(downPath, "utf8"),
|
|
readFile(legacyUpPath, "utf8"),
|
|
readFile(legacyDownPath, "utf8"),
|
|
readFile(deleteUpPath, "utf8"),
|
|
readFile(deleteDownPath, "utf8")
|
|
]);
|
|
|
|
const checks = [];
|
|
const record = (name, ok, detail = "") => {
|
|
checks.push({ name, ok, detail });
|
|
};
|
|
const count = (source, pattern) => [...source.matchAll(pattern)].length;
|
|
|
|
record(
|
|
"up creates exact schema without reuse",
|
|
count(upSql, /^CREATE SCHEMA condon;$/gm) === 1
|
|
&& !/\bIF\s+NOT\s+EXISTS\b/i.test(upSql)
|
|
);
|
|
record(
|
|
"up has no cross-schema references",
|
|
!/\b(?:public|booking|finance|ingestion)\s*\./i.test(upSql)
|
|
);
|
|
record(
|
|
"down has no cross-schema references",
|
|
!/\b(?:public|booking|finance|ingestion)\s*\./i.test(downSql)
|
|
);
|
|
record(
|
|
"no shared-database mutations",
|
|
!/\b(?:CREATE\s+EXTENSION|ALTER\s+(?:ROLE|DATABASE|SYSTEM)|SET\s+ROLE|COPY|GRANT)\b/i.test(
|
|
`${upSql}\n${downSql}`
|
|
)
|
|
);
|
|
record(
|
|
"no cascade or broad conditional drop",
|
|
!/\bCASCADE\b/i.test(`${upSql}\n${downSql}`)
|
|
&& !/\bDROP\b[\s\S]{0,80}\bIF\s+EXISTS\b/i.test(downSql)
|
|
);
|
|
record(
|
|
"up does not delete or drop",
|
|
!/^\s*(?:DELETE|TRUNCATE|DROP)\b/im.test(upSql)
|
|
);
|
|
|
|
const upMutationTargets = [
|
|
...upSql.matchAll(
|
|
/^\s*(?:CREATE\s+(?:TABLE|FUNCTION)|INSERT\s+INTO|UPDATE)\s+([a-z_][a-z0-9_.]*)/gim
|
|
)
|
|
].map(match => match[1].toLowerCase());
|
|
record(
|
|
"all up mutation targets are condon-qualified",
|
|
upMutationTargets.length > 0
|
|
&& upMutationTargets.every(target => target.startsWith("condon.")),
|
|
upMutationTargets.join(", ")
|
|
);
|
|
|
|
const downDropTargets = [
|
|
...downSql.matchAll(
|
|
/^\s*DROP\s+(?:FUNCTION|INDEX|TABLE|SCHEMA)\s+([a-z_][a-z0-9_.]*)/gim
|
|
)
|
|
].map(match => match[1].toLowerCase());
|
|
record(
|
|
"all down drop targets are condon-qualified",
|
|
downDropTargets.length === 16
|
|
&& downDropTargets.every(target => target === "condon" || target.startsWith("condon.")),
|
|
downDropTargets.join(", ")
|
|
);
|
|
|
|
record(
|
|
"expected table count",
|
|
count(upSql, /^CREATE TABLE condon\./gm) === 6
|
|
);
|
|
record(
|
|
"expected function count and security mode",
|
|
count(upSql, /^CREATE FUNCTION condon\./gm) === 3
|
|
&& count(upSql, /^SECURITY INVOKER$/gm) === 3
|
|
&& count(upSql, /^SET search_path = pg_catalog$/gm) === 3
|
|
);
|
|
record(
|
|
"public execute revoked on new functions",
|
|
count(upSql, /^REVOKE ALL ON FUNCTION condon\./gm) === 3
|
|
);
|
|
record(
|
|
"indexes are created only on condon tables",
|
|
count(upSql, /^CREATE INDEX /gm) === 6
|
|
&& count(upSql, /^\s+ON condon\./gm) === 6
|
|
);
|
|
record(
|
|
"foreign keys stay inside condon",
|
|
count(upSql, /^\s+REFERENCES condon\./gm) >= 6
|
|
&& !/^\s+REFERENCES (?!condon\.)/gim.test(upSql)
|
|
);
|
|
|
|
const seededRoomTypes = [
|
|
...upSql.matchAll(/^\s*\('([A-Z0-9]+)',\s*(?:[123]|NULL),\s*(?:true|false)\)[,;]$/gm)
|
|
].map(match => match[1]);
|
|
record(
|
|
"exact room-type seed set",
|
|
JSON.stringify(seededRoomTypes.sort()) === JSON.stringify(
|
|
["AC2", "RM1", "RM2", "RM3", "RM4", "SU1", "SU2", "SU3", "SU6", "UG1", "UG2"].sort()
|
|
)
|
|
);
|
|
record(
|
|
"derived values are function-owned",
|
|
upSql.includes("v_night_count := p_check_out - p_check_in;")
|
|
&& upSql.includes("v_use_nights := v_night_count * v_multiplier;")
|
|
&& upSql.includes("v_balance_after := v_balance_before - v_use_nights;")
|
|
);
|
|
record(
|
|
"down removes only exact new object set",
|
|
count(downSql, /^DROP FUNCTION condon\./gm) === 3
|
|
&& count(downSql, /^DROP INDEX condon\./gm) === 6
|
|
&& count(downSql, /^DROP TABLE condon\./gm) === 6
|
|
&& count(downSql, /^DROP SCHEMA condon;$/gm) === 1
|
|
);
|
|
record(
|
|
"legacy migration stays inside condon",
|
|
!/\b(?:public|booking|finance|ingestion)\s*\./i.test(`${legacyUpSql}\n${legacyDownSql}`)
|
|
&& !/\bCASCADE\b/i.test(`${legacyUpSql}\n${legacyDownSql}`)
|
|
);
|
|
record(
|
|
"legacy migration has booking and source fields",
|
|
/^CREATE TABLE condon\.bookings/gm.test(legacyUpSql)
|
|
&& legacyUpSql.includes("raw_used_room_type")
|
|
&& legacyUpSql.includes("source_sheet")
|
|
&& legacyUpSql.includes("source_sequence")
|
|
&& legacyUpSql.includes("usage_records_confirmation_booking_fk")
|
|
);
|
|
record(
|
|
"legacy migration has 1:N API function",
|
|
/^CREATE FUNCTION condon\.create_usage_record_v2\(/m.test(legacyUpSql)
|
|
&& legacyUpSql.includes("ON CONFLICT (confirmation_no) DO NOTHING")
|
|
&& legacyUpSql.includes("rule_version = 'legacy-source'")
|
|
&& /^REVOKE ALL ON FUNCTION condon\.create_usage_record_v2/gm.test(legacyUpSql)
|
|
);
|
|
record(
|
|
"legacy migration down reverses its objects",
|
|
/^DROP TABLE condon\.bookings;$/gm.test(legacyDownSql)
|
|
&& /^DROP FUNCTION condon\.create_usage_record_v2/gm.test(legacyDownSql)
|
|
&& /^DROP INDEX condon\.usage_records_source_location_key;$/gm.test(legacyDownSql)
|
|
);
|
|
record(
|
|
"delete migration is an internal transactional function",
|
|
/^CREATE FUNCTION condon\.delete_usage_record\(\s*p_usage_record_id uuid\s*\)/m.test(deleteUpSql)
|
|
&& /^RETURNS void$/m.test(deleteUpSql)
|
|
&& /^SECURITY INVOKER$/m.test(deleteUpSql)
|
|
&& deleteUpSql.includes("FOR UPDATE")
|
|
&& deleteUpSql.includes("DELETE FROM condon.entitlement_ledger")
|
|
&& deleteUpSql.includes("UPDATE condon.entitlement_periods")
|
|
&& /^REVOKE ALL ON FUNCTION condon\.delete_usage_record\(uuid\) FROM PUBLIC;$/m.test(deleteUpSql)
|
|
);
|
|
record(
|
|
"delete migration down reverses its function",
|
|
/^DROP FUNCTION condon\.delete_usage_record\(uuid\);$/m.test(deleteDownSql)
|
|
&& !/\bCASCADE\b/i.test(deleteDownSql)
|
|
);
|
|
|
|
for (const check of checks) {
|
|
process.stdout.write(
|
|
`${check.ok ? "PASS" : "FAIL"} ${check.name}${check.ok || !check.detail ? "" : `: ${check.detail}`}\n`
|
|
);
|
|
}
|
|
|
|
const checksum = createHash("sha256").update(upSql).digest("hex");
|
|
process.stdout.write(`migration checksum ${checksum}\n`);
|
|
|
|
if (checks.some(check => !check.ok)) process.exitCode = 1;
|