This section describes the object-oriented interface to SQL databases using specialized CLOS classes. These classes have standard-db-object as one of their superclasses and have a common metaclass which provides the specialized behavior for mapping subclasses of standard-db-object onto records in the database. A class of this kind is created using def-view-class.
In the simple case, a class maps onto a database table, an instance of the class maps onto a record in the table, and a slot in the class maps onto an attribute in the table.
In general, however, a class maps onto a database view, an instance of the class maps onto a collection of records in the view, and a slot in the class is either:
If an instance maps onto more than one record in the view then for each record, all the key attributes from each table in the view are the same.
It is not possible to inherit from a class that was defined by def-view-class. All of the slots need to be in the same class (and hence also in the same SQL table).
The OODDL lets you define a mapping between the relational and object-oriented worlds to be defined. Through the mapping a CLOS object can effectively denote a collection of records in a database view, and can contain pointers to other view-based CLOS objects. The CLOS object makes explicit an object implicitly described by the flat relational values.
The mapping is defined using the macro def-view-class. This extends the syntax of defclass to allow special base slots to be mapped onto the attributes of database views (presently single tables). When you submit a select query that names a View Class (that is, a class defined by def-view-class), then the corresponding database view is queried, and the slots in the resulting instances are filled with attribute values from the database.
It is also possible to create join slots and virtual (ordinary) slots .
All the special slots are distinguished by a modified set of class and slot options. The special slots and their options are described in more detail under def-view-class in the LispWorks Reference Manual .
Note: def-view-class defines a Lisp view of an underlying database table. It is a similar concept to that of SQL VIEWs, but does not interact with them.
You can create a table based on a View Class using the function create-view-from-class and delete it using the function drop-view-from-class.
The following example shows a View Class corresponding to the traditional employees table, with the employee's department given by a join with the departments table. See def-view-class for a description of the slot options.
(def-view-class employee (standard-db-object)
((employee-number :db-kind :key
:column empno
:type integer)
(employee-name :db-kind :base
:column ename
:type (string 20)
:accessor employee-name)
(employee-department :db-kind :base
:column deptno
:type integer
:accessor employee-department)
(employee-job :db-kind :base
:column job
:type (string 9))
(employee-manager :db-kind :base
:column mgr
:type integer)
(employee-location :db-kind :join
:db-info (:join-class department
:retrieval :deferred
:set nil
:home-key employee-department
:foreign-key department-number
:target-slot department-loc)
:accessor employee-location))
(:base-table emp))
The def-view-class macro allows elements or lists of elements to follow :home-key and :foreign-key. The elements can be symbols, nil, strings, integers or floats.
This syntax means that an object from the join class is only included in the join slot if the values from home-key are equal to the values in foreign-key, in order. These values are calculated as follows:
Note that some database vendors may have short maximum identifier lengths. The CLOS interface uses constructed alias names for tables in its SQL queries, and long table names or long class names may cause the constructed aliases to exceed the maximum identifier length for a particular vendor.
The OODML is designed to be powerful and expressive, while remaining familiar to users of the FDML. To achieve this aim, some of the functions and macros in the SQL interface have been overloaded -- particularly the select function and the iteration constructs.
The function select is common across the both the functional and object-oriented SQL interfaces. If its first argument, selections, refers to a View Class by supplying its symbolic name then the select operation becomes object-oriented and it returns a list of instances instead of a list of attributes.
A subsequent equivalent select call will return the same (eql) instances. The :refresh argument can be used to ensure that existing instances get updated with any changed data. If such an update requires action by your application, then add methods on the generic function instance-refreshed.
In a View Class select call, the symbol slot-value is a valid SQL operator for use within the :where argument.
To find the View Classes for a particular database, use the function list-classes.
To manipulate data via a View Class, that is to modify the records corresponding to instances of the View Class, using the generic functions update-records-from-instance, and update-record-from-slot.
To delete records corresponding to instances of the View Class, use the generic function delete-instance-records.
To update existing instances of a View Class when data is known to have changed, use the generic functions update-slot-from-record and update-instance-from-records.
[select 'employee]
-> #<SQL-OBJECT-QUERY (EMPLOYEE)>
(select 'employee
:where [= [slot-value 'employee 'employee-job]
"SALESMAN"])
((#<db-instance EMPLOYEE 8067092>)
(#<db-instance EMPLOYEE 8069536>)
(#<db-instance EMPLOYEE 8069176>))
(list-classes)
(#<db-class EMPLOYEE> #<db-class DEPARTMENT>)
The object-oriented SQL interface has the same three iteration constructs as the functional interface (see Iteration): a do-loop, a mapping function, and an extension to the Common Lisp loop macro. However, in this case, the iteration focus is not a tuple of attributes (that is, a record), but a tuple of instances. For example:
(loop for (jones company) being the tuples in
[select 'person 'organization
:where [= [slot-value 'person 'surname] "Jones"]]
do (format t "~A ~A ~%"
(slot-value jones 'forename)
(slot-value company 'short-name)))
Note: Instances may denote many database records, and hence the effective iteration focus in this case is a tuple of sets of tuples of attributes.
LispWorks User Guide and Reference Manual - 20 Sep 2017