Database Access API

ACS Documentation : ACS Core Architecture Guide : Database Access API

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

The Big Picture

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 three significant problems with the way ACS 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 which of the three "magic pools" (main, subquery, and log) to allocate a new handle from.
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 routine which needed to operate transactionally, the semantics were non-obvious. Consider:
```
proc foo { db args } {
    ns_db dml $db "begin transaction"
      ...
    ns_db dml $db "end transaction"
}

ns_db dml $db "begin transaction"
ns_db dml $db "insert into greeble(bork) values(33)"
foo $db
ns_db dml $db "insert into greeble(bork) values(50)"
ns_db dml $db "end transaction"
```
This would insert greeble #33 and do all the stuff in foo transactionally, but the end transaction in foo would actually cause a commit, and greeble #50 would later be inserted in auto-commit mode. This could cause subtle bugs: e.g., in the case that the insert for greeble #50 failed, part of the "transaction" would have already have been committed!. This is not a good thing.
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).

The high-level database API addresses these problems by

Making use of database handles transparent.
Wrapping common database operations (including transaction management) in Tcl control structures. This is, after all, what Tcl is good at!

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:

db_1row "select first_names, last_name from users where user_id = [ad_get_user_id]"
ns_write "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 write:

if { [db_0or1row "select first_names, last_name from users where user_id = [ad_get_user_id]"] } {
    ns_write "Hello, $first_names $last_name!"
} else {
    # Executed if the query returns no rows.
    ns_write "There's no such user!"
}

Selecting a bunch of rows is a lot prettier now:

db_foreach "select first_names, last_name from users" {
     ns_write "Say hi to $first_names $last_name for me!<br>"
}

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

db_foreach "select first_names, last_name from users where last_name like 'S%'" {
     ns_write "Say hi to $first_names $last_name for me!<br>"
} if_no_rows {
     ns_write "There aren't any users with last names beginnings with S!"
}

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:

ad_write "<ul>"
db_foreach "select first_names, last_name, user_id from users" {
    # Automatically allocated a database handle from the main pool.
    ad_write "<li>User $first_names $last_name\n<ul>"

    db_foreach "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.
        ad_write "<li>Member of group #$group_id.\n"
    } if_no_rows {
        # Not a member of any groups.
        ad_write "<li>Not a member of any group.\n"
    }
}
ad_write "</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 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.

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_foreach sql code_block [ if_no_rows if_no_rows_block ]

Performs the SQL query sql, executing code_block once for each row with variables set to column values. If the query returns no rows, executes if_no_rows_block (if provided).

Example:

db_foreach "select foo, bar from greeble" {
    ns_write "<li>foo=$foo; bar=$bar\n"
} if_no_rows {
    ns_write "<li>There are no greebles in the database.\n"
}

db_1row sql

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

Example:

db_1row "select foo, bar from greeble where greeble_id = $greeble_id"
# Bombs if there's no such greeble!
# Now $foo and $bar are set.

db_0or1row sql

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 [ -default default ] sql

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_list sql

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 sql

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_dml [ -blobs blob_list | -clobs clob_list | -blob_files blob_file_list | -clob_files clob_file_list ] sql

Performs the DML or DDL statement sql.

If a length-n list of blobs or clobs is provided, then the SQL should return n blobs or clobs into the bind variables :1, :2, ... :n. blobs or clobs, if specified, should be a list of individual BLOBs or CLOBs to insert; blob_files or clob_files, if specified, should be a list of paths to files containing the data to insert. Only one of -blobs, -clobs, -blob_files, and -clob_files may be provided.

Example:

db_dml -blob_files [list "/var/tmp/the_photo" "/var/tmp/the_thumbnail"] \
    "
        insert photos(photo_id, image, thumbnail_image)
        values(photo_id_seq.nextval, empty_blob(), empty_blob())
        returning image, thumbnail_image into :1, :2
    "

This inserts a new row into the photos table, with the contents of the files /var/tmp/the_photo and /var/tmp/the_thumbnail in the image and thumbnail columns, respectively.

db_release_unused_handles

Releases any allocated, unused database handles.

db_transaction code_block

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 abort all levels of transactions.

Example:

proc replace_the_foo { col } {
    db_transaction {
        db_dml "delete from foo"
        db_dml "insert into foo(col) values($col)"
    }
}

proc print_the_foo {} {
    ad_write "foo is [db_string "select col from foo"]<br>\n"
}

replace_the_foo 8
print_the_foo ; # Writes out "foo is 8"

db_transaction {
    replace_the_foo 14
    print_the_foo ; # Writes out "foo is 14"
    db_dml "insert into some_other_table(col) values(999)"
      ...
    ad_abort_transaction
}

print_the_foo ; # Writes out "foo is 8"

db_resultrows

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

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, db_1row, etc.), but need to use database handles explicitly.

Example:

proc lookup_the_foo { foo } {
    db_with_handle db {
        return [database_to_tcl_string $db "select ..."]
    }
}

db_with_handle 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

        lookup_the_foo $foo
    }
}

jsalz@mit.edu