X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=doc%2Fref-ooddl.xml;h=b6873c8d75d32bee43313ddc7e15f267dc5f5e2b;hp=e61a1cc014b5fec90321e10653973288e0f8c776;hb=fc58e4fb7d908985389c86adf57ddee6c1dde5d2;hpb=3a45ae325932b9565993407c5c94b28cdefd1f14 diff --git a/doc/ref-ooddl.xml b/doc/ref-ooddl.xml index e61a1cc..b6873c8 100644 --- a/doc/ref-ooddl.xml +++ b/doc/ref-ooddl.xml @@ -8,25 +8,28 @@ Object Oriented Data Definition Language (OODDL) - - - The Object Oriented Data Definition Language (OODDL) provides - access to relational SQL tables using Common Lisp Object System - (CLOS) objects. SQL tables are mapped to CLOS objects with the - SQL columns being mapped to slots of the CLOS object. - - - The mapping between SQL tables and CLOS objects is defined - with the macro def-view-class. SQL - tables are created with create-view-from-class - and SQL tables can be deleted with drop-view-from-class. - - The above functions refer to the Lisp view of the SQL - table. This Lisp view should not be confused with SQL VIEW statement. - + + + The Object Oriented Data Definition Language (OODDL) provides + access to relational SQL tables using Common Lisp Object System + (CLOS) objects. SQL tables are mapped to CLOS objects with the + SQL columns being mapped to slots of the CLOS object. + + + The mapping between SQL tables and CLOS objects is defined with + the macro def-view-class. SQL + tables are created with create-view-from-class + and SQL tables can be deleted with drop-view-from-class. + + + The above functions refer to the Lisp + view of the SQL table. This Lisp view + should not be confused with SQL VIEW + statement. + @@ -38,11 +41,11 @@ Class Precedence List - - standard-db-object - standard-object - t - + + standard-db-object + standard-object + t + @@ -50,17 +53,17 @@ of all &clsql; View Classes. - Class details + Class details (defclass STANDARD-DB-OBJECT ()(...)) - Slots + Slots - - slot VIEW-DATABASE is of type (OR NULL DATABASE) + + slot VIEW-DATABASE is of type (OR NULL DATABASE) which stores the associated database for the - instance. - + instance. + @@ -74,7 +77,7 @@ Value Type - Fixnum + Fixnum @@ -85,21 +88,27 @@ Description If a slot of a class defined by - DEF-VIEW-CLASS is of the type - STRING or VARCHAR and does - not have a length specified, then the value of this variable - is used as SQL length. + def-view-class is of the type + string or + varchar and does not have a length + specified, then the value of this variable is used as SQL + length. Examples - (let ((*default-string-length* 80)) - (def-view-class s80 () - ((a :type string) - (b :type (string 80)) - (c :type varchar)))) - (create-view-from-class 's80) +(let ((*default-string-length* 80)) + (def-view-class s80 () + ((a :type string) + (b :type (string 80)) + (c :type varchar)))) +=> #<Standard-Db-Class S80 {480A431D}> + +(create-view-from-class 's80) +=> +(table-exists-p [s80]) +=> T The above code causes a SQL table to be created with the SQL command @@ -108,8 +117,10 @@ Affected By - Some SQL backends do not support VARCHAR - lengths greater than 255 . + + Some SQL backends do not support + varchar lengths greater than 255. + See Also @@ -124,59 +135,67 @@ CREATE-VIEW-FROM-CLASS - Create a SQL table from a view class. + Create a SQL table from a View Class. Function Syntax - (create-view-from-class view-class-name &key (database *default-database*) (transactions t)) => + create-view-from-class view-class-name &key database transactions => Arguments and Values view-class-name - - - The name of a view class that has been defined with - def-view-class. - - - + + + The name of a View + Class that has been defined with def-view-class. + + + database - - - The database in which to create the SQL table. + + + The database in + which to create the SQL table. This will default to the + value of *default-database*. - - + + transactions - - - When &nil; specifies that a table type which does not support transactions should be used. - - - + + + When &nil; specifies that a table type which does not + support transactions should be used. + + + Description - Creates a table as defined by the View Class - VIEW-CLASS-NAME in DATABASE which defaults to - *DEFAULT-DATABASE*. + + Creates a table as defined by the View Class + view-class-name in + database. Examples - * (def-view-class 'foo () ((a :type (string 80)))) - #<CLSQL-SYS::STANDARD-DB-CLASS FOO> - * (create-view-from-class 'foo) - * (list-tables) - ("FOO") +(def-view-class foo () ((a :type (string 80)))) +=> #<Standard-Db-Class FOO {4807F7CD}> +(create-view-from-class 'foo) +=> +(list-tables) +=> ("FOO") @@ -197,14 +216,14 @@ Exceptional Situations - A condition will be signaled if the table can not be created - in the SQL database. + A condition will be signaled if the table can not be created + in the SQL database. See Also - + def-view-class drop-view-from-class @@ -218,6 +237,11 @@ applications which would benefit from faster table access and do not require transaction support. + + The case of the table name is determined by the type of the + database. &mysql;, for example, creates databases in upper-case + while &postgresql; uses lowercase. + @@ -230,197 +254,300 @@ Syntax - (def-view-class name superclasses slots &rest class-options) [macro] => class + def-view-class name superclasses slots &rest class-options => class Arguments and Values - - name - - - The class name. - - - - - name - - - The superclasses for the defined class. - - - - - slots - - - The class slot definitions. - - - - - class - - - The defined class. - - - - - - - Slot Options - - - - :db-kind - specifies the kind of - DB mapping which is performed for this slot and defaults - to :base which indicates that the - slot maps to an ordinary column of the database table. A - :db-kind value of - :key indicates that this slot is - a special kind of :base slot - which maps onto a column which is one of the unique keys - for the database table, the value - :join indicates this slot - represents a join onto another View Class which contains - View Class objects, and the value - :virtual indicates a standard - CLOS slot which does not map onto columns of the - database table. - - + + name - :db-info - if a slot is specified with - :db-kind :join, the - slot option :db-info contains a list - which specifies the nature of the join. + The class name. + + + superclasses - :type - for slots of - :db-kind :base or - :key, the :type slot - option has a special interpretation such that Lisp - types, such as string, integer and float are - automatically converted into appropriate SQL types for - the column onto which the slot maps. This behaviour may - be overridden using the :db-type slot - option. The valid values are: - - - string - a variable length character field up to - *default-string-length* characters. - - - (string n) - a fixed length character field - n characters long. - - - varchar - a variable length character field up to - *default-string-length* characters. - - - (varchar n) - a variable length character field up to - n characters in length. - - - char - a single character field - - integer - signed integer at least 32-bits wide - (integer n) - float - (float n) - long-float - number - (number n) - (number n p) - - smallint - An integer column 16-bits - wide. [not supported by all database backends] - - - bigint - An integer column - 64-bits wide. [not supported by all database backends] - - - universal-time - an integer - field sufficiently wide to store a - universal-time. On most databases, a slot of this - type assigned a SQL type of - BIGINT - - - wall-time - a slot which - stores a date and time in a SQL timestamp - column. &clsql; provides a number of time - manipulation functions to support objects of type - wall-time. - - - duration - stores a duration structure. - &clsql; provides routines for wall-time and duration - processing. - - boolean - stores a &t; or &nil; value. - - generalized-boolean - similar - to a boolean in that either a - &t; or &nil; value is stored in the SQL - database. However, any Lisp object can be stored in - the Lisp object. A Lisp value of &nil; is stored as - FALSE in the database, any - other Lisp value is stored as - TRUE. - - - keyword - stores a keyword - - symbol - stores a symbol - - list - stores a list by writing it to a string. The items - in the list must be able to be readable written. - - vector - stores a vector similarly to list - array - stores a array similarly to list - + The superclasses for the defined class. - + + + slots - :column - specifies the name of - the SQL column which the slot maps onto, if - :db-kind is not - :virtual, and defaults to the - slot name. + The class slot definitions. - - - :void-value - specifies - the value to store in the Lisp instance if the SQL value is NULL and defaults - to NIL. - - + + + class options - :db-constraints - is a string - representing an SQL table constraint expression or a - list of such strings. + The class options. + + + class - :db-type - a string to specify the SQL - column type. If specified, this string overrides the SQL - column type as computed from the :type - slot value. + The defined class. + + + + + Slot Options + + + + :db-kind - specifies the kind of + database mapping which is performed for this slot and + defaults to :base which indicates + that the slot maps to an ordinary column of the database + table. A :db-kind value of + :key indicates that this slot is a + special kind of :base slot which + maps onto a column which is one of the unique keys for the + database table, the value :join + indicates this slot represents a join onto another + View Class + which contains View Class objects, and the value + :virtual indicates a standard CLOS + slot which does not map onto columns of the database + table. + + + + + :db-info - if a slot is specified + with :db-kind + :join, the slot option + :db-info contains a property list + which specifies the nature of the join. The valid members + of the list are: + + + + + :join-class + class-name - the name of the + class to join on. + + + + + :home-key + slot-name - the name of the slot + of this class for joining + + + + + :foreign-key + slot-name - the name of the slot + of the :join-class for joining + + + + + :target-slot + target-slot - this is an optional + parameter. If specified, then the join slot of the + defining class will contain instances of this target + slot rather than of the join class. This can be useful + when the :join-class is an + intermediate class in a + many-to-many relationship and the + application is actually interested in the + :target-slot. + + + + + :retrieval + time - The default value is + :deferred, which defers filling + this slot until the value is accessed. The other valid + value is :immediate which + performs the SQL query when the instance of the class + is created. In this case, the + :set is automatically set to + &nil; + + + + + :set set - + This controls what is stored in the join slot. The + default value is &t;. When set is + &t; and target-slot is undefined, + the join slot will contain a list of instances of the + join class. Whereas, if + target-slot is defined, then the + join slot will contain a list of pairs of + (target-value join-instance). + When set is &nil;, the join slot + will contain a single instances. + + + + + + + :type - for slots of + :db-kind :base or + :key, the :type slot + option has a special interpretation such that Lisp + types, such as string, integer and float are + automatically converted into appropriate SQL types for + the column onto which the slot maps. This behaviour may + be overridden using the :db-type slot + option. The valid values are: + + + string - a variable length + character field up to *default-string-length* + characters. + + + (string n) - a fixed length + character field n characters + long. + + + varchar - a variable length + character field up to *default-string-length* + characters. + + + (varchar n) - a variable length + character field up to n + characters in length. + + + char - a single character field + + integer - signed integer + at least 32-bits wide + (integer n) + float + (float n) + long-float + number + (number n) + (number n p) + + tinyint - An integer column 8-bits + wide. [not supported by all database backends] + + + smallint - An integer column 16-bits + wide. [not supported by all database backends] + + + bigint - An integer column + 64-bits wide. [not supported by all database backends] + + + universal-time - an integer + field sufficiently wide to store a + universal-time. On most databases, a slot of this + type assigned a SQL type of + BIGINT + + + wall-time - a slot which stores + a date and time in a SQL timestamp column. &clsql; + provides a number of time manipulation functions to + support objects of type wall-time. + + + date - a slot which stores the + date (without any time of day resolution) in a + column. &clsql; provides a number of time + manipulation functions that operate on date values. + + + duration - stores a + duration structure. &clsql; provides + routines for wall-time and + duration processing. + + boolean - stores a &t; or + &nil; value. + + generalized-boolean - similar + to a boolean in that either a + &t; or &nil; value is stored in the SQL + database. However, any Lisp object can be stored in + the Lisp object. A Lisp value of &nil; is stored as + FALSE in the database, any + other Lisp value is stored as + TRUE. + + + keyword - stores a keyword + + symbol - stores a symbol + + list - stores a list by writing + it to a string. The items in the list must be able to + be readable written. + + vector - stores a vector + similarly to list + array - stores a array + similarly to list + + + + + + + :column - specifies the name of + the SQL column which the slot maps onto, if + :db-kind is not + :virtual, and defaults to the + slot name. If the slot name is used for the SQL column + name, any hypens in the slot name are converted + to underscore characters. + + + + + :void-value - specifies the value + to store in the Lisp instance if the SQL value is NULL and + defaults to NIL. + + + + + :db-constraints - is a keyword + symbol representing an SQL column constraint expression or + a list of such symbols. The following column constraints + are supported: :not-null, + :primary-key, :unique, + :unsigned (&mysql; specific), + :zerofill (&mysql; specific) and + :auto-increment (&mysql; specific). + + + + + :db-type - a string to specify the SQL + column type. If specified, this string overrides the SQL + column type as computed from the :type + slot value. + + @@ -429,8 +556,10 @@ - :base-table - specifies the name of the - SQL database table. The default value is the class name. + :base-table - specifies the name + of the SQL database table. The default value is the + class name. Like slot names, hypens in the class name + are converted to underscore characters. @@ -439,23 +568,168 @@ Description - Creates a View Class called NAME whose - slots SLOTS can map onto the attributes + Creates a View + Class called name whose + slots slots can map onto the attributes of a table in a database. If - SUPERCLASSES is &nil; then the - superclass of CLASS will be - STANDARD-DB-OBJECT, otherwise - SUPERCLASSES is a list of superclasses - for CLASS which must include - STANDARD-DB-OBJECT or a descendent of this - class. + superclasses is &nil; then the + superclass of class will be + standard-db-object, otherwise + superclasses is a list of superclasses + for class which must include + standard-db-object or a descendent of + this class. Examples + + The following examples are from the &clsql; test suite. + - +(def-view-class person (thing) + ((height :db-kind :base :accessor height :type float + :initarg :height) + (married :db-kind :base :accessor married :type boolean + :initarg :married) + (birthday :type clsql:wall-time :initarg :birthday) + (bd-utime :type clsql:universal-time :initarg :bd-utime) + (hobby :db-kind :virtual :initarg :hobby :initform nil))) + +(def-view-class employee (person) + ((emplid + :db-kind :key + :db-constraints :not-null + :type integer + :initarg :emplid) + (groupid + :db-kind :key + :db-constraints :not-null + :type integer + :initarg :groupid) + (first-name + :accessor first-name + :type (varchar 30) + :initarg :first-name) + (last-name + :accessor last-name + :type (varchar 30) + :initarg :last-name) + (email + :accessor employee-email + :type (varchar 100) + :initarg :email) + (ecompanyid + :type integer + :initarg :companyid) + (company + :accessor employee-company + :db-kind :join + :db-info (:join-class company + :home-key ecompanyid + :foreign-key companyid + :set nil)) + (managerid + :type integer + :initarg :managerid) + (manager + :accessor employee-manager + :db-kind :join + :db-info (:join-class employee + :home-key managerid + :foreign-key emplid + :set nil)) + (addresses + :accessor employee-addresses + :db-kind :join + :db-info (:join-class employee-address + :home-key emplid + :foreign-key aemplid + :target-slot address + :set t))) + (:base-table employee)) + +(def-view-class company () + ((companyid + :db-kind :key + :db-constraints :not-null + :type integer + :initarg :companyid) + (groupid + :db-kind :key + :db-constraints :not-null + :type integer + :initarg :groupid) + (name + :type (varchar 100) + :initarg :name) + (presidentid + :type integer + :initarg :presidentid) + (president + :reader president + :db-kind :join + :db-info (:join-class employee + :home-key presidentid + :foreign-key emplid + :set nil)) + (employees + :reader company-employees + :db-kind :join + :db-info (:join-class employee + :home-key (companyid groupid) + :foreign-key (ecompanyid groupid) + :set t)))) + +(def-view-class address () + ((addressid + :db-kind :key + :db-constraints :not-null + :type integer + :initarg :addressid) + (street-number + :type integer + :initarg :street-number) + (street-name + :type (varchar 30) + :void-value "" + :initarg :street-name) + (city + :column "city_field" + :void-value "no city" + :type (varchar 30) + :initarg :city) + (postal-code + :column zip + :type integer + :void-value 0 + :initarg :postal-code)) + (:base-table addr)) + +;; many employees can reside at many addressess +(def-view-class employee-address () + ((aemplid :type integer :initarg :emplid) + (aaddressid :type integer :initarg :addressid) + (verified :type boolean :initarg :verified) + (address :db-kind :join + :db-info (:join-class address + :home-key aaddressid + :foreign-key addressid + :retrieval :immediate))) + (:base-table "ea_join")) + +(def-view-class deferred-employee-address () + ((aemplid :type integer :initarg :emplid) + (aaddressid :type integer :initarg :addressid) + (verified :type boolean :initarg :verified) + (address :db-kind :join + :db-info (:join-class address + :home-key aaddressid + :foreign-key addressid + :retrieval :deferred + :set nil))) + (:base-table "ea_join")) @@ -471,17 +745,17 @@ Exceptional Situations - None. + None. See Also - + create-view-from-class standard-db-object drop-view-from-class - + @@ -494,6 +768,15 @@ and a column type VARCHAR2(100) in &oracle; + + The actual lisp type for a slot may be different than the + value specified by the :type attribute. + For example, a slot declared with ":type (string + 30)" actually sets the slots Lisp type as + (or null string). This is to allow a + &nil; value or a string shorter than 30 characters to be + stored in the slot. + @@ -506,37 +789,50 @@ Syntax - (drop-view-from-class view-class-name &key (database *default-database*)) => + drop-view-from-class view-class-name &key database => Arguments and Values + + view-class-name + + + The name of the View + Class. + + + - view-class-name + database - The name of the view class. - - - + database + object. This will default to the value of + *default-database*. + + + Description - Removes a table defined by the View Class - VIEW-CLASS-NAME from - DATABASE which defaults to - *DEFAULT-DATABASE*. + Removes a table defined by the View Class + view-class-name from + database which defaults to + *default-database*. Examples - * (list-tables) - ("FOO" "BAR") - * (drop-view-from-class 'foo) - * (list-tables) - ("BAR") +(list-tables) +=> ("FOO" "BAR") +(drop-view-from-class 'foo) +=> +(list-tables) +=> ("BAR") @@ -562,16 +858,16 @@ See Also - + create-view-from-class def-view-class - + Notes - + None. @@ -585,69 +881,71 @@ Syntax - (list-classes &key (test #'identity) (root-class (find-class 'standard-db-object)) (database *default-database*)) => classes + list-classes &key test root-class database => classes Arguments and Values - - test - - + + test + + a function used to filter the search. By default, identity is used which will return all classes. - - - - - root-class - - - specifies the root class to the search. By default, standard-db-object is used - which is the root for all view classes. - - - - - database - - - The database to search for view classes. - - - - - classes - - + + + + + root-class + + + specifies the root class to the search. By default, + standard-db-object is used which + is the root for all view classes. + + + + + database + + + The database to + search for view classes. This will default to the value + of *default-database*. + + + + + classes + + List of view classes. - - - + + + Description Returns a list of all the View Classes which have been defined in the Lisp session and are connected to - DATABASE, which defaults to - *DEFAULT-DATABASE*, and which descended - from the class ROOT-CLASS and which satisfy the function - TEST. By default ROOT-CLASS is STANDARD-DB-OBJECT and - TEST is IDENTITY. + database and which descended from the + class root-class and which satisfy the + function test. Examples - * (list-classes) - (#<clsql-sys::standard-db-class big> #<clsql-sys::standard-db-class employee-address> - #<clsql-sys::standard-db-class address> #<clsql-sys::standard-db-class company> - #<clsql-sys::standard-db-class employee>) +(list-classes) +=> (#<clsql-sys::standard-db-class big> #<clsql-sys::standard-db-class employee-address> + #<clsql-sys::standard-db-class address> #<clsql-sys::standard-db-class company> + #<clsql-sys::standard-db-class employee>) - * (list-classes :test #'(lambda (c) (> (length (symbol-name (class-name c))) 3))) - (#<clsql-sys::standard-db-class employee-address> #<clsql-sys::standard-db-class address> - #<clsql-sys::standard-db-class company> #<clsql-sys::standard-db-class employee>) +(list-classes :test #'(lambda (c) (> (length (symbol-name (class-name c))) 3))) +=> (#<clsql-sys::standard-db-class employee-address> #<clsql-sys::standard-db-class address> + #<clsql-sys::standard-db-class company> #<clsql-sys::standard-db-class employee>) @@ -659,9 +957,10 @@ Affected by - - Which view classes have been defined in the Lisp session. - + + Which view classes have been defined in the Lisp + session. + @@ -673,7 +972,7 @@ See Also - + def-view-class @@ -681,12 +980,10 @@ Notes - + None. - -