Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I use sequences in SQLite?

I'm writing a PHP-based web application that should work with multiple database systems. The most important are MySQL and SQLite but PostgreSQL and others would also be nice. For this I try to use as portable SQL as possible. Where this isn't possible, I have defined some meta words in my queries that are handled by my DB layer and converted to platform-specific SQL commands.

I'm now trying to add sequences support. Every DBMS handles sequences differently, there is no common way to write them in SQL. I have read and understood the way PostgreSQL does it. I have found an interesting solution for MySQL that uses MyISAM tables to escape the isolation constraints of a transaction. After all, sequences are not rolled back with the transaction they're used in and that's exactly what I want. Sequences are supposed to be multi-user safe.

Now I haven't found a solution for SQLite. It lacks built-in sequence support. It doesn't provide ways to store data outside a running transaction. My current implementation is to lock the table far enough to do a SELECT MAX(...) and use that value. But I want to get rid of that entirely. In SQLite, this approach requires locking the whole database!

Does anybody know a solution for this with SQLite?

like image 220
ygoe Avatar asked Dec 19 '10 20:12

ygoe


1 Answers

Just create a regular counter table. On creation of a sequence foo, do

create table foo(value int);
insert into foo(value) values(0);

Then, when asking for the next counter value, do

update foo set value=value+1;

While this gets rolled back when the transaction is aborted, it is multi-user safe: no two users will commit the same number. sqlite implements concurrency with a database lock, so the second writer will block anyway (not just because of the sequence update, but also because of the other changes it wants to make).

like image 184
Martin v. Löwis Avatar answered Sep 21 '22 14:09

Martin v. Löwis