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
10 ;;;; This file, part of CLSQL, is Copyright (c) 2002-2010 by Kevin M. Rosenberg
12 ;;;; CLSQL users are granted the rights to distribute and use this software
13 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
14 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
15 ;;;; *************************************************************************
17 (in-package #:clsql-sys)
19 (defmacro defaulting (&rest place-value-plist)
20 "for places and values (as an &rest plist)
21 if place-n is null set it to value-n"
23 ,@(loop for (place value . rest) on place-value-plist by #'cddr
24 collect `(unless ,place (setf ,place ,value)))))
26 (defmacro pop-n (place &optional (n 1))
27 "pops n items off of a list in place and returns their values in a new list
29 if n > the length of the list in place, then we return the full list,
30 setting the place to nil"
33 collect (pop ,place)))
37 (string (parse-integer v :junk-allowed t))
39 (number (truncate v))))
42 (if (and (listp it) (eql (first it) 'quote))
46 (defvar +whitespace-chars+
47 '(#\space #\tab #\newline #\return
48 ;; Tested: sbcl unicode, allegrocl, openmcl,clisp use #\no-break_space
49 ;; lispworks uses #\no-break-space
50 ;; sbcl non-unicode doesn't support no break space
51 ;; AllegroCL 8-bit strings don't fail on reading #\no-break_space,
52 ;; but can't represent such a character
53 ;; CMUCL errors when trying to read #\no-break_space
54 #+(and lispworks unicode) #\no-break-space
55 #+(or (and sbcl sb-unicode) (and allegro ics) (and clisp i18n)
56 (and openmcl openmcl-unicode-strings))
59 "List of whitespace characters for this lisp implementation.")
61 (defun number-to-sql-string (num)
64 (princ-to-string num))
66 (float-to-sql-string (coerce num 'double-float)))
68 (float-to-sql-string num))))
70 (defun float-to-sql-string (num)
71 "Convert exponent character for SQL"
72 (let ((str (write-to-string num :readably t)))
73 (declare (type string str))
76 (substitute #\e #\f str))
78 (substitute #\e #\d str))
80 (substitute #\e #\l str))
82 (substitute #\e #\S str))
84 (substitute #\e #\F str))
86 (substitute #\e #\D str))
88 (substitute #\e #\L str))
90 (substitute #\e #\S str))
94 (defun sql-escape (identifier)
95 "Change hyphens to underscores, ensure string"
96 (let ((unescaped (etypecase identifier
97 (symbol (symbol-name identifier))
98 (string identifier))))
99 (substitute #\_ #\- unescaped)))
102 (defvar +lw-has-without-preemption+
106 (defvar +lw-global-lock+
107 (unless +lw-has-without-preemption+
108 (mp:make-lock :name "CLSQL" :important-p nil :safep t :recursivep nil
111 (defmacro without-interrupts (&body body)
112 #+allegro `(mp:without-scheduling ,@body)
113 #+clisp `(progn ,@body)
114 #+cmu `(system:without-interrupts ,@body)
116 (if +lw-has-without-preemption+
117 `(mp:without-preemption ,@body)
118 `(mp:with-exclusive-lock (+lw-global-lock+)
120 #+openmcl `(ccl:without-interrupts ,@body)
121 #+sbcl `(sb-sys::without-interrupts ,@body))
123 (defun make-process-lock (name)
124 #+allegro (mp:make-process-lock :name name)
125 #+cmu (mp:make-lock name)
126 #+lispworks (mp:make-lock :name name)
127 #+openmcl (ccl:make-lock name)
128 #+sb-thread (sb-thread:make-mutex :name name)
129 #+scl (thread:make-lock name)
130 #-(or allegro cmu lispworks openmcl sb-thread scl) (declare (ignore name))
131 #-(or allegro cmu lispworks openmcl sb-thread scl) nil)
133 (defmacro with-process-lock ((lock desc) &body body)
134 #+(or cmu allegro lispworks openmcl sb-thread)
135 (declare (ignore desc))
136 #+(or allegro cmu lispworks openmcl sb-thread)
139 #+allegro (mp:with-process-lock (,l) ,@body)
140 #+cmu (mp:with-lock-held (,l) ,@body)
141 #+openmcl (ccl:with-lock-grabbed (,l) ,@body)
142 #+lispworks (mp:with-lock (,l) ,@body)
143 #+sb-thread (sb-thread:with-recursive-lock (,l) ,@body)
145 #+scl `(thread:with-lock-held (,lock ,desc) ,@body)
146 #-(or cmu allegro lispworks openmcl sb-thread scl) (declare
148 #-(or cmu allegro lispworks openmcl sb-thread scl) `(progn ,@body))
150 (defun sql-escape-quotes (s)
151 "Escape quotes for SQL string writing"
152 (substitute-string-for-char s #\' "''"))
154 (defun substitute-string-for-char (procstr match-char subst-str)
155 "Substitutes a string for a single matching character of a string"
158 (declare (type string procstr))
159 (let ((pos (position match-char procstr)))
162 (subseq procstr 0 pos) subst-str
163 (substitute-string-for-char
164 (subseq procstr (1+ pos)) match-char subst-str))
168 (defun position-char (char string start max)
170 (declare (optimize (speed 3) (safety 0) (space 0))
171 (fixnum start max) (simple-string string))
172 (do* ((i start (1+ i)))
175 (when (char= char (schar string i)) (return i))))
177 (defun delimited-string-to-list (string &optional (separator #\space)
179 "Split a string with delimiter, from KMRCL."
180 (declare (optimize (speed 3) (safety 0) (space 0) (compilation-speed 0))
182 (type character separator))
183 (do* ((len (length string))
186 (end (position-char separator string pos len)
187 (position-char separator string pos len)))
190 (push (subseq string pos) output)
191 (when (or (not skip-terminal) (zerop len))
194 (declare (type fixnum pos len)
195 (type (or null fixnum) end))
196 (push (subseq string pos end) output)
197 (setq pos (1+ end))))
199 (defun string-to-list-connection-spec (str)
200 (declare (type string str))
201 (let ((at-pos (position-char #\@ str 0 (length str))))
203 ((and at-pos (> (length str) at-pos))
204 ;; Connection spec is SQL*NET format
205 (cons (subseq str (1+ at-pos))
206 (delimited-string-to-list (subseq str 0 at-pos) #\/)))
208 (delimited-string-to-list str #\/)))))
211 (eval-when (:compile-toplevel :load-toplevel :execute)
212 (unless (find-package '#:excl.osi)
215 (defun command-output (control-string &rest args)
216 ;; Concatenates output and error since Lispworks combines
217 ;; these, thus CLSQL can't depend upon separate results
218 (multiple-value-bind (output error status)
219 (apply #'%command-output control-string args)
221 (concatenate 'string (if output output "")
225 (defun read-stream-to-string (in)
226 (with-output-to-string (out)
227 (let ((eof (gensym)))
228 (do ((line (read-line in nil eof)
229 (read-line in nil eof)))
231 (format out "~A~%" line)))))
234 (defun %command-output (control-string &rest args)
235 "Interpolate ARGS into CONTROL-STRING as if by FORMAT, and
236 synchronously execute the result using a Bourne-compatible shell,
237 returns (VALUES string-output error-output exit-status)"
238 (let ((command (apply #'format nil control-string args)))
240 (let* ((process (sb-ext:run-program
243 :input nil :output :stream :error :stream))
244 (output (read-stream-to-string (sb-impl::process-output process)))
245 (error (read-stream-to-string (sb-impl::process-error process))))
246 (close (sb-impl::process-output process))
247 (close (sb-impl::process-error process))
251 (sb-impl::process-exit-code process)))
255 (let* ((process (ext:run-program
258 :input nil :output :stream :error :stream))
259 (output (read-stream-to-string (ext::process-output process)))
260 (error (read-stream-to-string (ext::process-error process))))
261 (close (ext::process-output process))
262 (close (ext::process-error process))
267 (ext::process-exit-code process)))
270 (multiple-value-bind (output error status)
271 (excl.osi:command-output command :whole t)
272 (values output error status))
275 ;; BUG: Lispworks combines output and error streams
276 (let ((output (make-string-output-stream)))
279 (system:call-system-showing-output
281 :shell-type "/bin/sh"
282 :output-stream output)))
283 (values (get-output-stream-string output) nil status))
287 ;; BUG: CLisp doesn't allow output to user-specified stream
291 (ext:run-shell-command command :output :terminal :wait t))
294 (let* ((process (ccl:run-program
297 :input nil :output :stream :error :stream
299 (output (read-stream-to-string (ccl::external-process-output-stream process)))
300 (error (read-stream-to-string (ccl::external-process-error-stream process))))
301 (close (ccl::external-process-output-stream process))
302 (close (ccl::external-process-error-stream process))
305 (nth-value 1 (ccl::external-process-status process))))
307 #-(or openmcl clisp lispworks allegro scl cmu sbcl)
308 (error "COMMAND-OUTPUT not implemented for this Lisp")
314 (defmacro in (obj &rest choices)
315 (let ((insym (gensym)))
316 `(let ((,insym ,obj))
317 (or ,@(mapcar #'(lambda (c) `(eql ,insym ,c))
321 (defun substitute-char-string (procstr match-char subst-str)
322 "Substitutes a string for a single matching character of a string"
323 (substitute-chars-strings procstr (list (cons match-char subst-str))))
325 (defun replaced-string-length (str repl-alist)
326 (declare (simple-string str)
327 (optimize (speed 3) (safety 0) (space 0)))
329 (orig-len (length str))
331 ((= i orig-len) new-len)
332 (declare (fixnum i orig-len new-len))
333 (let* ((c (char str i))
334 (match (assoc c repl-alist :test #'char=)))
335 (declare (character c))
337 (incf new-len (1- (length
338 (the simple-string (cdr match)))))))))
341 (defun substitute-chars-strings (str repl-alist)
342 "Replace all instances of a chars with a string. repl-alist is an assoc
343 list of characters and replacement strings."
344 (declare (simple-string str)
345 (optimize (speed 3) (safety 0) (space 0)))
346 (do* ((orig-len (length str))
347 (new-string (make-string (replaced-string-length str repl-alist)))
352 (declare (fixnum spos dpos) (simple-string new-string))
353 (let* ((c (char str spos))
354 (match (assoc c repl-alist :test #'char=)))
355 (declare (character c))
357 (let* ((subst (cdr match))
358 (len (length subst)))
359 (declare (fixnum len)
360 (simple-string subst))
363 (setf (char new-string dpos) (char subst j))
366 (setf (char new-string dpos) c)
371 "Return the value of the environment variable."
372 #+allegro (sys::getenv (string var))
373 #+clisp (ext:getenv (string var))
375 (cdr (assoc (string var) ext:*environment-list* :test #'equalp
377 #+lispworks (lw:environment-variable (string var))
378 #+mcl (ccl::getenv var)
379 #+sbcl (sb-ext:posix-getenv var))
381 (eval-when (:compile-toplevel :load-toplevel :execute)
382 (when (char= #\a (schar (symbol-name '#:a) 0))
383 (pushnew :clsql-lowercase-reader *features*)))
385 (defun symbol-name-default-case (str)
386 #-clsql-lowercase-reader
388 #+clsql-lowercase-reader
389 (string-downcase str))
391 (defun convert-to-db-default-case (str database)
393 (case (db-type-default-case (database-underlying-type database))
394 (:upper (string-upcase str))
395 (:lower (string-downcase str))
397 ;; Default CommonSQL behavior is to upcase strings
398 (string-upcase str)))
400 (defun ensure-keyword (name)
401 "Returns keyword for a name."
404 (string (nth-value 0 (intern (symbol-name-default-case name) :keyword)))
405 (symbol (nth-value 0 (intern (symbol-name name) :keyword)))))
407 (eval-when (:compile-toplevel :load-toplevel :execute)
408 (setq cl:*features* (delete :clsql-lowercase-reader cl:*features*)))
410 (defun replace-all (string part replacement &key (test #'char=) stream)
411 "Returns a new string in which all the occurences of the part
412 is replaced with replacement. [FROM http://cl-cookbook.sourceforge.net/strings.html#manip]"
413 (let ((out (or stream (make-string-output-stream))))
414 (loop with part-length = (length part)
415 for old-pos = 0 then (+ pos part-length)
416 for pos = (search part string
419 do (write-string string out
421 :end (or pos (length string)))
422 when pos do (write-string replacement out)
425 (get-output-stream-string out))))
428 (defun filter-plist (plist &rest keys-to-remove)
429 "Returns a copy of the given plist with indicated key-value pairs
430 removed. keys are searched with #'MEMBER"
431 (declare (dynamic-extent keys-to-remove))
433 (loop for (k v . rest) = plist then rest
434 unless (member k keys-to-remove)
435 collect k and collect v
438 (defmacro make-weak-hash-table (&rest args)
439 "Creates a weak hash table for use in a cache."
442 ;;NB: These are generally used for caches that may not have an alternate
443 ;;clearing mechanism. If you are on an implementation that doesn't support
444 ;;weak hash tables then you're memory may accumulate.
446 #-(or sbcl allegro clisp lispworks ccl)
447 (warn "UNSAFE! use of weak hash on implementation without support. (see clsql/sql/utils.lisp to add)")
450 #+allegro :values #+allegro :weak
451 #+clisp :weak #+clisp :value
452 #+lispworks :weak-kind #+lispworks :value
453 #+sbcl :weakness #+sbcl :value
454 #+ccl :weak #+ccl :value
458 (defun to-slot-name (slot)
459 "try to turn what we got representing the slot into a slot name"
462 (slot-definition (slot-definition-name slot))))
467 (symbol (find-class it))
468 (standard-object (class-of it))))
470 (defun to-class-name (o)
473 (standard-class (class-name o))
474 (standard-object (class-name (class-of o)))))
476 (defun easy-slot-value (obj slot)
477 "like slot-value except it accepts slot-names or defs
478 and returns nil when the slot is unbound"
479 (let ((n (to-slot-name slot)))
480 (when (and obj (slot-boundp obj n))
481 (slot-value obj n))))
483 (defun (setf easy-slot-value) (new obj slot)
484 "like slot-value except it accepts slot-names or defs"
485 (setf (slot-value obj (to-slot-name slot)) new))
487 (defun delist-if-single (x)
488 "if this is a single item in a list return it"
489 (if (and (listp x) (= 1 (length x)))