THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Store your configuration settings as a hierarchy in a database.

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'

 

 

 

Published Sunday, August 24, 2008 5:37 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Somjade said:

Hi Alexander Kuznetsov how do we apply this query with override and merge and how about LIKE keyword performance

April 22, 2009 10:34 PM
 

Alex Kuznetsov said:

Somjade,

MERGE is available on 2008, but on 2008 I would just use hierarchyID which is built in. Also because the table is clustered on FullPath, LIKE predicates which I am using are indexed and as such very fast.

April 24, 2009 1:10 PM
 

Somjade said:

Alex,

Thanks

I have implement 2 methods called Override, Merge of data see below

OVR = Override

MRG = Merge

Key  Value   Level Method

---  ------- ----- ------

K#1  K#1V#1    0    OVR

K#1  K#1V#2    1    OVR

K#2  K#2V#1    0    MRG

K#2  K#1V#2    1    MRG

I needs result aggregates base on Level and Method columns below

Result:=

Key  Value        Level Method

---  ------------ ----- ------

K#1  K#1V#2          1   OVR

K#2  K#2V#1K#1V#2    0   MRG

do you have any suggestion query

September 3, 2009 6:10 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement