Sorry for those brutally honest words - and yes, datastore is not well defined.
But look at how homepage introduces SQLite: a "serverless, ... SQL database engine". As said, I believe SQLite could avoid traps by declaring more clearly what it is and what it is *not*. Wikipedia says: it is "an ACID-compliant embedded relational database management system (...)" Then finally: "(...) SQLite uses a dynamically and weakly typed SQL syntax that does not guarantee the domain integrity". I clearly prefer 'weakly typed' (or even type-less) over "dynamically typed" (mentioned in http://www.sqlite.org/datatype3.html ). To me, SQLite is an embedded and weakly typed (or type-less) relational datastore library. It is a light-weight, hybrid in-memory/on-disk system and follows closely SQL syntax and ACID principles. SQlite to me can into a completely "typeless" database like with randomly user defined data type names and typeless views (see below). Consistency is being ignored (often silently!). As a data reader you have to expect any type in every column value. Look at the examples below... This is why to me it's more like a relational data store than a RDBMS. This has some advantages and but also some - often unexpected - disadvantages. Yours, Stefan P.S. Disclaimer: Pls. take into account that I'm actually a promotor of SQLite/Spatialite as a 'desktop format' to easily exchange geospatial data. EXAMPLES SHOWING HOW SQLITE IGNORES DATA TYPES SCHEMA INFORMATION --------------------------------------------------------------------- sqlite> -- Create a table called 'atable': CREATE TABLE atable ( id INTEGER PRIMARY KEY AUTOINCREMENT, anint INTEGER NOT NULL, anumeric NUMERIC, acolumn i_dont_care); => look at datatype name 'i_dont_care' -- insert a valid and an invalid tuple INSERT INTO atable (anint, anumeric, acolumn) VALUES (2, 3, 4); INSERT INTO atable (id, anint) VALUES (1, 2); => Error: PRIMARY KEY must be unique -- show schema information: PRAGMA table_info(atable); 0|id|INTEGER|0||1 1|anint|INTEGER|1||0 2|anumeric|INTEGER|0||0 3|acolumn|i_dont_care|0||0 => Schema says id (pk) column is NULLable (notnull=0)?! -- insert another invalid primary key value sqlite> INSERT INTO atable (id, anint, anumeric, acolumn) VALUES (NULL, 2, 3, 4); => No error although NULL value for id (=primary key)?! SELECT * FROM atable; 1|2|3|4 2|2|3|4 => Ignored NULL value for id and replaced it with autoincrement. -- insert another tuple INSERT INTO atable (anint, anumeric) VALUES ('2', '3'); => no error? SELECT ROWID, TypeOf(id), TypeOf(anint), TypeOf(anumeric), TypeOf(acolumn) FROM atable; 1|integer|integer|integer|integer 2|integer|integer|integer|integer 3|integer|integer|integer|null => Implicit casts from text to integer/numeric have been made. -- insert another tuple INSERT INTO atable (anint, anumeric, acolumn) VALUES ('2.2', '3.3', 4.4); SELECT ROWID, TypeOf(id), TypeOf(anint), TypeOf(anumeric), TypeOf(acolumn) FROM atable; 1|integer|integer|integer|integer 2|integer|integer|integer|integer 3|integer|integer|integer|null 4|integer|real|real|real => No cast is made to integer but to real...!? PRAGMA table_info(atable); => Schema still pretends anint is integer and anumeric is numeric. -- insert another tuple INSERT INTO atable (anint, acolumn) VALUES (2, 'whateveryouwant'); INSERT INTO atable (anint, anumeric, acolumn) VALUES ('whocares?', 'whocares?', 'whocares?'); => Inserts chars everywhere...! SELECT * FROM atable; 1|2|3|4 2|2|3|4 3|2|3| 4|2.2|3.3|4.4 5|2||whateveryouwant 6|whocares?|whocares?|whocares? SELECT ROWID, TypeOf(id), TypeOf(anint), TypeOf(anumeric), TypeOf(acolumn) FROM atable; 1|integer|integer|integer|integer 2|integer|integer|integer|integer 3|integer|integer|integer|null 4|integer|real|real|real 5|integer|integer|null|text 6|integer|text|text|text => -- Create view with cast: CREATE VIEW aview AS SELECT id, CAST(anint AS INTEGER) "anint2" FROM atable; SELECT ROWID, TypeOf(id), TypeOf(anint2) FROM aview; => Scans through all tuples! => anint2 contains all integers as promised => ROWID ignored; no ROWID anymore!? PRAGMA table_info(aview); => View def. casted anint2 to be integer but reports 'unkown' type in schema!? -- Partial solution: CREATE TEMP TABLE atable_tmp AS SELECT id, CAST(anint AS INTEGER) "anint", anumeric, acolumn FROM atable; SELECT ROWID, * FROM atable_tmp; ... SELECT ROWID, TypeOf(id), TypeOf(anint), TypeOf(anumeric), TypeOf(acolumn) FROM atable_tmp; ... 2011/4/17 Pavel Ivanov <paiva...@gmail.com>: > Stefan, > >> SQLite should'nt be called a "SQL database" (as e.g. touted on its homepage). >> Instead it should clearly declare itself as an "In-memory SQL >> Datastore" or a "Data container with SQL capabilities". > > This is quite serious allegations. Making them you should explain what > they are based on. > SQLite is in no way "in-memory", it stores all data to file. And > please explain how "data container" differs from "database". In some > sense all databases are "data containers" because they contain data. > > > Pavel > > > On Sun, Apr 17, 2011 at 8:12 AM, Stefan Keller <sfkel...@gmail.com> wrote: >> Michael and Jay are right about the subtleties on how SQlite >> interprets what is a data type, a primary key and a database schema >> and it's ACID implementation in general. >> >> To me, the main reason - and remedy - of this FAQ is that SQlite >> should'nt be called a "SQL database" (as e.g. touted on its homepage). >> Instead it should clearly declare itself as an "In-memory SQL >> Datastore" or a "Data container with SQL capabilities". >> >> Yours, S. >> >> >> 2011/4/17 Black, Michael (IS) <michael.bla...@ngc.com>: >>> Seems to behave OK for me on 3.7.5 on Windows. What version are you using >>> on what OS with what compile flags? >>> >>> You also "said" it didn't work but you didnt' actually what what you did. >>> >>> Like this... >>> >>> >>> >>> SQLite version 3.7.5 >>> Enter ".help" for instructions >>> Enter SQL statements terminated with a ";" >>> sqlite> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT, >>> ...> OtherColumn INTEGER); >>> sqlite> >>> sqlite> INSERT INTO Tg (TgConfigId) VALUES (1); >>> sqlite> SELECT * FROM Tg WHERE TgConfigId = 1; >>> 1| >>> sqlite> >>> sqlite> SELECT * FROM Tg; >>> 1| >>> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('1'); >>> Error: PRIMARY KEY must be unique >>> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('2'); >>> sqlite> SELECT * FROM Tg; >>> 1| >>> 2| >>> sqlite> SELECT * FROM Tg WHERE TgConfigId = 2; >>> 2| >>> >>> >>> >>> >>> >>> Michael D. Black >>> >>> Senior Scientist >>> >>> NG Information Systems >>> >>> Advanced Analytics Directorate >>> >>> >>> >>> ________________________________ >>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on >>> behalf of Tobias Vesterlund [tobias.vesterl...@ericsson.com] >>> Sent: Saturday, April 16, 2011 12:40 PM >>> To: sqlite-users@sqlite.org >>> Subject: EXT :[sqlite] 'integer' >>> >>> Hi, >>> >>> I ran into something I don't understand, maybe someone here can shed some >>> light on it for me. >>> >>> I have a table named Tg which is created (with tcl) by: >>> >>> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT, >>> OtherColumn INTEGER); >>> >>> If I do: >>> INSERT INTO Tg (TgConfigId) VALUES (1); >>> >>> The following select works: >>> SELECT * FROM Tg WHERE TgConfigId = 1; >>> >>> But if I insert '1' instead I have to select on '1', 1 no longer works. >>> That makes some sense, but not entirely, as what I inserted isn't an >>> integer any longer but a string. Why would I be allowed to insert values >>> with '' in a column that is specified to be an integer? >>> >>> Regards, >>> Tobias >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
