Before we can create, query and manipulate CLSQL objects, we need to define our data model as noted by Philip Greenspun [1]
When data modeling, you are telling the relational database management system (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 CLSQL 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),
email 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 CLSQL, we would have two "view classes" (a fancy word for a class mapped into a database). They would be defined as follows:
(clsql: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
:initarg :companyid)
(managerid
:type integer
:nulls-ok t
:initarg :managerid))
(:base-table employee))
(clsql:def-view-class company ()
((companyid
:db-kind :key
:db-constraints :not-null
:type integer
:initarg :companyid)
(name
:type (string 100)
:initarg :name)
(presidentid
:type integer
:initarg :presidentid))
(: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 database mapping which is performed for this slot. :base indicates the slot maps to an ordinary column of the database 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 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.
:db-writer - 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.
:db-type - A string which will be used as the type specifier for this slots column definition in the database.
:void-value - The Lisp value to return if the
field is NULL. The default is NIL.
: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 (CLSQL 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 CLSQL 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 in the database 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.
Another class option is :normalizedp, which signals
CLSQL to use a normalized schema for the mapping from slots to
SQL columns. By default CLSQL includes all the slots of a parent
class that map to SQL columns into the child class. This option
tells CLSQL to normalize the schema, so that a join is done on the
primary keys of the concerned tables to get a complete column set
for the classes. For more information, see
def-view-class.