[PATCH] Fix problem with auto-inc primary keys

Rupert Swarbrick <[email protected]> Sat, 25 Sep 2010 17:01:24 +0100

Newsgroups	gmane.lisp.clsql.general
Message-ID	<[email protected]>

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

_______________________________________________
CLSQL mailing list
[email protected]
http://lists.b9.com/cgi-bin/mailman/listinfo/clsql

0001-Fix-behaviour-with-auto-inc-primary-keys.patch (text/x-diff, 2.7 KB)

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

signature.asc (application/pgp-signature, 315 B)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iJwEAQECAAYFAkyeHNQACgkQRtd/pJbYVoa00gP/XR68hVZQi4J9k5JqUkLLFZ9i
CxqMQ2QswbXGa6FUhs08EI0/BlFSxIAzOUB7SGxtmvwQnWxBF2j2xaMesK/aBv+j
8Rhueu0hgC/fZ/rMmdmx9+RschuCmRue0va8vqUESCK+0fiACVHkuuKMqMfMHMmT
l6UPsNTL8/5o66rrNZI=
=7Z00
-----END PGP SIGNATURE-----