Re: Autoincrement support for Sqlite3

Hi Russ,

Thanks for your advice. I was finally able to make autoincrement to work
to a degree when a very simple and basic test passes (from the gist)

The changes are here:

https://github.com/vityok/clsql/commit/4332610851d855ebc858f231f06cff81c675e5f2

Sample gist is here:

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

I will submit a pull request to merge in these changes. Please modify
them to better fit the rest of the library.

Thanks,
Victor


On Tue, 15 Oct 2013 20:17:29 +0300, Russ Tyndall <[email protected]> wrote:

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


-- 
Tu ne cede malis, sed contra audentior ito --- LvM