nstcl: Bringing the best of AOLserver and OpenACS to tclsh and wish

Michael A. Cleverly
[email protected]

A Brief Introduction

nstcl 1, 2 is a Tcl package which reimplements many of the useful APIs and commands from AOLserver3 and OpenACS4, making them available for use in Tcl/Tk applications and throughout scripts.

AOLserver

AOLserver is a multi-threaded webserver that embeds Tcl and is famous for it's high performance and pooled connections to various flavors of relational database management systems. AOLserver began life in 1994 as NaviServer, a commercial product from NaviSoft, but was renamed after NaviSoft was acquired by America Online. AOL initially distributed free binary versions of AOLserver and in 1999 released version 3.0 under an OSS license.5

OpenACS

OpenACS (the Open Architecture Community System) is "an advanced toolkit for building scalable, community-oriented web applications."6 Written largely in Tcl, and running on AOLserver against either Postgres or Oracle, it is often compared as the Tcl equivalent to Python's Zope.7

The OpenACS is an outgrowth of the ArsDigita Community System. The project began originally to make the ACS work with Postgres, so that the entire stack (AOLserver, Tcl, and database server) could be built using Open Source tools. After ArsDigita went from being a profitable company to taking upon millions in VC funding, and the new management decided to discontinue their Tcl product in favor of a (then) yet-to-be-developed Java version, the OpenACS community took over active development of both the Oracle and Postgres versions. Since then, OpenACS has made numerous advances while ArsDigita imploded in late 2001 and it's remaining assets sold to Red Hat in February of 2002. 8, 9, 10

An Overview of nstcl Functionality

nstcl is actually a collection of Tcl packages:

  1. nstcl-core
  2. nstcl-database
  3. nstcl-html
  4. nstcl-http
  5. nstcl-images
  6. nstcl-misc
  7. nstcl-nssets
  8. nstcl-sendmail
  9. nstcl-templating
  10. nstcl-time

In this paper we will look at the core, database and nssets packages, as they generally have the widest appeal. The other packages are more specialized (for instance, the html and http packages simplify the programming of web clients and robots11), but aren't likely to be as widely used, and so we will not cover them here.

The ns_set Data Structure

An ns_set is a data structure which shares properties of both arrays (key/value pairs) and lists (numerically indexed). In some ways it is similar to the new "Dictionary Values and Manipulators" proposed by TIP #111;12 however unlike dictionaries, an ns_set may contain multiple keys with the same name but with (possibly) differing values, and the order keys and values are stored is defined. Methods exist13 to create, copy, split, move, put, conditionally put, update, find, delete, and get (by key or by index position) the value of an ns_set.

Results from a database query fit nicely into ns_set's. Each row of data can be encapsulated in an ns_set, and there is no risk of losing data if multiple columns happen to have the same name.

Database Support in Tcl

The Tcl core does not provide any commands for accessing databases, leaving this task to extensions. An extension exists (in some cases multiple extensions) to just about every flavor of RDBMS.14

Database PlatformTcl ExtensionSource
InformixInformix/Tclhttp://isqltclsourceforge.net
MySQLmysqltclhttp://www.xdobry.de/mysqltcl
fbmysqlhttp://www.fastbase.co.nz/fbsql/index.html
ODBCtclodbchttp://sourceforge.net/projects/tclodbc
OracleOratclhttp://oratcl.sourceforge.net
Postgrespgtclhttps://www.postgresql.org/docs/7.4/static/pgtcl.html
pgin.tclhttp://pgintcl.sourceforge.net/
SolidSoltclhttp://bolts.guam.net/soltcl
SQLitetclsqlitehttp://www.hwaci.com/sw/sqlite
SybaseSybtclhttp://sybtcl.sourceforge.net

Each Tcl database extension tends to expose it's own unique API at the script level (examples are included in an appendix at the end of this paper). This introduces a "Tower of Babel" effect, forcing the script author up-front to either commit to a particular database or write an abstraction layer.

