[sqlite] Is there a way to "predict" autoincrement rowid range
I have a query "INSERT ... SELECT" and after it performed it I have to to store range of rowids (autoincrement) of the inserted rows. While max(rowid) for right bound seems ok, assuming max(rowid)+1 for the left bound (before the query) depends on the fact whether there were deletes from the table before. If there were deletes, my estimation will differ from the actual value. So is there a correct way to know the range of rowids in this case or just to know for sure "expected" rowid in similar cases? Thanks, Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
Max Vlasov wrote: > I have a query "INSERT ... SELECT" and after it performed it I have to to > store range of rowids (autoincrement) of the inserted rows. While max(rowid) > for right bound seems ok, assuming max(rowid)+1 for the left bound (before > the query) depends on the fact whether there were deletes from the table > before. If there were deletes, my estimation will differ from the actual > value. So is there a correct way to know the range of rowids in this case or > just to know for sure "expected" rowid in similar cases? If by autoincrement you mean a column actually declared with the AUTOINCREMENT keyword, then the next ID is stored in SQLITE_SEQUENCE table. For details, see http://www.sqlite.org/autoinc.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
On Thu, Jan 7, 2010 at 3:56 PM, Igor Tandetnik wrote: > Max Vlasov wrote: > > I have a query "INSERT ... SELECT" and after it performed it I have to to > > If by autoincrement you mean a column actually declared with the > AUTOINCREMENT keyword, then the next ID is stored in SQLITE_SEQUENCE table. > For details, see http://www.sqlite.org/autoinc.html > > Igor Tandetnik > > I thought that direct access to that table is not a good idea. If the format of it is not documented it can change at any moment or at least in the next major version increment (sqlite 4 for example). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
On Thu, Jan 07, 2010 at 03:49:59PM +0300, Max Vlasov scratched on the wall: > I have a query "INSERT ... SELECT" and after it performed it I have to to > store range of rowids (autoincrement) of the inserted rows. While max(rowid) > for right bound seems ok, assuming max(rowid)+1 for the left bound (before > the query) depends on the fact whether there were deletes from the table > before. If there were deletes, my estimation will differ from the actual > value. So is there a correct way to know the range of rowids in this case or > just to know for sure "expected" rowid in similar cases? 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. With "INSERT... SELECT" there are several techniques to make a very good guess, such as setting AUTOINCREMENT and then looking at pre-INSERT and post-INSERT values, but there are some cases when this may not work. In specific: http://www.sqlite.org/autoinc.html AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but [does] not [guarantee] that they will be sequential. (BTW, someone needs to review this section... it looks half-edited.) -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
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
max(rowid) + 1 before the insert is a good approximation for the left bound. But if you want to know the exact value you can remember max(rowid) + 1 before insert and execute this after insert: select min(rowid) from table_name where rowid >= remembered_value It will be guaranteed to give you exact left bound for the insert in case if reading remembered_value and inserting is made in the same transaction. Pavel On Thu, Jan 7, 2010 at 8:02 AM, Max Vlasov wrote: > On Thu, Jan 7, 2010 at 3:56 PM, Igor Tandetnik wrote: > >> Max Vlasov wrote: >> > I have a query "INSERT ... SELECT" and after it performed it I have to to >> >> If by autoincrement you mean a column actually declared with the >> AUTOINCREMENT keyword, then the next ID is stored in SQLITE_SEQUENCE table. >> For details, see http://www.sqlite.org/autoinc.html >> >> Igor Tandetnik >> >> > I thought that direct access to that table is not a good idea. If the format > of it is not documented it can change at any moment or at least in the next > major version increment (sqlite 4 for example). > ___ > 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
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
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. This is sometimes a bad idea. 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. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
Thanks for the answers. At the first place I wanted to use rowid to save space (since rowids always exist). After reading the replies I changed declaration of ID to the one without AUTOINCREMENT and manually fill the consecutive values starting current max(rowid)+1. So rowids still used but now they're under my full control. I have a query "INSERT ... SELECT" and after it performed it I have to to > store range of rowids (autoincrement) of the inserted rows. While max(rowid) > for right bound seems ok, assuming max(rowid)+1 for the left bound (before > the query) depends on the fact whether there were deletes from the table > before. If there were deletes, my estimation will differ from the actual > value. So is there a correct way to know the range of rowids in this case or > just to know for sure "expected" rowid in similar cases? > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
On Thu, Jan 07, 2010 at 03:39:42PM +, 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
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
> 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 wrote: > On Thu, Jan 07, 2010 at 03:39:42PM +, 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
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
On Thu, Jan 07, 2010 at 11:25:28AM -0500, Pavel Ivanov scratched on the wall: > > ?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. True. An SQL transaction isn't strictly required, but it still strikes me as prudent. You definitely want to do it as an "application transaction," in the sense that if you have a threaded application, the sequence of calls needs to happen within a single lock/release sequence over the database connection. But an SQL transaction is not actually required. > > ?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(). They do if you're inside the trigger. And can then change the value returned again, just by exiting. My intention with that statement was to say "be careful of your context because this function and triggers interact in ways you might not expect." Looking at it now, I see my choice of words was not very clear. You are quite correct that, when an INSERT (even with triggers) is all said and done, the value returned by last_insert_rowid() is the one you'd expect. I didn't mean to imply otherwise. -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
Re: [sqlite] Is there a way to "predict" autoincrement rowid range
As mentioned, don't use raw rowid, define a "my_id INTEGER PRIMARY KEY
AUTOINCREMENT". This currently acts as an alias to rowid, but if the
implementation changes in the future, it will continue to work as
documented. So things will be correct, but they might not be as
efficient as before.
Splitting the INSERT and SELECT isn't that bad. Prepare both
statements, then something like:
int rv;
while ((rv = sqlite3_step(select_stmt)) == SQLITE_ROW) {
for (int i = 0; i < sqlite3_bind_parameter_count(insert_stmt)) {
rv = sqlite3_bind_value(insert_stmt, i,
sqlite3_column_value(select_stmt, i));
if (rv != SQLITE_OK) Panic();
}
rv = sqlite3_step(insert_stmt);
if (rv != SQLITE_DONE) Panic();
Remember(sqlite3_last_insert_rowid(db));
rv = sqlite3_reset(insert_stmt);
if (rv != SQLITE_OK) Panic();
}
if (rv != SQLITE_DONE) Panic();
Shouldn't perform much different than the big statement.
-scott
On Thu, Jan 7, 2010 at 7:56 AM, Max Vlasov wrote:
> Thanks for the answers. At the first place I wanted to use rowid to save
> space (since rowids always exist). After reading the replies I changed
> declaration of ID to the one without AUTOINCREMENT and manually fill the
> consecutive values starting current max(rowid)+1. So rowids still used but
> now they're under my full control.
>
> I have a query "INSERT ... SELECT" and after it performed it I have to to
>> store range of rowids (autoincrement) of the inserted rows. While max(rowid)
>> for right bound seems ok, assuming max(rowid)+1 for the left bound (before
>> the query) depends on the fact whether there were deletes from the table
>> before. If there were deletes, my estimation will differ from the actual
>> value. So is there a correct way to know the range of rowids in this case or
>> just to know for sure "expected" rowid in similar cases?
>>
>>
> ___
> 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
