Index: openacs-4/packages/acs-core-docs/www/db-api-detailed.html =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/db-api-detailed.html,v diff -u -r1.8.2.4 -r1.8.2.5 --- openacs-4/packages/acs-core-docs/www/db-api-detailed.html 7 Apr 2003 16:59:25 -0000 1.8.2.4 +++ openacs-4/packages/acs-core-docs/www/db-api-detailed.html 15 Apr 2003 17:03:01 -0000 1.8.2.5 @@ -1,24 +1,24 @@ -Database Access API
Prev Chapter 11. Kernel Documentation Next

Database Access API

+Database Access API

Prev Chapter�11.�Kernel Documentation Next

Database Access API

by Jon Salz. Revised and expanded by Roberto Mello (rmello at fslc dot usu dot edu), July 2002.
OpenACS docs are written by the named authors, but may be edited by OpenACS documentation staff. -

  • Tcl procedures: /packages/acs-kernel/10-database-procs.tcl

  • Tcl initialization: /packages/acs-kernel/database-init.tcl

The Big Picture

+

  • Tcl procedures: /packages/acs-kernel/10-database-procs.tcl

  • Tcl initialization: /packages/acs-kernel/database-init.tcl

The Big Picture

One of OpenACS's great strengths is that code written for it is very close to the database. It is very easy to interact with the database from anywhere within OpenACS. Our goal is to develop a coherent API for database access which makes this even easier.

There were four significant problems with the way OpenACS previously used the -database (i.e., directly through the ns_db interface):

  1. Handle management. We required code to pass database +database (i.e., directly through the ns_db interface):

    1. Handle management. We required code to pass database handles around, and for routines which needed to perform database access but didn't receive a database handle as input, it was difficult to know from which of the three "magic pools" (main, subquery, and log) to allocate a new handle. -

    2. Nested transactions. In our Oracle driver, begin +

    3. Nested transactions. In our Oracle driver, begin transaction really means "turn auto-commit mode off" and end transaction means "commit the current transaction and turn auto-commit mode on." Thus if transactional code needed to call a @@ -46,14 +46,14 @@ already have been committed!. This is not a good thing. -

    4. Unorthodox use of variables. The standard mechanism for +

    5. Unorthodox use of variables. The standard mechanism for mapping column values into variables involved the use of the set_variables_after_query routine, which relies on an uplevel variable named selection (likewise for set_variables_after_subquery and subselection). -

    6. Hard-coded reliance on Oracle. It's difficult to +

    7. Hard-coded reliance on Oracle. It's difficult to write code supporting various different databases (dynamically using the appropriate dialect based on the type of database being used, e.g., using DECODE on Oracle and CASE ... WHEN on @@ -81,7 +81,7 @@ design. Therefore, we know that the effort will not be wasted, and taking advantage of the new support for bind variables will already require code that uses 3.3.0 version of the API to be updated. -

    The Bell Tolls for set_variables_after_query

    +

    The Bell Tolls for set_variables_after_query

    set_variables_after_query is gone! (Well, it's still there, but you'll never need to use it.) The new API routines set local variables automatically. For instance: @@ -126,7 +126,7 @@ doc_body_append "There aren't any users with last names beginnings with S!" } -

    Handle Management

    +

    Handle Management

    The new API keeps track of which handles are in use, and automatically allocates new handles when they are necessary (e.g., to perform subqueries while a select is active). For example: @@ -155,7 +155,7 @@ until db_release_unused_handles is invoked (or the script terminates).

    Note that there is no analogue to ns_db gethandle - the -handle is always automatically allocated the first time it's needed.

    Bind Variables

    Introduction

    +handle is always automatically allocated the first time it's needed.

    Bind Variables

    Introduction

    Most SQL statements require that the code invoking the statement pass along data associated with that statement, usually obtained from the user. For instance, in order to delete a WimpyPoint presentation, a Tcl script might @@ -168,7 +168,7 @@ where some_presentation_id is a number which is a valid presentation ID of the presentation I want to delete. It's easy to write code handling situations like this since SQL statements can include -bind variables, which represent placeholders for actual +bind variables, which represent placeholders for actual data. A bind variable is specified as a colon followed by an identifier, so the statement above can be coded as: 

    @@ -201,7 +201,7 @@
 
 select * from :table_name
 
-

    Why Bind Variables Are Useful

    +

    Why Bind Variables Are Useful

    Why bother with bind variables at all - why not just write the Tcl statement above like this: 

    @@ -231,7 +231,7 @@
 always considers the values of bind variables to be literals, it becomes more
 difficult for users to perform URL surgery to trick scripts into running
 dangerous queries and DML. 
-
    Usage
    Every db_* command accepting a SQL command as an argument
+
    Usage
    Every db_* command accepting a SQL command as an argument
 supports bind variables. You can either
    • specify the -bind switch to provide a set with bind variable
 values, or
    • specify the -bind switch to explicitly provide a list of
 bind variable names and values, or
    • not specify a bind variable list at all, in which case Tcl variables are
