Real-schema import and round-trip behavior

Phase 53 hardens imports against constructs observed in restored PostgreSQL and SQLite schemas. The fixtures in devdata/ddl-real/ isolate those constructs so failures can be reproduced without production data.

Extension tables and hierarchy inference

A child table is inferred as an identity subtype only when its primary-key column names match the parent primary key. For example, record_extras(record_id) referencing records(id) stays a separate entity because record_id and id differ.

That pair is represented by an identifying relationship with one to zero_or_one cardinality. Import therefore preserves both tables without creating an invalid hierarchy. A hierarchy written explicitly with mismatched primary-key names is still invalid and produces the existing validation diagnostic.

Routine and view ordering

Within one schema, a routine is emitted after any routine its body invokes. Given a_display() calling b_slug(), generated DDL creates b_slug() first even though alphabetical order would put a_display() first.

Only executable identifier calls create dependencies. Text such as 'b_slug()', -- b_slug(), or /* b_slug() */ is ignored. Cyclic routine dependencies retain stable name order, while a view that calls a routine is emitted after that routine. The same ordering applies to full forward generation and migrations that add several routines.

PostgreSQL physical types

PostgreSQL's quoted internal type "char" is distinct from ordinary char(1). Import keeps the physical spelling "char", maps it to logical char(1), and writes "char" again for PostgreSQL. Other targets continue to generate their normal logical char(1) representation.

Physical type arguments may be identifiers as well as numbers or strings. Thus geometry(Point,4326) is stored and rendered canonically as geometry(Point, 4326), and geometry(MultiPolygon,4326) retains MultiPolygon rather than treating it as a string or rejecting it.

Vector dimensions remain part of the physical type. A vector(768) column survives model text, JSON interchange, PostgreSQL generation, and re-import as vector(768). Changing it to vector(1024) is a type change, so migration emits the corresponding column-type alteration.

Extension-owned relations such as spatial_ref_sys are excluded from application catalog imports because the owning extension recreates them. The PostgreSQL type fixture requires PostGIS and pgvector to be installed before it is applied.

Keyword-shaped column names

The DDL reader permits key, primary, unique, check, constraint, foreign, and default at the start of a column definition. For example, CREATE TABLE page_meta (key TEXT PRIMARY KEY, value TEXT) imports key as a column and as the primary-key member.

