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>
167 <para>Creates a table as defined by the View Class
168 VIEW-CLASS-NAME in DATABASE which defaults to
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.
224 <refentry id="def-view-class">
226 <refname>DEF-VIEW-CLASS</refname>
227 <refpurpose>Defines CLOS classes with mapping to SQL database.</refpurpose>
228 <refclass>Macro</refclass>
231 <title>Syntax</title>
233 <function>(def-view-class name superclasses slots &rest class-options) [macro]</function> => <returnvalue>class</returnvalue></synopsis>
236 <title>Arguments and Values</title>
239 <term><parameter>name</parameter></term>
247 <term><parameter>name</parameter></term>
250 The superclasses for the defined class.
255 <term><parameter>slots</parameter></term>
258 The class slot definitions.
263 <term><parameter>class</parameter></term>
273 <title>Slot Options</title>
277 <parameter>:db-kind</parameter> - specifies the kind of
278 DB mapping which is performed for this slot and defaults
279 to <parameter>:base</parameter> which indicates that the
280 slot maps to an ordinary column of the database table. A
281 <parameter>:db-kind</parameter> value of
282 <parameter>:key</parameter> indicates that this slot is
283 a special kind of <parameter>:base</parameter> slot
284 which maps onto a column which is one of the unique keys
285 for the database table, the value
286 <parameter>:join</parameter> indicates this slot
287 represents a join onto another View Class which contains
288 View Class objects, and the value
289 <parameter>:virtual</parameter> indicates a standard
290 CLOS slot which does not map onto columns of the
296 <parameter>:db-info</parameter> - if a slot is specified with
297 <parameter>:db-kind</parameter> <parameter>:join</parameter>, the
298 slot option <parameter>:db-info</parameter> contains a list
299 which specifies the nature of the join.
304 <parameter>:type</parameter> - for slots of
305 <parameter>:db-kind</parameter> <parameter>:base</parameter> or
306 <parameter>:key</parameter>, the <parameter>:type</parameter> slot
307 option has a special interpretation such that Lisp
308 types, such as string, integer and float are
309 automatically converted into appropriate SQL types for
310 the column onto which the slot maps. This behaviour may
311 be overridden using the <parameter>:db-type</parameter> slot
312 option. The valid values are:
315 <parameter>string</parameter> - a variable length character field up to
316 <link linkend="default-string-length">*default-string-length*</link> characters.
319 <parameter>(string n)</parameter> - a fixed length character field
320 <parameter>n</parameter> characters long.
323 <parameter>varchar</parameter> - a variable length character field up to
324 <link linkend="default-string-length">*default-string-length*</link> characters.
327 <parameter>(varchar n)</parameter> - a variable length character field up to
328 <parameter>n</parameter> characters in length.
331 <parameter>char</parameter> - a single character field
333 <member><parameter>integer</parameter> - signed integer at least 32-bits wide</member>
334 <member><parameter>(integer n)</parameter></member>
335 <member><parameter>float</parameter></member>
336 <member><parameter>(float n)</parameter></member>
337 <member><parameter>long-float</parameter></member>
338 <member><parameter>number</parameter></member>
339 <member><parameter>(number n)</parameter></member>
340 <member><parameter>(number n p)</parameter></member>
342 <parameter>smallint</parameter> - An integer column 16-bits
343 wide. [not supported by all database backends]
346 <parameter>bigint</parameter> - An integer column
347 64-bits wide. [not supported by all database backends]
350 <parameter>universal-time</parameter> - an integer
351 field sufficiently wide to store a
352 universal-time. On most databases, a slot of this
353 type assigned a SQL type of
354 <parameter>BIGINT</parameter>
357 <parameter>wall-time</parameter> - a slot which
358 stores a date and time in a SQL timestamp
359 column. &clsql; provides a number of time
360 manipulation functions to support objects of type
361 <type>wall-time</type>.
364 <parameter>duration</parameter> - stores a <type>duration</type> structure.
365 &clsql; provides routines for <type>wall-time</type> and <type>duration</type>
368 <member><parameter>boolean</parameter> - stores a &t; or &nil; value.</member>
370 <parameter>generalized-boolean</parameter> - similar
371 to a <parameter>boolean</parameter> in that either a
372 &t; or &nil; value is stored in the SQL
373 database. However, any Lisp object can be stored in
374 the Lisp object. A Lisp value of &nil; is stored as
375 <constant>FALSE</constant> in the database, any
376 other Lisp value is stored as
377 <constant>TRUE</constant>.
380 <parameter>keyword</parameter> - stores a keyword
382 <member><parameter>symbol</parameter> - stores a symbol</member>
384 <parameter>list</parameter> - stores a list by writing it to a string. The items
385 in the list must be able to be readable written.
387 <member><parameter>vector</parameter> - stores a vector similarly to <parameter>list</parameter></member>
388 <member><parameter>array</parameter> - stores a array similarly to <parameter>list</parameter></member>
395 <parameter>:column</parameter> - specifies the name of
396 the SQL column which the slot maps onto, if
397 <parameter>:db-kind</parameter> is not
398 <parameter>:virtual</parameter>, and defaults to the
404 <parameter>:void-value</parameter> - specifies
405 the value to store in the Lisp instance if the SQL value is NULL and defaults
411 <parameter>:db-constraints</parameter> - is a string
412 representing an SQL table constraint expression or a
413 list of such strings.
418 <parameter>:db-type</parameter> - a string to specify the SQL
419 column type. If specified, this string overrides the SQL
420 column type as computed from the <parameter>:type</parameter>
427 <title>Class Options</title>
432 <parameter>:base-table</parameter> - specifies the name of the
433 SQL database table. The default value is the class name.
440 <title>Description</title>
442 Creates a View Class called <parameter>NAME</parameter> whose
443 slots <parameter>SLOTS</parameter> can map onto the attributes
444 of a table in a database. If
445 <parameter>SUPERCLASSES</parameter> is &nil; then the
446 superclass of <parameter>CLASS</parameter> will be
447 <parameter>STANDARD-DB-OBJECT</parameter>, otherwise
448 <parameter>SUPERCLASSES</parameter> is a list of superclasses
449 for <parameter>CLASS</parameter> which must include
450 <parameter>STANDARD-DB-OBJECT</parameter> or a descendent of this
456 <title>Examples</title>
462 <title>Side Effects</title>
463 <para>Creates a new CLOS class.</para>
466 <title>Affected by</title>
472 <title>Exceptional Situations</title>
478 <title>See Also</title>
481 <member><link linkend="create-view-from-class"><function>create-view-from-class</function></link></member>
482 <member><link linkend="standard-db-object"><parameter>standard-db-object</parameter></link></member>
483 <member><link linkend="drop-view-from-class"><function>drop-view-from-class</function></link></member>
490 The actual SQL type for a column depends up the database type
491 in which the SQL table is stored. As an example, the view
492 class type <parameter>(varchar 100)</parameter> specifies a
493 SQL column type <parameter>VARCHAR(100)</parameter> in &mysql;
494 and a column type <parameter>VARCHAR2(100)</parameter> in
498 The actual lisp type for a slot may be different than the
499 value specified by the <parameter>:type</parameter> attribute.
500 For example, a slot declared with "<parameter>:type (string
501 30)</parameter>" actually sets the slots Lisp type as
502 <parameter>(or null string)</parameter>. This is to allow a
503 &nil; value or a string shorter than 30 characters to be
509 <refentry id="drop-view-from-class">
511 <refname>DROP-VIEW-FROM-CLASS</refname>
512 <refpurpose>Delete table from SQL database.</refpurpose>
513 <refclass>Function</refclass>
516 <title>Syntax</title>
518 <function>(drop-view-from-class view-class-name &key (database *default-database*))</function> => <returnvalue><!-- result --></returnvalue></synopsis>
521 <title>Arguments and Values</title>
524 <term><parameter>view-class-name</parameter></term>
527 The name of the view class.
534 <title>Description</title>
535 <para>Removes a table defined by the View Class
536 <parameter>VIEW-CLASS-NAME</parameter> from
537 <parameter>DATABASE</parameter> which defaults to
538 <parameter>*DEFAULT-DATABASE*</parameter>.
542 <title>Examples</title>
546 * (drop-view-from-class 'foo)
552 <title>Side Effects</title>
554 Deletes a table from the SQL database.
558 <title>Affected by</title>
560 Whether the specified table exists in the SQL database.
564 <title>Exceptional Situations</title>
566 A condition may be signalled if the table does not exist in
567 the SQL database or if the SQL connection does not have
568 sufficient permissions to delete tables.
572 <title>See Also</title>
575 <member><link linkend="create-view-from-class"><function>create-view-from-class</function></link></member>
576 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>
588 <refentry id="list-classes">
590 <refname>LIST-CLASSES</refname>
591 <refpurpose>List classes for tables in SQL database.</refpurpose>
592 <refclass>Function</refclass>
595 <title>Syntax</title>
597 <function>(list-classes &key (test #'identity) (root-class (find-class 'standard-db-object)) (database *default-database*))</function> => <returnvalue>classes</returnvalue></synopsis>
600 <title>Arguments and Values</title>
603 <term><parameter>test</parameter></term>
606 a function used to filter the search. By default, <parameter>identity</parameter> is used which
607 will return all classes.
612 <term><parameter>root-class</parameter></term>
615 specifies the root class to the search. By default, <parameter>standard-db-object</parameter> is used
616 which is the root for all view classes.
621 <term><parameter>database</parameter></term>
624 The database to search for view classes.
629 <term><parameter>classes</parameter></term>
632 List of view classes.
639 <title>Description</title>
640 <para>Returns a list of all the View Classes which have been
641 defined in the Lisp session and are connected to
642 <parameter>DATABASE</parameter>, which defaults to
643 <parameter>*DEFAULT-DATABASE*</parameter>, and which descended
644 from the class ROOT-CLASS and which satisfy the function
645 TEST. By default ROOT-CLASS is STANDARD-DB-OBJECT and
646 <parameter>TEST</parameter> is IDENTITY.
650 <title>Examples</title>
653 (#<clsql-sys::standard-db-class big> #<clsql-sys::standard-db-class employee-address>
654 #<clsql-sys::standard-db-class address> #<clsql-sys::standard-db-class company>
655 #<clsql-sys::standard-db-class employee>)
657 * (list-classes :test #'(lambda (c) (> (length (symbol-name (class-name c))) 3)))
658 (#<clsql-sys::standard-db-class employee-address> #<clsql-sys::standard-db-class address>
659 #<clsql-sys::standard-db-class company> #<clsql-sys::standard-db-class employee>)
663 <title>Side Effects</title>
669 <title>Affected by</title>
672 Which view classes have been defined in the Lisp session.
677 <title>Exceptional Situations</title>
683 <title>See Also</title>
686 <member><link linkend="def-view-class"><function>def-view-class</function></link></member>