SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)

[Mike Owens]

I coworker pointed me to this thread.

>>>and why it isn't SQL.

>> It isn't SQL simply because SQL won't let you insert text
>> into a numeric field.

> Yup, I have to agree that's pretty crappy. (Makes mental note to limit
> use of SQLite).

Ever heard of check constraints? That's another feature of this
crappy, non-SQL database. They are one of at least three different
approaches you can take to implement strict typing using SQLite's SQL
and C library facilities.

mike at owens:/tmp# ./sqlite3
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> create table stupid(x INTEGER check(typeof(x)=='integer'));
sqlite> create table little(x REAL check(typeof(x)=='real'));
sqlite> create table database(x INTEGER check(typeof(x)!='text'));

sqlite> -- insert text
sqlite> insert into stupid values('SQLite sucks!');
SQL error: constraint failed
sqlite>  insert into stupid values("1");
sqlite> --whattayaknow

sqlite> -- insert text
sqlite> insert into little values('This isn''t SQL!');
SQL error: constraint failed
sqlite> insert into little values(1.0);
sqlite> --how about that

sqlite> -- insert text
sqlite> insert into database values('Dynamic typing just doesn''t work!');
SQL error: constraint failed
sqlite> insert into database values(x'FFFF');
sqlite> -- something's going on here

sqlite> -- so what do we have in the tables?

sqlite> .h on
sqlite> .m col

sqlite> select x, typeof(x) from stupid;
x           typeof(x)
----------  ----------
1           integer

sqlite> -- integers only

sqlite> select x, typeof(x) from little;
x           typeof(x)
----------  ----------
1.0         real

sqlite> -- reals only

sqlite> select x, typeof(x) from database;
x           typeof(x)
----------  ----------
            blob

sqlite> -- anything but text

That darned dynamic typing. It works like almost any other database if
you want it to. (Makes mental note to spend more time reading SQLite
documentation before bashing SQLite.)