579597e3 |
1 | (in-package :meta-model) |
2 | |
3 | (eval-when (:compile-toplevel :load-toplevel :execute) |
4 | (use-package :clsql)) |
5 | |
6 | (export 'def-meta-model-from-table) |
7 | (export 'def-meta-models) |
8 | (export 'def-view-class/meta) |
9 | (export 'list-base-classes) |
10 | |
47a72814 |
11 | |
12 | |
13 | (defmethod sync-instance ((view clsql:standard-db-object) &key (database *default-database*)) |
14 | (labels ((sym->sql (sym) (string-downcase (substitute #\_ #\- (string sym)))) |
15 | (get-def (slot) (caar (query |
16 | (format nil "SELECT DISTINCT adsrc from pg_attrdef join pg_attribute on attnum = adnum where adrelid = (select oid from pg_class where relname = '~A') and attname = '~A'" (sym->sql (class-name (class-of view))) (sym->sql slot))))) |
a6644385 |
17 | (get-default-value (slot) |
18 | (let ((def (get-def slot))) |
19 | (if def |
20 | (caar (query (format nil "SELECT ~A" def))) |
21 | (error "No default value for primary key : ~A" slot))))) |
47a72814 |
22 | |
23 | (dolist (slot (list-slots view)) |
24 | (when (and (primary-key-p view slot) |
25 | (or (not (slot-boundp view slot)) |
26 | (equal (slot-value view slot) nil))) |
27 | (setf (slot-value view slot) (get-default-value slot))))) |
28 | (update-records-from-instance view :database database) |
a6644385 |
29 | (update-instance-from-records view :database database) |
30 | (update-objects-joins (list view))) |
47a72814 |
31 | |
32 | |
33 | |
579597e3 |
34 | (defparameter *clsql-base-classes* (list) ) |
35 | |
36 | (defmethod list-base-classes ((type (eql :clsql))) |
37 | *clsql-base-classes*) |
38 | |
39 | (defmethod def-base-class-expander ((model meta-model-class) (base-type (eql :clsql)) (name t) (args t)) |
40 | `(def-view-class ,name () |
41 | ,(meta-model.metadata model))) |
42 | |
43 | (defmethod def-base-class-expander :after ((model meta-model-class) (base-type (eql :clsql)) (name t) (args t)) |
44 | (unless (member name *clsql-base-classes*) |
45 | (setf *clsql-base-classes* (cons name *clsql-base-classes*)))) |
46 | |
47 | (defparameter *sql-type-map* '((:INT4 integer) (:TEXT string) (:VARCHAR string) (:TIMESTAMP clsql-sys::wall-time) (:NUMERIC number)(:BYTEA string))) |
48 | |
49 | (defun gen-type (table column) |
50 | (cadr (assoc |
51 | (cadr (assoc |
52 | column |
53 | (list-attribute-types table) |
54 | :test #'equalp )) |
55 | *sql-type-map*))) |
56 | |
57 | (defun sql->sym (name &optional (package nil)) |
58 | (flet ((xform (x) |
59 | (string-upcase (substitute #\- #\_ x)))) |
60 | (if package |
61 | (intern (xform (string name)) package) |
62 | (intern (xform (string name)))))) |
63 | |
64 | (defun table->slots (table pkey) |
65 | (mapcar |
66 | #'(lambda (col) |
67 | `(,(sql->sym col) |
68 | :accessor ,(sql->sym col) |
69 | :initarg ,(sql->sym col "KEYWORD") |
70 | :type ,(gen-type table col) |
71 | :db-kind |
72 | ,(if (equalp col pkey) |
73 | `:key |
74 | `:base))) |
75 | (list-attributes table))) |
76 | |
77 | (defun view-class-definition-list () |
78 | (mapcar #'(lambda (x) `(def-meta-model-from-table ,x)) |
79 | (list-tables))) |
80 | |
81 | (defmacro def-meta-models () |
82 | (let ((defs (view-class-definition-list))) |
83 | `(progn ,@defs))) |
84 | |
85 | |
579597e3 |
86 | (defun get-pkeys () |
87 | (let ((keys '())) |
88 | (dolist (row (get-pkeys-query)) |
89 | (setf keys (acons (car row) (list (cadr row)) keys))) |
90 | keys)) |
91 | |
92 | (defun get-pkeys-query() |
93 | (query |
94 | "SELECT pg_class.relname, pg_attribute.attname, pg_catalog.quote_ident(conname) AS constraint_n |
95 | , pg_catalog.pg_get_indexdef(d.objid) AS constraint_definition |
96 | , CASE |
97 | WHEN contype = 'p' THEN |
98 | 'PRIMARY KEY' |
99 | ELSE |
100 | 'UNIQUE' |
101 | END as constraint_type |
102 | FROM |
103 | pg_class, pg_attribute, |
104 | pg_catalog.pg_constraint AS c |
105 | JOIN pg_catalog.pg_depend AS d ON (d.refobjid = c.oid) |
106 | WHERE contype IN ('p', 'u') |
107 | AND deptype = 'i' |
108 | and conrelid = pg_class.oid |
109 | and pg_attribute.attnum = ANY (c.conkey) |
110 | and pg_attribute.attrelid = pg_class.oid")) |
111 | |
112 | ;;here is how this works |
113 | ;;from the postgres system tables we get |
114 | ;;list of all the has-a relationships. |
115 | ;;the inverse of a has-a is an implicit has-many |
116 | ;;and any relation having more than one foreign key |
117 | ;;is a join table hosting a many-to-many relationship |
118 | |
119 | (defun get-fkey-explosions () |
120 | (let ((key-table (get-fkey-explosions-query)) |
121 | (keys '())) |
122 | (dolist (row key-table) |
123 | (setf row (mapcar #'(lambda (x) |
124 | (sql->sym x)) |
125 | row)) |
126 | ;;this one does the has-a |
127 | (setf keys (acons (car row) (gen-has-a row) |
128 | keys)) |
129 | ;;the inverse of the previous represents a has-many. |
130 | (setf keys |
131 | (acons (fourth row) (gen-has-many row) |
132 | keys)) |
133 | |
134 | ;;many-to-many |
135 | (dolist (mrow |
136 | (remove-if #'(lambda (r) (or (not (equal (car row) (car r))) |
137 | (equal (last row) (last r)))) |
138 | (mapcar #'(lambda (x) |
139 | (mapcar #'sql->sym x)) |
140 | key-table))) |
141 | (setf keys (acons (fourth row) |
142 | (gen-many-to-many mrow (third row) (second row)) |
143 | keys)))) |
144 | keys )) |
145 | |
146 | |
147 | (defun get-fkey-explosions-query () |
148 | ;;these query's are a mess, i don't even know how they work :) |
149 | (query " |
150 | SELECT pg_class.relname, |
151 | pg_attribute.attname, |
152 | fa.attname , |
153 | f.relname |
154 | FROM pg_class, |
155 | pg_constraint, |
156 | pg_attribute, |
157 | pg_class as f , |
158 | pg_attribute as fa |
159 | WHERE pg_class.relname in (select tablename from pg_tables where schemaname = 'public') |
160 | AND pg_class.oid = pg_constraint.conrelid |
161 | AND pg_attribute.attnum = ANY (pg_constraint.conkey) |
162 | AND pg_attribute.attrelid = pg_class.oid |
163 | AND f.oid = confrelid |
164 | AND fa.attrelid = f.oid |
165 | AND fa.attnum = ANY (pg_constraint.confkey)")) |
166 | |
167 | |
168 | ;; i chose keyword args here so as to make the code more understandable. |
169 | ;; it didn't really work. |
170 | (defun gen-join-slot (&key name home-key foreign-key join-class (set nil)) |
171 | `(,(intern name) |
172 | :accessor ,(intern name) |
173 | :db-kind :join |
174 | :db-info (:join-class ,join-class |
175 | :home-key ,home-key |
176 | :foreign-key ,foreign-key |
177 | :set ,set))) |
178 | |
179 | (defun gen-has-a (row) |
180 | (gen-join-slot |
181 | :name |
182 | (format nil "~A->~A" (string (car row))(string (second row))) |
183 | :home-key (second row) |
184 | :foreign-key (third row) |
185 | :join-class (fourth row))) |
186 | |
187 | (defun gen-has-many (row) |
188 | (gen-join-slot |
189 | :name |
190 | (format nil "~A->~A" (string (car row))(string (second row))) |
191 | :home-key (third row) |
192 | :foreign-key (second row) |
193 | :join-class (car row) |
194 | :set t)) |
195 | |
196 | (defun gen-many-to-many (row home-key foreign-key) |
197 | (let ((name (sql->sym (string-upcase (format nil "~A->~A" (string (car row)) (string (second row))))))) |
198 | (setf row (mapcar #'sql->sym row)) |
199 | `(,name |
200 | :accessor ,name |
201 | :db-kind :join |
202 | :db-info (:join-class ,(car row) |
203 | :home-key ,home-key |
204 | :foreign-key ,foreign-key |
205 | :target-slot ,name |
206 | :set t)))) |
207 | |
579597e3 |
208 | (defmacro def-view-class/meta (name supers slots &rest args) |
209 | `(progn |
210 | (let* ((m (def-meta-model model-name ,supers ,slots ,args)) |
211 | (i (make-instance m))) |
212 | (prog1 (eval (def-base-class-expander i :clsql ',name ',args)) |
213 | (defmethod meta-model.metadata ((self ,name)) |
214 | (meta-model.metadata i)))))) |
215 | |
216 | |
217 | (defmacro def-view-class/table (table &optional name) |
218 | "takes the name of a table as a string and |
219 | creates a clsql view-class" |
220 | (let* ((pkey (cadr (assoc table (get-pkeys) :test #'equalp))) |
221 | (table-slots (table->slots table pkey)) |
222 | (join-slots |
223 | (let ((slots nil)) |
224 | (dolist (exp (get-fkey-explosions)) |
225 | (when (equalp (car exp) (sql->sym table)) |
226 | (setf slots (cons (cdr exp) slots)))) |
227 | slots))) |
228 | `(def-view-class/meta ,(if name name (sql->sym table)) |
229 | () |
230 | ,(append table-slots join-slots)))) |
231 | |
232 | |
233 | |