3 ms·

You can use strict tables: https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html

by zimmi 6mo ago

pseudalopex 6mo ago
This could enforce dates are strings. They wanted to enforce dates are dates I thought.
- zimmi 6mo ago
  Storing dates as INTEGER (year * 10000 + month * 100 + day, e.g. 20260530) is not so bad. Proper date / timestamp types would be great though.
  - mollerhoj 6mo ago
    "feels like using a 30years old database"
- simonw 6mo ago
  create table events ( id integer primary key, name text not null, event_date text not null check ( -- YYYY-MM-DD event_date glob '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' and date(event_date) is not null and date(event_date) = event_date ) ); In Python that raises this error if the date is invalid: sqlite3.IntegrityError: CHECK constraint failed: event_date glob '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
  - deleted 6mo ago
    [deleted]
  - pseudalopex 6mo ago
    I see. The strict tables page did not mention the date and time functions. Python would show the 1st line always? Or the failed part? This is unreasonable for a very common type I think.
chrismorgan 6mo ago
I don’t like strict tables, because it conflates two concerns, with one somewhat good and one distinctly bad effect (in my assessments). The somewhat good: it gets rid of most of the weak typing. It still coerces, in line with other SQL databases, but at least a column will only store values of one type. Personally I’d prefer to opt out of the coercion. And I don’t think most ways of writing SQL (in applications especially, but also manually) will ever actually trigger the strict differences. So it doesn’t feel like it’s actually particularly useful. The distinctly bad: you’re limited to six datatype names. You may well now want external documentation or load-bearing comments in your schema, and your application code may be hobbled, if it liked to infer types based on the datatype name. For example, in sqlx, SQLite datatype BOOLEAN can automatically map to Rust type bool <https://github.com/transact-rs/sqlx/blob/75bc0487eb661da811bb7a3c5d158f1bd463fef4/examples/sqlite/todos/src/main.rs#L93 https://github.com/transact-rs/sqlx/blob/75bc0487eb661da811b...>. Without that, you have to resort to a variety of less-pleasant techniques, such as selecting `done as "done: bool"` or overriding things in sqlx.toml. I really, really wish they’d implement some form of CREATE TYPE and let that work with strict tables. If I could `CREATE TYPE BOOLEAN FROM INTEGER` and such, I’d be all in on strict tables.

You can use strict tables: https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html

by zimmi 6mo ago

You can use strict tables: https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html

pseudalopex 6mo ago

This could enforce dates are strings. They wanted to enforce dates are dates I thought.

zimmi 6mo ago
Storing dates as INTEGER (year * 10000 + month * 100 + day, e.g. 20260530) is not so bad. Proper date / timestamp types would be great though.
- mollerhoj 6mo ago
  "feels like using a 30years old database"
simonw 6mo ago
create table events ( id integer primary key, name text not null, event_date text not null check ( -- YYYY-MM-DD event_date glob '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' and date(event_date) is not null and date(event_date) = event_date ) ); In Python that raises this error if the date is invalid: sqlite3.IntegrityError: CHECK constraint failed: event_date glob '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
- deleted 6mo ago
  [deleted]
- pseudalopex 6mo ago
  I see. The strict tables page did not mention the date and time functions. Python would show the 1st line always? Or the failed part? This is unreasonable for a very common type I think.

chrismorgan 6mo ago

I don’t like strict tables, because it conflates two concerns, with one somewhat good and one distinctly bad effect (in my assessments). The somewhat good: it gets rid of most of the weak typing. It still coerces, in line with other SQL databases, but at least a column will only store values of one type. Personally I’d prefer to opt out of the coercion. And I don’t think most ways of writing SQL (in applications especially, but also manually) will ever actually trigger the strict differences. So it doesn’t feel like it’s actually particularly useful. The distinctly bad: you’re limited to six datatype names. You may well now want external documentation or load-bearing comments in your schema, and your application code may be hobbled, if it liked to infer types based on the datatype name. For example, in sqlx, SQLite datatype BOOLEAN can automatically map to Rust type bool <https://github.com/transact-rs/sqlx/blob/75bc0487eb661da811bb7a3c5d158f1bd463fef4/examples/sqlite/todos/src/main.rs#L93 https://github.com/transact-rs/sqlx/blob/75bc0487eb661da811b...>. Without that, you have to resort to a variety of less-pleasant techniques, such as selecting `done as "done: bool"` or overriding things in sqlx.toml. I really, really wish they’d implement some form of CREATE TYPE and let that work with strict tables. If I could `CREATE TYPE BOOLEAN FROM INTEGER` and such, I’d be all in on strict tables.