undefined | Better HN

0 pointssamatman2y ago0 comments

> Of course one can program all kind of check constraints, like one can program all kinds of value validations in javascript.

I don't consider this a valid distinction where databases are concerned.

If you define a datatype in an ordinary SQL database, and try to pass it invalid data, it will fail at runtime. How else could it work? There's no compile-time interaction between the value and the database.

If you define a field as BOOLEAN in Postgres, then the value must be 0 or 1, or the database will refuse to write it and return an error. In SQLite this is spelled INTEGER NOT NULL CHECK (col_name = 0 or col_name = 1). More verbose? Yes. Identical semantics? Also yes.

It would certainly be nice if SQLite had a datetime validator that could be used as a check constraint! Hold, up, I got you fam: CHECK(date IS strftime('%Y-%m-%d', date)). If you need a different format, those are,, available.

I guess it has a date type after all! Learn something new every day.

0 comments

exceptione2y ago

Types are a contract, that both parties can understand. This notion is not captured by operational semantics.

Parties that do not read the database contract will get caught by runtime validation. However, any code that targets the database contract could make use of that contract, e.g. with scaffolding. This might enable tighter integration with type checking in the client program.

What you propose is not a contract, your code doesn't understand it, so now you introduce a new problem. (I think that is why the sqlite author doesn't seem to be too enthusiastic about bolting on strictness checks, as its potential is really limited and it contradicts its design).

wizzwizz42y ago

What are operational semantics to you, if not a contract?

asa4002y ago

Heh, I haven't seen that particular datetime constraint before, thanks for that!

samatmanOP2y ago

If there's a risk of properly-formatted but nonetheless invalid dates, like 2024-03-34, one can do this: DATE(date_column, '+0 days') IS date_column). The '+0 days' causes 2024-03-34 to normalize to 2024-04-03 (this is part of the ISO standard!) and therefore the check fails.

Admittedly these sorts of tricks are obscure, if by 'obscure' we mean "you have to feed a search engine a string like 'Validate SQLite datetime' and read some sources". But to reiterate my point slightly differently, the verbosity of these CHECK constraints doesn't indicate that they're doing anything different from a "typed database".

Out of curiosity, I asked ChatGPT, which got the "well formed" version, when I pointed out it would accept 2023-03-34, it gave a correct explanation of what SQLite would do with that date, and suggested `CHECK(date_column = strftime('%Y-%m-%d', date_column))`, which is more satisfying than the other one, and has the same effect. Really gotta keep an eye on the chatbot.

j / k navigate · click thread line to collapse