Index: openacs-4/packages/acs-core-docs/www/db-api.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/db-api.adp,v diff -u -r1.1.2.5 -r1.1.2.6 --- openacs-4/packages/acs-core-docs/www/db-api.adp 9 Jun 2016 08:44:49 -0000 1.1.2.5 +++ openacs-4/packages/acs-core-docs/www/db-api.adp 23 Jun 2016 08:32:45 -0000 1.1.2.6 @@ -11,17 +11,17 @@

The OpenACS Database Access API

By Pete Su and Jon Salz. Modified by Roberto Mello.

-Overview

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 +Overview

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, and we have a coherent API for database access which makes this even easier.

More detailed information about the DB api is available at Database Access API.

DB API Examples

The OpenACS database API is meant to save developers from making common mistakes and to provide a more structured syntax for -specifying database operations, including transactions. Here's an -example of the API.

+specifying database operations, including transactions. Here's
+an example of the API.
 set count 0
 set tcl_var "foo"
 set sql {
@@ -116,9 +116,9 @@
 
 db_foreach my_query { select :table from some_table where :condition }
     
-
SQL will not allow a literal to occur where we've put the bind
-variables, so the query is syntactically incorrect. You have to
-remember that while the bind variable syntax looks similar to
+

SQL will not allow a literal to occur where we've put the +bind variables, so the query is syntactically incorrect. You have +to remember that while the bind variable syntax looks similar to variable interpolation in Tcl, It is not the same thing at all.

Finally, the DB API has several different styles for passing bind variable values to queries. In general, use the style @@ -194,11 +194,13 @@ into null. (This coercion does not occur in the WHERE clause of a query, i.e. -col = '' and col is null are not equivalent.)

As a result, when using bind variables, the only way to make +col = '' and +col is null are not +equivalent.)

As a result, when using bind variables, the only way to make Oracle set a column value to null is to set the corresponding bind variable to the empty string, since a bind variable whose value is -the string "null" will be interpreted as the literal string -"null".

These Oracle quirks complicate the process of writing clear and +the string "null" will be interpreted as the literal +string "null".

These Oracle quirks complicate the process of writing clear and abstract DML difficult. Here is an example that illustrates why:

 
@@ -268,17 +270,17 @@
 

 Basic API
The Database API has several functions that wrap familiar parts
 of the AOLserver database 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.

+and (if you want) call db_release_unused_handles when you're
+done as a hint to release the database handle.

 

 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 instead of db_dml "abort" "abort
-transaction".

+this instead of db_dml
+"abort" "abort transaction".

 

 db_multirow

 
@@ -296,10 +298,10 @@
 var_name:columns to a list of
 column names.
Each row also has a column, rownum, automatically added and set
 to the row number, starting with 1. Note that this will override
-any column in the SQL statement named 'rownum', also if you're
-using the Oracle rownum pseudo-column.
If the -local is passed, the
+any column in the SQL statement named 'rownum', also if
+you're using the Oracle rownum pseudo-column.
If the -local is passed, the
 variables defined by db_multirow will be set locally (useful if
-you're compiling dynamic templates in a function or similar
+you're compiling dynamic templates in a function or similar
 situations).
You may supply a code block, which will be executed for each row
 in the loop. This is very useful if you need to make computations
 that are better done in Tcl than in SQL, for example using
@@ -309,13 +311,13 @@
 copied back into the multirow.
You may also add additional, computed columns to the multirow,
 using the -extend { col_1col_2 ... } switch. This is
 useful for things like constructing a URL for the object retrieved
-by the query.
If you're constructing your multirow through multiple queries
-with the same set of columns, but with different rows, you can use
-the -append switch. This causes
-the rows returned by this query to be appended to the rows already
-in the multirow, instead of starting a clean multirow, as is the
-normal behavior. The columns must match the columns in the original
-multirow, or an error will be thrown.
Your code block may call continue in order to skip a row and not
+by the query.
If you're constructing your multirow through multiple
+queries with the same set of columns, but with different rows, you
+can use the -append switch.
+This causes the rows returned by this query to be appended to the
+rows already in the multirow, instead of starting a clean multirow,
+as is the normal behavior. The columns must match the columns in
+the original multirow, or an error will be thrown.
Your code block may call continue in order to skip a row and not
 include it in the multirow. Or you can call break to skip this row and quit
 looping.
