+++ /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