There were four significant problems with the way OpenACS previously used the
database (i.e., directly through the ns_db interface):
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
+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.
@@ -50,7 +50,7 @@
set_variables_after_subquery and subselection).
-
Hard-coded reliance on Oracle. It's difficult to +
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
@@ -68,9 +68,9 @@
3.3.1. The statement names supplied to each call are not used by the API at
all. The API's design for SQL abstraction is in fact incomplete;
unresolved issues include:
how to add WHERE clause criteria dynamically
how to build a dynamic ORDER BY clause (Ben Adida has a
-proposed solution for this)
how to define a statement's formal interface (i.e., what bind +proposed solution for this)
how to define a statement's formal interface (i.e., what bind
variables it expects, what columns its SELECT clause must
-contain if it's a query) without actually implementing the statement in a
+contain if it's a query) without actually implementing the statement in a
specific SQL dialect
So why is the incremental change of adding statement naming to the API worth the effort? It is worth the effort because we know that giving each SQL @@ -79,25 +79,25 @@ advantage of the new support for bind variables will already require code that uses 3.3.0 version of the API to be updated.
-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
+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:
db_1row select_names "select first_names, last_name from users where user_id = [ad_conn user_id]" doc_body_append "Hello, $first_names $last_name!"
-Like ns_db 1row, this will bomb if the query doesn't return
-any rows (no such user exists). If this isn't what you want, you can
+Like ns_db 1row, this will bomb if the query doesn't return
+any rows (no such user exists). If this isn't what you want, you can
write:
if { [db_0or1row select_names "select first_names, last_name from users where user_id = [ad_conn user_id]"] } {
doc_body_append "Hello, $first_names $last_name!"
} else {
# Executed if the query returns no rows.
- doc_body_append "There's no such user!"
+ doc_body_append "There's no such user!"
}
@@ -110,7 +110,7 @@ }
-That's right, db_foreach is now like ns_db
+That's right, db_foreach is now like ns_db
select plus a while loop plus
set_variables_after_query plus an if statement
(containing code to be executed if no rows are returned).
@@ -120,7 +120,7 @@
db_foreach select_names "select first_names, last_name from users where last_name like 'S%'" {
doc_body_append "Say hi to $first_names $last_name for me!<br>"
} if_no_rows {
- doc_body_append "There aren't any users with last names beginnings with S!"
+ doc_body_append "There aren't any users with last names beginnings with S!"
}
@@ -135,7 +135,7 @@ doc_body_append "<li>User $first_names $last_name\n<ul>" db_foreach select_groups "select group_id from user_group_map where user_id = $user_id" { - # There's a selection in progress, so we allocated a database handle + # There's a selection in progress, so we allocated a database handle # from the subquery pool for this selection. doc_body_append "<li>Member of group #$group_id.\n" } if_no_rows { @@ -147,12 +147,12 @@ db_release_unused_handles
-A new handle isn't actually allocated and released for every selection,
+A new handle isn't actually allocated and released for every selection,
of course - as a performance optimization, the API keeps old handles around
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.
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 @@ -163,7 +163,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
+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
data. A bind variable is specified as a colon followed by an identifier, so
@@ -177,7 +177,7 @@
When this SQL statement is invoked, the value for the bind variable
:some_presentation_id is pulled from the Tcl variable
-$some_presentation_id (in the caller's environment). Note
+$some_presentation_id (in the caller's environment). Note
that bind variables are not limited to one per statement; you can use an
arbitrary number, and each will pull from the correspondingly named Tcl
variable. (Alternatively, you can also specify an list or ns_set
@@ -187,7 +187,7 @@
variable, or to use db_quote to escape single-quotes contained
in the value. The following works fine, despite the apostrophe:
-set exclamation "That's all, folks!"
+set exclamation "That's all, folks!"
db_dml exclamation_insert { insert into exclamations(exclamation) values(:exclamation) }
Note that you can use a bind variable in a SQL statement only where you
@@ -223,7 +223,7 @@
eliminates this gaping security hole: since bind variable values are taken
literally. Oracle will attempt to delete presentations whose presentation ID
is literally '3 or 1 = 1' (i.e., no presentations, since
-'3 or 1 = 1' can't possibly be a valid integer
+'3 or 1 = 1' can't possibly be a valid integer
primary key for wp_presentations. In general, since Oracle
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
@@ -377,7 +377,7 @@
first_names is Jon. last_name is Salz.
first_names is Lars. last_name is Pind.
first_names is Michael. last_name is Yoon.
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
+db_release_unused_handles when you're done as a hint to
release the database handle.
db_null
@@ -413,7 +413,7 @@
column values. Raises an error if the query does not return exactly 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.
db_0or1row @@ -424,7 +424,7 @@ 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
+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
@@ -440,15 +440,15 @@
db_listdb_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
+query sql. If sql doesn't
return any rows, returns an empty list. Analogous to
database_to_tcl_list.
db_list_of_listsdb_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.
+sql doesn't return any rows, returns an empty list.
(Analogous to database_to_tcl_list_list.)
db_list_of_ns_sets
@@ -560,11 +560,11 @@
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
+ 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 variables defined
- by db_multirow will be set locally (useful if you're compiling dynamic templates
+ by db_multirow will be set locally (useful if 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
@@ -580,7 +580,7 @@
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
+ 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
@@ -612,7 +612,7 @@
db_with_handledb_with_handle var code_block
Places a database handle into the variable var and
-executes code_block. This is useful when you don't
+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 handles explicitly.
Example:
@@ -623,7 +623,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
@@ -677,7 +677,7 @@
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 + 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.