Summary  edit

Richard Suchenwirth 2004-03-22: In A simple database, I showed how databases may be implemented with Tcl arrays. This take here is closer to traditional relational databases, with tables of pre-defined columns (modeled here as a list of lists, the first being the column heading, the rest the "records"), and mimicks the SQL SELECT statement a bit.

See Also  edit

editRecord

a tiny dialog to edit records in such a database.

Description  edit

Tables are "pure values", and the result of select is a valid table again:

proc select {fields "from" tbl "where" condition} {
    set infields [lindex $tbl 0]
    if {$fields eq "*"} {set fields $infields}
    set res [list $fields]
    foreach inrow [lrange $tbl 1 end] {
        foreach $infields $inrow break
        if $condition {
            set row {}
            foreach field $fields {lappend row [set $field]}
            lappend res $row
        }
    }
    set res
}

#-- Test data, assuming a little inventory control system:
set table {
    {number description pieces}
    {1234   Foo         100}
    {2345   Bar         50}
    {3456   Grill       2}
}

Testing:

% select * from $table where {$pieces < 100}
{number description pieces} {2345 Bar 50} {3456 Grill 2}

% select {pieces description} from $table where {$number != 1234}
{pieces description} {50 Bar} {2 Grill}

Cute, ain't it? There is a danger though, if you happen to name a "database" column condition, row, fields, res or so... because the column names are used as variables, and would overwrite the working variables, possibly causing syntax errors.

AM: If you use namespace variables (like ::db::row - not "variable row"), you should be able to avoid even such clashes ...

Adding a "record" to this database is trivial:

lappend table {1234 "another Item" 1}

Editing a value in place goes well with lset, where you for now need to specify the record number, but can address a column by its name:

proc col {table field} {
    lsearch [lindex $table 0] $field
}

lset table 4 [col $table description] "Item, another"

---

Another frequent operation is sorting a table on a column, with options like -increasing or -integer. We only have to make sure that the header list stays always in front:}

proc sort {table field args} {
    set res [list [lindex $table 0]]
    eval lappend res [eval lsort -index [col $table $field] $args \
        [list [lrange $table 1 end]]]
}

% sort $table pieces -integer
{number description pieces} {3456   Grill       2} {2345   Bar         50} {1234   Foo         100}

% sort $table description  -decreasing
{number description pieces} {3456   Grill       2} {1234   Foo         100} {2345   Bar         50}

And as fashionable these days, here's a simple sketch how to export a table as XML (with entity escaping of cell):

proc toXML {table {type table}} {
    set fields [lindex $table 0]
    set res <$type>\n
    foreach row [lrange $table 1 end] {
        append res <row>
        foreach field $fields cell $row {
            set cell [string map {< "&lt;" & "&amp;" > "&gt;"} $cell]
            append res <$field>$cell</$field>
        }
        append res </row>\n
    }
    append res </$type>
}

---

RS 2004-09-30: Another terribly simple piece of code generates a full index of the table, where keys are field=value, and the "row number(s)" of applicable records are the value. It returns it as a serialized array (which could be turned to dict later):

proc mkindex table {
    set fields [lindex $table 0]
    array set a {}
    set i 0
    foreach row [lrange $table 1 end] {
        incr i
        foreach field $fields value $row {
            lappend a($field=$value) $i 
        }
    }
    array get a
}

Testing:

% mkindex $table
pieces=2 3 number=3456 3 number=2345 2 number=1234 1 description=Foo 1 
description=Bar 2  description=Grill 3 pieces=50 2 pieces=100 1

To query this index, dump it into an array again, and search for all things whose description starts with "G", with

array get arr description=G*

---

[unperson]: Very impressive work! Well done, Richard!

May I ask you a question? When one opens the file where the data is stored to have a look at the contents, (say we open the file with Notepad), what do we see? Text + {{Opening and closing braces}}?

When we are talking about entering the data with fields, in a table, we are talking about seing all the records in the database and not only one. Some people (including yours truly) need a global view of a database. Is there a way to have a table view of the records?

I wonder if it would be easy to adapt your database from the opening-closing-brace data structure to the CSV data structure.

---

RS The only data structure involved here is a list of lists. How to serialize (stringify) that in a text file is left to the user (Tcl format, CSV, whatever). For displaying the whole table, one might look at mclistbox or such, but if there are several 100 000 records, putting all in listboxes may not be the best idea :^) Then again, one could also put extracts (as delivered by select above) in an mclistbox.

ZB 2009-11-15: Comparing this solution with that described in a simple database I've got a feeling, that array-based solution - while being older - is still superior to this one. Having a set of basic array functions - completed with list- and dict-related - one just doesn't need to iterate, to fetch any amount of data, whether there's a need for rows, or for columns; a good example is "how to get column", just by: array get db *,title (of course, there must be some iteration anyway, but not at TCL-level). Storing the "records" in lists - or dictionaries - will force, at some point, the need for iteration. (After a while) from the other side: iteration can be used f.e. to fill treeview widget without a need to buffer a large amount of data (one "record" at a time will do) - so actually it depends on the need...

---