3 ms·
The downside of strict tables is that some data types are not available, such as Date. Strict should really be the default. If a database is shared by multiple
by petilon 3mo ago
The downside of strict tables is that some data types are not available, such as Date.
Strict should really be the default. If a database is shared by multiple applications then you should be able to rely on the declared data type. If one application stores a string into a numeric column that breaks everyone else.
On the other hand, the main use case for SQLite is embedded databases. And that means only one application is using the database. In that scenario being able to evolve the schema (as opposed to creating a new database and copying the data over) can be seen as an advantage. The application's code knows what to expect in each column--including mixed data types.
- Ciantic 3mo agoSQLite has no date data type. Also SQLite has no way to call EXPLAIN on query and get the dummy type name either for arbitrary SELECT query, so you can't even infer it, if you were to use the dummy type name "DATE" or "DATETIME".
- masklinn 3mo ago> some data types are not available, such as Date. That’s not a type, you just get a numeric-affinity column.
- petilon 3mo agoRight, and that's a serious limitation when in strict mode.
- masklinn 3mo agoThe serious limitation is that you create a column as date, you don’t understand what sqlite does with it, and you start storing strings in there, at which point everything is confused. You can use comments to preserve intent in strict mode, and that’s strictly more useful than fuzzy mode: it is richer, it is clearer, and it is no less reliable.
- e2le 3mo ago> The downside of strict tables is that some data types are not available, such as Date. There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC. https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html
- masklinn 3mo agonumeric is not a type it’s an affinity, the underlying types are real and integer. That is why numeric is not valid on strict tables.
- ncruces 3mo agoThe downside is that you loose the place to store the metadata that a column is supposed to store a date. Which is why I prefer not to use them.
- masklinn 3mo agoA comment will do more than a nonsensical type which is not enforced: it won’t have the wrong affinity, it will spell out what the concrete type is, and there’s no limit to what it can specify. Domain types would be the best fix, but I do not think legacy tables are the second best.
- ncruces 3mo agoA comment is not something I can get programmatically through `sqlite3_column_decltype` or `sqlite3_table_column_metadata`. I can use either API to trigger bool and time handling in my Go driver. https://github.com/ncruces/go-sqlite3/blob/main/driver/driver.go https://github.com/ncruces/go-sqlite3/blob/main/driver/drive...
- petilon 3mo agoRight, and that's the problem. There should be DATE and BOOL as well, especially in strict mode.
- 3mo ago