Re: Autoincrement support for Sqlite3

I will supply comments and review via github.

Thanks much for your efforts,
Russ Tyndall
Acceleration.net


On 10/16/2013 10:40 AM, Victor wrote:
> 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
>