postmodern-insert

Postmodern Examples Table of Contents Dao

Insert and Insert-into

First, without s-sql

(query "insert into 'countries values (920 'Oz' 'Oz Dollars')")

With s-sql

Now using s-sql. Notice there are two ways. First directly setting the columns, using the :set keyword and alternating column names and values. This means that the :insert-into clause expects the arguments to :set to be in multiples of two. Odd arguments will trigger an error.

(query (:insert-into 'countries :set 'id 920 'name "Oz" 'currency "Oz Dollars"))

Notice that a list with two items is a single item, not a multiple of two. You can use sql-compile to deal with this. See this example from the mailing list.

(sql-compile `(:insert-into 'kupci :set ,@(when t (list 'ime "a"))))

The second method, without the :set keyword, uses :select, followed by values.

(query (:insert-into 'countries (:select "Oz" "Oz Dollars")))
Assume you have a plist where the keys are interned that you want to insert as a record. Consider the following:
(query (sql-compile (append `(:insert-into ,table :set) plst)))

See below for further discussion.

With DAOs

Postmodern has an update-dao and an insert-dao function. My tendency is to check and see if the dao that I'm currently dealing with has its primary key set (in which case I am editing an existing object and need to use update-dao. If not, then I'm dealing with a brand new object and will need to set the primary key and use the insert-dao method, using something like this:

(if (id item) (postmodern:update-dao item) (progn (setf (id item) (get-next-id class-name)) (postmodern:insert-dao item)))
In the alternative, you can rely on sequencing to handle the key field.

(defclass test1 () ((id :accessor id :col-type serial :initarg :id) (name :accessor name :col-type text :type text :initarg :name :initform :NULL :documentation "Library Name")) (:documentation "A class to hold information about each library") (:metaclass postmodern:dao-class) (:table-name test-table)(:keys id))
Notice that there is no :initform in the id field. Now we create the table from the class definition.
(postmodern:execute (postmodern dao-table-definition 'test1))
Now create an instance of the class and insert it. 
(let ((item (make-instance 'test1 :name "test-name"))) (postmodern:insert-dao item))

Multiple Row Inserts

The question was asked on the postmodern mailing list whether there is some way in S-SQL to generate INSERT INTO clauses with multiple rows, e.g. "INSERT INTO \"table\" (a, b) VALUES (10, 20), (30, 40)" ? Desired sexp format is `(:insert-into table :set a (10 20) b (30 40)),

The answer was that the functionality already exists, albeit undocumented.

(:insert-rows-into 'table :columns 'a 'b :values '((10 20) (30 40)))

Insert-into

Insert into does not cover all the use cases. You can either use it with a select form, as in:

(query (:insert-into 'test2 (:select '* :from 'test1)))
or with set and alternating field names and values, as in:
(:insert-into 'my-table :set 'field-1 42 'field-2 "foobar")

Alist

What happens if you want to insert from an alist? I usually find alists easier to use in this area than plists, but sql-compile has a better solution for plists so long as you can intern the keys (see below). 

Plist 

What happens if you want to insert from a plist? 
Assume you have a plist where the keys are interned that you want to insert as a record. Consider the following:
(query (sql-compile (append `(:insert-into ,table :set) plst)))

That gives you the opportunity to generalize into something like this:


(defun insert-db-from-plist (table plst)
  "Takes a table and a plist and inserts the plist into the table as a new record."
  (when (stringp table)
    (setf table (intern (string-upcase table))))
     (with-connection (db)
                      (query
                       (sql-compile
                        (append `(:insert-into ,table :set)
                                  (loop for x in plst counting x into y collect
                                        (if (oddp y)
                                            (cond ((symbolp x)
                                                   x)
                                                  ((stringp x)
                                                   (intern (string-upcase x)))
                                                  (t nil))
                                          x)))))))

Update
Comments