I apologize for top posting and not snipping but a tablet makes that difficult...
The problem with a db-specific function is that it makes a lot of otherwise portable, driver-agnostic sql code not applicable to sqlite4. For example PDO and similar abstraction layers. On Jun 28, 2012 11:32 PM, "Simon Slavin" <slav...@bigfraud.org> wrote: > > On 28 Jun 2012, at 9:05pm, Nico Williams <n...@cryptonector.com> wrote: > > > I think AUTOINCREMENT should imply that the column values a) must be > > INTEGER, b) tracking the max value seen so far. (b) is tricky because > > it's tempting to not require an index on that column unless it's > > constrained to be unique (implied for a primary key), but then, if > > there is no index then ensuring that an autoincrement value is not > > used requires a leap of faith -- but again, if not declared unique > > then I think it's fair to assume that it isn't required to be unique. > > I think SQLite4 will need to support AUTOINCREMENT for INTEGERs because so > many SQL users will assume that it's supported. > > But the use of AUTOINCREMENT for key fields is really something more like > 'always be able to generate a unique value'. There's no reason, for > instance, why it shouldn't work for a TEXT column or even a BLOB. So > support for it can be presented like the max() function: find the highest > value so far, and use it to generate one a little higher. You could even > expose a genuine SQLite4 aggregate function for it, and insist that anyone > who defines a new column type supply the function to be used to generate > the next value. > > This will make life simpler for those working with relational databases, > because it'll let them do > > BEGIN EXCLUSIVE; > SELECT next_key_value(id) FROM authors; <-- store value here > SELECT next_key_value(id) FROM books; <-- store value here > > -- now you already know all the values you need for the rest > INSERT INTO authors (id,name) VALUES (***,'Zenna Henderson'); > <-- use value here > INSERT INTO authors (authorID,title) VALUES (***,'Pilgrimage'); > <-- use value here > INSERT INTO shortStories (bookID,title) VALUES (***,'Shadow on the > Moon'); <-- use value here > END; > > as an alternative to using last_insert_rowid() . > > Simon. > _______________________________________________ > 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
