> At the very least, do the initial INSERT and get-last in a > single transaction.
Not necessarily. You can do INSERT and get-last without starting transaction because get-last is per-connection, not per-database. So the only thing that should be taken care of is no inserts are executed on the connection between your initial INSERT and get-last. > Also be careful with triggers. Triggers can muck everything up by > changing the "last." Also false. Triggers don't change the value returned by last_insert_rowid(). Or it's better say they change it but only within the trigger context, when trigger exits value is reverted to that been set before trigger executed. Read http://www.sqlite.org/c3ref/last_insert_rowid.html carefully. Pavel On Thu, Jan 7, 2010 at 11:16 AM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Thu, Jan 07, 2010 at 03:39:42PM +0000, Simon Slavin scratched on the wall: >> >> On 7 Jan 2010, at 1:15pm, Jay A. Kreibich wrote: >> >> > There isn't a correct way of doing this. You need to manually loop >> > over the SELECT, do the INSERT, and call sqlite3_last_insert_rowid() >> > (or the SQL function last_insert_rowid()) after each INSERT to build >> > up a collection of ROWIDs. >> >> One other aspect is this: you are taking what should be an internal >> function of SQLite and using it for your own purposes. > > Both the C and SQL functions are there to be used. Proper foreign > key support is nearly impossible without them. I'd guess that's > why the SQL version of the function exists. > >> This is sometimes a bad idea. > > Well, yes, just about anything can be abused. The sqlite3_sequence > table, for example. > >> If you want to have a column with particular >> values in which you use for your own purposes, make your own column >> and put whatever values into it you want. > > Yes and no. Using the raw ROWID column for application purposes-- > especially foreign keys-- is not a good idea. One .dump or VACUUM > and you're screwed. > > That said, within the SQLite environment the proper and recognized > way of doing unique, auto-generated ID values is using an INTEGER > PRIMARY KEY, and it is well understood that this is a ROWID alias. > If you want to setup foreign key references (the most common reason > to need the generated ID value of a row that was just INSERTed, > and what I assume the OP is trying to do), you need a function like > this or some other way to get the new ROWID-- that is, the new > application defined ID-- for this table. > > So proper use does make some assumptions. For starters, the rows > should be INSERTed into a table that has an INTEGER PRIMARY KEY. > In other words, the function should be thought of as > "sqlite3_last_insert_integer_primary_key()" (but I'm really glad it > isn't named that). > > Second, if you INSERT a record, grab the ROWID/PRIMARY KEY via > sqlite3_last_insert_rowid(), and then INSERT one or more rows > into a different table that contains a foreign key reference > back to the original table, you should do that whole sequence > within a transaction to make sure you get the proper "last" ROWID. > Of course, you should likely be doing that all within a transaction > anyways. At the very least, do the initial INSERT and get-last in a > single transaction. > > Also be careful with triggers. Triggers can muck everything up by > changing the "last." > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > _______________________________________________ > 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
