54a7d8c3646701d64a88eacf192e760f7f145767
[clsql.git] / doc / ref-ooddl.xml
1 <?xml version='1.0' ?>
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">
5 %myents;
6 ]>
7
8 <!-- Object Oriented Data Definition Language --> 
9 <reference id="ref-ooddl"> 
10   <title>Object Oriented Data Definition Language (OODDL)</title> 
11     <partintro>
12       <para>
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.
17       </para>
18       <para> 
19         The mapping between SQL tables and CLOS objects is defined
20         with the macro <link
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>.
26       </para>
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.
29       </note>
30   </partintro>
31
32   <refentry id="standard-db-object">
33     <refnamediv>
34       <refname>STANDARD-DB-OBJECT</refname>
35       <refpurpose>Superclass for all &clsql; View Classes.</refpurpose>
36       <refclass>Class</refclass>
37     </refnamediv>
38     <refsect1>
39       <title>Class Precedence List</title>
40       <para>
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>
45         </simplelist>
46       </para>
47     </refsect1>
48     <refsect1>
49       <title>Description</title> <para>This class is the superclass
50       of all &clsql; View Classes.</para>
51     </refsect1>
52     <refsect1>
53       <title class="contenttitle">Class details</title>
54       <programlisting>(defclass STANDARD-DB-OBJECT ()(...))</programlisting>
55     </refsect1>
56     <refsect1>
57       <title class="contenttitle">Slots</title>
58       <para>
59         <simplelist> 
60           <property>slot VIEW-DATABASE is of type (OR NULL DATABASE)
61           which stores the associated database for the
62           instance.</property>
63         </simplelist> 
64       </para>
65     </refsect1>
66   </refentry>
67
68   <refentry id="default-string-length">
69     <refnamediv>
70       <refname>*DEFAULT-STRING-LENGTH*</refname>
71       <refpurpose>Default length of SQL strings.</refpurpose>
72       <refclass>Variable</refclass>
73     </refnamediv>
74     <refsect1>
75       <title>Value Type</title>
76       <para>
77         Fixnum
78       </para> 
79     </refsect1>
80     <refsect1>
81       <title>Initial Value</title>
82       <para><parameter>255</parameter></para>
83     </refsect1>
84     <refsect1>
85       <title>Description</title> 
86       <para>
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.
92       </para>
93     </refsect1>
94     <refsect1>
95       <title>Examples</title>
96       <screen>
97         (let ((*default-string-length* 80))
98           (def-view-class s80 ()
99             ((a :type string)
100              (b :type (string 80))
101              (c :type varchar))))
102           (create-view-from-class 's80)   
103       </screen>
104       <para>
105         The above code causes a SQL table to be created with the SQL command
106       </para>
107       <screen>CREATE TABLE (A VARCHAR(80), B CHAR(80), C VARCHAR(80))</screen>
108     </refsect1>
109     <refsect1>
110       <title>Affected By</title>
111       <para>Some SQL backends do not support <parameter>VARCHAR</parameter>
112       lengths greater than 255 .</para>
113     </refsect1>
114     <refsect1>
115       <title>See Also</title>
116       <para>None.</para>
117     </refsect1>
118     <refsect1>
119       <title>Notes</title>
120       <para>This is a CLSQL extension to the CommonSQL API.</para>
121     </refsect1>
122   </refentry>
123
124   <refentry id="create-view-from-class">
125     <refnamediv>
126       <refname>CREATE-VIEW-FROM-CLASS</refname>
127       <refpurpose>Create a SQL table from a view class.</refpurpose>
128       <refclass>Function</refclass>
129     </refnamediv>
130     <refsect1>
131       <title>Syntax</title>
132       <synopsis>
133       <function> (create-view-from-class view-class-name &amp;key (database *default-database*) (transactions t))</function> => <returnvalue><!-- no values --></returnvalue></synopsis>
134     </refsect1>
135     <refsect1>
136       <title>Arguments and Values</title>
137       <variablelist>
138         <varlistentry>
139           <term><parameter>view-class-name</parameter></term>
140           <listitem>
141             <para>
142               The name of a view class that has been defined with
143               <link linkend="def-view-class"><function>def-view-class</function></link>.
144             </para>
145           </listitem>
146         </varlistentry>
147         <varlistentry>
148           <term><parameter>database</parameter></term>
149           <listitem>
150             <para>
151               The database in which to create the SQL table.
152             </para>
153           </listitem>
154         </varlistentry>
155         <varlistentry>
156           <term><parameter>transactions</parameter></term>
157           <listitem>
158             <para>
159               When &nil; specifies that a table type which does not support transactions should be used.
160             </para>
161           </listitem>
162         </varlistentry>
163       </variablelist>
164     </refsect1>
165     <refsect1>
166       <title>Description</title>
167       <para>Creates a table as defined by the View Class
168       VIEW-CLASS-NAME in DATABASE which defaults to
169       *DEFAULT-DATABASE*.
170       </para>
171     </refsect1>
172     <refsect1>
173       <title>Examples</title>
174       <screen>
175         * (def-view-class 'foo () ((a :type (string 80))))
176         #&lt;CLSQL-SYS::STANDARD-DB-CLASS FOO>
177         * (create-view-from-class 'foo)
178         * (list-tables)
179         ("FOO")
180       </screen>
181     </refsect1>
182     <refsect1>
183       <title>Side Effects</title>
184       <para>
185         Causes a table to be created in the SQL database.
186       </para>
187     </refsect1>
188     <refsect1>
189       <title>Affected by</title>
190       <para>
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.
195       </para>
196     </refsect1>
197     <refsect1>
198       <title>Exceptional Situations</title>
199       <para>
200         A condition will be signaled if the table can not be created
201         in the SQL database.
202       </para>
203     </refsect1>
204     <refsect1>
205       <title>See Also</title>
206       <para>
207         <simplelist>
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>
210         </simplelist>
211       </para>
212     </refsect1>
213     <refsect1>
214       <title>Notes</title>
215       <para>
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.
220       </para>
221     </refsect1>
222   </refentry>
223
224   <refentry id="def-view-class">
225     <refnamediv>
226       <refname>DEF-VIEW-CLASS</refname>
227       <refpurpose>Defines CLOS classes with mapping to SQL database.</refpurpose>
228       <refclass>Macro</refclass>
229     </refnamediv>
230     <refsect1>
231       <title>Syntax</title>
232       <synopsis>
233       <function>(def-view-class name superclasses slots &amp;rest class-options) [macro]</function> => <returnvalue>class</returnvalue></synopsis>
234     </refsect1>
235     <refsect1>
236       <title>Arguments and Values</title>
237       <variablelist>
238         <varlistentry>
239           <term><parameter>name</parameter></term>
240           <listitem>
241             <para>
242               The class name.
243             </para>
244           </listitem>
245         </varlistentry>
246         <varlistentry>
247           <term><parameter>name</parameter></term>
248           <listitem>
249             <para>
250               The superclasses for the defined class.
251             </para>
252           </listitem>
253         </varlistentry>
254         <varlistentry>
255           <term><parameter>slots</parameter></term>
256           <listitem>
257             <para>
258               The class slot definitions.
259             </para>
260           </listitem>
261         </varlistentry>
262         <varlistentry>
263           <term><parameter>class</parameter></term>
264           <listitem>
265             <para>
266               The defined class.
267             </para>
268           </listitem>
269         </varlistentry>
270       </variablelist>
271     </refsect1>
272     <refsect1>
273       <title>Slot Options</title>
274         <itemizedlist>
275           <listitem>
276             <para>
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
291               database table.
292             </para>
293           </listitem>
294           <listitem>
295             <para>
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.
300             </para>
301           </listitem>
302           <listitem>
303             <para>
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:
313               <simplelist>
314                 <member>
315                   <parameter>string</parameter> - a variable length character field up to
316                   <link linkend="default-string-length">*default-string-length*</link> characters.
317                 </member>
318                 <member>
319                   <parameter>(string n)</parameter> - a fixed length character field 
320                   <parameter>n</parameter> characters long.
321                 </member>
322                 <member>
323                   <parameter>varchar</parameter> - a variable length character field up to
324                   <link linkend="default-string-length">*default-string-length*</link> characters.
325                 </member>
326                 <member>
327                   <parameter>(varchar n)</parameter> - a variable length character field up to
328                   <parameter>n</parameter> characters in length.
329                 </member>
330                 <member>
331                   <parameter>char</parameter> - a single character field 
332                 </member>
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>
341                 <member>
342                   <parameter>smallint</parameter> - An integer column 16-bits
343                   wide. [not supported by all database backends]
344                 </member>
345                 <member>
346                   <parameter>bigint</parameter> - An integer column 
347                   64-bits wide. [not supported by all database backends]
348                 </member>
349                 <member>
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>
355                 </member>
356                 <member>
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>.
362                 </member>
363                 <member>
364                   <parameter>duration</parameter> - stores a <type>duration</type> structure.
365                   &clsql; provides routines for <type>wall-time</type> and <type>duration</type>
366                   processing.
367                 </member>
368                 <member><parameter>boolean</parameter> - stores a &t; or &nil; value.</member>
369                 <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>.
378                 </member>
379                 <member>
380                   <parameter>keyword</parameter> - stores a keyword
381                 </member>       
382                 <member><parameter>symbol</parameter> - stores a symbol</member>
383                 <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.
386                 </member>
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>
389               </simplelist>
390             </para>
391             
392           </listitem>
393           <listitem>
394             <para>
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
399               slot name.
400             </para>
401           </listitem>
402           <listitem>
403           <para>
404             <parameter>:void-value</parameter> - specifies
405               the value to store in the Lisp instance if the SQL value is NULL and defaults
406               to NIL.
407           </para>
408           </listitem>
409           <listitem>
410             <para>
411               <parameter>:db-constraints</parameter> - is a string
412               representing an SQL table constraint expression or a
413               list of such strings.
414             </para>
415           </listitem>
416           <listitem>
417             <para>
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>
421               slot value.
422             </para>
423           </listitem>
424       </itemizedlist>
425     </refsect1>
426     <refsect1>
427       <title>Class Options</title>
428       <para>
429         <itemizedlist>
430           <listitem>
431             <para>
432               <parameter>:base-table</parameter> - specifies the name of the
433               SQL database table. The default value is the class name.
434             </para>
435           </listitem>
436         </itemizedlist>
437       </para>
438     </refsect1>
439     <refsect1>
440       <title>Description</title>
441       <para>
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
451         class.
452       </para>
453
454     </refsect1>
455     <refsect1>
456       <title>Examples</title>
457       <screen>
458         <!-- examples -->
459       </screen>
460     </refsect1>
461     <refsect1>
462       <title>Side Effects</title>
463       <para>Creates a new CLOS class.</para>
464     </refsect1>
465     <refsect1>
466       <title>Affected by</title>
467       <para>
468         Nothing.
469       </para>
470     </refsect1>
471     <refsect1>
472       <title>Exceptional Situations</title>
473       <para>
474         None.
475       </para>
476     </refsect1>
477     <refsect1>
478       <title>See Also</title>
479       <para>
480         <simplelist>
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>
484         </simplelist>
485       </para>
486     </refsect1>
487     <refsect1>
488       <title>Notes</title>
489       <para>
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
495         &oracle;
496       </para>
497       <para>
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
504         stored in the slot.
505       </para>
506     </refsect1>
507   </refentry>
508
509   <refentry id="drop-view-from-class">
510     <refnamediv>
511       <refname>DROP-VIEW-FROM-CLASS</refname>
512       <refpurpose>Delete table from SQL database.</refpurpose>
513       <refclass>Function</refclass>
514     </refnamediv>
515     <refsect1>
516       <title>Syntax</title>
517       <synopsis>
518       <function>(drop-view-from-class view-class-name &amp;key (database *default-database*))</function> => <returnvalue><!-- result --></returnvalue></synopsis>
519     </refsect1>
520     <refsect1>
521       <title>Arguments and Values</title>
522       <variablelist>
523         <varlistentry>
524           <term><parameter>view-class-name</parameter></term>
525           <listitem>
526             <para>
527               The name of the view class.
528             </para>
529           </listitem>
530         </varlistentry>
531       </variablelist>
532     </refsect1>
533     <refsect1>
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>.
539       </para>
540     </refsect1>
541     <refsect1>
542       <title>Examples</title>
543       <screen>
544         * (list-tables)
545         ("FOO" "BAR")   
546         * (drop-view-from-class 'foo)
547         * (list-tables)
548         ("BAR") 
549       </screen>
550     </refsect1>
551     <refsect1>
552       <title>Side Effects</title>
553       <para>
554         Deletes a table from the SQL database.
555       </para>
556     </refsect1>
557     <refsect1>
558       <title>Affected by</title>
559       <para>
560         Whether the specified table exists in the SQL database.
561       </para>
562     </refsect1>
563     <refsect1>
564       <title>Exceptional Situations</title>
565       <para>
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.
569       </para>
570     </refsect1>
571     <refsect1>
572       <title>See Also</title>
573       <para>
574         <simplelist>
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>
577         </simplelist>
578       </para>
579     </refsect1>
580     <refsect1>
581       <title>Notes</title>
582       <para>
583         <!-- notes --> 
584       </para>
585     </refsect1>
586   </refentry>
587
588   <refentry id="list-classes">
589     <refnamediv>
590       <refname>LIST-CLASSES</refname>
591       <refpurpose>List classes for tables in SQL database.</refpurpose>
592       <refclass>Function</refclass>
593     </refnamediv>
594     <refsect1>
595       <title>Syntax</title>
596       <synopsis>
597       <function>(list-classes &amp;key (test #'identity) (root-class (find-class 'standard-db-object)) (database *default-database*))</function> => <returnvalue>classes</returnvalue></synopsis>
598     </refsect1>
599     <refsect1>
600       <title>Arguments and Values</title>
601       <variablelist>
602         <varlistentry>
603           <term><parameter>test</parameter></term>
604           <listitem>
605             <para>
606               a function used to filter the search. By default, <parameter>identity</parameter> is used which
607               will return all classes.
608             </para>
609           </listitem>
610         </varlistentry>
611         <varlistentry>
612           <term><parameter>root-class</parameter></term>
613           <listitem>
614             <para>
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.
617             </para>
618           </listitem>
619         </varlistentry>
620         <varlistentry>
621           <term><parameter>database</parameter></term>
622           <listitem>
623             <para>
624               The database to search for view classes.
625             </para>
626           </listitem>
627         </varlistentry>
628         <varlistentry>
629           <term><parameter>classes</parameter></term>
630           <listitem>
631             <para>
632               List of view classes.
633             </para>
634           </listitem>
635         </varlistentry>
636       </variablelist>
637     </refsect1>
638     <refsect1>
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.
647       </para>
648     </refsect1>
649     <refsect1>
650       <title>Examples</title>
651       <screen>
652         * (list-classes)
653         (#&lt;clsql-sys::standard-db-class big> #&lt;clsql-sys::standard-db-class employee-address>
654         #&lt;clsql-sys::standard-db-class address> #&lt;clsql-sys::standard-db-class company> 
655         #&lt;clsql-sys::standard-db-class employee>)
656
657         * (list-classes :test #'(lambda (c) (> (length (symbol-name (class-name c))) 3)))
658         (#&lt;clsql-sys::standard-db-class employee-address> #&lt;clsql-sys::standard-db-class address>
659         #&lt;clsql-sys::standard-db-class company> #&lt;clsql-sys::standard-db-class employee>)
660       </screen>
661     </refsect1>
662     <refsect1>
663       <title>Side Effects</title>
664       <para>
665         None.
666       </para>
667     </refsect1>
668     <refsect1>
669       <title>Affected by</title>
670       <para>
671         <simplelist>
672           Which view classes have been defined in the Lisp session.
673         </simplelist>
674       </para>
675     </refsect1>
676     <refsect1>
677       <title>Exceptional Situations</title>
678       <para>
679         None.
680       </para>
681     </refsect1>
682     <refsect1>
683       <title>See Also</title>
684       <para>
685         <simplelist>
686           <member><link linkend="def-view-class"><function>def-view-class</function></link></member>
687         </simplelist>
688       </para>
689     </refsect1>
690     <refsect1>
691       <title>Notes</title>
692       <para>
693         <!-- notes --> 
694       </para>
695     </refsect1>
696   </refentry>
697
698
699 </reference>