X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=sql%2Fobjects.lisp;h=9641c5116bbc7b1234237039a941940a4489bfa3;hb=82a46396a91b6eb30efb60eb6356b4a20a7a9136;hp=f2d82e62e8fd2c7e737d4e43d636832e7d29cba2;hpb=afe872863374de73b2f2ef767e742a40d7465e4a;p=clsql.git

diff --git a/sql/objects.lisp b/sql/objects.lisp
index f2d82e6..9641c51 100644
--- a/sql/objects.lisp
+++ b/sql/objects.lisp
@@ -13,7 +13,7 @@
 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
 ;;;; *************************************************************************
 
-(in-package #:clsql-sys)
+(in-package #:clsql)
 
 (defclass standard-db-object ()
   ((view-database :initform nil :initarg :view-database :reader view-database
@@ -21,6 +21,10 @@
   (:metaclass standard-db-class)
   (:documentation "Superclass for all CLSQL View Classes."))
 
+(defvar *update-records-on-make-instance* nil
+  "When T, UPDATE-RECORDS-FROM-INSTANCE will be automatically called
+when a new instance of a view-class is created.")
+
 (defvar *db-deserializing* nil)
 (defvar *db-initializing* nil)
 
@@ -39,17 +43,19 @@
               (setf (slot-value instance slot-name) nil))))))
   (call-next-method))
 
+#+ignore ;; not currently used
 (defmethod (setf slot-value-using-class) (new-value (class standard-db-class)
 					  instance slot)
   (declare (ignore new-value instance slot))
   (call-next-method))
 
