06 Apr 2004 Kevin Rosenberg (kevin@rosenberg.net)
* With for Marcus Pearce's excellent work, I've merged
- his clsql-usql port into the code base. Added the USQL
- documentation to the XML documentation.
- * The CLSQL and CLSQL-USQL high-level interfaces can now
- both be loaded and imported into the same package.
+ his clsql-usql port into clsql.
02 Apr 2004 Kevin Rosenberg (kevin@rosenberg.net)
* Integrate patch from Marcus Pearce <ek735@soi.city.ac.uk>
-CLSQL now supports SBCL, OpenMCL, and SCL.
-
-CLSQL now supports locking on connection pools.
-
-
+CLSQL now supports the CommonSQL-API with the merge of the orphaned
+UncommonSQL package.
</author>
<author>
<othername>onShore Development, Inc.</othername>
+ <affiliation><jobtitle>Author of UncommonSQL Package</jobtitle></affiliation>
</author>
</authorgroup>
<printhistory>
--- /dev/null
+
+(in-package :cl-user)
+
+;; You must set these variables to appropriate values.
+(defvar *tutorial-database-type* nil
+ "Possible values are :postgresql,:postgresql-socket :mysql or :sqlite")
+(defvar *tutorial-database-name* ""
+ "The name of the database we will work in.")
+(defvar *tutorial-database-user* ""
+ "The name of the database user we will work as.")
+(defvar *tutorial-database-server* ""
+ "The name of the database server if required")
+(defvar *tutorial-database-password* ""
+ "The password if required")
+
+(sql:def-view-class employee ()
+ ((emplid
+ :db-kind :key
+ :db-constraints :not-null
+ :nulls-ok nil
+ :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)
+ (company
+ :accessor employee-company
+ :db-kind :join
+ :db-info (:join-class company
+ :home-key companyid
+ :foreign-key companyid
+ :set nil))
+ (managerid
+ :type integer
+ :nulls-ok t)
+ (manager
+ :accessor employee-manager
+ :db-kind :join
+ :db-info (:join-class employee
+ :home-key managerid
+ :foreign-key emplid
+ :set nil)))
+ (: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)
+ (president
+ :reader president
+ :db-kind :join
+ :db-info (:join-class employee
+ :home-key presidentid
+ :foreign-key emplid
+ :set nil))
+ (employees
+ :reader company-employees
+ :db-kind :join
+ :db-info (:join-class employee
+ :home-key companyid
+ :foreign-key companyid
+ :set t)))
+ (:base-table company))
+
+;; Connect to the database (see the CLSQL documentation for vendor
+;; specific connection specs).
+(sql:connect `(,*tutorial-database-server*
+ ,*tutorial-database-name*
+ ,*tutorial-database-user*
+ ,*tutorial-database-password*)
+ :database-type *tutorial-database-type*)
+
+;; Record the sql going out, helps us learn what is going
+;; on behind the scenes
+(sql:start-sql-recording)
+
+;; Create the tables for our view classes
+;; First we drop them, ignoring any errors
+(ignore-errors
+ (sql:drop-view-from-class 'employee)
+ (sql:drop-view-from-class 'company))
+
+(sql:create-view-from-class 'employee)
+(sql:create-view-from-class 'company)
+
+
+;; Create some instances of our view classes
+(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"))
+
+;; 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)
+
+(sql:update-records-from-instance employee1)
+(sql:update-records-from-instance employee2)
+(sql:update-records-from-instance company1)
+
+;; lets us use the functional
+;; sql interface
+(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)))
+
+
+;; Some queries
+
+;; 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)
<xi:include href="bookinfo.xml" xmlns:xi="http://www.w3.org/2001/XInclude" />
<xi:include href="preface.xml" xmlns:xi="http://www.w3.org/2001/XInclude" />
<xi:include href="intro.xml" xmlns:xi="http://www.w3.org/2001/XInclude" />
- <xi:include href="usql.xml" xmlns:xi="http://www.w3.org/2001/XInclude" />
+ <xi:include href="csql.xml" xmlns:xi="http://www.w3.org/2001/XInclude" />
<xi:include href="ref_clsql.xml" xmlns:xi="http://www.w3.org/2001/XInclude" />
<xi:include href="ref_clsql_sys.xml" xmlns:xi="http://www.w3.org/2001/XInclude" />
<xi:include href="appendix.xml" xmlns:xi="http://www.w3.org/2001/XInclude" />
--- /dev/null
+<?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="csql">
+ <title>&commonsql; Tutorial</title>
+ <subtitle>Based on the &usql; Tutorial</subtitle>
+
+ <sect1 id="csql-intro">
+ <title>Introduction</title>
+
+ <para>
+ The goal of this tutorial is to guide a new developer thru the
+ process of creating a set of &clsql; 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>
+ &clsql; 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 <symbol>SELECT</symbol> and
+ <symbol>UPDATE</symbol>. The object-oriented 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>
+ &clsql; is based on the CommonSQL package from Xanalys, so the
+ documentation that Xanalys makes available online is useful for
+ &clsql; as well. It is suggested that developers new to &clsql; read
+ their documentation as well, as any differences between CommonSQL
+ and &clsql; 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 &lw; 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 &lw; 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 &clsql;</title>
+
+ <para>
+ Before we can create, query and manipulate &clsql; 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 relational database
+ management system (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 &clsql; 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 &clsql;, we would have two "view classes" (a fancy word for a class
+mapped into a database). They would be defined as follows:
+</para>
+
+<programlisting>
+(clsql: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))
+
+(clsql: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 <function>DEF-VIEW-CLASS</function> macro is just like the
+ normal CLOS <function>DEFCLASS</function> macro, except that it
+ handles several slot options that <function>DEFCLASS</function>
+ 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>
+ <symbol>:column</symbol> - 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>
+ <symbol>:db-kind</symbol> - The kind of database mapping which
+ is performed for this slot. <symbol>:base</symbol> indicates
+ the slot maps to an ordinary column of the database view.
+ <symbol>:key</symbol> indicates that this slot corresponds to
+ part of the unique keys for this view, <symbol>:join</symbol>
+ indicates a join slot representing a relation to another view
+ and :virtual indicates that this slot is an ordinary CLOS slot.
+ Defaults to <symbol>:base</symbol>. </para></listitem>
+
+ <listitem>
+ <para>
+ <symbol>:db-reader</symbol> - If a string, then when reading
+ values from the database, 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>
+ <symbol>:db-writer</symbol> - If a string, then when reading
+ values from the slot for the database, 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 database. 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>
+ <symbol>:db-type</symbol> - A string which will be used as the
+ type specifier for this slots column definition in the database.
+ </para></listitem>
+
+ <listitem>
+ <para>
+ <symbol>:nulls-ok</symbol> - If &t;, all &sql; &null; values
+ retrieved from the database become nil; if &nil;, all &null;
+ values retrieved are converted by
+ <function>DATABASE-NULL-VALUE</function>. </para></listitem>
+
+ <listitem>
+ <para>
+ <symbol>:db-info</symbol> - 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 (&clsql; supports multi-field primary keys) by specifying
+ the <symbol>:db-kind</symbol> key slot option.
+</para>
+
+<para>
+ The &sql; type of a slot when it is mapped into the database is
+ determined by the <symbol>:type</symbol> slot option. The argument
+ for the <symbol>:type</symbol> option is a Common Lisp datatype.
+ The &clsql; 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 <symbol>:db-type</symbol> option like
+ "NUMBER(38)" and we would be guaranteed that the slot would be
+ stored in the database as a NUMBER(38). This is not recomended
+ because it could makes your view class unportable across database
+ systems.
+</para>
+
+<para>
+ <function>DEF-VIEW-CLASS</function> also supports some class
+ options, like <symbol>:base-table</symbol>. The
+ <symbol>:base-table</symbol> option specifies what the table name
+ for the view class will be when it is mapped into the database.
+</para>
+ </sect1>
+
+<sect1 id="csql-rel">
+<title>Class Relations</title>
+
+<para>
+In an &sql; only application, the <symbol>EMPLOYEE</symbol> and
+<symbol>COMPANY</symbol> 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."
+</programlisting>
+
+<para>
+Who is Vladamir's manager?
+</para>
+
+<programlisting>
+SELECT managerid FROM employee
+ WHERE employee.first_name = "Vladamir"
+ AND employee.last_name = "Lenin"
+</programlisting>
+
+<para>
+What company does Josef work for?
+</para>
+
+<programlisting>
+SELECT company_name FROM company, employee
+ WHERE employee.first_name = "Josef"
+ AND employee.last-name = "Stalin"
+ AND employee.companyid = company.companyid
+</programlisting>
+
+<para>
+With &clsql; 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
+<symbol>COMPANY</symbol> 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 <symbol>EMPLOYEES</symbol> 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>
+&clsql; 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
+<symbol>:db-kind</symbol> <symbol>:join</symbol>, as opposed to
+<symbol>:base</symbol> or <symbol>:key</symbol>. Once we do that, we
+still need to tell &clsql; how to create the join statements for the
+relation. This is what the <symbol>:db-info</symbol> option does. It
+is a list of keywords and values. The available keywords are:
+</para>
+
+<itemizedlist>
+ <listitem>
+ <para>
+ <symbol>:join-class</symbol> - 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>
+ <symbol>:home-key</symbol> - 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>
+ <symbol>:foreign-key</symbol> - The slot(s) in the join-class
+ which will be compared to the value(s) of the home-key.
+ </para></listitem>
+
+ <listitem>
+ <para>
+ <symbol>:set</symbol> - 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 &clsql;, but we will
+save those till we get to the many-to-many relation examples.
+</para>
+
+</sect1>
+
+<sect1 id="csql-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 <filename>doc/clsql-tutorial.lisp</filename> contains
+view class definitions which you can load into your list at this point
+in order to play along at home.
+</para>
+
+<programlisting>
+(clsql:create-view-from-class 'employee)
+(clsql: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
+<function>UPDATE-RECORDS-FROM-INSTANCE</function> function as follows:
+</para>
+
+<programlisting>
+(clsql:update-records-from-instance employee1)
+(clsql:update-records-from-instance employee2)
+(clsql:update-records-from-instance company1)
+</programlisting>
+
+<para>
+Now we can set up some of the relations between employees and
+companies, and their managers. The
+<function>ADD-TO-RELATION</function> 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)
+(clsql:add-to-relation employee2 'manager employee1)
+
+;; Lenin and Stalin both work for Widgets Inc.
+(clsql:add-to-relation company1 'employees employee1)
+(clsql:add-to-relation company1 'employees employee2)
+
+;; Lenin is president of Widgets Inc.
+(clsql:add-to-relation company1 'president employee1)
+</programlisting>
+
+<para>
+ After you make any changes to an object, you have to specifically
+ tell &clsql; to update the &sql; database. The
+ <function>UPDATE-RECORDS-FROM-INSTANCE</function> method will write
+ all of the changes you have made to the object into the database.
+</para>
+
+<para>
+ Since &clsql; objects are 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
+ from 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 &clsql; interface with [] syntax
+(clsql: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
+(clsql:update-records-from-instance employee1)
+
+(let ((new-lenin (car (clsql: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 <function>LET</function> expression
+ is already familiar to us by now. To understand the call to
+ <function>CLSQL:SELECT</function> we need to discuss the
+ Functional &sql; interface and it's integration with the Object
+ Oriented interface of &clsql;.
+</para>
+
+</sect1>
+
+<sect1 id="csql-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? &clsql; 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>
+(clsql: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] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
+
+;; a attribute identifier with table qualifier
+[foo bar] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
+
+;; a attribute identifier with table qualifier
+[= "Lenin" [first_name]] =>
+ #<CLSQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
+
+[< [emplid] 3] =>
+ #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
+
+[and [< [emplid] 2] [= [first_name] "Lenin"]] =>
+ #<CLSQL-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] =>
+ #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
+
+[= [slot-value 'employee 'emplid]
+ [slot-value 'company 'presidentid]] =>
+ #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
+</programlisting>
+
+<para>
+ The <function>SLOT-VALUE</function> 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
+ <function>SLOT-VALUE</function> &sql; extension.
+</para>
+
+<para>
+ Since we can now formulate &sql; relational expression which can be
+ used as qualifiers, like we put after the <symbol>WHERE</symbol>
+ keyword in &sql; statements, we can start querying our objects.
+ &clsql; provides a function <symbol>SELECT</symbol> 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 <symbol>SELECT</symbol> 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
+ <symbol>SELECT</symbol>.
+</para>
+
+<programlisting>
+;; all employees
+(clsql:select 'employee)
+;; all companies
+(clsql:select 'company)
+
+;; employees named Lenin
+(clsql:select 'employee :where [= [slot-value 'employee 'last-name]
+ "Lenin"])
+
+(clsql:select 'company :where [= [slot-value 'company 'name]
+ "Widgets Inc."])
+
+;; Employees of Widget's Inc.
+(clsql: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="csql-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
+ <function>DELETE-INSTANCE-RECORDS</function> 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="csql-concl">
+<title>Conclusion</title>
+
+<para>
+ There are many nooks and crannies to &clsql;, some of which are
+ covered in the Xanalys documents we refered to earlier, some are
+ not. The best documentation at this time is still the source code
+ for &clsql; itself and the inline documentation for its various
+ functions.
+</para>
+
+</sect1>
+
+</chapter>
<!ENTITY maisql "<application><emphasis>MaiSQL</emphasis></application>">
<!ENTITY sql "<application>SQL</application>">
<!ENTITY usql "<application>UncommonSQL</application>">
+<!ENTITY commonsql "<application>CommonSQL</application>">
<!ENTITY mysql "<application>MySQL</application>">
<!ENTITY postgresql "<application>PostgreSQL</application>">
<!ENTITY sqlite "<application>SQLite</application>">
<sect1 id="purpose">
<title>Purpose</title>
<para>&clsql; is a Common Lisp interface to <glossterm
- linkend="gloss-sql">SQL</glossterm> databases. A number of Common
- Lisp implementations and SQL databases are supported. The general
- structure of &clsql; is based on the
- <application>CommonSQL</application>
- package by Xanalys.
+ linkend="gloss-sql">SQL</glossterm> databases. A number of Common
+ Lisp implementations and SQL databases are supported. The general
+ structure of &clsql; is based on the &commonsql; package by
+ Xanalys.
</para>
</sect1>
<sect1 id="history">
<title>History</title>
<para>
- &clsql; is written by Kevin M. Rosenberg in 2001 and was based
- substantially on Pierre R. Mai's excellent &maisql; package. In
- April 2004, Marcus Pearce ported the UncommonSQL to &clsql;
- which provides a CommonSQL-compatible API for &clsql;. The main
- changes from &maisql; are:
+ The &clsql; project was started by Kevin M. Rosenberg in 2001 to
+ support SQL access on multiple Common Lisp implementations using
+ the &uffi; library. The initial code was based substantially on
+ Pierre R. Mai's excellent &maisql; package. In late 2003, the
+ &usql; library was orphaned by its author, onShore Development,
+ Inc with the cessation of further development. In April 2004,
+ Marcus Pearce ported the &usql; library to &clsql;. The &usql;
+ library provides a &commonsql;-compatible API for &clsql;.
+ </para>
+
+ <para>The main changes from &maisql; and &usql; are:
<itemizedlist>
<listitem>
- <para>port from the &cmucl; FFI to &uffi;.</para>
+ <para>Port from the &cmucl; FFI to &uffi; which provide
+ compatibility with the major Common Lisp
+ implementations.</para>
</listitem>
<listitem>
<para>Optimized loading of integer and floating-point fields.</para>
</listitem>
<listitem>
- <para>new &acl; ODBC interface back-end.</para>
+ <para>Additional database backends: &acl; ODBC and &sqlite;.</para>
</listitem>
<listitem>
- <para>compatibility layer for &cmucl; specific code.</para>
+ <para>A compatibility layer for &cmucl; specific code.</para>
</listitem>
<listitem>
- <para>much improved robustness for the &mysql; back-end
+ <para>Much improved robustness for the &mysql; back-end
along with version 4 client library support.</para>
</listitem>
<listitem>
- <para>improved system loading.</para>
+ <para>Improved library loading and installation documentation.</para>
</listitem>
<listitem>
- <para>improved packages and symbol export.</para>
+ <para>Improved packages and symbol export.</para>
</listitem>
<listitem>
- <para>transaction support.</para>
+ <para>Pooled connections.</para>
</listitem>
<listitem>
- <para>UncommonSQL support.</para>
+ <para>Integrated transaction support for the classic
+ &maisql; iteration macros.</para>
</listitem>
</itemizedlist>
</para>
+++ /dev/null
-
-(in-package :cl-user)
-
-;; You must set these variables to appropriate values.
-(defvar *tutorial-database-type* nil
- "Possible values are :postgresql,:postgresql-socket :mysql or :sqlite")
-(defvar *tutorial-database-name* ""
- "The name of the database we will work in.")
-(defvar *tutorial-database-user* ""
- "The name of the database user we will work as.")
-(defvar *tutorial-database-server* ""
- "The name of the database server if required")
-(defvar *tutorial-database-password* ""
- "The password if required")
-
-(sql:def-view-class employee ()
- ((emplid
- :db-kind :key
- :db-constraints :not-null
- :nulls-ok nil
- :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)
- (company
- :accessor employee-company
- :db-kind :join
- :db-info (:join-class company
- :home-key companyid
- :foreign-key companyid
- :set nil))
- (managerid
- :type integer
- :nulls-ok t)
- (manager
- :accessor employee-manager
- :db-kind :join
- :db-info (:join-class employee
- :home-key managerid
- :foreign-key emplid
- :set nil)))
- (: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)
- (president
- :reader president
- :db-kind :join
- :db-info (:join-class employee
- :home-key presidentid
- :foreign-key emplid
- :set nil))
- (employees
- :reader company-employees
- :db-kind :join
- :db-info (:join-class employee
- :home-key companyid
- :foreign-key companyid
- :set t)))
- (:base-table company))
-
-;; Connect to the database (see the CLSQL documentation for vendor
-;; specific connection specs).
-(sql:connect `(,*tutorial-database-server*
- ,*tutorial-database-name*
- ,*tutorial-database-user*
- ,*tutorial-database-password*)
- :database-type *tutorial-database-type*)
-
-;; Record the sql going out, helps us learn what is going
-;; on behind the scenes
-(sql:start-sql-recording)
-
-;; Create the tables for our view classes
-;; First we drop them, ignoring any errors
-(ignore-errors
- (sql:drop-view-from-class 'employee)
- (sql:drop-view-from-class 'company))
-
-(sql:create-view-from-class 'employee)
-(sql:create-view-from-class 'company)
-
-
-;; Create some instances of our view classes
-(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"))
-
-;; 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)
-
-(sql:update-records-from-instance employee1)
-(sql:update-records-from-instance employee2)
-(sql:update-records-from-instance company1)
-
-;; lets us use the functional
-;; sql interface
-(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)))
-
-
-;; Some queries
-
-;; 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)
+++ /dev/null
-<?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>
- &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 <symbol>SELECT</symbol> and
- <symbol>UPDATE</symbol>. The object-oriented 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>
-(clsql-usql: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))
-
-(clsql-usql: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 <function>DEF-VIEW-CLASS</function> macro is just like the
- normal CLOS <function>DEFCLASS</function> macro, except that it
- handles several slot options that <function>DEFCLASS</function>
- 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>
- <symbol>:column</symbol> - 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>
- <symbol>:db-kind</symbol> - The kind of database mapping which
- is performed for this slot. <symbol>:base</symbol> indicates
- the slot maps to an ordinary column of the database view.
- <symbol>:key</symbol> indicates that this slot corresponds to
- part of the unique keys for this view, <symbol>:join</symbol>
- indicates a join slot representing a relation to another view
- and :virtual indicates that this slot is an ordinary CLOS slot.
- Defaults to <symbol>:base</symbol>. </para></listitem>
-
- <listitem>
- <para>
- <symbol>:db-reader</symbol> - If a string, then when reading
- values from the database, 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>
- <symbol>:db-writer</symbol> - If a string, then when reading
- values from the slot for the database, 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 database. 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>
- <symbol>:db-type</symbol> - A string which will be used as the
- type specifier for this slots column definition in the database.
- </para></listitem>
-
- <listitem>
- <para>
- <symbol>:nulls-ok</symbol> - If &t;, all &sql; &null; values
- retrieved from the database become nil; if &nil;, all &null;
- values retrieved are converted by
- <function>DATABASE-NULL-VALUE</function>. </para></listitem>
-
- <listitem>
- <para>
- <symbol>:db-info</symbol> - 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 <symbol>:db-kind</symbol> key slot option.
-</para>
-
-<para>
- The &sql; type of a slot when it is mapped into the database is
- determined by the <symbol>:type</symbol> slot option. The argument
- for the <symbol>:type</symbol> 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 <symbol>:db-type</symbol> option like
- "NUMBER(38)" and we would be guaranteed that the slot would be
- stored in the database as a NUMBER(38). This is not recomended
- because it could makes your view class unportable across database
- systems.
-</para>
-
-<para>
- <function>DEF-VIEW-CLASS</function> also supports some class
- options, like <symbol>:base-table</symbol>. The
- <symbol>:base-table</symbol> 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 <symbol>EMPLOYEE</symbol> and
-<symbol>COMPANY</symbol> 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."
-</programlisting>
-
-<para>
-Who is Vladamir's manager?
-</para>
-
-<programlisting>
-SELECT managerid FROM employee
- WHERE employee.first_name = "Vladamir"
- AND employee.last_name = "Lenin"
-</programlisting>
-
-<para>
-What company does Josef work for?
-</para>
-
-<programlisting>
-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
-<symbol>COMPANY</symbol> 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 <symbol>EMPLOYEES</symbol> 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
-<symbol>:db-kind</symbol> <symbol>:join</symbol>, as opposed to
-<symbol>:base</symbol> or <symbol>:key</symbol>. Once we do that, we
-still need to tell &usql; how to create the join statements for the
-relation. This is what the <symbol>:db-info</symbol> option does. It
-is a list of keywords and values. The available keywords are:
-</para>
-
-<itemizedlist>
- <listitem>
- <para>
- <symbol>:join-class</symbol> - 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>
- <symbol>:home-key</symbol> - 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>
- <symbol>:foreign-key</symbol> - The slot(s) in the join-class
- which will be compared to the value(s) of the home-key.
- </para></listitem>
-
- <listitem>
- <para>
- <symbol>:set</symbol> - 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 <filename>doc/usql-tutorial.lisp</filename> contains
-view class definitions which you can load into your list at this point
-in order to play along at home.
-</para>
-
-<programlisting>
-(clsql-usql:create-view-from-class 'employee)
-(clsql-usql: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
-<function>UPDATE-RECORDS-FROM-INSTANCE</function> function as follows:
-</para>
-
-<programlisting>
-(clsql-usql:update-records-from-instance employee1)
-(clsql-usql:update-records-from-instance employee2)
-(clsql-usql:update-records-from-instance company1)
-</programlisting>
-
-<para>
-Now we can set up some of the relations between employees and
-companies, and their managers. The
-<function>ADD-TO-RELATION</function> 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)
-(clsql-usql:add-to-relation employee2 'manager employee1)
-
-;; Lenin and Stalin both work for Widgets Inc.
-(clsql-usql:add-to-relation company1 'employees employee1)
-(clsql-usql:add-to-relation company1 'employees employee2)
-
-;; Lenin is president of Widgets Inc.
-(clsql-usql: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
- <function>UPDATE-RECORDS-FROM-INSTANCE</function> 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
- from 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
-(clsql-usql: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
-(clsql-usql:update-records-from-instance employee1)
-
-(let ((new-lenin (car (clsql-usql: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 <function>LET</function> expression
- is already familiar to us by now. To understand the call to
- <function>CLSQL-USQL:SELECT</function> 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>
-(clsql-usql: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] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
-
-;; a attribute identifier with table qualifier
-[foo bar] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
-
-;; a attribute identifier with table qualifier
-[= "Lenin" [first_name]] =>
- #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
-
-[< [emplid] 3] =>
- #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
-
-[and [< [emplid] 2] [= [first_name] "Lenin"]] =>
- #<CLSQL-USQL-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] =>
- #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
-
-[= [slot-value 'employee 'emplid]
- [slot-value 'company 'presidentid]] =>
- #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
-</programlisting>
-
-<para>
- The <function>SLOT-VALUE</function> 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
- <function>SLOT-VALUE</function> &sql; extension.
-</para>
-
-<para>
- Since we can now formulate &sql; relational expression which can be
- used as qualifiers, like we put after the <symbol>WHERE</symbol>
- keyword in &sql; statements, we can start querying our objects.
- &usql; provides a function <symbol>SELECT</symbol> 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 <symbol>SELECT</symbol> 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
- <symbol>SELECT</symbol>.
-</para>
-
-<programlisting>
-;; all employees
-(clsql-usql:select 'employee)
-;; all companies
-(clsql-usql:select 'company)
-
-;; employees named Lenin
-(clsql-usql:select 'employee :where [= [slot-value 'employee 'last-name]
- "Lenin"])
-
-(clsql-usql:select 'company :where [= [slot-value 'company 'name]
- "Widgets Inc."])
-
-;; Employees of Widget's Inc.
-(clsql-usql: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
- <function>DELETE-INSTANCE-RECORDS</function> 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 in 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>
+++ /dev/null
-INTRODUCTIION
-
-CLSQL-USQL is a high level SQL interface for Common Lisp which is
-based on the CommonSQL package from Xanalys. It was originally
-developed at Onshore Development, Inc. based on Pierre Mai's MaiSQL
-package. It now incorporates some of the code developed for CLSQL. See
-the files CONTRIBUTORS and COPYING for more details.
-
-CLSQL-USQL depends on the low-level database interfaces provided by
-CLSQL and includes both a functional and an object oriented
-interface to SQL RDBMS.
-
-DOCUMENTATION
-
-A CLSQL-USQL tutorial can be found in the directory doc/
-
-Also see the CommonSQL documentation avaialble on the Lispworks website:
-
-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/
-
-
-PREREQUISITES
-
- o COMMON LISP: currently CMUCL, SBCL, Lispworks
- o RDBMS: currently Postgresql, Mysql, Sqlite
- o ASDF (from http://cvs.sourceforge.net/viewcvs.py/cclan/asdf/)
- o CLSQL-2.0.0 or later (from http://clsql.b9.com)
- o RT for running the test suite (from http://files.b9.com/rt/rt.tar.gz)
-
-
-INSTALLATION
-
-Just load clsql-usql.asd or put it somewhere where ASDF can find it
-and call:
-
-(asdf:oos 'asdf:load-op :clsql-usql)
-
-You'll then need to load a CLSQL backend before you can do anything.
-
-To run the regression tests load clsql-usql-tests.asd or put it
-somewhere where ASDF can find it, edit the file tests/test-init.lisp
-and set the following variables to appropriate values:
-
- *test-database-server*
- *test-database-name*
- *test-database-user*
- *test-database-password*
-
-And then call:
-
-(asdf:oos 'asdf:load-op :clsql-usql-tests)
-(usql-tests:test-usql BACKEND)
-
-where BACKEND is the CLSQL database interface to use (currently one of
-:postgresql, :postgresql-socket, :sqlite or :mysql).
-
-
+* RUNNING THE REGRESSION SUITE
+
+Just load clsql.asd or put it somewhere where ASDF can find it
+and call:
+
+(asdf:oos 'asdf:load-op 'clsql)
+
+You'll then need to load a CLSQL backend before you can do anything.
+
+To run the regression tests load clsql-tests.asd or put it
+somewhere where ASDF can find it, edit the file tests/test-init.lisp
+and set the following variables to appropriate values:
+
+ *test-database-server*
+ *test-database-name*
+ *test-database-user*
+ *test-database-password*
+
+And then call:
+
+(asdf:oos 'asdf:load-op 'clsql-tests)
+(clsql-tests:test-usql BACKEND)
+
+where BACKEND is the CLSQL database interface to use (currently one of
+:postgresql, :postgresql-socket, :sqlite or :mysql).
+
* REGRESSION TEST SUITE GOALS
The intent of this test suite is to provide sufficient coverage for
*** Object Create/Modification/Deletion in a context -- partly covered already
*** Interaction of multiple contexts
*** Schema manipulation within a context
- *** Rollback and error handling within a context
\ No newline at end of file
+ *** Rollback and error handling within a context
projects / clsql.git / commitdiff