I'm back, and it's 5am, back from the dark shift :) I chose BIGINT because it matches exactly the internal represention of ROWID (from a mysql perspective, granted). but it wouldn't matter if it didn't anyway.
The only adverse reaction I can see from my patch is it doesn't allow you to then put any other datatype other that INTEGER in the BIGINT PRIMARY KEY (which I was pleased with as it proves the 2 line patch is working exactly as intended and a quick win for me). This is what, I, as a sensible human being would expect because in my head BIGINT is an INTEGER. I suppose that's why I was shocked sqlite didn't *seem* to treat it as such. I suppose though it would break compatibility for people already defining BIGINT PRIMARY KEY and sticking BLOBS and whatever into the field and finding themselves with a datatype error post patch :( Though I think these people don't deserve to design database systems. (thanks drh for the missing jigsaw piece there) My thinking was: Is BIGINT an INTEGER, yes. Do INTEGERS autoincrement in sqlite, YES!. Can a BIGINT autoincrement in sqlite, err, no. :( Found this by drh in the archive... "There is a #define that may interest the original poster. If you compile with -DSQLITE_32BIT_ROWID=1, then rowids are limited to 32 bits. This feature is not particularly well tested (actually, I'm not sure it is tested at all) but it might work. It is worth a try, I suppose." Interesting.... This could also help me in my situation. Perhaps a -DBIGINT_PRIMARY_KEY_AUTOINC patch could also work for people wanting compatibility with other db engines that need BIGINT for 64bit and autoincrementing, just me by the looks of things (might as well call it -DNATHAN) This in no way affects the libdbi issue about easily and efficiently discovering a field that is under the control of sqlite's 64bit autoincrement. I think that got slightly lost in the BIGINT issue. Though looping round and seeing if pk is set more than once is better than nowt i suppose. regards and thanks, I learned things. Nathan Quoting "D. Richard Hipp" <d...@hwaci.com>: > > On Dec 15, 2008, at 11:23 AM, Thomas Briggs wrote: > >> I think Nathan's point is that the integer you get when declaring a >> column INTEGER PRIMARY KEY can hold a 64-bit value anyway, so why >> couldn't it simply be declared differently and behave the same? > > INTEGER PRIMARY KEY is the exception to the rules for SQLite > datatypes. Any other column can hold any datatype, regardless of the > declared datatype. But an INTEGER PRIMARY KEY is different. INTEGER > PRIMARY KEY is an alias for the underlying rowid. It can only hold an > integer. > > If we allowed any integer-typed column that was a PRIMARY KEY to > behave this way, then you would suddenly have many expections to the > rule, rather than just one. And there would be no way to declare a > column that had integer affinity that was the primary key. > > The INTEGER PRIMARY KEY exception was first introduced in SQLite > version 2.2.0, about 7 years ago (2001-12-20). > > > D. Richard Hipp > d...@hwaci.com > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
