I am afraid I do not know SQL syntax at all and it looks rather horrifying, but here is a more functional approach:
(= *sql-template*
; first, express semantics with the template structure
'(Study (StudyId (int) studyseq) ;; this will always be the key field.
(StudyCode (string 25)) ;; and always encapsulate the type info in their own list
(StartDate (datetime 8))
(Description (string 25))))
The trick above is to be a little creative on the front-end and express some things with structure that will make the code do less work. I eliminated the pk designator and simply specify the first thing after the table name to be a key designator. If there can be multipel keys, the template would need a list in that position and then the destructuring would be (table keys . otherfields).
It does not come to play, but notice that I wrapped the type info in its own list in case we ever do need to get at it algorithmically.
In the code I can now use destructuring to parse the template (and if I had to dig into the field info I would dstructure those, but here I just use car)
(let (tbl key . fields) *sql-template*
(prn (string "insert into " tbl
" ( " (let c nil
(each (f . rest) fields
(when c (= c (string c ", ")))
(= c (string c f)))
c)
", " (car key)
" ) values ( "
(let c nil
(each (f . rest) fields
(when c (= c (string c ", ")))
(= c (string c ":" f)))
c)
", " (last key) ".nextVal"
" ) returning " (car key) " into " (string ":" (car key)))))
This version uses list splicing:
(let (tbl key . fields) *sql-template*
(prn (apply string `("insert into " ,tbl
" ( " ,@(let c nil
(each (f . rest) fields
(when c (push ", " c))
(push f c))
(rev c))
", " ,(car key)
" ) values ( "
,@(let c nil
(each (f . rest) fields
(when c (push ", " c))
(push ":" c)
(push f c))
(rev c))
", " ,(last key) ".nextVal"
" ) returning " ,(car key)
" into " ,(string ":" (car key))))))
By the way, this is almost a solution in Common Lisp using the format string mini-language:
(destructuring-bind (tbl key . fields)
'(Study (StudyId (int) studyseq) ;; this will always be the key field.
(StudyCode (string 25)) ;; and always encapsulate the type info in their own list
(StartDate (datetime 8))
(Description (string 25)))
(format t "insert into ~a (~{ ~a~^,~}) values (~{ :~a~^,~}) returning ~a into ~a"
tbl
(append (mapcar 'car fields)(list (car key)))
(append (mapcar 'car fields)(list (car (last key))))
(car key) (car key)))
It does not do the nextVal bit, and I would have to put bars around things like |StudyCode| to preserve case. But the neat thing is the ~{~} list expander (like @ in backquoted lists) and the escape thing ~^ that arranges for the comma not to print after the last item.
Isn't destructuring cool? We take an anonymous list and quickly associate useful names with what otherwise would be car, cadr, and caddr, etc.
In common lisp when we use destructuring-bind we can have optional /and/ keyword arguments, so you can plan ahead and create an adhoc list like (1 2 :height 3 :width 4) and let :weight default to 42:
(destructuring-bind (x y &key (height )(width 0)(weight 42))
..etc with x, y, height, width, and weight...)
Hmmm, I think I did that for my Arc implementation of defun...shuffle, shuffle, dig..ah:
(mac defun (name params . body)
(w/uniq (rtargs)
`(def ,name ,rtargs
(withs ,(with (reqs nil key? nil opt? nil keys nil opts nil without)
(each p params
(if (is p '&o) (do (assert (no opt?) "Duplicate &o:" ',params)
(assert (no key?) "&k cannot precede &o:" ',params)
(= opt? t))
(is p '&k) (do (assert (no key?) "Duplicate &k:" ',params)
(= key? t))
key? (push-end p keys)
opt? (push-end p opts)
(do (assert (~acons p) "Reqd parameters need not be defaulted:" p)
(push-end p reqs))))
(with (n -1 kvs (uniq))
(+ (mappend [list _ `(nth ,(++ n) ,rtargs)] reqs)
(mappend [list (carif _) `(or (nth ,(++ n) ,rtargs)
,(cadrif _))] opts)
(list kvs `(pair (nthcdr ,(++ n) ,rtargs)))
(mappend [list (carif _)
`(or (alref ,kvs ',(carif _))
,(cadrif _))] keys)
)))
,@body))))
