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

[sradman]

Great news, domain constraint enforcement is a welcome new feature. However, let's not throw the baby out with the bathwater; being able to specify an ANYTYPE/VARIANT column on a STRICT table would make this feature more useful. The canonical use case for ANYTYPE is a BIGTABLE or Entity–Attribute–Value (EAV) model.

I haven't checked recently, but I was unable to determine the type of ? parameters in SQLite prepared statements. Maybe this is something that can also be accommodated with STRICT tables.

[SQLite → sradman]

In something like this:

CREATE TABLE t1(a INT, b TEXT); INSERT INTO t1(a,b) VALUES(1,'2'); SELECT * FROM t1 WHERE a=?;

The type of the ? is ambiguous. You can say that it "prefers" an integer, but most RDBMSes will also accept a string literal in place of the ?:

SELECT * FROM t1 WHERE a='1'; -- works in PG, MySQL, SQLServer, and Oracle

[sradman → SQLite]

I'm interested in the API interface while the string literal example is more about the SQL language. JDBC has long provided prepared statement metadata that is used extensively by numerous tools.