Author: Max Kleiner
In a relational database like the well known IBLocal you find a table DEPARTMENT
which has a self join on her own, means there is a tree inside with a Parent/Child
structure.
Answer:
In a relational database like the well known IBLocal you find a table DEPARTMENT
which has a self join on her own, means there is a tree inside with a Parent/Child
structure:
DEPT_NO DEPARTMENT HEAD_DEPT
100 Sales and Marketing 000
120 European Headquarters 100
121 Field Office Swiss 120
and so on...
This article is aimed mainly at people interested in trees and recurcions who need
to find their way around self referencing system and get going with a
object-visualisation in a tree. Furthermore it's an example for the Composite
Design Pattern.
First we need a startprocedure mainly to call the query, build the tree with
records in objects and show the tree at last:
1
2 procedure TForm1.btnTreeSelfClick(Sender: TObject);
3 var
4 dimlist: TStringList;
5 begin
6 dimlist := TStringlist.create;
7 datdepartment := TBusinessObj.Create(nil);
8 try
9 if datDepartment.open_recursiveQuery then
10 with TDims.create(nil, DimList) do
11 begin
12 FillTree(treeview1, nil);
13 Free;
14 end;
15 treeview1.FullExpand;
16 btnLTree.visible := true;
17 finally;
18 dimlist.Free;
19 datDepartment.Free;
20 end;
21 end;
The records in the query must follow one condition [child# > parent#], means a
child like "FieldOffice Swiss" has a child# 121 so the parent it belongs is 120.
So we call the query and open the dataset:
22
23 function TBusinessObj.open_recursiveQuery: boolean;
24 begin
25 result := false;
26 with qryselfTree do
27 begin
28 try
29 SQL.Clear;
30 SQL.add('SELECT dept_no, department, location, head_dept' +
31 ' FROM department ORDER BY dept_no');
32 open;
33 result := true;
34 except on EDataBaseError do
35 showmessage('data not found');
36 end;
37 end;
38 end;
Next we implement a class which holds at runtime the whole tree table in objects.
Every object is a stored record from the table DEPARTMENT with the attributes you
want to publish or manipulate at runtime. Grace the members FParent, FChild the
whole table is chained in objects with a top level object, in our case the
CORPORATE HEADQUARTERS. This top level object doesn't have a parent, the parent is
NIL.
39 TDims = class(TObject)
40 private
41 FstrDimArt: string;
42 FstrDimArtBez: string;
43 FParent: TDims;
44 FChilds: TList;
45 public
46 constructor create(Sender: TDims; myRegister: TStringList);
47 destructor Destroy; override;
48 procedure FillTree(aOl: TTreeview; xnode: TTreenode);
49 function IsDimartInChilds(DimArt: string): Boolean;
50 property DimArtBez: string read FstrDimArtBez;
51 property DimArt: string read FstrDimArt;
52 property treechilds: TList read FChilds;
53 end;
Now comes the real power part, a recursive constructor which collects all records
to build the tree in memory. When a parent like "Sales and Marketing" finds some
childs like "European Headquarters" it creates new objects in a recursion and adds
the object to the list:
FChilds.Add(TDims.create(self, myRegister));
Recursions aren't dark chapter by opening in Delphi the debug windows "Call Stack
and Local Variables" you'll learn a lot. When a function name appears anywhere
else in a statement block, the compiler interprets it as a recursive call to the
function itself.
The constructor has been used to recursively include another objects. But in every
tree an object without childs terminates without having cycles in them. The last
level of a tree is almost the deepness of recursions.
By the way do you know the explanation of a recursion in a "well behaved"
dictionary:
Recursion: See under Recursion ;)
Let jokes aside, here it is:
A programming technique in which a subroutine calls itself. Use care to ensure that
a recursion eventually exits. Otherwise, an infinite recursion will cause a stack
fault.
54
55 constructor TDims.create(Sender: TDims; myRegister: TStringList);
56 var
57 bmAkt: TBookmark;
58 begin
59 inherited Create;
60 with datDepartment.qrySelfTree do
61 begin
62 FstrDimArt := fieldByName('DEPT_NO').AsString;
63 FstrDimArtBez := fieldbyName('DEPARTMENT').AsString;
64 myRegister.AddObject(Format('%10s', [FstrDimArt]), self);
65 FChilds := TList.Create;
66 FParent := Sender;
67 bmAkt := GetBookmark;
68 if Locate('DEPT_NO', FstrDimArt, []) then
69 while not (EOF) do
70 begin
71 if (fieldByName('HEAD_DEPT').Asstring = FstrDimArt) then
72 FChilds.Add(TDims.create(self, myRegister));
73 Next;
74 end;
75 GotoBookmark(bmAkt);
76 FreeBookmark(bmAkt);
77 end;
78 end;
79
80 destructor TDims.Destroy;
81 var
82 i: integer;
83 begin
84 if FChilds <> nil then
85 for i := 0 to FChilds.Count - 1 do
86 TDims(FChilds[i]).Free;
87 FChilds.Free;
88 inherited Destroy;
89 end;
Now comes the last part, the most efficient way to represent the tabel tree in a
view. TTreeView represents a window that displays a hierarchical list of items,
such as the headings in a document, the entries in an index, or the files and
directories on a disk.
Use TTreeView to add an expanding and contracting outline to a form. Each node in a
tree view control consists of a label and a number of optional bitmapped images.
Each node can have a list of subnodes associated with it. By clicking on a node,
the user can expand or collapse the associated list of subnodes.
At run-time nodes can be added and inserted by using the TTreeNodes methods
AddChildFirst, AddChild, AddChildObjectFirst, AddChildObject, AddFirst, Add,
AddObjectFirst, AddObject and Insert. We only need AddChild:
90
91 procedure TDims.FillTree(aOl: TTreeview; xnode: TTreenode);
92 var
93 i: integer;
94 dbcontent: string[255];
95 begin
96 dbcontent := dimart + ' ' + dimartbez;
97 xnode := aOl.items.addchild(xnode, dbcontent);
98 for i := 0 to treechilds.Count - 1 do
99 TDims(treechilds.items[i]).FillTree(aOl, xnode);
100 end;
|
