8

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?

ygoe
  • 18,655
  • 23
  • 113
  • 210
  • 1
    Why don't you use an existing multiple-db layer like PEAR DB (http://pear.php.net/) instead of writing your own? – Pablo Santa Cruz Dec 19 '10 at 20:49
  • 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. – ygoe Dec 23 '10 at 12:15

2 Answers2

4

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

Martin v. Löwis
  • 124,830
  • 17
  • 198
  • 235
  • 3
    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. – ygoe Dec 22 '10 at 21:16
  • 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. – Martin v. Löwis Dec 22 '10 at 22:29
3

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.

UrOni
  • 431
  • 4
  • 9
  • 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. – ygoe Dec 21 '10 at 16:33
  • 2
    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. – ygoe Dec 23 '10 at 12:19
  • 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 – Tommie C. Dec 07 '17 at 18:09