Database Support in Other Scripting Languages

Perl has a standard database interface package, Perl::DBI, which exposes a common API to the Perl programmer, regardless of the database platform, leaving the actual implemenation differences to a low-level driver.15

Python has defined a database API to "encourage similarity between the Python modules that are used to access databases."16 Python modules, which implement this API, exist for a variety of databases.17

PHP does not have a common database API.18 Like Tcl, various extensions exist, each providing it's own API.19 There are a number of database wrapper libraries for PHP. Some of the more prominent appear to be PEAR20 and Metabase21.

The ns_db API

AOLserver has had, since it's initial release in 1994, a database abstraction layer that is exposed at the Tcl level via the ns_db command. Each C coded database driver hooks into the ns_db API22. In nstcl, in contrast, an existing Tcl database extension is "wrapped" into the ns_db API by defining a mere six procedures (see the Implementation Details below). nstcl currently provides wrappers around mysqltcl, tclodbc, Oratcl, pgtcl (and pgin.tcl), Soltcl, tclsqlite, and Sybtcl.

Defining Database Pools

In the AOLserver configuration file, the appropriate database driver modules are loaded and one or more "database pools" are defined. A given pool connects to one specific database host with a specified username and password, and a maximum number of concurrent connections is defined.

Multiple pools can exist (even to the same database host) with any number of connections per pool. If more than one pool is defined, one of them may be designated as the "default pool."


            ns_section ns/db/pool/main
            ns_param Driver ora8
            ns_param Connections 8
            ns_param DataSource ""
            ns_param User michael
            ns_param Password ********
            ns_param Verbose Off
            
            ns_sections ns/db/pool/log
            ns_param Driver solid
            ns_param Connections 10
            ns_param DataSource {tcp localhost 1313}
            ns_param User michael
            ns_param Password ********
            ns_param Verbose Off

            ns_section ns/server/$servername/db
            ns_param pools main,log
            ns_param defaultpool main
        

In nstcl we define two commands to load and configure (so as not to burden all nstcl users with a separate configuration file a la AOLserver).


            ::nstcl::load_driver oracle
            ::nstcl::configure_pool -default oracle main 8 "" michael ********

            ::nstcl::load_driver solid
            ::nstcl::configure_pool solid log 10 "tcp localhost 1313" michael ********
        

Handle Management

Communication with the database is done by way of an opaque handle to the database, much like the opaque handle returned by Tcl's open or socket commands, and like each of the Tcl database extensions we review in the appendix.

To obtain a handle we use ns_db gethandle. When we are done we call ns_db releasehandle.


            set db [ns_db gethandle]
            #
            # do whatever with the handle ...
            #
            ns_db releasehandle $db
        

Data Manipulation and Data Definition Language Statements

DML (Data Manipulation Language) statements alter the contents of the database. Common examples include SQL commands such as update, insert, and delete. DDL (Data Definition Language) statements alter the structure of the database. Common examples include SQL commands such as create, alter, and drop. We use ns_db dml to execute DML and DDL statements.


            # Give all employees a 4% cost of living raise
            ns_db dml $db "update employees set salary = salary * 1.04"

            # Get rid of an index
            ns_db dml $db "drop index employees"
        

Retrieving the Result(s) of a Query

The principal command for running a query is ns_db select. It returns an ns_set that we'll use to fetch the data from each row using ns_db getrow.


            set data [ns_db select $db "select name, salary from employees"]

            while {[ns_db getrow $db $data]} {
                set name   [ns_set get $data name]
                set salary [ns_set get $data salary]

                puts "$name's new salary is \$[format %0.2f $salary]"
            }
        