Those words start table constraints only when the following tokens form a full clause: PRIMARY KEY (, UNIQUE (, CHECK (, CONSTRAINT name, or FOREIGN KEY (. The same distinction is used by the generic, PostgreSQL, MySQL, and SQLite DDL paths. Quoted SQLite identifiers, such as "check", remain column names during cross-dialect fixture imports.

Live round-trip checks

Set BLUEP_TEST_PG=1 to run the PostgreSQL fixture round trips against a reachable server configured by the existing BLUEP_PG_HOST, BLUEP_PG_PORT, and BLUEP_PG_USER variables. Each fixture is imported, regenerated into a fresh database, re-imported, and required to produce an empty diff.

Set BLUEP_TEST_SQLITE=1 to create real SQLite files through sqlite3, import them, generate a second file, and compare the two catalogs. The complete regression suite is in crates/bluep-core/tests/real_schema.rs, with new expected outputs under devdata/golden/real-schema/.

Second real-data round

Phase 54 checks facts that a model-to-model diff could miss when both imports lost the same catalog detail.

Live PostgreSQL and MySQL tests require explicit ports. BLUEP_PG_PORT must not be 5432 or 5433, and BLUEP_MYSQL_PORT must not be 3306, because those ports are reserved for production servers.

PostgreSQL routine return types retain multi-word spellings such as timestamp with time zone, including SETOF and array forms.

MySQL catalog import filters the columns query to base tables. View columns no longer create phantom tables, while view definitions still come from information_schema.views.

Catalog CSV is decoded as UTF-8 by field and invalid input reports its line. PostgreSQL clients request UTF-8 explicitly, bulk CSV keeps edited text, and identifier substitution preserves non-ASCII literals.

Unique constraints and unique indexes retain NULLS NOT DISTINCT. The flag participates in model text, JSON, comparison, migration, and PostgreSQL generation.

Other dialects emit ordinary UNIQUE syntax and report a generation note when the stronger PostgreSQL null semantics cannot be represented.

Index columns retain non-default PostgreSQL operator classes for both columns and expressions. PostgreSQL writes the class after the indexed expression; other dialects omit it with a note.

Routine metadata retains non-default parallel safety, leakproof status, cost, set-returning row estimates, and ordered SET clauses.

Routine setting order is significant. SET search_path, SET TimeZone, and SET ... FROM CURRENT therefore survive DDL import, live import, text, JSON, diff, and regeneration.

Live PostgreSQL import reads installed extensions except plpgsql. Public-schema extensions omit the schema in the model, and extension versions are deliberately not captured.

Generation creates extensions with IF NOT EXISTS. An extension assigned to a non-default schema is emitted after that schema has been created.

When dependencies require it, PostgreSQL orders tables, views, materialized views, and routines across the whole target instead of completing one schema at a time.

Views follow every relation or routine named by their SQL. Routines follow called routines and relation row types named by arguments or return values.

Tables follow routines called by defaults, generated expressions, or checks. SQL text inside string literals and comments does not create an ordering edge.

Independent objects and cycles retain deterministic schema, kind, and name ordering. Targets already valid in the former schema-local order keep byte-identical output.

Scripts that create a SQL-language routine, including a BEGIN ATOMIC body, set check_function_bodies to false once immediately before the first such routine.

The independent catalog fingerprint can compare a source database with a regenerated database without trusting bluep's own importer or diff.

Run it once for each database with the same psql client and compare the captured files:

psql -X -h 127.0.0.1 -p "$BLUEP_PG_PORT" -U postgres -d source_db \
  -q -v ON_ERROR_STOP=1 -f tools/pg-catalog-fingerprint.sql >source.fp
psql -X -h 127.0.0.1 -p "$BLUEP_PG_PORT" -U postgres -d regenerated_db \
  -q -v ON_ERROR_STOP=1 -f tools/pg-catalog-fingerprint.sql >regenerated.fp
diff -u source.fp regenerated.fp

An empty diff means the fingerprinted constraints, indexes, routines, extensions, and relations agree at the PostgreSQL catalog level.

Overloads, composite types, unlogged tables, event triggers, atomic bodies

PostgreSQL routines are identified by their name and input argument types. Overloaded functions therefore coexist as separate model objects, such as fact(text) and fact(jsonb). Model text keeps the argument list in each routine header, while JSON records every signature independently. Dropping one overload produces a signature-qualified statement such as DROP FUNCTION public.fact(jsonb). Routine dependency analysis conservatively treats a call as depending on every overload with that name. MySQL and SQLite generation rejects a model containing overloads and names the affected routine in the error.

Composite types retain their ordered field names and physical types, plus an optional type comment. PostgreSQL generation creates composites alongside enums, before tables and routines that can refer to them. A type comment is emitted with COMMENT ON TYPE. Changing a composite field replaces the composite type during migration. Dialects without composite-type support skip the object and add a generation note.

The unlogged table flag records PostgreSQL's relpersistence = 'u' catalog value. It also round-trips through CREATE UNLOGGED TABLE, model text, and JSON. Changing the flag migrates with ALTER TABLE ... SET UNLOGGED or SET LOGGED. Other dialects leave the table logged and report that the PostgreSQL property was omitted.

Event triggers are target-wide objects because PostgreSQL does not place them in a schema. The model retains the event, qualified function, ordered tag filter, enabled state, and comment. Generation places event triggers after all routines, so their handler functions already exist. A disabled event trigger is followed by ALTER EVENT TRIGGER ... DISABLE. Unsupported dialects skip event triggers with a generation note.

Source origins describe where an object was read, not database semantics. Physical comparison clears origins from extensions and every other origin-bearing object before diffing. A fresh import compared with its source database consequently reports zero database drift.

SQL-standard routine bodies remain distinct from string-literal routine bodies. BEGIN ATOMIC ... END and bare RETURN bodies carry the model option body "atomic". PostgreSQL writes these bodies directly after the routine attributes without dollar quoting. This preserves parse-time function semantics and makes regenerated routines callable. Dialects that cannot represent SQL-standard bodies skip those routines and add a generation note.