Re: Autoincrement support for Sqlite3

Hello,

I don't have time to pursue this work and don't have need of it 
personally (we define databases in the database then generate 
clsql-view-classes with clsql-orm).

However, I will offer a few pieces of advice:
  * The constraint name could be manipulated through some combination of 
database-constraint-statement, make-constraints-description (by 
investigating the backend of the current connection). (see 
sql/expressions.lisp)
  * Almost every string written to the database goes through 
database-output-sql which can be specialized on databases of type 
sqlite3 and thus almost everything can be customized to output exactly 
what you need it to.
  * create-view-from-class calls %install-class which in turn calls 
database-generate-column-definition for each column.  The result of this 
is passed to the create-table function that you already have working.  
This can be specialized on database and so could be one entry point to 
accomplishing your goals.

The company I work for Acceleration.net is available for hire if this 
work is important to you.  Anything accomplished would obviously be open 
source and part of clsql moving forward. Otherwise if you do accomplish 
this work, I will be happy to review your work, and merge it when 
ready.  Feel free to push patches / issue pull requests to 
https://github.com/UnwashedMeme/clsql/ for review or merging.

Thank you for pursuing this work and endeavoring to make clsql better.

Cheers and hope this helps,
Russ Tyndall
Acceleration.net



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