· 7 years ago · Jan 17, 2019, 06:26 PM
1DROP TABLE IF EXISTS subject;
2CREATE TABLE IF NOT EXISTS subject ( # Comment
3 subject_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # Subject ID
4 subject VARCHAR(25) NOT NULL, # Subject name
5 parent_id INT UNSIGNED NULL DEFAULT NULL, # Parent ID as seen from
6 PRIMARY KEY (subject_id), # the diagram refers to
7 UNIQUE (subject), # the subject_id field
8 INDEX (parent_id),
9 CONSTRAINT fk_subject_parent
10 FOREIGN KEY (parent_id)
11 REFERENCES subject (subject_id)
12 ON DELETE RESTRICT
13 ON UPDATE CASCADE
14) ENGINE=InnoDB DEFAULT CHARSET=utf8;
15
16INSERT INTO subject (subject, parent_id) VALUES
17 ('Science', NULL),
18 ('Mathematics', 1),
19 ('Calculus', 2),
20 ('Algebra', 2),
21 ('Geometry', 2),
22 ('Languages', NULL),
23 ('English', 6),
24 ('Latin', 6);
25
26SELECT * FROM subject;
27â•”â•â•â•â•â•â•â•â•â•â•â•â•â•¦â•â•â•â•â•â•â•â•â•â•â•â•â•â•¦â•â•â•â•â•â•â•â•â•â•â•â•—
28â•‘ subject_id â•‘ subject â•‘ parent_id â•‘
29â• â•â•â•â•â•â•â•â•â•â•â•â•â•¬â•â•â•â•â•â•â•â•â•â•â•â•â•â•¬â•â•â•â•â•â•â•â•â•â•â•â•£
30â•‘ 1 â•‘ Science â•‘ NULL â•‘
31â•‘ 2 â•‘ Mathematics â•‘ 1 â•‘
32â•‘ 3 â•‘ Calculus â•‘ 2 â•‘
33â•‘ 4 â•‘ Algebra â•‘ 2 â•‘
34â•‘ 5 â•‘ Geometry â•‘ 2 â•‘
35â•‘ 6 â•‘ Languages â•‘ NULL â•‘
36â•‘ 7 â•‘ English â•‘ 6 â•‘
37â•‘ 8 â•‘ Latin â•‘ 6 â•‘
38â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•©â•â•â•â•â•â•â•â•â•â•â•â•â•â•©â•â•â•â•â•â•â•â•â•â•â•â•
39
40DELIMITER$$
41
42DROP PROCEDURE IF EXISTS get_parent_subject_list;
43CREATE PROCEDURE get_parent_subject_list ()
44BEGIN
45 SELECT subject_id, subject
46 FROM subject
47 WHERE parent_id IS NULL
48 ORDER BY subject ASC;
49END$$
50
51
52DROP PROCEDURE IF EXISTS get_child_subject_list;
53CREATE PROCEDURE get_child_subject_list (IN parentID INT)
54BEGIN
55 SELECT subject_id, subject
56 FROM subject
57 WHERE parent_id = parentID
58 ORDER BY subject ASC;
59END$$
60
61DELIMITER ;
62
63procedure TForm1.CreateSubjectTreeView(Sender: TObject);
64var
65 i : integer;
66begin
67 i := 0;
68
69 q1.SQL.Clear;
70 q1.SQL.Add('CALL get_parent_subject_list()');
71 q1.Open;
72 q1.First;
73
74 while not q1.EOF do
75 begin
76 TreeView.Items.Add(nil, q1.Fields[1].Value);
77
78 q2.SQL.Clear;
79 q2.SQL.Add('CALL get_child_subject_list(' +
80 VarToStr(q1.Fields[0].Value) + ')');
81 q2.Open;
82 q2.First;
83
84 while not q2.EOF do
85 begin
86 TreeView.Items.AddChild(TreeView.Items.Item[i], q2.Fields[1].Value);
87 q2.Next;
88 end;
89
90 i := TreeView.Items.Count;
91 q1.Next;
92 end;
93end;
94
95+- Science
96| |
97| +- Mathematics
98|
99+- Languages
100 |
101 +- English
102 +- Latin
103
104+- Science
105| |
106| +- Mathematics
107| |
108| +- Calculus
109| +- Algebra
110| +- Geometry
111|
112+- Languages
113 |
114 +- English
115 +- Latin
116
117Select ID, Title, This, That from TREE where Parent_ID = :ID
118
119var i,imax,ikey,iParent : integer;
120 aNode,aParentNode : TTreeNode;
121 aData : TMyData;
122 aContainer : TSparseObjectArray; // cDataStructs , delphi fundamentals
123 aNodeIndex : TSparseObjectArray; // delphi 7
124 begin
125 try
126 aContainer := TSparseObjectArray.Create(true);
127 aNodeIndex := TSparseObjectArray.Create(False);
128 imax := 10000;
129 // create test data;
130 for i := 1 to imax do
131 begin
132 aData := TMyData.Create;
133 aData.iKey := i;
134 aData.iParent := Random(imax); // random parent
135 aData.Data := 'I:' + IntToStr(aData.iKey);
136 aContainer.Item[i] := aData;
137 end;
138
139 tv1.Items.Clear;
140 tv1.Items.BeginUpdate;
141 // build tree
142 // First Pass - build root tree nodes and create cross ref. index
143 for i := 1 to imax do
144 begin
145 aData := TMYData(aContainer.Item[i]);
146 aNode := tv1.Items.AddChild(nil,aData.Data);
147 aNodeIndex.Item[aData.iKey] := aNode;
148 end;
149 // Second Pass - find parent node using index and move node
150 for i := 1 to imax do
151 begin
152 aData := TMYData(aContainer.Item[i]);
153 aNode := TTreeNode(aNodeIndex.Item[aData.iKey]);
154 if aNodeIndex.HasItem(aData.iparent)
155 then begin
156 aParentNode := TTreeNode(aNodeIndex.Item[aData.iparent]);
157 aNode.MoveTo(aParentNode,naAddChild);
158 end;
159 end;
160 tv1.Items.EndUpdate;
161 tv1.Select( tv1.Items.GetFirstNode);
162 finally
163 aContainer.Free;
164 aNodeIndex.free;
165 end;
166 end;
167
168type
169 tElementoMenu = Class(TObject)
170 Comando : String;
171 //Nombre : String;
172 ID : String;
173 End;
174...
175procedure TForm1.CrearMenuDeArbol(dsOrigen: TDataSource; CampoID, IDPadre,
176 CampoComando, CampoCaption, CampoVisible: String; Raiz : TTreeNode = Nil);
177var
178 RamaActual, PrimeraRama : TTreeNode;
179 ElementoMenu : TElementoMenu;
180 iIndiceImagen : Integer;
181begin
182 RamaActual := Nil;
183 PrimeraRama := Nil;
184 if not assigned(Raiz) then
185 VaciarArbol;
186
187 with dsOrigen.DataSet do
188 begin
189 //For this example I use filter, however it can be use with WHERE sentence
190 Filtered := False;
191 IF not assigned(Raiz) then
192 Filter := IdPadre + ' IS NULL '
193 else
194 Filter := IDPadre + ' = ' + TElementoMenu(Raiz.Data).ID;
195 Filtered := True;
196
197 First;
198 while not Eof do
199 begin
200 if FieldByName(CampoVisible).AsString = 'Y' then
201 begin
202 ElementoMenu := TElementoMenu.Create;
203 ElementoMenu.Comando := FieldByName(CampoComando).AsString;
204 ElementoMenu.ID := FieldByName(CampoID).AsString;
205 //ElementoMenu.Nombre := FieldByName(CampoName).AsString; //Otros datos para agregar al elemento del menu
206 iIndiceImagen := 0;
207 if Not Assigned(Raiz) then
208 RamaActual := TreeView1.Items.AddObject(Nil, FieldByName(CampoCaption).AsString, ElementoMenu )
209 else
210 Begin
211 RamaActual := TreeView1.Items.AddChildObject(Raiz, FieldByName(CampoCaption).AsString, ElementoMenu );
212 iIndiceImagen := 1;
213 End;
214
215 RamaActual.ImageIndex := iIndiceImagen;
216 RamaActual.SelectedIndex := iIndiceImagen;
217 end;
218 Next;
219 end;
220
221 if not Assigned(Raiz) then
222 PrimeraRama := TreeView1.Items.GetFirstNode
223 else
224 PrimeraRama := Raiz.getFirstChild;
225
226 while Assigned(PrimeraRama) do
227 begin
228 CrearMenuDeArbol(dsOrigen, CampoID, IDPadre, CampoComando, CampoCaption, CampoVisible, PrimeraRama);
229 PrimeraRama := PrimeraRama.getNextSibling;
230 end;
231 end;
232end;
233
234
235procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
236begin
237 VaciarArbol;
238end;
239
240procedure TForm1.TreeView1DblClick(Sender: TObject);
241begin
242 if Assigned(treeView1.Selected) then
243 ShowMessage(TElementoMenu(treeView1.Selected.Data).Comando);
244end;
245
246procedure TForm1.VaciarArbol;
247var
248 itm : TTreeNode;
249begin
250 while TreeView1.Items.Count > 0 do
251 begin
252 itm := TreeView1.Items[TreeView1.Items.Count-1];
253 TElementoMenu(itm.Data).Free;
254 TreeView1.Items.Delete(itm);
255 end;
256end;
257
258procedure TForm1.Button1Click(Sender: TObject);
259begin
260 CrearMenuDeArbol(ds1, 'ID', 'PID', 'NAME', 'CAPTION', 'ISVISIBLE');
261 Treeview1.FullExpand;
262end;