Tcl Database Connectivity

Tcl Database Connectivity is a presentation given at Tcl2008 given by Kevin Kenny. This page contains a set of notes on the presentation. Subsequently, TDBC became the standard database interface specification and implementation for Tcl.

Presentation Notes

What is TDBC? Uniform, portable API to SQL databases (similar to Perl's DBI and Java's JDBC). Why?

SQL has become ubiquitous. Tcl is already a glue language. Potentially great for federated applications. It's a "check box" feature supported by other languages. Portal access to SQL databases widely requested.

tclodbc:

• Stable. Lots of databases. Nice OO syntax.
• No Unicode. Microsoft-centric. Slow.

nstcl:

• Pure Tcl. Portable.
• No cursors. No bound parameters (MC notes: technically true, but nstcl's high(er) level db_* APIs emulates them using :variable syntax that properly quotes input before passing it to underlying database extension). Slow.

Nearly everything else was tailored to a specific database.

Requirements: Homogeneous interface--all databases look the same. O-O looks to be the easiest way to get there. "Duck typing" for extensiblity--specify only the methods that a database object must support. (But provide base classes for implementors).

Bound parameters are important to avoid SQL injection attacks.

Requirements:

• NULL--much ado about nothing
• Everything is a string. NULL is not a string.
• Not willing to give up EIAS.
• Hence: Represent NULL by "something missing" (array element, dictionary key, variable ...)
• Chosen: dictionary with column names for keys. Keys omitted for NULL values. Lists also available for those that don't care.
• Three Ways of presenting result sets:

1. List of lists (list of dictionaries, etc.) "No iteration"
2. Callback executed once per row "internal iteration"
3. External code makes a fetch call to get a row. "External iteration"

• Explicit transaction control ($db begintransaction; $db commit; $db rollback; or even $db transaction {...script...})

TDBC does not specify how to connect to a database; a driver does that. Statements all may have bound parameters, introduced by the colon character. Why the odd syntax (not dollar), dynamic applications may want double substitution:

db prepare "select * from $table where $column = :value"

(table and column are substituted early; value is substituted late).

What can you do with statements? You can set paramater types (some SQL APIs need this).

... several slides with code snippets ...

External iterators are problematic: Resource management (result sets and statements must be closed). If an error is thrown many layers need to be caught with catch. Internal iterators, create statements, result sets as needed. Clean up on any kind of exit (TCL_OK, TCL_ERROR, anything else).

What TDBC doesn't do:

• Many things omitted for simplicity
• Batched data ("merely" a performance consideration)
• Asynchronous query (use threads)
• Reference cursors (complex result sets from stored procedures)

Why? Mostly to make it easier to write drivers. Some of the above "features" are only supported by some databases. Targeting personal needs and lowest common denominator.

Current status: Fossil repository at http://tdbc.tcl.tk . Will be merged into the Tcl mainline by 8.6b1. Base classes complete. Sample driver in C (tdbc::odbc bridge) complete (except for a couple of minor issues). Sample driver in Tcl (tdbc::sqlite3) also complete. Test suite. Need docs beyond the TIP (#308).

Gauging preferred driver interest:

• MySQL (4)
• Postgres (8)
• Oracle (9)
• Sybase (3)
• Informix (0)
• DB/2 (0)
• SQLite (C driver instead of existing Tcl based one, 7)
• Adabas (1)

What does it take to do a driver? Three classes:

1. Connection (7 methods)
2. Statement (3 methods)
3. Result set (5 methods)

Tcl driver for SQLite3 is ~300 lines of Tcl (including lots of comments). C driver for ODBC is ~2500 lines of C, but ODBC is byzantine.

What's next? Start building real apps! Would like someone to build a database administration tool (desktop like TOAD or Web-based like PHPMyAdmin). Query builders/visualizers. Tcl on Train Tracks? Use your imagination!