Re: Autoincrement support for Sqlite3

Also it seems like oodml.lisp:update-auto-increments-keys should 
probably be genericized and improved upon in order to support all the 
various db-backend specific methods of getting the inserted id.

There is a different approach to solving this same problem in 
clsql-helper/db-object.lisp that I only recently pushed but has been in 
our code for years.  It would be nice to reconcile this with clsql 
internals.

Cheers,
Russ Tyndall
Acceleration.net

https://github.com/AccelerationNet/clsql-helper/blob/master/db-object.lisp

On 10/15/2013 11:24 AM, Victor wrote:
> 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
>
> _______________________________________________
> CLSQL mailing list
> [email protected]
> http://lists.b9.com/cgi-bin/mailman/listinfo/clsql