All Manuals > LispWorks User Guide and Reference Manual > 47 The SQL Package

NextPrevUpTopContentsIndex

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.

Slot Options

The slot options 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.

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.

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))
See also

create-view-from-class
delete-instance-records
drop-view-from-class
standard-db-object
update-record-from-slot
update-records-from-instance


LispWorks User Guide and Reference Manual - 13 Feb 2015

NextPrevUpTopContentsIndex