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>.
27 <note>The above functions refer to the Lisp <emphasis>view</emphasis> of the SQL
28 table. This Lisp view should not be confused with SQL <function>VIEW</function> statement.
32 <refentry id="standard-db-object">
34 <refname>STANDARD-DB-OBJECT</refname>
35 <refpurpose>Superclass for all &clsql; View Classes.</refpurpose>
36 <refclass>Class</refclass>
39 <title>Class Precedence List</title>
41 <simplelist type="inline">
42 <member><type>standard-db-object</type></member>
43 <member><type>standard-object</type></member>
44 <member><type>t</type></member>
49 <title>Description</title> <para>This class is the superclass
50 of all &clsql; View Classes.</para>
53 <title class="contenttitle">Class details</title>
54 <programlisting>(defclass STANDARD-DB-OBJECT ()(...))</programlisting>
57 <title class="contenttitle">Slots</title>
60 <property>slot VIEW-DATABASE is of type (OR NULL DATABASE)
61 which stores the associated database for the
68 <refentry id="default-string-length">
70 <refname>*DEFAULT-STRING-LENGTH*</refname>
71 <refpurpose>Default length of SQL strings.</refpurpose>
72 <refclass>Variable</refclass>
75 <title>Value Type</title>
81 <title>Initial Value</title>
82 <para><parameter>255</parameter></para>
85 <title>Description</title>
87 If a slot of a class defined by
88 <function>DEF-VIEW-CLASS</function> is of the type
89 <parameter>STRING</parameter> or <parameter>VARCHAR</parameter> and does
90 not have a length specified, then the value of this variable
91 is used as SQL length.
95 <title>Examples</title>
97 (let ((*default-string-length* 80))
98 (def-view-class s80 ()
100 (b :type (string 80))
102 (create-view-from-class 's80)
105 The above code causes a SQL table to be created with the SQL command
107 <screen>CREATE TABLE (A VARCHAR(80), B CHAR(80), C VARCHAR(80))</screen>
110 <title>Affected By</title>
111 <para>Some SQL backends do not support <parameter>VARCHAR</parameter>
112 lengths greater than 255 .</para>
115 <title>See Also</title>
120 <para>This is a CLSQL extension to the CommonSQL API.</para>
124 <refentry id="create-view-from-class">
126 <refname>CREATE-VIEW-FROM-CLASS</refname>
127 <refpurpose>Create a SQL table from a view class.</refpurpose>
128 <refclass>Function</refclass>
131 <title>Syntax</title>
133 <function> (create-view-from-class view-class-name &key (database *default-database*) (transactions t))</function> => <returnvalue><!-- no values --></returnvalue></synopsis>
136 <title>Arguments and Values</title>
139 <term><parameter>view-class-name</parameter></term>
142 The name of a view class that has been defined with
143 <link linkend="def-view-class"><function>def-view-class</function></link>.
148 <term><parameter>database</parameter></term>
151 The database in which to create the SQL table.
156 <term><parameter>transactions</parameter></term>
159 When &nil; specifies that a table type which does not support transactions should be used.
166 <title>Description</title>
168 Creates a table as defined by the View Class
169 <parameter>view-class-name</parameter> in <parameter>database</parameter>.
173 <title>Examples</title>
175 * (def-view-class 'foo () ((a :type (string 80))))
176 #<CLSQL-SYS::STANDARD-DB-CLASS FOO>
177 * (create-view-from-class 'foo)
183 <title>Side Effects</title>
185 Causes a table to be created in the SQL database.
189 <title>Affected by</title>
191 Most SQL database systems will signal an error if a table
192 creation is attempted when a table with the same name already
193 exists. The SQL user, as specified in the database connection,
194 must have sufficient permission for table creation.
198 <title>Exceptional Situations</title>
200 A condition will be signaled if the table can not be created
205 <title>See Also</title>
208 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>
209 <member><link linkend="drop-view-from-class"><function>drop-view-from-class</function></link></member>
216 Currently, only &mysql; supports transactionless
217 tables. &clsql; provides the ability to create such tables for
218 applications which would benefit from faster table access and
219 do not require transaction support.
222 The case of the table name is determined by the type of the
223 database. &mysql;, for example, creates databases in upper-case
224 while &postgresql; uses lowercase.
229 <refentry id="def-view-class">
231 <refname>DEF-VIEW-CLASS</refname>
232 <refpurpose>Defines CLOS classes with mapping to SQL database.</refpurpose>
233 <refclass>Macro</refclass>
236 <title>Syntax</title>
238 <function>(def-view-class name superclasses slots &rest class-options)</function> => <returnvalue>class</returnvalue></synopsis>
241 <title>Arguments and Values</title>
244 <term><parameter>name</parameter></term>
252 <term><parameter>name</parameter></term>
255 The superclasses for the defined class.
260 <term><parameter>slots</parameter></term>
263 The class slot definitions.
268 <term><parameter>class</parameter></term>
278 <title>Slot Options</title>
282 <parameter>:db-kind</parameter> - specifies the kind of
283 database mapping which is performed for this slot and defaults
284 to <parameter>:base</parameter> which indicates that the
285 slot maps to an ordinary column of the database table. A
286 <parameter>:db-kind</parameter> value of
287 <parameter>:key</parameter> indicates that this slot is
288 a special kind of <parameter>:base</parameter> slot
289 which maps onto a column which is one of the unique keys
290 for the database table, the value
291 <parameter>:join</parameter> indicates this slot
292 represents a join onto another View Class which contains
293 View Class objects, and the value
294 <parameter>:virtual</parameter> indicates a standard
295 CLOS slot which does not map onto columns of the
301 <parameter>:db-info</parameter> - if a slot is specified with
302 <parameter>:db-kind</parameter> <parameter>:join</parameter>, the
303 slot option <parameter>:db-info</parameter> contains a property list
304 which specifies the nature of the join. The valid members of the list
310 <parameter>:join-class</parameter>
311 <emphasis>class-name</emphasis> - the name of the
317 <parameter>:home-key</parameter>
318 <emphasis>slot-name</emphasis> - the name of the slot
319 of this class for joining
324 <parameter>:foreign-key</parameter>
325 <emphasis>slot-name</emphasis> - the name of the slot
326 of the <parameter>:join-class</parameter> for joining
331 <parameter>:target-slot</parameter>
332 <emphasis>target-slot</emphasis> - this is an optional
333 parameter. If specified, then the join slot of the
334 defining class will contain instances of this target
335 slot rather than of the join class. This can be useful
336 when the <parameter>:join-class</parameter> is an
337 intermediate class in a
338 <emphasis>many-to-many</emphasis> relationship and the
339 application is actually interested in the
340 <parameter>:target-slot</parameter>.
345 <parameter>:retrieval</parameter>
346 <emphasis>time</emphasis> - The default value is
347 <parameter>:deferred</parameter>, which defers filling
348 this slot until the value is accessed. The other valid
349 value is <parameter>:immediate</parameter> which
350 performs the SQL query when the instance of the class
351 is created. In this case, the
352 <parameter>:set</parameter> is automatically set to
358 <parameter>:set</parameter>
359 <emphasis>set</emphasis> - This controls what is stored in the join slot.
360 The default value is &t;. When <emphasis>set</emphasis> is &t; and
361 <emphasis>target-slot</emphasis> is undefined, the join slot will contain
362 a list of instances of the join class. Whereas, if <emphasis>target-slot</emphasis> is defined,
363 then the join slot will contain a list of pairs of <emphasis>(target-value join-instance)</emphasis>.
364 When <emphasis>set</emphasis> is &nil;, the join slot will contain a single instances.
371 <parameter>:type</parameter> - for slots of
372 <parameter>:db-kind</parameter> <parameter>:base</parameter> or
373 <parameter>:key</parameter>, the <parameter>:type</parameter> slot
374 option has a special interpretation such that Lisp
375 types, such as string, integer and float are
376 automatically converted into appropriate SQL types for
377 the column onto which the slot maps. This behaviour may
378 be overridden using the <parameter>:db-type</parameter> slot
379 option. The valid values are:
382 <parameter>string</parameter> - a variable length character field up to
383 <link linkend="default-string-length">*default-string-length*</link> characters.
386 <parameter>(string n)</parameter> - a fixed length character field
387 <parameter>n</parameter> characters long.
390 <parameter>varchar</parameter> - a variable length character field up to
391 <link linkend="default-string-length">*default-string-length*</link> characters.
394 <parameter>(varchar n)</parameter> - a variable length character field up to
395 <parameter>n</parameter> characters in length.
398 <parameter>char</parameter> - a single character field
400 <member><parameter>integer</parameter> - signed integer at least 32-bits wide</member>
401 <member><parameter>(integer n)</parameter></member>
402 <member><parameter>float</parameter></member>
403 <member><parameter>(float n)</parameter></member>
404 <member><parameter>long-float</parameter></member>
405 <member><parameter>number</parameter></member>
406 <member><parameter>(number n)</parameter></member>
407 <member><parameter>(number n p)</parameter></member>
409 <parameter>smallint</parameter> - An integer column 16-bits
410 wide. [not supported by all database backends]
413 <parameter>bigint</parameter> - An integer column
414 64-bits wide. [not supported by all database backends]
417 <parameter>universal-time</parameter> - an integer
418 field sufficiently wide to store a
419 universal-time. On most databases, a slot of this
420 type assigned a SQL type of
421 <parameter>BIGINT</parameter>
424 <parameter>wall-time</parameter> - a slot which
425 stores a date and time in a SQL timestamp
426 column. &clsql; provides a number of time
427 manipulation functions to support objects of type
428 <type>wall-time</type>.
431 <parameter>duration</parameter> - stores a <type>duration</type> structure.
432 &clsql; provides routines for <type>wall-time</type> and <type>duration</type>
435 <member><parameter>boolean</parameter> - stores a &t; or &nil; value.</member>
437 <parameter>generalized-boolean</parameter> - similar
438 to a <parameter>boolean</parameter> in that either a
439 &t; or &nil; value is stored in the SQL
440 database. However, any Lisp object can be stored in
441 the Lisp object. A Lisp value of &nil; is stored as
442 <constant>FALSE</constant> in the database, any
443 other Lisp value is stored as
444 <constant>TRUE</constant>.
447 <parameter>keyword</parameter> - stores a keyword
449 <member><parameter>symbol</parameter> - stores a symbol</member>
451 <parameter>list</parameter> - stores a list by writing it to a string. The items
452 in the list must be able to be readable written.
454 <member><parameter>vector</parameter> - stores a vector similarly to <parameter>list</parameter></member>
455 <member><parameter>array</parameter> - stores a array similarly to <parameter>list</parameter></member>
462 <parameter>:column</parameter> - specifies the name of
463 the SQL column which the slot maps onto, if
464 <parameter>:db-kind</parameter> is not
465 <parameter>:virtual</parameter>, and defaults to the
466 slot name. If the slot name is used for the SQL column
467 name, any hypens in the slot name are converted
468 to underscore characters.
473 <parameter>:void-value</parameter> - specifies
474 the value to store in the Lisp instance if the SQL value is NULL and defaults
480 <parameter>:db-constraints</parameter> - is a string
481 representing an SQL table constraint expression or a
482 list of such strings.
487 <parameter>:db-type</parameter> - a string to specify the SQL
488 column type. If specified, this string overrides the SQL
489 column type as computed from the <parameter>:type</parameter>
496 <title>Class Options</title>
501 <parameter>:base-table</parameter> - specifies the name of the
502 SQL database table. The default value is the class name. Like slot names,
503 hypens in the class name are converted to underscore characters.
510 <title>Description</title>
512 Creates a View Class called <parameter>name</parameter> whose
513 slots <parameter>slots</parameter> can map onto the attributes
514 of a table in a database. If
515 <parameter>superclasses</parameter> is &nil; then the
516 superclass of <parameter>class</parameter> will be
517 <parameter>standard-db-object</parameter>, otherwise
518 <parameter>superclasses</parameter> is a list of superclasses
519 for <parameter>class</parameter> which must include
520 <parameter>standard-db-object</parameter> or a descendent of this
526 <title>Examples</title>
528 The following examples are from the &clsql; test suite.
531 (def-view-class person (thing)
532 ((height :db-kind :base :accessor height :type float
534 (married :db-kind :base :accessor married :type boolean
536 (birthday :type clsql:wall-time :initarg :birthday)
537 (bd-utime :type clsql:universal-time :initarg :bd-utime)
538 (hobby :db-kind :virtual :initarg :hobby :initform nil)))
540 (def-view-class employee (person)
543 :db-constraints :not-null
548 :db-constraints :not-null
554 :initarg :first-name)
560 :accessor employee-email
567 :accessor employee-company
569 :db-info (:join-class company
571 :foreign-key companyid
577 :accessor employee-manager
579 :db-info (:join-class employee
584 :accessor employee-addresses
586 :db-info (:join-class employee-address
591 (:base-table employee))
593 (def-view-class company ()
596 :db-constraints :not-null
601 :db-constraints :not-null
609 :initarg :presidentid)
613 :db-info (:join-class employee
614 :home-key presidentid
618 :reader company-employees
620 :db-info (:join-class employee
621 :home-key (companyid groupid)
622 :foreign-key (ecompanyid groupid)
625 (def-view-class address ()
628 :db-constraints :not-null
633 :initarg :street-number)
637 :initarg :street-name)
640 :void-value "no city"
647 :initarg :postal-code))
650 ;; many employees can reside at many addressess
651 (def-view-class employee-address ()
652 ((aemplid :type integer :initarg :emplid)
653 (aaddressid :type integer :initarg :addressid)
654 (verified :type boolean :initarg :verified)
655 (address :db-kind :join
656 :db-info (:join-class address
658 :foreign-key addressid
659 :retrieval :immediate)))
660 (:base-table "ea_join"))
662 (def-view-class deferred-employee-address ()
663 ((aemplid :type integer :initarg :emplid)
664 (aaddressid :type integer :initarg :addressid)
665 (verified :type boolean :initarg :verified)
666 (address :db-kind :join
667 :db-info (:join-class address
669 :foreign-key addressid
672 (:base-table "ea_join"))
676 <title>Side Effects</title>
677 <para>Creates a new CLOS class.</para>
680 <title>Affected by</title>
686 <title>Exceptional Situations</title>
692 <title>See Also</title>
695 <member><link linkend="create-view-from-class"><function>create-view-from-class</function></link></member>
696 <member><link linkend="standard-db-object"><parameter>standard-db-object</parameter></link></member>
697 <member><link linkend="drop-view-from-class"><function>drop-view-from-class</function></link></member>
704 The actual SQL type for a column depends up the database type
705 in which the SQL table is stored. As an example, the view
706 class type <parameter>(varchar 100)</parameter> specifies a
707 SQL column type <parameter>VARCHAR(100)</parameter> in &mysql;
708 and a column type <parameter>VARCHAR2(100)</parameter> in
712 The actual lisp type for a slot may be different than the
713 value specified by the <parameter>:type</parameter> attribute.
714 For example, a slot declared with "<parameter>:type (string
715 30)</parameter>" actually sets the slots Lisp type as
716 <parameter>(or null string)</parameter>. This is to allow a
717 &nil; value or a string shorter than 30 characters to be
723 <refentry id="drop-view-from-class">
725 <refname>DROP-VIEW-FROM-CLASS</refname>
726 <refpurpose>Delete table from SQL database.</refpurpose>
727 <refclass>Function</refclass>
730 <title>Syntax</title>
732 <function>(drop-view-from-class view-class-name &key (database *default-database*))</function> => <returnvalue><!-- result --></returnvalue></synopsis>
735 <title>Arguments and Values</title>
738 <term><parameter>view-class-name</parameter></term>
741 The name of the view class.
748 <title>Description</title>
749 <para>Removes a table defined by the View Class
750 <parameter>VIEW-CLASS-NAME</parameter> from
751 <parameter>DATABASE</parameter> which defaults to
752 <parameter>*DEFAULT-DATABASE*</parameter>.
756 <title>Examples</title>
760 * (drop-view-from-class 'foo)
766 <title>Side Effects</title>
768 Deletes a table from the SQL database.
772 <title>Affected by</title>
774 Whether the specified table exists in the SQL database.
778 <title>Exceptional Situations</title>
780 A condition may be signalled if the table does not exist in
781 the SQL database or if the SQL connection does not have
782 sufficient permissions to delete tables.
786 <title>See Also</title>
789 <member><link linkend="create-view-from-class"><function>create-view-from-class</function></link></member>
790 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>
802 <refentry id="list-classes">
804 <refname>LIST-CLASSES</refname>
805 <refpurpose>List classes for tables in SQL database.</refpurpose>
806 <refclass>Function</refclass>
809 <title>Syntax</title>
811 <function>(list-classes &key (test #'identity) (root-class (find-class 'standard-db-object)) (database *default-database*))</function> => <returnvalue>classes</returnvalue></synopsis>
814 <title>Arguments and Values</title>
817 <term><parameter>test</parameter></term>
820 a function used to filter the search. By default, <parameter>identity</parameter> is used which
821 will return all classes.
826 <term><parameter>root-class</parameter></term>
829 specifies the root class to the search. By default, <parameter>standard-db-object</parameter> is used
830 which is the root for all view classes.
835 <term><parameter>database</parameter></term>
838 The database to search for view classes.
843 <term><parameter>classes</parameter></term>
846 List of view classes.
853 <title>Description</title>
854 <para>Returns a list of all the View Classes which have been
855 defined in the Lisp session and are connected to
856 <parameter>database</parameter> and which descended from the
857 class <parameter>root-class</parameter> and which satisfy the
858 function <parameter>test</parameter>.
862 <title>Examples</title>
865 (#<clsql-sys::standard-db-class big> #<clsql-sys::standard-db-class employee-address>
866 #<clsql-sys::standard-db-class address> #<clsql-sys::standard-db-class company>
867 #<clsql-sys::standard-db-class employee>)
869 * (list-classes :test #'(lambda (c) (> (length (symbol-name (class-name c))) 3)))
870 (#<clsql-sys::standard-db-class employee-address> #<clsql-sys::standard-db-class address>
871 #<clsql-sys::standard-db-class company> #<clsql-sys::standard-db-class employee>)
875 <title>Side Effects</title>
881 <title>Affected by</title>
884 Which view classes have been defined in the Lisp session.
889 <title>Exceptional Situations</title>
895 <title>See Also</title>
898 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>