Re: Sequences and primary keys

On 3/29/2010 8:10 PM, Patrick May wrote:
> On Mar 29, 2010, at 5:08 PM, Nathan Bird wrote:
>   
>> On 3/29/2010 3:40 PM, Patrick May wrote:
>>     
>>> 	I'm working against a database that uses sequences to automatically generate primary keys for some tables.  How can I refresh my view object with the primary key after evaluating update-records-from-instance?  I can't just query for the max id, because another row might have been inserted in the meantime.
>>>       
>> CLSQL currently has no generic system for doing this. On :mysql backends
>> you can declare the view-class column as :auto-increment and it will
>> work. Elsewhere I've defined (defmethod update-records-from-instance
>> :after ...) methods that do the query and updates the object-- what that
>> query will be is different for every backend.
>>
>> I have a branch trying to abstract the auto-increment functionality
>> across the different backends since all of them provide something
>> similar along these lines but with different syntax in each case. If you
>> want to look at that and try to develop it further it is in the
>> auto-increment branch
>> http://git.b9.com/?p=clsql.git;a=shortlog;h=refs/heads/auto-increment
>>     
> 	Thanks, I'll take a look at it.  I'm using PostgreSQL and the SERIAL type for the database column, so hopefully autoincrement ought to be straightforward.
>
>   

One of the tricky issues I found is that some databases (e.g. mysql)
treat this as constraint on the column, while others treat this as the
datatype (e.g. postgresql, mssql).

Here's some postgres code you can mine, I've have this in the individual
project with a few other tweaks so you will need to modify it:

(defmethod clsql:update-record-from-slots :after ((obj pg-db-obj) slots
&key clsql:database)
  "After effecting the database record, if the key-slot is empty then use
curval(sequence) to fill it. If > 1 key-slot, this won't do anything."
  (arnesi:when-bind key-slots
      (clsql-sys::key-slots (class-of obj))
    (if (= 1 (length key-slots))
    (let ((key-slot-name (sb-mop:slot-definition-name
                  (first key-slots))))
      (unless (and (slot-boundp obj key-slot-name)
               (slot-value obj key-slot-name))
        (setf (slot-value obj key-slot-name)
          (car (clsql-sys:query
            #?"SELECT currval('${(clsql:view-table (class-of
obj))}_id_seq')"
            :flatp t
            :database
            (clsql-sys::choose-database-for-instance
             obj
             clsql-sys:database)))))))))