Object Oriented Data Manipulation Language (OODML)
-
+ Object Oriented Data Manipulation Language (OODML) provides a
+ Common Lisp Object System (CLOS) interface to SQL
+ databases. View classes are defined with the OODDL interface and objects are read
+ and written with the OODML.
+
+
+ The main function for reading data with the OODML is the select
+ function. The select is also used in the
+ FDML. However, when select is given a view
+ class name, it returns a list of instances of view classes.
+
+
+ View class instances can be updated to reflect any changes in
+ the database with the functions update-slot-from-record
+ and update-instance-from-records.
+
+ To update the database to reflect changes made to instances of view classes, use the functions update-records-from-instance,
+ update-record-from-slot, and
+ update-record-from-slots.
+
+
+ The function delete-instance-records
+ deletes the records corresponding to an instance of a view
+ class.
@@ -31,23 +62,48 @@
&nil;
- Description
- When this variable is &t; an instance is stored in the SQL database when the instance is created
- by make-instance. When this variable is &nil;, which is the default value, &clsql;
- behaves like &commonsql;: instances of view classes are stored to the SQL database only when
- update-record-from-slots
- is called.
+ Description
+
+ When this variable is &t; an instance is stored in the SQL
+ database when the instance is created by
+ make-instance. Furthermore, the
+ appropriate database records are updated whenever the slots of
+ a View Class
+ instance are modified.
+
+
+ When this variable is &nil;, which is the default value,
+ &clsql; behaves like &commonsql;: instances of view classes
+ are stored or updated in the SQL database only when update-record-from-instance,
+ update-record-from-slot
+ or update-record-from-slots
+ are called.
+ Examples
(let ((instance (make-instance 'foo)))
- (update-record-from-slots instance))
+ (update-records-from-instance instance))
;; is equivalent to
(let ((*db-auto-sync* t))
(make-instance 'foo))
+
+ ;; and
+
+ (progn
+ (setf (slot-value instance 'bar) "baz")
+ (update-record-from-slot instance 'bar))
+
+ ;; is equivalent to
+
+ (let ((*db-auto-sync* t))
+ (setf (slot-value instance 'bar) "baz"))
@@ -57,13 +113,66 @@
See Also
+ update-records-from-instance
+ update-record-from-slotupdate-record-from-slotsNotes
+ This is a CLSQL extension to the CommonSQL API.
+
+
+
+
+
+ *DEFAULT-CACHING*
+ Controls the default caching behavior.
+ Variable
+
+
+ Value Type
+
+ Boolean
+
+
+
+ Initial Value
+ &t;
+
+
+ Description
+
+ This variable stores the default value of the
+ CACHING keyword for the select.
+
+
+
+ Examples
+
+ (let ((*default-caching* nil)))
+ (select 'foo))
+
+ ;; is equivalent to
+
+ (select 'foo :caching nil)
+
+
+
+ Affected ByNone.
+
+ See Also
+
+ select
+
+
+
+ NotesThis is a CLSQL extension to the
+ &commonsql; API. &commonsql; has caching on at all times.
+
@@ -87,7 +196,7 @@
This special variable provides the default value for the
max-len argument of the function update-object-joins.
+ linkend="update-objects-joins">update-object-joins.
@@ -102,7 +211,9 @@
See Also
- None.
+
+ update-object-joins
+ Notes
@@ -110,591 +221,863 @@
-
+
- DELETE-INSTANCE-RECORDS
- Delete SQL records represented by a view class object
- Function
+ INSTANCE-REFRESHED
+ User hook to call on object refresh.
+ Generic functionSyntax
- (delete-instance-records object) =>
+ instance-refreshedobject => Arguments and Values
-
+
+ object
+
+
+ The View Class object which is being refreshed.
+
+
+ Description
- Deletes the records represented by OBJECT in the
- appropriate table of the database associated with OBJECT. If
- OBJECT is not yet associated with a database, an error is
- signalled.
+ Provides a hook which is called within an object oriented
+ call to select with a non-nil value of
+ refresh when the View Class instance
+ object has been updated from the
+ database. A method specialised on
+ standard-db-object is provided which has no
+ effects. Methods specialised on particular View Classes can be
+ used to specify any operations that need to be made on View
+ Classes instances which have been updated in calls to
+ select.
Examples
- * (def-view-class tab () ((a :type integer :db-kind :key) (b :type string)))
- #<clsql-sys::db-standard-class tab>
- * (defvar obj (let ((*db-auto-sync* t))
- (make-instance 'tab :a 5 :b "the string")))
- * (start-sql-recording :type :both)
- * (delete-instance-records obj)
-
-
+(slot-value employee1 'email)
+=> "lenin@soviet.org"
+(defmethod instance-refreshed ((e employee))
+ (format t "~&Details for ~A ~A have been updated from the database."
+ (slot-value e 'first-name)
+ (slot-value e 'last-name)))
+=> #<Standard-Method INSTANCE-REFRESHED (EMPLOYEE) {48174D9D}>
+(select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)
+=> (#<EMPLOYEE {48149995}>)
+(slot-value (car *) 'email)
+=> "lenin@soviet.org"
+(update-records [employee] :av-pairs '(([email] "v.lenin@soviet.org"))
+ :where [= [emplid] 1])
+=>
+(select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)
+=> (#<EMPLOYEE {48149995}>)
+(slot-value (car *) 'email)
+=> "lenin@soviet.org"
+(select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t :refresh t)
+Details for Vladimir Lenin have been updated from the database.
+=> (#<EMPLOYEE {48149995}>)
+(slot-value (car *) 'email)
+=> "v.lenin@soviet.org"
Side Effects
- Deletes data from the SQL database.
-
-
-
- Affected by
-
- Permissions granted by the SQL database to the user in the database connection.
+ The user hook function may cause side effects.
Exceptional Situations
- An exception may be signaled if the database connection user does not have
- sufficient privileges to modify the database.
+ None.
See Also
-
- update-records
- update-records-from-instance
-
+
+ select
+ Notes
- Instances are referenced in the database by values stored in the key slots. If
- delete-records-from-instance is called with an instance of a class that does
- not contain any keys, then all records in that table will be deleted.
+ None.
-
+
- INSTANCE-REFRESHED
- Hook to call on object refresh
- Generic function
+ DELETE-INSTANCE-RECORDS
+ Delete SQL records represented by a View Class
+ object.
+ FunctionSyntax
- (instance-refreshed object) =>
+ delete-instance-recordsobject => Arguments and Values
-
- object
-
-
- The view class object which is being refreshed.
-
-
-
+
+ object
+
+
+ An instance of a View
+ Class.
+
+
+ Description
- Provides a hook which is called within an object
- oriented call to SELECT with a non-nil value of REFRESH when
- the View Class instance OBJECT has been updated from the
- database. A method specialised on STANDARD-DB-OBJECT is
- provided which has no effects. Methods specialised on
- particular View Classes can be used to specify any operations
- that need to be made on View Classes instances which have been
- updated in calls to SELECT.
+ Deletes the records represented by
+ object in the appropriate table of the
+ database associated with object. If
+ object is not yet associated with a
+ database, an error is signalled.
Examples
-
+(def-view-class tab ()
+ ((a :initarg :a :type integer :db-kind :key)
+ (b :initarg :b :type string)))
+=> #<Standard-Db-Class TAB {49B01845}>
+(create-view-from-class 'tab)
+=>
+(defvar obj (let ((*db-auto-sync* t))
+ (make-instance 'tab :a 5 :b "the string")))
+=> OBJ
+(start-sql-recording :type :both)
+=>
+(delete-instance-records obj)
+;; 2004-07-17 11:07:19 foo/bar/baz => DELETE FROM tab WHERE tab.a = 5
+;; 2004-07-17 11:07:19 foo/bar/baz <= T
+=>
Side Effects
-
+ Deletes data from the SQL database.
Affected by
-
-
-
+ Permissions granted by the SQL database to the user in the
+ database connection.
Exceptional Situations
- None.
+ An exception may be signaled if the database connection user
+ does not have sufficient privileges to modify the database. An
+ error of type sql-database-error is signalled if
+ object is not associated with an
+ active database.
See Also
-
-
-
-
-
+
+ update-records
+ delete-records
+ update-records-from-instance
+ Notes
-
+ Instances are referenced in the database by values stored in
+ the key slots. If
+ delete-records-from-instance is called
+ with an instance of a class that does not contain any keys,
+ then all records in that table will be deleted.
-
+
- UPDATE-INSTANCE-FROM-RECORDS
- Update slot values from database.
+ UPDATE-RECORDS-FROM-INSTANCE
+ Update database from view class object.FunctionSyntax
- (update-instance-from-records object &key database) =>
+ update-records-from-instanceobject &key database => Arguments and Values
-
+
+ object
+
+
+ An instance of a View
+ Class.
+
+
+
+
+ database
+
+
+ database
+ object. This will default to the value of
+ *default-database*.
+
+
+ Description
- Updates the slot values of the View Class instance
- OBJECT using the attribute values of the appropriate table of
- DATABASE which defaults to the database associated with OBJECT
- or, if OBJECT is not associated with a database,
- *DEFAULT-DATABASE*. Join slots are updated but instances of
- the class on which the join is made are not updated.
+ Using an instance of a View Class,
+ object, update the table that stores its
+ instance data. database specifies the
+ database in which the update is made only if
+ object is not associated with a
+ database. In this case, a record is created in the appropriate
+ table of database using values from the
+ slot values of object, and
+ object becomes associated with
+ database.
Examples
-
-
+(select [email] :from [employee] :where [= [emplid] 1] :field-names nil :flatp t)
+=> ("lenin@soviet.org")
+(defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
+=> *E1*
+(slot-value *e1* 'email)
+=> "lenin@soviet.org"
+(setf (slot-value *e1* 'email) "v.lenin@soviet.org")
+=> "v.lenin@soviet.org"
+(update-records-from-instance *e1*)
+=>
+(select [email] :from [employee] :where [= [emplid] 1] :field-names nil :flatp t)
+=> ("v.lenin@soviet.org")
+
Side Effects
-
+ Modifies the database.
Affected by
-
-
-
+ Nothing.
Exceptional Situations
-
+ Database errors.
See Also
-
-
-
-
-
+
+ update-record-from-slot
+ update-record-from-slots
+ update-records
+ Notes
-
+ None.
-
+
- UPDATE-OBJECTS-JOINS
- Updates joined slots of objects
+ UPDATE-RECORD-FROM-SLOT
+ Updates database from slot value.FunctionSyntax
- (update-objects-joins objects &key (slots t) (force-p t) class-name (max-len *default-update-objects-max-len*)) =>
+ update-record-from-slotobjectslot &key database => Arguments and Values
-
+
+ object
+
+
+ An instance of a View Class.
+
+
+
+
+ slot
+
+
+ The name of a slot in object.
+
+
+
+
+ database
+
+
+ A database
+ object. This will default to the value of
+ *default-database*.
+
+
+ Description
- Updates from the records of the appropriate
- database tables the join slots specified by SLOTS in
- the supplied list of View Class instances OBJECTS.
- SLOTS is t by default which means that all join slots
- with :retrieval :immediate are updated. CLASS-NAME is
- used to specify the View Class of all instance in
- OBJECTS and default to nil which means that the class
- of the first instance in OBJECTS is used. FORCE-P is t
- by default which means that all join slots are updated
- whereas a value of nil means that only unbound join
- slots are updated. MAX-LEN defaults to
- *DEFAULT-UPDATE-OBJECTS-MAX-LEN* and when non-nil
- specifies that UPDATE-OBJECT-JOINS may issue multiple
- database queries with a maximum of MAX-LEN instances
- updated in each query.
+ Updates the value stored in the column represented by the
+ slot, specified by the CLOS slot name
+ slot, of View Class instance
+ object. database
+ specifies the database in which the update is made only if
+ object is not associated with a
+ database. In this case, a record is created in
+ database and the attribute represented by
+ slot is initialised from the value of the
+ supplied slots with other attributes having default
+ values. Furthermore, object becomes
+ associated with database.
Examples
-
+(select [email] :from [employee] :where [= [emplid] 1] :field-names nil :flatp t)
+=> ("lenin@soviet.org")
+(defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
+=> *E1*
+(slot-value *e1* 'email)
+=> "lenin@soviet.org"
+(setf (slot-value *e1* 'email) "v.lenin@soviet.org")
+=> "v.lenin@soviet.org"
+(update-record-from-slot *e1* 'email)
+=>
+(select [email] :from [employee] :where [= [emplid] 1] :field-names nil :flatp t)
+=> ("v.lenin@soviet.org")
Side Effects
-
+ Modifies database.
- Affected by
+ Affected By
-
-
-
+ Nothing.
Exceptional Situations
-
+ Database errors.
See Also
-
-
-
-
-
+
+ update-record-from-slots
+ update-records-from-instance
+ Notes
-
+ None.
-
+
-
+
- UPDATE-RECORD-FROM-SLOT
- Updates database from slot value
- Function
+ UPDATE-RECORD-FROM-SLOTS
+ Update database from slots of view class object.
+ function
- Syntax
+ syntax
- (update-record-from-slot object slot &key database) =>
+ update-record-from-slotsobjectslots &key database => Arguments and Values
-
+
+ object
+
+
+ An instance of a View Class.
+
+
+
+
+ slots
+
+
+ A list of slot names in object.
+
+
+
+
+ database
+
+
+ A database
+ object. This will default to the value of
+ *default-database*.
+
+
+ Description
- Updates the value stored in the column represented by
- the slot, specified by the CLOS slot name SLOT, of View Class
- instance OBJECT. DATABASE defaults to *DEFAULT-DATABASE* and
+ Updates the values stored in the columns represented by
+ the slots, specified by the clos slot names
+ slots, of View Class instance
+ object. database
specifies the database in which the update is made only if
- OBJECT is not associated with a database. In this case, a
- record is created in DATABASE and the attribute represented by
- SLOT is initialised from the value of the supplied slots with
- other attributes having default values. Furthermore, OBJECT
- becomes associated with DATABASE.
+ object is not associated with a
+ database. In this case, a record is created in the appropriate
+ table of database and the attributes
+ represented by slots are initialised from
+ the values of the supplied slots with other attributes having
+ default values. Furthermore, object
+ becomes associated with database.
Examples
-
+(select [last-name] [email] :from [employee] :where [= [emplid] 1] :field-names nil)
+=> (("Lenin" "lenin@soviet.org"))
+(defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
+=> *E1*
+(slot-value *e1* 'last-name)
+=> "Lenin"
+(slot-value *e1* 'email)
+=> "lenin@soviet.org"
+(setf (slot-value *e1* 'last-name) "Ivanovich")
+=> "Ivanovich"
+(setf (slot-value *e1* 'email) "v.ivanovich@soviet.org")
+=> "v.ivanovich@soviet.org"
+(update-record-from-slots *e1* '(email last-name))
+=>
+(select [last-name] [email] :from [employee] :where [= [emplid] 1] :field-names nil)
+=> (("Ivanovich" "v.ivanovich@soviet.org"))
Side Effects
-
+ Modifies the SQL database.
Affected by
-
-
-
+ Nothing.
Exceptional Situations
-
+ Database errors.
See Also
-
+ update-record-from-slot
+ update-records-from-instanceNotes
-
+ None.
-
+
- UPDATE-RECORD-FROM-SLOTS
- Update database from slots of view class object
+ UPDATE-INSTANCE-FROM-RECORDS
+ Update slot values from database.FunctionSyntax
- (update-record-from-slots object slots &key database) =>
+ update-instance-from-recordsobject &key database => objectArguments and Values
-
+
+ object
+
+
+ An instance of a View Class.
+
+
+
+
+ database
+
+
+ A database
+ object. This will default to the value of
+ *default-database*.
+
+
+ Description
- Updates the values stored in the columns represented by
- the slots, specified by the CLOS slot names SLOTS, of View
- Class instance OBJECT. DATABASE defaults to *DEFAULT-DATABASE*
- and specifies the database in which the update is made only if
- OBJECT is not associated with a database. In this case, a
- record is created in the appropriate table of DATABASE and the
- attributes represented by SLOTS are initialised from the
- values of the supplied slots with other attributes having
- default values. Furthermore, OBJECT becomes associated with
- DATABASE.
+ Updates the slot values of the View Class instance
+ object using the attribute values of the
+ appropriate table of database which
+ defaults to the database associated with
+ object or, if
+ object is not associated with a database,
+ *default-database*. Join slots are updated
+ but instances of the class on which the join is made are not
+ updated.
Examples
-
+(defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
+=> *E1*
+(slot-value *e1* 'email)
+=> "lenin@soviet.org"
+(update-records [employee]
+ :av-pairs '(([email] "v.lenin@soviet.org"))
+ :where [= [emplid] 1])
+=>
+(update-instance-from-records *e1*)
+=> #<EMPLOYEE {4806B53D}>
+(slot-value *e1* 'email)
+=> "v.lenin@soviet.org"
Side Effects
-
+ Slot values of object may be modified.
Affected by
-
+ Data in SQL database.Exceptional Situations
-
+ If database is not able to be read.
See Also
-
-
-
-
-
+
+ update-slot-from-record
+ update-objects-joins
+ Notes
-
+ None.
-
-
+
- UPDATE-RECORDS-FROM-INSTANCE
- Update database from view class object.
+ UPDATE-SLOT-FROM-RECORD
+ Update objects slot from database.FunctionSyntax
- (update-records-from-instance object &key database) =>
+ update-slot-from-recordobjectslot &key database => objectArguments and Values
-
+
+ object
+
+
+ An instance of a View Class.
+
+
+
+
+ slot
+
+
+ The name of a slot in object.
+
+
+
+
+ database
+
+
+ A database
+ object. This will default to the value of
+ *default-database*.
+
+
+ Description
- Using an instance of a View Class, OBJECT, update the
- table that stores its instance data. DATABASE defaults to
- *DEFAULT-DATABASE* and specifies the database in which the
- update is made only if OBJECT is not associated with a
- database. In this case, a record is created in the appropriate
- table of DATABASE using values from the slot values of OBJECT,
- and OBJECT becomes associated with DATABASE.
+ Updates the slot value, specified by the CLOS slot name
+ slot, of the View Class instance
+ object using the attribute values of the
+ appropriate table of database which
+ defaults to the database associated with
+ object or, if
+ object is not associated with a database,
+ *default-database*. Join slots are updated
+ but instances of the class on which the join is made are not
+ updated.
Examples
-
+(defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
+=> *E1*
+(slot-value *e1* 'email)
+=> "lenin@soviet.org"
+(update-records [employee]
+ :av-pairs '(([email] "v.lenin@soviet.org"))
+ :where [= [emplid] 1])
+=>
+(update-slot-from-record *e1* 'email)
+=> #<EMPLOYEE {4806B53D}>
+(slot-value *e1* 'email)
+=> "v.lenin@soviet.org"
Side Effects
-
+ Modifies the slot value of the object.
Affected by
-
+ Data in SQL database.Exceptional Situations
-
+ Database errors.
See Also
-
-
-
-
-
+
+ update-instance-from-records
+ update-objects-joins
+ Notes
-
+ None.
-
+
- UPDATE-SLOT-FROM-RECORD
- Update objects slot from database.
+ UPDATE-OBJECTS-JOINS
+ Updates joined slots of objects.FunctionSyntax
- (update-slot-from-record object slot &key database) =>
+ update-objects-joinsobjects &key slotsforce-pclass-namemax-len => Arguments and Values
-
+
+ objects
+
+
+ A list of instances of a View Class.
+
+
+
+
+ slots
+
+
+ A list of slot names in object or &t;.
+
+
+
+
+ force-p
+
+
+ A Boolean, defaulting to &t;.
+
+
+
+
+ class-name
+
+
+ A list of instances of a View Class.
+
+
+
+
+ max-len
+
+
+ A non-negative integer or &nil; defaulting to
+ *default-update-objects-max-len*.
+
+
+ Description
- Updates the slot value, specified by the CLOS slot name
- SLOT, of the View Class instance OBJECT using the attribute
- values of the appropriate table of DATABASE which defaults to
- the database associated with OBJECT or, if OBJECT is not
- associated with a database, *DEFAULT-DATABASE*. Join slots
- are updated but instances of the class on which the join is
- made are not updated.
+ Updates from the records of the appropriate database
+ tables the join slots specified by slots
+ in the supplied list of View
+ Class instances
+ objects. slots
+ when &t; means that all join slots with
+ :retrieval:immediate are
+ updated. class-name is used to specify
+ the View Class of
+ all instance in objects, when &nil; then
+ the class of the first instance in
+ objects is
+ used. force-p when &t; means that all
+ join slots are updated whereas a value of &nil; means that only
+ unbound join slots are updated. max-len
+ when non-nil specifies that
+ update-object-joins may issue multiple
+ database queries with a maximum of
+ max-len instances updated in each query.
Examples
-
+(defvar *addresses* (select 'deferred-employee-address :order-by [ea_join aaddressid] :flatp t))
+=> *ADDRESSES*
+(slot-boundp (car *addresses*) 'address)
+=> NIL
+(update-objects-joins *addresses*)
+=>
+(slot-boundp (car *addresses*) 'address)
+=> T
+(slot-value (car *addresses*) 'address)
+=> #<ADDRESS {480B0F1D}>
Side Effects
-
+ The slot values of objects are modified.
Affected by
-
-
+ *default-update-objects-max-len*
+
Exceptional Situations
-
+ Database errors.
See Also
-
-
-
-
-
+
+ *default-update-objects-max-len*
+ update-instance-from-records
+ update-slot-from-record
+ Notes
-
+ None.
-
+