1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; *************************************************************************
3 ;;;; FILE IDENTIFICATION
6 ;;;; Purpose: SQL utility functions
7 ;;;; Programmer: Kevin M. Rosenberg
8 ;;;; Date Started: Mar 2002
12 ;;;; This file, part of CLSQL, is Copyright (c) 2002-2004 by Kevin M. Rosenberg
14 ;;;; CLSQL users are granted the rights to distribute and use this software
15 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
16 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
17 ;;;; *************************************************************************
19 (in-package #:clsql-sys)
21 (defun number-to-sql-string (num)
26 (float-to-sql-string (coerce num 'double-float)))
28 (float-to-sql-string num))))
30 (defun float-to-sql-string (num)
31 "Convert exponent character for SQL"
32 (let ((str (write-to-string num :readably t)))
35 (substitute #\e #\f str))
37 (substitute #\e #\d str))
39 (substitute #\e #\l str))
41 (substitute #\e #\S str))
43 (substitute #\e #\F str))
45 (substitute #\e #\D str))
47 (substitute #\e #\L str))
49 (substitute #\e #\S str))
53 (defun sql-escape (identifier)
54 "Change hyphens to underscores, ensure string"
55 (let* ((unescaped (etypecase identifier
56 (symbol (symbol-name identifier))
58 (escaped (make-string (length unescaped))))
59 (dotimes (i (length unescaped))
60 (setf (char escaped i)
61 (cond ((equal (char unescaped i) #\-)
65 (char unescaped i)))))
68 (defmacro without-interrupts (&body body)
69 #+lispworks `(mp:without-preemption ,@body)
70 #+allegro `(mp:without-scheduling ,@body)
71 #+cmu `(system:without-interrupts ,@body)
72 #+sbcl `(sb-sys::without-interrupts ,@body)
73 #+openmcl `(ccl:without-interrupts ,@body))
75 (defun make-process-lock (name)
76 #+allegro (mp:make-process-lock :name name)
77 #+cmu (mp:make-lock name)
78 #+lispworks (mp:make-lock :name name)
79 #+openmcl (ccl:make-lock name)
80 #+sb-thread (sb-thread:make-mutex :name name)
81 #+scl (thread:make-lock name)
82 #-(or allegro cmu lispworks openmcl sb-thread scl) (declare (ignore name))
83 #-(or allegro cmu lispworks openmcl sb-thread scl) nil)
85 (defmacro with-process-lock ((lock desc) &body body)
86 #+(or cmu allegro lispworks openmcl sb-thread)
87 (declare (ignore desc))
88 #+(or allegro cmu lispworks openmcl sb-thread)
91 #+allegro (mp:with-process-lock (,l) ,@body)
92 #+cmu (mp:with-lock-held (,l) ,@body)
93 #+openmcl (ccl:with-lock-grabbed (,l) ,@body)
94 #+lispworks (mp:with-lock (,l) ,@body)
95 #+sb-thread (sb-thread:with-recursive-lock (,l) ,@body)
97 #+scl `(thread:with-lock-held (,lock ,desc) ,@body)
98 #-(or cmu allegro lispworks openmcl sb-thread scl) (declare
100 #-(or cmu allegro lispworks openmcl sb-thread scl) `(progn ,@body))
102 (defun sql-escape-quotes (s)
103 "Escape quotes for SQL string writing"
104 (substitute-string-for-char s #\' "''"))
106 (defun substitute-string-for-char (procstr match-char subst-str)
107 "Substitutes a string for a single matching character of a string"
108 (let ((pos (position match-char procstr)))
111 (subseq procstr 0 pos) subst-str
112 (substitute-string-for-char
113 (subseq procstr (1+ pos)) match-char subst-str))
117 (defun position-char (char string start max)
119 (declare (optimize (speed 3) (safety 0) (space 0))
120 (fixnum start max) (simple-string string))
121 (do* ((i start (1+ i)))
124 (when (char= char (schar string i)) (return i))))
126 (defun delimited-string-to-list (string &optional (separator #\space)
128 "Split a string with delimiter, from KMRCL."
129 (declare (optimize (speed 3) (safety 0) (space 0) (compilation-speed 0))
131 (type character separator))
132 (do* ((len (length string))
135 (end (position-char separator string pos len)
136 (position-char separator string pos len)))
139 (push (subseq string pos) output)
140 (when (or (not skip-terminal) (zerop len))
143 (declare (type fixnum pos len)
144 (type (or null fixnum) end))
145 (push (subseq string pos end) output)
146 (setq pos (1+ end))))
148 (defun string-to-list-connection-spec (str)
149 (let ((at-pos (position-char #\@ str 0 (length str))))
151 ((and at-pos (> (length str) at-pos))
152 ;; Connection spec is SQL*NET format
153 (cons (subseq str (1+ at-pos))
154 (delimited-string-to-list (subseq str 0 at-pos) #\/)))
156 (delimited-string-to-list str #\/)))))
159 (eval-when (:compile-toplevel :load-toplevel :execute)
160 (unless (find-package '#:excl.osi)
163 (defun command-output (control-string &rest args)
164 ;; Concatenates output and error since Lispworks combines
165 ;; these, thus CLSQL can't depend upon separate results
166 (multiple-value-bind (output error status)
167 (apply #'%command-output control-string args)
169 (concatenate 'string (if output output "")
173 (defun read-stream-to-string (in)
174 (with-output-to-string (out)
175 (let ((eof (gensym)))
176 (do ((line (read-line in nil eof)
177 (read-line in nil eof)))
179 (format out "~A~%" line)))))
182 (defun %command-output (control-string &rest args)
183 "Interpolate ARGS into CONTROL-STRING as if by FORMAT, and
184 synchronously execute the result using a Bourne-compatible shell,
185 returns (VALUES string-output error-output exit-status)"
186 (let ((command (apply #'format nil control-string args)))
188 (let* ((process (sb-ext:run-program
191 :input nil :output :stream :error :stream))
192 (output (read-stream-to-string (sb-impl::process-output process)))
193 (error (read-stream-to-string (sb-impl::process-error process))))
194 (close (sb-impl::process-output process))
195 (close (sb-impl::process-error process))
199 (sb-impl::process-exit-code process)))
203 (let* ((process (ext:run-program
206 :input nil :output :stream :error :stream))
207 (output (read-stream-to-string (ext::process-output process)))
208 (error (read-stream-to-string (ext::process-error process))))
209 (close (ext::process-output process))
210 (close (ext::process-error process))
215 (ext::process-exit-code process)))
218 (multiple-value-bind (output error status)
219 (excl.osi:command-output command :whole t)
220 (values output error status))
223 ;; BUG: Lispworks combines output and error streams
224 (let ((output (make-string-output-stream)))
227 (system:call-system-showing-output
229 :shell-type "/bin/sh"
230 :output-stream output)))
231 (values (get-output-stream-string output) nil status))
235 ;; BUG: CLisp doesn't allow output to user-specified stream
239 (ext:run-shell-command command :output :terminal :wait t))
242 (let* ((process (ccl:run-program
245 :input nil :output :stream :error :stream
247 (output (read-stream-to-string (ccl::external-process-output-stream process)))
248 (error (read-stream-to-string (ccl::external-process-error-stream process))))
249 (close (ccl::external-process-output-stream process))
250 (close (ccl::external-process-error-stream process))
253 (nth-value 1 (ccl::external-process-status process))))
255 #-(or openmcl clisp lispworks allegro scl cmu sbcl)
256 (error "COMMAND-OUTPUT not implemented for this Lisp")
262 (defmacro in (obj &rest choices)
263 (let ((insym (gensym)))
264 `(let ((,insym ,obj))
265 (or ,@(mapcar #'(lambda (c) `(eql ,insym ,c))
269 (defun substitute-char-string (procstr match-char subst-str)
270 "Substitutes a string for a single matching character of a string"
271 (substitute-chars-strings procstr (list (cons match-char subst-str))))
273 (defun replaced-string-length (str repl-alist)
274 (declare (simple-string str)
275 (optimize (speed 3) (safety 0) (space 0)))
277 (orig-len (length str))
279 ((= i orig-len) new-len)
280 (declare (fixnum i orig-len new-len))
281 (let* ((c (char str i))
282 (match (assoc c repl-alist :test #'char=)))
283 (declare (character c))
285 (incf new-len (1- (length
286 (the simple-string (cdr match)))))))))
289 (defun substitute-chars-strings (str repl-alist)
290 "Replace all instances of a chars with a string. repl-alist is an assoc
291 list of characters and replacement strings."
292 (declare (simple-string str)
293 (optimize (speed 3) (safety 0) (space 0)))
294 (do* ((orig-len (length str))
295 (new-string (make-string (replaced-string-length str repl-alist)))
300 (declare (fixnum spos dpos) (simple-string new-string))
301 (let* ((c (char str spos))
302 (match (assoc c repl-alist :test #'char=)))
303 (declare (character c))
305 (let* ((subst (cdr match))
306 (len (length subst)))
307 (declare (fixnum len)
308 (simple-string subst))
311 (setf (char new-string dpos) (char subst j))
314 (setf (char new-string dpos) c)
318 (eval-when (:compile-toplevel :load-toplevel :execute)
319 (when (char= #\a (schar (symbol-name '#:a) 0))
320 (pushnew :clsql-lowercase-reader *features*)))
322 (defun symbol-name-default-case (str)
323 #-clsql-lowercase-reader
325 #+clsql-lowercase-reader
326 (string-downcase str))
328 (defun convert-to-db-default-case (str database)
330 (case (db-type-default-case (database-underlying-type database))
331 (:upper (string-upcase str))
332 (:lower (string-downcase str))
334 ;; Default CommonSQL behavior is to upcase strings
335 (string-upcase str)))
338 (defun ensure-keyword (name)
339 "Returns keyword for a name"
342 (string (nth-value 0 (intern (symbol-name-default-case name) :keyword)))
343 (symbol (nth-value 0 (intern (symbol-name name) :keyword)))))
345 (eval-when (:compile-toplevel :load-toplevel :execute)
346 (setq cl:*features* (delete :clsql-lowercase-reader cl:*features*)))