Getting link tables wrong, and a bizarre apparent bug depending on calling environment of function?

[Simon Brooke]

Hi

I have two tables with a link table between them, establishing a many to many relationship, declared so:

(defentity category

  (pk :id)

  (table :categories)

  (database db-spec)

  (entity-fields :id :description :intro :parent)

  (many-to-many customer :categories_customers {:lfk "category_id" :rfk "customer_id"})

  (many-to-many item :categories_items {:lfk "category_id" :rfk "item_id"}))

(defentity item

  (pk :id)

  (table :items)

  (database db-spec)

  (entity-fields :id :name :description :image :sold :fixedprice :auctstart)

  (has-one image {:fk :image})

  (has-many image {:fk :item_id})

  (many-to-many category :categories_items {:lfk :item_id :rfk :category_id}))

I'm probably doing the join wrong, since I haven't manage to do an insert or update on one table which successfully inserts/updates the link table, so I've defined a third entity specifically for the link table:

(defentity ln-categories-items

  (table :categories_items)

  (database db-spec)

  (has-one item {:fk :item_id})

  (has-one category {:fk :category_id}))

So in order to update an item (the values are coming from a web form with a multi-select in it; when only one category is selected by the user I get back the category number as a string "69"; when multiple categories are selected I get a persistent vector of strings, ["43" "78" "84" "28"]) I have written a function:

(defn store

  "Submit a set of values from which a record might be updated

   (if an id is supplied) or created (otherwise); return a map with

   keys :value (the record stored), :messages (any resulting messages),

   :errors (any resulting errors)."

  [params]

  (timbre/info (str "Persisting item from params: " params))

  (try

    (let [fields (validate (mung-fields params))

          id (:id fields)

          value (if id (modify fields) (create fields))

          message (str "Successfully " (if id "updated" "created")

                       " item " (:name params))

          categories (:categories params)]

      (cond

       (:id value)

       (store-fix-categories (:id value)

                             (if (string? categories)

                               (list categories)

                               categories)))

      {:value (fetch (:id value)) :messages (list message)})

    (catch Exception any

      (throw any)

      {:value params

       :errors (cons "Unable to store record"

                     (show-reasons any))})))

(I apologise for the fact this function is a bit messy - I've hacked and hacked at it trying to isolate the problem)

The function that actually fixes up the link table is then

(defn store-fix-categories

  "Smash and rebuild the item -> category links in the database,

  linking these `categories` to the item with this `item-id`."

  [item-id categories]

  (timbre/info (str "Fixing up categories " categories ": " (type categories)))

  (transaction 

   (delete schema/ln-categories-items (where {:item_id item-id}))

   (map

      #(insert schema/ln-categories-items

              (values {:item_id item-id :category_id  (Integer/valueOf %)}))

    categories)))

Now, the very curious problem: if I call that function from the REPL, it works absolutely reliably:

et-clj.repl=> (use 'et-clj.models.items :reload)

nil

et-clj.repl=> (store-fix-categories 539 ["43" "78" "84" "28"])

2015-Jan-14 21:25:00 +0000 fletcher INFO [et-clj.models.items] - Fixing up categories ["43" "78" "84" "28"]: class clojure.lang.PersistentVector

({:category_id 43, :item_id 539} {:category_id 78, :item_id 539} {:category_id 84, :item_id 539} {:category_id 28, :item_id 539})

However when I call the store method from within the webapp, the 'delete' query happens but the insert queries don't; I get this in the log:

2015-Jan-14 21:40:16 +0000 fletcher INFO [et-clj.models.items] - Persisting item from params: {:save "Save this!", :sold "false", :fixedprice "3850", :image "2260", :description "Marine chronometer No. 374 by Robert Roskell, (1798 -1830) Liverpool, England mounted on gimbals in a mahogany brass bound case. Sotheby's London 4 April 2001 sold another marine chronometer by Robert Roskell No.1082 (lot 220) so if this was one of his last made around 1830 then it is a fair assumption that No. 374 was made around 1820 or before. The reason I bought and offer this marine chronometer for sale is that it appears to be in untouched original condition throughout and that is rare for a chronometer of this age today. The box is a 7.8\" (20 cm) cube. Delivery extra quoted at cost.    ", :categories ["43" "78" "84" "28"], :name "Early nineteenth century Marine Chronometer  ", :id "539"}

2015-Jan-14 21:40:16 +0000 fletcher INFO [et-clj.models.items] - Fixing up categories ["43" "78" "84" "28"]: class clojure.lang.PersistentVector

and I'm left with no records in the link table. Nevertheless, the message that comes back in the result map is "Successfully updated item Early nineteenth century Marine Chronometer", implying that no exception was thrown.

However, most interestingly of all, when I copy and paste the map from the webapp log to the repl, I get this different result:

et-clj.repl=> (store  {:save "Save this!", :sold "false", :fixedprice "3850", :image "2260", :description "Marine chronometer No. 374 by Robert Roskell, (1798 -1830) Liverpool, England mounted on gimbals in a mahogany brass bound case. Sotheby's London 4 April 2001 sold another marine chronometer by Robert Roskell No.1082 (lot 220) so if this was one of his last made around 1830 then it is a fair assumption that No. 374 was made around 1820 or before. The reason I bought and offer this marine chronometer for sale is that it appears to be in untouched original condition throughout and that is rare for a chronometer of this age today. The box is a 7.8\" (20 cm) cube. Delivery extra quoted at cost.    ", :categories ["43" "78" "84" "28"], :name "Early nineteenth century Marine Chronometer  ", :id "539"})

2015-Jan-14 21:41:44 +0000 fletcher INFO [et-clj.models.items] - Persisting item from params: {:name "Early nineteenth century Marine Chronometer  ", :image "2260", :sold "false", :fixedprice "3850", :categories ["43" "78" "84" "28"], :id "539", :description "Marine chronometer No. 374 by Robert Roskell, (1798 -1830) Liverpool, England mounted on gimbals in a mahogany brass bound case. Sotheby's London 4 April 2001 sold another marine chronometer by Robert Roskell No.1082 (lot 220) so if this was one of his last made around 1830 then it is a fair assumption that No. 374 was made around 1820 or before. The reason I bought and offer this marine chronometer for sale is that it appears to be in untouched original condition throughout and that is rare for a chronometer of this age today. The box is a 7.8\" (20 cm) cube. Delivery extra quoted at cost.    ", :save "Save this!"}

ClassNotFoundException korma.sql.engine$sql_set$iter__3836__3840  java.net.URLClassLoader$1.run (URLClassLoader.java:366)

I'm not sure what's happening here. Presumably the ClassNotFound exception is the result of some macro being executed by Korma.

Presumably also, this is arising because I simply don't know how to update link tables in Korma.

Please could some kind person either

1. Point out what's wrong with my function; or, better
   
2. Point me to a tutorial on using link tables with Korma

Many thanks!

[Immo Heikkinen]

Insert queries don't happen because `map` is lazy and you're not using the return value for anything. You should be using `doseq` here because you're only interested in the side effects i.e. db inserts.

ClassNotFound seems to be related to `korma.core/update` but I don't see it used anywhere here. Is it used some other function that is not included in the email (I'm guessing `modify`)?

--
You received this message because you are subscribed to the Google Groups "Korma" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlkorma+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Simon Brooke]

D'oh!

Many thanks!

You received this message because you are subscribed to a topic in the Google Groups "Korma" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlkorma/YxJLtDU9Cys/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlkorma+u...@googlegroups.com.

[Immo Heikkinen]

I forgot to mention that the relationships in entity definitions don't affect inserts in any way, so you need to insert the links manually as you are doing.