Re: [PATCH] Fix problem with auto-inc primary keys

Thanks for the patch. I'll test it and commit it a few days.

Kevin

On Sep 25, 2010, at 10:01 AM, Rupert Swarbrick wrote:

> Hi,
> 
> I've bumped into a problem where, if you have a table with
> auto-incrementing primary keys, the following code doesn't work:
> 
> (let ((clsql:*db-auto-sync* t))
>  (let ((x (make-instance 'foo)))
>    ;; (slot-value x 'id) is unbound/nil here because we
>    ;; didn't set it ourselves
>    (setf (some-slot x) "bar")))
> 
> The problem is that to update the record from the slot, the code has to
> do a sort of "UPDATE mytable WHERE id = 'n' ...", but we don't know 'n'
> so it doesn't work.
> 
> The attached patch seems to fix this behaviour for me.
> 
> Rupert
> 
> From d8855cedd9f803c664283a43212f45a1d46904fd Mon Sep 17 00:00:00 2001
> From: Rupert Swarbrick <[email protected]>
> Date: Sat, 25 Sep 2010 16:42:03 +0100
> Subject: [PATCH] Fix behaviour with auto-inc primary keys.
> 
> This patch sets the slot value of the field containing the primary key
> to the one that the database gave it. Before, it wasn't saved so then
> changes to other slots didn't update the database (since the query
> ended up looking something like "UPDATE blah WHEN id = 'NULL' ...",
> which obviously doesn't work).
> ---
> sql/oodml.lisp |   17 ++++++++++++-----
> 1 files changed, 12 insertions(+), 5 deletions(-)
> 
> diff --git a/sql/oodml.lisp b/sql/oodml.lisp
> index 710e5e8..7bf7d5b 100644
> --- a/sql/oodml.lisp
> +++ b/sql/oodml.lisp
> @@ -265,6 +265,7 @@
>                                          (ordered-class-direct-slots view-class)
>                                          (ordered-class-slots view-class))))
>                (record-values (mapcar #'slot-value-list slots)))
> +
>           (cond ((and (not (normalizedp view-class))
>                       (not record-values))
>                  (error "No settable slots."))
> @@ -285,16 +286,22 @@
>                  (insert-records :into (sql-expression :table view-class-table)
>                                  :av-pairs record-values
>                                  :database database)
> +
>                  (when pk-slot
>                    (if (or (and (listp (view-class-slot-db-constraints pk-slot))
>                                 (member :auto-increment (view-class-slot-db-constraints pk-slot)))
>                            (eql (view-class-slot-db-constraints pk-slot) :auto-increment))
> +                       (unless pk
> +                         (let ((db-pk (car (query "SELECT LAST_INSERT_ID();"
> +                                                  :flatp t :field-names nil
> +                                                  :database database))))
> +                           (setf pk db-pk
> +                                 (slot-value
> +                                  obj (slot-definition-name pk-slot)) db-pk)))
> +
>                        (setf pk (or pk
> -                                    (car (query "SELECT LAST_INSERT_ID();"
> -                                                :flatp t :field-names nil
> -                                                :database database))))
> -                       (setf pk (or pk
> -                                    (slot-value obj (slot-definition-name pk-slot))))))
> +                                    (slot-value
> +                                     obj (slot-definition-name pk-slot))))))
>                  (when (eql this-class nil)
>                    (setf (slot-value obj 'view-database) database)))))))
>     pk))
> -- 
> 1.7.1
> 
> _______________________________________________
> CLSQL mailing list
> [email protected]
> http://lists.b9.com/cgi-bin/mailman/listinfo/clsql