--- /dev/null
+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
+
+
projects / clsql.git / blobdiff