Arc Forumnew | comments | leaders | submitlogin
generating sql query for c# with arc
3 points by lboard 6112 days ago | 5 comments
hi, i just started learning lisp.. and experimenting it whether i can make use for my daily job.

I wrote some code to generate sql statement, to use with c#, but it seems to be very harder than writing in c#. It also doesnt look neat. I want to know how you people would make this code look cool and short.

  ;;; table metadata

  (= tbl
   '(Study              ; table name
     (StudyId int pk studyseq)
     (StudyCode string 25)
     (StartDate datetime 8)
     (Description string 25)))

  ;; this code generates the insert statement 
  ;; for the above table

   (do
       (pr "insert into " (car tbl) " ( ")
    (with (l1 "" l2 "" pk nil) 
          (each field (cdr tbl)
                (if (find 'pk field)
                    (= pk field)
                    (do 
                        (+= l1 (string (car field)) ", ")
                        (+= l2  ":" (string (car field)) ", "))))
          (sref l1 #\Space (- (len l1) 2))
          (sref l2 #\Space (- (len l2) 2))
          (pr l1)
          (unless (is pk nil)
            (pr ", " (string (car pk))))
          (pr " ) values ( " l2)
          (unless (is pk nil)
            (pr ", " (string (last pk) ".nextval")))
          (pr ")")
          (unless (is pk nil)
            (pr " returning " (string (car pk)) " into :"
                (string (car pk)))))
  nil)
output is : insert into Study ( StudyCode, StartDate, Description , StudyId ) values ( :StudyCode, :StartDate, :Description , studyseq.nextval) returning StudyId into :StudyId


4 points by kennytilton 6112 days ago | link

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))))))

-----

4 points by kennytilton 6112 days ago | link

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.

So what does the C# version look like?

-----

2 points by lboard 6111 days ago | link

i think destructuing will help a lot for these kind of things. I learned from u, thanks.

-----

5 points by kennytilton 6109 days ago | link

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))))
I'll carve out a dsb tomorrow.

-----

2 points by almkglor 6112 days ago | link

I suggest you use real tables, and iterate using ontable:

  (= tbl
    (fill-table (table)
      (list
        ''name 'Study ; to avoid polluting the field namespace
        'StudyId '(int pk studyseq)
        'StudyCode '(string 25)
        'StartData '(datetime 8)
        'Description '(string 25))))

  ;I assume you want a string to be passed elsewhere later?
  (tostring
   (pr "insert into " (tbl ''name) " ")
   (with (fields nil pk-fields nil pk-values)
     (ontable field type tbl
      (when (alist type)
       (if (some 'pk type)
         (do
           (push field pk-fields)
           (push (last type) pk-values))
         (push field fields))))
     ; push creates the list in reverse order, so
     ; zap the lists with our rev-stick
     (zap rev pk-fields)
     (zap rev pk-values)
     (zap rev fields)
     (pr " ( ")
     (when fields
       (prall fields) (pr ","))
     (prall pk-fields)
     (pr " ) ")
     (pr "values ( ")
     (when fields
       (prall (map [string ":" _] fields)) (pr ","))
     (prall (map [string _ ".nextval"] pk-values))
     (pr " ) ")
     (pr " returning " (car pk-fields) " into :" (car pk-fields))))
I also suggest you get nex-3's arc-wiki.git:

http://arclanguage.com/item?id=833

Among the amenities is a "not-bad" (help ...) function which will probably also help you explore the non-pg-documented parts of the language.

-----