Introspection for table check constraints and other details from CREATE TABLE

[simonw]

Split from:

• .transform() drops check constraints and comments #762

If we're going to have .transform() work correctly against more complex schemas we're going to have to parse the CREATE TABLE statement to figure out what check constraints have been defined (and maybe comments as well).

[simonw]

Potential design:

• db.table("my_table").checks - returns a dictionary mapping column names to their check constraints.
• db.table("my_table").table_checks - returns a list of check constraints defined against the table itself

Consider for example:

CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    age INTEGER CHECK (age >= 0),
    price REAL CHECK (price > 0),
    name TEXT,
    CHECK (length(name) > 0),
    CHECK (price > age)
);

This would return for .checks:

{
    "age": "age >= 0",
    "price": "price > 0"
}

And for .table_checks:

["length(name) > 0", "price > age"]

[simonw]

Claude suggested these as harder cases:

CREATE TABLE edgy (
    x INTEGER CHECK (x > 0) CHECK (x < 100),       -- two checks, one column
    y INTEGER CONSTRAINT y_positive CHECK (y >= 0), -- named column check
    CONSTRAINT combo CHECK (x + y < 1000)           -- named table check
);

SQLite named checks are discussed here - https://sqlite.org/forum/info/fea07633d5417e80 - looks like it's a SQL standard thing, but SQLite also sometimes surfaces them in error. messages.

[simonw]

I'm going to go with:

• table.checks - a list of Check() objects
• table.column_checks - a dictionary mapping column names to list of checks (derived from .checks)
• table.table_checks - a list of the checks that are table-level and not column-level

A Check() will have .name (usually blank), .check (the SQL syntax), .column (blank or the column name), and .options (a special thing that extracts the options from a CHECK (x in 'a', 'b', 'c').

[simonw]

Prototype by Claude: https://gist.github.com/simonw/9f104d13ce756fa3a82c4f1699dd086f - https://claude.ai/share/d98870ea-74fb-4e91-9e2c-0720d851c8e3

Run like this:

uv run https://gist.githubusercontent.com/simonw/9f104d13ce756fa3a82c4f1699dd086f/raw/bda34f7b83c41d45a15f57dce79ab9f958591cd1/create_table_parser.py "CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    age INTEGER CHECK (age >= 0),
    price REAL CHECK (price > 0),
    name TEXT,
    CHECK (length(name) > 0),
    CHECK (price > age)
);"

Output:

[Check(check='age >= 0', name='', column='age', options=None),
 Check(check='price > 0', name='', column='price', options=None),
 Check(check='length(name) > 0', name='', column='', options=None),
 Check(check='price > age', name='', column='', options=None)]

And against a more complex example:

uv run https://gist.githubusercontent.com/simonw/9f104d13ce756fa3a82c4f1699dd086f/raw/bda34f7b83c41d45a15f57dce79ab9f958591cd1/create_table_parser.py "CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    age INTEGER CHECK (age >= 0),
    price REAL CHECK (price > 0),
    name TEXT,
    status TEXT CHECK (status IN ('active', 'inactive', 'pending')),
    CHECK (length(name) > 0),
    CHECK (price > age)
);"

Output:

[Check(check='age >= 0', name='', column='age', options=None),
 Check(check='price > 0', name='', column='price', options=None),
 Check(check="status IN ('active', 'inactive', 'pending')", name='', column='status', options=['active', 'inactive', 'pending']),
 Check(check='length(name) > 0', name='', column='', options=None),
 Check(check='price > age', name='', column='', options=None)]