wilig

create or replace function update_asset_json(text, text, integer) returns text as $$
import simplejson
from datetime import datetime
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
updated_data = simplejson.loads(args[1])
rs = plpy.execute("select * from assets where id in (%s)" % args[0])
resp = []
for r in rs:
    data = simplejson.loads(r['data'])
    data.update(updated_data) # apply the update(s)

wilig

create or replace function update_asset_json(text, text, integer) returns setof integer as $$
import simplejson
from datetime import datetime
now = datetime.now()
updated_data = simplejson.loads(args[1])
rs = plpy.execute("select id, data from assets where id in (%s)" % args[0])
resp = []
for r in rs:
    data = simplejson.loads(r['data'])
    data.update(updated_data)

wilig

["select * from products where id > ?" 5]

wilig

sequel> (->> (collect :table1 :table2 [:table1/id :table2/name])
	     (filter (and (> :table1/id 5) (< :table1/id 15)))
	     (limit 100)
	     (offset 50)
	     (order-by (asc :table2/name))
	     (to-sql))
["select table1.id, table2.name from table1, table2 WHERE (table1.id > ? AND table1.id < ?) LIMIT 100 OFFSET 50 ORDER BY table2.name ASC" 5 15]

wilig

sequel> (to-sql 
	  (order-by (asc :table2/name) 
		    (offset 50 (limit 100 
				      (filter 
				       (and (> :table1/id 5) (< :table1/id 15)) 
				       (collect :table1 :table2 [:table1/id :table2/name]))))))
["select table1.id, table2.name from table1, table2 WHERE (table1.id > ? AND table1.id < ?) LIMIT 100 OFFSET 50 ORDER BY table2.name ASC" 5 15]

wilig

(defn limit
  [num query]
  (merge-with concat query (stmt " LIMIT " num)))

(defn offset
  [num query]
  (merge-with concat query (stmt " OFFSET " num)))

wilig

(defmacro order-by
  [& forms]
  (let [sorts (butlast forms)
	query (last forms)]
    `(let [~'asc (fn[x# & xs#] (str (str-utils/str-join ", " (map sqlize (conj xs# x#))) " ASC"))
	   ~'desc (fn[x# & xs#] (str (str-utils/str-join ", " (map sqlize (conj xs# x#))) " DESC"))]
       (let [ob# [~@sorts]]
	 (merge-with concat ~query (stmt (str " ORDER BY " (str-utils/str-join ", " ob#))))))))

wilig

sequel> (to-sql (filter (> :table1/id 5) (collect :table1)))
["select * from table1 WHERE table1.id > ?" 5]
sequel> (to-sql (filter (and (> :table1/id 5) (< :table1/id 15)) (collect :table1)))
["select * from table1 WHERE (table1.id > ? AND table1.id < ?)" 5 15]
sequel> (to-sql (filter (or (and (> :table1/id 5) (< :table1/id 15)) (= :table1/id 50)) (collect :table1)))
["select * from table1 WHERE ((table1.id > ? AND table1.id < ?) OR table1.id = ?)" 5 15 50]

wilig

(defn to-sql
  [query]
  (into [(apply str (query :stmt))] (query :vars)))

wilig

(defn- nested
  [query]
  (let [q (reduce #(merge-with concat %1 %2) (stmt "(") query)]
    (merge-with concat q (stmt ")"))))