[postmodern-devel] Inserting into related tables

[Ivan Boldyrev]

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.