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

[Rafael Porres Molina]

Hi,

I'm afraid I don't understand how my change would not have fixed the case I mentioned. What I needed is SQLite to behave as other DB's with autoinc PKs and that is exactly what I achieved. Anyway, you're quite right that any change in SQLite generator should give a proper general solution that includes the case that you don't want autoinc with integers and my pull request didn't take that into account, so I understand it cannot be merged as it is now.

SQLite documentation suggests [here](http://www.sqlite.org/lang_createtable.html#rowid) that the only thing that you have to do is to set the data_type to INT rather than INTEGER. I've tested it and it seems to work. I've created the following table:

````sql
CREATE TABLE test_no_auto_int (
  id INT PRIMARY KEY NOT NULL,
  a TEXT
);
````
and it won't accept null values for id

`````
sqlite> insert into test_no_auto_int (a) values (1);
SQL error: test_no_auto_int.id may not be NULL
``````

as it would accept it if we had created with INTEGER

````sql
CREATE TABLE test_no_auto_integer (
  id INTEGER PRIMARY KEY NOT NULL,
  a TEXT
);
`````
``````
sqlite> insert into test_no_auto_integer (a) values (1);
sqlite> select * from test_no_auto_integer;
1|1
`````

We cannot create autoinc PKs with an INT datatype as in the following example

`````sql
CREATE TABLE test_int_auto (
  id INT PRIMARY KEY AUTOINCREMENT NOT NULL,
  a TEXT
);
``````
as SQLite would complain:

``````
SQL error near line 1: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
```````

I would say that my pull request should also contain modifications on [SQL::Translator::Generator::DDL::SQLite::_ipk](https://github.com/dbsrgits/sql-translator/blob/master/lib/SQL/Translator/Generator/DDL/SQLite.pm#L66) subroutine to be correct, as INT and INTEGER are not equally considered by SQLite when creating tables.

I could take a look to that, amend the pull request and reopen it so that you can check if you're more comfortable with the solution.  Or if you want to take another route and I can be of any help, please let me know.

Regards

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