1 ;;;; -*- Mode: Lisp; Syntax: ANSI-Common-Lisp; Base: 10; Package: umlisp -*-
2 ;;;; *************************************************************************
3 ;;;; FILE IDENTIFICATION
6 ;;;; Purpose: SQL Generation functions for Hyperobject
7 ;;;; Programmer: Kevin M. Rosenberg
8 ;;;; Date Started: Apr 2000
10 ;;;; $Id: sqlgen.lisp,v 1.1 2002/12/01 21:07:28 kevin Exp $
12 ;;;; This file, part of Hyperobject-SQL, is
13 ;;;; Copyright (c) 2000-2002 by Kevin M. Rosenberg, M.D.
14 ;;;; *************************************************************************
16 (in-package :hyperobject)
17 (declaim (optimize (speed 3) (safety 1) (compilation-speed 0) (debug 3)))
20 ;;;; Metaclass initialization commands
21 (defun process-sql (cl)
22 (let ((esds (class-slots cl)))
23 (let* ((table-name-slot (slot-value cl 'sql-name))
24 (generate-table-cmd (generate-create-table-string
25 (if (consp table-name-slot)
29 (setf (slot-value cl 'create-table-cmd) generate-table-cmd))
32 (when (dsd-inverse dsd)
33 (define-inverse cl dsd))))
36 (defun define-inverse (class dsd)
37 (let ((inverse (dsd-inverse dsd)))
40 `(defun ,inverse (key)
41 (format t "~&Finding key: ~a~%" key)
45 ;; create inverse function
49 (defun generate-create-table-string (table-name dsds)
50 (let ((cmd (format nil "CREATE TABLE ~A ("
51 (slot-name-to-sql-name table-name))))
53 (unless (eq dsd (car dsds))
54 (string-append cmd ", "))
55 (string-append cmd (slot-name-to-sql-name
56 #+allegro (clos:slot-definition-name dsd)
57 #+lispworks (clos:slot-definition-name dsd)
59 (let ((length (dsd-length dsd))
60 (sql-type (dsd-sql-type dsd)))
61 (string-append cmd (sql-field-cmd sql-type length))))
62 (string-append cmd ")")))
71 (defmethod sql-create ((self sqltable))
72 (sql (sql-cmd-create-table self))
73 (dolist (cmd (sql-cmd-create-indices self))
77 (defmethod sql-drop ((self sqltable))
78 (sql (sql-cmd-drop-table self))
81 (defmethod sql-insert ((self sqltable))
83 (format nil "INSERT INTO ~a (~a) VALUES (~a)"
84 (table-name self) (sql-cmd-field-names self) (format-values self))))
86 (defmethod sql-select ((self sqltable) key)
90 (format nil "SELECT ~a FROM ~a WHERE ~a=~a"
91 (sql-cmd-field-names self) (table-name self)
92 (inverse-field-name self) key)))))
94 (format t "process returned fields"))))
97 (defun format-values (self)
99 (fields (fields self)))
100 (dolist (field fields)
101 (unless (eq field (car fields))
102 (string-append values ","))
103 (let ((name (car field)))
104 (with-key-value-list (key value (rest field))
106 (string-append values
108 ((:fixnum :bigint :short-float :double-float)
110 (slot-value self name)))
114 (slot-value self name))))))))))
117 (defmacro defsqltable (tname &key fields)
119 (defclass ,tname (sqltable)
120 ,(parse-fields tname fields)
121 ,(default-initargs fields))
123 (defmethod table-name ((self ,tname))
124 ,(substitute #\_ #\- (write-to-string tname)))
126 (defmethod fields ((self ,tname))
129 (defmethod sql-cmd-create-table ((self ,tname))
130 ,(create-table-string tname fields))
132 (defmethod sql-cmd-create-indices ((self ,tname))
133 "Return a list of index cmds"
134 (quote ,(create-indices-string tname fields)))
136 (defmethod sql-cmd-drop-table ((self ,tname))
137 ,(format nil "DROP TABLE ~a" tname))
139 (defmethod sql-cmd-field-names ((self ,tname))
140 ,(row-field-string fields))
142 (defmethod inverse-field-name ((self ,tname))
143 ,(inverse-field-string fields))
146 (defun create-indices-string (table-name fields)
148 (dolist (field fields)
149 (let ((name-string (write-to-string (car field))))
150 (with-key-value-list (key value (rest field))
151 (when (eq key :unique)
154 (push (sql-cmd-index table-name name-string nil) indices))
156 (push (sql-cmd-index table-name name-string t) indices)))))))
159 (defun inverse-field-string (fields)
161 (dolist (field fields)
162 (let ((name-string (write-to-string (car field))))
163 (with-key-value-list (key value (rest field))
164 (when (eq key :inverse)
165 (setq inverse value)))))
167 (write-to-string inverse))))
169 (defun sql-cmd-index (table field unique)
170 (let ((*print-circle* nil))
171 (format nil "CREATE ~A INDEX ~A_~A ON ~A(~A)"
172 (if unique "UNIQUE" "")
173 (slot-name-to-sql-name table)
174 (slot-name-to-sql-name field)
175 (slot-name-to-sql-name table)
176 (slot-name-to-sql-name field))))
178 (defun row-field-string (fields)
180 (dolist (field fields)
181 (unless (eq field (car fields))
182 (string-append names ","))
183 (string-append names (slot-name-to-sql-name (car field))))
186 (defun slot-name-to-sql-name (name)
187 (substitute #\_ #\- (format nil "~a" name)))
189 (defun create-table-string (table-name fields)
190 (let ((cmd (format nil "CREATE TABLE ~A (" (slot-name-to-sql-name table-name))))
191 (dolist (field fields)
192 (unless (eq field (car fields))
193 (string-append cmd ", "))
194 (string-append cmd (slot-name-to-sql-name (car field)) " ")
196 (with-key-value-list (key value (rest field))
202 (string-append cmd (sql-field-cmd type length))))
203 (string-append cmd ")")))
206 (defun sql-field-cmd (type length)
210 (format nil "CHAR(~d)" length)
211 (format nil "VARCHAR(~d)" length)))
223 (defmacro with-key-value-list ((key value list) form)
225 `(loop for ,i from 0 to (1- (length ,list)) by 2 do
226 (let ((,key (nth ,i ,list))
227 (,value (nth (1+ ,i) ,list)))
230 (defun parse-fields (table-name fields)
232 (dolist (field fields)
233 (let* ((fname (car field))
234 (name-string (write-to-string fname))
235 (initarg (intern name-string :keyword))concat-symbol
237 (options (rest field)))
238 (with-key-value-list (key value options)
241 (setq def (nconc def (list :type
255 (setq def (nconc def (list
257 :accessor (concat-symbol
258 (write-to-string table-name) "-"
259 (write-to-string fname)))))
260 (push def class-fields)))
263 (defun default-initargs (fields)
264 (let ((initargs (list :default-initargs)))
265 (dolist (field fields)
266 (let* ((fname (car field))
267 (name-string (write-to-string fname))
268 (initarg (intern name-string :keyword)))
269 (setq initargs (nconc initargs (list initarg nil)))))