Notice the nonstandard numbering (everything else in Tcl starts
 at 0); the reason is that the graphics designer, a non programmer,
@@ -340,8 +342,8 @@
   lappend asset_id_l $asset_id
 }
           
-
Technically it's equivalent to using a code block on the end of
-your db_multirow.

+

Technically it's equivalent to using a code block on the end +of your db_multirow.

db_null
db_null

Returns a value which can be used in a bind variable to @@ -381,7 +383,7 @@ 1 row.

Example:

 
 db_1row select_foo "select foo, bar from greeble where greeble_id = $greeble_id"
-# Bombs if there's no such greeble!
+# Bombs if there's no such greeble!
 # Now $foo and $bar are set.
 
           
@@ -417,7 +419,7 @@
 db_string statement-namesql [ -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, +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.

@@ -428,7 +430,7 @@

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 +doesn't return any rows, returns an empty list. Analogous to database_to_tcl_list.

db_list_of_lists
@@ -437,8 +439,8 @@

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.)

+If sql doesn't return any +rows, returns an empty list. (Analogous to database_to_tcl_list_list.)

db_dml
@@ -497,8 +499,8 @@

Executes code_block transactionally. Nested transactions are supported (end -transaction is transparently ns_db dml'ed when the outermost transaction -completes). The db_abort_transaction command can be used to +transaction is transparently ns_db dml'ed when the outermost +transaction completes). The db_abort_transaction command can be used to abort all levels of transactions. It is possible to specify an optional on_error code block that will be executed if some code in code_block throws an exception. The @@ -548,7 +550,7 @@ db_with_handle varcode_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, db_1row, etc.), but need to use database +you don't want to have to use the new API (db_foreach, db_1row, etc.), but need to use database handles explicitly.

Example:

 
 proc lookup_the_foo { foo } {
@@ -558,7 +560,7 @@
 }
 
 db_with_handle db {
-    # Now there's a database handle in $db.
+    # Now there's a database handle in $db.
     set selection [ns_db select $db "select foo from bar"]
     while { [ns_db getrow $db $selection] } {
         set_variables_after_query
@@ -588,7 +590,7 @@
 set n_rows [db_string unused "select count(*) from foo where baz is null"]
 #
 # $n_rows is 1; in effect, the "baz is null" criterion is matching
-# the empty string we just inserted (because of Oracle's coercion
+# the empty string we just inserted (because of Oracle's coercion
 # quirk)
 
           
@@ -600,22 +602,23 @@
-
($‌Id: db-api.xml,v 1.13.8.1 2016/01/02 21:55:21 -gustafn Exp $)
+
($‌Id: db-api.html,v 1.50.2.11 2016/06/21 +07:44:35 gustafn Exp $)

Caching Database API Results

The database API allows for direct caching of query results. Repeated calls will return the cached value until it is either explicitly flushed using db_flush_cache, times out (configured the ns_cache is called to create the cache), or another cached query fills the cache, causing older entries to be flushed.

Values returned by a query are cached if you pass the -"-cache_key" switch to the database procedure. The switch value -will be used as the key in the ns_cache eval call used to execute -the query and processing code. The db_flush proc should be called -to flush the cache when appropriate. The "-cache_pool" parameter -can be used to specify the cache pool to be used, and defaults to -db_cache_pool. The size of the default cache is governed by the -kernel parameter "DBCacheSize" in the "caching" section.

Currently db_string, db_list, db_list_of_lists, db_1row, +"-cache_key" switch to the database procedure. The switch +value will be used as the key in the ns_cache eval call used to +execute the query and processing code. The db_flush proc should be +called to flush the cache when appropriate. The +"-cache_pool" parameter can be used to specify the cache +pool to be used, and defaults to db_cache_pool. The size of the +default cache is governed by the kernel parameter +"DBCacheSize" in the "caching" section.

Currently db_string, db_list, db_list_of_lists, db_1row, db_0or1row, and db_multirow support caching.

For caching to be effective, one must carefully design a cache_pool and cache_key strategy that uniquely identifies a query within the system, including the relevant objects being referenced