Re: Autoincrement support for Sqlite3

On Tue, 08 Oct 2013 16:43:59 +0300, Victor <[email protected]> wrote:

> On Fri, 27 Sep 2013 19:42:52 +0300, Victor <[email protected]> wrote:
>
>> Hi,
>>
>> Thanks a lot for creating such a wonderful and useful library.
>>
>> However, lack of autoincrement fields support for Sqlite3 databases is very inconvenient.
>>
>> I wanted to share a patch (partially it was submitted before) that makes it possible to work with autoincrement fields in Sqlite3 with clsql.
>>
>> Additional modifications might be necessary, but this patch at least makes it possible to insert objects into the database.
>>
>> I hope that CLSQL will have a full support for autoincrement fields in the Sqlite3 databases.
>
>
> 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.
>

Hi all,

Apparently the issue with autoincrement field support in Sqlite3 was
raised a number of times before:

http://lists.b9.com/pipermail/clsql-help/2006-January/000552.html

However, I did not find enough patches that would add a fully
functional autoincrement implementation for Sqlite3 databases.

The problem is that Sqlite expects a very specific autoincrement field
declaration (see http://www.sqlite.org/autoinc.html ):

     INTEGER PRIMARY KEY AUTOINCREMENT

But CLSQL is tuned to work with the AUTO_INCREMENT keyword and it also
provides key declarations in the constraints part of the table
definition.

With some simple hacking and patching I am able to create tables with
CLSQL CREATE-TABLE function:

     (clsql:create-table [item] '(([id] integer :primary-key :autoincrement)
                                  ([name] varchar)))

But this is not so convenient as DEF-VIEW-FROM-CLASS function and it
also requires manually patching CLSQL after it is downloaded by
Quicklisp (which is not good at all).

Could somebody please help making CLSQL produce proper autoincrement
key definition for the Sqlite3 database?

Probably a somewhat easier work-around is to use the ROWID column as a
key. But it requires a view-class to have a slot that is retrieved
 from the database but is not defined in the CREATE TABLE command and
is not assigned on INSERT.

Any help is appreciated.

Thanks,
Victor