@@ -292,7 +292,7 @@
     # of "administrator"
 }
 
-

    Nulls and Bind Variables

    +

    Nulls and Bind Variables

    When processing a DML statement, Oracle coerces empty strings into null. (This coercion does not occur in the WHERE clause of a query, i.e. @@ -330,7 +330,7 @@ its column values), which defeats the purpose of SQL abstraction.

    Therefore, the Database Access API provides a database-independent way to represent null (instead of the Oracle-specific idiom of the -empty string): db_null.

    Use it instead of the empty string whenever you want to set a column value +empty string): db_null.

    Use it instead of the empty string whenever you want to set a column value explicitly to null, e.g.:

     
 set bar [db_null]
@@ -340,13 +340,13 @@
 #
 # sets the values for both the "bar" and "baz" columns to null
 
-

    SQL Abstraction

    +

    SQL Abstraction

    We now require that each SQL statement be assigned a logical name for the statement that is unique to the procedure or page in which it is defined. This is so that (eventually) we can implement logically named statements with alternative SQL for non-Oracle databases (e.g., Postgres). More on this later. -

    Placing Column Values in Arrays and Sets

    +

    Placing Column Values in Arrays and Sets

    Normally, db_foreach, db_0or1row, and db_1row places the results of queries in Tcl variables, so you can say: @@ -377,21 +377,21 @@

    will write something like: -

    • first_names is Jon. last_name is Salz.

    • first_names is Lars. last_name is Pind.

    • first_names is Michael. last_name is Yoon.

    API

    +

    • first_names is Jon. last_name is Salz.

    • first_names is Lars. last_name is Pind.

    • first_names is Michael. last_name is Yoon.

    API

    Note that you never have to use ns_db anymore (including ns_db gethandle)! Just start doing stuff, and (if you want) call db_release_unused_handles when you're done as a hint to release the database handle. -

    db_null +

    db_null 
    -db_null
+db_null

    Returns a value which can be used in a bind variable to represent the SQL value null. See Nulls and Bind Variables above.

    -db_foreach +db_foreach 
    -db_foreach statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \
+db_foreach statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \
     [ -column_array array_name | -column_set set_name ] \
     code_block [ if_no_rows if_no_rows_block ]

    Performs the SQL query sql, executing @@ -409,8 +409,8 @@

    The code block may contain break statements (which terminate the loop and flush the database handle) and continue statements -(which continue to the next row of the loop).

    db_1row
    -db_1row statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \
+(which continue to the next row of the loop).
    db_1row
    +db_1row statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \
     [ -column_array array_name | -column_set set_name ]

    Performs the SQL query sql, setting variables to column values. Raises an error if the query does not return exactly 1 row.

    Example:

    @@ -419,48 +419,48 @@
 # Bombs if there's no such greeble!
 # Now $foo and $bar are set.
 
-
    db_0or1row 
    -db_0or1row statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \
+
    db_0or1row 
    +db_0or1row statement-name sql [ -bind bind_set_id | -bind bind_value_list ] \
     [ -column_array array_name | -column_set set_name ]

    Performs the SQL query sql. If a row is returned, sets variables to column values and returns 1. If no rows are returned, -returns 0. If more than one row is returned, throws an error.

    db_string 
    -db_string statement-name sql [ -default default ] [ -bind bind_set_id | -bind bind_value_list ]
+returns 0. If more than one row is returned, throws an error.
    db_string 
    +db_string statement-name sql [ -default default ] [ -bind bind_set_id | -bind bind_value_list ]

    Returns the first column of the result of SQL query sql. If sql doesn't return a row, returns default (or throws an error if default is unspecified). Analogous to database_to_tcl_string and database_to_tcl_string_or_null. -

    db_nextval 
    -db_nextval sequence-name
+
    db_nextval 
    +db_nextval sequence-name

    Returns the next value for the sequence sequence-name (using a SQL statement like SELECT sequence-name.nextval FROM DUAL). If sequence pooling is enabled for the sequence, transparently uses a value from the pool if available to save a round-trip to the database. -

    db_list
    -db_list statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
+
    db_list
    +db_list statement-name sql [ -bind bind_set_id | -bind bind_value_list ]

    Returns a Tcl list of the values in the first column of the result of SQL query sql. If sql doesn't return any rows, returns an empty list. Analogous to database_to_tcl_list. -

    db_list_of_lists
    -db_list_of_lists statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
+
    db_list_of_lists
    +db_list_of_lists statement-name sql [ -bind bind_set_id | -bind bind_value_list ]

    Returns a Tcl list, each element of which is a list of all column values in a row of the result of SQL query sql. If sql doesn't return any rows, returns an empty list. (Analogous to database_to_tcl_list_list.) -

    db_list_of_ns_sets
    -db_list_of_ns_sets statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
