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] => # ;; a attribute identifier with table qualifier [foo bar] => # ;; a attribute identifier with table qualifier [= "Lenin" [first_name]] => # [< [emplid] 3] => # [and [< [emplid] 2] [= [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] => # [= [slot-value 'employee 'emplid] [slot-value '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