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>tinyint</parameter> - An integer column 8-bits
411 wide. [not supported by all database backends]
414 <parameter>smallint</parameter> - An integer column 16-bits
415 wide. [not supported by all database backends]
418 <parameter>bigint</parameter> - An integer column
419 64-bits wide. [not supported by all database backends]
422 <parameter>universal-time</parameter> - an integer
423 field sufficiently wide to store a
424 universal-time. On most databases, a slot of this
425 type assigned a SQL type of
426 <parameter>BIGINT</parameter>
429 <parameter>wall-time</parameter> - a slot which
430 stores a date and time in a SQL timestamp
431 column. &clsql; provides a number of time
432 manipulation functions to support objects of type
433 <type>wall-time</type>.
436 <parameter>duration</parameter> - stores a <type>duration</type> structure.
437 &clsql; provides routines for <type>wall-time</type> and <type>duration</type>
440 <member><parameter>boolean</parameter> - stores a &t; or &nil; value.</member>
442 <parameter>generalized-boolean</parameter> - similar
443 to a <parameter>boolean</parameter> in that either a
444 &t; or &nil; value is stored in the SQL
445 database. However, any Lisp object can be stored in
446 the Lisp object. A Lisp value of &nil; is stored as
447 <constant>FALSE</constant> in the database, any
448 other Lisp value is stored as
449 <constant>TRUE</constant>.
452 <parameter>keyword</parameter> - stores a keyword
454 <member><parameter>symbol</parameter> - stores a symbol</member>
456 <parameter>list</parameter> - stores a list by writing it to a string. The items
457 in the list must be able to be readable written.
459 <member><parameter>vector</parameter> - stores a vector similarly to <parameter>list</parameter></member>
460 <member><parameter>array</parameter> - stores a array similarly to <parameter>list</parameter></member>
467 <parameter>:column</parameter> - specifies the name of
468 the SQL column which the slot maps onto, if
469 <parameter>:db-kind</parameter> is not
470 <parameter>:virtual</parameter>, and defaults to the
471 slot name. If the slot name is used for the SQL column
472 name, any hypens in the slot name are converted
473 to underscore characters.
478 <parameter>:void-value</parameter> - specifies
479 the value to store in the Lisp instance if the SQL value is NULL and defaults
485 <parameter>:db-constraints</parameter> - is a string
486 representing an SQL table constraint expression or a
487 list of such strings.
492 <parameter>:db-type</parameter> - a string to specify the SQL
493 column type. If specified, this string overrides the SQL
494 column type as computed from the <parameter>:type</parameter>
501 <title>Class Options</title>
506 <parameter>:base-table</parameter> - specifies the name of the
507 SQL database table. The default value is the class name. Like slot names,
508 hypens in the class name are converted to underscore characters.
515 <title>Description</title>
517 Creates a View Class called <parameter>name</parameter> whose
518 slots <parameter>slots</parameter> can map onto the attributes
519 of a table in a database. If
520 <parameter>superclasses</parameter> is &nil; then the
521 superclass of <parameter>class</parameter> will be
522 <parameter>standard-db-object</parameter>, otherwise
523 <parameter>superclasses</parameter> is a list of superclasses
524 for <parameter>class</parameter> which must include
525 <parameter>standard-db-object</parameter> or a descendent of this
531 <title>Examples</title>
533 The following examples are from the &clsql; test suite.
536 (def-view-class person (thing)
537 ((height :db-kind :base :accessor height :type float
539 (married :db-kind :base :accessor married :type boolean
541 (birthday :type clsql:wall-time :initarg :birthday)
542 (bd-utime :type clsql:universal-time :initarg :bd-utime)
543 (hobby :db-kind :virtual :initarg :hobby :initform nil)))
545 (def-view-class employee (person)
548 :db-constraints :not-null
553 :db-constraints :not-null
559 :initarg :first-name)
565 :accessor employee-email
572 :accessor employee-company
574 :db-info (:join-class company
576 :foreign-key companyid
582 :accessor employee-manager
584 :db-info (:join-class employee
589 :accessor employee-addresses
591 :db-info (:join-class employee-address
596 (:base-table employee))
598 (def-view-class company ()
601 :db-constraints :not-null
606 :db-constraints :not-null
614 :initarg :presidentid)
618 :db-info (:join-class employee
619 :home-key presidentid
623 :reader company-employees
625 :db-info (:join-class employee
626 :home-key (companyid groupid)
627 :foreign-key (ecompanyid groupid)
630 (def-view-class address ()
633 :db-constraints :not-null
638 :initarg :street-number)
642 :initarg :street-name)
645 :void-value "no city"
652 :initarg :postal-code))
655 ;; many employees can reside at many addressess
656 (def-view-class employee-address ()
657 ((aemplid :type integer :initarg :emplid)
658 (aaddressid :type integer :initarg :addressid)
659 (verified :type boolean :initarg :verified)
660 (address :db-kind :join
661 :db-info (:join-class address
663 :foreign-key addressid
664 :retrieval :immediate)))
665 (:base-table "ea_join"))
667 (def-view-class deferred-employee-address ()
668 ((aemplid :type integer :initarg :emplid)
669 (aaddressid :type integer :initarg :addressid)
670 (verified :type boolean :initarg :verified)
671 (address :db-kind :join
672 :db-info (:join-class address
674 :foreign-key addressid
677 (:base-table "ea_join"))
681 <title>Side Effects</title>
682 <para>Creates a new CLOS class.</para>
685 <title>Affected by</title>
691 <title>Exceptional Situations</title>
697 <title>See Also</title>
700 <member><link linkend="create-view-from-class"><function>create-view-from-class</function></link></member>
701 <member><link linkend="standard-db-object"><parameter>standard-db-object</parameter></link></member>
702 <member><link linkend="drop-view-from-class"><function>drop-view-from-class</function></link></member>
709 The actual SQL type for a column depends up the database type
710 in which the SQL table is stored. As an example, the view
711 class type <parameter>(varchar 100)</parameter> specifies a
712 SQL column type <parameter>VARCHAR(100)</parameter> in &mysql;
713 and a column type <parameter>VARCHAR2(100)</parameter> in
717 The actual lisp type for a slot may be different than the
718 value specified by the <parameter>:type</parameter> attribute.
719 For example, a slot declared with "<parameter>:type (string
720 30)</parameter>" actually sets the slots Lisp type as
721 <parameter>(or null string)</parameter>. This is to allow a
722 &nil; value or a string shorter than 30 characters to be
728 <refentry id="drop-view-from-class">
730 <refname>DROP-VIEW-FROM-CLASS</refname>
731 <refpurpose>Delete table from SQL database.</refpurpose>
732 <refclass>Function</refclass>
735 <title>Syntax</title>
737 <function>(drop-view-from-class view-class-name &key (database *default-database*))</function> => <returnvalue><!-- result --></returnvalue></synopsis>
740 <title>Arguments and Values</title>
743 <term><parameter>view-class-name</parameter></term>
746 The name of the view class.
753 <title>Description</title>
754 <para>Removes a table defined by the View Class
755 <parameter>VIEW-CLASS-NAME</parameter> from
756 <parameter>DATABASE</parameter> which defaults to
757 <parameter>*DEFAULT-DATABASE*</parameter>.
761 <title>Examples</title>
765 * (drop-view-from-class 'foo)
771 <title>Side Effects</title>
773 Deletes a table from the SQL database.
777 <title>Affected by</title>
779 Whether the specified table exists in the SQL database.
783 <title>Exceptional Situations</title>
785 A condition may be signalled if the table does not exist in
786 the SQL database or if the SQL connection does not have
787 sufficient permissions to delete tables.
791 <title>See Also</title>
794 <member><link linkend="create-view-from-class"><function>create-view-from-class</function></link></member>
795 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>
807 <refentry id="list-classes">
809 <refname>LIST-CLASSES</refname>
810 <refpurpose>List classes for tables in SQL database.</refpurpose>
811 <refclass>Function</refclass>
814 <title>Syntax</title>
816 <function>(list-classes &key (test #'identity) (root-class (find-class 'standard-db-object)) (database *default-database*))</function> => <returnvalue>classes</returnvalue></synopsis>
819 <title>Arguments and Values</title>
822 <term><parameter>test</parameter></term>
825 a function used to filter the search. By default, <parameter>identity</parameter> is used which
826 will return all classes.
831 <term><parameter>root-class</parameter></term>
834 specifies the root class to the search. By default, <parameter>standard-db-object</parameter> is used
835 which is the root for all view classes.
840 <term><parameter>database</parameter></term>
843 The database to search for view classes.
848 <term><parameter>classes</parameter></term>
851 List of view classes.
858 <title>Description</title>
859 <para>Returns a list of all the View Classes which have been
860 defined in the Lisp session and are connected to
861 <parameter>database</parameter> and which descended from the
862 class <parameter>root-class</parameter> and which satisfy the
863 function <parameter>test</parameter>.
867 <title>Examples</title>
870 (#<clsql-sys::standard-db-class big> #<clsql-sys::standard-db-class employee-address>
871 #<clsql-sys::standard-db-class address> #<clsql-sys::standard-db-class company>
872 #<clsql-sys::standard-db-class employee>)
874 * (list-classes :test #'(lambda (c) (> (length (symbol-name (class-name c))) 3)))
875 (#<clsql-sys::standard-db-class employee-address> #<clsql-sys::standard-db-class address>
876 #<clsql-sys::standard-db-class company> #<clsql-sys::standard-db-class employee>)
880 <title>Side Effects</title>
886 <title>Affected by</title>
889 <member>Which view classes have been defined in the Lisp session.</member>
894 <title>Exceptional Situations</title>
900 <title>See Also</title>
903 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>