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 hugh list of settings and a hugh 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:
the basic configuration ("Basic") values are:
"Group A" and "Group B" are based on the basic configuration,
Group A overwrites the following value:
for Group B
"User 1" belongs to "Group A", the personal setting is
"User 2" belongs to "Group B", but uses
"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:
User 2:
User 3:
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:
In case the user name is unknown you will get the basic configuration.
getValues [getSQL [getChain "User 99"]]
}