1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; *************************************************************************
3 ;;;; FILE IDENTIFICATION
6 ;;;; Purpose: High-level SQL interface
7 ;;;; Authors: Kevin M. Rosenberg based on code by Pierre R. Mai
8 ;;;; Date Started: Feb 2002
12 ;;;; This file, part of CLSQL, is Copyright (c) 2002-2004 by Kevin M. Rosenberg
13 ;;;; and Copyright (c) 1999-2001 by Pierre R. Mai
15 ;;;; CLSQL users are granted the rights to distribute and use this software
16 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
17 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
18 ;;;; *************************************************************************
20 (in-package #:clsql-sys)
23 (defun map-query (output-type-spec function query-expression
24 &key (database *default-database*)
26 "Map the function over all tuples that are returned by the query in
27 query-expression. The results of the function are collected as
28 specified in output-type-spec and returned like in MAP."
29 ;; DANGER Will Robinson: Parts of the code for implementing
30 ;; map-query (including the code below and the helper functions
31 ;; called) are highly CMU CL specific.
32 ;; KMR -- these have been replaced with cross-platform instructions above
33 (macrolet ((type-specifier-atom (type)
34 `(if (atom ,type) ,type (car ,type))))
35 (case (type-specifier-atom output-type-spec)
37 (map-query-for-effect function query-expression database types))
39 (map-query-to-list function query-expression database types))
40 ((simple-vector simple-string vector string array simple-array
41 bit-vector simple-bit-vector base-string
43 (map-query-to-simple output-type-spec function query-expression database types))
45 (funcall #'map-query (cmucl-compat:result-type-or-lose output-type-spec t)
46 function query-expression :database database :types types)))))
48 (defun map-query-for-effect (function query-expression database types)
49 (multiple-value-bind (result-set columns)
50 (database-query-result-set query-expression database :full-set nil
54 (do ((row (make-list columns)))
55 ((not (database-store-next-row result-set database row))
58 (database-dump-result-set result-set database)))))
60 (defun map-query-to-list (function query-expression database types)
61 (multiple-value-bind (result-set columns)
62 (database-query-result-set query-expression database :full-set nil
66 (let ((result (list nil)))
67 (do ((row (make-list columns))
68 (current-cons result (cdr current-cons)))
69 ((not (database-store-next-row result-set database row))
71 (rplacd current-cons (list (apply function row)))))
72 (database-dump-result-set result-set database)))))
75 (defun map-query-to-simple (output-type-spec function query-expression database types)
76 (multiple-value-bind (result-set columns rows)
77 (database-query-result-set query-expression database :full-set t
82 ;; We know the row count in advance, so we allocate once
84 (cmucl-compat:make-sequence-of-type output-type-spec rows))
85 (row (make-list columns))
87 ((not (database-store-next-row result-set database row))
89 (declare (fixnum index))
90 (setf (aref result index)
91 (apply function row)))
92 ;; Database can't report row count in advance, so we have
93 ;; to grow and shrink our vector dynamically
95 (cmucl-compat:make-sequence-of-type output-type-spec 100))
96 (allocated-length 100)
97 (row (make-list columns))
99 ((not (database-store-next-row result-set database row))
100 (cmucl-compat:shrink-vector result index))
101 (declare (fixnum allocated-length index))
102 (when (>= index allocated-length)
103 (setq allocated-length (* allocated-length 2)
104 result (adjust-array result allocated-length)))
105 (setf (aref result index)
106 (apply function row))))
107 (database-dump-result-set result-set database)))))
109 (defmacro do-query (((&rest args) query-expression
110 &key (database '*default-database*)
113 (let ((result-set (gensym))
117 `(let ((,db ,database))
118 (multiple-value-bind (,result-set ,columns)
119 (database-query-result-set ,query-expression ,db
120 :full-set nil :types ,types)
123 (do ((,row (make-list ,columns)))
124 ((not (database-store-next-row ,result-set ,db ,row))
126 (destructuring-bind ,args ,row
128 (database-dump-result-set ,result-set ,db)))))))
131 ;;; Row processing macro
135 (defun lisp->sql-name (field)
138 (symbol (string-upcase (symbol-name field)))
140 (t (format nil "~A" field))))
142 (defun field-names (field-forms)
143 "Return a list of field name strings from a fields form"
144 (loop for field-form in field-forms
147 (if (cadr field-form)
151 (defun from-names (from)
152 "Return a list of field name strings from a fields form"
153 (loop for table in (if (atom from) (list from) from)
154 collect (lisp->sql-name table)))
157 (defun where-strings (where)
158 (loop for w in (if (atom (car where)) (list where) where)
161 (format nil "~A ~A ~A" (second w) (first w) (third w))
162 (format nil "~A" w))))
164 (defun order-by-strings (order-by)
165 (loop for o in order-by
169 (format nil "~A ~A" (lisp->sql-name (car o))
170 (lisp->sql-name (cadr o))))))
172 (defun query-string (fields from where distinct order-by limit)
175 (format nil "select ~A~{~A~^,~} from ~{~A~^ and ~}"
176 (if distinct "distinct " "") (field-names fields)
178 (if where (format nil " where ~{~A~^ ~}"
179 (where-strings where)) "")
180 (if order-by (format nil " order by ~{~A~^, ~}"
181 (order-by-strings order-by)))
182 (if limit (format nil " limit ~D" limit) "")))
184 (defmacro for-each-row (((&rest fields) &key from order-by where distinct limit) &body body)
185 (let ((d (gensym "DISTINCT-"))
186 (bind-fields (loop for f in fields collect (car f)))
187 (w (gensym "WHERE-"))
188 (o (gensym "ORDER-BY-"))
189 (frm (gensym "FROM-"))
190 (l (gensym "LIMIT-"))
191 (q (gensym "QUERY-")))
197 (let ((,q (query-string ',fields ,frm ,w ,d ,o ,l)))
198 (loop for tuple in (query ,q)
199 collect (destructuring-bind ,bind-fields tuple
202 ;;; Marc Battyani : Large objects support
204 (defun create-large-object (&key (database *default-database*))
205 "Creates a new large object in the database and returns the object identifier"
206 (database-create-large-object database))
208 (defun write-large-object (object-id data &key (database *default-database*))
209 "Writes data to the large object"
210 (database-write-large-object object-id data database))
212 (defun read-large-object (object-id &key (database *default-database*))
213 "Reads the large object content"
214 (database-read-large-object object-id database))
216 (defun delete-large-object (object-id &key (database *default-database*))
217 "Deletes the large object in the database"
218 (database-delete-large-object object-id database))