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.