Introduction
Object oriented programming is an ancient art. When you hear about inversion of control or dependency injection, you should know that these are new names for concepts that have been around for a long time. Today we are going to explore one such ancient technique: a pattern for populating a tree control using the inversion of control; from the early days of computing when resources were sparse.
Let us create a Windows Forms derive Tree View to visualise a hierarchy in an elegant manner. When I say elegant I mean:
- minimal memory signature,
- making control reusable,
- lazy loading data as the user drills down the tree, and
- allow various data objects to be attached to tree nodes.
There is really only one trick behind it: populate top level nodes and check if they have any children. If they do - insert a dummy node under them so that + appears left of the tree node. This will enable tree node expansion. Detect it and populate sub-tree using exactly the same technique.
In 2002 I have published an article on modelling hierarchies using SQL based DBMS. If the data that you visualise uses such storage there are some good tricks there to consider.
The Tree Node
First we need to define a tree node data structure. This is basic building block of our tree structure. It is independent of presentation method. You will be surprised to see that the class has no reference to its parent or its children. Because we are using lazy loading these references are resolved when needed. The code for resolving them is separated to the hierarchy feed class.
public class Node
{
public Node(string unique, string name, bool hasChildren)
{ Unique = unique; Name = name; HasChildren = hasChildren; }
public string Unique { get; set; }
public string Name { get; set; }
public bool HasChildren { get; set; }
}
The three fields are:
Unique This is the identifier (or the key) of this particular node.
Name This is human readable name for the node.
HasChildren True if node has children and can be expanded.
The Feed
We want the user to be able to use our control for visualizing any hierarchy with minimal effort. Here is a minimalistic tree feed interface. All you really need to implement is a function to query children of a node (or root nodes if parent is not given).
public interface IHierarchyFeed
{
List<Node> QueryChildren(Node parent);
}
For better understanding of how this feed works let us observe an implementation of this interface for enumerating files and folders in the file system.
public class FileSysHierarchyFeed : IHierarchyFeed
{
private string _rootPath;
private string _filter;
public FileSysHierarchyFeed(string rootPath, string filter)
{
_rootPath = rootPath;
_filter = filter;
}
public List<Node> QueryChildren(Node parent)
{
List<Node> children = new List<Node>();
if (parent == null)
AddFilesAndFolders(_rootPath, children);
else
AddFilesAndFolders(parent.Unique, children);
return children;
}
#pragma warning disable 168 // Ex variable is never used.
private void AddFilesAndFolders(string path, List children) {
foreach (string fso in Directory.EnumerateDirectories(path,"*.*",SearchOption.TopDirectoryOnly)) {
string unique=Path.Combine(path,fso);
try { children.Add(new Node(unique, Path.GetFileName(fso), Directory.EnumerateFileSystemEntries(unique).Count() > 0)); }
catch (UnauthorizedAccessException ex) { } // Ignore unauthorized access violations.
}
foreach(string file in Directory.EnumerateFiles(path,_filter)) children.Add(new Node(Path.Combine(path,file),Path.GetFileName(file),false));
}
}
Simple, isn’t it? You initialize the feed object with root path and filter, for example c:\ and *.*. When you call QueryChildren with null parameter it returns files and folders from the root path. It uses entire path as the node unique. When calling QueryChildren on a particular node it extracts path from the unique and uses it to enumerate files and folders under this folder.
You can easily write feeder class for database items, remote items, etc.
The TreeView Control
Last but not least - here is the tree view derived control.
public class NavigatorTree : TreeView
{
private class ExpandableNode
{
private Node _node;
private IHierarchyFeed _feed;
public ExpandableNode(Node node, IHierarchyFeed feed) { _node = node; _feed = feed; }
public void Expand(TreeNode treeNode) {
treeNode.TreeView.BeginUpdate();
treeNode.Nodes.RemoveAt(0); // Remove expandable node.
foreach (Node childNode in _feed.QueryChildren(_node))
{
// Add company.
TreeNode childTreeNode = treeNode.Nodes.Add(childNode.Name);
childTreeNode.Tag = childNode;
// Check if there are any children.
if (childNode.HasChildren)
{
TreeNode toExpandNode = childTreeNode.Nodes.Add("");
toExpandNode.Tag = new ExpandableNode(childNode, _feed);
}
}
treeNode.TreeView.EndUpdate();
}
}
private IHierarchyFeed _feed;
public void SetFeed(IHierarchyFeed feed)
{
_feed = feed;
Populate();
}
private void Populate()
{
Nodes.Clear();
BeginUpdate();
foreach (Node node in _feed.QueryChildren(null))
{
// Add company.
TreeNode treeNode = Nodes.Add(node.Name);
treeNode.Tag = node;
// Check if there are any children.
if (node.HasChildren)
{
TreeNode toExpandNode = treeNode.Nodes.Add("");
toExpandNode.Tag = new ExpandableNode(node, _feed);
}
}
EndUpdate();
}
protected override void OnBeforeExpand(TreeViewCancelEventArgs e)
{
// Check if node has only one child and that child is expandable.
if (e.Node.Nodes.Count == 1)
{
ExpandableNode expandable = e.Node.Nodes[0].Tag as ExpandableNode;
if (expandable != null)
expandable.Expand(e.Node);
}
}
}
Voila. It doesn’t get any simpler that that. You initialize tree control by calling SetFeed and providing feed class. For example:
navigatorTree.SetFeed(new FileSysHierarchyFeed("c:\\", "*.*"));
The control then calls Populate() which in turn populates first tree level and links every tree node with corresponding Node object via the Tag field. If a node has children the populate function adds a fake node of type ExpandableNode under it.
In OnBeforeExpand function the control checks for ExpandableNode. If it founds it - it calls it’s expand function to populate next tree level … and removes the fake node.
This is a little pet project of mine. It provides me with sort of in-depth understanding of embedded designs, programming languages and operating systems that only a hands-on approach can.
And now for something a bit more complex. Today we're going to discover the art of creating a stored procedure / function layer in Postgres database.
Let us first agree on terminology. In Postgres there is no difference between a stored procedure and a function. A procedure is merely a function returning void. Thus from now on we will only use term function.
Just in case you wonder - as a mature DBMS Postgres prepares (precompiles) all functions for optimal performance, exactly as its commercial competitors.In contemporary databases functions are commonly used to implement security layer. It is generally easier to give a user permission to execute a function that manipulates many database tables to complete a business operation then it is to assign him or her just the right permissions on all involved database tables for the same result.
Therefore modern designs introduce an additional layer of abstraction to access database tables. This layer is implemented via functions. In such scenario users can access database tables only through functions. They have no direct access to database tables.
To implement this design we need to know more about the Security of definer concept.
Security of definer
In Postgres a function can be defined to have “Security of definer” property set. You can set this propety in pgAdmin (see the screenshot bellow).
To set this property manually add SECURITY DEFINER to the end of function definition like this -
CREATE FUNCTION insert_city(character varying)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO city("name") VALUES($1);
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
Adding this to a function will make it run under the context of the owner of the function instead of context of the caller of the function. This allows a user to insert city into a table without having insert permission on it. As long as he has permission to execute this function and the owner of the function has permission to insert into city table. It is a way to implement chained security on Postgres.
Postgres functions that return datasets
Postgres functions that return data sets are poorly documented and their syntax might look a bit awkward to those of you used to SQL Server. When a function in Postgres returns multiple results it must be declared to return SETOF some type. Call syntax for such functions is a bit different then what you are used to. You call it using SELECT * FROM function() instead of usual SELECT function() call.
SELECT function_returning_setof(); -- Wrong!
SELECT * FROM function_returning_setop(); -- OK!
SELECT function_returning_scalar(); -- OK
Let's imagine we have a table of all cities with two fields – city_id of type SERIAL, and name of type VARCHAR(80). Actually we're imagining this througout this article.
Here is a code fragment showing how to write a function returning all cities.
CREATE FUNCTION list_all_cities()
RETURNS SETOF city AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN (SELECT * FROM city) LOOP
RETURN NEXT rec;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Call the function.
SELECT * FROM list_all_cities();
This code is using generic type called RECORD. Try experimenting with %ROWTYPE to obtain same results. Function above returns type SETOF city - each record in the set has the same structure as a record of city table. But what if you wanted to return another structure? One way is to create a type like this -
CREATE TYPE list_all_city_names_result_type AS (city_name varchar(80));
CREATE FUNCTION list_all_city_names()
RETURNS SETOF list_all_city_names_result_type AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN (SELECT "name" FROM city) LOOP
RETURN NEXT rec;
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM list_all_city_names();
If this imposes too harsh limitations then you can also use the generic RECORD data type as return type and tell the procedure what to expect when calling it.
CREATE FUNCTION list_all_city_names2()
RETURNS SETOF RECORD AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN (SELECT "name" FROM city) LOOP
RETURN NEXT rec;
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM list_all_city_names2()
AS ("city_name" varchar(80)); -- Expect varchar(80)
There's one more trick. I don't recommend it but I'm publishing it anyways. If you'd like to make things really simple for you then you could use another language instead of plpgsql. For example, using sql code you can write queries directly into your function like this -
CREATE FUNCTION getallzipcodes()
RETURNS SETOF zip AS
$BODY$
SELECT * FROM zip;
$BODY$
LANGUAGE 'sql';
Stored procedure layer and automatic code generation
When I create stored procedure layer I like to generate code for basic CRUD functions. I only write code for complex business functions by hand. Thus in my next article I am going to write code generator to do just that for Postgres/c++ pair.
But right now let's just explain some concepts. CRUD functions are - Create (new record from data), Read (given ID), Update (existing record given ID and new data) and Delete (given id) for single table. These functions are needed for every table. For example for table city you would have following functions:
- create_city(name),
- read_city(id),
- update_city(id,new_name),
- delete_city(id).
Two more types of functions are often needed. Get functions and list functions (sometimes called find functions). The difference between get and list function is that a get function always return single result. For example get_city_id_by_name(name) or get_user_by_phone(phone). Whereas list function returns all records that match the condition provided as parameters. For example - list_cities_starting_with(word) or list_persons_older_then(age) or list_all_countries().
Some people prefer list_ and other prefer find_ prefix for list functions. It is a matter of taste; as long as your programming stlye is consistent.
Table above shows functions that will be needed for almost any table and are therefore candidates for automatic code generation. We'll deal with code generation in one of our following articles.
I would like to write a small unix business application.
In .NET I would divide my code it into three packages - user interface, business logic, and database abstracton layer. 
User interface would be a simple form application which would host many user controls and take care for communication between them. Its responsibilities would include taking care for dataset flows and responding to events.
Business logic would be further decomposed to client part and business part. Client part would provide user controls that would use facade objects for various business domains. These would communicate with server part of business logic via web service or COM+; using integrated role based security.
Web service would further call business logic dynamic link library. This would communicate with database abstraction layer library using only datasets. Database abstraction layer library would communicate with SQL Server stored procedures. And these would manipulate database tables.
The task for the weekend is figuring out how to do the same in Ubuntu using C++ with various libraries and Postgres?