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
- color: blue
"User 1" belongs to "Group A", the personal setting is
- size: 2
- color: green
Hierarchies in this exampe are simple: Basic -> Group -> UserNote, 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
- size: 1
- color: green
- language: de
- size: 1
- color: blue
- language: de
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
getValues [getSQL [getChain "User 99"]]
- size : 1
- color : red
- language : de
An example SQL Query:
SELECT key, value, level_id FROM setting WHERE level_id = 4 UNION SELECT key, value, level_id FROM setting WHERE level_id = 2 AND key NOT IN (select key from setting where level_id IN (4)) UNION SELECT key, value, level_id FROM setting WHERE level_id = 1 AND key NOT IN (select key from setting where level_id IN (4,2)) ;
Any feedback is much appreciated.}
TV Apart from grouping data together, where one usually want a handy encoding, such as naming a set such that the name is smaller than the set, it sounds like a decompostion or commutation-like reasoning.Having a set of data represented in some form, another set of data is made by appying a transformation function to that set, and to the human mind, that transformation is easy to follow.The function can be applied repeatedly, and has a type of domain comparible to its domain. And maybe subterms can be rewritten by having rules to rewrite a(b(c) b(d)) as a(e) or (ab(c) ab(d)) .Or the cascading can be seperated per subset, which could be rewritten as an argument to a function on subsets.
initial_dbase =^ Union(a,b,c) where inters(a,b,c) =^ 0/
|
V
t1(t2(t3(initial_dbase))) ==> Union(t1'(a),t2'(b),t3'(c))or even bettert1(t2(t3(initial_dbase))) ==> Union(t1(a),t2(b),t3(c))The idea of a nice cascading function would be that its decomposition runs nicely, otherwise braincells gnat and results are hard to derive?
phk Thanks for your reply and example. If I understood you right, this is no "nice" cascading example, because there no decomposition (least of all a nice one ;-)While I can follow this
t1(t2(t3(initial_dbase)))t3 is the transformation which overwrites zero, one or more values from the inital settings. (t3 could be defined in "Group A" which means "language"-value is overwritten with "en")okay, I could get all values from the basic configuration, apply the transformation to get to the next higher level. (in our case this means: getting all values of the next higher level)In other words: I execute 3 selects and overwrite in appropriate order.but I choosed the lazy way to let the database calculate. This way I get directly the correct result presented. maybe I missunderstood something, because
Union(t1(a),t2(b),t3(c))I don't understand. Where are the inital_settings?TV Honestly, I was thinking about the page title more than that I followed your exact example, and no, I don't think your example is unnice, that remark is about my ad hoc decomposition's feelings, which is not very objective, just the way that view happened to become more involved than a freewheeling wikireader could come with in a few seconds..The idea of the initial settings and functions being applied is a very valid and general idea, though.Suppose you start with an empty sql database, you start by applying functions to the empty state of that database to give it content, you would add fields and data to it, so you would have a function fill_database:
initial_settings_of_the_database = fill_database1 (fill_database2(fill...(emtpyset)...))where each fill_database(i) adds things to the data stored in the database, and from that point I though I'd start thinking about using different types of functions to change or update the database:
change_database1(change_database2(...(initial_settings)..)Maybe the filling of the database would make completely unoverlapping datasets, and the changes would start making things complicated.
phk ahhh, okay I see. and no worry I do not feel affected if it is nice or not. I didn't explain clearly that my default settings ("Basic") already cover everything and from there I overwrite settings. This is based on one of the goals (of the application where I use the complete stuff): It is supposed to run without settings ("Basic" is generated automatically) and from there you can change your specific settings in a user/group etc. kind. So I expect overlapping settings, but I don't want to deal with them.this wiki page is more a chat log now ;-) Thanks a lot for your time to add all above!
