** Summary **

[Richard Suchenwirth] 2004-03-22:  In [A simple database], I showed how
databases may be implemented with Tcl [array]s. 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 **

   [editRecord]:   a tiny dialog to edit records in such a database.

** Description **

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:
======none
% 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]]]
}
======

======none
% 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:

======none
% 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...


<<categories>> Category Database | Arts and Crafts of Tcl-Tk Programming | Arts and Crafts of Tcl-Tk Programming | RS