Prefer Strict Tables in SQLite(evanhahn.com) |
Prefer Strict Tables in SQLite(evanhahn.com) |
That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!
Another thing I dislike is the lack of timestamp types. Instead, you're expected to just use a text column and store a textual timestamp. Even worse, instead of using ISO, the standard date time functions produce strings on the form "yyyy-mm-dd HH:MM:SS" which you're just supposed to assume are in UTC. Why not at least give us "yyyy-mm-ddTHH:MM:SSZ"? Or, you know, a proper space efficient timestamp data type.
A truly great project, with some truly baffling design decisions.
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.
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
Runtime validation is there to enable when using SQLite in other ways.
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.
There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC.
Which is why I prefer not to use them.
That’s not a type, you just get a numeric-affinity column.
What is least surprising? That INTEGER implicity accepts 'hello world' without error, or that you can't insert such a value unless you use a keyword like NONSTRICT or a type like ANY?
I would wager the vast majority of SQLite users if asked would probably not expect it to work.
> SQLite strives to be flexible regarding the datatype of the content that it stores.
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!
CREATE TABLE users (
user_id CHAR(36) NOT NULL PRIMARY KEY CONSTRAINT user_id_length CHECK (LENGTH(user_id) = 36),
email_address VARCHAR(255) UNIQUE CONSTRAINT email_address_length CHECK (email_address IS NULL OR LENGTH(email_address) < 256),
role UNSIGNED TINYINT(1) NOT NULL CONSTRAINT role_valid CHECK (role >= 0 AND role <= 9)
)
It's a little frustrating that all this extra cruft is necessary to get the world's most popular RDBMS to take data correctness seriously. I hope that some SQLite fork that behaves more like other RDBMSes when it comes to this stuff catches on some day, but the fact that that hasn't happened yet makes me think that the demand isn't there, somehow, unfortunately.
As of January 2006 you could add CHECK constraints using the TYPEOF function to reject that at the SQL level. And it is your own code - there is no server - doing the insertions. As was common back then, protecting you from your own bugs was not a high priority for APIs!