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>UncommonSQL</title>
11 <sect1 id="usql-intro">
12 <title>Introduction</title>
15 The goal of this tutorial is to guide a new developer thru the
16 process of creating a set of &usql; classes providing a
17 Object-Oriented interface to persistent data stored in an &sql;
18 database. We will assume that the reader is familiar with how
19 &sql; works, how relations (tables) should be structured, and
20 has created at least one &sql; application previously. We will
21 also assume a minor level of experience with Common Lisp.
25 &usql; provides two different interfaces to &sql; databases, a
26 Functional interface, and an Object-Oriented interface. The
27 Functional interface consists of a special syntax for embedded
28 &sql; expressions in Lisp, and provides lisp functions for &sql;
29 operations like <symbol>SELECT</symbol> and
30 <symbol>UPDATE</symbol>. The object-oriented interface provides
31 a way for mapping Common Lisp Objects System (CLOS) objects into
32 databases and includes functions for inserting new objects,
33 querying objects, and removing objects. Most applications will
34 use a combination of the two.
38 &usql; is based on the CommonSQL package from Xanalys, so the
39 documentation that Xanalys makes available online is useful for
40 &usql; as well. It is suggested that developers new to &usql; check
41 their documentation out, as any differences between CommonSQL
42 and &usql; are minor. Xanalys makes the following documents
49 <ulink url="http://www.lispworks.com/reference/lw43/LWUG/html/lwuser-167.htm">
50 <citetitle>Xanalys LispWorks User Guide - The CommonSQL Package
58 <ulink url="http://www.lispworks.com/reference/lw43/LWRM/html/lwref-383.htm">
59 <citetitle>Xanalys LispWorks Reference Manual - The SQL Package</citetitle>
66 <ulink url="http://www.ravenbrook.com/doc/2002/09/13/common-sql/">
67 <citetitle>CommonSQL Tutorial by Nick Levine</citetitle>
75 <title>Data Modeling with UncommonSQL</title>
78 Before we can create, query and manipulate &usql; objects, we need to
79 define our data model as noted by Philip Greenspun
83 url="http://www.arsdigita.com/books/sql/data-modeling.html">
84 <citetitle>Philip Greenspun's "SQL For Web Nerds" - Data Modeling
92 When data modeling, you are telling the RDBMS the following:
97 <para>What elements of the data you will store</para>
100 <para>How large each element can be</para>
103 <para>What kind of information each element can contain</para>
106 <para>What elements may be left blank</para>
109 <para>Which elements are constrained to a fixed range</para>
112 <para>Whether and how various tables are to be linked</para>
117 With &sql; database one would do this by defining a set of
118 relations, or tables, followed by a set of queries for joining
119 the tables together in order to construct complex records.
120 However, with &usql; we do this by defining a set of CLOS classes,
121 specifying how they will be turned into tables, and how they can
122 be joined to one another via relations between their attributes.
123 The &sql; tables, as well as the queries for joining them together
124 are created for us automatically, saving us from dealing with
125 some of the tedium of &sql;.
129 Let us start with a simple example of two &sql; tables, and the
130 relations between them.
134 CREATE TABLE EMPLOYEE (
135 emplid NOT NULL number(38),
136 first_name NOT NULL varchar2(30),
137 last_name NOT NULL varchar2(30),
139 companyid NOT NULL number(38),
143 CREATE TABLE COMPANY (
144 companyid NOT NULL number(38),
145 name NOT NULL varchar2(100),
146 presidentid NOT NULL number(38)
151 This is of course the canonical &sql; tutorial example, "The Org Chart".
155 In &usql;, we would have two "view classes" (a fancy word for a class
156 mapped into a database). They would be defined as follows:
160 (sql:def-view-class employee ()
163 :db-constraints :not-null
169 :initarg :first-name)
175 :accessor employee-email
184 (:base-table employee))
186 (sql:def-view-class company ()
189 :db-constraints :not-null
197 (:base-table company))
201 The DEF-VIEW-CLASS macro is just like the normal CLOS DEFCLASS
202 macro, except that it handles several slot options that DEFCLASS
203 doesn't. These slot options have to do with the mapping of the slot
204 into the database. We only use a few of the slot options in the
205 above example, but there are several others.
211 :column -- The name of the &sql; column this slot is stored in.
212 Defaults to the slot name. If the slot name is not a valid &sql;
213 identifier, it is escaped, so foo-bar becomes foo_bar.
217 :db-kind -- The kind of DB mapping which is performed for this
218 slot. :BASE indicates the slot maps to an ordinary column of the
219 DB view. :KEY indicates that this slot corresponds to part of the
220 unique keys for this view, :JOIN indicates a join slot
221 representing a relation to another view and :virtual indicates
222 that this slot is an ordinary CLOS slot. Defaults to :base.
226 :db-reader -- If a string, then when reading values from the DB, the
227 string will be used for a format string, with the only value being
228 the value from the database. The resulting string will be used as
229 the slot value. If a function then it will take one argument, the
230 value from the database, and return the value that should be put
235 :db-writer -- If a string, then when reading values from the slot
236 for the DB, the string will be used for a format string, with the
237 only value being the value of the slot. The resulting string will
238 be used as the column value in the DB. If a function then it will
239 take one argument, the value of the slot, and return the value
240 that should be put into the database.
244 :db-type -- A string which will be used as the type specifier for
245 this slots column definition in the database.
249 :nulls-ok -- If t, all sql NULL values retrieved from the database
250 become nil; if nil, all NULL values retrieved are converted by
255 :db-info -- A join specification.
260 In our example each table as a primary key attribute, which is
261 required to be unique. We indicate that a slot is part of the
262 primary key (&usql; supports multi-field primary keys) by specifying
263 the :db-kind :key slot option.
267 The &sql; type of a slot when it is mapped into the database is
268 determined by the :type slot option. The argument for the :type
269 option is a Common Lisp datatype. The &usql; framework will determine
270 the appropriate mapping depending on the database system the table
271 is being created in. If we really wanted to determine what &sql; type
272 was used for a slot, we could specify a :db-type option like
273 "NUMBER(38)" and we would be guaranteed that the slot would be
274 stored n the DB as a NUMBER(38). This is not recomended because it
275 could makes your view class unportable across database systems.
279 DEF-VIEW-CLASS also supports some class options, like :base-table.
280 The :base-table option specifies what the table name for the view
281 class will be when it is mapped into the database.
285 <sect1 id="usql-rel">
286 <title>Class Relations</title>
289 In an &sql; only application, the EMPLOYEE and COMPANY tables can be
290 queried to determine things like, "Who is Vladamir's manager?", What
291 company does Josef work for?", and "What employees work for Widgets
292 Inc.". This is done by joining tables with an &sql; query.
296 Who works for Widgets Inc.?
300 SELECT first_name, last_name FROM employee, company
301 WHERE employee.companyid = company.companyid
302 AND company.company_name = "Widgets Inc."
304 Who is Vladamir's manager
306 SELECT managerid FROM employee
307 WHERE employee.first_name = "Vladamir"
308 AND employee.last_name = "Lenin"
310 What company does Josef work for?
312 SELECT company_name FROM company, employee
313 WHERE employee.first_name = "Josef"
314 AND employee.last-name = "Stalin"
315 AND employee.companyid = company.companyid
319 With &usql; however we do not need to write out such queries because our
320 view classes can maintain the relations between employees and
321 companies, and employees to their managers for us. We can then access
322 these relations like we would any other attribute of an employee or
323 company object. In order to do this we define some join slots for our
328 What company does an employee work for? If we add the following slot
329 definition to the employee class we can then ask for it's COMPANY slot
330 and get the appropriate result.
334 ;; In the employee slot list
336 :accessor employee-company
338 :db-info (:join-class company
340 :foreign-key companyid
345 Who are the employees of a given company? And who is the president of
346 it? We add the following slot definition to the company view class and
347 we can then ask for it's EMPLOYEES slot and get the right result.
351 ;; In the company slot list
353 :reader company-employees
355 :db-info (:join-class employee
357 :foreign-key companyid
363 :db-info (:join-class employee
364 :home-key presidentid
370 And lastly, to define the relation between an employee and their
375 ;; In the employee slot list
377 :accessor employee-manager
379 :db-info (:join-class employee
386 &usql; join slots can represent one-to-one, one-to-many, and
387 many-to-many relations. Above we only have one-to-one and one-to-many
388 relations, later we will explain how to model many-to-many relations.
389 First, let's go over the slot definitions and the available options.
393 In order for a slot to be a join, we must specify that it's :db-kind
394 :join, as opposed to :base or :key. Once we do that, we still need to
395 tell &usql; how to create the join statements for the relation. This is
396 what the :db-info option does. It is a list of keywords and values.
397 The available keywords are:
402 :join-class -- The view class to which we want to join. It can be
403 another view class, or the same view class as our object.
407 :home-key -- The slot(s) in the immediate object whose value will
408 be compared to the foreign-key slot(s) in the join-class in order
409 to join the two tables. It can be a single slot-name, or it can
410 be a list of slot names.
414 :foreign-key -- The slot(s) in the join-class which will be compared
415 to the value(s) of the home-key.
419 :set -- A boolean which if false, indicates that this is a
420 one-to-one relation, only one object will be returned. If true,
421 than this is a one-to-many relation, a list of objects will be
422 returned when we ask for this slots value.
427 There are other :join-info options available in &usql;, but we will save
428 those till we get to the many-to-many relation examples.
433 <sect1 id="usql-creat">
434 <title>Object Creation</title>
437 Now that we have our model laid out, we should create some object.
438 Let us assume that we have a database connect set up already. We
439 first need to create our tables in the database:
443 Note: the file usql-tutorial.lisp contains view class definitions
444 which you can load into your list at this point in order to play along
449 (sql:create-view-from-class 'employee)
450 (sql:create-view-from-class 'company)
454 Then we will create our objects. We create them just like you would
455 any other CLOS object:
459 (defvar employee1 (make-instance 'employee
461 :first-name "Vladamir"
463 :email "lenin@soviet.org"))
465 (defvar company1 (make-instance 'company
467 :name "Widgets Inc."))
470 (defvar employee2 (make-instance 'employee
474 :email "stalin@soviet.org"))
478 In order to insert an objects into the database we use the
479 UPDATE-RECORDS-FROM-INSTANCE function as follows:
483 (sql:update-records-from-instance employee1)
484 (sql:update-records-from-instance employee2)
485 (sql:update-records-from-instance company1)
489 Now we can set up some of the relations between employees and
490 companies, and their managers. The ADD-TO-RELATION method provides us
491 with an easy way of doing that. It will update both the relation
492 slot, as well as the home-key and foreign-key slots in both objects in
497 ;; Lenin manages Stalin (for now)
498 (sql:add-to-relation employee2 'manager employee1)
500 ;; Lenin and Stalin both work for Widgets Inc.
501 (sql:add-to-relation company1 'employees employee1)
502 (sql:add-to-relation company1 'employees employee2)
504 ;; Lenin is president of Widgets Inc.
505 (sql:add-to-relation company1 'president employee1)
509 After you make any changes to an object, you have to specifically tell
510 &usql; to update the &sql; database. The UPDATE-RECORDS-FROM-INSTANCE
511 method will write all of the changes you have made to the object into
516 Since &usql; objects re just normal CLOS objects, we can manipulate
517 their slots just like any other object. For instance, let's say that
518 Lenin changes his email because he was getting too much SPAM fro the
523 ;; Print Lenin's current email address, change it and save it to the
524 ;; database. Get a new object representing Lenin from the database
525 ;; and print the email
527 ;; This lets us use the functional &usql; interface with [] syntax
528 (sql:locally-enable-sql-reader-syntax)
530 (format t "The email address of ~A ~A is ~A"
531 (first-name employee1)
532 (last-name employee1)
533 (employee-email employee1))
535 (setf (employee-email employee1) "lenin-nospam@soviets.org")
537 ;; Update the database
538 (sql:update-records-from-instance employee1)
540 (let ((new-lenin (car (sql:select 'employee
541 :where [= [slot-value 'employee 'emplid] 1]))))
542 (format t "His new email is ~A"
543 (employee-email new-lenin)))
547 Everything except for the last LET expression is already familiar to
548 us by now. To understand the call to SQL:SELECT we need to discuss
549 the Functional &sql; interface and it's integration with the Object
550 Oriented interface of &usql;.
555 <sect1 id="usql-find">
556 <title>Finding Objects</title>
559 Now that we have our objects in the database, how do we get them out
560 when we need to work with them? &usql; provides a Functional interface
561 to &sql;, which consists of a special Lisp reader macro and some
562 functions. The special syntax allows us to embed &sql; in lisp
563 expressions, and lisp expressions in &sql;, with ease.
567 Once we have turned on the syntax with the expression:
571 (sql:locally-enable-sql-reader-syntax)
575 we can start entering fragments of &sql; into our lisp reader. We will
576 get back objects which represent the lisp expressions. These objects
577 will later be compiled into &sql; expressions that are optimized for the
578 database backed we are connected to. This means that we have a
579 database independent &sql; syntax. Here are some examples:
583 ;; an attribute or table name
584 [foo] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
586 ;; a attribute identifier with table qualifier
587 [foo bar] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
589 ;; a attribute identifier with table qualifier
590 [= "Lenin" [first_name]] =>
591 #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
594 #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
596 [and [< [emplid] 2] [= [first_name] "Lenin"]] =>
597 #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND
598 (FIRST_NAME = 'Lenin'))>
601 ;; If we want to reference a slot in an object we can us the
602 ;; SLOT-VALUE sql extension
603 [= [slot-value 'employee 'emplid] 1] =>
604 #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
606 [= [slot-value 'employee 'emplid]
607 [slot-value 'company 'presidentid]] =>
608 #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
612 The SLOT-VALUE operator is important because it let's us query objects
613 in a way that is robust to any changes in the object->table mapping,
614 like column name changes, or table name changes. So when you are
615 querying objects, be sure to use the SLOT-VALUE &sql; extension.
619 Since we can now formulate &sql; relational expression which can be used
620 as qualifiers, like we put after the WHERE keyword in &sql; statements,
621 we can start querying our objects. &usql; provides a function SELECT
622 which can return use complete objects from the database which conform
623 to a qualifier, can be sorted, and various other &sql; operations.
627 The first argument to SELECT is a class name. it also has a set of
628 keyword arguments which are covered in the documentation. For now we
629 will concern ourselves only with the :where keyword. Select returns a
630 list of objects, or nil if it can't find any. It's important to
631 remember that it always returns a list, so even if you are expecting
632 only one result, you should remember to extract it from the list you
638 (sql:select 'employee)
640 (sql:select 'company)
642 ;; employees named Lenin
643 (sql:select 'employee :where [= [slot-value 'employee 'last-name]
646 (sql:select 'company :where [= [slot-value 'company 'name]
649 ;; Employees of Widget's Inc.
650 (sql:select 'employee
651 :where [and [= [slot-value 'employee 'companyid]
652 [slot-value 'company 'companyid]]
653 [= [slot-value 'company 'name]
656 ;; Same thing, except that we are using the employee
657 ;; relation in the company view class to do the join for us,
658 ;; saving us the work of writing out the &sql;!
659 (company-employees company1)
661 ;; President of Widgets Inc.
664 ;; Manager of Josef Stalin
665 (employee-manager employee2)
670 <sect1 id="usql-del">
671 <title>Deleting Objects</title>
674 Now that we know how to create objects in our database, manipulate
675 them and query them (including using our predefined relations to save
676 us the trouble writing alot of &sql;) we should learn how to clean up
677 after ourself. It's quite simple really. The function
678 DELETE-INSTANCE-RECORDS will remove an object from the database.
679 However, when we remove an object we are responsible for making sure
680 that the database is left in a correct state.
684 For example, if we remove a company record, we need to either remove
685 all of it's employees or we need to move them to another company.
686 Likewise if we remove an employee, we should make sure to update any
687 other employees who had them as a manager.
692 <sect1 id="usql-concl">
693 <title>Conclusion</title>
696 There are alot more nooks and crannies to &usql;, some of which are
697 covered n the Xanalys documents we refered to earlier, some are not.
698 The best documentation at this time is still the source code for &usql;
699 itself and the inline documentation for it's various function.