SQLite CVSTrac

*** 91,96 ****
--- 91,115 ----
  
  *:AUTO_INCREMENT field type. SQLite supports auto_incrementing fields but only if that field is set as "INTEGER PRIMARY KEY".
  
+ _:Oh god no! Stop the evil from spreading! AUTO_INCREMENT is possibly the worst way of doing unique ids for tables. It requires cached per-connection-handle last_insert_id() values. And you're probably already familiar with how much of a hack THAT is.
+ 
+ _:A much better solution would be to give SQLite proper SEQUENCE support. You already have a private table namespace, so using sqlite_sequences to store these wouldn't be such a big deal. This is created when the database is created, and looks something like this, taken from a perl MySQL sequence emulation module.
+ 
+         create table mysql_sequences (
+             sequence_name char(32) not null primary key,
+             sequence_start bigint not null default 1,
+             sequence_increment bigint not null default 1,
+             sequence_value bigint not null default 1
+         )
+ 
+ _:In fact, why don't you just take a look at the original module <HTML><A href="http://search.cpan.org/~adamk/DBIx-MySQLSequence-0.1/MySQLSequence.pm">DBIx::MySQLSequence</A></HTML>. In fact, why don't you just copy that module, and rewrite using code inside the database.
+ 
+ _:The main reason for doing this, is that if you want to insert multiple records which reference each other, and these references are not null, you cannot insert one record until you have inserted the one to which it refers, then fetched the last_insert_id(), added it to the other record, then insert that, and so in. In trivial cases this isn't too bad, but imagine the cases where you have circular references, or don't know the structure of the data in advance at all.
+ 
+ _:With sequence support and access to ids before inserting, there are algorithms to resolve these cases. Without it, you are left with things like just outright suspending contraints checking, inserting everything incorrectly, then hoping you can find all the cases of broken values, and fixing them. Which sucks if you don't know the structure beforehand.
+ 
+ _:To resolve compatibility issues, just do what you do now with the INTEGER PRIMARY_KEY fields with no default, but allow a DEFAULT SEQUENCENAME.NEXTVAL() or something...
+ 
  *:SELECT t1.ID, (SELECT COUNT(*) FROM t2 WHERE t2.ID=t1.ID) FROM t1{linebreak}
  _:In other words, in a subselect backreferencing to a field in its parent select.

sqlite - Unsupported Sql