Re: Autoincrement support for Sqlite3

Hi Victor,


>> These modifications are not enough: the way how CLSQL creates tables is also important as it does not work with Sqlite3 when there are Autoincrement fields.
>> 
>> The problem is that CLSQL tries to define primary keys also in the end of the columns list as a kind of constraints.

That's actually been discussed before. When I made some changes to support autoincrement on Postgres over two years ago, I also took a look at supporting sqlite3. This was what I found
(from a mail I send to the mailihg list on 2011-03-10):

> I also tried adding autoincrement support for sqlite3, but eventually
> gave up. Adapting database-last-autoincrement-id is easy enough, but
> supporting table creation is not. sqlite3 takes autoincrement as a
> constraint only for INTEGER PRIMARY KEY column types. However, using
> :type integer and :db-kind :key results in a column type INT (not
> INTEGER) and the primary key constraint doesn't get included in the
> constraint list (it's appended in an ALTER clause, IIRC).
> Additionally, AUTO-INCREMENT would need to be translated (in a
> specialized version of database-constraint-statement) to AUTOINCREMENT
> (instead of AUTO_INCREMENT for mysql). So, supporting autoincrement
> properly on sqlite requires quite a lot of rework.

I haven't looked into the code since then. I think there has been quite a lot of changes, but from what you describe, I guess my findings could still be valid.

With kind regards,

 Holger