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 Definition Language -->
9 <reference id="ref-ooddl">
10 <title>Object Oriented Data Definition Language (OODDL)</title>
13 The Object Oriented Data Definition Language (OODDL) provides
14 access to relational SQL tables using Common Lisp Object System
15 (CLOS) objects. SQL tables are mapped to CLOS objects with the
16 SQL columns being mapped to slots of the CLOS object.
19 The mapping between SQL tables and CLOS objects is defined
21 linkend="def-view-class"><function>def-view-class</function></link>. SQL
22 tables are created with <link
23 linkend="create-view-from-class"><function>create-view-from-class</function></link>
24 and SQL tables can be deleted with <link
25 linkend="drop-view-from-class"><function>drop-view-from-class</function></link>.
28 <para>The above functions refer to the Lisp <emphasis>view</emphasis> of the SQL
29 table. This Lisp view should not be confused with SQL <function>VIEW</function> statement.</para>
33 <refentry id="standard-db-object">
35 <refname>STANDARD-DB-OBJECT</refname>
36 <refpurpose>Superclass for all &clsql; View Classes.</refpurpose>
37 <refclass>Class</refclass>
40 <title>Class Precedence List</title>
42 <simplelist type="inline">
43 <member><type>standard-db-object</type></member>
44 <member><type>standard-object</type></member>
45 <member><type>t</type></member>
50 <title>Description</title> <para>This class is the superclass
51 of all &clsql; View Classes.</para>
54 <title>Class details</title>
55 <programlisting>(defclass STANDARD-DB-OBJECT ()(...))</programlisting>
61 <member>slot VIEW-DATABASE is of type (OR NULL DATABASE)
62 which stores the associated database for the
69 <refentry id="default-string-length">
71 <refname>*DEFAULT-STRING-LENGTH*</refname>
72 <refpurpose>Default length of SQL strings.</refpurpose>
73 <refclass>Variable</refclass>
76 <title>Value Type</title>
82 <title>Initial Value</title>
83 <para><parameter>255</parameter></para>
86 <title>Description</title>
88 If a slot of a class defined by
89 <function>DEF-VIEW-CLASS</function> is of the type
90 <parameter>STRING</parameter> or <parameter>VARCHAR</parameter> and does
91 not have a length specified, then the value of this variable
92 is used as SQL length.
96 <title>Examples</title>
98 (let ((*default-string-length* 80))
99 (def-view-class s80 ()
101 (b :type (string 80))
103 (create-view-from-class 's80)
106 The above code causes a SQL table to be created with the SQL command
108 <screen>CREATE TABLE (A VARCHAR(80), B CHAR(80), C VARCHAR(80))</screen>
111 <title>Affected By</title>
112 <para>Some SQL backends do not support <parameter>VARCHAR</parameter>
113 lengths greater than 255 .</para>
116 <title>See Also</title>
121 <para>This is a CLSQL extension to the CommonSQL API.</para>
125 <refentry id="create-view-from-class">
127 <refname>CREATE-VIEW-FROM-CLASS</refname>
128 <refpurpose>Create a SQL table from a view class.</refpurpose>
129 <refclass>Function</refclass>
132 <title>Syntax</title>
134 <function> (create-view-from-class view-class-name &key (database *default-database*) (transactions t))</function> => <returnvalue><!-- no values --></returnvalue></synopsis>
137 <title>Arguments and Values</title>
140 <term><parameter>view-class-name</parameter></term>
143 The name of a view class that has been defined with
144 <link linkend="def-view-class"><function>def-view-class</function></link>.
149 <term><parameter>database</parameter></term>
152 The database in which to create the SQL table.
157 <term><parameter>transactions</parameter></term>
160 When &nil; specifies that a table type which does not support transactions should be used.
167 <title>Description</title>
169 Creates a table as defined by the View Class
170 <parameter>view-class-name</parameter> in <parameter>database</parameter>.
174 <title>Examples</title>
176 * (def-view-class 'foo () ((a :type (string 80))))
177 #<CLSQL-SYS::STANDARD-DB-CLASS FOO>
178 * (create-view-from-class 'foo)
184 <title>Side Effects</title>
186 Causes a table to be created in the SQL database.
190 <title>Affected by</title>
192 Most SQL database systems will signal an error if a table
193 creation is attempted when a table with the same name already
194 exists. The SQL user, as specified in the database connection,
195 must have sufficient permission for table creation.
199 <title>Exceptional Situations</title>
201 A condition will be signaled if the table can not be created
206 <title>See Also</title>
209 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>
210 <member><link linkend="drop-view-from-class"><function>drop-view-from-class</function></link></member>
217 Currently, only &mysql; supports transactionless
218 tables. &clsql; provides the ability to create such tables for
219 applications which would benefit from faster table access and
220 do not require transaction support.
223 The case of the table name is determined by the type of the
224 database. &mysql;, for example, creates databases in upper-case
225 while &postgresql; uses lowercase.
230 <refentry id="def-view-class">
232 <refname>DEF-VIEW-CLASS</refname>
233 <refpurpose>Defines CLOS classes with mapping to SQL database.</refpurpose>
234 <refclass>Macro</refclass>
237 <title>Syntax</title>
239 <function>(def-view-class name superclasses slots &rest class-options)</function> => <returnvalue>class</returnvalue></synopsis>
242 <title>Arguments and Values</title>
245 <term><parameter>name</parameter></term>
253 <term><parameter>name</parameter></term>
256 The superclasses for the defined class.
261 <term><parameter>slots</parameter></term>
264 The class slot definitions.
269 <term><parameter>class</parameter></term>
279 <title>Slot Options</title>
283 <parameter>:db-kind</parameter> - specifies the kind of
284 database mapping which is performed for this slot and defaults
285 to <parameter>:base</parameter> which indicates that the
286 slot maps to an ordinary column of the database table. A
287 <parameter>:db-kind</parameter> value of
288 <parameter>:key</parameter> indicates that this slot is
289 a special kind of <parameter>:base</parameter> slot
290 which maps onto a column which is one of the unique keys
291 for the database table, the value
292 <parameter>:join</parameter> indicates this slot
293 represents a join onto another View Class which contains
294 View Class objects, and the value
295 <parameter>:virtual</parameter> indicates a standard
296 CLOS slot which does not map onto columns of the
302 <parameter>:db-info</parameter> - if a slot is specified with
303 <parameter>:db-kind</parameter> <parameter>:join</parameter>, the
304 slot option <parameter>:db-info</parameter> contains a property list
305 which specifies the nature of the join. The valid members of the list
311 <parameter>:join-class</parameter>
312 <emphasis>class-name</emphasis> - the name of the
318 <parameter>:home-key</parameter>
319 <emphasis>slot-name</emphasis> - the name of the slot
320 of this class for joining
325 <parameter>:foreign-key</parameter>
326 <emphasis>slot-name</emphasis> - the name of the slot
327 of the <parameter>:join-class</parameter> for joining
332 <parameter>:target-slot</parameter>
333 <emphasis>target-slot</emphasis> - this is an optional
334 parameter. If specified, then the join slot of the
335 defining class will contain instances of this target
336 slot rather than of the join class. This can be useful
337 when the <parameter>:join-class</parameter> is an
338 intermediate class in a
339 <emphasis>many-to-many</emphasis> relationship and the
340 application is actually interested in the
341 <parameter>:target-slot</parameter>.
346 <parameter>:retrieval</parameter>
347 <emphasis>time</emphasis> - The default value is
348 <parameter>:deferred</parameter>, which defers filling
349 this slot until the value is accessed. The other valid
350 value is <parameter>:immediate</parameter> which
351 performs the SQL query when the instance of the class
352 is created. In this case, the
353 <parameter>:set</parameter> is automatically set to
359 <parameter>:set</parameter>
360 <emphasis>set</emphasis> - This controls what is stored in the join slot.
361 The default value is &t;. When <emphasis>set</emphasis> is &t; and
362 <emphasis>target-slot</emphasis> is undefined, the join slot will contain
363 a list of instances of the join class. Whereas, if <emphasis>target-slot</emphasis> is defined,
364 then the join slot will contain a list of pairs of <emphasis>(target-value join-instance)</emphasis>.
365 When <emphasis>set</emphasis> is &nil;, the join slot will contain a single instances.
372 <parameter>:type</parameter> - for slots of
373 <parameter>:db-kind</parameter> <parameter>:base</parameter> or
374 <parameter>:key</parameter>, the <parameter>:type</parameter> slot
375 option has a special interpretation such that Lisp
376 types, such as string, integer and float are
377 automatically converted into appropriate SQL types for
378 the column onto which the slot maps. This behaviour may
379 be overridden using the <parameter>:db-type</parameter> slot
380 option. The valid values are:
383 <parameter>string</parameter> - a variable length character field up to
384 <link linkend="default-string-length">*default-string-length*</link> characters.
387 <parameter>(string n)</parameter> - a fixed length character field
388 <parameter>n</parameter> characters long.
391 <parameter>varchar</parameter> - a variable length character field up to
392 <link linkend="default-string-length">*default-string-length*</link> characters.
395 <parameter>(varchar n)</parameter> - a variable length character field up to
396 <parameter>n</parameter> characters in length.
399 <parameter>char</parameter> - a single character field
401 <member><parameter>integer</parameter> - signed integer at least 32-bits wide</member>
402 <member><parameter>(integer n)</parameter></member>
403 <member><parameter>float</parameter></member>
404 <member><parameter>(float n)</parameter></member>
405 <member><parameter>long-float</parameter></member>
406 <member><parameter>number</parameter></member>
407 <member><parameter>(number n)</parameter></member>
408 <member><parameter>(number n p)</parameter></member>
410 <parameter>smallint</parameter> - An integer column 16-bits
411 wide. [not supported by all database backends]
414 <parameter>bigint</parameter> - An integer column
415 64-bits wide. [not supported by all database backends]
418 <parameter>universal-time</parameter> - an integer
419 field sufficiently wide to store a
420 universal-time. On most databases, a slot of this
421 type assigned a SQL type of
422 <parameter>BIGINT</parameter>
425 <parameter>wall-time</parameter> - a slot which
426 stores a date and time in a SQL timestamp
427 column. &clsql; provides a number of time
428 manipulation functions to support objects of type
429 <type>wall-time</type>.
432 <parameter>duration</parameter> - stores a <type>duration</type> structure.
433 &clsql; provides routines for <type>wall-time</type> and <type>duration</type>
436 <member><parameter>boolean</parameter> - stores a &t; or &nil; value.</member>
438 <parameter>generalized-boolean</parameter> - similar
439 to a <parameter>boolean</parameter> in that either a
440 &t; or &nil; value is stored in the SQL
441 database. However, any Lisp object can be stored in
442 the Lisp object. A Lisp value of &nil; is stored as
443 <constant>FALSE</constant> in the database, any
444 other Lisp value is stored as
445 <constant>TRUE</constant>.
448 <parameter>keyword</parameter> - stores a keyword
450 <member><parameter>symbol</parameter> - stores a symbol</member>
452 <parameter>list</parameter> - stores a list by writing it to a string. The items
453 in the list must be able to be readable written.
455 <member><parameter>vector</parameter> - stores a vector similarly to <parameter>list</parameter></member>
456 <member><parameter>array</parameter> - stores a array similarly to <parameter>list</parameter></member>
463 <parameter>:column</parameter> - specifies the name of
464 the SQL column which the slot maps onto, if
465 <parameter>:db-kind</parameter> is not
466 <parameter>:virtual</parameter>, and defaults to the
467 slot name. If the slot name is used for the SQL column
468 name, any hypens in the slot name are converted
469 to underscore characters.
474 <parameter>:void-value</parameter> - specifies
475 the value to store in the Lisp instance if the SQL value is NULL and defaults
481 <parameter>:db-constraints</parameter> - is a string
482 representing an SQL table constraint expression or a
483 list of such strings.
488 <parameter>:db-type</parameter> - a string to specify the SQL
489 column type. If specified, this string overrides the SQL
490 column type as computed from the <parameter>:type</parameter>
497 <title>Class Options</title>
502 <parameter>:base-table</parameter> - specifies the name of the
503 SQL database table. The default value is the class name. Like slot names,
504 hypens in the class name are converted to underscore characters.
511 <title>Description</title>
513 Creates a View Class called <parameter>name</parameter> whose
514 slots <parameter>slots</parameter> can map onto the attributes
515 of a table in a database. If
516 <parameter>superclasses</parameter> is &nil; then the
517 superclass of <parameter>class</parameter> will be
518 <parameter>standard-db-object</parameter>, otherwise
519 <parameter>superclasses</parameter> is a list of superclasses
520 for <parameter>class</parameter> which must include
521 <parameter>standard-db-object</parameter> or a descendent of this
527 <title>Examples</title>
529 The following examples are from the &clsql; test suite.
532 (def-view-class person (thing)
533 ((height :db-kind :base :accessor height :type float
535 (married :db-kind :base :accessor married :type boolean
537 (birthday :type clsql:wall-time :initarg :birthday)
538 (bd-utime :type clsql:universal-time :initarg :bd-utime)
539 (hobby :db-kind :virtual :initarg :hobby :initform nil)))
541 (def-view-class employee (person)
544 :db-constraints :not-null
549 :db-constraints :not-null
555 :initarg :first-name)
561 :accessor employee-email
568 :accessor employee-company
570 :db-info (:join-class company
572 :foreign-key companyid
578 :accessor employee-manager
580 :db-info (:join-class employee
585 :accessor employee-addresses
587 :db-info (:join-class employee-address
592 (:base-table employee))
594 (def-view-class company ()
597 :db-constraints :not-null
602 :db-constraints :not-null
610 :initarg :presidentid)
614 :db-info (:join-class employee
615 :home-key presidentid
619 :reader company-employees
621 :db-info (:join-class employee
622 :home-key (companyid groupid)
623 :foreign-key (ecompanyid groupid)
626 (def-view-class address ()
629 :db-constraints :not-null
634 :initarg :street-number)
638 :initarg :street-name)
641 :void-value "no city"
648 :initarg :postal-code))
651 ;; many employees can reside at many addressess
652 (def-view-class employee-address ()
653 ((aemplid :type integer :initarg :emplid)
654 (aaddressid :type integer :initarg :addressid)
655 (verified :type boolean :initarg :verified)
656 (address :db-kind :join
657 :db-info (:join-class address
659 :foreign-key addressid
660 :retrieval :immediate)))
661 (:base-table "ea_join"))
663 (def-view-class deferred-employee-address ()
664 ((aemplid :type integer :initarg :emplid)
665 (aaddressid :type integer :initarg :addressid)
666 (verified :type boolean :initarg :verified)
667 (address :db-kind :join
668 :db-info (:join-class address
670 :foreign-key addressid
673 (:base-table "ea_join"))
677 <title>Side Effects</title>
678 <para>Creates a new CLOS class.</para>
681 <title>Affected by</title>
687 <title>Exceptional Situations</title>
693 <title>See Also</title>
696 <member><link linkend="create-view-from-class"><function>create-view-from-class</function></link></member>
697 <member><link linkend="standard-db-object"><parameter>standard-db-object</parameter></link></member>
698 <member><link linkend="drop-view-from-class"><function>drop-view-from-class</function></link></member>
705 The actual SQL type for a column depends up the database type
706 in which the SQL table is stored. As an example, the view
707 class type <parameter>(varchar 100)</parameter> specifies a
708 SQL column type <parameter>VARCHAR(100)</parameter> in &mysql;
709 and a column type <parameter>VARCHAR2(100)</parameter> in
713 The actual lisp type for a slot may be different than the
714 value specified by the <parameter>:type</parameter> attribute.
715 For example, a slot declared with "<parameter>:type (string
716 30)</parameter>" actually sets the slots Lisp type as
717 <parameter>(or null string)</parameter>. This is to allow a
718 &nil; value or a string shorter than 30 characters to be
724 <refentry id="drop-view-from-class">
726 <refname>DROP-VIEW-FROM-CLASS</refname>
727 <refpurpose>Delete table from SQL database.</refpurpose>
728 <refclass>Function</refclass>
731 <title>Syntax</title>
733 <function>(drop-view-from-class view-class-name &key (database *default-database*))</function> => <returnvalue><!-- result --></returnvalue></synopsis>
736 <title>Arguments and Values</title>
739 <term><parameter>view-class-name</parameter></term>
742 The name of the view class.
749 <title>Description</title>
750 <para>Removes a table defined by the View Class
751 <parameter>VIEW-CLASS-NAME</parameter> from
752 <parameter>DATABASE</parameter> which defaults to
753 <parameter>*DEFAULT-DATABASE*</parameter>.
757 <title>Examples</title>
761 * (drop-view-from-class 'foo)
767 <title>Side Effects</title>
769 Deletes a table from the SQL database.
773 <title>Affected by</title>
775 Whether the specified table exists in the SQL database.
779 <title>Exceptional Situations</title>
781 A condition may be signalled if the table does not exist in
782 the SQL database or if the SQL connection does not have
783 sufficient permissions to delete tables.
787 <title>See Also</title>
790 <member><link linkend="create-view-from-class"><function>create-view-from-class</function></link></member>
791 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>
803 <refentry id="list-classes">
805 <refname>LIST-CLASSES</refname>
806 <refpurpose>List classes for tables in SQL database.</refpurpose>
807 <refclass>Function</refclass>
810 <title>Syntax</title>
812 <function>(list-classes &key (test #'identity) (root-class (find-class 'standard-db-object)) (database *default-database*))</function> => <returnvalue>classes</returnvalue></synopsis>
815 <title>Arguments and Values</title>
818 <term><parameter>test</parameter></term>
821 a function used to filter the search. By default, <parameter>identity</parameter> is used which
822 will return all classes.
827 <term><parameter>root-class</parameter></term>
830 specifies the root class to the search. By default, <parameter>standard-db-object</parameter> is used
831 which is the root for all view classes.
836 <term><parameter>database</parameter></term>
839 The database to search for view classes.
844 <term><parameter>classes</parameter></term>
847 List of view classes.
854 <title>Description</title>
855 <para>Returns a list of all the View Classes which have been
856 defined in the Lisp session and are connected to
857 <parameter>database</parameter> and which descended from the
858 class <parameter>root-class</parameter> and which satisfy the
859 function <parameter>test</parameter>.
863 <title>Examples</title>
866 (#<clsql-sys::standard-db-class big> #<clsql-sys::standard-db-class employee-address>
867 #<clsql-sys::standard-db-class address> #<clsql-sys::standard-db-class company>
868 #<clsql-sys::standard-db-class employee>)
870 * (list-classes :test #'(lambda (c) (> (length (symbol-name (class-name c))) 3)))
871 (#<clsql-sys::standard-db-class employee-address> #<clsql-sys::standard-db-class address>
872 #<clsql-sys::standard-db-class company> #<clsql-sys::standard-db-class employee>)
876 <title>Side Effects</title>
882 <title>Affected by</title>
885 <member>Which view classes have been defined in the Lisp session.</member>
890 <title>Exceptional Situations</title>
896 <title>See Also</title>
899 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>