Which seems reasonable. And those who care deeply will have no problem configuring it the specific way they want on their own project. Win-win.

> This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19 (2009-10-14).

'0' and '1', while not ideal, seems fine to maintain? There is not even a true SQLite boolean type.

Then again, I have been subjected to Oracle nonsense for too long and have had to accept all of the boolean alternatives: 0,1,'0','1',Y,N,y,n,YES,NO,T,F, etc

> Instead, you're expected to just use a text column and store a textual timestamp.

You can actually use an integer column and store Unix timestamps (or floats for subsecond accuracy).

But yes, sqlite has very little types support and its default behaviour is very much unityped / dynamically typed which I also dislike. Same with having to enable foreign keys every time you open a connection.

If every time the SQLite team added a better default for something they changed it, we'd be at SQLite 11 by now and every application would contain at least six incompatible versions of SQLite.

numeric 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.

You can be flexible with strict tables, type every column as ANY and you pretty much get back the original behaviour.

SQLite was originally started as a local database library for use during development for times when the main networked database was not available. It used dbm as the underlying storage mechanism, with the dbm API roughly being string keys with string values. ie all underlying values were actually stored as strings. The SQLite code would automatically do conversions - eg the plus operator would convert the strings to int or float, add them, and generate a stringified number as a result. The vast majority of implementation code did not have to care about types, and very local decisions could be made such as in the addition example.

TCL was used as a dev wrapper language at the time, and it functioned the same way.

It was only in mid-2004 that SQLite 3 was released which used its own storage backend, and that allowed for the 5 supported storage types (int64, string, bytes, float, null). It was API compatible (with minor adjustments) with the earlier SQLite 2, so the lack of static typing continued, otherwise everyone would have to rewrite their code. You do get dynamic typing, which hasn't been a problem for the vast majority of SQLite users.

Do remember that SQLite is competition for fopen, not Oracle / Postgres etc. It is trying to make things as effective as possible in that scenario. If you don't want numbers in your string column, then don't do that!

It's even worse than implicit casting, if the value can't be cast to the the column's type, it's just inserted without casting. Eg. into an integer column, '10' -> 10 and '1O' -> '1O' (hint: latter is uppercase o)

SQLite docs: The Advantages Of Flexible Typing: https://sqlite.org/flextypegood.html

IIRC, the project started out as TCL code and it carried that vibe through to what it is today.