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 Xanalys, so the
40 documentation that Xanalys 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. Xanalys makes the following documents
50 <ulink url="http://www.lispworks.com/reference/lw43/LWUG/html/lwuser-167.htm">
51 <citetitle>Xanalys &lw; User Guide - The &commonsql;
60 <ulink url="http://www.lispworks.com/reference/lw43/LWRM/html/lwref-383.htm">
61 <citetitle>Xanalys &lw; Reference Manual - The SQL
69 <ulink url="http://www.ravenbrook.com/doc/2002/09/13/common-sql/">
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://www.arsdigita.com/books/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
184 (:base-table employee))
186 (clsql:def-view-class company ()
189 :db-constraints :not-null
197 (:base-table company))
201 The <function>DEF-VIEW-CLASS</function> macro is just like the
202 normal CLOS <function>DEFCLASS</function> macro, except that it
203 handles several slot options that <function>DEFCLASS</function>
204 doesn't. These slot options have to do with the mapping of the slot
205 into the database. We only use a few of the slot options in the
206 above example, but there are several others.
212 <symbol>:column</symbol> - The name of the &sql; column this slot is stored in.
213 Defaults to the slot name. If the slot name is not a valid &sql;
214 identifier, it is escaped, so foo-bar becomes foo_bar.
219 <symbol>:db-kind</symbol> - The kind of database mapping which
220 is performed for this slot. <symbol>:base</symbol> indicates
221 the slot maps to an ordinary column of the database view.
222 <symbol>:key</symbol> indicates that this slot corresponds to
223 part of the unique keys for this view, <symbol>:join</symbol>
224 indicates a join slot representing a relation to another view
225 and :virtual indicates that this slot is an ordinary CLOS slot.
226 Defaults to <symbol>:base</symbol>. </para></listitem>
230 <symbol>:db-reader</symbol> - If a string, then when reading
231 values from the database, the string will be used for a format
232 string, with the only value being the value from the database.
233 The resulting string will be used as the slot value. If a
234 function then it will take one argument, the value from the
235 database, and return the value that should be put into the slot.
240 <symbol>:db-writer</symbol> - If a string, then when reading
241 values from the slot for the database, the string will be used
242 for a format string, with the only value being the value of the
243 slot. The resulting string will be used as the column value in
244 the database. If a function then it will take one argument, the
245 value of the slot, and return the value that should be put into
246 the database.</para></listitem>
250 <symbol>:column-</symbol> - A string which will be used as the
251 type specifier for this slots column definition in the database.
256 <symbol>:void-value</symbol> - The Lisp value to return if the
257 field is &null;. The default is &nil;.</para></listitem>
261 <symbol>:db-info</symbol> - A join specification.
266 In our example each table as a primary key attribute, which is
267 required to be unique. We indicate that a slot is part of the
268 primary key (&clsql; supports multi-field primary keys) by specifying
269 the <symbol>:db-kind</symbol> key slot option.
273 The &sql; type of a slot when it is mapped into the database is
274 determined by the <symbol>:type</symbol> slot option. The argument
275 for the <symbol>:type</symbol> option is a Common Lisp datatype.
276 The &clsql; framework will determine the appropriate mapping
277 depending on the database system the table is being created in. If
278 we really wanted to determine what &sql; type was used for a slot,
279 we could specify a <symbol>:db-type</symbol> option like
280 "NUMBER(38)" and we would be guaranteed that the slot would be
281 stored in the database as a NUMBER(38). This is not recomended
282 because it could makes your view class unportable across database
287 <function>DEF-VIEW-CLASS</function> also supports some class
288 options, like <symbol>:base-table</symbol>. The
289 <symbol>:base-table</symbol> option specifies what the table name
290 for the view class will be when it is mapped into the database.
294 <sect1 id="csql-rel">
295 <title>Class Relations</title>
298 In an &sql; only application, the <symbol>EMPLOYEE</symbol> and
299 <symbol>COMPANY</symbol> tables can be queried to determine things
300 like, "Who is Vladamir's manager?", "What company does Josef work
301 for?", and "What employees work for Widgets Inc.". This is done by
302 joining tables with an &sql; query.
306 Who works for Widgets Inc.?
310 SELECT first_name, last_name FROM employee, company
311 WHERE employee.companyid = company.companyid
312 AND company.company_name = "Widgets Inc."
316 Who is Vladamir's manager?
320 SELECT managerid FROM employee
321 WHERE employee.first_name = "Vladamir"
322 AND employee.last_name = "Lenin"
326 What company does Josef work for?
330 SELECT company_name FROM company, employee
331 WHERE employee.first_name = "Josef"
332 AND employee.last-name = "Stalin"
333 AND employee.companyid = company.companyid
337 With &clsql; however we do not need to write out such queries because
338 our view classes can maintain the relations between employees and
339 companies, and employees to their managers for us. We can then access
340 these relations like we would any other attribute of an employee or
341 company object. In order to do this we define some join slots for our
346 What company does an employee work for? If we add the following slot
347 definition to the employee class we can then ask for it's
348 <symbol>COMPANY</symbol> slot and get the appropriate result.
352 ;; In the employee slot list
354 :accessor employee-company
356 :db-info (:join-class company
358 :foreign-key companyid
363 Who are the employees of a given company? And who is the president of
364 it? We add the following slot definition to the company view class and
365 we can then ask for it's <symbol>EMPLOYEES</symbol> slot and get the
370 ;; In the company slot list
372 :reader company-employees
374 :db-info (:join-class employee
376 :foreign-key companyid
382 :db-info (:join-class employee
383 :home-key presidentid
389 And lastly, to define the relation between an employee and their
394 ;; In the employee slot list
396 :accessor employee-manager
398 :db-info (:join-class employee
405 &clsql; join slots can represent one-to-one, one-to-many, and
406 many-to-many relations. Above we only have one-to-one and one-to-many
407 relations, later we will explain how to model many-to-many relations.
408 First, let's go over the slot definitions and the available options.
412 In order for a slot to be a join, we must specify that it's
413 <symbol>:db-kind</symbol> <symbol>:join</symbol>, as opposed to
414 <symbol>:base</symbol> or <symbol>:key</symbol>. Once we do that, we
415 still need to tell &clsql; how to create the join statements for the
416 relation. This is what the <symbol>:db-info</symbol> option does. It
417 is a list of keywords and values. The available keywords are:
423 <symbol>:join-class</symbol> - The view class to which we want
424 to join. It can be another view class, or the same view class
425 as our object.</para></listitem>
429 <symbol>:home-key</symbol> - The slot(s) in the immediate object
430 whose value will be compared to the foreign-key slot(s) in the
431 join-class in order to join the two tables. It can be a single
432 slot-name, or it can be a list of slot names.</para></listitem>
436 <symbol>:foreign-key</symbol> - The slot(s) in the join-class
437 which will be compared to the value(s) of the home-key.
442 <symbol>:set</symbol> - A boolean which if false, indicates that
443 this is a one-to-one relation, only one object will be returned.
444 If true, than this is a one-to-many relation, a list of objects
445 will be returned when we ask for this slots value.
450 There are other :join-info options available in &clsql;, but we will
451 save those till we get to the many-to-many relation examples.
456 <sect1 id="csql-creat">
457 <title>Object Creation</title>
460 Now that we have our model laid out, we should create some object.
461 Let us assume that we have a database connect set up already. We
462 first need to create our tables in the database:
466 Note: the file <filename>examples/clsql-tutorial.lisp</filename> contains
467 view class definitions which you can load into your list at this point
468 in order to play along at home.
472 (clsql:create-view-from-class 'employee)
473 (clsql:create-view-from-class 'company)
477 Then we will create our objects. We create them just like you would
478 any other CLOS object:
482 (defvar employee1 (make-instance 'employee
484 :first-name "Vladamir"
486 :email "lenin@soviet.org"))
488 (defvar company1 (make-instance 'company
490 :name "Widgets Inc."))
493 (defvar employee2 (make-instance 'employee
497 :email "stalin@soviet.org"))
501 In order to insert an objects into the database we use the
502 <function>UPDATE-RECORDS-FROM-INSTANCE</function> function as follows:
506 (clsql:update-records-from-instance employee1)
507 (clsql:update-records-from-instance employee2)
508 (clsql:update-records-from-instance company1)
512 Now we can set up some of the relations between employees and
513 companies, and their managers. The
514 <function>ADD-TO-RELATION</function> method provides us with an easy
515 way of doing that. It will update both the relation slot, as well as
516 the home-key and foreign-key slots in both objects in the relation.
520 ;; Lenin manages Stalin (for now)
521 (clsql:add-to-relation employee2 'manager employee1)
523 ;; Lenin and Stalin both work for Widgets Inc.
524 (clsql:add-to-relation company1 'employees employee1)
525 (clsql:add-to-relation company1 'employees employee2)
527 ;; Lenin is president of Widgets Inc.
528 (clsql:add-to-relation company1 'president employee1)
532 After you make any changes to an object, you have to specifically
533 tell &clsql; to update the &sql; database. The
534 <function>UPDATE-RECORDS-FROM-INSTANCE</function> method will write
535 all of the changes you have made to the object into the database.
539 Since &clsql; objects are just normal CLOS objects, we can manipulate
540 their slots just like any other object. For instance, let's say
541 that Lenin changes his email because he was getting too much spam
542 from the German Socialists.
546 ;; Print Lenin's current email address, change it and save it to the
547 ;; database. Get a new object representing Lenin from the database
548 ;; and print the email
550 ;; This lets us use the functional &clsql; interface with [] syntax
551 (clsql:locally-enable-sql-reader-syntax)
553 (format t "The email address of ~A ~A is ~A"
554 (first-name employee1)
555 (last-name employee1)
556 (employee-email employee1))
558 (setf (employee-email employee1) "lenin-nospam@soviets.org")
560 ;; Update the database
561 (clsql:update-records-from-instance employee1)
563 (let ((new-lenin (car (clsql:select 'employee
564 :where [= [slot-value 'employee 'emplid] 1]))))
565 (format t "His new email is ~A"
566 (employee-email new-lenin)))
570 Everything except for the last <function>LET</function> expression
571 is already familiar to us by now. To understand the call to
572 <function>CLSQL:SELECT</function> we need to discuss the
573 Functional &sql; interface and it's integration with the Object
574 Oriented interface of &clsql;.
579 <sect1 id="csql-find">
580 <title>Finding Objects</title>
583 Now that we have our objects in the database, how do we get them out
584 when we need to work with them? &clsql; provides a functional
585 interface to &sql;, which consists of a special Lisp reader macro
586 and some functions. The special syntax allows us to embed &sql; in
587 lisp expressions, and lisp expressions in &sql;, with ease.
591 Once we have turned on the syntax with the expression:
595 (clsql:locally-enable-sql-reader-syntax)
599 We can start entering fragments of &sql; into our lisp reader. We
600 will get back objects which represent the lisp expressions. These
601 objects will later be compiled into &sql; expressions that are
602 optimized for the database backed we are connected to. This means
603 that we have a database independent &sql; syntax. Here are some
608 ;; an attribute or table name
609 [foo] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
611 ;; a attribute identifier with table qualifier
612 [foo bar] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
614 ;; a attribute identifier with table qualifier
615 [= "Lenin" [first_name]] =>
616 #<CLSQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
619 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
621 [and [< [emplid] 2] [= [first_name] "Lenin"]] =>
622 #<CLSQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND
623 (FIRST_NAME = 'Lenin'))>
626 ;; If we want to reference a slot in an object we can us the
627 ;; SLOT-VALUE sql extension
628 [= [slot-value 'employee 'emplid] 1] =>
629 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
631 [= [slot-value 'employee 'emplid]
632 [slot-value 'company 'presidentid]] =>
633 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
637 The <function>SLOT-VALUE</function> operator is important because it
638 let's us query objects in a way that is robust to any changes in the
639 object->table mapping, like column name changes, or table name
640 changes. So when you are querying objects, be sure to use the
641 <function>SLOT-VALUE</function> &sql; extension.
645 Since we can now formulate &sql; relational expression which can be
646 used as qualifiers, like we put after the <symbol>WHERE</symbol>
647 keyword in &sql; statements, we can start querying our objects.
648 &clsql; provides a function <symbol>SELECT</symbol> which can return
649 use complete objects from the database which conform to a qualifier,
650 can be sorted, and various other &sql; operations.
654 The first argument to <symbol>SELECT</symbol> is a class name. it
655 also has a set of keyword arguments which are covered in the
656 documentation. For now we will concern ourselves only with the
657 :where keyword. Select returns a list of objects, or nil if it
658 can't find any. It's important to remember that it always returns a
659 list, so even if you are expecting only one result, you should
660 remember to extract it from the list you get from
661 <symbol>SELECT</symbol>.
666 (clsql:select 'employee)
668 (clsql:select 'company)
670 ;; employees named Lenin
671 (clsql:select 'employee :where [= [slot-value 'employee 'last-name]
674 (clsql:select 'company :where [= [slot-value 'company 'name]
677 ;; Employees of Widget's Inc.
678 (clsql:select 'employee
679 :where [and [= [slot-value 'employee 'companyid]
680 [slot-value 'company 'companyid]]
681 [= [slot-value 'company 'name]
684 ;; Same thing, except that we are using the employee
685 ;; relation in the company view class to do the join for us,
686 ;; saving us the work of writing out the &sql;!
687 (company-employees company1)
689 ;; President of Widgets Inc.
692 ;; Manager of Josef Stalin
693 (employee-manager employee2)
698 <sect1 id="csql-del">
699 <title>Deleting Objects</title>
702 Now that we know how to create objects in our database, manipulate
703 them and query them (including using our predefined relations to
704 save us the trouble writing alot of &sql;) we should learn how to
705 clean up after ourself. It's quite simple really. The function
706 <function>DELETE-INSTANCE-RECORDS</function> will remove an object
707 from the database. However, when we remove an object we are
708 responsible for making sure that the database is left in a correct
713 For example, if we remove a company record, we need to either remove
714 all of it's employees or we need to move them to another company.
715 Likewise if we remove an employee, we should make sure to update any
716 other employees who had them as a manager.
721 <sect1 id="csql-concl">
722 <title>Conclusion</title>
725 There are many nooks and crannies to &clsql;, some of which are
726 covered in the Xanalys documents we refered to earlier, some are
727 not. The best documentation at this time is still the source code
728 for &clsql; itself and the inline documentation for its various