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">
9 <title>&commonsql; Tutorial</title>
10 <subtitle>Based on the &usql; Tutorial</subtitle>
12 <sect1 id="csql-intro">
13 <title>Introduction</title>
16 The goal of this tutorial is to guide a new developer thru the
17 process of creating a set of &clsql; classes providing a
18 Object-Oriented interface to persistent data stored in an &sql;
19 database. We will assume that the reader is familiar with how
20 &sql; works, how relations (tables) should be structured, and
21 has created at least one &sql; application previously. We will
22 also assume a minor level of experience with Common Lisp.
26 &clsql; provides two different interfaces to &sql; databases, a
27 Functional interface, and an Object-Oriented interface. The
28 Functional interface consists of a special syntax for embedded
29 &sql; expressions in Lisp, and provides lisp functions for &sql;
30 operations like <symbol>SELECT</symbol> and
31 <symbol>UPDATE</symbol>. The object-oriented interface provides
32 a way for mapping Common Lisp Objects System (CLOS) objects into
33 databases and includes functions for inserting new objects,
34 querying objects, and removing objects. Most applications will
35 use a combination of the two.
39 &clsql; is based on the CommonSQL package from LispWorks Ltd, so the
40 documentation that LispWorks makes available online is useful for
41 &clsql; as well. It is suggested that developers new to &clsql; read
42 their documentation as well, as any differences between CommonSQL
43 and &clsql; are minor. LispWorks makes the following documents
50 <ulink url="http://www.lispworks.com/documentation/lw44/LWUG/html/lwuser-204.htm">
51 <citetitle>&lw; User Guide - The &commonsql;
60 <ulink url="http://www.lispworks.com/documentation/lw44/LWRM/html/lwref-424.htm">
61 <citetitle>&lw; Reference Manual - The SQL
69 <ulink url="http://www.lispworks.com/documentation/sql-tutorial/index.html">
70 <citetitle>&commonsql; Tutorial by Nick Levine</citetitle>
78 <title>Data Modeling with &clsql;</title>
81 Before we can create, query and manipulate &clsql; objects, we
82 need to define our data model as noted by Philip Greenspun
86 url="http://philip.greenspun.com/sql/data-modeling.html">
87 <citetitle>Philip Greenspun's "SQL For Web Nerds" - Data
95 When data modeling, you are telling the relational database
96 management system (RDBMS) the following:
101 <para>What elements of the data you will store.</para>
104 <para>How large each element can be.</para>
107 <para>What kind of information each element can contain.</para>
110 <para>What elements may be left blank.</para>
113 <para>Which elements are constrained to a fixed range.</para>
116 <para>Whether and how various tables are to be linked.</para>
121 With &sql; database one would do this by defining a set of
122 relations, or tables, followed by a set of queries for joining
123 the tables together in order to construct complex records.
124 However, with &clsql; we do this by defining a set of CLOS
125 classes, specifying how they will be turned into tables, and how
126 they can be joined to one another via relations between their
127 attributes. The &sql; tables, as well as the queries for
128 joining them together are created for us automatically, saving
129 us from dealing with some of the tedium of &sql;.
133 Let us start with a simple example of two &sql; tables, and the
134 relations between them.
138 CREATE TABLE EMPLOYEE ( emplid NOT NULL number(38),
139 first_name NOT NULL varchar2(30),
140 last_name NOT NULL varchar2(30),
142 companyid NOT NULL number(38),
143 managerid number(38))
145 CREATE TABLE COMPANY ( companyid NOT NULL number(38),
146 name NOT NULL varchar2(100),
147 presidentid NOT NULL number(38))
151 This is of course the canonical &sql; tutorial example, "The Org Chart".
155 In &clsql;, we would have two "view classes" (a fancy word for a class
156 mapped into a database). They would be defined as follows:
160 (clsql:def-view-class employee ()
163 :db-constraints :not-null
169 :initarg :first-name)
175 :accessor employee-email
185 :initarg :managerid))
186 (:base-table employee))
188 (clsql:def-view-class company ()
191 :db-constraints :not-null
199 :initarg :presidentid))
200 (:base-table company))
204 The <function>DEF-VIEW-CLASS</function> macro is just like the
205 normal CLOS <function>DEFCLASS</function> macro, except that it
206 handles several slot options that <function>DEFCLASS</function>
207 doesn't. These slot options have to do with the mapping of the slot
208 into the database. We only use a few of the slot options in the
209 above example, but there are several others.
215 <symbol>:column</symbol> - The name of the &sql; column this slot is stored in.
216 Defaults to the slot name. If the slot name is not a valid &sql;
217 identifier, it is escaped, so foo-bar becomes foo_bar.
222 <symbol>:db-kind</symbol> - The kind of database mapping which
223 is performed for this slot. <symbol>:base</symbol> indicates
224 the slot maps to an ordinary column of the database view.
225 <symbol>:key</symbol> indicates that this slot corresponds to
226 part of the unique keys for this view, <symbol>:join</symbol>
227 indicates a join slot representing a relation to another view
228 and :virtual indicates that this slot is an ordinary CLOS slot.
229 Defaults to <symbol>:base</symbol>. </para></listitem>
233 <symbol>:db-reader</symbol> - If a string, then when reading
234 values from the database, the string will be used for a format
235 string, with the only value being the value from the database.
236 The resulting string will be used as the slot value. If a
237 function then it will take one argument, the value from the
238 database, and return the value that should be put into the slot.
243 <symbol>:db-writer</symbol> - If a string, then when reading
244 values from the slot for the database, the string will be used
245 for a format string, with the only value being the value of the
246 slot. The resulting string will be used as the column value in
247 the database. If a function then it will take one argument, the
248 value of the slot, and return the value that should be put into
249 the database.</para></listitem>
253 <symbol>:db-type</symbol> - A string which will be used as the
254 type specifier for this slots column definition in the database.
259 <symbol>:void-value</symbol> - The Lisp value to return if the
260 field is &null;. The default is &nil;.</para></listitem>
264 <symbol>:db-info</symbol> - A join specification.
269 In our example each table as a primary key attribute, which is
270 required to be unique. We indicate that a slot is part of the
271 primary key (&clsql; supports multi-field primary keys) by specifying
272 the <symbol>:db-kind</symbol> key slot option.
276 The &sql; type of a slot when it is mapped into the database is
277 determined by the <symbol>:type</symbol> slot option. The argument
278 for the <symbol>:type</symbol> option is a Common Lisp datatype.
279 The &clsql; framework will determine the appropriate mapping
280 depending on the database system the table is being created in. If
281 we really wanted to determine what &sql; type was used for a slot,
282 we could specify a <symbol>:db-type</symbol> option like
283 "NUMBER(38)" and we would be guaranteed that the slot would be
284 stored in the database as a NUMBER(38). This is not recomended
285 because it could makes your view class unportable across database
290 <function>DEF-VIEW-CLASS</function> also supports some class
291 options, like <symbol>:base-table</symbol>. The
292 <symbol>:base-table</symbol> option specifies what the table name
293 for the view class will be when it is mapped into the database.
297 Another class option is <symbol>:normalisedp</symbol>, which signals
298 &clsql; to use a normalised schema for the mapping from slots to
299 &sql; columns. By default &clsql; includes all the slots of a parent
300 class that map to &sql; columns into the child class. This option
301 tells &clsql; to normalise the schema, so that a join is done on the
302 primary keys of the concerned tables to get a complete column set
303 for the classes. For more information, see <link linkend="def-view-class">
304 <function>def-view-class</function></link>.
308 <sect1 id="csql-rel">
309 <title>Class Relations</title>
312 In an &sql; only application, the <symbol>EMPLOYEE</symbol> and
313 <symbol>COMPANY</symbol> tables can be queried to determine things
314 like, "Who is Vladimir's manager?", "What company does Josef work
315 for?", and "What employees work for Widgets Inc.". This is done by
316 joining tables with an &sql; query.
320 Who works for Widgets Inc.?
324 SELECT first_name, last_name FROM employee, company
325 WHERE employee.companyid = company.companyid
326 AND company.company_name = "Widgets Inc."
330 Who is Vladimir's manager?
334 SELECT managerid FROM employee
335 WHERE employee.first_name = "Vladimir"
336 AND employee.last_name = "Lenin"
340 What company does Josef work for?
344 SELECT company_name FROM company, employee
345 WHERE employee.first_name = "Josef"
346 AND employee.last-name = "Stalin"
347 AND employee.companyid = company.companyid
351 With &clsql; however we do not need to write out such queries because
352 our view classes can maintain the relations between employees and
353 companies, and employees to their managers for us. We can then access
354 these relations like we would any other attribute of an employee or
355 company object. In order to do this we define some join slots for our
360 What company does an employee work for? If we add the following slot
361 definition to the employee class we can then ask for it's
362 <symbol>COMPANY</symbol> slot and get the appropriate result.
366 ;; In the employee slot list
368 :accessor employee-company
370 :db-info (:join-class company
372 :foreign-key companyid
377 Who are the employees of a given company? And who is the president of
378 it? We add the following slot definition to the company view class and
379 we can then ask for it's <symbol>EMPLOYEES</symbol> slot and get the
384 ;; In the company slot list
386 :reader company-employees
388 :db-info (:join-class employee
390 :foreign-key companyid
396 :db-info (:join-class employee
397 :home-key presidentid
403 And lastly, to define the relation between an employee and their
408 ;; In the employee slot list
410 :accessor employee-manager
412 :db-info (:join-class employee
419 &clsql; join slots can represent one-to-one, one-to-many, and
420 many-to-many relations. Above we only have one-to-one and one-to-many
421 relations, later we will explain how to model many-to-many relations.
422 First, let's go over the slot definitions and the available options.
426 In order for a slot to be a join, we must specify that it's
427 <symbol>:db-kind</symbol> <symbol>:join</symbol>, as opposed to
428 <symbol>:base</symbol> or <symbol>:key</symbol>. Once we do that, we
429 still need to tell &clsql; how to create the join statements for the
430 relation. This is what the <symbol>:db-info</symbol> option does. It
431 is a list of keywords and values. The available keywords are:
437 <symbol>:join-class</symbol> - The view class to which we want
438 to join. It can be another view class, or the same view class
439 as our object.</para></listitem>
443 <symbol>:home-key</symbol> - The slot(s) in the immediate object
444 whose value will be compared to the foreign-key slot(s) in the
445 join-class in order to join the two tables. It can be a single
446 slot-name, or it can be a list of slot names.</para></listitem>
450 <symbol>:foreign-key</symbol> - The slot(s) in the join-class
451 which will be compared to the value(s) of the home-key.
456 <symbol>:set</symbol> - A boolean which if false, indicates that
457 this is a one-to-one relation, only one object will be returned.
458 If true, than this is a one-to-many relation, a list of objects
459 will be returned when we ask for this slots value.
464 There are other :join-info options available in &clsql;, but we will
465 save those till we get to the many-to-many relation examples.
469 <title>Object Oriented Class Relations</title>
472 &clsql; provides an Object Oriented Data Definition Language, which
473 provides a mapping from &sql; tables to CLOS objects. By default class
474 inheritance is handled by including all the columns from parent
475 classes into the child class. This means your database schema becomes
476 very much denormalised. The class option <symbol>:normalisedp</symbol>
477 can be used to disable the default behaviour and have &clsql;
478 normalise the database schemas of inherited classes.
482 See <link linkend="def-view-class"><function>def-view-class</function></link>
483 for more information.
488 <sect1 id="csql-creat">
489 <title>Object Creation</title>
492 Now that we have our model laid out, we should create some object.
493 Let us assume that we have a database connect set up already. We
494 first need to create our tables in the database:
498 Note: the file <filename>examples/clsql-tutorial.lisp</filename> contains
499 view class definitions which you can load into your list at this point
500 in order to play along at home.
504 (clsql:create-view-from-class 'employee)
505 (clsql:create-view-from-class 'company)
509 Then we will create our objects. We create them just like you would
510 any other CLOS object:
514 (defvar company1 (make-instance 'company
517 :name "Widgets Inc."))
519 (defvar employee1 (make-instance 'employee
521 :first-name "Vladimir"
523 :email "lenin@soviet.org"
526 (defvar employee2 (make-instance 'employee
530 :email "stalin@soviet.org"
536 In order to insert an objects into the database we use the
537 <function>UPDATE-RECORDS-FROM-INSTANCE</function> function as follows:
541 (clsql:update-records-from-instance employee1)
542 (clsql:update-records-from-instance employee2)
543 (clsql:update-records-from-instance company1)
547 After you make any changes to an object, you have to specifically
548 tell &clsql; to update the &sql; database. The
549 <function>UPDATE-RECORDS-FROM-INSTANCE</function> method will write
550 all of the changes you have made to the object into the database.
554 Since &clsql; objects are just normal CLOS objects, we can manipulate
555 their slots just like any other object. For instance, let's say
556 that Lenin changes his email because he was getting too much spam
557 from the German Socialists.
561 ;; Print Lenin's current email address, change it and save it to the
562 ;; database. Get a new object representing Lenin from the database
563 ;; and print the email
565 ;; This lets us use the functional &clsql; interface with [] syntax
566 (clsql:locally-enable-sql-reader-syntax)
568 (format t "The email address of ~A ~A is ~A"
569 (first-name employee1)
570 (last-name employee1)
571 (employee-email employee1))
573 (setf (employee-email employee1) "lenin-nospam@soviets.org")
575 ;; Update the database
576 (clsql:update-records-from-instance employee1)
578 (let ((new-lenin (car (clsql:select 'employee
579 :where [= [slot-value 'employee 'emplid] 1]))))
580 (format t "His new email is ~A"
581 (employee-email new-lenin)))
585 Everything except for the last <function>LET</function> expression
586 is already familiar to us by now. To understand the call to
587 <function>CLSQL:SELECT</function> we need to discuss the
588 Functional &sql; interface and it's integration with the Object
589 Oriented interface of &clsql;.
594 <sect1 id="csql-find">
595 <title>Finding Objects</title>
598 Now that we have our objects in the database, how do we get them out
599 when we need to work with them? &clsql; provides a functional
600 interface to &sql;, which consists of a special Lisp reader macro
601 and some functions. The special syntax allows us to embed &sql; in
602 lisp expressions, and lisp expressions in &sql;, with ease.
606 Once we have turned on the syntax with the expression:
610 (clsql:locally-enable-sql-reader-syntax)
614 We can start entering fragments of &sql; into our lisp reader. We
615 will get back objects which represent the lisp expressions. These
616 objects will later be compiled into &sql; expressions that are
617 optimized for the database backed we are connected to. This means
618 that we have a database independent &sql; syntax. Here are some
623 ;; an attribute or table name
624 [foo] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
626 ;; a attribute identifier with table qualifier
627 [foo bar] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
629 ;; a attribute identifier with table qualifier
630 [= "Lenin" [first_name]] =>
631 #<CLSQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
634 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
636 [and [< [emplid] 2] [= [first_name] "Lenin"]] =>
637 #<CLSQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND
638 (FIRST_NAME = 'Lenin'))>
641 ;; If we want to reference a slot in an object we can us the
642 ;; SLOT-VALUE sql extension
643 [= [slot-value 'employee 'emplid] 1] =>
644 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
646 [= [slot-value 'employee 'emplid]
647 [slot-value 'company 'presidentid]] =>
648 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
652 The <function>SLOT-VALUE</function> operator is important because it
653 let's us query objects in a way that is robust to any changes in the
654 object->table mapping, like column name changes, or table name
655 changes. So when you are querying objects, be sure to use the
656 <function>SLOT-VALUE</function> &sql; extension.
660 Since we can now formulate &sql; relational expression which can be
661 used as qualifiers, like we put after the <symbol>WHERE</symbol>
662 keyword in &sql; statements, we can start querying our objects.
663 &clsql; provides a function <symbol>SELECT</symbol> which can return
664 use complete objects from the database which conform to a qualifier,
665 can be sorted, and various other &sql; operations.
669 The first argument to <symbol>SELECT</symbol> is a class name. it
670 also has a set of keyword arguments which are covered in the
671 documentation. For now we will concern ourselves only with the
672 :where keyword. Select returns a list of objects, or nil if it
673 can't find any. It's important to remember that it always returns a
674 list, so even if you are expecting only one result, you should
675 remember to extract it from the list you get from
676 <symbol>SELECT</symbol>.
681 (clsql:select 'employee)
683 (clsql:select 'company)
685 ;; employees named Lenin
686 (clsql:select 'employee :where [= [slot-value 'employee 'last-name]
689 (clsql:select 'company :where [= [slot-value 'company 'name]
692 ;; Employees of Widget's Inc.
693 (clsql:select 'employee
694 :where [and [= [slot-value 'employee 'companyid]
695 [slot-value 'company 'companyid]]
696 [= [slot-value 'company 'name]
699 ;; Same thing, except that we are using the employee
700 ;; relation in the company view class to do the join for us,
701 ;; saving us the work of writing out the &sql;!
702 (company-employees company1)
704 ;; President of Widgets Inc.
707 ;; Manager of Josef Stalin
708 (employee-manager employee2)
713 <sect1 id="csql-del">
714 <title>Deleting Objects</title>
717 Now that we know how to create objects in our database, manipulate
718 them and query them (including using our predefined relations to
719 save us the trouble writing alot of &sql;) we should learn how to
720 clean up after ourself. It's quite simple really. The function
721 <function>DELETE-INSTANCE-RECORDS</function> will remove an object
722 from the database. However, when we remove an object we are
723 responsible for making sure that the database is left in a correct
728 For example, if we remove a company record, we need to either remove
729 all of it's employees or we need to move them to another company.
730 Likewise if we remove an employee, we should make sure to update any
731 other employees who had them as a manager.
736 <sect1 id="csql-concl">
737 <title>Conclusion</title>
740 There are many nooks and crannies to &clsql;, some of which are
741 covered in the Xanalys documents we refered to earlier, some are
742 not. The best documentation at this time is still the source code
743 for &clsql; itself and the inline documentation for its various