Clearly there are many well known and proven ways to store
and maintain the configuration settings of your applications. However, where
your requirements have outgrown the ‘off
the shelf’ methods, such as Active
Directory, the relational database can
be used to maintain a hierarchy of default settings, such as a global default
setting for the whole company, optional
lower level default settings for departments overriding upper level defaults,
as well as optional settings for individual users overriding all default ones.
This article provides a simple working
example which demonstrates how to accomplish that.
Why develop your own solution when there are canned ones?
If all you need is to supply hierarchical sets of
configuration values to your applications, then you can just use an existing
solution such as Active Directory, and you are all set. In this case you
probably do not need the solution described in this post. Suppose, however,
that your system needs more functionality than just storing and retrieving
configuration settings. For instance, suppose that your system frequently needs
to include various configuration settings with the data stored in the database
in the same reports. Consider the following requirement: select names and
e-mail accounts of all users (both names and e-mail accounts stored in the
database) who configured their application to output reports on network shared
drive NAS1. If configuration settings are stored in your database, developing
such a report is much easier. If you need many such reports, it is generally
easier to invest some development time to come up with your own solution for
storing configuration in the database, and save more development time on
writing simpler reports. As you will soon see, a solution for storing
configuration in the database is quite simple. Another requirement that I frequently
need is to store history of old values and provide ‘query as of some date in
the past’ functionality. Although storing history is very easy to accomplish,
it is beyond the scope of this post.
Setting up the tables and populating them with sample
data.
In this section I will provide the database structure and
populate it with sample data. In the next two sections, I will demonstrate how
to retrieve configuration settings and how to modify the hierarchy.
The following table dbo.ConfigTree
stores a hierarchy of departments and employees. It uses materialized path
approach to implement hierarchy. Materialized path is a well known approach for
storing hierarchies; it represents the hierarchy in an easy to understand way
and allows for very simple querying.
Note: there are other ways to store hierarchies in
the database, all having their advantages and disadvantages. A more detailed
discussion of materialized path and its alternatives is beyond the scope of
this article.
The table has several constraints which ensure that the
hierarchy is indeed a tree. The columns ParentPath and FullPath
visually present all the ancestors of a node in a very similar way to a
hierarchy of directories in a file system.
CREATE TABLE
dbo.ConfigTree(
NodeName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
ParentPath varchar(880) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
FullPath varchar(880) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT PK_ConfigTree PRIMARY
KEY CLUSTERED (FullPath ASC),
CONSTRAINT UNQ_ConfigTree_NodeName UNIQUE(NodeName),
CONSTRAINT ConfigTree_ParentPath_FK FOREIGN KEY(ParentPath)
REFERENCES dbo.ConfigTree
(FullPath),
CONSTRAINT ConfigTree_CorrectFullPath
CHECK ((FullPath=((case when ParentPath IS NULL then '\' else ParentPath end+NodeName)+'\'))),
CONSTRAINT ConfigTree_NoBackslashInNodeName CHECK ((NodeName=replace(NodeName,'\','')))
)
You’ll notice that backslashes in node names aren’t allowed,
so as to keep the querying logic simpler. The Fullpath value must end
with a backslash for the same purpose. You will see it for yourself in the next
section.
Here is how we populate this table with sample data:
INSERT INTO
dbo.ConfigTree
(NodeName, ParentPath, FullPath)
SELECT 'ABC Inc.', NULL, '\ABC Inc.\'
UNION ALL
SELECT 'UK', '\ABC Inc.\', '\ABC Inc.\UK\'
UNION ALL
SELECT 'Lars', '\ABC Inc.\UK\', '\ABC Inc.\UK\Lars\'
UNION ALL
SELECT 'Midwest', '\ABC Inc.\', '\ABC Inc.\Midwest\'
UNION ALL
SELECT 'Jill', '\ABC Inc.\Midwest\', '\ABC Inc.\Midwest\Jill\'
UNION ALL
SELECT 'Bob', '\ABC Inc.\Midwest\', '\ABC Inc.\Midwest\Bob\'
UNION ALL
SELECT 'Headquarters', '\ABC Inc.\', '\ABC Inc.\Headquarters\'
UNION ALL
SELECT 'CTO', '\ABC Inc.\Headquarters\', '\ABC
Inc.\Headquarters\CTO\'
The following table dbo.SettingValues
is where we store configuration settings for each node of the hierarchy tree:
CREATE TABLE
dbo.SettingValues(
SettingName varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
FullPath varchar(880) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
value varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT PK_SettingValues PRIMARY
KEY CLUSTERED(FullPath ASC, SettingName ASC),
CONSTRAINT FK_SettingValues_ConfigTree FOREIGN KEY(FullPath) REFERENCES dbo.ConfigTree
(FullPath)
ON UPDATE CASCADE
)
To keep the example simple and short, I’ll provide only two
configuration settings in my test data: The server name and the time zone:
INSERT INTO
dbo.SettingValues
(SettingName, FullPath, value)
-- Global setting: use PROD_SERVER1 for all the
company, unless it is
-- overridden at some other lower level node
SELECT 'Server', '\ABC Inc.\', 'PROD_SERVER1'
UNION ALL
-- Another global setting: Eastern Standard Time
unless otherwise specified
SELECT 'TimeZone', '\ABC Inc.\', 'EST'
UNION ALL
-- But of course GMT in UK
SELECT 'TimeZone', '\ABC Inc.\UK\', 'GMT'
UNION ALL
-- Central Standard Time in Midwest
SELECT 'TimeZone', '\ABC Inc.\Midwest\', 'CST'
UNION ALL
-- Bob reports to Midwest team lead, but lives in
Denver,
-- so Mountain Time for Bob
SELECT 'TimeZone', '\ABC Inc.\Midwest\Bob\', 'MST'
UNION ALL
-- Another server for headquarters only
SELECT 'Server', '\ABC Inc.\Headquarters\', 'PROD_SERVER2'
Selecting configuration settings for individual users.
The main advantage of materialized path is that it is easy
to select the data. The following stored procedure is used to return the
settings and their values for any node. It is short and not particularly complex:
CREATE PROCEDURE
dbo.SelectConfiguration
@NodeName VARCHAR(20)
AS
WITH Leaves(LeafPath)
AS(SELECT FullPath AS LeafPath FROM dbo.ConfigTree c WHERE c.FullPath LIKE '%\'+@NodeName+'\%'
AND NOT
EXISTS(SELECT 1
FROM dbo.ConfigTree
c1 WHERE c1.FullPath
LIKE c.FullPath+'%' AND c1.FullPath <> c.FullPath))
SELECT SettingName, value, LeafPath, FullPath FROM(
SELECT s.SettingName, s.value, l.LeafPath, s.FullPath,
ROW_NUMBER() OVER(PARTITION BY SettingName,
LeafPath ORDER BY
FullPath DESC)
rn
FROM dbo.SettingValues
s JOIN Leaves l ON
(l.LeafPath LIKE s.FullPath+'%')) t
WHERE rn =
1
ORDER BY
LeafPath, SettingName
The query is short, but not entirely obvious. Let me explain
why I used ROW_NUMBER() in this query.
Consider the situation when a default setting is overridden by a lower level
default or by a setting for an individual employee. The setting which applies
is the one on the lowest level in the hierarchy. Of course the setting on the
lowest level has a longer full path to the root than any of its ancestors.
Translating this condition into Transact-SQL leads to the following condition:
ROW_NUMBER() OVER(PARTITION BY
SettingName, LeafPath ORDER BY FullPath DESC)=1
To retrieve the configuration for an individual, call the
stored procedure with that individual’s name:
EXEC dbo.SelectConfiguration
@NodeName = 'Bob'
![]()
The FullPath column indicates where the setting comes
from. In this case, server name comes from the global setting, but time zone
is Bob’s personal setting. The ability
to specify a global setting for the whole company in one place is very
convenient, it is a great advantage of this approach.
You can call the
stored procedure with a department’s name just as easily. You will get
configuration settings for all the employees in the department:
EXEC dbo.SelectConfiguration @NodeName = 'Midwest'
![]()
How to modify the hierarchy.
In this section I will briefly demonstrate basic
modifications against the hierarchy stored as a materialized path. Although
some modifications are not exactly trivial, that’s the price you need to pay
for easy selects.
Adding a Setting: Suppose Bob wants to keep Mountain
Time on his desktop computer, but needs Central Time on his laptop. Extending
the hierarchy is very easy:
INSERT INTO
dbo.ConfigTree
(NodeName, ParentPath, FullPath)
SELECT 'Bob''s
Laptop', '\ABC
Inc.\Midwest\Bob\', '\ABC Inc.\Midwest\Bob\Bob''s Laptop\'
INSERT INTO
dbo.ConfigTree
(NodeName, ParentPath, FullPath)
SELECT 'Bob''s
Desktop', '\ABC
Inc.\Midwest\Bob\', '\ABC Inc.\Midwest\Bob\Bob''s Desktop\'
INSERT INTO
dbo.SettingValues
(SettingName, FullPath, value)
SELECT 'TimeZone', '\ABC
Inc.\Midwest\Bob\Bob''s Laptop\', 'CST'
Configuration for Bob looks exactly as needed:
EXEC dbo.SelectConfiguration
@NodeName = 'Bob'
![]()
Adding a node: Adding an office in Seattle, hiring
Ann and Jay, specifying Pacific Time:
INSERT INTO
dbo.ConfigTree
(NodeName, ParentPath, FullPath)
SELECT 'Seattle', '\ABC Inc.\', '\ABC Inc.\Seattle\'
INSERT INTO
dbo.ConfigTree
(NodeName, ParentPath, FullPath)
SELECT 'Ann', '\ABC Inc.\Seattle\', '\ABC Inc.\Seattle\Ann\'
INSERT INTO
dbo.ConfigTree
(NodeName, ParentPath, FullPath)
SELECT 'Jay', '\ABC Inc.\Seattle\', '\ABC Inc.\Seattle\Jay\'
INSERT INTO
dbo.SettingValues
(SettingName, FullPath, value)
SELECT 'TimeZone', '\ABC Inc.\Seattle\', 'PST'
Inserting a node: Adding US division, having Midwest
and Seattle offices report to it:
INSERT INTO
dbo.ConfigTree
(NodeName, ParentPath, FullPath)
SELECT 'US', '\ABC Inc.\', '\ABC Inc.\US\'
DECLARE @oldParentPathLength INT,
@oldFullPathLength INT
SELECT @oldParentPathLength = LEN('\ABC Inc.\'),
@oldFullPathLength = LEN('\ABC Inc.\Seattle\')
UPDATE dbo.ConfigTree
SET
ParentPath = '\ABC Inc.\US'+SUBSTRING(ParentPath,
@oldParentPathLength, 880),
FullPath = '\ABC Inc.\US\Seattle'+SUBSTRING(FullPath,
@oldFullPathLength, 880)
WHERE FullPath LIKE
'\ABC Inc.\Seattle\%'
See for yourself how it affected the hierarchy:
EXEC dbo.SelectConfiguration
@NodeName = 'US'
![]()
Removing a node: flatten the company’s structure,
remove the US division:
DECLARE @oldPathLength INT
SELECT @oldPathLength = LEN('\ABC Inc.\US\')
UPDATE dbo.ConfigTree
SET
ParentPath = '\ABC Inc.'+SUBSTRING(ParentPath,
@oldPathLength, 880),
FullPath = '\ABC Inc.'+SUBSTRING(FullPath,
@oldPathLength, 880)
WHERE FullPath LIKE
'\ABC Inc.\US\Seattle\%'
DELETE dbo.ConfigTree
WHERE FullPath =
'\ABC Inc.\US\'
Moving a node: Transfer Jay to Midwest:
UPDATE dbo.ConfigTree
SET
ParentPath = '\ABC Inc.\Midwest\',
FullPath = '\ABC Inc.\Midwest\'+NodeName+'\'
WHERE NodeName =
'Jay'