One of ACS'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 ACS. Our goal is to develop a coherent API for
database access which makes this even easier.
There were four significant problems with the way ACS previously used
the database (i.e., directly through the ns_db
interface):
It lays the groundwork for addressing the fourth problem by assigning
each SQL statement a logical name. In a future version of the ACS
Core, this API will translate logical statement names into actual SQL,
based on the type of database in use. (To smooth the learning curve,
we provide a facility for writing SQL inline for a "default SQL
dialect", which we assume to be Oracle for now.)
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 statement a logical name will be required by the complete SQL
abstraction 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.
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:
, 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:
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:
doc_body_append "<ul>"
db_foreach select_names "select first_names, last_name, user_id from users" {
# Automatically allocated a database handle from the main pool.
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
# from the subquery pool for this selection.
doc_body_append "<li>Member of group #$group_id.\n"
} if_no_rows {
# Not a member of any groups.
doc_body_append "<li>Not a member of any group.\n"
}
}
doc_body_append "</ul>"
db_release_unused_handles
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
which deletes every presentation in the database? We can get around this by
carefully checking every string included in a SQL query, but a safer way
is to use
. This enables the following syntax:
as above,
Oracle will attempt to delete presentations whose presentation ID is literally
.
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 dangerous queries and DML.
switch is omitted)
is that these procedures expect to find local
variables that correspond in name to the referenced bind variables,
e.g.:
set user_id 123456
set role "administrator"
db_foreach user_group_memberships_by_role {
select g.group_id, g.group_name
from user_groups g, user_group_map map
where g.group_id = map.user_id
and map.user_id = :user_id
and map.role = :role
} {
# do something for each group of which user 123456 is in the role
# of "administrator"
}
set bind_vars [ns_set create]
ns_set put $bind_vars user_id 123456
ns_set put $bind_vars role "administrator"
db_foreach user_group_memberships_by_role {
select g.group_id, g.group_name
from user_groups g, user_group_map map
where g.group_id = map.user_id
and map.user_id = :user_id
and map.role = :role
} -bind $bind_vars {
# do something for each group in which user 123456 has the role
# of "administrator"
}
db_foreach user_group_memberships_by_role {
select g.group_id, g.group_name
from user_groups g, user_group_map map
where g.group_id = map.user_id
and map.user_id = :user_id
and map.role = :role
} -bind [list user_id 123456 role "administrator"] {
# do something for each group in which user 123456 has the role
# of "administrator"
}
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.
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.
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
abstract DML difficult. Here is an example that illustrates why:
#
# Given the table:
#
# create table foo (
# bar integer,
# baz varchar(10)
# );
#
set bar ""
set baz ""
db_dml foo_create "insert into foo(bar, baz) values(:bar, :baz)"
#
# the values of the "bar" and "baz" columns in the new row are both
# null, because Oracle has coerced the empty string (even for the
# numeric column "bar") into null in both cases
Since databases other than Oracle do not coerce empty strings into
null, this code has different semantics depending on the
underlying database (i.e., the row that gets inserted may not have
null as 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 explicitly to null, e.g.:
set bar [db_null]
set baz [db_null]
db_dml foo_create "insert into foo(bar, baz) values(:bar, :baz)"
#
# sets the values for both the "bar" and "baz" columns to null
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:
db_foreach users_select "select first_names, last_name from users" {
doc_body_append "<li>$first_names $last_name\n"
}
However, sometimes this is not sufficient: you may need to examine the rows returned,
to dynamically determine the set of columns returned by the query, or to avoid collisions
with existing variables. You can use the -column_array and
-column_set switches to
db_foreach, db_0or1row, and db_1row
to instruct the database routines to place the results in a Tcl array or ns_set,
respectively, where the keys are the column names and the values are the column values.
For example:
db_foreach users_select "select first_names, last_name from users" -column_set columns {
# Now $columns is an ns_set.
doc_body_append "<li>"
for { set i 0 } { $i < [ns_set size $columns] } { incr i } {
doc_body_append "[ns_set key $columns $i] is [ns_set value $columns $i]. \n"
}
}
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
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.
