Data Modeling with CLSQL

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:

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.

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.