Autogenerated primary key in postgresql with clsql

Question

I'm trying to make a simple database with Common Lisp ORM. I use PostgreSQL and CLSQL. I could create classes and generate tables, but it doesn't work when I want to insert a value without primary key in order to get a generated value. It seems that it works with mysql databases. Is it possible to do that with PostgreSQL?

I define the primary key as:

(id :db-kind :key
    :db-type "serial" 
    :db-constraints (:not-null :unique)
    :type integer
    :initarg :id)

And I get this error:

While accessing database #<POSTGRESQL-DATABASE localhost/cl_ormex/postgres OPEN {1004FCC403}>
  with expression "SELECT currval ('NIL')":
   Error 42P01 / relation "nil" does not exist
   LINE 1: SELECT currval ('NIL')
                           ^  
 has occurred.
   [Condition of type SQL-DATABASE-DATA-ERROR]

I use PostgreSQL 9.5.2 with SBCL 1.3.1.

edit

Here's an example:

(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 person ()
  ((id :db-kind :key
       :db-type "serial"
       :db-constraints (:not-null :unique)
       :type integer
       :initarg :id
       :accessor person-id)
   (name :type (varchar 30)
     :initarg :name
     :accessor person-name)))

(defparameter person1
  (make-instance 'person
         :name "Matt"))

(dolist (c '(person)) (create-view-from-class c))
(update-records-from-instance person1)

I don't really understand this error, but the row seems to be inserted in the database.

A complete, minimal example would be helpful, and the stacktrace. — Svante, Apr 19 '16 at 01:00
Actually, I've noticed, that all the fields are inserted (and also the id), but the id slot is still undefined in the object... — andrei-n, Apr 20 '16 at 17:53

score 3 · Answer 1 · edited Oct 25 '19 at 09:56

3

From Loving Lisp by Mark Watson - db-constraints need to be defined with :auto-increment.
Note - the book version as of today (25/10/2019) is not correct, but the downloaded code is:

(clsql:def-view-class article ()
  ((id
    :db-kind :key
    :db-constraints (:auto-increment :not-null :unique)
    :type integer
    :initarg :id)
   (uri
    :accessor uri
    :type (string 60)
    :initarg :uri)
   (title
    :accessor title
    :type (string 90)
    :initarg :title)
   (text
    :accessor text
    :type (string 500)
    :nulls-ok t
    :initarg :text)))

edited Oct 25 '19 at 09:56

CKE

1,533
19
18
29

answered Oct 25 '19 at 06:58

paulj

31
4

I should also point out that you will need to update the table properties if you created it without the above constraints. In my case, there was no data in it, so I deleted it and re-created it from lisp with the new class details. – paulj Oct 25 '19 at 12:20

score 1 · Accepted Answer · edited Oct 25 '19 at 07:02

It seems that this will not work. it has a todo file that says this:

Test that ":db-kind :key" adds an index for that key. This is complicated by different backends showing autogenerated primary key in different ways.

So maybe it doesn't work with Postgres. I believe that you have many possibilities.

Use other frameworks a little bit more updated like cl-dbi, take a look here:

cl-dbi provides a uniform interface to the various database server-specific libraries (cl-postgres, cl-mysql, etc.). SxQL provides a DSL for building safe, automatically parametrized SQL queries.

There are two fairly complete ORMs: Crane, by yours truly, and Integral, by the author of cl-dbi.

Consolidation:
Discourage using anything other than cl-dbi.

Future Work:

Bindings for other database systems, e.g. Oracle, exist. Writing drivers for cl-dbi would be the best course of action and help consolidation.

I find easy to generate ids that can be ordered in ascending or descending order by creation time easily with timestamp, or also you can use generators or take in account the last number you insert

but this will do trick, the universal time and also add a random number, for creating many entities at the same time and having a low rate of collision

(format nil "~12,'0d-~6,'0d" (get-universal-time) (random 1000000))

Autogenerated primary key in postgresql with clsql

2 Answers2