The schema model and normalization

Everything schemashift does works on one in-memory model (model.py), whatever the source: hand-written .sql files, a SQLite database or a PostgreSQL database.

Single path in: DDL text

Both introspectors do not build model objects directly. They produce DDL text and feed it through the same parser as your files:

• SQLite: select sql from sqlite_master is already DDL.
• PostgreSQL: columns are rendered from pg_attribute (format_type, pg_get_expr), constraints from pg_get_constraintdef, indexes from pg_get_indexdef, views from pg_get_viewdef, plus enums, sequences, triggers and comments.

The consequence: "database vs files" is the same comparison as "files vs files", and every spelling difference the server introduces is handled by one normalizer.

Model

Schema holds dicts of Table, Index, View, Enum, Sequence, Trigger plus the set of schema names. A Table has ordered Columns, at most one PrimaryKey, Uniques, Checks and ForeignKeys, SQLite table options (without rowid, strict) and the autoincrement flag. Names in schemas other than public/main are stored as schema.name.

Expressions (defaults, checks, index expressions, partial-index predicates, generated columns) are stored as normalized strings, not trees.

Normalization

normalize(schema, dialect) and the parser together guarantee:

1. Types: aliases collapse (int, int4 to integer; int8 to bigint; bool; character varying(n) to varchar(n); float8, float to double precision; decimal to numeric; timestamp with time zone to timestamptz, ...). Parameters are kept: numeric(10,2). serial/bigserial on PostgreSQL become integer/bigint NOT NULL DEFAULT nextval('t_c_seq'), which is what the catalog reports, and are rendered back as serial.
2. Expressions go through a small Pratt parser and a canonical printer (expr.py): - keywords and identifiers lowercase, quoting only when needed; - redundant parentheses vanish, necessary ones stay: a - (b - c) keeps them, ((a > 0) AND (b < 3)) does not; - casts on literals are dropped ('x'::text, (0)::numeric), as are casts of a column to text/varchar (PostgreSQL adds (col)::text); - x IN ('a','b') equals (x)::text = ANY (ARRAY['a'::text, ...]); - now(), transaction_timestamp() and CURRENT_TIMESTAMP are one thing; - <> and != are one thing. Anything outside that grammar falls back to a whitespace/case-normalized token string.
3. Constraints are compared by definition (signature()), never by name. Unnamed constraints get the PostgreSQL naming convention (t_pkey, t_col_key, t_col_fkey; checks get t_chk_<hash>) only when a name is needed, for example to DROP CONSTRAINT. Names of introspected PostgreSQL constraints are the real ones.
4. Primary key columns are NOT NULL.
5. Order of columns, constraints and indexes within a table is ignored by the diff.
6. Views are token-normalized; WHERE/HAVING/ON predicates go through the expression normalizer and literal casts are dropped.

Worked example

These three declarations of the same column normalize to the same model object:

price numeric(10,2) NOT NULL DEFAULT 0 CHECK (price >= 0)
"price" decimal(10, 2) NOT NULL DEFAULT (0), CONSTRAINT t_price_check CHECK ((price >= (0)::numeric))

and the diff of the two tables is empty, which tests/test_normalize.py asserts.

Validation

After normalization every foreign key must point at an existing table and columns, every constraint and plain index column must exist, and triggers must have a table. SQLite schemas containing enums or sequences are rejected.