if 0 { [phk] 2003-09-10: I don't even know if the title is easy to understand... Imagine your application has several users, all with their own settings. You might source plain textfiles, a default configuration first, followed by specific settings. If more than one user belong to a group and the group itself requires settings, it can be difficult to update all config files. A database for sure looks like a overkill, but just imagine a huge list of settings and a long list of hierarchy levels. This example uses [SQLite]. ---- this will be the data used in the example: A configuration setup will consist of three values: * size * color * language ---- the basic configuration ("Basic") values are: * size : 1 * color : red * language : de ---- "Group A" and "Group B" are based on the basic configuration, Group A overwrites the following value: * language: en for Group B * color: blue ---- "User 1" belongs to "Group A", the personal setting is * size: 2 "User 2" belongs to "Group B", but uses * color: green "User 3" belongs to "Group 3", he has no personal settings ---- Hierarchies in this exampe are simple: Basic -> Group -> User Note, that you could add several groups, they way is always to go backwards (up to the basic Configuration). Maybe now the cascade can be seen. ---- The expected results would be: User 1: * size: 2 * color: red * language: en User 2: * size: 1 * color: green * language: de User 3: * size: 1 * color: blue * language: de For this example I prepare two tables "level" (which defines the hierarchy) and "settings" which hold key-value pairs. } package require sqlite #---------------------------------------------------------------------- # preparation #---------------------------------------------------------------------- proc prepareTables {} { sqlite db demo.db db eval "\ CREATE TABLE level ( \ id INTEGER PRIMARY KEY, \ parent_id INTEGER, \ name VARCHAR(20) \ );" db eval "\ CREATE TABLE setting ( \ id INTEGER PRIMARY KEY, \ level_id INTEGER, \ key VARCHAR(20), \ value VARCHAR(20) \ );" db close } proc prepareData {} { sqlite db demo.db # levels db eval "INSERT INTO level (id, parent_id, name) VALUES (1, '', 'Basic');" db eval "INSERT INTO level (id, parent_id, name) VALUES (2, 1, 'Group A');" db eval "INSERT INTO level (id, parent_id, name) VALUES (3, 1, 'Group B');" db eval "INSERT INTO level (id, parent_id, name) VALUES (4, 2, 'User 1');" db eval "INSERT INTO level (id, parent_id, name) VALUES (5, 3, 'User 2');" db eval "INSERT INTO level (id, parent_id, name) VALUES (6, 3, 'User 3');" # values # - base db eval "INSERT INTO setting (id, level_id, key, value) VALUES (1, 1, 'size', '1');" db eval "INSERT INTO setting (id, level_id, key, value) VALUES (2, 1, 'color', 'red');" db eval "INSERT INTO setting (id, level_id, key, value) VALUES (3, 1, 'language', 'de');" # - group A db eval "INSERT INTO setting (id, level_id, key, value) VALUES (4, 2, 'language', 'en');" # - group B db eval "INSERT INTO setting (id, level_id, key, value) VALUES (5, 3, 'color', 'blue');" # - user 1 db eval "INSERT INTO setting (id, level_id, key, value) VALUES (6, 4, 'size', '2');" # - user 2 db eval "INSERT INTO setting (id, level_id, key, value) VALUES (7, 5, 'color', 'green');" db close } #---------------------------------------------------------------------- # procs #---------------------------------------------------------------------- proc getChain {name} { sqlite db demo.db set chain {} # get key by itself set id [lindex [db eval "SELECT id from level where name = '$name';"] 0] if {![string length $id]} { # this level is unknown, return base configuration return [getChain "Basic"] } else { lappend chain $id } # go up until there is no parent anymore (=base) set id [lindex [db eval "SELECT parent_id from level where id = '$id';"] 0] while {[string length $id]} { lappend chain $id set id [lindex [db eval "SELECT parent_id from level where id = '$id';"] 0] } db close return $chain } proc getSQL {chain} { set except {} set sql {} foreach id $chain { append sql " SELECT key, value, level_id " append sql "FROM setting WHERE level_id = $id" if {[llength $except]} { append sql " AND key NOT IN (select key from setting where level_id IN ([join $except ","]))" } append sql " UNION " lappend except $id } # remove the last UNION (unnecessary) regsub -- {UNION $} $sql ";" sql return $sql } proc getValues {query} { sqlite db demo.db db eval $query {} { puts "[format %-10s $key]: $value" } db close } if 0 { This is all tcl Code we need, now let it run: } prepareTables prepareData # usage getValues [getSQL [getChain "User 1"]] if 0 { you will get: * size : 2 * color : red * language : en In case the user name is unknown you will get the basic configuration. getValues [getSQL [getChain "User 99"]] * size : 1 * color : red * language : de } ---- Any feedback is much appreciated.