+
    db_list_of_ns_sets
    +db_list_of_ns_sets statement-name sql [ -bind bind_set_id | -bind bind_value_list ]

    Returns a list of ns_sets with the values of each column of each row returned by the sql query specified. -

    db_dml
    -db_dml statement-name sql \
+
    db_dml
    +db_dml statement-name sql \
     [ -bind bind_set_id | -bind bind_value_list ] \
     [ -blobs blob_list | -clobs clob_list |
       -blob_files blob_file_list | -clob_files clob_file_list ]
@@ -488,22 +488,22 @@
    -db_write_clob, -db_write_blob, -db_blob_get_file +db_write_clob, +db_write_blob, +db_blob_get_file 
    -db_write_clob statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
+db_write_clob statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
 
-db_write_blob statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
+db_write_blob statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
 
-db_blob_get_file statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
+db_blob_get_file statement-name sql [ -bind bind_set_id | -bind bind_value_list ]

    Analagous to ns_ora write_clob/write_blob/blob_get_file. -

    db_release_unused_handles
    -db_release_unused_handles
-

    Releases any allocated, unused database handles.

    db_transaction
    -db_transaction code_block [ on_error { code_block } ]
+
    db_release_unused_handles
    +db_release_unused_handles
+

    Releases any allocated, unused database handles.

    db_transaction
    +db_transaction code_block [ on_error { code_block } ]

    Executes code_block transactionally. Nested transactions are supported (end transaction is transparently ns_db dml'ed when the outermost transaction completes). The @@ -540,16 +540,16 @@ print_the_foo ; # Writes out "foo is 8" -

    db_abort_transaction +
    db_abort_transaction 
    -db_abort_transaction
+db_abort_transaction

    Aborts all levels of a transaction. That is if this is called within several nested transactions, all of them are terminated. Use this insetead of db_dml "abort" "abort transaction". -

    db_multirow
    -db_multirow [ -local ] [ -append ] [ -extend column_list ] \
+
    db_multirow
    +db_multirow [ -local ] [ -append ] [ -extend column_list ] \
     var-name statement-name sql \
     [ -bind bind_set_id | -bind bind_value_list ] \
     code_block [ if_no_rows if_no_rows_block ]
@@ -606,14 +606,14 @@
 } {
     set user_url [acs_community_member_url -user_id $user_id]
 }
-
    db_resultrows
    -db_resultrows
+
    db_resultrows
    +db_resultrows

    Returns the number of rows affected or returned by the previous statement. -

    db_with_handle
    -db_with_handle var code_block
+
    db_with_handle
    +db_with_handle var code_block

    Places a database handle into the variable var and executes code_block. This is useful when you don't want to have to use the new API (db_foreach, @@ -636,92 +636,92 @@ }

    - + db_name - + 
    -			
+			
 				db_name
-			
+

    Returns the name of the database, as returned by the driver.

    - + db_type - + 
    -			
+			
 				db_type
-			
+

    Returns the RDBMS type (i.e. oracle, postgresql) this OpenACS installation is using. The nsv ad_database_type is set up during the bootstrap process.

    - + db_compatible_rdbms_p - + 
    -			db_compatible_rdbms_p db_type
+			db_compatible_rdbms_p db_type

    Returns 1 if the given db_type is compatible with the current RDBMS.

    - + db_package_supports_rdbms_p - + 
    -			db_package_supports_rdbms_p db_type_list
+			db_package_supports_rdbms_p db_type_list

    Returns 1 if db_type_list contains the current RDMBS type. A package intended to run with a given RDBMS must note this in it's package info file regardless of whether or not it actually uses the database.

    - + db_legacy_package_p - + 
    -			db_legacy_package_p db_type_list
+			db_legacy_package_p db_type_list

    Returns 1 if the package is a legacy package. We can only tell for certain if it explicitly supports Oracle 8.1.6 rather than the OpenACS more general oracle.

    - + db_version - + 
    -			db_version
+			db_version

    Returns the RDBMS version (i.e. 8.1.6 is a recent Oracle version; 7.1 a recent PostgreSQL version.

    - + db_current_rdbms - + 
    -			db_current_rdbms
+			db_current_rdbms

    Returns the current rdbms type and version.

    - + db_known_database_types - + 
    -			db_known_database_types
+			db_known_database_types

    Returns a list of three-element lists describing the database engines known to OpenACS. Each sublist contains the internal database name (used in file @@ -731,4 +731,4 @@ The nsv containing the list is initialized by the bootstrap script and should never be referenced directly by user code. Returns the current rdbms type and version. -

    ($Id$)
    Prev Home Next
    OpenACS 4.6.2 Package Manager Design Up OpenACS 4 Security Requirements
    rmello at fslc.usu.edu
    vinod@kurup.com
    View comments on this page at openacs.org
    +

($Id$)
Prev Home Next
OpenACS 4.6.2 Package Manager Design Up OpenACS 4 Security Requirements
rmello at fslc.usu.edu
vinod@kurup.com
View comments on this page at openacs.org