Re: Autoincrement support for Sqlite3

Holger Schauer <Holger.Schauer@...> writes:

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

Hi Holger,

Thanks for the info. For whatever reason it did not get to my inbox.

I've created a sample program and offered a possible workaround in a gist:

https://gist.github.com/vityok/6993172

However, I am still looking forward to proper autoincrement support in Sqlite.

Thanks!
Victor