Mega Code Archive
Processing Hierarchically Organized Data
<![CDATA[
Title: Processing Hierarchically Organized Data
Question: Hierarchically organized data storage can be applied to a great number of business tasks. It is a very flexible structure to implement parent-to-child type of relations between various business objects. Organizational structure, approval cycle, etc. - every object in these structures has to have an id and a reference to parent object id. All other attributes depend on your business requirements. This article describes main components of the solution and provides sample project to illustrate the approach.
Answer:
This article describes main components of the solution and provides sample project to illustrate the approach. Sample project includes Delphi 5 (using ADO components) project with UDL file describing the DB connection, MS SQL Server 7.0 script to create table and stored procedures (create the database first, name it MZ ), sample data in comma separated format TXT file (import it into the table). Note please that the sample project ZIP file may not be available as attachment to this article for some time.
Solution
Solution consists of three components:
1. DB - the table structure that supports hierarchical data.
2. DB Query - SELECT statement that returns hierarchical data ready for further use.
3. Delphi code - generating the tree view nodes.
The script below creates the table that will store hierarchically organized data. In this particular case fields are limited to ID, ParentID and Name only. It is just enough to display data in a TTreeView component.
CREATE TABLE [dbo].[mz_Hierarchy] (
[ID] [int] NOT NULL ,
[ParentID] [int] NOT NULL ,
[Name] [char] (50) NULL
) ON [PRIMARY]
The combination of the ID and ParentID is unique and identifies the node to be displayed on the TTreeView. ID field is a unique ID of the node and ParentID is a reference to the ID field of the record's parent record.
Traditional SELECT * query is not enough to retrieve data from the table having structure like this. The SQL query below (implemented as a stored procedure) is a little bit more complicated then the regular SELECT statement - recursive query, and returns data ready for further use in a project.
SELECT DISTINCT H1.ParentID, H1.ID, H1.Name
FROM mz_Hierarchy H1
JOIN mz_Hierarchy H2
ON H1.ParentID = H2.ID
ORDER BY H1.ParentID, H1.ID
After you create a database, table, and import sample data into the table try to run SELECT * statement from the Query Analyzer console and EXEC mz_GetHierarchy command to see the difference in results returned.
The code below loads the data retrieved from the DB into the TTreeView component (all of the overhead works on activating the ADO connection, stored procedures, etc. are in the project files attached to this article).
procedure TForm1.LoadTreeView;
var
tmpNode : TTreeNode;
ParentID,
ID : Integer;
NodeName : String;
DataPtr : PData;
begin
{Clear TTreeView, LoadTreeView is used later to refresh TTreeView}
TreeView1.Items.Clear;
{Loop through all data set}
with DataSource1.DataSet do
begin
First;
while not Eof do
begin
{Read fields}
ParentID := Fields.FieldByName('ParentID').Value;
ID := Fields.FieldByName('ID').Value;
{Trim clears the string value - SQL query returns all 50 characters}
NodeName := Trim( Fields.FieldByName('Name').Value );
{Instanciate pointer to data that will be assigned to tree node}
New(DataPtr);
{Assign values to data pointer fields}
DataPtr^.PID := ParentID;
DataPtr^.SID := ID;
{Check if the current node represents root level of a hierarchy}
if ParentID = ID then
{TRUE - add root level not to TTreeView}
TreeView1.Items.AddObject( nil, NodeName, DataPtr )
else
begin
{FALSE - Obtain TNode object of the current record parent}
tmpNode := GetParentNodeByID( ParentID );
{Add child object to the obtained parent TNode}
TreeView1.Items.AddChildObject( tmpNode, NodeName, DataPtr );
end; {if}
Next;
end; {while}
First;
end; {with}
end;
A couple notes.
First, each node created in a TTreeView component has a pointer to ID and ParentID assigned to it.
type
PData = ^TNodeData;
TNodeData = Record
PID: Integer;
SID: Integer;
end;
This information is used to locate parents in the hierarchy of the TTreeView nodes. Procedure provided below implements this search. It returns the TNode object that is a parent with the specified ID.
function TForm1.GetParentNodeByID( id: Integer ): TTreeNode;
var
i,
ii,
tmpID : Integer;
tmpNode: TTreeNode;
begin
{Get the number of items already in the TTreeView}
ii := TreeView1.Items.Count;
{Loop through all the items or until the parent TNode is found}
for i:= 0 to ii do
begin
{Get TNode}
tmpNode := TreeView1.Items.Item[i];
{Get its 'self ID' as it was in the DB}
tmpID := PData(tmpNode.Data)^.SID;
{If it is a parent we were looking for - exit and return TNode}
if id = tmpID then
begin
Result := tmpNode;
exit;
end;
end;
end;
The code above (and project attached to this article) may be not optimal implementation but it illustrates the approach that can be applied to XML as a data storage and/or ASP.
In case of ASP - the best idea would be to create a server object that implements hierarchical data processing and returns HTML code (using a bit of JavaScript and proper HTML tags/DOM objects identification you can create a tree view on your web page that functionally is really close to TTreeView component... to some extend).
And please do not pay much attention to the sample data - it is just a SAMPLE data after all.
]]>