CLSQL + PostgreSQL: insert with a foreign key

andrei.n · 04-21-2016, 03:26 AM

I am trying to learn to use the CLSQL ORM for Common Lisp.

Here is the code.

Code:

(require 'clsql)
(defpackage :orm-ex (:use :cl :clsql))
(in-package :orm-ex)
(file-enable-sql-reader-syntax)
(enable-sql-reader-syntax)
(setf *default-caching* nil)
(connect '("localhost" "examp" "postgres" "postgres")
	 :database-type :postgresql)

(def-view-class master ()
  ((master#id :db-kind :key
	      :db-type "serial"
	      :db-constraints (:not-null :unique)
	      :type integer
	      :initarg :master#id
	      :accessor master-id)
   (master#name :type (varchar 30)
		:initarg :master#name
		:accessor master-name)))

(def-view-class slave ()
  ((slave#id :db-kind :key
	     :db-type "serial"
	     :db-constraints (:not-null :unique)
	     :type integer
	     :initarg :slave#id
	     :accessor slave-id)
   (slave#name :type (varchar 30)
	       :initarg :slave#name
	       :accessor slave-name)
   (slave#master :type integer
		 :accessor slave-master
		 :initarg :slave#master)
   (master :accessor master-acc
	   :db-kind :join
	   :db-info (:join-class master :home-key slave#master
			      :foreign-key master#id
			      :set nil))))

(defparameter master1
  (make-instance 'master
		 :master#name "Matt"))

(defparameter slave1
  (make-instance 'slave
		 :slave#name "Alex"
		 :master-acc master1))

(dolist (c '(master slave)) (create-view-from-class c))
(update-records-from-instance master1)
(update-records-from-instance slave1)

I get two problems with this code:

I get an error on insert:

Code:

While accessing database #<POSTGRESQL-DATABASE localhost/examp/postgres OPEN {10045D0443}>
  with expression "SELECT currval ('NIL')":
  Error 42P01 / relation "nil" does not exist
LINE 1: SELECT currval ('NIL')

But the object is inserted. And it seems it's possible to insert the same object twice and it will have different id in the database, which is also a problem.

When I insert the object with the foreign key, it doesn't get inserted into the database. I have no idea how I can do that.

I thought perhaps I can insert it manually, just set the slave#master slot in the :before method of the accessor:
Code:
```
(defmethod (setf slave-acc) :before (m (s slave))
  (setf (slave-master s) (master-id m)))
```
, but I don't have access to the id of the object just inserted. If I do
Code:
```
(master-id master1)
```
or
Code:
```
(slot-value master1 'master#id)
```
, I get the error
Code:
```
The slot ORM-EX::|MASTER#ID| is unbound in the object
#<MASTER {1004545553}>.
```

So I cannot understand how to use this ORM. Is there a way to solve these problems?

Thank you.

sundialsvcs · 04-21-2016, 08:47 AM

I'm not quite sure how to do this with Postgres, but the first thing that I would seek to do is to ask the database server what SQL it actually sees. Since you're dealing with an ORM [i](and an ORM in Lisp!), it can be a bit difficult to see exactly what the ORM is producing. (In general, ORMs can act strangely if you don't yet know what you're doing, since building error-tests into an ORM greatly increases its complexity.)

It sounds like you have set up either the ORM, or the database schema itself, to auto-generate values for primary keys. You can do this, of course. If you want to prevent the insertion of duplicates of a particular set of columns ("tuple" ...), you'll need to define a UNIQUE INDEX on that set of columns.

When inserting into a table having a foreign-key constraint, remember that you cannot insert a record into a child table unless corresponding keys exist in all masters. This is the case no matter what language you're using to produce the queries.

andrei.n · 04-25-2016, 02:15 AM

In the end I found a solution by doing it almost manually by sending several "query" requests. First I get the serial sequence for the table:

Code:

(defvar client_serial_seq)
(setf client_serial_seq (caar (query
       "select pg_get_serial_sequence('client_r', 'id');")))

And then I insert the tuple and retrieve id with a postgresql function currval that takes the serial sequence as a parameter:

Code:

;; insert client
(insert-records :into [client_r] 
                :attributes '(name)
                :values '("johnny"))

(defvar client-id)
(setf client-id (caar (query (concatenate 'string "select currval('" client_serial_seq "')"))))

In the end it's not an ORM anymore since I don't use objects in this code...