///|
fn ident(v : String) -> String {
"\"\{v}\""
}
///|
fn qualified(schema : String, table : String) -> String {
"\{ident(schema)}.\{ident(table)}"
}
///|
fn literal(v : String) -> String {
let escaped = v
.replace_all(old="\\", new="\\\\")
.replace_all(old="\n", new="\\n")
.replace_all(old="\r", new="\\r")
.replace_all(old="'", new="''")
"E'\{escaped}'"
}
///|
fn assert_sql(condition : String, message : String) -> String {
"DO $moonbranch$ BEGIN IF NOT (\{condition}) THEN RAISE EXCEPTION \{literal(message)}; END IF; END $moonbranch$;\n"
}
///|
fn catalog_checks(spec : Spec) -> String {
let sql = StringBuilder()
for table in spec.tables {
let name = qualified(spec.schema, table.name)
let relation = "\{literal(name)}::pg_catalog.regclass"
let columns = table.columns.map(literal).join(",")
let pk = @json.ToJson::to_json(table.key).stringify()
let key_cols = table.key.copy()
for fk in table.foreign_keys {
key_cols.append(fk.columns)
}
let keys = key_cols.map(literal).join(",")
let include_cols = "a.attname IN (\{columns}) AND a.attnum > 0 AND NOT a.attisdropped"
sql.write_string(
assert_sql(
"EXISTS (SELECT 1 FROM pg_catalog.pg_class WHERE oid = \{relation} AND relkind = 'r' AND NOT relispartition AND NOT relhassubclass AND NOT relrowsecurity)",
"unsupported relation kind or row-level policy: \{table.name}",
),
)
sql.write_string(
assert_sql(
"(SELECT count(*) FROM pg_catalog.pg_attribute a WHERE a.attrelid = \{relation} AND \{include_cols} AND a.attgenerated = '' AND a.attidentity = '') = \{table.columns.length()}",
"missing, generated or identity column: \{table.name}",
),
)
sql.write_string(
assert_sql(
"NOT EXISTS (SELECT 1 FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_collation c ON c.oid = a.attcollation WHERE a.attrelid = \{relation} AND a.attname IN (\{keys}) AND (a.atttypid NOT IN (20,21,23,25,1043,2950) OR NOT COALESCE(c.collisdeterministic, true)))",
"unsupported key type or collation: \{table.name}",
),
)
let local_cols = "(SELECT jsonb_agg(a.attname ORDER BY k.ord) FROM unnest(c.conkey) WITH ORDINALITY k(num, ord) JOIN pg_catalog.pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.num)"
let remote = "(SELECT jsonb_agg(a.attname ORDER BY k.ord) FROM unnest(c.confkey) WITH ORDINALITY k(num, ord) JOIN pg_catalog.pg_attribute a ON a.attrelid = c.confrelid AND a.attnum = k.num)"
sql.write_string(
assert_sql(
"EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conrelid = \{relation} AND c.contype = 'p' AND \{local_cols} = \{literal(pk)}::jsonb)",
"primary key differs from recipe: \{table.name}",
),
)
let fk_checks : Array[String] = []
for fk in table.foreign_keys {
let from_cols = @json.ToJson::to_json(fk.columns).stringify()
let to_cols = @json.ToJson::to_json(fk.references).stringify()
let parent = literal(qualified(spec.schema, fk.parent))
fk_checks.push(
"EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conrelid = \{relation} AND c.contype = 'f' AND c.convalidated AND c.confmatchtype = 's' AND c.confrelid = \{parent}::regclass AND \{local_cols} = \{literal(from_cols)}::jsonb AND \{remote} = \{literal(to_cols)}::jsonb)",
)
}
fk_checks.push(
"(SELECT count(*) FROM pg_catalog.pg_constraint c WHERE c.conrelid = \{relation} AND c.contype = 'f') = \{table.foreign_keys.length()}",
)
sql.write_string(
assert_sql(
fk_checks.join(" AND "),
"foreign keys differ from recipe: \{table.name}",
),
)
}
sql.to_string()
}
///|
let pg_settings : String =
#|SET LOCAL search_path = pg_catalog;
#|SET LOCAL standard_conforming_strings = on;
#|SET LOCAL statement_timeout = '30s';
#|SET LOCAL lock_timeout = '5s';
#|SET LOCAL TimeZone = 'UTC';
#|SET LOCAL DateStyle = 'ISO, YMD';
#|SET LOCAL IntervalStyle = 'postgres';
#|SET LOCAL extra_float_digits = 3;
#|SET LOCAL bytea_output = 'hex';
#|
///|
pub let capture_limit : Int32 = 50000
///|
fn lock_list(spec : Spec) -> String {
let names = spec.tables.map(fn(t) { qualified(spec.schema, t.name) })
names.sort()
names.join(", ")
}
///|
/// Read each declared table in one consistent snapshot. Masked columns are
/// replaced in PostgreSQL before any source value reaches the client.
pub fn capture_sql(spec : Spec) -> String raise BranchError {
validate(spec)
let queries : Array[String] = []
for table in spec.tables {
let name = qualified(spec.schema, table.name)
let fields = table.columns
.map(fn(col) {
let expr = match mask_value(spec, table.name, col) {
Some(String(value)) => "\{literal(value)}::text"
Some(Null) => "NULL::text"
_ => "t.\{ident(col)}::text"
}
"\{expr} AS \{ident(col)}"
})
.join(", ")
queries.push(
"SELECT \{literal(table.name)} AS name, COALESCE(json_agg(r), '[]'::json) AS rows FROM (SELECT \{fields} FROM \{name} t LIMIT \{capture_limit + 1}) r",
)
}
let body = queries.join("\nUNION ALL\n")
"BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;\n\{pg_settings}LOCK TABLE \{lock_list(spec)} IN ACCESS SHARE MODE;\n\{catalog_checks(spec)}SELECT json_build_object('tables', json_object_agg(name, rows)) FROM (\{body}) AS snapshot;\nCOMMIT;\n"
}
///|
pub fn check_capture_size(data : Dataset) -> Unit raise BranchError {
for name, rows in data.tables {
require(
rows.length() <= capture_limit,
"capture scan cap exceeded in \{name}; use a smaller source database",
)
}
}