<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
+ 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.
+ &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>
- 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; 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
+ &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
+ &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
+ and &usql; are minor. Xanalys makes the following documents
available:
</para>
<title>Data Modeling with UncommonSQL</title>
<para>
- Before we can create, query and manipulate USQL objects, we need to
+ Before we can create, query and manipulate &usql; objects, we need to
define our data model as noted by Philip Greenspun
<footnote>
<para>
</itemizedlist>
<para>
- With SQL database one would do this by defining a set of
+ 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,
+ 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
+ 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.
+ some of the tedium of &sql;.
</para>
<para>
- Let us start with a simple example of two SQL tables, and the
+ Let us start with a simple example of two &sql; tables, and the
relations between them.
</para>
</programlisting>
<para>
-This is of course the canonical SQL tutorial example, "The Org Chart".
+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
+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>
<itemizedlist>
<listitem><para>
- :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
+ :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.
</para></listitem>
<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
+ primary key (&usql; supports multi-field primary keys) by specifying
the :db-kind :key slot option.
</para>
<para>
- The SQL type of a slot when it is mapped into the database is
+ 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
+ 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
+ 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
<title>Class Relations</title>
<para>
-In an SQL only application, the EMPLOYEE and COMPANY tables can be
+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.
+Inc.". This is done by joining tables with an &sql; query.
</para>
<para>
</programlisting>
<para>
-With USQL however we do not need to write out such queries because our
+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
</programlisting>
<para>
-USQL join slots can represent one-to-one, one-to-many, and
+&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>
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
+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:
</para>
</itemizedlist>
<para>
-There are other :join-info options available in USQL, but we will save
+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>
<para>
After you make any changes to an object, you have to specifically tell
-USQL to update the SQL database. The UPDATE-RECORDS-FROM-INSTANCE
+&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.
</para>
<para>
-Since USQL objects re just normal CLOS objects, we can manipulate
+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.
;; database. Get a new object representing Lenin from the database
;; and print the email
-;; This lets us use the functional USQL interface with [] syntax
+;; 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"
<para>
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.
+the Functional &sql; interface and it's integration with the Object
+Oriented interface of &usql;.
</para>
</sect1>
<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.
+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>
</programlisting>
<para>
-we can start entering fragments of SQL into our lisp reader. We will
+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
+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:
+database independent &sql; syntax. Here are some examples:
</para>
<programlisting>
;; an attribute or table name
-[foo] => #<MAISQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
+[foo] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO>
;; a attribute identifier with table qualifier
-[foo bar] => #<MAISQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
+[foo bar] => #<CLSQL-USQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR>
;; a attribute identifier with table qualifier
[= "Lenin" [first_name]] =>
- #<MAISQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
+ #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)>
[< [emplid] 3] =>
- #<MAISQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
+ #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)>
[and [< [emplid] 2] [= [first_name] "Lenin"]] =>
- #<MAISQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND
+ #<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] =>
- #<MAISQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
+ #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)>
[= [slot-value 'employee 'emplid]
[slot-value 'company 'presidentid]] =>
- #<MAISQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
+ #<CLSQL-USQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)>
</programlisting>
<para>
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.
+querying objects, be sure to use the SLOT-VALUE &sql; extension.
</para>
<para>
-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
+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.
+to a qualifier, can be sorted, and various other &sql; operations.
</para>
<para>
;; 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!
+;; saving us the work of writing out the &sql;!
(company-employees company1)
;; President of Widgets Inc.
<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
+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
<title>Conclusion</title>
<para>
-There are alot more nooks and crannies to USQL, some of which are
+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
+The best documentation at this time is still the source code for &usql;
itself and the inline documentation for it's various function.
</para>
projects / clsql.git / commitdiff