-(defmethod initialize-instance :around ((object standard-db-object)
+(defmethod initialize-instance ((object standard-db-object)
 					&rest all-keys &key &allow-other-keys)
   (declare (ignore all-keys))
   (let ((*db-initializing* t))
     (call-next-method)
-    (unless *db-deserializing*
+    (when (and *update-records-on-make-instance*
+	       (not *db-deserializing*))
       #+nil (created-object object)
       (update-records-from-instance object))))
 
@@ -97,7 +103,7 @@ the view. The argument DATABASE has a default value of
   (when (member (view-class-slot-db-kind slotdef) '(:base :key))
     (let ((cdef
            (list (sql-expression :attribute (view-class-slot-column slotdef))
-                 (slot-type slotdef))))
+                 (specified-type slotdef))))
       (setf cdef (append cdef (list (view-class-slot-db-type slotdef))))
       (let ((const (view-class-slot-db-constraints slotdef)))
         (when const 
@@ -169,8 +175,9 @@ superclass of the newly-defined View Class."
     (defclass ,class ,supers ,slots 
       ,@(if (find :metaclass `,cl-options :key #'car)
 	    `,cl-options
-	    (cons '(:metaclass clsql-sys::standard-db-class) `,cl-options)))
-    (finalize-inheritance (find-class ',class))))
+	    (cons '(:metaclass clsql::standard-db-class) `,cl-options)))
+    (finalize-inheritance (find-class ',class))
+    (find-class ',class)))
 
 (defun keyslots-for-class (class)
   (slot-value class 'key-slots))
@@ -232,16 +239,13 @@ superclass of the newly-defined View Class."
       (car list)
       list))
 
-(defun slot-type (slotdef)
-  (specified-type slotdef))
-
 (defvar *update-context* nil)
 
 (defmethod update-slot-from-db ((instance standard-db-object) slotdef value)
   (declare (optimize (speed 3) #+cmu (extensions:inhibit-warnings 3)))
   (let* ((slot-reader (view-class-slot-db-reader slotdef))
 	 (slot-name   (slot-definition-name slotdef))
-	 (slot-type   (slot-type slotdef))
+	 (slot-type   (specified-type slotdef))
 	 (*update-context* (cons (type-of instance) slot-name)))
     (cond ((and value (null slot-reader))
            (setf (slot-value instance slot-name)
@@ -261,7 +265,7 @@ superclass of the newly-defined View Class."
 (defmethod key-value-from-db (slotdef value database) 
   (declare (optimize (speed 3) #+cmu (extensions:inhibit-warnings 3)))
   (let ((slot-reader (view-class-slot-db-reader slotdef))
-        (slot-type (slot-type slotdef)))
+        (slot-type (specified-type slotdef)))
     (cond ((and value (null slot-reader))
            (read-sql-value value (delistify slot-type) database))
           ((null value)
@@ -275,7 +279,7 @@ superclass of the newly-defined View Class."
 
 (defun db-value-from-slot (slotdef val database)
   (let ((dbwriter (view-class-slot-db-writer slotdef))
-	(dbtype (slot-type slotdef)))
+	(dbtype (specified-type slotdef)))
     (typecase dbwriter
       (string (format nil dbwriter val))
       (function (apply dbwriter (list val)))
@@ -287,7 +291,7 @@ superclass of the newly-defined View Class."
 	  (database-output-sql-as-type dbtype val database)))))))
 
 (defun check-slot-type (slotdef val)
-  (let* ((slot-type (slot-type slotdef))
+  (let* ((slot-type (specified-type slotdef))
          (basetype (if (listp slot-type) (car slot-type) slot-type)))
     (when (and slot-type val)
       (unless (typep val basetype)
@@ -397,7 +401,7 @@ superclass of the newly-defined View Class."
 	(let ((qualifier (key-qualifier-for-instance instance :database vd)))
 	  (delete-records :from vt :where qualifier :database vd)
 	  (setf (slot-value instance 'view-database) nil))
-	(error 'clsql-no-database-error nil))))
+	(error 'clsql-base::clsql-no-database-error :database nil))))
 
 (defmethod update-instance-from-records ((instance standard-db-object)
                                          &key (database *default-database*))
@@ -408,7 +412,8 @@ superclass of the newly-defined View Class."
          (sels (generate-selection-list view-class))
          (res (apply #'select (append (mapcar #'cdr sels)
                                       (list :from  view-table
-                                            :where view-qual)))))
+                                            :where view-qual)
+				      (list :result-types nil)))))
     (when res
       (get-slot-values-from-view instance (mapcar #'car sels) (car res)))))
 
@@ -420,7 +425,8 @@ superclass of the newly-defined View Class."
          (view-qual (key-qualifier-for-instance instance :database vd))
          (slot-def (slotdef-for-slot-with-class slot view-class))
          (att-ref (generate-attribute-reference view-class slot-def))
-         (res (select att-ref :from  view-table :where view-qual)))
+         (res (select att-ref :from  view-table :where view-qual
+		      :result-types nil)))
     (when res 
       (get-slot-values-from-view instance (list slot-def) (car res)))))
 
@@ -428,9 +434,7 @@ superclass of the newly-defined View Class."
 (defmethod update-slot-with-null ((object standard-db-object)
 				  slotname
 				  slotdef)
-  (let ((st (slot-type slotdef))
-        (void-value (slot-value slotdef 'void-value)))
-    (setf (slot-value object slotname) void-value)))
+  (setf (slot-value object slotname) (slot-value slotdef 'void-value)))
 
 (defvar +no-slot-value+ '+no-slot-value+)
 
@@ -456,7 +460,7 @@ superclass of the newly-defined View Class."
 
 (defmethod database-get-type-specifier (type args database)
   (declare (ignore type args))
-  (if (clsql-base-sys::in (database-underlying-type database)
+  (if (clsql-base::in (database-underlying-type database)
 			  :postgresql :postgresql-socket)
           "VARCHAR"
           "VARCHAR(255)"))
@@ -467,12 +471,20 @@ superclass of the newly-defined View Class."
   (if args
       (format nil "INT(~A)" (car args))
       "INT"))
+
+(deftype bigint () 
+  "An integer larger than a 32-bit integer, this width may vary by SQL implementation."
+  'integer)
+
+(defmethod database-get-type-specifier ((type (eql 'bigint)) args database)
+  (declare (ignore args database))
+  "BIGINT")
               
 (defmethod database-get-type-specifier ((type (eql 'simple-base-string)) args
                                         database)
   (if args
       (format nil "VARCHAR(~A)" (car args))
-    (if (clsql-base-sys::in (database-underlying-type database) 
+    (if (clsql-base::in (database-underlying-type database) 
 			    :postgresql :postgresql-socket)
 	"VARCHAR"
       "VARCHAR(255)")))
@@ -481,7 +493,7 @@ superclass of the newly-defined View Class."
                                         database)
   (if args
       (format nil "VARCHAR(~A)" (car args))
-    (if (clsql-base-sys::in (database-underlying-type database) 
+    (if (clsql-base::in (database-underlying-type database) 
 			    :postgresql :postgresql-socket)
 	"VARCHAR"
       "VARCHAR(255)")))
@@ -489,11 +501,19 @@ superclass of the newly-defined View Class."
 (defmethod database-get-type-specifier ((type (eql 'string)) args database)
   (if args
       (format nil "VARCHAR(~A)" (car args))
-    (if (clsql-base-sys::in (database-underlying-type database) 
+    (if (clsql-base::in (database-underlying-type database) 
 			    :postgresql :postgresql-socket)
 	"VARCHAR"
       "VARCHAR(255)")))
 
+(deftype universal-time () 
+  "A positive integer as returned by GET-UNIVERSAL-TIME."
+  '(integer 1 *))
+
+(defmethod database-get-type-specifier ((type (eql 'universal-time)) args database)
+  (declare (ignore args database))
+  "BIGINT")
+
 (defmethod database-get-type-specifier ((type (eql 'wall-time)) args database)
   (declare (ignore args))
   (case (database-underlying-type database)
@@ -545,7 +565,7 @@ superclass of the newly-defined View Class."
   (declare (ignore database))
   (progv '(*print-circle* *print-array*) '(t t)
     (let ((escaped (prin1-to-string val)))
-      (clsql-base-sys::substitute-char-string
+      (clsql-base::substitute-char-string
        escaped #\Null " "))))
 
 (defmethod database-output-sql-as-type ((type (eql 'symbol)) val database)
@@ -576,8 +596,11 @@ superclass of the newly-defined View Class."
     (prin1-to-string val)))
 
 (defmethod database-output-sql-as-type ((type (eql 'boolean)) val database)
-  (declare (ignore database))
-  (if val "t" "f"))
+  (case (database-underlying-type database)
+    (:mysql
+     (if val 1 0))
+    (t
+     (if val "t" "f"))))
 
 (defmethod database-output-sql-as-type ((type (eql 'string)) val database)
   (declare (ignore database))
@@ -622,15 +645,24 @@ superclass of the newly-defined View Class."
 (defmethod read-sql-value (val (type (eql 'symbol)) database)
   (declare (ignore database))
   (when (< 0 (length val))
-    (unless (string= val (clsql-base-sys:symbol-name-default-case "NIL"))
-      (intern (clsql-base-sys:symbol-name-default-case val)
+    (unless (string= val (clsql-base:symbol-name-default-case "NIL"))
+      (intern (clsql-base:symbol-name-default-case val)
               (symbol-package *update-context*)))))
 
 (defmethod read-sql-value (val (type (eql 'integer)) database)
   (declare (ignore database))
   (etypecase val
     (string
-     (read-from-string val))
+     (unless (string-equal "NIL" val)
+       (parse-integer val)))
+    (number val)))
+
+(defmethod read-sql-value (val (type (eql 'bigint)) database)
+  (declare (ignore database))
+  (etypecase val
+    (string
+     (unless (string-equal "NIL" val)
+       (parse-integer val)))
     (number val)))
 
 (defmethod read-sql-value (val (type (eql 'float)) database)
@@ -639,8 +671,25 @@ superclass of the newly-defined View Class."
   (float (read-from-string val))) 
 
 (defmethod read-sql-value (val (type (eql 'boolean)) database)
+  (case (database-underlying-type database)
+    (:mysql
+     (etypecase val
+       (string (if (string= "0" val) nil t))
+       (integer (if (zerop val) nil t))))
+    (:postgresql
+     (if (eq :odbc (database-type database))
+	 (if (string= "0" val) nil t)
+       (equal "t" val)))
+    (t
+     (equal "t" val))))
+
+(defmethod read-sql-value (val (type (eql 'univeral-time)) database)
   (declare (ignore database))
-  (equal "t" val))
+  (unless (eq 'NULL val)
+    (etypecase val
+      (string
+       (parse-integer val))
+      (number val))))
 
 (defmethod read-sql-value (val (type (eql 'wall-time)) database)
   (declare (ignore database))
@@ -661,23 +710,43 @@ superclass of the newly-defined View Class."
 	 (jc (gethash :join-class dbi)))
     (let ((jq (join-qualifier class object slot-def)))
       (when jq 
-        (select jc :where jq)))))
+        (select jc :where jq :flatp t :result-types nil)))))
+
+;; FIXME: Create a single join query for efficiency
+(defun fault-join-target-slot (class object slot-def)
+  (let* ((res (fault-join-slot-raw class object slot-def))
+	 (dbi (view-class-slot-db-info slot-def))
+	 (target-name (gethash :target-slot dbi))
+	 (target-class (find-class target-name)))
+    (when res
+      (mapcar (lambda (obj)
+		(list 
+		 (car
+		  (fault-join-slot-raw 
+		   target-class
+		   obj
+		   (find target-name (class-slots (class-of obj))
+			 :key #'slot-definition-name)))
+		 obj))
+	      res)
+      #+ignore ;; this doesn't work when attempting to call slot-value
+      (mapcar (lambda (obj)
+		(cons obj (slot-value obj ts))) res))))
 
 (defun fault-join-slot (class object slot-def)
   (let* ((dbi (view-class-slot-db-info slot-def))
-	 (ts (gethash :target-slot dbi))
-	 (res (fault-join-slot-raw class object slot-def)))
-    (when res
-      (cond
-	((and ts (gethash :set dbi))
-	 (mapcar (lambda (obj)
-		   (cons obj (slot-value obj ts))) res))
-	((and ts (not (gethash :set dbi)))
-	 (mapcar (lambda (obj) (slot-value obj ts)) res))
-	((and (not ts) (not (gethash :set dbi)))
-	 (car res))
-	((and (not ts) (gethash :set dbi))
-	 res)))))
+	 (ts (gethash :target-slot dbi)))
+    (if (and ts (gethash :set dbi))
+	(fault-join-target-slot class object slot-def)
+	(let ((res (fault-join-slot-raw class object slot-def)))
+	  (when res
+	    (cond
+	      ((and ts (not (gethash :set dbi)))
+	       (mapcar (lambda (obj) (slot-value obj ts)) res))
+	      ((and (not ts) (not (gethash :set dbi)))
+	       (car res))
+	      ((and (not ts) (gethash :set dbi))
+	       res)))))))
 
 (defun join-qualifier (class object slot-def)
     (declare (ignore class))
@@ -720,11 +789,14 @@ superclass of the newly-defined View Class."
 
 (defun find-all (view-classes &rest args &key all set-operation distinct from
                  where group-by having order-by order-by-descending offset limit
-		 refresh (database *default-database*))
-  "tweeze me apart someone pleeze"
-  (declare (ignore all set-operation group-by having offset limit)
+		 refresh flatp result-types (database *default-database*))
+  "Called by SELECT to generate object query results when the
+  View Classes VIEW-CLASSES are passed as arguments to SELECT."
+  (declare (ignore all set-operation group-by having offset limit result-types)
            (optimize (debug 3) (speed 1)))
   (remf args :from)
+  (remf args :flatp)
+  (remf args :result-types)
   (labels ((table-sql-expr (table)
 	     (sql-expression :table (view-table table)))
 	   (ref-equal (ref1 ref2)
@@ -746,14 +818,16 @@ superclass of the newly-defined View Class."
 	       obj))
 	   (build-objects (vals sclasses sels)
 	     (let ((objects (mapcar #'(lambda (sclass sel) 
-					(build-object vals sclass sel))
+					(prog1 (build-object vals sclass sel)
+					  (setf vals (nthcdr (list-length sel)
+							     vals))))
 				    sclasses sels)))
-	       (if (= (length sclasses) 1)
+	       (if (and flatp (= (length sclasses) 1))
 		   (car objects)
 		   objects))))
     (let* ((*db-deserializing* t)
 	   (*default-database* (or database
-				   (error 'clsql-no-database-error nil)))
+				   (error 'clsql-base::clsql-no-database-error :database nil)))
 	   (sclasses (mapcar #'find-class view-classes))
 	   (sels (mapcar #'generate-selection-list sclasses))
 	   (fullsels (apply #'append sels))
@@ -786,19 +860,44 @@ superclass of the newly-defined View Class."
 		     (append (mapcar #'cdr fullsels)
 			     (cons :from 
 				   (list (append (when from (listify from)) 
-						 (listify tables)))) args)))
+						 (listify tables)))) 
+			     (list :result-types nil)
+			     args)))
 	(mapcar #'(lambda (r) (build-objects r sclasses sels)) res))))
 
 (defmethod instance-refreshed ((instance standard-db-object)))
 
-(defun select (&rest select-all-args)
-  "Selects data from database given the constraints specified. Returns
-a list of lists of record values as specified by select-all-args. By
-default, the records are each represented as lists of attribute
-values. The selections argument may be either db-identifiers, literal
-strings or view classes.  If the argument consists solely of view
-classes, the return value will be instances of objects rather than raw
-tuples."
+(defun select (&rest select-all-args) 
+   "The function SELECT selects data from DATABASE, which has a
+default value of *DEFAULT-DATABASE*, given the constraints
+specified by the rest of the ARGS. It returns a list of objects
+as specified by SELECTIONS. By default, the objects will each be
+represented as lists of attribute values. The argument SELECTIONS
+consists either of database identifiers, type-modified database
+identifiers or literal strings. A type-modifed database
+identifier is an expression such as [foo :string] which means
+that the values in column foo are returned as Lisp strings.  The
+FLATP argument, which has a default value of nil, specifies if
+full bracketed results should be returned for each matched
+entry. If FLATP is nil, the results are returned as a list of
+lists. If FLATP is t, the results are returned as elements of a
+list, only if there is only one result per row. The arguments
+ALL, SET-OPERATION, DISTINCT, FROM, WHERE, GROUP-BY, HAVING and
+ORDER-by have the same function as the equivalent SQL expression.
+The SELECT function is common across both the functional and
+object-oriented SQL interfaces. If selections refers to View
+Classes then the select operation becomes object-oriented. This
+means that SELECT returns a list of View Class instances, and
+SLOT-VALUE becomes a valid SQL operator for use within the where
+clause. In the View Class case, a second equivalent select call
+will return the same View Class instance objects. If REFRESH is
+true, then existing instances are updated if necessary, and in
+this case you might need to extend the hook INSTANCE-REFRESHED.
+The default value of REFRESH is nil. SQL expressions used in the
+SELECT function are specified using the square bracket syntax,
+once this syntax has been enabled using
+ENABLE-SQL-READER-SYNTAX."
+
   (flet ((select-objects (target-args)
            (and target-args
                 (every #'(lambda (arg)
@@ -809,13 +908,28 @@ tuples."
         (query-get-selections select-all-args)
       (if (select-objects target-args)
           (apply #'find-all target-args qualifier-args)
-          (let ((expr (apply #'make-query select-all-args)))
-            (destructuring-bind (&key (flatp nil)
-				      (database *default-database*)
-                                      &allow-other-keys)
-                qualifier-args
-              (let ((res (query expr :database database)))
-		(if (and flatp
-			 (= (length (slot-value expr 'selections)) 1))
-		    (mapcar #'car res)
-		  res))))))))
+	(let* ((expr (apply #'make-query select-all-args))
+	       (specified-types
+		(mapcar #'(lambda (attrib)
+			    (if (typep attrib 'sql-ident-attribute)
+				(let ((type (slot-value attrib 'type)))
+				  (if type
+				      type
+				    t))
+			      t))
+			(slot-value expr 'selections))))
+	  (destructuring-bind (&key (flatp nil)
+				    (result-types :auto)
+				    (field-names t) 
+				    (database *default-database*)
+			       &allow-other-keys)
+	      qualifier-args
+	    (query expr :flatp flatp 
+		   :result-types 
+		   ;; specifying a type for an attribute overrides result-types
+		   (if (some #'(lambda (x) (not (eq t x))) specified-types) 
+		       specified-types
+		     result-types)
+		   :field-names field-names
+		   :database database)))))))
+