Database Access API

ACS Documentation : ACS Core Architecture Guide : Database Access API

Backward Incompatibility Alert

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

The Big Picture

There were four significant problems with the way ACS previously used the 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 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 } {
       db_transaction {
         ...
       }
   }
   
   db_transaction {
       db_dml unused "insert into greeble(bork) values(33)"
       foo $db
       db_dml unused "insert into greeble(bork) values(50)"
   }

   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.

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

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

1. making use of database handles transparent
2. wrapping common database operations (including transaction management) in Tcl control structures (this is, after all, what Tcl is good at!)

To be clear, SQL abstraction is not fully implemented in ACS 3.4. 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 variables it expects, what columns its SELECT clause must contain if it's a query) without actually implementing the statement in a specific SQL dialect

The Bell Tolls for set_variables_after_query

db_1row select_names "select first_names, last_name from users where user_id = [ad_get_user_id]"
doc_body_append "Hello, $first_names $last_name!"

if { [db_0or1row select_names "select first_names, last_name from users where user_id = [ad_get_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!"
}

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

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!"
}

Handle Management

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

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

Bind Variables

db_dml presentation_delete "delete from wp_presentations where presentation_id = $presentation_id"

delete from wp_presentations where presentation_id = 3 or 1 = 1

db_dml presentation_delete "
    delete from wp_presentations where presentation_id = :presentation_id
"

Every db_* command accepting a SQL command as an argument now 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 used as bind variables.

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"
}

The -bind switch can takes the name of an ns_set containing keys for each bind variable named in the query, e.g.:

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"
}

SQL Abstraction

Nulls and Bind Variables