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 has
20 created at least one SQL application previously. We will also
21 assume a minor level of experience with Common Lisp.
25 UncommonSQL (USQL) provides two different interfaces to SQL
26 databases, a Functional interface, and an Object-Oriented
27 interface. The Functional interface consists of a special
28 syntax for embedded SQL expressions in Lisp, and provides lisp
29 functions for SQL operations like SELECT and UPDATE. The OO
30 interface provides a way for mapping Common Lisp Objects System
31 (CLOS) objects into databases and includes functions for
32 inserting new objects, querying objects, and removing objects.
33 Most applications will use a combination of the two.
37 USQL is based on the CommonSQL package from Xanalys, so the
38 documentation that Xanalys makes available online is useful for
39 USQL as well. It is suggested that developers new to USQL check
40 their documentation out, as any differences between CommonSQL
41 and USQL are minor. Xanalys makes the following documents
48 <ulink url="http://www.lispworks.com/reference/lw43/LWUG/html/lwuser-167.htm">
49 <citetitle>Xanalys LispWorks User Guide - The CommonSQL Package
57 <ulink url="http://www.lispworks.com/reference/lw43/LWRM/html/lwref-383.htm">
58 <citetitle>Xanalys LispWorks Reference Manual - The SQL Package</citetitle>
65 <ulink url="http://www.ravenbrook.com/doc/2002/09/13/common-sql/">
66 <citetitle>CommonSQL Tutorial by Nick Levine</citetitle>
74 <title>Data Modeling with UncommonSQL</title>
77 Before we can create, query and manipulate USQL objects, we need to
78 define our data model as noted by Philip Greenspun
82 url="http://www.arsdigita.com/books/sql/data-modeling.html">
83 <citetitle>Philip Greenspun's "SQL For Web Nerds" - Data Modeling
91 When data modeling, you are telling the RDBMS the following:
96 <para>What elements of the data you will store</para>
99 <para>How large each element can be</para>
102 <para>What kind of information each element can contain</para>
105 <para>What elements may be left blank</para>
108 <para>Which elements are constrained to a fixed range</para>
111 <para>Whether and how various tables are to be linked</para>
116 With SQL database one would do this by defining a set of
117 relations, or tables, followed by a set of queries for joining
118 the tables together in order to construct complex records.
119 However, with USQL we do this by defining a set of CLOS classes,
120 specifying how they will be turned into tables, and how they can
121 be joined to one another via relations between their attributes.
122 The SQL tables, as well as the queries for joining them together
123 are created for us automatically, saving us from dealing with
124 some of the tedium of SQL.
128 Let us start with a simple example of two SQL tables, and the
129 relations between them.
133 CREATE TABLE EMPLOYEE (
134 emplid NOT NULL number(38),
135 first_name NOT NULL varchar2(30),
136 last_name NOT NULL varchar2(30),
138 companyid NOT NULL number(38),
142 CREATE TABLE COMPANY (
143 companyid NOT NULL number(38),
144 name NOT NULL varchar2(100),
145 presidentid NOT NULL number(38)
150 This is of course the canonical SQL tutorial example, "The Org Chart".
154 In USQL, we would have two "view classes" (a fancy word for a class
155 mapped into a database). They would be defined as follows:
159 (sql:def-view-class employee ()
162 :db-constraints :not-null
168 :initarg :first-name)
174 :accessor employee-email
183 (:base-table employee))
185 (sql:def-view-class company ()
188 :db-constraints :not-null
196 (:base-table company))
200 The DEF-VIEW-CLASS macro is just like the normal CLOS DEFCLASS
201 macro, except that it handles several slot options that DEFCLASS
202 doesn't. These slot options have to do with the mapping of the slot
203 into the database. We only use a few of the slot options in the
204 above example, but there are several others.
210 :column -- The name of the SQL column this slot is stored in.
211 Defaults to the slot name. If the slot name is not a valid SQL
212 identifier, it is escaped, so foo-bar becomes foo_bar.
216 :db-kind -- The kind of DB mapping which is performed for this
217 slot. :BASE indicates the slot maps to an ordinary column of the
218 DB view. :KEY indicates that this slot corresponds to part of the
219 unique keys for this view, :JOIN indicates a join slot
220 representing a relation to another view and :virtual indicates
221 that this slot is an ordinary CLOS slot. Defaults to :base.
225 :db-reader -- If a string, then when reading values from the DB, the
226 string will be used for a format string, with the only value being
227 the value from the database. The resulting string will be used as
228 the slot value. If a function then it will take one argument, the
229 value from the database, and return the value that should be put
234 :db-writer -- If a string, then when reading values from the slot
235 for the DB, the string will be used for a format string, with the
236 only value being the value of the slot. The resulting string will
237 be used as the column value in the DB. If a function then it will
238 take one argument, the value of the slot, and return the value
239 that should be put into the database.
243 :db-type -- A string which will be used as the type specifier for
244 this slots column definition in the database.
248 :nulls-ok -- If t, all sql NULL values retrieved from the database
249 become nil; if nil, all NULL values retrieved are converted by
254 :db-info -- A join specification.
259 In our example each table as a primary key attribute, which is
260 required to be unique. We indicate that a slot is part of the
261 primary key (USQL supports multi-field primary keys) by specifying
262 the :db-kind :key slot option.
266 The SQL type of a slot when it is mapped into the database is
267 determined by the :type slot option. The argument for the :type
268 option is a Common Lisp datatype. The USQL framework will determine
269 the appropriate mapping depending on the database system the table
270 is being created in. If we really wanted to determine what SQL type
271 was used for a slot, we could specify a :db-type option like
272 "NUMBER(38)" and we would be guaranteed that the slot would be
273 stored n the DB as a NUMBER(38). This is not recomended because it
274 could makes your view class unportable across database systems.
278 DEF-VIEW-CLASS also supports some class options, like :base-table.
279 The :base-table option specifies what the table name for the view
280 class will be when it is mapped into the database.
284 <sect1 id="usql-rel">
285 <title>Class Relations</title>
288 In an SQL only application, the EMPLOYEE and COMPANY tables can be
289 queried to determine things like, "Who is Vladamir's manager?", What
290 company does Josef work for?", and "What employees work for Widgets
291 Inc.". This is done by joining tables with an SQL query.
295 Who works for Widgets Inc.?
299 SELECT first_name, last_name FROM employee, company
300 WHERE employee.companyid = company.companyid
301 AND company.company_name = "Widgets Inc."
303 Who is Vladamir's manager
305 SELECT managerid FROM employee
306 WHERE employee.first_name = "Vladamir"
307 AND employee.last_name = "Lenin"
309 What company does Josef work for?
311 SELECT company_name FROM company, employee
312 WHERE employee.first_name = "Josef"
313 AND employee.last-name = "Stalin"
314 AND employee.companyid = company.companyid
318 With USQL however we do not need to write out such queries because our
319 view classes can maintain the relations between employees and
320 companies, and employees to their managers for us. We can then access
321 these relations like we would any other attribute of an employee or
322 company object. In order to do this we define some join slots for our
327 What company does an employee work for? If we add the following slot
328 definition to the employee class we can then ask for it's COMPANY slot
329 and get the appropriate result.
333 ;; In the employee slot list
335 :accessor employee-company
337 :db-info (:join-class company
339 :foreign-key companyid
344 Who are the employees of a given company? And who is the president of
345 it? We add the following slot definition to the company view class and
346 we can then ask for it's EMPLOYEES slot and get the right result.
350 ;; In the company slot list
352 :reader company-employees
354 :db-info (:join-class employee
356 :foreign-key companyid
362 :db-info (:join-class employee
363 :home-key presidentid
369 And lastly, to define the relation between an employee and their
374 ;; In the employee slot list
376 :accessor employee-manager
378 :db-info (:join-class employee
385 USQL join slots can represent one-to-one, one-to-many, and
386 many-to-many relations. Above we only have one-to-one and one-to-many
387 relations, later we will explain how to model many-to-many relations.
388 First, let's go over the slot definitions and the available options.
392 In order for a slot to be a join, we must specify that it's :db-kind
393 :join, as opposed to :base or :key. Once we do that, we still need to
394 tell USQL how to create the join statements for the relation. This is
395 what the :db-info option does. It is a list of keywords and values.
396 The available keywords are:
401 :join-class -- The view class to which we want to join. It can be
402 another view class, or the same view class as our object.
406 :home-key -- The slot(s) in the immediate object whose value will
407 be compared to the foreign-key slot(s) in the join-class in order
408 to join the two tables. It can be a single slot-name, or it can
409 be a list of slot names.
413 :foreign-key -- The slot(s) in the join-class which will be compared
414 to the value(s) of the home-key.
418 :set -- A boolean which if false, indicates that this is a
419 one-to-one relation, only one object will be returned. If true,
420 than this is a one-to-many relation, a list of objects will be
421 returned when we ask for this slots value.
426 There are other :join-info options available in USQL, but we will save
427 those till we get to the many-to-many relation examples.
432 <sect1 id="usql-creat">
433 <title>Object Creation</title>
436 Now that we have our model laid out, we should create some object.
437 Let us assume that we have a database connect set up already. We
438 first need to create our tables in the database:
442 Note: the file usql-tutorial.lisp contains view class definitions
443 which you can load into your list at this point in order to play along
448 (sql:create-view-from-class 'employee)
449 (sql:create-view-from-class 'company)
453 Then we will create our objects. We create them just like you would
454 any other CLOS object:
458 (defvar employee1 (make-instance 'employee
460 :first-name "Vladamir"
462 :email "lenin@soviet.org"))
464 (defvar company1 (make-instance 'company
466 :name "Widgets Inc."))
469 (defvar employee2 (make-instance 'employee
473 :email "stalin@soviet.org"))
477 In order to insert an objects into the database we use the
478 UPDATE-RECORDS-FROM-INSTANCE function as follows:
482 (sql:update-records-from-instance employee1)
483 (sql:update-records-from-instance employee2)
484 (sql:update-records-from-instance company1)
488 Now we can set up some of the relations between employees and
489 companies, and their managers. The ADD-TO-RELATION method provides us
490 with an easy way of doing that. It will update both the relation
491 slot, as well as the home-key and foreign-key slots in both objects in
496 ;; Lenin manages Stalin (for now)
497 (sql:add-to-relation employee2 'manager employee1)
499 ;; Lenin and Stalin both work for Widgets Inc.
500 (sql:add-to-relation company1 'employees employee1)
501 (sql:add-to-relation company1 'employees employee2)
503 ;; Lenin is president of Widgets Inc.
504 (sql:add-to-relation company1 'president employee1)
508 After you make any changes to an object, you have to specifically tell
509 USQL to update the SQL database. The UPDATE-RECORDS-FROM-INSTANCE
510 method will write all of the changes you have made to the object into
515 Since USQL objects re just normal CLOS objects, we can manipulate
516 their slots just like any other object. For instance, let's say that
517 Lenin changes his email because he was getting too much SPAM fro the
522 ;; Print Lenin's current email address, change it and save it to the
523 ;; database. Get a new object representing Lenin from the database
524 ;; and print the email
526 ;; This lets us use the functional USQL interface with [] syntax
527 (sql:locally-enable-sql-reader-syntax)
529 (format t "The email address of ~A ~A is ~A"
530 (first-name employee1)
531 (last-name employee1)
532 (employee-email employee1))
534 (setf (employee-email employee1) "lenin-nospam@soviets.org")
536 ;; Update the database
537 (sql:update-records-from-instance employee1)
539 (let ((new-lenin (car (sql:select 'employee
540 :where [= [slot-value 'employee 'emplid] 1]))))
541 (format t "His new email is ~A"
542 (employee-email new-lenin)))
546 Everything except for the last LET expression is already familiar to
547 us by now. To understand the call to SQL:SELECT we need to discuss
548 the Functional SQL interface and it's integration with the Object
549 Oriented interface of USQL.
554 <sect1 id="usql-find">
555 <title>Finding Objects</title>
558 Now that we have our objects in the database, how do we get them out
559 when we need to work with them? USQL provides a Functional interface
560 to SQL, which consists of a special Lisp reader macro and some
561 functions. The special syntax allows us to embed SQL in lisp
562 expressions, and lisp expressions in SQL, with ease.
566 Once we have turned on the syntax with the expression:
570 (sql:locally-enable-sql-reader-syntax)
574 we can start entering fragments of SQL into our lisp reader. We will
575 get back objects which represent the lisp expressions. These objects
576 will later be compiled into SQL expressions that are optimized for the
577 database backed we are connected to. This means that we have a
578 database independent SQL syntax. Here are some examples:
582 ;; an attribute or table name
583 [foo] => #<MAISQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
585 ;; a attribute identifier with table qualifier
586 [foo bar] => #<MAISQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
588 ;; a attribute identifier with table qualifier
589 [= "Lenin" [first_name]] =>
590 #<MAISQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
593 #<MAISQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
595 [and [< [emplid] 2] [= [first_name] "Lenin"]] =>
596 #<MAISQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND
597 (FIRST_NAME = 'Lenin'))>
600 ;; If we want to reference a slot in an object we can us the
601 ;; SLOT-VALUE sql extension
602 [= [slot-value 'employee 'emplid] 1] =>
603 #<MAISQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
605 [= [slot-value 'employee 'emplid]
606 [slot-value 'company 'presidentid]] =>
607 #<MAISQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
611 The SLOT-VALUE operator is important because it let's us query objects
612 in a way that is robust to any changes in the object->table mapping,
613 like column name changes, or table name changes. So when you are
614 querying objects, be sure to use the SLOT-VALUE SQL extension.
618 Since we can now formulate SQL relational expression which can be used
619 as qualifiers, like we put after the WHERE keyword in SQL statements,
620 we can start querying our objects. USQL provides a function SELECT
621 which can return use complete objects from the database which conform
622 to a qualifier, can be sorted, and various other SQL operations.
626 The first argument to SELECT is a class name. it also has a set of
627 keyword arguments which are covered in the documentation. For now we
628 will concern ourselves only with the :where keyword. Select returns a
629 list of objects, or nil if it can't find any. It's important to
630 remember that it always returns a list, so even if you are expecting
631 only one result, you should remember to extract it from the list you
637 (sql:select 'employee)
639 (sql:select 'company)
641 ;; employees named Lenin
642 (sql:select 'employee :where [= [slot-value 'employee 'last-name]
645 (sql:select 'company :where [= [slot-value 'company 'name]
648 ;; Employees of Widget's Inc.
649 (sql:select 'employee
650 :where [and [= [slot-value 'employee 'companyid]
651 [slot-value 'company 'companyid]]
652 [= [slot-value 'company 'name]
655 ;; Same thing, except that we are using the employee
656 ;; relation in the company view class to do the join for us,
657 ;; saving us the work of writing out the SQL!
658 (company-employees company1)
660 ;; President of Widgets Inc.
663 ;; Manager of Josef Stalin
664 (employee-manager employee2)
669 <sect1 id="usql-del">
670 <title>Deleting Objects</title>
673 Now that we know how to create objects in our database, manipulate
674 them and query them (including using our predefined relations to save
675 us the trouble writing alot of SQL) we should learn how to clean up
676 after ourself. It's quite simple really. The function
677 DELETE-INSTANCE-RECORDS will remove an object from the database.
678 However, when we remove an object we are responsible for making sure
679 that the database is left in a correct state.
683 For example, if we remove a company record, we need to either remove
684 all of it's employees or we need to move them to another company.
685 Likewise if we remove an employee, we should make sure to update any
686 other employees who had them as a manager.
691 <sect1 id="usql-concl">
692 <title>Conclusion</title>
695 There are alot more nooks and crannies to USQL, some of which are
696 covered n the Xanalys documents we refered to earlier, some are not.
697 The best documentation at this time is still the source code for USQL
698 itself and the inline documentation for it's various function.