Jay, Let me try to "scratch on the wall" one more time and perhaps my point will make its way through.
Notwithstanding your insistence that INT and INTEGER are the same in SQLite, *with respect to use in the PRIMARY KEY definition* there are subtle differences. In the authoritative version of SQLite, an INTEGER PRIMARY KEY is an alias for the rowid but an INT PRIMARY KEY (or smallint or any other flavor of int) is not an alias for the rowid but an "ordinary" column. From the docs: "The special behavior of INTEGER PRIMARY KEY is only available if the type name is exactly "INTEGER" in any mixture of upper and lower case. Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity <http://www.sqlite.org/datatype3.html#affinity> and a unique index, not as an alias for the rowid." http://www.sqlite.org/lang_createtable.html I pointed out above that this subtle behavioral difference in the authoritative version was *not* honored by Adobe in its implementation of SQLite. The CAVEAT: Adobe's departure from the authoritative version in this regard may prove to be a pitfall for anyone who may ever need to share a SQLite database file they have created with someone who is using software based on the Adobe implementation. The RECOMMENDATION: My advice for anyone who may find themselves in that position would be to eschew INT PRIMARY KEY and to use INTEGER PRIMARY KEY. Corollary advice would be to eschew all use of the CREATE TABLE FOO AS SELECT.... syntax because it produces a table with INT PRIMARY KEY definition even if the prototype table had INTEGER PRIMARY KEY. (@Puneet: you see, I am hardly trying to promote the use of CREATE TABLE FOO AS SELECT.. syntax) The SIDEBAR: I am not saying that the CREATE TABLE FOO AS SELECT syntax must do something other than what it is doing now, thought I certainly recommend the architects consider such a change in light of the potential pitfalls of the current behavior in the wider context of "portability" of SQLite database files. I mean cross-implementation portability not cross-platform portability. The EXAMPLE: If you create a database in the authoritative version of SQLite using INT PRIMARY KEY (rather than INTEGER PRIMARY KEY), when you share the database with your Adobe-using affiliate, all hell will break loose. I will repeat the example I gave above: Let's say you had created this table in authoritative SQLite using INT rather than INTEGER PRIMARY KEY: CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT) insert into FOO(1,'Groucho') insert into FOO(2,'Chico') Insert into FOO(999, 'Harpo') And then you have another table MOVIES where FOOID is a foreign key pointing back to FOO.id: MOVIES fooid|moviename 1|Duck Soup 2|Duck Soup 3|Duck Soup ** Note that Harpo's id is 999 in FOO and that table MOVIES contains no such foreign key. Now your Adobe affiliate executes this query: select MOVIES.moviename, FOO.name from MOVIES INNER JOIN FOO ON FOO.id = MOVIES.fooid Unless Adobe has since changed the behavior they told me they were not going to change, the query above will produce this result in Adobe: Duck Soup|Groucho Duck Soup|Chico Duck Soup|Harpo But Harpo should *not* appear in the resultset. Adobe looks for the row in FOO whose rowid =3 and finds Harpo. If Adobe were following authoritative SQLite, it would look for the row in FOO whose "ordinary column" id = 3 and find no such row. As I said, all hell can break loose because the queries don't break -- they simply return the wrong results which on their face may seem plausible and could therefore go undetected as erroneous until well after the damage (whatever it may be) has been done. CONCLUSION: wherever the SQLite architects find opportunities to tighten up behaviors in this nexus, the tightening up effort would be well-spent, IMO. Which brings me back full circle to the subject line of this posting. Regards Tim Romano Swarthmore PA On Tue, Jun 29, 2010 at 12:30 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Tue, Jun 29, 2010 at 11:46:34AM -0400, Tim Romano scratched on the wall: > > > The core concern, at least as I see it, is the undesirable effects of > > sharing data between implementations that do not handle INT and INTEGER > > primary keys compatibly. > > The only known program that can read SQLite database files is SQLite. > In the SQLite world, INT and INTEGER are the same. The only way to > get the SQL generated by CREATE TABLE ... SELECT back out of an > SQLite database is to dump the data into a SQL text file. If you read > that SQL back into SQLite, it will know exactly what to do with it. > > If you read that SQL into any other database, all best are off, and > this is the very least of your compatibility concerns. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > _______________________________________________ > 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
