TclDB database abstraction layer and DB GUI
GUI tutorial

The following tutorial's code is included in the tcldb distribution package. See file releases for details on how to download TclDB. Also, if you're curious about the results now, have a look at the entries list screenshot and edit pane screenshot.

TclDB can be used to build simple and complex database driven GUI applications. This tutorial will demonstrate how to build a simple GUI application that will allow storing a simple address book.

The first thing is to get started. So, the Tcl file begins with require statements.

# require GUI and configuration packages
package require tcldb
package require tcldb::gui
package require tcldb::config

Now, the next part initializes the database using tcldb::config to store the database in user's home directory. After that, we will load the database tables and some SQL queries definitions - these should be names the same as the script, only with .sql and .dba extensions.

# initialize configuration - so that a default database is set up
tcldb::config_init -name example.gui_table_edit

# initialize the table and list query
db.ts load [info script].dba
db.q load [info script].sql

Let's take a look at the database definition gui_table_edit.tcl.dba file. Although it is more convenient to use dbdesigner to develop the tables, it is also quite easy to write or maintain those files by hand.

user user {
    version 1
    temporary 0
} {
    id serial {}
    ctime datetime {}
    mtime datetime {}
    firstname string {}
    middlename string {}
    lastname string {}
    address1 string {}
    address2 string {}
    postcode string {}
    city string {}
    tel string {}
    fax string {}
    email string {}
} {

Now for the sql queries file. It only contains the query that is used for listing the entries in the database.

# query list1
# -output {{id integer -hide 1} {firstname string -stretch 1 -title {C "First name"}} {lastname string -stretch 1 -title {C "Last name"}} {ctime datetime -title {C "Created"}} {mtime datetime -title {C "Modified"}} }
SELECT id, firstname, lastname, ctime, mtime FROM user
# endquery

Back to Tcl. The next step is to either create SQL queries for doing insert/update/delete or use Tcl procedures, which is the case here. These are using tcldb::tdb_table to do the operations and handle field names/types automatically.

# procedures for handling data
proc user_delete {valuelist} {
    db.ts table user delete -valuelist $valuelist -match {id}

proc user_insert {oldvaluelist valuelist} {
    # set ctime, mtime, delete any previously set id and do an insert
    array set values $valuelist
    set now [clock format [clock seconds] -format "%Y%m%d%H%M%S"]
    set values(ctime) $now
    set values(mtime) $now
    unset -nocomplain values(id)
    db.ts table user insert -variable values

proc user_update {oldvaluelist valuelist} {
    # update mtime and do an update of the data
    array set values $valuelist
    set now [clock format [clock seconds] -format "%Y%m%d%H%M%S"]
    set values(mtime) $now
    db.ts table user update -variable values \
        -oldvaluelist $oldvaluelist -match {id}

proc user_select {valuelist} {
    # delete an entry that matches id field
    return [db.ts table user select -valuelist $valuelist -match {id}]

proc user_validate {valuelist} {
    array set values $valuelist
    set rc [list]
    if {![regexp {^$|(.@.+\..)} $values(email)]} {
	lappend rc email "Invalid email format"
    if {![regexp {.} $values(firstname)]} {
	lappend rc firstname "Invalid first name"
    if {![regexp {.} $values(lastname)]} {
	lappend rc lastname "Invalid last name"
    return $rc

The final things are to prepare the actual edit and list form, add a list as a new tab and pack + set up geometry storing.

# actual edit form
tcldb::tdbedit .edit \
    -editcommand user_select \
    -validatecommand user_validate \
    -insertcommand user_insert \
    -updatecommand user_update \
    -deletecommand user_delete \
    -widgets [list \
	-namespace ::tcldb::dt \
	-globalposition n \
	-params {-anchor e} -wargs {-text} \
	-usevar 0 \
	-at 0,0 \
	label l_firstname "First name" \
	label l_middlename "Middle name" \
	label l_lastname "Last name" \
	label l_address1 "Address 1" \
	label l_address2 "Address 2" \
	label l_postcode "Postcode" \
	label l_city "City" \
	-at 0,2 \
	label l_tel "Telephone" \
	label l_fax "Fax" \
	label l_email "E-mail" \
	-usevar 1 -columnweight 1 -params {} -wargs {} -sticky we \
	-at 0,1 \
	dtstring firstname \
	dtstring middlename \
	dtstring lastname \
	dtstring address1 \
	dtstring address2 \
	dtstring postcode \
	dtstring city \
	-at 0,3 \
	dtstring tel \
	dtstring fax \
	dtstring email \
.edit addlist [db.q query list1] [list] -listmode list -label "List"
pack .edit -fill both -expand 1

# geometry storing - done at the end
# so initial sizing will be used by default
tcldb::config_geometry Geometry . .

wm title . "Example - gui table edit"

... and that's it. The actual GUI (using MS Windows) can be checked at:

This code is a part of TclDB package, in the Examples directory.

© 2002-2006 Data Quest and
Wojciech Kocjan.
All rights reserverd.