On: Strict Tables – Column type constraints in SQLite - Draft

[simonw]

It looks like this check-in from yesterday contains the implementation and tests: https://sqlite.org/src/info/f9c1d3441b87ee29

As always the TCL tests are worth admiring:

    # STRICT tables have on a limited number of allowed datatypes.
    #
    do_catchsql_test strict1-1.1 {
    CREATE TABLE t1(a) STRICT;
    } {1 {unknown datatype for t1.a: ""}}
    do_catchsql_test strict1-1.2 {
    CREATE TABLE t1(a PRIMARY KEY) STRICT, WITHOUT ROWID;
    } {1 {unknown datatype for t1.a: ""}}
    do_catchsql_test strict1-1.3 {
    CREATE TABLE t1(a PRIMARY KEY) WITHOUT ROWID, STRICT;
    } {1 {unknown datatype for t1.a: ""}}
    do_catchsql_test strict1-1.4 {
    CREATE TABLE t1(a BANJO PRIMARY KEY) WITHOUT ROWID, STRICT;
    } {1 {unknown datatype for t1.a: "BANJO"}}
    do_catchsql_test strict1-1.5 {
    CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f DATE) strict;
    } {1 {unknown datatype for t1.f: "DATE"}}
    do_catchsql_test strict1-1.6 {
    CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f TEXT(50)) WITHOUT ROWID, STRICT;
    } {1 {unknown datatype for t1.f: "TEXT(50)"}}

    do_execsql_test strict1-2.0 {
    CREATE TABLE t1(
        a INT,
        b INTEGER,
        c BLOB,
        d TEXT,
        e REAL
    ) STRICT;
    } {}
    do_catchsql_test strict1-2.1 {
    INSERT INTO t1(a) VALUES('xyz');
    } {1 {cannot store TEXT value in INT column t1.a}}

[yurishimo → simonw]

I listened to a podcast yesterday with the creator of SQLite who said they have billions of test cases running against the project at all times across hardware and software dating back decades at this point.

It really is a great project.

[vessel → yurishimo]

The Untold Story of SQLite With Richard Hipp - CoRecursive Podcast

https://corecursive.com/066-sqlite-with-richard-hipp/