def-view-class

def-view-class Macro

Summary

Extends the syntax of defclass to allow specified 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.

Description

The macro def-view-class creates a class called name which maps onto a database view. Such a class is called a View Class.

The macro def-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 a select 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.

If superclasses is nil then standard-db-object automatically becomes the superclass of the newly-defined View Class. If superclasses is nil, it must include standard-db-object.

Slot Options

The slot options in slots for def-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. All View Classes must have `:key` fields that uniquely distinguish the instances, to maintain object identity. To specify a key which spans multiple slots, each of the slots should have `:db-kind :key`. The underlying requirement is that tuples of the form (key1 ... keyN) are unique. The `:db-kind :key` slots do not need to be keys in the table.
`: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` `target-slot`
	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 contain instances of this target slot as opposed to those of `:join-class`. The actual behavior depends on the value of `set`. 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` `retrieval-time`
	`retrieval-time` can be `:deferred`, which defers filling this slot from the database until the slot itself is accessed. This is the default value. `retrieval-time` can alternatively be `:immediate` which 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. When `retrieval-time` is `:immediate`, then `set` is `nil`.
`:set` `set`	When `set` is `t` and `target-slot` is defined, the slot will contain a list of pairs (`target-value` `join-instance`) where `target-value` is the value of the target slot and `join-instance` is the corresponding instance of the join class. When `set` is `t` and `target-slot` is undefined, the slot will contain a list of instances of the join class. When `set` is `nil` the slot will contain a single instance. The default value of `set` is `t`.

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 home-key are equal to the values in 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, and therefore :type is mandatory for slots with :db-kind :base or :key. 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. The following Lisp types are accepted for type, and correspond to the SQL type shown:

`(string n)`	CHAR(n)
integer	INTEGER
`(integer n)`	INTEGER(n)
float	FLOAT
`(float n)`	FLOAT(n)
`sql:universal-time`	TIMESTAMP

Class Options

def-view-class recognizes the following class-options in addition to the standard class options defined for defclass:

`(:base-table table-name)`
	The slots of the class `name` will be read from the table `table-name`. If you do not specify the `:base-table` option, then `table-name` defaults to the name of the class.

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

(def-view-class flex-schema ()
  ((name        :type (string 8) :db-kind :key)
   (description :type (string 256))
   (classes :db-kind :join
            :db-info (:home-key name
                      :foreign-key schema-name
                      :join-class flex-class
                      :retrieval :deferred)))
                (:base-table flex_schema))

(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))