r8821: integrate usql support

[clsql.git] / usql / doc / usql-tutorial.txt

INTRODUCTION

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.

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.

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:

Xanalys LispWorks User Guide  - The CommonSQL Package
http://www.lispworks.com/reference/lw43/LWUG/html/lwuser-167.htm

Xanalys LispWorks Reference Manual -- The SQL Package
http://www.lispworks.com/reference/lw43/LWRM/html/lwref-383.htm

CommonSQL Tutorial by Nick Levine
http://www.ravenbrook.com/doc/2002/09/13/common-sql/


DATA MODELING WITH UNCOMMONSQL

Before we can create, query and manipulate USQL objects, we need to
define our data model. To borrow from Philip Greenspun[1]:

When data modeling, you are telling the RDBMS the following: 

    * What elements of the data you will store 
    * How large each element can be 
    * What kind of information each element can contain 
    * What elements may be left blank 
    * Which elements are constrained to a fixed range 
    * Whether and how various tables are to be linked 

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.

Let us start with a simple example of two SQL tables, and the
relations between them.

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)
)

This is of course the canonical SQL tutorial example, "The Org Chart".

In USQL, we would have two "view classes" (a fancy word for a class
mapped into a database).  They would be defined as follows:

(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))


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.

    :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.

    :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.

   :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.

   :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.

   :db-type -- A string which will be used as the type specifier for
    this slots column definition in the database.

   :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

   :db-info -- A join specification.

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.  

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.

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.


CLASS RELATIONS

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.

Who works for Widgets Inc.?

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

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.

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.

    ;; 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))

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.

      ;; 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))

And lastly, to define the relation between an employee and their
manager.

        ;; 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))

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.

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:

    :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.

    :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.

    :foreign-key -- The slot(s) in the join-class which will be compared
    to the value(s) of the home-key.

    :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.

There are other :join-info options available in USQL, but we will save
those till we get to the many-to-many relation examples.


OBJECT CREATION

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:

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.

(sql:create-view-from-class 'employee)
(sql:create-view-from-class 'company)

Then we will create our objects.  We create them just like you would
any other CLOS object:

(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"))

In order to insert an objects into the database we use the
UPDATE-RECORDS-FROM-INSTANCE function as follows:

(sql:update-records-from-instance employee1)
(sql:update-records-from-instance employee2)
(sql:update-records-from-instance company1)

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.

;; 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)

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.

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.

;; 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)))

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.


FINDING OBJECTS

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.

Once we have turned on the syntax with the expression:

(sql:locally-enable-sql-reader-syntax)

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:

;; 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)>

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.

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.

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.

;; 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)


DELETING OBJECTS

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.

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.


CONCLUSION

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.



[1] Philip Greenspun's "SQL For Web Nerds" - Data Modeling
    http://www.arsdigita.com/books/sql/data-modeling.html