2 <!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN"
3 "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd" [
4 <!ENTITY % myents SYSTEM "entities.inc">
8 <!-- Object Oriented Data Manipulation Language -->
9 <reference id="ref-oodml">
10 <title>Object Oriented Data Manipulation Language (OODML)</title>
13 Object Oriented Data Manipulation Language (OODML) provides a
14 Common Lisp Object System (CLOS) interface to SQL
15 databases. View classes are defined with the <link
16 linkend="ref-ooddl">OODDL</link> interface and objects are read
17 and written with the OODML.
20 The main function for reading data with the OODML is the <link
21 linkend="select"><function>select</function></link>
22 function. The <function>select</function> is also used in the
23 FDML. However, when <function>select</function> is given a view
24 class name, it returns a list of instances of view classes.
27 View class instances can be updated to reflect any changes in
28 the database with the functions <link
29 linkend="update-slot-from-record"><function>update-slot-from-record</function></link>
31 linkend="update-instance-from-records"><function>update-instance-from-records</function></link>.
33 <para>To update the database to reflect changes made to instances of view classes, use the functions <link
34 linkend="update-records-from-instance"><function>update-records-from-instance</function></link>,
36 linkend="update-record-from-slot"><function>update-record-from-slot</function></link>, and
38 linkend="update-record-from-slots"><function>update-record-from-slots</function></link>.
42 linkend="delete-instance-records"><function>delete-instance-records</function></link>
43 deletes the records corresponding to an instance of a view
48 <refentry id="db-auto-sync">
50 <refname>*DB-AUTO-SYNC*</refname>
51 <refpurpose>Enables SQL storage during Lisp object creation.</refpurpose>
52 <refclass>Variable</refclass>
55 <title>Value Type</title>
61 <title>Initial Value</title>
65 <title>Description</title>
67 When this variable is &t; an instance is stored in the SQL
68 database when the instance is created by
69 <function>make-instance</function>. Furthermore, the
70 appropriate database records are updated whenever the slots of
71 a <glossterm linkend="gloss-view-class">View Class</glossterm>
72 instance are modified.
75 When this variable is &nil;, which is the default value,
76 &clsql; behaves like &commonsql;: instances of view classes
77 are stored or updated in the SQL database only when <link
78 linkend="update-records-from-instance"><function>update-record-from-instance</function></link>,
80 linkend="update-record-from-slot"><function>update-record-from-slot</function></link>
82 linkend="update-record-from-slots"><function>update-record-from-slots</function></link>
87 <title>Examples</title>
89 (let ((instance (make-instance 'foo)))
90 (update-records-from-instance instance))
94 (let ((*db-auto-sync* t))
100 (setf (slot-value instance 'bar) "baz")
101 (update-record-from-slot instance 'bar))
105 (let ((*db-auto-sync* t))
106 (setf (slot-value instance 'bar) "baz"))
110 <title>Affected By</title>
114 <title>See Also</title>
116 <member><link linkend="update-records-from-instance"><function>update-records-from-instance</function></link></member>
117 <member><link linkend="update-record-from-slot"><function>update-record-from-slot</function></link></member>
118 <member><link linkend="update-record-from-slots"><function>update-record-from-slots</function></link></member>
123 <para>This is a CLSQL extension to the CommonSQL API.</para>
127 <refentry id="default-update-objects-max-len">
129 <refname>*DEFAULT-UPDATE-OBJECTS-MAX-LEN*</refname>
130 <refpurpose>The default maximum number of objects each query to perform a join</refpurpose>
131 <refclass>Variable</refclass>
134 <title>Value Type</title>
140 <title>Initial Value</title>
144 <title>Description</title>
146 This special variable provides the default value for the
147 <parameter>max-len</parameter> argument of the function <link
148 linkend="update-objects-joins"><function>update-object-joins</function></link>.
152 <title>Examples</title>
154 (setq *default-update-objects-max-len* 100)
158 <title>Affected By</title>
162 <title>See Also</title>
164 <member><link linkend="update-objects-joins"><function>update-object-joins</function></link></member>
173 <refentry id="instance-refreshed">
175 <refname>INSTANCE-REFRESHED</refname>
176 <refpurpose>User hook to call on object refresh.</refpurpose>
177 <refclass>Generic function</refclass>
180 <title>Syntax</title>
182 <function>instance-refreshed</function> <replaceable>object</replaceable> => <returnvalue><!-- no result --></returnvalue></synopsis>
185 <title>Arguments and Values</title>
188 <term><parameter>object</parameter></term>
191 The <glossterm linkend="gloss-view-class">View Class</glossterm> object which is being refreshed.
198 <title>Description</title>
199 <para>Provides a hook which is called within an object oriented
200 call to <function>select</function> with a non-nil value of
201 <parameter>refresh</parameter> when the <glossterm
202 linkend="gloss-view-class">View Class</glossterm> instance
203 <parameter>object</parameter> has been updated from the
204 database. A method specialised on
205 <type>standard-db-object</type> is provided which has no
206 effects. Methods specialised on particular View Classes can be
207 used to specify any operations that need to be made on View
208 Classes instances which have been updated in calls to
209 <function>select</function>.
213 <title>Examples</title>
215 (slot-value employee1 'email)
216 => "lenin@soviet.org"
217 (defmethod instance-refreshed ((e employee))
218 (format t "~&Details for ~A ~A have been updated from the database."
219 (slot-value e 'first-name)
220 (slot-value e 'last-name)))
221 => #<Standard-Method INSTANCE-REFRESHED (EMPLOYEE) {48174D9D}>
222 (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)
223 => (#<EMPLOYEE {48149995}>)
224 (slot-value (car *) 'email)
225 => "lenin@soviet.org"
226 (update-records [employee] :av-pairs '(([email] "v.lenin@soviet.org"))
227 :where [= [emplid] 1])
229 (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)
230 => (#<EMPLOYEE {48149995}>)
231 (slot-value (car *) 'email)
232 => "lenin@soviet.org"
233 (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t :refresh t)
234 Details for Vladamir Lenin have been updated from the database.
235 => (#<EMPLOYEE {48149995}>)
236 (slot-value (car *) 'email)
237 => "v.lenin@soviet.org"
241 <title>Side Effects</title>
243 The user hook function may cause side effects.
247 <title>Exceptional Situations</title>
253 <title>See Also</title>
256 <member><link linkend="select"><function>select</function></link></member>
268 <refentry id="delete-instance-records">
270 <refname>DELETE-INSTANCE-RECORDS</refname>
271 <refpurpose>Delete SQL records represented by a <glossterm linkend="gloss-view-class">View Class</glossterm>
273 <refclass>Function</refclass>
276 <title>Syntax</title>
278 <function>delete-instance-records</function> <replaceable>object</replaceable> => <returnvalue><!-- no result --></returnvalue></synopsis>
281 <title>Arguments and Values</title>
284 <term><parameter>object</parameter></term>
287 An instance of a <glossterm linkend="gloss-view-class">View
295 <title>Description</title>
296 <para>Deletes the records represented by
297 <parameter>object</parameter> in the appropriate table of the
298 database associated with <parameter>object</parameter>. If
299 <parameter>object</parameter> is not yet associated with a
300 database, an error is signalled.
304 <title>Examples</title>
306 (def-view-class tab ()
307 ((a :initarg :a :type integer :db-kind :key)
308 (b :initarg :b :type string)))
309 => #<Standard-Db-Class TAB {49B01845}>
310 (create-view-from-class 'tab)
312 (defvar obj (let ((*db-auto-sync* t))
313 (make-instance 'tab :a 5 :b "the string")))
315 (start-sql-recording :type :both)
317 (delete-instance-records obj)
318 ;; 2004-07-17 11:07:19 foo/bar/baz => DELETE FROM tab WHERE tab.a = 5
319 ;; 2004-07-17 11:07:19 foo/bar/baz <= T
324 <title>Side Effects</title>
326 Deletes data from the SQL database.
330 <title>Affected by</title>
332 Permissions granted by the SQL database to the user in the
337 <title>Exceptional Situations</title>
339 An exception may be signaled if the database connection user
340 does not have sufficient privileges to modify the database. An
341 error of type <type>sql-database-error</type> is signalled if
342 <replaceable>object</replaceable> is not associated with an
347 <title>See Also</title>
349 <member><link linkend="update-records"><function>update-records</function></link></member>
350 <member><link linkend="delete-records"><function>delete-records</function></link></member>
351 <member><link linkend="update-records-from-instance"><function>update-records-from-instance</function></link></member>
357 Instances are referenced in the database by values stored in
359 <function>delete-records-from-instance</function> is called
360 with an instance of a class that does not contain any keys,
361 then all records in that table will be deleted.
366 <refentry id="update-records-from-instance">
368 <refname>UPDATE-RECORDS-FROM-INSTANCE</refname>
369 <refpurpose>Update database from view class object.</refpurpose>
370 <refclass>Function</refclass>
373 <title>Syntax</title>
375 <function>update-records-from-instance</function> <replaceable>object</replaceable> &key <replaceable>database</replaceable> => <returnvalue><!-- no result --></returnvalue></synopsis>
378 <title>Arguments and Values</title>
381 <term><parameter>object</parameter></term>
384 An instance of a <glossterm linkend="gloss-view-class">View
390 <term><parameter>database</parameter></term>
393 <glossterm linkend="gloss-database-object">database
394 object</glossterm>. This will default to the value of
395 <symbol>*default-database*</symbol>.
402 <title>Description</title>
403 <para>Using an instance of a <glossterm
404 linkend="gloss-view-class">View Class</glossterm>,
405 <parameter>object</parameter>, update the table that stores its
406 instance data. <parameter>database</parameter> specifies the
407 database in which the update is made only if
408 <parameter>object</parameter> is not associated with a
409 database. In this case, a record is created in the appropriate
410 table of <parameter>database</parameter> using values from the
411 slot values of <parameter>object</parameter>, and
412 <parameter>object</parameter> becomes associated with
413 <parameter>database</parameter>.
417 <title>Examples</title>
419 (select [email] :from [employee] :where [= [emplid] 1] :field-names nil :flatp t)
420 => ("lenin@soviet.org")
421 (defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
423 (slot-value *e1* 'email)
424 => "lenin@soviet.org"
425 (setf (slot-value *e1* 'email) "v.lenin@soviet.org")
426 => "v.lenin@soviet.org"
427 (update-records-from-instance *e1*)
429 (select [email] :from [employee] :where [= [emplid] 1] :field-names nil :flatp t)
430 => ("v.lenin@soviet.org")
434 <title>Side Effects</title>
436 Modifies the database.
440 <title>Affected by</title>
446 <title>Exceptional Situations</title>
452 <title>See Also</title>
454 <member><link linkend="update-record-from-slot"><function>update-record-from-slot</function></link></member>
455 <member><link linkend="update-record-from-slots"><function>update-record-from-slots</function></link></member>
456 <member><link linkend="update-records"><function>update-records</function></link></member>
467 <refentry id="update-record-from-slot">
469 <refname>UPDATE-RECORD-FROM-SLOT</refname>
470 <refpurpose>Updates database from slot value.</refpurpose>
471 <refclass>Function</refclass>
474 <title>Syntax</title>
476 <function>update-record-from-slot</function> <replaceable>object</replaceable> <replaceable>slot</replaceable> &key <replaceable>database</replaceable> => <returnvalue><!-- no result --></returnvalue></synopsis>
479 <title>Arguments and Values</title>
482 <term><parameter>object</parameter></term>
485 An instance of a <glossterm linkend="gloss-view-class">View Class</glossterm>.
490 <term><parameter>slot</parameter></term>
493 The name of a slot in <parameter>object</parameter>.
498 <term><parameter>database</parameter></term>
501 A <glossterm linkend="gloss-database-object">database
502 object</glossterm>. This will default to the value of
503 <symbol>*default-database*</symbol>.
510 <title>Description</title>
511 <para>Updates the value stored in the column represented by the
512 slot, specified by the CLOS slot name
513 <parameter>slot</parameter>, of <glossterm linkend="gloss-view-class">View Class</glossterm> instance
514 <parameter>object</parameter>. <parameter>database</parameter>
515 specifies the database in which the update is made only if
516 <parameter>object</parameter> is not associated with a
517 database. In this case, a record is created in
518 <parameter>database</parameter> and the attribute represented by
519 <parameter>slot</parameter> is initialised from the value of the
520 supplied slots with other attributes having default
521 values. Furthermore, <parameter>object</parameter> becomes
522 associated with <parameter>database</parameter>.
526 <title>Examples</title>
528 (select [email] :from [employee] :where [= [emplid] 1] :field-names nil :flatp t)
529 => ("lenin@soviet.org")
530 (defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
532 (slot-value *e1* 'email)
533 => "lenin@soviet.org"
534 (setf (slot-value *e1* 'email) "v.lenin@soviet.org")
535 => "v.lenin@soviet.org"
536 (update-record-from-slot *e1* 'email)
538 (select [email] :from [employee] :where [= [emplid] 1] :field-names nil :flatp t)
539 => ("v.lenin@soviet.org")
543 <title>Side Effects</title>
549 <title>Affected By</title>
555 <title>Exceptional Situations</title>
561 <title>See Also</title>
563 <member><link linkend="update-record-from-slots"><function>update-record-from-slots</function></link></member>
564 <member><link linkend="update-records-from-instance"><function>update-records-from-instance</function></link></member>
575 <refentry id="update-record-from-slots">
577 <refname>UPDATE-RECORD-FROM-SLOTS</refname>
578 <refpurpose>Update database from slots of view class object.</refpurpose>
579 <refclass>function</refclass>
582 <title>syntax</title>
584 <function>update-record-from-slots</function> <replaceable>object</replaceable> <replaceable>slots</replaceable> &key <replaceable>database</replaceable> => <returnvalue><!-- no result --></returnvalue></synopsis>
587 <title>Arguments and Values</title>
590 <term><parameter>object</parameter></term>
593 An instance of a <glossterm linkend="gloss-view-class">View Class</glossterm>.
598 <term><parameter>slots</parameter></term>
601 A list of slot names in <parameter>object</parameter>.
606 <term><parameter>database</parameter></term>
609 A <glossterm linkend="gloss-database-object">database
610 object</glossterm>. This will default to the value of
611 <symbol>*default-database*</symbol>.
618 <title>Description</title>
619 <para>Updates the values stored in the columns represented by
620 the slots, specified by the clos slot names
621 <parameter>slots</parameter>, of <glossterm linkend="gloss-view-class">View Class</glossterm> instance
622 <parameter>object</parameter>. <parameter>database</parameter>
623 specifies the database in which the update is made only if
624 <parameter>object</parameter> is not associated with a
625 database. In this case, a record is created in the appropriate
626 table of <parameter>database</parameter> and the attributes
627 represented by <parameter>slots</parameter> are initialised from
628 the values of the supplied slots with other attributes having
629 default values. Furthermore, <parameter>object</parameter>
630 becomes associated with <parameter>database</parameter>.
634 <title>Examples</title>
636 (select [last-name] [email] :from [employee] :where [= [emplid] 1] :field-names nil)
637 => (("Lenin" "lenin@soviet.org"))
638 (defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
640 (slot-value *e1* 'last-name)
642 (slot-value *e1* 'email)
643 => "lenin@soviet.org"
644 (setf (slot-value *e1* 'last-name) "Ivanovich")
646 (setf (slot-value *e1* 'email) "v.ivanovich@soviet.org")
647 => "v.ivanovich@soviet.org"
648 (update-record-from-slots *e1* '(email last-name))
650 (select [last-name] [email] :from [employee] :where [= [emplid] 1] :field-names nil)
651 => (("Ivanovich" "v.ivanovich@soviet.org"))
655 <title>Side Effects</title>
657 Modifies the SQL database.
661 <title>Affected by</title>
667 <title>Exceptional Situations</title>
673 <title>See Also</title>
676 <member><link linkend="update-record-from-slot"><function>update-record-from-slot</function></link></member>
677 <member><link linkend="update-records-from-instance"><function>update-records-from-instance</function></link></member>
689 <refentry id="update-instance-from-records">
691 <refname>UPDATE-INSTANCE-FROM-RECORDS</refname>
692 <refpurpose>Update slot values from database.</refpurpose>
693 <refclass>Function</refclass>
696 <title>Syntax</title>
698 <function>update-instance-from-records</function> <replaceable>object</replaceable> &key <replaceable>database</replaceable> => <returnvalue>object</returnvalue></synopsis>
701 <title>Arguments and Values</title>
704 <term><parameter>object</parameter></term>
707 An instance of a <glossterm linkend="gloss-view-class">View Class</glossterm>.
712 <term><parameter>database</parameter></term>
715 A <glossterm linkend="gloss-database-object">database
716 object</glossterm>. This will default to the value of
717 <symbol>*default-database*</symbol>.
724 <title>Description</title>
725 <para>Updates the slot values of the <glossterm
726 linkend="gloss-view-class">View Class</glossterm> instance
727 <parameter>object</parameter> using the attribute values of the
728 appropriate table of <parameter>database</parameter> which
729 defaults to the database associated with
730 <parameter>object</parameter> or, if
731 <parameter>object</parameter> is not associated with a database,
732 <varname>*default-database*</varname>. Join slots are updated
733 but instances of the class on which the join is made are not
738 <title>Examples</title>
740 (defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
742 (slot-value *e1* 'email)
743 => "lenin@soviet.org"
744 (update-records [employee]
745 :av-pairs '(([email] "v.lenin@soviet.org"))
746 :where [= [emplid] 1])
748 (update-instance-from-records *e1*)
749 => #<EMPLOYEE {4806B53D}>
750 (slot-value *e1* 'email)
751 => "v.lenin@soviet.org"
755 <title>Side Effects</title>
757 Slot values of <parameter>object</parameter> may be modified.
761 <title>Affected by</title>
764 <member>Data in SQL database.</member>
769 <title>Exceptional Situations</title>
771 If <parameter>database</parameter> is not able to be read.
775 <title>See Also</title>
777 <member><link linkend="update-slot-from-record"><function>update-slot-from-record</function></link></member>
778 <member><link linkend="update-objects-joins"><function>update-objects-joins</function></link></member>
789 <refentry id="update-slot-from-record">
791 <refname>UPDATE-SLOT-FROM-RECORD</refname>
792 <refpurpose>Update objects slot from database.</refpurpose>
793 <refclass>Function</refclass>
796 <title>Syntax</title>
798 <function>update-slot-from-record</function> <replaceable>object</replaceable> <replaceable>slot</replaceable> &key <replaceable>database</replaceable> => <returnvalue>object</returnvalue></synopsis>
801 <title>Arguments and Values</title>
804 <term><parameter>object</parameter></term>
807 An instance of a <glossterm linkend="gloss-view-class">View Class</glossterm>.
812 <term><parameter>slot</parameter></term>
815 The name of a slot in <parameter>object</parameter>.
820 <term><parameter>database</parameter></term>
823 A <glossterm linkend="gloss-database-object">database
824 object</glossterm>. This will default to the value of
825 <symbol>*default-database*</symbol>.
832 <title>Description</title>
833 <para>Updates the slot value, specified by the CLOS slot name
834 <parameter>slot</parameter>, of the <glossterm
835 linkend="gloss-view-class">View Class</glossterm> instance
836 <parameter>object</parameter> using the attribute values of the
837 appropriate table of <parameter>database</parameter> which
838 defaults to the database associated with
839 <parameter>object</parameter> or, if
840 <parameter>object</parameter> is not associated with a database,
841 <varname>*default-database*</varname>. Join slots are updated
842 but instances of the class on which the join is made are not
847 <title>Examples</title>
849 (defvar *e1* (car (select 'employee :where [= [slot-value 'employee 'emplid] 1] :flatp t)))
851 (slot-value *e1* 'email)
852 => "lenin@soviet.org"
853 (update-records [employee]
854 :av-pairs '(([email] "v.lenin@soviet.org"))
855 :where [= [emplid] 1])
857 (update-slot-from-record *e1* 'email)
858 => #<EMPLOYEE {4806B53D}>
859 (slot-value *e1* 'email)
860 => "v.lenin@soviet.org"
864 <title>Side Effects</title>
866 Modifies the slot value of the object.
870 <title>Affected by</title>
873 <member>Data in SQL database.</member>
878 <title>Exceptional Situations</title>
884 <title>See Also</title>
886 <member><link linkend="update-instance-from-records"><function>update-instance-from-records</function></link></member>
887 <member><link linkend="update-objects-joins"><function>update-objects-joins</function></link></member>
898 <refentry id="update-objects-joins">
900 <refname>UPDATE-OBJECTS-JOINS</refname>
901 <refpurpose>Updates joined slots of objects.</refpurpose>
902 <refclass>Function</refclass>
905 <title>Syntax</title>
907 <function>update-objects-joins</function> <replaceable>objects</replaceable> &key <replaceable>slots</replaceable> <replaceable>force-p</replaceable> <replaceable>class-name</replaceable> <replaceable>max-len</replaceable> => <returnvalue><!-- no result --></returnvalue></synopsis>
910 <title>Arguments and Values</title>
913 <term><parameter>objects</parameter></term>
916 A list of instances of a <glossterm
917 linkend="gloss-view-class">View Class</glossterm>.
922 <term><parameter>slots</parameter></term>
925 A list of slot names in <parameter>object</parameter> or &t;.
930 <term><parameter>force-p</parameter></term>
933 A Boolean, defaulting to &t;.
938 <term><parameter>class-name</parameter></term>
941 A list of instances of a <glossterm
942 linkend="gloss-view-class">View Class</glossterm>.
947 <term><parameter>max-len</parameter></term>
950 A non-negative integer or &nil; defaulting to
951 <symbol>*default-update-objects-max-len*</symbol>.
958 <title>Description</title>
959 <para>Updates from the records of the appropriate database
960 tables the join slots specified by <parameter>slots</parameter>
961 in the supplied list of <glossterm linkend="gloss-view-class">View
962 Class</glossterm> instances
963 <parameter>objects</parameter>. <parameter>slots</parameter>
964 when &t; means that all join slots with
965 <symbol>:retrieval</symbol> <symbol>:immediate</symbol> are
966 updated. <parameter>class-name</parameter> is used to specify
967 the <glossterm linkend="gloss-view-class">View Class</glossterm> of
968 all instance in <parameter>objects</parameter>, when &nil; then
969 the class of the first instance in
970 <parameter>objects</parameter> is
971 used. <parameter>force-p</parameter> when &t; means that all
972 join slots are updated whereas a value of &nil; means that only
973 unbound join slots are updated. <parameter>max-len</parameter>
974 when non-nil specifies that
975 <function>update-object-joins</function> may issue multiple
976 database queries with a maximum of
977 <parameter>max-len</parameter> instances updated in each query.
981 <title>Examples</title>
983 (defvar *addresses* (select 'deferred-employee-address :order-by [ea_join aaddressid] :flatp t))
985 (slot-boundp (car *addresses*) 'address)
987 (update-objects-joins *addresses*)
989 (slot-boundp (car *addresses*) 'address)
991 (slot-value (car *addresses*) 'address)
992 => #<ADDRESS {480B0F1D}>
996 <title>Side Effects</title>
998 The slot values of <parameter>objects</parameter> are modified.
1002 <title>Affected by</title>
1006 linkend="default-update-objects-max-len"><varname>*default-update-objects-max-len*</varname></link></member>
1011 <title>Exceptional Situations</title>
1017 <title>See Also</title>
1019 <member><link linkend="default-update-objects-max-len"><varname>*default-update-objects-max-len*</varname></link></member>
1020 <member><link linkend="update-instance-from-records"><function>update-instance-from-records</function></link></member>
1021 <member><link linkend="update-slot-from-record"><function>update-slot-from-record</function></link></member>
1025 <title>Notes</title>