X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=doc%2Fref-ooddl.xml;h=891e213a70dcc09ad053aac05c94f4f8d0699eff;hp=3114dcf6b6069cc07a01f9644f7e0a8ac6492829;hb=534849c88501e0ea2ee5dbf78d13d8cb73814d71;hpb=d80d8b58af5e3074bca3e8830a04efb2578c2f11 diff --git a/doc/ref-ooddl.xml b/doc/ref-ooddl.xml index 3114dcf..891e213 100644 --- a/doc/ref-ooddl.xml +++ b/doc/ref-ooddl.xml @@ -5,9 +5,9 @@ %myents; ]> - - - Object Oriented Data Definition Language (OODDL) + + + Object Oriented Data Definition Language (OODDL) The Object Oriented Data Definition Language (OODDL) provides @@ -15,9 +15,9 @@ (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 + 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 @@ -25,8 +25,10 @@ linkend="drop-view-from-class">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 above functions refer to the Lisp + view of the SQL table. This Lisp view + should not be confused with SQL VIEW + statement. @@ -57,11 +59,11 @@ Slots - + slot VIEW-DATABASE is of type (OR NULL DATABASE) which stores the associated database for the instance. - + @@ -76,31 +78,37 @@ Value Type Fixnum - + Initial Value 255 - Description + 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 @@ -109,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 @@ -125,13 +135,13 @@ 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 @@ -140,8 +150,9 @@ 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. @@ -149,7 +160,10 @@ 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*. @@ -157,7 +171,8 @@ 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. @@ -166,18 +181,21 @@ Description - Creates a table as defined by the View Class - view-class-name in 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") @@ -236,7 +254,7 @@ Syntax - (def-view-class name superclasses slots &rest class-options) => class + def-view-class name superclasses slots &rest class-options => class Arguments and Values @@ -250,7 +268,7 @@ - name + superclasses The superclasses for the defined class. @@ -265,6 +283,14 @@ + + class options + + + The class options. + + + class @@ -281,29 +307,30 @@ :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. + 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: + :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: @@ -356,13 +383,17 @@ - :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. + :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. @@ -380,25 +411,32 @@ option. The valid values are: - string - a variable length character field up to - *default-string-length* characters. + string - a variable length + character field up to *default-string-length* + characters. - (string n) - a fixed length character field - n characters long. + (string n) - a fixed length + character field n characters + long. - varchar - a variable length character field up to - *default-string-length* characters. + 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. + (varchar n) - a variable length + character field up to n + characters in length. - char - a single character field + char - a single character field - integer - signed integer at least 32-bits wide + integer - signed integer + at least 32-bits wide (integer n) float (float n) @@ -415,7 +453,7 @@ wide. [not supported by all database backends] - bigint - An integer column + bigint - An integer column 64-bits wide. [not supported by all database backends] @@ -426,18 +464,25 @@ 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. + 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. + duration - stores a + duration structure. &clsql; provides + routines for wall-time and + duration processing. - boolean - stores a &t; or &nil; value. + boolean - stores a &t; or + &nil; value. generalized-boolean - similar to a boolean in that either a @@ -450,17 +495,20 @@ 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. + 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 + vector - stores a vector + similarly to list + array - stores a array + similarly to list - + @@ -475,16 +523,21 @@ - :void-value - specifies - the value to store in the Lisp instance if the SQL value is NULL and defaults - to NIL. + :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 string - representing an SQL table constraint expression or a - list of such strings. + :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). @@ -495,6 +548,31 @@ slot value. + + + :db-reader - If a string, then when + reading values from the database, the string will be used + for a format string, with the only value being the value + from the database. The resulting string will be used as + the slot value. If a function then it will take one + argument, the value from the database, and return the + value that should be put into the slot. If a symbol, then + the symbol-function of the symbol will be used. + + + + + :db-writer - If a string, then when + reading values from the slot for the database, the string + will be used for a format string, with the only value + being the value of the slot. The resulting string will be + used as the column value in the database. If a function + then it will take one argument, the value of the slot, and + return the value that should be put into the database. If + a symbol, then the symbol-function of the symbol will be + used. + + @@ -503,9 +581,20 @@ - :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. + :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. + + + + + :normalizedp - specifies whether + this class uses normalized inheritance from parent classes. + Defaults to nil, i.e. non-normalized schemas. When true, + SQL database tables that map to this class and parent + classes are joined on their primary keys to get the full + set of database columns for this class. @@ -514,7 +603,8 @@ Description - Creates a View Class called name whose + 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 @@ -522,10 +612,131 @@ standard-db-object, otherwise superclasses is a list of superclasses for class which must include - standard-db-object or a descendent of this - class. + standard-db-object or a descendent of + this class. + + Normalized inheritance schemas + + Specifying that :normalizedp is T + tells &clsql; to normalize the database schema for inheritance. + What this means is shown in the examples below. + + + + With :normalizedp equal to NIL + (the default) the class inheritance would result in the following: + + +(def-view-class node () + ((title :accessor title :initarg :title :type (varchar 240)))) + +SQL table NODE: ++-------+--------------+------+-----+---------+-------+ +| Field | Type | Null | Key | Default | Extra | ++-------+--------------+------+-----+---------+-------+ +| TITLE | varchar(240) | YES | | NULL | | ++-------+--------------+------+-----+---------+-------+ + +(def-view-class user (node) + ((user-id :accessor user-id :initarg :user-id + :type integer :db-kind :key :db-constraints (:not-null)) + (nick :accessor nick :initarg :nick :type (varchar 64)))) + +SQL table USER: ++---------+--------------+------+-----+---------+-------+ +| Field | Type | Null | Key | Default | Extra | ++---------+--------------+------+-----+---------+-------+ +| USER_ID | int(11) | NO | PRI | | | +| NICK | varchar(64) | YES | | NULL | | +| TITLE | varchar(240) | YES | | NULL | | ++---------+--------------+------+-----+---------+-------+ + + + + Using :normalizedp T, both + view-classes need a primary key to join them on: + + +(def-view-class node () + ((node-id :accessor node-id :initarg :node-id + :type integer :db-kind :key + :db-constraints (:not-null)) + (title :accessor title :initarg :title :type (varchar 240)))) + +SQL table NODE: ++---------+--------------+------+-----+---------+-------+ +| Field | Type | Null | Key | Default | Extra | ++---------+--------------+------+-----+---------+-------+ +| NODE_ID | int(11) | NO | PRI | | | +| TITLE | varchar(240) | YES | | NULL | | ++---------+--------------+------+-----+---------+-------+ + +(def-view-class user (node) + ((user-id :accessor user-id :initarg :user-id + :type integer :db-kind :key :db-constraints (:not-null)) + (nick :accessor nick :initarg :nick :type (varchar 64))) + (:normalizedp t)) + +SQL table USER: ++---------+-------------+------+-----+---------+-------+ +| Field | Type | Null | Key | Default | Extra | ++---------+-------------+------+-----+---------+-------+ +| USER_ID | int(11) | NO | PRI | | | +| NICK | varchar(64) | YES | | NULL | | ++---------+-------------+------+-----+---------+-------+ + + + + In this second case, all slots of the view-class 'node + are also available in view-class 'user, and can be used + as one would expect. For example, with the above normalized + view-classes 'node and 'user, and SQL tracing turned on: + + +CLSQL> (setq test-user (make-instance 'user :node-id 1 :nick "test-user" + :title "This is a test user")) +]]> + +CLSQL> (update-records-from-instance test-user :database db) + INSERT INTO NODE (NODE_ID,TITLE) VALUES (1,'This is a test user') +;; .. <= T +;; .. => INSERT INTO USER (USER_ID,NICK) VALUES (1,'test-user') +;; .. <= T +1 +]]> + +CLSQL> (node-id test-user) +1 + +CLSQL> (title test-user) +"This is a test user" + +CLSQL> (nick test-user) +"test-user" + + + Notes from a refactor of this code. + + There are many assumptions that need to be met for normalized classes to work + + * The each of the classes should have its own single key column (of a different name) + that will contain an identical value. EG: node has a node-id, setting which + is a node has a node-id and a setting-id which must be equal. You cannot use + node-id as the primary key on both tables (as I would have expected). The exception + to this seems to be if your class has no slots at all, then you dont need to have a + single key column, because your class is fully represented in the db by its parent(s) + + * more than one parent class per normalized class should be considered experimental + and untested (vaya con Dios) + + * There are a few code paths that just dont pay any attention to normalized classes + eg: delete-records-for-instance + + + Examples @@ -541,7 +752,7 @@ (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 @@ -734,7 +945,7 @@ Syntax - (drop-view-from-class view-class-name &key (database *default-database*)) => + drop-view-from-class view-class-name &key database => Arguments and Values @@ -743,7 +954,18 @@ view-class-name - The name of the view class. + The name of the View + Class. + + + + + database + + + database + object. This will default to the value of + *default-database*. @@ -751,20 +973,22 @@ 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") @@ -799,7 +1023,7 @@ Notes - + None. @@ -813,7 +1037,7 @@ 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 @@ -831,8 +1055,9 @@ root-class - specifies the root class to the search. By default, standard-db-object is used - which is the root for all view classes. + specifies the root class to the search. By default, + standard-db-object is used which + is the root for all view classes. @@ -840,7 +1065,10 @@ database - The database to search for view classes. + The database to + search for view classes. This will default to the value + of *default-database*. @@ -866,14 +1094,14 @@ 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 :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) +=> (#<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>) @@ -886,7 +1114,8 @@ Affected by - Which view classes have been defined in the Lisp session. + Which view classes have been defined in the Lisp + session. @@ -904,7 +1133,13 @@ + + Notes + + None. + + - +