extend doquery to allow parameterised queries
Wed Feb 24 23:55:50 PST 2010 marijnh@gmail.com
* extend doquery to allow parameterised queries
Warning: CRC errors found. These are probably harmless but should be repaired.
See 'darcs gzcrcs --help' for more information.
diff -rN -u old-postmodern/doc/postmodern.html new-postmodern/doc/postmodern.html
--- old-postmodern/doc/postmodern.html 2014-07-30 10:17:36.000000000 -0700
+++ new-postmodern/doc/postmodern.html 2014-07-30 10:17:36.000000000 -0700
@@ -261,11 +261,16 @@
<p class="desc">Execute the given query (a string or a list
starting with a keyword), iterating over the rows in the result.
The body will be executed with the values in the row bound to the
- symbols given in <code>names</code>. For example:</p>
+ symbols given in <code>names</code>. To iterate over a
+ parameterised query, one can specify a list whose car is the
+ query, and whose cdr contains the arguments. For example:</p>
<pre class="code">
(doquery (:select 'name 'score :from 'scores) (n s)
- (incf (gethash n *scores*) s))</pre>
+ (incf (gethash n *scores*) s))
+
+(doquery ((:select 'name :from 'scores :where (:> 'score '$1)) 100) (name)
+ (print name))</pre>
<p class="def">
<span>macro</span>
diff -rN -u old-postmodern/postmodern/query.lisp new-postmodern/postmodern/query.lisp
--- old-postmodern/postmodern/query.lisp 2014-07-30 10:17:36.000000000 -0700
+++ new-postmodern/postmodern/query.lisp 2014-07-30 10:17:36.000000000 -0700
@@ -75,18 +75,29 @@
`(let ((rows (nth-value 1 (query ,query ,@args :none))))
(if rows (values rows rows) 0)))
+
(defmacro doquery (query (&rest names) &body body)
- "Iterate over the rows in the result of a query, binding the given
-names to the results and executing body for every row."
- (let ((fields (gensym))
- (query-name (gensym)))
- `(let ((,query-name ,(real-query query)))
- (exec-query *database* ,query-name
- (row-reader (,fields)
- (unless (= ,(length names) (length ,fields))
- (error "Number of field names does not match number of selected fields in query ~A." ,query-name))
- (loop :while (next-row)
- :do (let ,(loop :for i :from 0
- :for name :in names
- :collect `(,name (next-field (elt ,fields ,i))))
- ,@body)))))))
+ "Iterate over the rows in the result of a query, binding the given
+names to the results and executing body for every row. Query can be a
+string, an s-sql query, or a list starting with one of those, followed
+by the arguments to parameterize the query with."
+ (let* ((fields (gensym))
+ (query-name (gensym))
+ args
+ (reader-expr
+ `(row-reader (,fields)
+ (unless (= ,(length names) (length ,fields))
+ (error "Number of field names does not match number of selected fields in query ~A." ,query-name))
+ (loop :while (next-row)
+ :do (let ,(loop :for i :from 0
+ :for name :in names
+ :collect `(,name (next-field (elt ,fields ,i))))
+ ,@body)))))
+ (when (and (consp query) (not (keywordp (first query))))
+ (setf args (cdr query) query (car query)))
+ (if args
+ `(let ((,query-name ,(real-query query)))
+ (prepare-query *database* "" ,query-name)
+ (exec-prepared *database* "" (list ,@args) ,reader-expr))
+ `(let ((,query-name ,(real-query query)))
+ (exec-query *database* ,query-name ,reader-expr)))))
diff -rN -u old-postmodern/postmodern/tests.lisp new-postmodern/postmodern/tests.lisp
--- old-postmodern/postmodern/tests.lisp 2014-07-30 10:17:36.000000000 -0700
+++ new-postmodern/postmodern/tests.lisp 2014-07-30 10:17:36.000000000 -0700
@@ -99,6 +99,11 @@
(is (= number 55))
(is (string= string "foobar")))))
+(test doquery-params
+ (with-test-connection
+ (doquery ("select $1::integer + 10" 20) (answer)
+ (is (= answer 30)))))
+
(test transaction
(with-test-connection
(execute (:create-table test-data ((value :type integer))))
diff -rN -u old-postmodern/postmodern.asd new-postmodern/postmodern.asd
--- old-postmodern/postmodern.asd 2014-07-30 10:17:36.000000000 -0700
+++ new-postmodern/postmodern.asd 2014-07-30 10:17:36.000000000 -0700
@@ -28,7 +28,7 @@
("query" #+postmodern-use-mop "table"))))))
(defsystem :postmodern-tests
- :depends-on (:postmodern :fiveam :simple-date)
+ :depends-on (:postmodern :fiveam :simple-date :simple-date-postgres-glue)
:components
((:module :postmodern
:components ((:file "tests")))))