There are two convenience methods, ns_db 0or1row and ns_db 1row that save us from having to call ns_db getrow when we know that our query will always return at most one row.

            
            set data [ns_db 0or1row $db "
                select name 
                  from employees 
                 where employee_id = '24601'"]

            if {$data == ""} {
                puts "We don't employ #24601"
            } else {
                puts "#24601 is [ns_set value $data 0]"
            }
        

            set data [ns_db 1row $db "
                select count(*) 
                  from employees 
                 where salary < 18000"]
            set qty  [ns_set value $data 0]
        

Other ns_db Methods

In addition to the foregoing methods, there are a number of others which are not as frequently used. They include: bindrow, bouncepool, cancel, close, connected, datasource, dbtype, driver, exception, exec, flush, open, poolname, pools, password, setexception, user, and verbose. More information on these methods can be found in either the AOLserver or nstcl documentation. 23, 24

Implementation Details

It turns out that of the twenty-five ns_db subcommands, there are only a half-dozen that need to be customized to support a new Tcl database extension. These functions are:

MethodTcl Procedure
bindrow ::nstcl::database::${driver}::bindrow
close ::nstcl::database::${driver}::close
exec ::nstcl::database::${driver}::exec
flush ::nstcl::database::${driver}::flush
gethandle ::nstcl::database::${driver}::gethandle
getrow ::nstcl::database::${driver}::getrow

All of the other ns_db methods can be built on top of these six primitives. This makes sense when you consider that the basic operations needed to talk to any RDBMS are:

  1. Open a connection (ns_db gethandle)
  2. Execute some SQL and discern whether it was a query or if it was DML/DDL (ns_db exec)
  3. If the SQL was a query we need to be able to fetch the results (ns_db bindrow and ns_db getrow)
  4. Be able to throw away unfetched results we don't need (ns_db flush)
  5. Close the connection (ns_db close)

How "Drivers" Are Loaded

The ::nstcl::load_driver command does a package require nstcl-database-$driver. The ::nstcl::configure_pool creates a pool, using a loaded driver, for n_connections to a particular datasource. The format of the datasource will depend upon the underlying Tcl database extension that is being wrapped.

Keeping Track of State

We take advantage of the opaque nature of the ns_db API's database handle to implement an OOP-like system to track the state of each handle.

Each handle is actually the fully qualified name of an ns_set that resides in the ::nstcl::database namespace. This ns_set stores various settings for the handle. Using Tcl's interp alias command, the fully qualified variable name becomes an alias to a dispatch procedure which receives the driver, poolname and fully qualified handle as arguments.

This dispatch procedure accepts either zero, one, or two additional arguments. With no arguments it returns its own setId. With one argument it returns the specified key from its own ns_set. With two arguments it updates the value of the specified key in its setId.

Performance

Any abstraction layer, by definition, will cost more in performance than using a lower-level system. Most usage patterns do not involve pushing hundreds of thousands of rows back and forth across a network. Such work is better done within the database itself (either using SQL or a vendor supplied stored procedure language, such as Oracle's PL/SQL). For normal usage patterns we have found that nstcl does not pose an undue burden. The Solid, Postgres, and Oracle drivers have been the most used. Other drivers may well benefit from additional tuning. In general the time savings to the programmer outweigh the cost of a few more CPU cycles.

With the next release of AOLserver, currently in beta, the AOLserver developers have refactored portions of the code base into a loadable module, libnsd.so, which can be loaded as a Tcl package. The database drivers and ns_db API are not (as of this writing) included within libnsd.so. However, the next release of nstcl will, once AOLserver 4 is finalized, be able to take advantage of libnsd.so for a C implementation of ns_set's, which should enhance performance. While writing this paper, and reviewing the nstcl source, we have noted several possible speed enhancements which will be incorporated into the next release.

The OpenACS Database API

As nice as ns_db is (not having to change APIs when you switch to a different database), dealing with handle management and manually iterating over query results can become tedious.

Luckily for the Tcl programmer who craves laziness, OpenACS provides a higher-level database API on top of ns_db that hides all the mundane house-keeping chores. Additionaly, bind-variable emulation is available which frees the programmer from worrying about always escaping apostrophes in SQL statements. Bind variables are prefaced with a colon.


            set name "Jim O'Connor"
            set salary 40000

            db_dml new_hire {
                insert into employees (name, salary, dept)
                values (:name, :salary, 'Accounting')
            }
        

In the previous example you'll notice the first argument to db_dml is new_hire. This is known as a "statement name" and serves several purposes. First and foremost, it helps document the intent of a query. OpenACS has a "query dispatcher" that can use the statement name to lookup an appropriate database-specific query in an XML file and execute it instead.

There are a number of different approaches to retrieving the results of a query. Some Tcl database extensions require you to fetch a row at a time. Others return all the results in one fell swoop. Some may choose to provide both options. The OpenACS Database API allows either style, regardless of the underlying Tcl database extension.

db_foreach can be used to iterate and execute a block of Tcl code for every row returned from the database. A variable is pre-set for each of the database columns.


            db_foreach recent_hires {
                select name, salary, hire_date
                  from employees
                 where hire_date >= '2003-01-01'
                 order by name
            } {
                puts "$name makes \$[format %0.2f $salary]"
            }
        

db_list will return a Tcl list made up of the first column of each row. db_list_of_lists will return a list of lists, where the inner list contains each of the database columns in the order they were specified in the query.


            set ids [db_list employee_ids "select employee_id from employees"]
        

            array set employees [join [db_list_of_lists "
                select employee_id, name 
                  from employees"]]
        

Like ns_db, commands exist to retrieve queries which return at most one row.


            if {[db_0or1row specific_employee {
                select name, hire_date 
                  from employees 
                 where employee_id = 24601}]} {
                puts "We hired #24601, $name, on $hire_date"
            }
        

            db_1row burn_rate "
                select sum(salary)/24 as cash
                  from employees"

            puts "We pay out \$[format %0.2f $cash] every payroll"
        

A series of database queries can be evaluated within a transaction. This means that either all changes will occur, or none will. We'll look at implementing, within a transaction, a hypothetical algorithm PHB's25 might use to cut payroll expenses.


            # Implement an algorithm to reduce our payroll expenses
            db_transaction {
                db_dml pay_cut_for_execs {
                    update employees 
                       set salary = salary * 0.985
                     where salary > 175000
                }

                set qty [db_string number_of_peons {
                    select count(*)
                      from employees
                }]

                set laid_off 0

                db_foreach peons {
                    select employee_id, salary
                      from employees
                     where salary <= 65000
                } {
                    # roll the dice
                    if {int(rand()*6) == 0} {
                        incr laid_off

                        db_dml layoff_somebody {
                            delete from employees
                             where employee_id = :employee_id
                        }
                    }
                }

                # Did we lay off too many people?
                if {$laid_off >= $qty/2} then db_abort_transaction
            }
        

The nstcl implementation of the OpenACS Database API adds support for specifying which database pool to use. This makes it really easy to write quick Tcl scripts that glue one database to another. In nstcl, the statement name can be prefixed with the name of a pool to specify which database to use (otherwise the default pool is assumed).


            # Assume layoffs weren't enough and we need to save money by
            # cutting software licensing costs.  We've defined an "oracle"
            # pool and a "postgres" pool previously.

            db_transaction {
                db_foreach oracle:old_system {
                    select name, employee_id, salary, hire_date
                      from employees
                } {
                    set first_names [lrange $name 0 end-1]
                    set last_name   [lindex $name end]

                    db_dml postgres:new_system {
                        insert into wage_slaves 
                            (first_names, last_name, salary, ssn)
                        values 
                            (:first_names, :last_name, :salary, :employee_id)
                    }
                }
            }
        

Writing Procedures That Take Optional Switches with ad_proc

ad_proc is an enhanced version of Tcl's standard proc command. Procedures defined with ad_proc can support several different styles of switches.

-foo:boolean
An optional switch foo; a local variable named foo_p will be set to 1 if this switch was present, 0 otherwise.

-bar:required
A required switch bar; a local variable named bar will be set. An error is thrown if the procedure is invoked without specifying a value for this switch.

{-baz default}
An optional switch baz. If the switch is specified the local variable baz is set to the specified value; otherwise, the local variable baz will be set to default.

-foobar:optional
An optional switch foobar. The local variable foobar will be set only if specified by the caller; otherwise, the variable will not exist.

Switch parameters must be listed first, before any positional parameters. When invoking a procedure which has optional switches (and there is a possibility that the first positional parameter may begin with a hyphen) use "--" to indicate that there are no more switches to parse.

An Example Using ad_proc

To illustrate, here is the definition of the nstcl sysdate command from the nstcl-time package:


        ad_proc sysdate {-seconds:boolean {-format "%Y-%m-%d"} {-offset "0 days ago"} -base:optional} { 
            if {![info exists base]} {
                set time [clock seconds]
            } else {
                if {![string is integer -strict $base]} {
                    set time [clock scan $base]
                } else {
                    set time $base
                }
            }

            set time [clock scan $offset -base $time]
            
            if {$seconds_p} {
                return $time
            } else {
                return [clock format $time -format $format]
            }
        }
    

Conclusions

Tcl has a plethora of database extensions like most other programming languages. Unlike Perl and Python, which tend to ship large batteries included distributions, the Tcl core is purposefully lean. Using an abstraction layer, such as nstcl, puts Tcl on the same footing, in terms of code re-use and a consistent API, as these other languages.

Appendix A: Examples of Tcl Database Extension Syntax

To illustrate the differences between extensions, consider the following code snippets that make a database connection, create a table, populate it with a few rows of data, and then retrieve a result.

These are the generic SQL statements that we'll use in our example:


            #
            # SQL statements common to all examples
            #

            set SQL(create) "
                CREATE TABLE zipcodes (
                    zipcode CHAR(5) UNIQUE NOT NULL PRIMARY KEY,
                    state   CHAR(2) NOT NULL,
                    city    VARCHAR(30) NOT NULL
                )"

            set SQL(insert,1) "INSERT INTO zipcodes VALUES ('95054', 'CA', 'Santa Clara')"
            set SQL(insert,2) "INSERT INTO zipcodes VALUES ('90210', 'CA', 'Hollywod')"
            set SQL(insert,3) "INSERT INTO zipcodes VALUES ('84041', 'UT', 'Layton')"
            set SQL(insert,4) "INSERT INTO zipcodes VALUES ('48103', 'MI', 'Ann Arbor')"
            set SQL(insert,5) "INSERT INTO zipcodes VALUES ('94024', 'CA', 'Los Altos')"

            set SQL(select) "
                SELECT state, count(*) AS qty
                  FROM zipcodes
                 GROUP BY state
                 ORDER BY state"
        

Oracle via Oratcl


            package require Oratcl 3
            set conn [oralogon "user/[email protected]"]
            oraautocom $conn 1

            set cursor [oraopen $conn]
            orasql $cursor $SQL(create)
            
            for {set i 1} {$i <= 5} {incr i} {
                orasql $cursor $SQL(insert,$i)
            }

            orasql $cursor $SQL(select)
            set data [orafetch $cursor]
            while {$::oramsg(rc) != 1403} {
                puts "[lindex $data 0] has [lindex $data 1] zipcode(s) on file"
                set data [orafetch $cursor]
            }
            
            oraclose $cursor
            oralogoff $conn
        

Sybase via Sybtcl


            package require Sybtcl 3
            set conn [sybconnect sa "" SYBASE ""]
            sybsql $conn $SQL(create)

            for {set i 1} {$i <= 5} {incr i} {
                sybsql $conn $SQL(insert,$i)
            }

            if {[string equal "REG_ROW" [sybsql $conn $SQL(select)]]} {
                set data [sybnext $conn]
                while {[llength $data]} {
                    puts "[lindex $data 0] has [lindex $data 1] zipcode(s) on file"
                    set data [sybnext $conn]
                }
            }

            sybclose $conn
        

Solid via Soltcl


            load soltcl.so Solid
            sol AllocConnect conn
            sol Connect $conn "tcp localhost 1313" username password
            sol setConnectOption $conn autocommit on

            sol AllocStmt $conn cursor
            sol Prepare $cursor $SQL(create)
            sol Execute $cursor
            sol FreeStmt $cursor drop

            for {set i 1} {$i <= 5} {incr i} {
                sol AllocStmt $conn cursor
                sol Prepare $cursor $SQL(insert,$i)
                sol Execute $cursor
                sol FreeStmt $cursor drop
            }

            sol AllocStmt $conn cursor
            sol Prepare $cursor $SQL(select)
            sol Execute $cursor
            sol NumResultCols $cursor num_cols

            while {[sol Fetch $cursor] == "SQL_SUCCESS"} {
                set values [list]
                for {set i 1} {$i <= $num_cols} {incr i} {
                    sol getData $cursor $i value
                    lappend values $value
                }

                puts "[lindex $values 0] has [lindex $values 1] zipcode(s) on file"
            }

            sol FreeStmt $cursor drop
            sol Disconnect $conn
            sol FreeConnect $conn
        

Postgres via pgtcl


            load libpgtcl.so
            set conn [pg_connect -conninfo [list dbname=dbname host=localhost user=user password=pass]]
            
            set cursor [pg_exec $conn $SQL(create)]
            pg_result $cursor -clear

            for {set i 1} {$i <= 5} {incr i} {
                set cursor [pg_exec $conn $SQL(insert,$i)]
                pg_result $cursor -clear
            }

            set cursor [pg_exec $conn $SQL(select)]
            set n_rows [pg_result $cursor -numTuples] 
            for {set i 0} {$i < $n_rows} {incr i} {
                pg_result $cursor -tupleArray $i data
                puts "$data(state) has $data(qty) zipcode(s) on file"
            }
            pg_result $cursor -clear
             
            pg_disconnect $conn
        

SQLite via tclsqlite


            package require sqlite
            sqlite db /path/to/database
            
            db eval $SQL(create)

            for {set i 1} {$i <= 5} {incr i} {
                db eval $SQL(insert,$i)
            }

            db eval $SQL(select) data {
                puts "$data(state) has $data(qty) zipcode(s) on file"
            }
        

Our Example Revisited (Using ns_db)


            set db [ns_db gethandle]

            ns_db dml $db $SQL(create)
            
            ns_db dml $db $SQL(insert,1)
            ns_db dml $db $SQL(insert,2)
            ns_db dml $db $SQL(insert,3)
            ns_db dml $db $SQL(insert,4)
            ns_db dml $db $SQL(insert,5)

            set selection [ns_db select $db $SQL(select)]

            while {[ns_db getrow $db $selection]} {
                puts "[ns_set $selection value 0] has [ns_set $selection value 1] zipcode(s) on file"
            }
 
            ns_set free $selection
            ns_db releasehandle $db
        

Our Example Revisited (Using the OpenACS Database API)


            db_dml create_table_example $SQL(create)

            db_dml insert_example_1 $SQL(insert,1)
            db_dml insert_example_2 $SQL(insert,2)
            db_dml insert_example_3 $SQL(insert,3)
            db_dml insert_example_4 $SQL(insert,4)
            db_dml insert_example_5 $SQL(insert,5)

            db_foreach select_example $SQL(select) {
                puts "$state has $qty zipcode(s) on file"
            }
        

Appendix B: An Example nstcl Database Driver

This is the nstcl database "driver" (or wrapper) for Postgres. It was the first driver written for nstcl 0.1.



package require nstcl-core
package require nstcl-fwdcompat
package require nstcl-database

# nstcl-1.1/nstcl-database-postgres.tcl
# $Id: nstcl-database-postgres.tcl,v 1.5 2002/11/21 20:04:59 cleverly Exp $
#
# nstcl -- AOLserver/OpenNSD routines for tclsh
#
#     Copyright (c) 2000, 2001, 2002 Michael A. Cleverly
#     
#     Permission is hereby granted, free of charge, to any person obtaining
#     a copy of this software and associated documentation files (the
#     "Software"), to deal in the Software without restriction, including
#     without limitation the rights to use, copy, modify, merge, publish,
#     distribute, sublicense, and/or sell copies of the Software, and to
#     permit persons to whom the Software is furnished to do so, subject to
#     the following conditions:
#     
#     The above copyright notice and this permission notice shall be
#     included in all copies or substantial portions of the Software.
#     
#     THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
#     EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
#     MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
#     IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
#     CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
#     TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
#     SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
#
# Author Contact Information:
#
#     Michael A. Cleverly
#     1448 W Pebblecreek Dr
#     Layton, Utah 84041-8112
# 
#     [email protected]
#     http://michael.cleverly.com
#
# nstcl home page: http://nstcl.sourceforge.net


namespace eval ::nstcl::database::postgres {}



#
# ... load_driver
#

::nstcl::ad_proc ::nstcl::database::postgres::load_driver {arguments} {
    if {[info commands pg_connect] != ""} then return

    foreach {library name} $arguments break
    if {![info exists library] || [string equal "" $library]} {
        set library libpgtcl.so
    }

    if {![info exists name] || [string equal "" $name]} {
        load [::nstcl::find_shared_library $library]
    } else {
        load [::nstcl::find_shared_library $library $name]
    }
}



#
# ... bindrow
#

::nstcl::ad_proc -private ::nstcl::database::postgres::bindrow {dbhandle} {
    set cursor [$dbhandle cursor]
    set setId  [::nstcl::ns_set create $cursor]
    
    foreach attribute [pg_result $cursor -attributes] {
        ::nstcl::ns_set put $setId $attribute ""
    }
    
    return $setId
}



#
# ... close
#

::nstcl::ad_proc -private ::nstcl::database::postgres::close {dbhandle} {
    pg_disconnect [$dbhandle conn]
    $dbhandle conn {}
}



#
# ... dbtype
#

::nstcl::ad_proc -private ::nstcl::database::postgres::dbtype {} {
    return "Postgres"
}



#
# ... exec
#

::nstcl::ad_proc -private ::nstcl::database::postgres::exec {dbhandle sql} {
    set cursor [$dbhandle cursor]
    set conn   [$dbhandle conn]
 
    # free previously allocated memory
    if {![string equal "" $cursor]} {
        catch { pg_result $cursor -clear }
        $dbhandle cursor {}
    }

    set cursor [pg_exec $conn $sql]
    set error  [pg_result $cursor -error]
   
    if {![string equal "" $error]} {
        pg_result $cursor -clear
        $dbhandle exception_code NSDB
        $dbhandle exception_text $error
        $dbhandle num_rows {}
        $dbhandle curr_row {}
        $dbhandle mode ERROR
        return -code error $error
    } else {
        $dbhandle exception_code {}
        $dbhandle exception_text {}
    }

    if {[pg_result $cursor -numAttrs]} {
        $dbhandle num_rows [pg_result $cursor -numTuples]
        $dbhandle curr_row 0
        $dbhandle cursor $cursor
        $dbhandle mode NS_ROWS
        return NS_ROWS
    } else {
        pg_result $cursor -clear
        $dbhandle num_rows 0
        $dbhandle curr_row 0
        $dbhandle mode NS_DML
        return NS_DML
    }
}



#
# ... flush
#

::nstcl::ad_proc -private ::nstcl::database::postgres::flush {dbhandle} {
    catch { pg_result [$dbhandle cursor] -clear }

    $dbhandle cursor {}
    $dbhandle exception_code {}
    $dbhandle exception_text {}
    $dbhandle num_rows {}
    $dbhandle curr_row {}
    $dbhandle mode {}
}



#
# ... gethandle
#

::nstcl::ad_proc -private ::nstcl::database::postgres::gethandle {pool 
                                                                  dbhandle} {
    upvar 0 ::nstcl::database::pools pools
    set datasource $pools($pool,datasource)
    set user       $pools($pool,user)
    set pass       $pools($pool,pass)
    
    if {[string equal "" $datasource]} {
        set datasource ::
    }

    foreach {host port dbname} [split $datasource :] break
    

    set conninfo [list "dbname=$dbname"]
    if {[string length $host]} { lappend conninfo "host=$host" }
    if {[string length $port]} { lappend conninfo "port=$port" }
    if {[string length $user]} { lappend conninfo "user=$user" }
    if {[string length $pass]} { lappend conninfo "password=$pass" }

    # override the default of ::nstcl::database::pseudo_bind_variables
    # since Postgres has a non-standard escape of \ (i.e. \' along w/ '')
    $dbhandle bind_vars ::nstcl::database::postgres::pg_bind_vars

    $dbhandle conn [pg_connect -conninfo $conninfo]
}



#
# ... getrow
#

::nstcl::ad_proc -private ::nstcl::database::postgres::getrow {dbhandle setId} {
    set size     [::nstcl::ns_set size $setId]
    set cursor   [$dbhandle cursor]
    set num_rows [$dbhandle num_rows]
    set curr_row [$dbhandle curr_row]

    if {$num_rows > 0 && $curr_row > $num_rows} {
        return -code error "Database operation \"getrow\" failed"
    }

    if {$num_rows == 0} {
        return 0
    }

    if {$num_rows == $curr_row} {
        return 0
    }
    
    if {$size} {
        ::nstcl::ns_set truncate $setId 0
    }

    pg_result $cursor -tupleArray $curr_row tupleArray
    foreach key [lsort -dictionary [array names tupleArray]] {
        ::nstcl::ns_set put $setId $key $tupleArray($key)
    }

    $dbhandle curr_row [incr curr_row]
    return 1
}


#
# pg_bind_vars: a custom pseudo_bind_variables
#

::nstcl::ad_proc -private ::nstcl::database::postgres::pg_bind_vars {sql} {
    uplevel 1 [list ::nstcl::database::pseudo_bind_variables $sql 1]
} 



package provide nstcl-database-postgres 1.1
    
        

Footnotes

1 http://nstcl.sourceforge.net

2 http://wiki.tcl.tk/nstcl

3 http://aolserver.com

4 http://openacs.org

5 http://philip.greenspun.com/wtr/aolserver/introduction-1.html

6 http://openacs.org

7 http://www.linuxjournal.com/article.php?sid=6223

8 http://openacs.org/about/history

9 http://michael.yoon.org/arsdigita

10 http://wiki.tcl.tk/ArsDigita

11 http://wiki.tcl.tk/1303

12 https://tip.tcl-lang.org/111

13 http://nstcl.sourceforge.net/docs/nstcl-nssets/ns%5fset.html

14 http://wiki.tcl.tk/620

15 http://www.perl.com/pub/a/1999/10/DBI.html

16 http://www.python.org/topics/database/DatabaseAPI-2.0.html

17 http://www.python.org/topics/database/modules.html

18 http://www.webkreator.com/php/concepts/php-database-wrappers.html

19 http://www.php.net/manual/en/funcref.php

20 http://pear.php.net/manual/en/core.db.php

21 http://www.phpclasses.org/browse.html/package/20.html

22 http://www.aolserver.com/docs/devel/driver/db

23 http://aolserver.com/docs/devel/tcl/api/db.html

24 http://nstcl.sourceforge.net/docs/nstcl-database/ns%5fdb.html

25 http://www.dilbert.com/comics/dilbert/the_characters/index.html