Migrating Application Configuration from Windows Registry to SQLite

Question

Currently, I am working on the migration mentioned in the title line. Problem is application configuration that is kept in registry has a tree like structure, for example:

X
|->Y
   |->Z
      |->SomeKey someValue
W
|->AnotherKey anotherValue

and so on.

How can I model this structure in SQLite (or any other DB)? If you have experience in similar problems, please send posts. Thanks in advance.

Answer 1

Representing hierarchies in a relational database is pretty easy. You just use self-references. For example, you have a category table that has a field called ParentCategoryId that is either null ( for leaf categories) or the id of the parent category. You can even setup foreign keys to enforce valid relationships. This kind of structure is easy to traverse in code, usually via recursion, but a pain when it comes to writing sql queries.

One way around this for a registry clone is to use the registry key path as the key. That is, have an entry where Path is "X/Y/Z/SomeKey" and Value is "someValue". This will query easier but may not express the hierarchy the way you might like. That is, you will only have the values and not the overall structure of the hierarchy.

The bottom line is you have to compromise to map a hierarchy with an unknown number of levels onto a relational database structure.

Answer 2

Baris, this structure its similar to a directory/file structure. You can model this with a simple parent<>child relationship on the directories and key value pairs relatade to the directory.

Something like

Directory:
id integer auto_increment;
name string not null;
parent_id integer not null default 0;

Property:
id integer auto_increment;
key string;
value string;
directory_id integer not null;

With this you can address the root directories searching for directories with parent_id=0, child directories by looking at WHERE parent_id=someid and for properties on that looking for directory_id=someid.

Hope this helps :)

Answer 3

Self-referencing tables mentioned by previous posters are nice when you want to store an hierarchy, they become less attractive when you start selecting leaves of the tree.

1. Could you clarify the use-case of retrieving data from the configuration?

2. Are you going to load the whole configuration at once or to retrieve each parameter separately?

3. How arbitrary will be the depth of the leaf nodes?