store cascaded settings in a database

if 0 {

phk hierarchy of preferences might be a better title ...

phk 2003-09-10: 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 B", 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

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 better

 t1(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!