[DBIx-Class-Devel] Re: [sql-translator] People/rporres/sqlite autoincrement (#26)

[Rafael Porres Molina]

Hi,

Sorry for not having commented anything before. I've just seen the discussion and the commit reversion. The way it is now leaves people like me with problems when we want to check that an autoincrement primary key always grows, as we see it in MySQL with InnoDB and MyISAM engines. Being no expert at all in Postgres, I've installed locally and seems to behave as MySQL using SERIAL datatype instead of autoincrements.

The problem comes from SQLite autoincrementing even when it is not told so. If you create a table in SQLite without an autoincrement in the primary key as in the following example

````sql
CREATE TABLE test (
  id integer NOT NULL,
  a integer NOT NULL,
  b integer NOT NULL,
  PRIMARY KEY  (id)
);
````

it will automagically autoincrement values for *id* when you insert

`````
sqlite> insert into test (a, b) values (1,2);
sqlite> insert into test (a, b) values (1,2);
sqlite> select * from test;
1|1|2
2|1|2
`````

which is something quite particular to SQLite, as you won't see that behaviour in other databases such as MySQL or Postgres. The documentation warns that it will perform a bit slower and that you should only use it when needed. 

This problem came as I was expecting increasing values of autoincrement primary keys in SQLite, as I run my test suite against SQLite when developing but it can also work against MySQL with an env variable, which is quite convenient for me.

There should be a way to tell SQL-Translator to behave as other databases do with autoincrement primary keys even if we want the default behaviour to remain as it is now (ignoring is_auto_increment as SQLite will do it for you even if you didn't ask for it). Maybe we could do it with another property like *sqlite_auto_increment* that would only make sense for SQLite as it would be ignored by other producers as far as I know.

If this approach makes sense to you, I can try to implement it and submit a pull request.

---
Reply to this email directly or view it on GitHub:
https://github.com/dbsrgits/sql-translator/pull/26#issuecomment-27630029
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class-devel/attachments/20131102/cdd8107a/attachment.htm