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>:column-</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 <sect1 id="csql-rel">
298 <title>Class Relations</title>
301 In an &sql; only application, the <symbol>EMPLOYEE</symbol> and
302 <symbol>COMPANY</symbol> tables can be queried to determine things
303 like, "Who is Vladimir's manager?", "What company does Josef work
304 for?", and "What employees work for Widgets Inc.". This is done by
305 joining tables with an &sql; query.
309 Who works for Widgets Inc.?
313 SELECT first_name, last_name FROM employee, company
314 WHERE employee.companyid = company.companyid
315 AND company.company_name = "Widgets Inc."
319 Who is Vladimir's manager?
323 SELECT managerid FROM employee
324 WHERE employee.first_name = "Vladimir"
325 AND employee.last_name = "Lenin"
329 What company does Josef work for?
333 SELECT company_name FROM company, employee
334 WHERE employee.first_name = "Josef"
335 AND employee.last-name = "Stalin"
336 AND employee.companyid = company.companyid
340 With &clsql; however we do not need to write out such queries because
341 our view classes can maintain the relations between employees and
342 companies, and employees to their managers for us. We can then access
343 these relations like we would any other attribute of an employee or
344 company object. In order to do this we define some join slots for our
349 What company does an employee work for? If we add the following slot
350 definition to the employee class we can then ask for it's
351 <symbol>COMPANY</symbol> slot and get the appropriate result.
355 ;; In the employee slot list
357 :accessor employee-company
359 :db-info (:join-class company
361 :foreign-key companyid
366 Who are the employees of a given company? And who is the president of
367 it? We add the following slot definition to the company view class and
368 we can then ask for it's <symbol>EMPLOYEES</symbol> slot and get the
373 ;; In the company slot list
375 :reader company-employees
377 :db-info (:join-class employee
379 :foreign-key companyid
385 :db-info (:join-class employee
386 :home-key presidentid
392 And lastly, to define the relation between an employee and their
397 ;; In the employee slot list
399 :accessor employee-manager
401 :db-info (:join-class employee
408 &clsql; join slots can represent one-to-one, one-to-many, and
409 many-to-many relations. Above we only have one-to-one and one-to-many
410 relations, later we will explain how to model many-to-many relations.
411 First, let's go over the slot definitions and the available options.
415 In order for a slot to be a join, we must specify that it's
416 <symbol>:db-kind</symbol> <symbol>:join</symbol>, as opposed to
417 <symbol>:base</symbol> or <symbol>:key</symbol>. Once we do that, we
418 still need to tell &clsql; how to create the join statements for the
419 relation. This is what the <symbol>:db-info</symbol> option does. It
420 is a list of keywords and values. The available keywords are:
426 <symbol>:join-class</symbol> - The view class to which we want
427 to join. It can be another view class, or the same view class
428 as our object.</para></listitem>
432 <symbol>:home-key</symbol> - The slot(s) in the immediate object
433 whose value will be compared to the foreign-key slot(s) in the
434 join-class in order to join the two tables. It can be a single
435 slot-name, or it can be a list of slot names.</para></listitem>
439 <symbol>:foreign-key</symbol> - The slot(s) in the join-class
440 which will be compared to the value(s) of the home-key.
445 <symbol>:set</symbol> - A boolean which if false, indicates that
446 this is a one-to-one relation, only one object will be returned.
447 If true, than this is a one-to-many relation, a list of objects
448 will be returned when we ask for this slots value.
453 There are other :join-info options available in &clsql;, but we will
454 save those till we get to the many-to-many relation examples.
459 <sect1 id="csql-creat">
460 <title>Object Creation</title>
463 Now that we have our model laid out, we should create some object.
464 Let us assume that we have a database connect set up already. We
465 first need to create our tables in the database:
469 Note: the file <filename>examples/clsql-tutorial.lisp</filename> contains
470 view class definitions which you can load into your list at this point
471 in order to play along at home.
475 (clsql:create-view-from-class 'employee)
476 (clsql:create-view-from-class 'company)
480 Then we will create our objects. We create them just like you would
481 any other CLOS object:
485 (defvar company1 (make-instance 'company
488 :name "Widgets Inc."))
490 (defvar employee1 (make-instance 'employee
492 :first-name "Vladimir"
494 :email "lenin@soviet.org"
497 (defvar employee2 (make-instance 'employee
501 :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 After you make any changes to an object, you have to specifically
519 tell &clsql; to update the &sql; database. The
520 <function>UPDATE-RECORDS-FROM-INSTANCE</function> method will write
521 all of the changes you have made to the object into the database.
525 Since &clsql; objects are just normal CLOS objects, we can manipulate
526 their slots just like any other object. For instance, let's say
527 that Lenin changes his email because he was getting too much spam
528 from the German Socialists.
532 ;; Print Lenin's current email address, change it and save it to the
533 ;; database. Get a new object representing Lenin from the database
534 ;; and print the email
536 ;; This lets us use the functional &clsql; interface with [] syntax
537 (clsql:locally-enable-sql-reader-syntax)
539 (format t "The email address of ~A ~A is ~A"
540 (first-name employee1)
541 (last-name employee1)
542 (employee-email employee1))
544 (setf (employee-email employee1) "lenin-nospam@soviets.org")
546 ;; Update the database
547 (clsql:update-records-from-instance employee1)
549 (let ((new-lenin (car (clsql:select 'employee
550 :where [= [slot-value 'employee 'emplid] 1]))))
551 (format t "His new email is ~A"
552 (employee-email new-lenin)))
556 Everything except for the last <function>LET</function> expression
557 is already familiar to us by now. To understand the call to
558 <function>CLSQL:SELECT</function> we need to discuss the
559 Functional &sql; interface and it's integration with the Object
560 Oriented interface of &clsql;.
565 <sect1 id="csql-find">
566 <title>Finding Objects</title>
569 Now that we have our objects in the database, how do we get them out
570 when we need to work with them? &clsql; provides a functional
571 interface to &sql;, which consists of a special Lisp reader macro
572 and some functions. The special syntax allows us to embed &sql; in
573 lisp expressions, and lisp expressions in &sql;, with ease.
577 Once we have turned on the syntax with the expression:
581 (clsql:locally-enable-sql-reader-syntax)
585 We can start entering fragments of &sql; into our lisp reader. We
586 will get back objects which represent the lisp expressions. These
587 objects will later be compiled into &sql; expressions that are
588 optimized for the database backed we are connected to. This means
589 that we have a database independent &sql; syntax. Here are some
594 ;; an attribute or table name
595 [foo] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
597 ;; a attribute identifier with table qualifier
598 [foo bar] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
600 ;; a attribute identifier with table qualifier
601 [= "Lenin" [first_name]] =>
602 #<CLSQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
605 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
607 [and [< [emplid] 2] [= [first_name] "Lenin"]] =>
608 #<CLSQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND
609 (FIRST_NAME = 'Lenin'))>
612 ;; If we want to reference a slot in an object we can us the
613 ;; SLOT-VALUE sql extension
614 [= [slot-value 'employee 'emplid] 1] =>
615 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
617 [= [slot-value 'employee 'emplid]
618 [slot-value 'company 'presidentid]] =>
619 #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
623 The <function>SLOT-VALUE</function> operator is important because it
624 let's us query objects in a way that is robust to any changes in the
625 object->table mapping, like column name changes, or table name
626 changes. So when you are querying objects, be sure to use the
627 <function>SLOT-VALUE</function> &sql; extension.
631 Since we can now formulate &sql; relational expression which can be
632 used as qualifiers, like we put after the <symbol>WHERE</symbol>
633 keyword in &sql; statements, we can start querying our objects.
634 &clsql; provides a function <symbol>SELECT</symbol> which can return
635 use complete objects from the database which conform to a qualifier,
636 can be sorted, and various other &sql; operations.
640 The first argument to <symbol>SELECT</symbol> is a class name. it
641 also has a set of keyword arguments which are covered in the
642 documentation. For now we will concern ourselves only with the
643 :where keyword. Select returns a list of objects, or nil if it
644 can't find any. It's important to remember that it always returns a
645 list, so even if you are expecting only one result, you should
646 remember to extract it from the list you get from
647 <symbol>SELECT</symbol>.
652 (clsql:select 'employee)
654 (clsql:select 'company)
656 ;; employees named Lenin
657 (clsql:select 'employee :where [= [slot-value 'employee 'last-name]
660 (clsql:select 'company :where [= [slot-value 'company 'name]
663 ;; Employees of Widget's Inc.
664 (clsql:select 'employee
665 :where [and [= [slot-value 'employee 'companyid]
666 [slot-value 'company 'companyid]]
667 [= [slot-value 'company 'name]
670 ;; Same thing, except that we are using the employee
671 ;; relation in the company view class to do the join for us,
672 ;; saving us the work of writing out the &sql;!
673 (company-employees company1)
675 ;; President of Widgets Inc.
678 ;; Manager of Josef Stalin
679 (employee-manager employee2)
684 <sect1 id="csql-del">
685 <title>Deleting Objects</title>
688 Now that we know how to create objects in our database, manipulate
689 them and query them (including using our predefined relations to
690 save us the trouble writing alot of &sql;) we should learn how to
691 clean up after ourself. It's quite simple really. The function
692 <function>DELETE-INSTANCE-RECORDS</function> will remove an object
693 from the database. However, when we remove an object we are
694 responsible for making sure that the database is left in a correct
699 For example, if we remove a company record, we need to either remove
700 all of it's employees or we need to move them to another company.
701 Likewise if we remove an employee, we should make sure to update any
702 other employees who had them as a manager.
707 <sect1 id="csql-concl">
708 <title>Conclusion</title>
711 There are many nooks and crannies to &clsql;, some of which are
712 covered in the Xanalys documents we refered to earlier, some are
713 not. The best documentation at this time is still the source code
714 for &clsql; itself and the inline documentation for its various