Strict mode by default when tables are strict

[aplavin]

SQLite supports "strict" mode since 2021: https://www.sqlite.org/stricttables.html. Can SQLite use its strict mode automatically whenever the sqlite database uses it? Should help type stability and performance.

[metab0t]

I think it is all about SQL?

[aplavin]

I don't understand the question. With strict mode, sqlite enforces actual data types to match table schema. This can be used in SQLite.jl to determine types once for a query, and then read all rows type-stably with fewer julia allocations and better performance.

[metab0t]

It means that SQLite.jl needs to parse the table name(s) from SQL first, runs PRAGMA table_list(tbl) to tell whether the table is strict and PRAGMA schema.table_xinfo(tbl) to get types of all columns. I am afraid that it is not worth the additional complexities for a wrapper of SQLite APIs. Anyway, welcome PR and performance comparisons.