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>:db-type</symbol> - A string which will be used as the
251 type specifier for this slots column definition in the database.
256 <symbol>:nulls-ok</symbol> - If &t;, all &sql; &null; values
257 retrieved from the database become nil; if &nil;, all &null;
258 values retrieved are converted by
259 <function>DATABASE-NULL-VALUE</function>. </para></listitem>
263 <symbol>:db-info</symbol> - A join specification.
268 In our example each table as a primary key attribute, which is
269 required to be unique. We indicate that a slot is part of the
270 primary key (&clsql; supports multi-field primary keys) by specifying
271 the <symbol>:db-kind</symbol> key slot option.
275 The &sql; type of a slot when it is mapped into the database is
276 determined by the <symbol>:type</symbol> slot option. The argument
277 for the <symbol>:type</symbol> option is a Common Lisp datatype.
278 The &clsql; framework will determine the appropriate mapping
279 depending on the database system the table is being created in. If
280 we really wanted to determine what &sql; type was used for a slot,
281 we could specify a <symbol>:db-type</symbol> option like
282 "NUMBER(38)" and we would be guaranteed that the slot would be
283 stored in the database as a NUMBER(38). This is not recomended
284 because it could makes your view class unportable across database
289 <function>DEF-VIEW-CLASS</function> also supports some class
290 options, like <symbol>:base-table</symbol>. The
291 <symbol>:base-table</symbol> option specifies what the table name
292 for the view class will be when it is mapped into the database.
296 <sect1 id="csql-rel">
297 <title>Class Relations</title>
300 In an &sql; only application, the <symbol>EMPLOYEE</symbol> and
301 <symbol>COMPANY</symbol> tables can be queried to determine things
302 like, "Who is Vladamir's manager?", "What company does Josef work
303 for?", and "What employees work for Widgets Inc.". This is done by
304 joining tables with an &sql; query.
308 Who works for Widgets Inc.?
312 SELECT first_name, last_name FROM employee, company
313 WHERE employee.companyid = company.companyid
314 AND company.company_name = "Widgets Inc."
318 Who is Vladamir's manager?
322 SELECT managerid FROM employee
323 WHERE employee.first_name = "Vladamir"
324 AND employee.last_name = "Lenin"
328 What company does Josef work for?
332 SELECT company_name FROM company, employee
333 WHERE employee.first_name = "Josef"
334 AND employee.last-name = "Stalin"
335 AND employee.companyid = company.companyid
339 With &clsql; however we do not need to write out such queries because
340 our view classes can maintain the relations between employees and
341 companies, and employees to their managers for us. We can then access
342 these relations like we would any other attribute of an employee or
343 company object. In order to do this we define some join slots for our
348 What company does an employee work for? If we add the following slot
349 definition to the employee class we can then ask for it's
350 <symbol>COMPANY</symbol> slot and get the appropriate result.
354 ;; In the employee slot list
356 :accessor employee-company
358 :db-info (:join-class company
360 :foreign-key companyid
365 Who are the employees of a given company? And who is the president of
366 it? We add the following slot definition to the company view class and
367 we can then ask for it's <symbol>EMPLOYEES</symbol> slot and get the
372 ;; In the company slot list
374 :reader company-employees
376 :db-info (:join-class employee
378 :foreign-key companyid
384 :db-info (:join-class employee
385 :home-key presidentid
391 And lastly, to define the relation between an employee and their
396 ;; In the employee slot list
398 :accessor employee-manager
400 :db-info (:join-class employee
407 &clsql; join slots can represent one-to-one, one-to-many, and
408 many-to-many relations. Above we only have one-to-one and one-to-many
409 relations, later we will explain how to model many-to-many relations.
410 First, let's go over the slot definitions and the available options.
414 In order for a slot to be a join, we must specify that it's
415 <symbol>:db-kind</symbol> <symbol>:join</symbol>, as opposed to
416 <symbol>:base</symbol> or <symbol>:key</symbol>. Once we do that, we
417 still need to tell &clsql; how to create the join statements for the
418 relation. This is what the <symbol>:db-info</symbol> option does. It
419 is a list of keywords and values. The available keywords are:
425 <symbol>:join-class</symbol> - The view class to which we want
426 to join. It can be another view class, or the same view class
427 as our object.</para></listitem>
431 <symbol>:home-key</symbol> - The slot(s) in the immediate object
432 whose value will be compared to the foreign-key slot(s) in the
433 join-class in order to join the two tables. It can be a single
434 slot-name, or it can be a list of slot names.</para></listitem>
438 <symbol>:foreign-key</symbol> - The slot(s) in the join-class
439 which will be compared to the value(s) of the home-key.
444 <symbol>:set</symbol> - A boolean which if false, indicates that
445 this is a one-to-one relation, only one object will be returned.
446 If true, than this is a one-to-many relation, a list of objects
447 will be returned when we ask for this slots value.
452 There are other :join-info options available in &clsql;, but we will
453 save those till we get to the many-to-many relation examples.
458 <sect1 id="csql-creat">
459 <title>Object Creation</title>
462 Now that we have our model laid out, we should create some object.
463 Let us assume that we have a database connect set up already. We
464 first need to create our tables in the database:
468 Note: the file <filename>doc/clsql-tutorial.lisp</filename> contains
469 view class definitions which you can load into your list at this point
470 in order to play along at home.
474 (clsql:create-view-from-class 'employee)
475 (clsql:create-view-from-class 'company)
479 Then we will create our objects. We create them just like you would
480 any other CLOS object:
484 (defvar employee1 (make-instance 'employee
486 :first-name "Vladamir"
488 :email "lenin@soviet.org"))
490 (defvar company1 (make-instance 'company
492 :name "Widgets Inc."))
495 (defvar employee2 (make-instance 'employee
499 :email "stalin@soviet.org"))
503 In order to insert an objects into the database we use the
504 <function>UPDATE-RECORDS-FROM-INSTANCE</function> function as follows:
508 (clsql:update-records-from-instance employee1)
509 (clsql:update-records-from-instance employee2)
510 (clsql:update-records-from-instance company1)
514 Now we can set up some of the relations between employees and
515 companies, and their managers. The
516 <function>ADD-TO-RELATION</function> method provides us with an easy
517 way of doing that. It will update both the relation slot, as well as
518 the home-key and foreign-key slots in both objects in the relation.
522 ;; Lenin manages Stalin (for now)
523 (clsql:add-to-relation employee2 'manager employee1)
525 ;; Lenin and Stalin both work for Widgets Inc.
526 (clsql:add-to-relation company1 'employees employee1)
527 (clsql:add-to-relation company1 'employees employee2)
529 ;; Lenin is president of Widgets Inc.
530 (clsql:add-to-relation company1 'president employee1)
534 After you make any changes to an object, you have to specifically
535 tell &clsql; to update the &sql; database. The
536 <function>UPDATE-RECORDS-FROM-INSTANCE</function> method will write
537 all of the changes you have made to the object into the database.
541 Since &clsql; objects are just normal CLOS objects, we can manipulate
542 their slots just like any other object. For instance, let's say
543 that Lenin changes his email because he was getting too much spam
544 from the German Socialists.
548 ;; Print Lenin's current email address, change it and save it to the
549 ;; database. Get a new object representing Lenin from the database
550 ;; and print the email
552 ;; This lets us use the functional &clsql; interface with [] syntax
553 (clsql:locally-enable-sql-reader-syntax)
555 (format t "The email address of ~A ~A is ~A"
556 (first-name employee1)
557 (last-name employee1)
558 (employee-email employee1))
560 (setf (employee-email employee1) "lenin-nospam@soviets.org")
562 ;; Update the database
563 (clsql:update-records-from-instance employee1)
565 (let ((new-lenin (car (clsql:select 'employee
566 :where [= [slot-value 'employee 'emplid] 1]))))
567 (format t "His new email is ~A"
568 (employee-email new-lenin)))
572 Everything except for the last <function>LET</function> expression
573 is already familiar to us by now. To understand the call to
574 <function>CLSQL:SELECT</function> we need to discuss the
575 Functional &sql; interface and it's integration with the Object
576 Oriented interface of &clsql;.
581 <sect1 id="csql-find">
582 <title>Finding Objects</title>
585 Now that we have our objects in the database, how do we get them out
586 when we need to work with them? &clsql; provides a functional
587 interface to &sql;, which consists of a special Lisp reader macro
588 and some functions. The special syntax allows us to embed &sql; in
589 lisp expressions, and lisp expressions in &sql;, with ease.
593 Once we have turned on the syntax with the expression:
597 (clsql:locally-enable-sql-reader-syntax)
601 We can start entering fragments of &sql; into our lisp reader. We
602 will get back objects which represent the lisp expressions. These
603 objects will later be compiled into &sql; expressions that are
604 optimized for the database backed we are connected to. This means
605 that we have a database independent &sql; syntax. Here are some
610 ;; an attribute or table name
611 [foo] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
613 ;; a attribute identifier with table qualifier
614 [foo bar] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
616 ;; a attribute identifier with table qualifier
617 [= "Lenin" [first_name]] =>
618 #<CLSQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
621 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
623 [and [< [emplid] 2] [= [first_name] "Lenin"]] =>
624 #<CLSQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND
625 (FIRST_NAME = 'Lenin'))>
628 ;; If we want to reference a slot in an object we can us the
629 ;; SLOT-VALUE sql extension
630 [= [slot-value 'employee 'emplid] 1] =>
631 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
633 [= [slot-value 'employee 'emplid]
634 [slot-value 'company 'presidentid]] =>
635 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
639 The <function>SLOT-VALUE</function> operator is important because it
640 let's us query objects in a way that is robust to any changes in the
641 object->table mapping, like column name changes, or table name
642 changes. So when you are querying objects, be sure to use the
643 <function>SLOT-VALUE</function> &sql; extension.
647 Since we can now formulate &sql; relational expression which can be
648 used as qualifiers, like we put after the <symbol>WHERE</symbol>
649 keyword in &sql; statements, we can start querying our objects.
650 &clsql; provides a function <symbol>SELECT</symbol> which can return
651 use complete objects from the database which conform to a qualifier,
652 can be sorted, and various other &sql; operations.
656 The first argument to <symbol>SELECT</symbol> is a class name. it
657 also has a set of keyword arguments which are covered in the
658 documentation. For now we will concern ourselves only with the
659 :where keyword. Select returns a list of objects, or nil if it
660 can't find any. It's important to remember that it always returns a
661 list, so even if you are expecting only one result, you should
662 remember to extract it from the list you get from
663 <symbol>SELECT</symbol>.
668 (clsql:select 'employee)
670 (clsql:select 'company)
672 ;; employees named Lenin
673 (clsql:select 'employee :where [= [slot-value 'employee 'last-name]
676 (clsql:select 'company :where [= [slot-value 'company 'name]
679 ;; Employees of Widget's Inc.
680 (clsql:select 'employee
681 :where [and [= [slot-value 'employee 'companyid]
682 [slot-value 'company 'companyid]]
683 [= [slot-value 'company 'name]
686 ;; Same thing, except that we are using the employee
687 ;; relation in the company view class to do the join for us,
688 ;; saving us the work of writing out the &sql;!
689 (company-employees company1)
691 ;; President of Widgets Inc.
694 ;; Manager of Josef Stalin
695 (employee-manager employee2)
700 <sect1 id="csql-del">
701 <title>Deleting Objects</title>
704 Now that we know how to create objects in our database, manipulate
705 them and query them (including using our predefined relations to
706 save us the trouble writing alot of &sql;) we should learn how to
707 clean up after ourself. It's quite simple really. The function
708 <function>DELETE-INSTANCE-RECORDS</function> will remove an object
709 from the database. However, when we remove an object we are
710 responsible for making sure that the database is left in a correct
715 For example, if we remove a company record, we need to either remove
716 all of it's employees or we need to move them to another company.
717 Likewise if we remove an employee, we should make sure to update any
718 other employees who had them as a manager.
723 <sect1 id="csql-concl">
724 <title>Conclusion</title>
727 There are many nooks and crannies to &clsql;, some of which are
728 covered in the Xanalys documents we refered to earlier, some are
729 not. The best documentation at this time is still the source code
730 for &clsql; itself and the inline documentation for its various