How can I use sequences in SQLite?

Question

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?

Michelle

Why don't you use an existing multiple-db layer like PEAR DB (pear.php.net) instead of writing your own? – Chaco

Because my DB layer does a bit more application-specific conversion and checking stuff. It uses PDO as the next layer, so the most multi-DBMS logic is already provided by PHP. My Database class is the only layer between application objects and PDO. With MDB2, I'd have one additional layer in user code. And MDB2 doesn't seem to be significantly better for my case than PDO. – Michelle

Answer 1

I would use lastInsertRowID. This returns the rowid of the last inserted data (which equals the INTEGER PRIMARY KEY value of that row). You won't need any sequence then.

Asset

While I could do that, it's a greatly different concept than a sequence. I'd like to keep the differences between DBMS to a minimum, so emulating a conventional sequence is preferred. – Michelle

I figured out that not even half of the long-term planned DBMS support real sequences. (PostgreSQL and Oracle do. MySQL, SQLite and MSSQL don't.) So I'm changing my database layer to transparently handle auto-increment columns and use sequence values for them on systems that support them. Where no sequence value was used and NULL was inserted instead, I fetch the last inserted value after the query. It's sufficient to know the new ID value after the SQL statement. – Michelle

Wouldn't this throw off your logic if you planned to use the id in any real way because once you delete rows the id becomes invalid? Better to have a separate serial value for the row-id and an id for your logical entities (eg. product id) - then build the counter table as suggested by @Martin v. Löwis – Athos

Answer 2

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).

Timekeeper

I have a problem with this solution. When one client starts a transaction and updates this value, then another client starts a transaction and wants to update the value, the second fails to update but keeps the transaction. As long as the second doesn't rollback, the first fails to commit and can only rollback, too, so both clients end up having failed altogether, resulting in two angry users where there should have been none at all. I could only start exclusive transactions for the sequence stuff, locking the database against anything else. – Michelle

While this is true, I think it applies for all modifications to the database: sqlite locks are always on the database level. Assuming that a transaction that increments a counter will also make other changes, you need an exclusive lock, anyway. – Hydromagnetics

Answer 3

You can emulate a sequence, first create one table with an auto-incremented primary key column.

CREATE TABLE G_SEQUENCE(
   id INTEGER AUTO_INCREMENT PRIMARY KEY
);

Insert into that table n number of times for the number of sequences you want. If you're worried about performance, then you can cache 100 sequences at a time.

BEGIN TRANSACTION;
insert into G_SEQUENCE default values; 
...
insert into G_SEQUENCE default values; 
COMMIT;

If your sequence table becomes too large - which is a nice problem to have - you can easily get the max id, and delete everything less than it.

Gladdy