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 (
139 emplid NOT NULL number(38),
140 first_name NOT NULL varchar2(30),
141 last_name NOT NULL varchar2(30),
143 companyid NOT NULL number(38),
147 CREATE TABLE COMPANY (
148 companyid NOT NULL number(38),
149 name NOT NULL varchar2(100),
150 presidentid NOT NULL number(38)
155 This is of course the canonical &sql; tutorial example, "The Org Chart".
159 In &clsql;, we would have two "view classes" (a fancy word for a class
160 mapped into a database). They would be defined as follows:
164 (clsql:def-view-class employee ()
167 :db-constraints :not-null
173 :initarg :first-name)
179 :accessor employee-email
188 (:base-table employee))
190 (clsql:def-view-class company ()
193 :db-constraints :not-null
201 (:base-table company))
205 The <function>DEF-VIEW-CLASS</function> macro is just like the
206 normal CLOS <function>DEFCLASS</function> macro, except that it
207 handles several slot options that <function>DEFCLASS</function>
208 doesn't. These slot options have to do with the mapping of the slot
209 into the database. We only use a few of the slot options in the
210 above example, but there are several others.
216 <symbol>:column</symbol> - The name of the &sql; column this slot is stored in.
217 Defaults to the slot name. If the slot name is not a valid &sql;
218 identifier, it is escaped, so foo-bar becomes foo_bar.
223 <symbol>:db-kind</symbol> - The kind of database mapping which
224 is performed for this slot. <symbol>:base</symbol> indicates
225 the slot maps to an ordinary column of the database view.
226 <symbol>:key</symbol> indicates that this slot corresponds to
227 part of the unique keys for this view, <symbol>:join</symbol>
228 indicates a join slot representing a relation to another view
229 and :virtual indicates that this slot is an ordinary CLOS slot.
230 Defaults to <symbol>:base</symbol>. </para></listitem>
234 <symbol>:db-reader</symbol> - If a string, then when reading
235 values from the database, the string will be used for a format
236 string, with the only value being the value from the database.
237 The resulting string will be used as the slot value. If a
238 function then it will take one argument, the value from the
239 database, and return the value that should be put into the slot.
244 <symbol>:db-writer</symbol> - If a string, then when reading
245 values from the slot for the database, the string will be used
246 for a format string, with the only value being the value of the
247 slot. The resulting string will be used as the column value in
248 the database. If a function then it will take one argument, the
249 value of the slot, and return the value that should be put into
250 the database.</para></listitem>
254 <symbol>:db-type</symbol> - A string which will be used as the
255 type specifier for this slots column definition in the database.
260 <symbol>:nulls-ok</symbol> - If &t;, all &sql; &null; values
261 retrieved from the database become nil; if &nil;, all &null;
262 values retrieved are converted by
263 <function>DATABASE-NULL-VALUE</function>. </para></listitem>
267 <symbol>:db-info</symbol> - A join specification.
272 In our example each table as a primary key attribute, which is
273 required to be unique. We indicate that a slot is part of the
274 primary key (&clsql; supports multi-field primary keys) by specifying
275 the <symbol>:db-kind</symbol> key slot option.
279 The &sql; type of a slot when it is mapped into the database is
280 determined by the <symbol>:type</symbol> slot option. The argument
281 for the <symbol>:type</symbol> option is a Common Lisp datatype.
282 The &clsql; framework will determine the appropriate mapping
283 depending on the database system the table is being created in. If
284 we really wanted to determine what &sql; type was used for a slot,
285 we could specify a <symbol>:db-type</symbol> option like
286 "NUMBER(38)" and we would be guaranteed that the slot would be
287 stored in the database as a NUMBER(38). This is not recomended
288 because it could makes your view class unportable across database
293 <function>DEF-VIEW-CLASS</function> also supports some class
294 options, like <symbol>:base-table</symbol>. The
295 <symbol>:base-table</symbol> option specifies what the table name
296 for the view class will be when it is mapped into the database.
300 <sect1 id="csql-rel">
301 <title>Class Relations</title>
304 In an &sql; only application, the <symbol>EMPLOYEE</symbol> and
305 <symbol>COMPANY</symbol> tables can be queried to determine things
306 like, "Who is Vladamir's manager?", What company does Josef work
307 for?", and "What employees work for Widgets Inc.". This is done by
308 joining tables with an &sql; query.
312 Who works for Widgets Inc.?
316 SELECT first_name, last_name FROM employee, company
317 WHERE employee.companyid = company.companyid
318 AND company.company_name = "Widgets Inc."
322 Who is Vladamir's manager?
326 SELECT managerid FROM employee
327 WHERE employee.first_name = "Vladamir"
328 AND employee.last_name = "Lenin"
332 What company does Josef work for?
336 SELECT company_name FROM company, employee
337 WHERE employee.first_name = "Josef"
338 AND employee.last-name = "Stalin"
339 AND employee.companyid = company.companyid
343 With &clsql; however we do not need to write out such queries because
344 our view classes can maintain the relations between employees and
345 companies, and employees to their managers for us. We can then access
346 these relations like we would any other attribute of an employee or
347 company object. In order to do this we define some join slots for our
352 What company does an employee work for? If we add the following slot
353 definition to the employee class we can then ask for it's
354 <symbol>COMPANY</symbol> slot and get the appropriate result.
358 ;; In the employee slot list
360 :accessor employee-company
362 :db-info (:join-class company
364 :foreign-key companyid
369 Who are the employees of a given company? And who is the president of
370 it? We add the following slot definition to the company view class and
371 we can then ask for it's <symbol>EMPLOYEES</symbol> slot and get the
376 ;; In the company slot list
378 :reader company-employees
380 :db-info (:join-class employee
382 :foreign-key companyid
388 :db-info (:join-class employee
389 :home-key presidentid
395 And lastly, to define the relation between an employee and their
400 ;; In the employee slot list
402 :accessor employee-manager
404 :db-info (:join-class employee
411 &clsql; join slots can represent one-to-one, one-to-many, and
412 many-to-many relations. Above we only have one-to-one and one-to-many
413 relations, later we will explain how to model many-to-many relations.
414 First, let's go over the slot definitions and the available options.
418 In order for a slot to be a join, we must specify that it's
419 <symbol>:db-kind</symbol> <symbol>:join</symbol>, as opposed to
420 <symbol>:base</symbol> or <symbol>:key</symbol>. Once we do that, we
421 still need to tell &clsql; how to create the join statements for the
422 relation. This is what the <symbol>:db-info</symbol> option does. It
423 is a list of keywords and values. The available keywords are:
429 <symbol>:join-class</symbol> - The view class to which we want
430 to join. It can be another view class, or the same view class
431 as our object.</para></listitem>
435 <symbol>:home-key</symbol> - The slot(s) in the immediate object
436 whose value will be compared to the foreign-key slot(s) in the
437 join-class in order to join the two tables. It can be a single
438 slot-name, or it can be a list of slot names.</para></listitem>
442 <symbol>:foreign-key</symbol> - The slot(s) in the join-class
443 which will be compared to the value(s) of the home-key.
448 <symbol>:set</symbol> - A boolean which if false, indicates that
449 this is a one-to-one relation, only one object will be returned.
450 If true, than this is a one-to-many relation, a list of objects
451 will be returned when we ask for this slots value.
456 There are other :join-info options available in &clsql;, but we will
457 save those till we get to the many-to-many relation examples.
462 <sect1 id="csql-creat">
463 <title>Object Creation</title>
466 Now that we have our model laid out, we should create some object.
467 Let us assume that we have a database connect set up already. We
468 first need to create our tables in the database:
472 Note: the file <filename>doc/clsql-tutorial.lisp</filename> contains
473 view class definitions which you can load into your list at this point
474 in order to play along at home.
478 (clsql:create-view-from-class 'employee)
479 (clsql:create-view-from-class 'company)
483 Then we will create our objects. We create them just like you would
484 any other CLOS object:
488 (defvar employee1 (make-instance 'employee
490 :first-name "Vladamir"
492 :email "lenin@soviet.org"))
494 (defvar company1 (make-instance 'company
496 :name "Widgets Inc."))
499 (defvar employee2 (make-instance 'employee
503 :email "stalin@soviet.org"))
507 In order to insert an objects into the database we use the
508 <function>UPDATE-RECORDS-FROM-INSTANCE</function> function as follows:
512 (clsql:update-records-from-instance employee1)
513 (clsql:update-records-from-instance employee2)
514 (clsql:update-records-from-instance company1)
518 Now we can set up some of the relations between employees and
519 companies, and their managers. The
520 <function>ADD-TO-RELATION</function> method provides us with an easy
521 way of doing that. It will update both the relation slot, as well as
522 the home-key and foreign-key slots in both objects in the relation.
526 ;; Lenin manages Stalin (for now)
527 (clsql:add-to-relation employee2 'manager employee1)
529 ;; Lenin and Stalin both work for Widgets Inc.
530 (clsql:add-to-relation company1 'employees employee1)
531 (clsql:add-to-relation company1 'employees employee2)
533 ;; Lenin is president of Widgets Inc.
534 (clsql:add-to-relation company1 'president employee1)
538 After you make any changes to an object, you have to specifically
539 tell &clsql; to update the &sql; database. The
540 <function>UPDATE-RECORDS-FROM-INSTANCE</function> method will write
541 all of the changes you have made to the object into the database.
545 Since &clsql; objects are just normal CLOS objects, we can manipulate
546 their slots just like any other object. For instance, let's say
547 that Lenin changes his email because he was getting too much spam
548 from the German Socialists.
552 ;; Print Lenin's current email address, change it and save it to the
553 ;; database. Get a new object representing Lenin from the database
554 ;; and print the email
556 ;; This lets us use the functional &clsql; interface with [] syntax
557 (clsql:locally-enable-sql-reader-syntax)
559 (format t "The email address of ~A ~A is ~A"
560 (first-name employee1)
561 (last-name employee1)
562 (employee-email employee1))
564 (setf (employee-email employee1) "lenin-nospam@soviets.org")
566 ;; Update the database
567 (clsql:update-records-from-instance employee1)
569 (let ((new-lenin (car (clsql:select 'employee
570 :where [= [slot-value 'employee 'emplid] 1]))))
571 (format t "His new email is ~A"
572 (employee-email new-lenin)))
576 Everything except for the last <function>LET</function> expression
577 is already familiar to us by now. To understand the call to
578 <function>CLSQL:SELECT</function> we need to discuss the
579 Functional &sql; interface and it's integration with the Object
580 Oriented interface of &clsql;.
585 <sect1 id="csql-find">
586 <title>Finding Objects</title>
589 Now that we have our objects in the database, how do we get them out
590 when we need to work with them? &clsql; provides a functional
591 interface to &sql;, which consists of a special Lisp reader macro
592 and some functions. The special syntax allows us to embed &sql; in
593 lisp expressions, and lisp expressions in &sql;, with ease.
597 Once we have turned on the syntax with the expression:
601 (clsql:locally-enable-sql-reader-syntax)
605 We can start entering fragments of &sql; into our lisp reader. We
606 will get back objects which represent the lisp expressions. These
607 objects will later be compiled into &sql; expressions that are
608 optimized for the database backed we are connected to. This means
609 that we have a database independent &sql; syntax. Here are some
614 ;; an attribute or table name
615 [foo] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
617 ;; a attribute identifier with table qualifier
618 [foo bar] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
620 ;; a attribute identifier with table qualifier
621 [= "Lenin" [first_name]] =>
622 #<CLSQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
625 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
627 [and [< [emplid] 2] [= [first_name] "Lenin"]] =>
628 #<CLSQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND
629 (FIRST_NAME = 'Lenin'))>
632 ;; If we want to reference a slot in an object we can us the
633 ;; SLOT-VALUE sql extension
634 [= [slot-value 'employee 'emplid] 1] =>
635 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
637 [= [slot-value 'employee 'emplid]
638 [slot-value 'company 'presidentid]] =>
639 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
643 The <function>SLOT-VALUE</function> operator is important because it
644 let's us query objects in a way that is robust to any changes in the
645 object->table mapping, like column name changes, or table name
646 changes. So when you are querying objects, be sure to use the
647 <function>SLOT-VALUE</function> &sql; extension.
651 Since we can now formulate &sql; relational expression which can be
652 used as qualifiers, like we put after the <symbol>WHERE</symbol>
653 keyword in &sql; statements, we can start querying our objects.
654 &clsql; provides a function <symbol>SELECT</symbol> which can return
655 use complete objects from the database which conform to a qualifier,
656 can be sorted, and various other &sql; operations.
660 The first argument to <symbol>SELECT</symbol> is a class name. it
661 also has a set of keyword arguments which are covered in the
662 documentation. For now we will concern ourselves only with the
663 :where keyword. Select returns a list of objects, or nil if it
664 can't find any. It's important to remember that it always returns a
665 list, so even if you are expecting only one result, you should
666 remember to extract it from the list you get from
667 <symbol>SELECT</symbol>.
672 (clsql:select 'employee)
674 (clsql:select 'company)
676 ;; employees named Lenin
677 (clsql:select 'employee :where [= [slot-value 'employee 'last-name]
680 (clsql:select 'company :where [= [slot-value 'company 'name]
683 ;; Employees of Widget's Inc.
684 (clsql:select 'employee
685 :where [and [= [slot-value 'employee 'companyid]
686 [slot-value 'company 'companyid]]
687 [= [slot-value 'company 'name]
690 ;; Same thing, except that we are using the employee
691 ;; relation in the company view class to do the join for us,
692 ;; saving us the work of writing out the &sql;!
693 (company-employees company1)
695 ;; President of Widgets Inc.
698 ;; Manager of Josef Stalin
699 (employee-manager employee2)
704 <sect1 id="csql-del">
705 <title>Deleting Objects</title>
708 Now that we know how to create objects in our database, manipulate
709 them and query them (including using our predefined relations to
710 save us the trouble writing alot of &sql;) we should learn how to
711 clean up after ourself. It's quite simple really. The function
712 <function>DELETE-INSTANCE-RECORDS</function> will remove an object
713 from the database. However, when we remove an object we are
714 responsible for making sure that the database is left in a correct
719 For example, if we remove a company record, we need to either remove
720 all of it's employees or we need to move them to another company.
721 Likewise if we remove an employee, we should make sure to update any
722 other employees who had them as a manager.
727 <sect1 id="csql-concl">
728 <title>Conclusion</title>
731 There are many nooks and crannies to &clsql;, some of which are
732 covered in the Xanalys documents we refered to earlier, some are
733 not. The best documentation at this time is still the source code
734 for &clsql; itself and the inline documentation for its various