X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=doc%2Fref-fddl.xml;h=b48bd3b8395395d9202799e5d648227335631fb0;hb=bd6843e2084ce45d3d7b769e383c3cac589b5e93;hp=9606c53fc0a78bd6248ae4d525d14ea326076c4b;hpb=5248ae9f94d2022596ef8846e0581ea716607a84;p=clsql.git diff --git a/doc/ref-fddl.xml b/doc/ref-fddl.xml index 9606c53..b48bd3b 100644 --- a/doc/ref-fddl.xml +++ b/doc/ref-fddl.xml @@ -8,1663 +8,2609 @@ Functional Data Definition Language (FDDL) - + - + &clsql; provides a functional DDL which supports the creation + and destruction of a variety of database objects including + tables, views, indexes and sequences. Functions which return + information about currently defined database objects are also + provided. In addition, the FDDL includes functionality for + examining table attributes and attribute types. + + CREATE-TABLE + CREATE-TABLE - + Create a database table. Function Syntax - (CREATE-TABLE NAME DESCRIPTION &KEY (DATABASE *DEFAULT-DATABASE*) (CONSTRAINTS NIL) (TRANSACTIONS T)) [function] => + create-table name description &key database constraints transactions => Arguments and Values - + + name + + + The name of the table as a string, symbol or SQL expression. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + description + + + A list. + + + + + constraints + + + A string, a list of strings or &nil;. + + + + + transactions + + + A Boolean. The default value is &t;. + + + Description - Creates a table called NAME, which may be a string, - symbol or SQL table identifier, in DATABASE which defaults to - *DEFAULT-DATABASE*. DESCRIPTION is a list whose elements are - lists containing the attribute names, types, and other - constraints such as not-null or primary-key for each column in - the table. CONSTRAINTS is a string representing an SQL table - constraint expression or a list of such strings. With MySQL - databases, if TRANSACTIONS is t an InnoDB table is created - which supports transactions. - - - - Examples - - - - - - Side Effects - - - - - - Affected by - - - - - - - - Exceptional Situations - - - - - - See Also - - - - - - - - Notes + Creates a table called name, which + may be a string, symbol or SQL table identifier, in + database which defaults to + *default-database*. description + is a list whose elements are lists containing the attribute + names, types, and other constraints such as not-null or + primary-key for each column in the table. + - - - - - - - - DESCRIBE-TABLE - - Function - - - Syntax - - (DESCRIBE-TABLE TABLE &KEY DATABASE) [generic] => - - - Arguments and Values - - - - - - Description - Describes a table, returns a list of - name/type for columns in table + constraints is a string representing an + SQL table constraint expression or a list of such strings. + + + With MySQL databases, if transactions + is &t; an InnoDB table is created which supports transactions. Examples - +(create-table [foo] + '(([id] integer) + ([height] float) + ([name] (string 24)) + ([comments] text))) +=> +(table-exists-p [foo]) +=> T + +(create-table [foo] '(([bar] integer :not-null :unique :primary-key) + ([baz] string :not-null :unique))) +=> +(table-exists-p [foo]) +=> T + +(create-table [foo] '(([bar] integer :not-null) ([baz] string :not-null)) + :constraints '("UNIQUE (bar,baz)" "PRIMARY KEY (bar)")) +=> +(table-exists-p [foo]) +=> T Side Effects - + A table is created in database. Affected by - - - + *default-database* Exceptional Situations - + An error is signalled if name is not a + string, symbol or SQL expression. An error of type + sql-database-data-error is signalled if a + relation called name already exists. See Also - - - - - + + drop-table + list-tables + table-exists-p + Notes - + The constraints and + transactions keyword arguments to + create-table are &clsql; extensions. The + transactions keyword argument is for + compatibility with MySQL databases. - + + + DROP-TABLE + DROP-TABLE - + Drop a database table. Function Syntax - (DROP-TABLE NAME &KEY (IF-DOES-NOT-EXIST :ERROR) (DATABASE *DEFAULT-DATABASE*)) [function] => + drop-table name &key if-does-not-exist database => Arguments and Values - + + name + + + The name of the table as a string, symbol or SQL expression. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + if-does-not-exist + + + A symbol. Meaningful values are :ignore + or :error (the default). + + + Description - Drops the table called NAME from DATABASE which defaults - to *DEFAULT-DATABASE*. If the table does not exist and - IF-DOES-NOT-EXIST is :ignore then DROP-TABLE returns nil whereas - an error is signalled if IF-DOES-NOT-EXIST is :error. + Drops the table called name from + database which defaults to + *default-database*. If the table does not exist + and if-does-not-exist is + :ignore then drop-table + returns &nil; whereas an error is signalled if + if-does-not-exist is + :error. Examples - +(table-exists-p [foo]) +=> T +(drop-table [foo] :if-does-not-exist :ignore) +=> +(table-exists-p [foo]) +=> NIL Side Effects - + A table is dropped database. Affected by - - - - + *default-database* + Exceptional Situations - + An error is signalled if name is not a + string, symbol or SQL expression. An error of type + sql-database-data-error is signalled if + name doesn't exist and + if-does-not-exist has a value of + :error. See Also - - - - - + + create-table + list-tables + table-exists-p + Notes - + The if-does-not-exist keyword argument + to drop-table is a &clsql; extension. + + LIST-TABLES + LIST-TABLES - + Returns a list of database tables. Function Syntax - (LIST-TABLES &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + list-tables &key owner database => result Arguments and Values - + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + A list of strings. + + + Description - Returns a list of strings representing table names in DATABASE - which defaults to *DEFAULT-DATABASE*. OWNER is nil by default - which means that only tables owned by users are listed. If OWNER - is a string denoting a user name, only tables owned by OWNER are - listed. If OWNER is :all then all tables are listed. + Returns a list of strings representing table names in + database which defaults to + *default-database*. owner + is &nil; by default which means that only tables owned by users + are listed. If owner is a string denoting + a user name, only tables owned by owner + are listed. If owner is + :all then all tables are listed. Examples - +(list-tables :owner "fred") +=> ("type_table" "type_bigint" "employee" "company" "addr" "ea_join" "big") + +(list-tables :owner :all) +=> ("pg_description" "pg_group" "pg_proc" "pg_rewrite" "pg_type" "pg_attribute" + "pg_class" "pg_inherits" "pg_index" "pg_operator" "pg_opclass" "pg_am" + "pg_amop" "pg_amproc" "pg_language" "pg_largeobject" "pg_aggregate" + "pg_trigger" "pg_listener" "pg_cast" "pg_namespace" "pg_shadow" + "pg_conversion" "pg_depend" "pg_attrdef" "pg_constraint" "pg_database" + "type_table" "type_bigint" "employee" "company" "pg_statistic" "addr" + "ea_join" "big") Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + create-table + drop-table + table-exists-p + Notes - + None. + + TABLE-EXISTS-P + TABLE-EXISTS-P - + Tests for the existence of a database table. Function Syntax - (TABLE-EXISTS-P NAME &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + table-exists-p name &key owner database => result Arguments and Values - + + name + + + The name of the table as a string, symbol or SQL expression. + + + + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + A Boolean. + + + Description - Tests for the existence of an SQL table called NAME in - DATABASE which defaults to *DEFAULT-DATABASE*. OWNER is nil by - default which means that only tables owned by users are - examined. If OWNER is a string denoting a user name, only - tables owned by OWNER are examined. If OWNER is :all then all - tables are examined. + Tests for the existence of an SQL table called + name in database + which defaults to *default-database*. + owner is &nil; by default which means + that only tables owned by users are examined. If + owner is a string denoting a user name, + only tables owned by owner are + examined. If owner is + :all then all tables are examined. Examples - +(table-exists-p [foo]) +=> T Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + create-table + drop-table + list-tables + Notes - + None. + + CREATE-VIEW + CREATE-VIEW - + Create a database view. Function Syntax - (CREATE-VIEW NAME &KEY AS COLUMN-LIST (WITH-CHECK-OPTION NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + create-view name &key as column-list with-check-option database => Arguments and Values - + + name + + + The name of the view as a string, symbol or SQL expression. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + as + + + A symbolic SQL query expression. + + + + + column-list + + + A list. + + + + + with-check-option + + + A Boolean. + + + Description - Creates a view called NAME in DATABASE which defaults to - *DEFAULT-DATABASE*. The view is created using the query AS and - the columns of the view may be specified using the COLUMN-LIST - parameter. The WITH-CHECK-OPTION is nil by default but if it - has a non-nil value, then all insert/update commands on the - view are checked to ensure that the new data satisfy the query - AS. + Creates a view called name in + database which defaults to + *default-database*. The view is created using + the query as and the columns of the view + may be specified using the column-list + parameter. The with-check-option is &nil; + by default but if it has a non-&nil; value, then all + insert/update commands on the view are checked to ensure that + the new data satisfy the query as. Examples - +(create-view [lenins-group] + :as [select [first-name] [last-name] [email] + :from [employee] + :where [= [managerid] 1]]) +=> + +(select [*] :from [lenins-group]) +=> (("Josef" "Stalin" "stalin@soviet.org") + ("Leon" "Trotsky" "trotsky@soviet.org") + ("Nikita" "Kruschev" "kruschev@soviet.org") + ("Leonid" "Brezhnev" "brezhnev@soviet.org") + ("Yuri" "Andropov" "andropov@soviet.org") + ("Konstantin" "Chernenko" "chernenko@soviet.org") + ("Mikhail" "Gorbachev" "gorbachev@soviet.org") + ("Boris" "Yeltsin" "yeltsin@soviet.org") + ("Vladimir" "Putin" "putin@soviet.org")), + ("first_name" "last_name" "email") Side Effects - + A view is created in database. Affected by - - - + *default-database* Exceptional Situations - + An error is signalled if name is not a + string, symbol or SQL expression. An error of type + sql-database-data-error is signalled if a + relation called name already exists. See Also - - - - - + + drop-view + list-views + view-exists-p + Notes - + None. + + DROP-VIEW + DROP-VIEW - + Drops a database view. Function Syntax - (DROP-VIEW NAME &KEY (IF-DOES-NOT-EXIST :ERROR) (DATABASE *DEFAULT-DATABASE*)) [function] => + drop-view name &key if-does-not-exist database => Arguments and Values - + + name + + + The name of the view as a string, symbol or SQL expression. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + if-does-not-exist + + + A symbol. Meaningful values are :ignore + or :error (the default). + + + Description - Drops the view called NAME from DATABASE - which defaults to *DEFAULT-DATABASE*. If the view does - not exist and IF-DOES-NOT-EXIST is :ignore then - DROP-VIEW returns nil whereas an error is signalled if - IF-DOES-NOT-EXIST is :error. + Drops the view called name from + database which defaults to + *default-database*. If the view does not exist + and if-does-not-exist is + :ignore then drop-view + returns &nil; whereas an error is signalled if + if-does-not-exist is + :error. Examples - +(view-exists-p [foo]) +=> T +(drop-view [foo] :if-does-not-exist :ignore) +=> +(view-exists-p [foo]) +=> NIL Side Effects - + A view is dropped database. Affected by - - - - + *default-database* + Exceptional Situations - + An error is signalled if name is not a + string, symbol or SQL expression. An error of type + sql-database-data-error is signalled if + name doesn't exist and + if-does-not-exist has a value of + :error. See Also - - - - - + + create-view + list-views + view-exists-p + Notes - + The if-does-not-exist keyword argument + to drop-view is a &clsql; extension. + + LIST-VIEWS + LIST-VIEWS - + Returns a list of database views. Function Syntax - (LIST-VIEWS &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + list-views &key owner database => result Arguments and Values - + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + A list of strings. + + + Description - Returns a list of strings representing view - names in DATABASE which defaults to - *DEFAULT-DATABASE*. OWNER is nil by default which - means that only views owned by users are listed. If - OWNER is a string denoting a user name, only views - owned by OWNER are listed. If OWNER is :all then all - views are listed. + Returns a list of strings representing view names in + database which defaults to + *default-database*. owner + is &nil; by default which means that only views owned by users + are listed. If owner is a string denoting + a user name, only views owned by owner + are listed. If owner is + :all then all views are listed. Examples - +(list-views :owner "fred") +=> ("lenins_group") + +(list-views :owner :all) +=> ("pg_user" "pg_rules" "pg_views" "pg_tables" "pg_indexes" "pg_stats" + "pg_stat_all_tables" "pg_stat_sys_tables" "pg_stat_user_tables" + "pg_statio_all_tables" "pg_statio_sys_tables" "pg_statio_user_tables" + "pg_stat_all_indexes" "pg_stat_sys_indexes" "pg_stat_user_indexes" + "pg_statio_all_indexes" "pg_statio_sys_indexes" "pg_statio_user_indexes" + "pg_statio_all_sequences" "pg_statio_sys_sequences" + "pg_statio_user_sequences" "pg_stat_activity" "pg_stat_database" + "pg_locks" "pg_settings" "lenins_group") Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + create-view + drop-view + view-exists-p + Notes - + list-views is a &clsql; extension. + + VIEW-EXISTS-P + VIEW-EXISTS-P - + Tests for the existence of a database view. Function Syntax - (VIEW-EXISTS-P NAME &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + view-exists-p name &key owner database => result Arguments and Values - + + name + + + The name of the view as a string, symbol or SQL expression. + + + + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + A Boolean. + + + Description - Tests for the existence of an SQL view called NAME in - DATABASE which defaults to *DEFAULT-DATABASE*. OWNER is nil by - default which means that only views owned by users are - examined. If OWNER is a string denoting a user name, only - views owned by OWNER are examined. If OWNER is :all then all - views are examined. + Tests for the existence of an SQL view called + name in database + which defaults to *default-database*. + owner is &nil; by default which means + that only views owned by users are examined. If + owner is a string denoting a user name, + only views owned by owner are + examined. If owner is + :all then all views are examined. Examples - +(view-exists-p [lenins-group]) +=> T Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + create-view + drop-view + list-views + Notes - + view-exists-p is a &clsql; extension. + + CREATE-INDEX + CREATE-INDEX - + Create a database index. Function Syntax - (CREATE-INDEX NAME &KEY ON (UNIQUE NIL) ATTRIBUTES (DATABASE *DEFAULT-DATABASE*)) [function] => + create-index name &key on unique attributes database => Arguments and Values - + + name + + + The name of the index as a string, symbol or SQL expression. + + + + + on + + + The name of a table as a string, symbol or SQL expression. + + + + + unique + + + A Boolean. + + + + + attributes + + + A list of attribute names. + + + + + database + + + A database object which defaults to + *default-database*. + + + Description - Creates an index called NAME on the table specified by - ON in DATABASE which default to *DEFAULT-DATABASE*. The table - attributes to use in constructing the index NAME are specified - by ATTRIBUTES. The UNIQUE argument is nil by default but if it - has a non-nil value then the indexed attributes must have - unique values. + Creates an index called name on the + table specified by on in + database which default to + *default-database*. The table attributes to use + in constructing the index name are + specified by attributes. The + unique argument is &nil; by default but + if it has a non-&nil; value then the indexed attributes must + have unique values. Examples - +(create-index [bar] :on [employee] + :attributes '([first-name] [last-name] [email]) + :unique t) +=> + +(index-exists-p [bar]) +=> T Side Effects - + An index is created in database. Affected by - - - + *default-database* Exceptional Situations - + An error is signalled if name is not a + string, symbol or SQL expression. An error of type + sql-database-data-error is signalled if a + relation called name already exists. See Also - - - - - + + drop-index + list-indexes + index-exists-p + Notes - + None. + + DROP-INDEX + DROP-INDEX - + Drop a database index. Function Syntax - (DROP-INDEX NAME &KEY (IF-DOES-NOT-EXIST :ERROR) (ON NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + drop-index name &key if-does-not-exist on database => Arguments and Values - + + name + + + The name of the index as a string, symbol or SQL expression. + + + + + on + + + The name of a table as a string, symbol or SQL + expression. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + if-does-not-exist + + + A symbol. Meaningful values are :ignore + or :error (the default). + + + Description - Drops the index called NAME in DATABASE which - defaults to *DEFAULT-DATABASE*. If the index does not - exist and IF-DOES-NOT-EXIST is :ignore then DROP-INDEX - returns nil whereas an error is signalled if - IF-DOES-NOT-EXIST is :error. The argument ON allows - the optional specification of a table to drop the - index from. + Drops the index called name in + database which defaults to + *default-database*. If the index does not exist + and if-does-not-exist is + :ignore then drop-index + returns &nil; whereas an error is signalled if + if-does-not-exist is + :error. + + + The argument on allows the optional + specification of a table to drop the index from. This is + required for compatability with MySQL. Examples - +(index-exists-p [foo]) +=> T +(drop-index [foo] :if-does-not-exist :ignore) +=> +(index-exists-p [foo]) +=> NIL Side Effects - + An index is dropped in database. Affected by - - - - + *default-database* + Exceptional Situations - + An error is signalled if name is not a + string, symbol or SQL expression. An error of type + sql-database-data-error is signalled if + name doesn't exist and + if-does-not-exist has a value of + :error. See Also - - - - - + + create-index + list-indexes + index-exists-p + Notes - + The if-does-not-exist and + on keyword arguments to + drop-index are &clsql; extensions. The + keyword argument on is provided for + compatibility with MySQL. - + + + LIST-INDEXES + - INDEX-EXISTS-P - + LIST-INDEXES + Returns a list of database indexes. Function Syntax - (INDEX-EXISTS-P NAME &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + list-indexes &key onowner database => result Arguments and Values - + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + on + + + The name of a table as a string, symbol or SQL + expression, a list of such names or &nil;. + + + + + result + + + A list of strings. + + + Description - Tests for the existence of an SQL index - called NAME in DATABASE which defaults to - *DEFAULT-DATABASE*. OWNER is nil by default which - means that only indexes owned by users are - examined. If OWNER is a string denoting a user name, - only indexes owned by OWNER are examined. If OWNER is - :all then all indexes are examined. + Returns a list of strings representing index names in + database which defaults to + *default-database*. owner + is &nil; by default which means that only indexes owned by users + are listed. If owner is a string denoting + a user name, only indexes owned by owner + are listed. If owner is + :all then all indexes are listed. + + + The keyword argument on limits the + results to indexes on the specified tables. Meaningful values + for on are &nil; (the default) which + means that all tables are considered, a string, symbol or SQL + expression representing a table name in + database or a list of such table + identifiers. Examples - +(list-indexes) +=> ("employeepk" "companypk" "addrpk" "bar") + +(list-indexes :on '([addr] [company])) +=> ("addrpk" "companypk") Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + create-index + drop-index + index-exists-p + Notes - + list-indexes is a &clsql; extension. - + + + INDEX-EXISTS-P + - LIST-INDEXES - + INDEX-EXISTS- + Tests for the existence of a database index. Function Syntax - (LIST-INDEXES &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + index-exists-p name &key owner database => result Arguments and Values - + + name + + + The name of the index as a string, symbol or SQL expression. + + + + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + A Boolean. + + + Description - Returns a list of strings representing index - names in DATABASE which defaults to - *DEFAULT-DATABASE*. OWNER is nil by default which - means that only indexes owned by users are listed. If - OWNER is a string denoting a user name, only indexes - owned by OWNER are listed. If OWNER is :all then all - indexes are listed. + Tests for the existence of an SQL index called + name in database + which defaults to + *default-database*. owner + is &nil; by default which means that only indexes owned by users + are examined. If owner is a string + denoting a user name, only indexes owned by + owner are examined. If + owner is :all then all + indexes are examined. Examples - +(index-exists-p [bar]) +=> T Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + create-index + drop-index + list-indexes + Notes - + index-exists-p is a &clsql; extension. - + + + ATTRIBUTE-TYPE + - LIST-TABLE-INDEXES - + ATTRIBUTE-TYPE + Returns the type of the supplied attribute. Function Syntax - (LIST-TABLE-INDEXES TABLE &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + attribute-type attribute table &key owner database => type, precision, scale, nulls-accepted Arguments and Values - + + attribute + + + The name of the index as a string, symbol or SQL expression. + + + + + table + + + The name of a table as a string, symbol or SQL expression. + + + + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + type + + + A keyword symbol denoting a vendor-specific SQL type. + + + + + precision + + + An integer denoting the precision of the attribute type + or &nil;. + + + + + scale + + + An integer denoting the scale of the attribute type + or &nil;. + + + + + nulls-accepted + + + 0 or 1. + + + Description - Returns a list of strings representing index - names on the table specified by TABLE in DATABASE - which defaults to *DEFAULT-DATABASE*. OWNER is nil by - default which means that only indexes owned by users - are listed. If OWNER is a string denoting a user name, - only indexes owned by OWNER are listed. If OWNER is - :all then all indexes are listed. + Returns a keyword symbol representing the vendor-specific + field type of the supplied attribute + attribute in the table specified by + table in database + which defaults to + *default-database*. owner + is &nil; by default which means that the attribute specified by + attribute, if it exists, must be user + owned else &nil; is returned. If owner is + a string denoting a user name, the attribute, if it exists, must + be owned by owner else &nil; is returned, + whereas if owner is :all + then the attribute, if it exists, will be returned regardless of + its owner. + + Other information is also returned. The second value is + the type precision, the third is the scale and the fourth + represents whether or not the attribute accepts null values (a + value of 0) or not (a value of 1). + Examples - +(attribute-type [emplid] [employee]) +=> :INT4, 4, NIL, 0 Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + list-attributes + list-attribute-types + Notes - + None. - - + + + LIST-ATTRIBUTE-TYPES + - ATTRIBUTE-TYPE - + LIST-ATTRIBUTE-TYPES + Returns information about the attribute types of a table. Function Syntax - (ATTRIBUTE-TYPE ATTRIBUTE TABLE &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + list-attribute-types table &key owner database => result Arguments and Values - + + table + + + The name of a table as a string, symbol or SQL expression. + + + + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + A list. + + + Description - Returns a string representing the field type of the - supplied attribute ATTRIBUTE in the table specified by TABLE - in DATABASE which defaults to *DEFAULT-DATABASE*. OWNER is nil - by default which means that the attribute specified by - ATTRIBUTE, if it exists, must be user owned else nil is - returned. If OWNER is a string denoting a user name, the - attribute, if it exists, must be owned by OWNER else nil is - returned, whereas if OWNER is :all then the attribute, if it - exists, will be returned regardless of its owner. + Returns a list containing information about the SQL types + of each of the attributes in the table specified by + table in database + which has a default value of + *default-database*. owner + is &nil; by default which means that only attributes owned by + users are listed. If owner is a string + denoting a user name, only attributes owned by + owner are listed. If + owner is :all then all + attributes are listed. The elements of the returned list are + lists where the first element is the name of the attribute, the + second element is its SQL type, the third is the type precision, + the fourth is the scale of the attribute and the fifth is 1 if + the attribute accepts null values and otherwise 0. Examples - +(list-attribute-types [employee]) +=> (("emplid" :INT4 4 NIL 0) ("groupid" :INT4 4 NIL 0) + ("first_name" :VARCHAR 30 NIL 1) ("last_name" :VARCHAR 30 NIL 1) + ("email" :VARCHAR 100 NIL 1) ("ecompanyid" :INT4 4 NIL 1) + ("managerid" :INT4 4 NIL 1) ("height" :FLOAT8 8 NIL 1) + ("married" :BOOL 1 NIL 1) ("birthday" :TIMESTAMP 8 NIL 1) + ("bd_utime" :INT8 8 NIL 1)) Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + attribute-type + list-attribute-types + Notes - + None. - + + + LIST-ATTRIBUTES + - LIST-ATTRIBUTE-TYPES - + LIST-ATTRIBUTES + Returns the attributes of a table as a list. Function Syntax - (LIST-ATTRIBUTE-TYPES TABLE &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + list-attributes name &key owner database => result Arguments and Values - + + name + + + The name of a table as a string, symbol or SQL expression. + + + + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + A list. + + + Description - Returns a list containing information about - the SQL types of each of the attributes in the table - specified by TABLE in DATABASE which has a default - value of *DEFAULT-DATABASE*. OWNER is nil by default - which means that only attributes owned by users are - listed. If OWNER is a string denoting a user name, - only attributes owned by OWNER are listed. If OWNER is - :all then all attributes are listed. The elements of - the returned list are lists where the first element is - the name of the attribute, the second element is its - SQL type, the third is the type precision, the fourth - is the scale of the attribute and the fifth is 1 if - the attribute accepts null values and otherwise - 0. + Returns a list of strings representing the attributes of + table name in + database which defaults to + *default-database*. owner + is &nil; by default which means that only attributes owned by + users are listed. If owner is a string + denoting a user name, only attributes owned by + owner are listed. If + owner is :all then all + attributes are listed. Examples - +(list-attributes [employee]) +=> ("emplid" "groupid" "first_name" "last_name" "email" "ecompanyid" "managerid" + "height" "married" "birthday" "bd_utime") Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + attribute-type + list-attribute-types + Notes - + None. - + + + CREATE-SEQUENCE + - LIST-ATTRIBUTES - + CREATE-SEQUENCE + Create a database sequence. Function Syntax - (LIST-ATTRIBUTES NAME &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + create-sequence name &key database => Arguments and Values - + + name + + + The name of the sequence as a string, symbol or SQL expression. + + + + + database + + + A database object which defaults to + *default-database*. + + + Description - Returns a list of strings representing the - attributes of table NAME in DATABASE which defaults to - *DEFAULT-DATABASE*. OWNER is nil by default which - means that only attributes owned by users are - listed. If OWNER is a string denoting a user name, - only attributes owned by OWNER are listed. If OWNER is - :all then all attributes are listed. + Creates a sequence called name in + database which defaults to + *default-database*. Examples - +(create-sequence [foo]) +=> +(sequence-exists-p [foo]) +=> T Side Effects - + A sequence is created in database. Affected by - - - + *default-database* Exceptional Situations - + An error is signalled if name is not a + string, symbol or SQL expression. An error of type + sql-database-data-error is signalled if a + relation called name already exists. See Also - - - - - + + drop-sequence + list-sequences + sequence-exists-p + sequence-last + sequence-next + set-sequence-position + Notes - + create-sequence is a &clsql; extension. - - + + + DROP-SEQUENCE + - CREATE-SEQUENCE - + DROP-SEQUENCE + Drop a database sequence. Function Syntax - (CREATE-SEQUENCE NAME &KEY (DATABASE *DEFAULT-DATABASE*)) [function] => + drop-sequence name &key if-does-not-exist database => Arguments and Values - + + name + + + The name of the sequence as a string, symbol or SQL expression. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + if-does-not-exist + + + A symbol. Meaningful values are :ignore + or :error (the default). + + + Description - Creates a sequence called NAME in DATABASE which - defaults to *DEFAULT-DATABASE*. + Drops the sequence called name from + database which defaults to + *default-database*. If the sequence does not + exist and if-does-not-exist is + :ignore then + drop-sequence returns &nil; whereas an + error is signalled if if-does-not-exist + is :error. Examples - +(sequence-exists-p [foo]) +=> T +(drop-sequence [foo] :if-does-not-exist :ignore) +=> +(sequence-exists-p [foo]) +=> NIL Side Effects - + A sequence is dropped from database. Affected by - - - - + *default-database* + Exceptional Situations - + An error is signalled if name is not a + string, symbol or SQL expression. An error of type + sql-database-data-error is signalled if + name doesn't exist and + if-does-not-exist has a value of + :error. See Also - - - - - + + create-sequence + list-sequences + sequence-exists-p + sequence-last + sequence-next + set-sequence-position + Notes - + drop-sequence is a &clsql; extension. - + + + LIST-SEQUENCES + - DROP-SEQUENCE - + LIST-SEQUENCES + Returns a list of database sequences. Function Syntax - (DROP-SEQUENCE NAME &KEY (IF-DOES-NOT-EXIST :ERROR) (DATABASE *DEFAULT-DATABASE*)) [function] => + list-sequences &key owner database => result Arguments and Values - + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + A list of strings. + + + Description - Drops the sequence called NAME from DATABASE - which defaults to *DEFAULT-DATABASE*. If the sequence - does not exist and IF-DOES-NOT-EXIST is :ignore then - DROP-SEQUENCE returns nil whereas an error is - signalled if IF-DOES-NOT-EXIST is :error. + Returns a list of strings representing sequence names in + database which defaults to + *default-database*. owner + is &nil; by default which means that only sequences owned by + users are listed. If owner is a string + denoting a user name, only sequences owned by + owner are listed. If + owner is :all then all + sequences are listed. Examples - +(list-sequences) +=> ("foo") Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + create-sequence + drop-sequence + sequence-exists-p + sequence-last + sequence-next + set-sequence-position + Notes - + list-sequences is a &clsql; extension. - + + + SEQUENCE-EXISTS-P + - LIST-SEQUENCES - + SEQUENCE-EXISTS-P + Tests for the existence of a database sequence. Function Syntax - (LIST-SEQUENCES &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + sequence-exists-p name &key owner database => result Arguments and Values - + + name + + + The name of the sequence as a string, symbol or SQL expression. + + + + + owner + + + A string, &nil; or :all. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + A Boolean. + + + Description - Returns a list of strings representing - sequence names in DATABASE which defaults to - *DEFAULT-DATABASE*. OWNER is nil by default which - means that only sequences owned by users are - listed. If OWNER is a string denoting a user name, - only sequences owned by OWNER are listed. If OWNER is - :all then all sequences are listed. + Tests for the existence of an SQL sequence called + name in database + which defaults to + *default-database*. owner + is &nil; by default which means that only sequences owned by + users are examined. If owner is a string + denoting a user name, only sequences owned by + owner are examined. If + owner is :all then all + sequences are examined. Examples - +(sequence-exists-p [foo]) +=> NIL Side Effects - + None. Affected by - - - - + *default-database* + Exceptional Situations - + None. See Also - - - - - + + create-sequence + drop-sequence + list-sequences + sequence-last + sequence-next + set-sequence-position + Notes - + sequence-exists-p is a &clsql; extension. - + + + SEQUENCE-LAST + - SEQUENCE-EXISTS-P - + SEQUENCE-LAST + Return the last element in a database sequence. Function Syntax - (SEQUENCE-EXISTS-P NAME &KEY (OWNER NIL) (DATABASE *DEFAULT-DATABASE*)) [function] => + sequence-last name &key database => result Arguments and Values - + + name + + + The name of the sequence as a string, symbol or SQL expression. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + An integer. + + + Description - Tests for the existence of an SQL sequence called NAME in - DATABASE which defaults to *DEFAULT-DATABASE*. OWNER is nil by - default which means that only sequences owned by users are - examined. If OWNER is a string denoting a user name, only - sequences owned by OWNER are examined. If OWNER is :all then all - sequences are examined. + Return the last value allocated in the sequence called + name in database + which defaults to *default-database*. Examples - +(sequence-last [foo]) +=> 1 Side Effects - + None. Affected by - - - + The current value stored in database sequence + name. + + + *default-database* Exceptional Situations - + Will signal an error of type + sql-database-data-error if a sequence called + name does not exist in + database. See Also - - - - - + + create-sequence + drop-sequence + list-sequences + sequence-exists-p + sequence-next + set-sequence-position + Notes - + sequence-last is a &clsql; extension. - + + + SEQUENCE-NEXT + - SEQUENCE-LAST - + SEQUENCE-NEXT + Increment the value of a database sequence. Function Syntax - (SEQUENCE-LAST NAME &KEY (DATABASE *DEFAULT-DATABASE*)) [function] => + sequence-next name &key database => result Arguments and Values - + + name + + + The name of the sequence as a string, symbol or SQL expression. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + An integer. + + + Description - Return the last value of the sequence called NAME in DATABASE - which defaults to *DEFAULT-DATABASE*. + Increment and return the value of the sequence called + name in database + which defaults to *default-database*. Examples - +(sequence-last [foo]) +=> 3 +(sequence-next [foo]) +=> 4 +(sequence-next [foo]) +=> 5 +(sequence-next [foo]) +=> 6 Side Effects - + Modifies the value of the sequence name + in database. Affected by - - - + The current value stored in database sequence + name. + + + *default-database* Exceptional Situations - + Will signal an error of type + sql-database-data-error if a sequence called + name does not exist in + database. See Also - - - - - + + create-sequence + drop-sequence + list-sequences + sequence-exists-p + sequence-last + set-sequence-position + Notes - + sequence-next is a &clsql; extension. - + + + SET-SEQUENCE-POSITION + - SEQUENCE-NEXT - + SET-SEQUENCE-POSITION + Sets the position of a database sequence. Function Syntax - (SEQUENCE-NEXT NAME &KEY (DATABASE *DEFAULT-DATABASE*)) [function] => + set-sequence-position name position &key database => result Arguments and Values - + + name + + + The name of the sequence as a string, symbol or SQL expression. + + + + + position + + + An integer. + + + + + database + + + A database object which defaults to + *default-database*. + + + + + result + + + An integer. + + + Description - Return the next value in the sequence called NAME in DATABASE - which defaults to *DEFAULT-DATABASE*. + Explicitly set the position of the sequence called + name in database, + which defaults to *default-database*, to + position which is returned. Examples - +(sequence-last [foo]) +=> 4 +(set-sequence-position [foo] 50) +=> 50 +(sequence-next [foo]) +=> 51 Side Effects - + Modifies the value of the sequence name + in database. Affected by - - - - + *default-database* + Exceptional Situations - + Will signal an error of type + sql-database-data-error if a sequence called + name does not exist in + database. See Also - - - - - + + create-sequence + drop-sequence + list-sequences + sequence-exists-p + sequence-last + sequence-next + Notes - + set-sequence-position is a &clsql; extension. - + + + TRUNCATE-DATABASE + - SET-SEQUENCE-POSITION - + TRUNCATE-DATABASE + Drop all tables, views, indexes and sequences in a database. Function Syntax - (SET-SEQUENCE-POSITION NAME POSITION &KEY (DATABASE *DEFAULT-DATABASE*)) [function] => + truncate-database &key database => Arguments and Values - + + database + + A + database + object. This will default to the value + of *default-database*. + + Description - Explicitly set the the position of the sequence called NAME in - DATABASE, which defaults to *DEFAULT-DATABSE*, to POSITION. + + Drop all tables, views, indexes and sequences in + database which defaults to + *default-database*. Examples - +(list-tables) +=> ("type_table" "type_bigint" "employee" "company" "addr" "ea_join" "big") +(list-indexes) +=> ("employeepk" "companypk" "addrpk") +(list-views) +=> ("lenins_group") +(list-sequences) +=> ("foo" "bar") +(truncate-database) +=> +(list-tables) +=> NIL +(list-indexes) +=> NIL +(list-views) +=> NIL +(list-sequences) +=> NIL Side Effects - + Modifications are made to the underlying database. Affected by - - - + None. Exceptional Situations - + Signals an error of type sql-database-error if + database is not a database object. See Also - + drop-table + drop-view + drop-index + drop-sequence Notes - + truncate-database is a &clsql; extension.