[postmodern-devel] Inserting into related tables

[Marijn Haverbeke]

Hi Ivan,

Why don't you just start by looking for the record, and create a new
record only if it is not found? Something like (untested):

(defun get-producer-id (name)
  (or (query (:select 'id :from 'producer :where (:= 'name name)))


On Jan 4, 2008 12:40 PM, Ivan Boldyrev <lispnik at gmail.com> wrote:
> I don't quite understand how to do inserts into related table.
>
> For example, we have table 'items' with field 'producer_id' and table
> 'producer' with 'producer_id' and 'producer_name' fields:
>
> ,----
> | (deftable item ()
> |   ((producer-id :type (or integer db-null)")
> |    ... ; Other fields
> |   )
> |   (:auto-id id))
> |
> | (postmodern:deftable producers ()
> |   ((producer-name :type (string 50)))
> |   (:auto-id producer-id)
> |   (:indices producer))
> |
> | ;; Add unique index manually
> | (postmodern:execute
> |      (:create-unique-index 'producers-name-uniq-idx
> |                            :on 'producers
> |                            :fields 'producer-name))
> `----
>
> When we add new item to the database, producer may exist or may be new.
> We have to check producer's existense and add it to table if required.
> The only safe way to do it seems to be
>
> ,----
> | ;; We ignore error if name exists.  But what about other errors?
> | (ignore-errors
> |   (let ((id (postmodern:next-id 'producers)))
> |             (postmodern:execute
> |              (:insert-into table :set
> |                            'producer-id id
> |                            'producer-name name))))
> |
> | ;; Now get ID to insert into items.producer-id.
> | (postmodern:query
> |     (:select 'producer-id :from table :where (:= 'producer-name name))
> |   :single)
> `----
>
> However, on each insertion we increment producers-producer-id-seq even
> if producer name already exists in the table.  This is somewhat clumsy.
> And faulty query wrapped with ignore-errors doesn't work with transactions.
> Is there any other postmodern-way?
>
> --
> Ivan Boldyrev
>
>                                        XML -- new language of ML family.
> _______________________________________________
> postmodern-devel mailing list
> postmodern-devel at common-lisp.net
> http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
>