> SQLite4 was an experimental rewrite of SQLite that was active from 2012 through 2014. All development work on SQLite4 has ended. Lessons learned from SQLite4 have been folded into the main SQLite3 product. SQLite4 was never released. There are no plans to revive it. You should be using SQLite3.
I don't see why. It's on the SQLite4 Home page: https://sqlite.org/src4/doc/trunk/www/index.wiki
The OP's link is to the first subpage, The Design of SQLite4. If you're putting it there, you might as well put it on every page.
That’s right, the type you give to a column when creating a table is merely a suggestion. This means at some point, TEXT may make it into a column where a NUMERIC is expected.
This actually caused a large scale problem in production once.
I still love SQLite, it’s the very best at what it does, but I’m a bit more careful now. The same documentation says SQLite uses dynamic typing so that you can “do more things” than with other databases, but for me this is akin to saying you can “do more things with assembly than with a high level language” (in both cases that’s mostly not even true).
I expect my SQL database to keep data from taking the wrong form. It looks like the SQLite 4 rewrite would at least have enabled constraints by default…
I think many would appreciate a “no surprises” strict, type safe mode in SQLite 3, for me this would complete it. Maybe that exists already?
One issue I've run into while using strict tables is that since sqlite does not (yet?) have a dedicated type for timestamps, a driver like github.com/mattn/go-sqlite3 use the typename in the schema to determine when a column can be converted to a native time datatype. But STRICT tables only allow 6 specific typenames: INT, INTEGER, REAL, TEXT, BLOB, ANY
> One issue I've run into while using strict tables is that since sqlite does not (yet?) have a dedicated type for timestamps, a driver like github.com/mattn/go-sqlite3 use the typename in the schema to determine when a column can be converted to a native time datatype. But STRICT tables only allow 6 specific typenames: INT, INTEGER, REAL, TEXT, BLOB, ANY
I wish they’d implement CREATE DOMAIN, like Postgres has. Using it you can define a named custom type from a base type, optionally with associated CHECK constraints. A minimal implementation would skip the constraints (and DEFAULT and collation) and just let you do CREATE DOMAIN timestamp AS INT (or TEXT if you’d rather do it that way), and thereafter STRICT tables could have timestamp as column type
Readit News
It seems this rewrite would not have addressed my biggest gripe with SQLite: Surprisingly, it is not actually type safe.
https://www.sqlite.org/datatype3.htmlThat’s right, the type you give to a column when creating a table is merely a suggestion. This means at some point, TEXT may make it into a column where a NUMERIC is expected.
This actually caused a large scale problem in production once.
I still love SQLite, it’s the very best at what it does, but I’m a bit more careful now. The same documentation says SQLite uses dynamic typing so that you can “do more things” than with other databases, but for me this is akin to saying you can “do more things with assembly than with a high level language” (in both cases that’s mostly not even true).
I expect my SQL database to keep data from taking the wrong form. It looks like the SQLite 4 rewrite would at least have enabled constraints by default…
I think many would appreciate a “no surprises” strict, type safe mode in SQLite 3, for me this would complete it. Maybe that exists already?
One issue I've run into while using strict tables is that since sqlite does not (yet?) have a dedicated type for timestamps, a driver like github.com/mattn/go-sqlite3 use the typename in the schema to determine when a column can be converted to a native time datatype. But STRICT tables only allow 6 specific typenames: INT, INTEGER, REAL, TEXT, BLOB, ANY
I wish they’d implement CREATE DOMAIN, like Postgres has. Using it you can define a named custom type from a base type, optionally with associated CHECK constraints. A minimal implementation would skip the constraints (and DEFAULT and collation) and just let you do CREATE DOMAIN timestamp AS INT (or TEXT if you’d rather do it that way), and thereafter STRICT tables could have timestamp as column type
Previous comment of mine which explains this in more detail: https://news.ycombinator.com/item?id=29367517
It does, somewhat. https://www.sqlite.org/stricttables.html
Added in 3.37.0 (2021-11-27), apparently :-)
Deleted Comment
Deleted Comment
Deleted Comment
The Design of SQLite4 - https://news.ycombinator.com/item?id=31785170 - June 2022 (19 comments)
Work on SQLite4 has concluded - https://news.ycombinator.com/item?id=15648280 - Nov 2017 (157 comments)
SQLite4: The Design of SQLite4 - https://news.ycombinator.com/item?id=10104964 - Aug 2015 (2 comments)
The Design Of SQLite4 - https://news.ycombinator.com/item?id=7797025 - May 2014 (67 comments)
The Design Of SQLite4 (work in progress) - https://news.ycombinator.com/item?id=4168645 - June 2012 (49 comments)
Deleted Comment