<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
+ 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>
+ <citetitle>Philip Greenspun's "SQL For Web Nerds" - Data
+ Modeling</citetitle>
</ulink>
</para>
</footnote>
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;.
+ 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>
</para>
<programlisting>
-(sql:def-view-class employee ()
+(clsql-usql:def-view-class employee ()
((emplid
:db-kind :key
:db-constraints :not-null
:nulls-ok t))
(:base-table employee))
-(sql:def-view-class company ()
+(clsql-usql:def-view-class company ()
((companyid
:db-type :key
:db-constraints :not-null
</programlisting>
<para>
- The DEF-VIEW-CLASS macro is just like the normal CLOS DEFCLASS
- macro, except that it handles several slot options that DEFCLASS
+ 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.
<itemizedlist>
<listitem><para>
- :column -- The name of the &sql; column this slot is stored in.
+ <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>
- :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.
-</para></listitem>
-
- <listitem><para>
- :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.
-</para></listitem>
-
- <listitem><para>
- :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.
-</para></listitem>
-
- <listitem><para>
- :db-type -- A string which will be used as the type specifier for
- this slots column definition in the database.
-</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>
- :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
-</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>
- :db-info -- A join specification.
-</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 :db-kind :key slot option.
+ 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 :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
+ 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 n the DB as a NUMBER(38). This is not recomended because it
- could makes your view class unportable across database systems.
+ 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>
- 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.
+ <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>
<title>Class Relations</title>
<para>
-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.
+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>
SELECT first_name, last_name FROM employee, company
WHERE employee.companyid = company.companyid
AND company.company_name = "Widgets Inc."
+</programlisting>
-Who is Vladamir's manager
+<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"
</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
+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
<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 COMPANY slot
-and get the appropriate result.
+definition to the employee class we can then ask for it's
+<symbol>COMPANY</symbol> slot and get the appropriate result.
</para>
<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 EMPLOYEES slot and get the right result.
+we can then ask for it's <symbol>EMPLOYEES</symbol> slot and get the
+right result.
</para>
<programlisting>
<para>
And lastly, to define the relation between an employee and their
-manager.
+manager:
</para>
<programlisting>
</para>
<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
-what the :db-info option does. It is a list of keywords and values.
-The available keywords are:
+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>
- :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.
- </para></listitem>
-
- <listitem><para>
- :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.
-</para></listitem>
+ <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>
- :foreign-key -- The slot(s) in the join-class which will be compared
- to the value(s) of the home-key.
-</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>
- :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.
-</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.
+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>
</para>
<para>
-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.
+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>
-(sql:create-view-from-class 'employee)
-(sql:create-view-from-class 'company)
+(clsql-usql:create-view-from-class 'employee)
+(clsql-usql:create-view-from-class 'company)
</programlisting>
<para>
<para>
In order to insert an objects into the database we use the
-UPDATE-RECORDS-FROM-INSTANCE function as follows:
+<function>UPDATE-RECORDS-FROM-INSTANCE</function> function as follows:
</para>
<programlisting>
-(sql:update-records-from-instance employee1)
-(sql:update-records-from-instance employee2)
-(sql:update-records-from-instance company1)
+(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 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.
+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)
-(sql:add-to-relation employee2 'manager employee1)
+(clsql-usql: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)
+(clsql-usql:add-to-relation company1 'employees employee1)
+(clsql-usql:add-to-relation company1 'employees employee2)
;; Lenin is president of Widgets Inc.
-(sql:add-to-relation company1 'president employee1)
+(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 UPDATE-RECORDS-FROM-INSTANCE
-method will write all of the changes you have made to the object into
-the database.
+ 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 fro the
-German Socialists.
+ 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>
;; and print the email
;; This lets us use the functional &usql; interface with [] syntax
-(sql:locally-enable-sql-reader-syntax)
+(clsql-usql:locally-enable-sql-reader-syntax)
(format t "The email address of ~A ~A is ~A"
(first-name employee1)
(setf (employee-email employee1) "lenin-nospam@soviets.org")
;; Update the database
-(sql:update-records-from-instance employee1)
+(clsql-usql:update-records-from-instance employee1)
-(let ((new-lenin (car (sql:select 'employee
+(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 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;.
+ 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>
<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.
+ 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:
+ Once we have turned on the syntax with the expression:
</para>
<programlisting>
-(sql:locally-enable-sql-reader-syntax)
+(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:
+ 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>
</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.
+ 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 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.
+ 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 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.
+ 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
-(sql:select 'employee)
+(clsql-usql:select 'employee)
;; all companies
-(sql:select 'company)
+(clsql-usql:select 'company)
;; employees named Lenin
-(sql:select 'employee :where [= [slot-value 'employee 'last-name]
+(clsql-usql:select 'employee :where [= [slot-value 'employee 'last-name]
"Lenin"])
-(sql:select 'company :where [= [slot-value 'company 'name]
+(clsql-usql:select 'company :where [= [slot-value 'company 'name]
"Widgets Inc."])
;; Employees of Widget's Inc.
-(sql:select 'employee
+(clsql-usql:select 'employee
:where [and [= [slot-value 'employee 'companyid]
[slot-value 'company 'companyid]]
[= [slot-value 'company 'name]
<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
-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.
+ 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.
+ 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>
<title>Conclusion</title>
<para>
-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.
+ 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>
-
-
-
projects / clsql.git / blobdiff