16 The SQL Package

def-view-class

Macro

Summary

Extends the syntax ofdefclass to allow special slots to be mapped onto the attributes of database views.

Package

sql

Signature

def-view-class name superclasses slots &rest class-options => class

Arguments

name: A class name.
superclasses: The superclasses of the class to be created.
slots: The slot definitions of the new class.
class-options: The class options of the new class.

Values

class: The defined class.

Slot Options

The slot options fordef-view-class are:db-kind and:db-info. In addition the slot option:type is treated specially for view classes.

:db-kind may be one of:base,:key,:join, or:virtual. The default is:base. Each value is described below:

:base: This indicates that this slot corresponds to an ordinary attribute of the database view. You can name the database attribute by using the keyword:column. By default, the database attribute is named by the slot.
:key: This indicates that this slot corresponds to part of the unique key for this view. A:key slot is also a:base slot.
:join: This indicates that this slot corresponds to a join. A slot of this type will contain view class objects.
:virtual: This indicates that this slot is an ordinary CLOS slot not associated with a database column.

A join is defined by the slot option:db-info, which takes a list. Items in the list may be:

:join-class class-name

This is the class to join on.

:home-key slot-name

This is the slot of the defining class to be a subject for the join. The argument slot-name may be an element or a list of elements, where elements can be symbols,nil, strings, integers or floats.

:foreign-key slot-name

This is the name of the slot of the:join-class to be a subject for the join. The slot-name may be an element or a list of elements, where elements can be symbols,nil, strings, integers or floats.

:target-slot slot-name

This is the name of a:join slot in:join-class. This is optional and is only specified if you want the defining slot to be filled with instances of this target-slot as opposed to those of:join-class. An example of its usage is when the:join-class is an intermediate class and you are really only interested in it as a route to the:target slot.

:retrieval :deferred

Defers filling this slot from the database until the slot itself is accessed.

:retrieval :immediate

This is the opposite of:deferred and generates the join SQL for this slot whenever a query is generated on the class. In other words, this is an intermediate class only, which is present for the purpose of joining two entities of other classes together. The:immediate specification for:retrieval also defines:set nil.

:set t

The slot will contain a list of pairs of instances, one of which is the target instance (if specified) and one is the join instance.

:set nil

The slot will contain a single instance.

The syntax for:home-key and:foreign-key means that an object from a join class will only be included in the join slot if the values from the home-key areequal to the values in the foreign-key, in order. These values are calculated as follows: if the element in the list is a symbol it is taken to be a slot name and the value of the slot is used, otherwise the element is taken to be the value. See the second example below.

The:type slot option is treated specially for view-classes. There is a need for stringent type-checking in view-classes because of the translation into database data. Some methods are provided for type checking and type conversion. For example, a:type specifier of(string 10) in SQL terms means allow a character type value with length of less than or equal to 10.

Class Options

A class-option fordef-view-class is:base-table, which is the name of the table corresponding to the class. If you do not specify it, it defaults to the name of the class.

Description

The macrodef-view-class creates a class called name which maps onto a database view. The macrodef-view-class extends the syntax of defclass to allow special base slots to be mapped onto the attributes of database views (presently single tables). When aselect query that names a view-class is submitted, then the corresponding database view is queried, and the slots in the resulting view-class instances are filled with attribute values from the database.

One of the default classes for superclasses isstandard-db-object.

Examples

The following example shows a class corresponding to the traditional employees table, with the employee's department given by a join with the departments table.

(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 following example illustrates how lists of elements can follow:home-key and:foreign-key in the:db-info slot option.

(def-view-class flex-class ()
   ((schema-name :type (string 8) :db-kind :key 
                                 :column schema_name)
    (name        :type (string 32) :db-kind :key)
    (base-name   :type (string 64) :column base_name)
    (super-classes :db-kind :join
                   :db-info (:home-key 
                              (schema-name name)
                             :foreign-key 
                              (schema-name class-name)
                             :join-class 
                              flex-superclass
                             :retrieval :deferred))
    (schema :db-kind :join
            :db-info (:home-key schema-name
                      :foreign-key name
                      :join-class flex-schema
                      :set nil))
    (properties  :db-kind :join
          :db-info (:home-key (schema-name name "")
                    :foreign-key 
                     (schema-name class-name slot-name)
                    :join-class flex-property
                    :retrieval :deferred)))
   (:base-table flex_class))

(def-view-class flex-slot ()
   ((schema-name :type (string 8) :db-kind :key 
                 :column schema_name)
    (class-name  :type (string 32) :db-kind :key 
                 :column class_name)
    (name        :type (string 32) :db-kind :key)
    (class :db-kind :join
           :db-info (:home-key (schema-name class-name)
                     :foreign-key (schema-name name)
                     :join-class flex-class
                     :set nil))
    (properties  :db-kind :join
         :db-info (:home-key 
                     (schema-name class-name name)
                   :foreign-key 
                     (schema-name class-name slot-name)
                   :join-class flex-property
                   :retrieval :deferred)))
   (:base-table flex_slot))

(def-view-class flex-property ()
   ((schema-name :type (string 8) :db-kind  :key 
                 :column schema_name)
    (class-name  :type (string 32) :db-kind :key
                 :column class_name)
    (slot-name   :type (string 32) :db-kind :key
                 :column slot_name)
    (property    :type (string 32) :db-kind :key)
    (values :db-kind :join
      :db-info (:home-key 
                 (schema-name class-name 
                  slot-name property)
                :foreign-key 
                 (schema-name class-name 
                  slot-name property)
                      :join-class flex-property-value
                      :retrieval :deferred)))
   (:base-table flex_property))
(def-view-class flex-property-value ()
   ((schema-name :type (string 8) :db-kind  :key
                 :column schema_name)
    (class-name  :type (string 32) :db-kind :key
                 :column class_name)
    (slot-name   :type (string 32) :column slot_name)
    (property    :type (string 32) :db-kind :key)
    (order       :type integer)
    (value       :type (string 128)))
   (:base-table flex_property_value))

See also
: create-view-from-class
drop-view-from-class