+<?xml version='1.0' ?>
+<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN"
+ "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd" [
+<!ENTITY % myents SYSTEM "entities.inc">
+%myents;
+]>
+
+<chapter id="usql">
+ <title>UncommonSQL</title>
+
+ <sect1 id="usql-intro">
+ <title>Introduction</title>
+
+ <para>
+ The goal of this tutorial is to guide a new developer thru the
+ process of creating a set of USQL classes providing a
+ Object-Oriented interface to persistent data stored in an SQL
+ database. We will assume that the reader is familiar with how
+ SQL works, how relations (tables) should be structured, and has
+ created at least one SQL application previously. We will also
+ assume a minor level of experience with Common Lisp.
+ </para>
+
+ <para>
+ UncommonSQL (USQL) provides two different interfaces to SQL
+ databases, a Functional interface, and an Object-Oriented
+ interface. The Functional interface consists of a special
+ syntax for embedded SQL expressions in Lisp, and provides lisp
+ functions for SQL operations like SELECT and UPDATE. The OO
+ interface provides a way for mapping Common Lisp Objects System
+ (CLOS) objects into databases and includes functions for
+ inserting new objects, querying objects, and removing objects.
+ Most applications will use a combination of the two.
+ </para>
+
+ <para>
+ USQL is based on the CommonSQL package from Xanalys, so the
+ documentation that Xanalys makes available online is useful for
+ USQL as well. It is suggested that developers new to USQL check
+ their documentation out, as any differences between CommonSQL
+ and USQL are minor. Xanalys makes the following documents
+ available:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <ulink url="http://www.lispworks.com/reference/lw43/LWUG/html/lwuser-167.htm">
+ <citetitle>Xanalys LispWorks User Guide - The CommonSQL Package
+</citetitle>
+ </ulink>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <ulink url="http://www.lispworks.com/reference/lw43/LWRM/html/lwref-383.htm">
+ <citetitle>Xanalys LispWorks Reference Manual - The SQL Package</citetitle>
+ </ulink>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <ulink url="http://www.ravenbrook.com/doc/2002/09/13/common-sql/">
+ <citetitle>CommonSQL Tutorial by Nick Levine</citetitle>
+ </ulink>
+ </para>
+ </listitem>
+ </itemizedlist>
+ </sect1>
+
+ <sect1>
+ <title>Data Modeling with UncommonSQL</title>
+
+ <para>
+ Before we can create, query and manipulate USQL objects, we need to
+ define our data model as noted by Philip Greenspun
+ <footnote>
+ <para>
+ <ulink
+ url="http://www.arsdigita.com/books/sql/data-modeling.html">
+ <citetitle>Philip Greenspun's "SQL For Web Nerds" - Data Modeling
+</citetitle>
+ </ulink>
+ </para>
+ </footnote>
+ </para>
+
+ <para>
+ When data modeling, you are telling the RDBMS the following:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>What elements of the data you will store</para>
+ </listitem>
+ <listitem>
+ <para>How large each element can be</para>
+ </listitem>
+ <listitem>
+ <para>What kind of information each element can contain</para>
+ </listitem>
+ <listitem>
+ <para>What elements may be left blank</para>
+ </listitem>
+ <listitem>
+ <para>Which elements are constrained to a fixed range</para>
+ </listitem>
+ <listitem>
+ <para>Whether and how various tables are to be linked</para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ With SQL database one would do this by defining a set of
+ relations, or tables, followed by a set of queries for joining
+ the tables together in order to construct complex records.
+ However, with USQL we do this by defining a set of CLOS classes,
+ specifying how they will be turned into tables, and how they can
+ be joined to one another via relations between their attributes.
+ The SQL tables, as well as the queries for joining them together
+ are created for us automatically, saving us from dealing with
+ some of the tedium of SQL.
+ </para>
+
+ <para>
+ Let us start with a simple example of two SQL tables, and the
+ relations between them.
+ </para>
+
+<programlisting>
+CREATE TABLE EMPLOYEE (
+ emplid NOT NULL number(38),
+ first_name NOT NULL varchar2(30),
+ last_name NOT NULL varchar2(30),
+ emall varchar2(100),
+ companyid NOT NULL number(38),
+ managerid number(38)
+)
+
+CREATE TABLE COMPANY (
+ companyid NOT NULL number(38),
+ name NOT NULL varchar2(100),
+ presidentid NOT NULL number(38)
+)
+</programlisting>
+
+<para>
+This is of course the canonical SQL tutorial example, "The Org Chart".
+</para>
+
+<para>
+In USQL, we would have two "view classes" (a fancy word for a class
+mapped into a database). They would be defined as follows:
+</para>
+
+<programlisting>
+(sql:def-view-class employee ()
+ ((emplid
+ :db-kind :key
+ :db-constraints :not-null
+ :type integer
+ :initarg :emplid)
+ (first-name
+ :accessor first-name
+ :type (string 30)
+ :initarg :first-name)
+ (last-name
+ :accessor last-name
+ :type (string 30)
+ :initarg :last-name)
+ (email
+ :accessor employee-email
+ :type (string 100)
+ :nulls-ok t
+ :initarg :email)
+ (companyid
+ :type integer)
+ (managerid
+ :type integer
+ :nulls-ok t))
+ (:base-table employee))
+
+(sql:def-view-class company ()
+ ((companyid
+ :db-type :key
+ :db-constraints :not-null
+ :type integer
+ :initarg :companyid)
+ (name
+ :type (string 100)
+ :initarg :name)
+ (presidentid
+ :type integer))
+ (:base-table company))
+</programlisting>
+
+<para>
+ The DEF-VIEW-CLASS macro is just like the normal CLOS DEFCLASS
+ macro, except that it handles several slot options that DEFCLASS
+ doesn't. These slot options have to do with the mapping of the slot
+ into the database. We only use a few of the slot options in the
+ above example, but there are several others.
+</para>
+
+<itemizedlist>
+
+ <listitem><para>
+ :column -- The name of the SQL column this slot is stored in.
+ Defaults to the slot name. If the slot name is not a valid SQL
+ identifier, it is escaped, so foo-bar becomes foo_bar.
+ </para></listitem>
+
+ <listitem><para>
+ :db-kind -- The kind of DB mapping which is performed for this
+ slot. :BASE indicates the slot maps to an ordinary column of the
+ DB view. :KEY indicates that this slot corresponds to part of the
+ unique keys for this view, :JOIN indicates a join slot
+ representing a relation to another view and :virtual indicates
+ that this slot is an ordinary CLOS slot. Defaults to :base.
+</para></listitem>
+
+ <listitem><para>
+ :db-reader -- If a string, then when reading values from the DB, the
+ string will be used for a format string, with the only value being
+ the value from the database. The resulting string will be used as
+ the slot value. If a function then it will take one argument, the
+ value from the database, and return the value that should be put
+ into the slot.
+</para></listitem>
+
+ <listitem><para>
+ :db-writer -- If a string, then when reading values from the slot
+ for the DB, the string will be used for a format string, with the
+ only value being the value of the slot. The resulting string will
+ be used as the column value in the DB. If a function then it will
+ take one argument, the value of the slot, and return the value
+ that should be put into the database.
+</para></listitem>
+
+ <listitem><para>
+ :db-type -- A string which will be used as the type specifier for
+ this slots column definition in the database.
+</para></listitem>
+
+ <listitem><para>
+ :nulls-ok -- If t, all sql NULL values retrieved from the database
+ become nil; if nil, all NULL values retrieved are converted by
+ DATABASE-NULL-VALUE
+</para></listitem>
+
+ <listitem><para>
+ :db-info -- A join specification.
+</para></listitem>
+</itemizedlist>
+
+<para>
+ In our example each table as a primary key attribute, which is
+ required to be unique. We indicate that a slot is part of the
+ primary key (USQL supports multi-field primary keys) by specifying
+ the :db-kind :key slot option.
+</para>
+
+<para>
+ The SQL type of a slot when it is mapped into the database is
+ determined by the :type slot option. The argument for the :type
+ option is a Common Lisp datatype. The USQL framework will determine
+ the appropriate mapping depending on the database system the table
+ is being created in. If we really wanted to determine what SQL type
+ was used for a slot, we could specify a :db-type option like
+ "NUMBER(38)" and we would be guaranteed that the slot would be
+ stored n the DB as a NUMBER(38). This is not recomended because it
+ could makes your view class unportable across database systems.
+</para>
+
+<para>
+ DEF-VIEW-CLASS also supports some class options, like :base-table.
+ The :base-table option specifies what the table name for the view
+ class will be when it is mapped into the database.
+</para>
+ </sect1>
+
+<sect1 id="usql-rel">
+<title>Class Relations</title>
+
+<para>
+In an SQL only application, the EMPLOYEE and COMPANY tables can be
+queried to determine things like, "Who is Vladamir's manager?", What
+company does Josef work for?", and "What employees work for Widgets
+Inc.". This is done by joining tables with an SQL query.
+</para>
+
+<para>
+Who works for Widgets Inc.?
+</para>
+
+<programlisting>
+SELECT first_name, last_name FROM employee, company
+ WHERE employee.companyid = company.companyid
+ AND company.company_name = "Widgets Inc."
+
+Who is Vladamir's manager
+
+SELECT managerid FROM employee
+ WHERE employee.first_name = "Vladamir"
+ AND employee.last_name = "Lenin"
+
+What company does Josef work for?
+
+SELECT company_name FROM company, employee
+ WHERE employee.first_name = "Josef"
+ AND employee.last-name = "Stalin"
+ AND employee.companyid = company.companyid
+</programlisting>
+
+<para>
+With USQL however we do not need to write out such queries because our
+view classes can maintain the relations between employees and
+companies, and employees to their managers for us. We can then access
+these relations like we would any other attribute of an employee or
+company object. In order to do this we define some join slots for our
+view classes.
+</para>
+
+<para>
+What company does an employee work for? If we add the following slot
+definition to the employee class we can then ask for it's COMPANY slot
+and get the appropriate result.
+</para>
+
+<programlisting>
+ ;; In the employee slot list
+ (company
+ :accessor employee-company
+ :db-kind :join
+ :db-info (:join-class company
+ :home-key companyid
+ :foreign-key companyid
+ :set nil))
+</programlisting>
+
+<para>
+Who are the employees of a given company? And who is the president of
+it? We add the following slot definition to the company view class and
+we can then ask for it's EMPLOYEES slot and get the right result.
+</para>
+
+<programlisting>
+ ;; In the company slot list
+ (employees
+ :reader company-employees
+ :db-kind :join
+ :db-info (:join-class employee
+ :home-key companyid
+ :foreign-key companyid
+ :set t))
+
+ (president
+ :reader president
+ :db-kind :join
+ :db-info (:join-class employee
+ :home-key presidentid
+ :foreign-key emplid
+ :set nil))
+</programlisting>
+
+<para>
+And lastly, to define the relation between an employee and their
+manager.
+</para>
+
+<programlisting>
+ ;; In the employee slot list
+ (manager
+ :accessor employee-manager
+ :db-kind :join
+ :db-info (:join-class employee
+ :home-key managerid
+ :foreign-key emplid
+ :set nil))
+</programlisting>
+
+<para>
+USQL join slots can represent one-to-one, one-to-many, and
+many-to-many relations. Above we only have one-to-one and one-to-many
+relations, later we will explain how to model many-to-many relations.
+First, let's go over the slot definitions and the available options.
+</para>
+
+<para>
+In order for a slot to be a join, we must specify that it's :db-kind
+:join, as opposed to :base or :key. Once we do that, we still need to
+tell USQL how to create the join statements for the relation. This is
+what the :db-info option does. It is a list of keywords and values.
+The available keywords are:
+</para>
+
+<itemizedlist>
+ <listitem><para>
+ :join-class -- The view class to which we want to join. It can be
+ another view class, or the same view class as our object.
+ </para></listitem>
+
+ <listitem><para>
+ :home-key -- The slot(s) in the immediate object whose value will
+ be compared to the foreign-key slot(s) in the join-class in order
+ to join the two tables. It can be a single slot-name, or it can
+ be a list of slot names.
+</para></listitem>
+
+ <listitem><para>
+ :foreign-key -- The slot(s) in the join-class which will be compared
+ to the value(s) of the home-key.
+</para></listitem>
+
+ <listitem><para>
+ :set -- A boolean which if false, indicates that this is a
+ one-to-one relation, only one object will be returned. If true,
+ than this is a one-to-many relation, a list of objects will be
+ returned when we ask for this slots value.
+</para></listitem>
+</itemizedlist>
+
+<para>
+There are other :join-info options available in USQL, but we will save
+those till we get to the many-to-many relation examples.
+</para>
+
+</sect1>
+
+<sect1 id="usql-creat">
+<title>Object Creation</title>
+
+<para>
+Now that we have our model laid out, we should create some object.
+Let us assume that we have a database connect set up already. We
+first need to create our tables in the database:
+</para>
+
+<para>
+Note: the file usql-tutorial.lisp contains view class definitions
+which you can load into your list at this point in order to play along
+at home.
+</para>
+
+<programlisting>
+(sql:create-view-from-class 'employee)
+(sql:create-view-from-class 'company)
+</programlisting>
+
+<para>
+Then we will create our objects. We create them just like you would
+any other CLOS object:
+</para>
+
+<programlisting>
+(defvar employee1 (make-instance 'employee
+ :emplid 1
+ :first-name "Vladamir"
+ :last-name "Lenin"
+ :email "lenin@soviet.org"))
+
+(defvar company1 (make-instance 'company
+ :companyid 1
+ :name "Widgets Inc."))
+
+
+(defvar employee2 (make-instance 'employee
+ :emplid 2
+ :first-name "Josef"
+ :last-name "Stalin"
+ :email "stalin@soviet.org"))
+</programlisting>
+
+<para>
+In order to insert an objects into the database we use the
+UPDATE-RECORDS-FROM-INSTANCE function as follows:
+</para>
+
+<programlisting>
+(sql:update-records-from-instance employee1)
+(sql:update-records-from-instance employee2)
+(sql:update-records-from-instance company1)
+</programlisting>
+
+<para>
+Now we can set up some of the relations between employees and
+companies, and their managers. The ADD-TO-RELATION method provides us
+with an easy way of doing that. It will update both the relation
+slot, as well as the home-key and foreign-key slots in both objects in
+the relation.
+</para>
+
+<programlisting>
+;; Lenin manages Stalin (for now)
+(sql:add-to-relation employee2 'manager employee1)
+
+;; Lenin and Stalin both work for Widgets Inc.
+(sql:add-to-relation company1 'employees employee1)
+(sql:add-to-relation company1 'employees employee2)
+
+;; Lenin is president of Widgets Inc.
+(sql:add-to-relation company1 'president employee1)
+</programlisting>
+
+<para>
+After you make any changes to an object, you have to specifically tell
+USQL to update the SQL database. The UPDATE-RECORDS-FROM-INSTANCE
+method will write all of the changes you have made to the object into
+the database.
+</para>
+
+<para>
+Since USQL objects re just normal CLOS objects, we can manipulate
+their slots just like any other object. For instance, let's say that
+Lenin changes his email because he was getting too much SPAM fro the
+German Socialists.
+</para>
+
+<programlisting>
+;; Print Lenin's current email address, change it and save it to the
+;; database. Get a new object representing Lenin from the database
+;; and print the email
+
+;; This lets us use the functional USQL interface with [] syntax
+(sql:locally-enable-sql-reader-syntax)
+
+(format t "The email address of ~A ~A is ~A"
+ (first-name employee1)
+ (last-name employee1)
+ (employee-email employee1))
+
+(setf (employee-email employee1) "lenin-nospam@soviets.org")
+
+;; Update the database
+(sql:update-records-from-instance employee1)
+
+(let ((new-lenin (car (sql:select 'employee
+ :where [= [slot-value 'employee 'emplid] 1]))))
+ (format t "His new email is ~A"
+ (employee-email new-lenin)))
+</programlisting>
+
+<para>
+Everything except for the last LET expression is already familiar to
+us by now. To understand the call to SQL:SELECT we need to discuss
+the Functional SQL interface and it's integration with the Object
+Oriented interface of USQL.
+</para>
+
+</sect1>
+
+<sect1 id="usql-find">
+<title>Finding Objects</title>
+
+<para>
+Now that we have our objects in the database, how do we get them out
+when we need to work with them? USQL provides a Functional interface
+to SQL, which consists of a special Lisp reader macro and some
+functions. The special syntax allows us to embed SQL in lisp
+expressions, and lisp expressions in SQL, with ease.
+</para>
+
+<para>
+Once we have turned on the syntax with the expression:
+</para>
+
+<programlisting>
+(sql:locally-enable-sql-reader-syntax)
+</programlisting>
+
+<para>
+we can start entering fragments of SQL into our lisp reader. We will
+get back objects which represent the lisp expressions. These objects
+will later be compiled into SQL expressions that are optimized for the
+database backed we are connected to. This means that we have a
+database independent SQL syntax. Here are some examples:
+</para>
+
+<programlisting>
+;; an attribute or table name
+[foo] => #<MAISQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
+
+;; a attribute identifier with table qualifier
+[foo bar] => #<MAISQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
+
+;; a attribute identifier with table qualifier
+[= "Lenin" [first_name]] =>
+ #<MAISQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
+
+[< [emplid] 3] =>
+ #<MAISQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
+
+[and [< [emplid] 2] [= [first_name] "Lenin"]] =>
+ #<MAISQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND
+ (FIRST_NAME = 'Lenin'))>
+
+
+;; If we want to reference a slot in an object we can us the
+;; SLOT-VALUE sql extension
+[= [slot-value 'employee 'emplid] 1] =>
+ #<MAISQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
+
+[= [slot-value 'employee 'emplid]
+ [slot-value 'company 'presidentid]] =>
+ #<MAISQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
+</programlisting>
+
+<para>
+The SLOT-VALUE operator is important because it let's us query objects
+in a way that is robust to any changes in the object->table mapping,
+like column name changes, or table name changes. So when you are
+querying objects, be sure to use the SLOT-VALUE SQL extension.
+</para>
+
+<para>
+Since we can now formulate SQL relational expression which can be used
+as qualifiers, like we put after the WHERE keyword in SQL statements,
+we can start querying our objects. USQL provides a function SELECT
+which can return use complete objects from the database which conform
+to a qualifier, can be sorted, and various other SQL operations.
+</para>
+
+<para>
+The first argument to SELECT is a class name. it also has a set of
+keyword arguments which are covered in the documentation. For now we
+will concern ourselves only with the :where keyword. Select returns a
+list of objects, or nil if it can't find any. It's important to
+remember that it always returns a list, so even if you are expecting
+only one result, you should remember to extract it from the list you
+get from SELECT.
+</para>
+
+<programlisting>
+;; all employees
+(sql:select 'employee)
+;; all companies
+(sql:select 'company)
+
+;; employees named Lenin
+(sql:select 'employee :where [= [slot-value 'employee 'last-name]
+ "Lenin"])
+
+(sql:select 'company :where [= [slot-value 'company 'name]
+ "Widgets Inc."])
+
+;; Employees of Widget's Inc.
+(sql:select 'employee
+ :where [and [= [slot-value 'employee 'companyid]
+ [slot-value 'company 'companyid]]
+ [= [slot-value 'company 'name]
+ "Widgets Inc."]])
+
+;; Same thing, except that we are using the employee
+;; relation in the company view class to do the join for us,
+;; saving us the work of writing out the SQL!
+(company-employees company1)
+
+;; President of Widgets Inc.
+(president company1)
+
+;; Manager of Josef Stalin
+(employee-manager employee2)
+</programlisting>
+
+</sect1>
+
+<sect1 id="usql-del">
+<title>Deleting Objects</title>
+
+<para>
+Now that we know how to create objects in our database, manipulate
+them and query them (including using our predefined relations to save
+us the trouble writing alot of SQL) we should learn how to clean up
+after ourself. It's quite simple really. The function
+DELETE-INSTANCE-RECORDS will remove an object from the database.
+However, when we remove an object we are responsible for making sure
+that the database is left in a correct state.
+</para>
+
+<para>
+For example, if we remove a company record, we need to either remove
+all of it's employees or we need to move them to another company.
+Likewise if we remove an employee, we should make sure to update any
+other employees who had them as a manager.
+</para>
+
+</sect1>
+
+<sect1 id="usql-concl">
+<title>Conclusion</title>
+
+<para>
+There are alot more nooks and crannies to USQL, some of which are
+covered n the Xanalys documents we refered to earlier, some are not.
+The best documentation at this time is still the source code for USQL
+itself and the inline documentation for it's various function.
+</para>
+
+</sect1>
+
+</chapter>
+
+
+