| 875 |
lars |
1 |
<?php
|
|
|
2 |
// TO DO: better exceptions, use params
|
|
|
3 |
class tree
|
|
|
4 |
{
|
|
|
5 |
protected $db = null;
|
|
|
6 |
protected $options = null;
|
|
|
7 |
protected $default = array(
|
|
|
8 |
'structure_table' => 'structure', // the structure table (containing the id, left, right, level, parent_id and position fields)
|
|
|
9 |
'data_table' => 'structure', // table for additional fields (apart from structure ones, can be the same as structure_table)
|
|
|
10 |
'data2structure' => 'id', // which field from the data table maps to the structure table
|
|
|
11 |
'structure' => array( // which field (value) maps to what in the structure (key)
|
|
|
12 |
'id' => 'id',
|
|
|
13 |
'left' => 'lft',
|
|
|
14 |
'right' => 'rgt',
|
|
|
15 |
'level' => 'lvl',
|
|
|
16 |
'parent_id' => 'pid',
|
|
|
17 |
'position' => 'pos'
|
|
|
18 |
),
|
|
|
19 |
'data' => array() // array of additional fields from the data table
|
|
|
20 |
);
|
|
|
21 |
|
|
|
22 |
public function __construct(\vakata\database\IDB $db, array $options = array()) {
|
|
|
23 |
$this->db = $db;
|
|
|
24 |
$this->options = array_merge($this->default, $options);
|
|
|
25 |
}
|
|
|
26 |
|
|
|
27 |
public function get_node($id, $options = array()) {
|
|
|
28 |
$node = $this->db->one("
|
|
|
29 |
SELECT
|
|
|
30 |
s.".implode(", s.", $this->options['structure']).",
|
|
|
31 |
d.".implode(", d.", $this->options['data'])."
|
|
|
32 |
FROM
|
|
|
33 |
".$this->options['structure_table']." s,
|
|
|
34 |
".$this->options['data_table']." d
|
|
|
35 |
WHERE
|
|
|
36 |
s.".$this->options['structure']['id']." = d.".$this->options['data2structure']." AND
|
|
|
37 |
s.".$this->options['structure']['id']." = ".(int)$id
|
|
|
38 |
);
|
|
|
39 |
if(!$node) {
|
|
|
40 |
throw new Exception('Node does not exist');
|
|
|
41 |
}
|
|
|
42 |
if(isset($options['with_children'])) {
|
|
|
43 |
$node['children'] = $this->get_children($id, isset($options['deep_children']));
|
|
|
44 |
}
|
|
|
45 |
if(isset($options['with_path'])) {
|
|
|
46 |
$node['path'] = $this->get_path($id);
|
|
|
47 |
}
|
|
|
48 |
return $node;
|
|
|
49 |
}
|
|
|
50 |
|
|
|
51 |
public function get_children($id, $recursive = false) {
|
|
|
52 |
$sql = false;
|
|
|
53 |
if($recursive) {
|
|
|
54 |
$node = $this->get_node($id);
|
|
|
55 |
$sql = "
|
|
|
56 |
SELECT
|
|
|
57 |
s.".implode(", s.", $this->options['structure']).",
|
|
|
58 |
d.".implode(", d.", $this->options['data'])."
|
|
|
59 |
FROM
|
|
|
60 |
".$this->options['structure_table']." s,
|
|
|
61 |
".$this->options['data_table']." d
|
|
|
62 |
WHERE
|
|
|
63 |
s.".$this->options['structure']['id']." = d.".$this->options['data2structure']." AND
|
|
|
64 |
s.".$this->options['structure']['left']." > ".(int)$node[$this->options['structure']['left']]." AND
|
|
|
65 |
s.".$this->options['structure']['right']." < ".(int)$node[$this->options['structure']['right']]."
|
|
|
66 |
ORDER BY
|
|
|
67 |
s.".$this->options['structure']['left']."
|
|
|
68 |
";
|
|
|
69 |
}
|
|
|
70 |
else {
|
|
|
71 |
$sql = "
|
|
|
72 |
SELECT
|
|
|
73 |
s.".implode(", s.", $this->options['structure']).",
|
|
|
74 |
d.".implode(", d.", $this->options['data'])."
|
|
|
75 |
FROM
|
|
|
76 |
".$this->options['structure_table']." s,
|
|
|
77 |
".$this->options['data_table']." d
|
|
|
78 |
WHERE
|
|
|
79 |
s.".$this->options['structure']['id']." = d.".$this->options['data2structure']." AND
|
|
|
80 |
s.".$this->options['structure']['parent_id']." = ".(int)$id."
|
|
|
81 |
ORDER BY
|
|
|
82 |
s.".$this->options['structure']['position']."
|
|
|
83 |
";
|
|
|
84 |
}
|
|
|
85 |
return $this->db->all($sql);
|
|
|
86 |
}
|
|
|
87 |
|
|
|
88 |
public function get_path($id) {
|
|
|
89 |
$node = $this->get_node($id);
|
|
|
90 |
$sql = false;
|
|
|
91 |
if($node) {
|
|
|
92 |
$sql = "
|
|
|
93 |
SELECT
|
|
|
94 |
s.".implode(", s.", $this->options['structure']).",
|
|
|
95 |
d.".implode(", d.", $this->options['data'])."
|
|
|
96 |
FROM
|
|
|
97 |
".$this->options['structure_table']." s,
|
|
|
98 |
".$this->options['data_table']." d
|
|
|
99 |
WHERE
|
|
|
100 |
s.".$this->options['structure']['id']." = d.".$this->options['data2structure']." AND
|
|
|
101 |
s.".$this->options['structure']['left']." < ".(int)$node[$this->options['structure']['left']]." AND
|
|
|
102 |
s.".$this->options['structure']['right']." > ".(int)$node[$this->options['structure']['right']]."
|
|
|
103 |
ORDER BY
|
|
|
104 |
s.".$this->options['structure']['left']."
|
|
|
105 |
";
|
|
|
106 |
}
|
|
|
107 |
return $sql ? $this->db->all($sql) : false;
|
|
|
108 |
}
|
|
|
109 |
|
|
|
110 |
public function mk($parent, $position = 0, $data = array()) {
|
|
|
111 |
$parent = (int)$parent;
|
|
|
112 |
if($parent == 0) { throw new Exception('Parent is 0'); }
|
|
|
113 |
$parent = $this->get_node($parent, array('with_children'=> true));
|
|
|
114 |
if(!$parent['children']) { $position = 0; }
|
|
|
115 |
if($parent['children'] && $position >= count($parent['children'])) { $position = count($parent['children']); }
|
|
|
116 |
|
|
|
117 |
$sql = array();
|
|
|
118 |
$par = array();
|
|
|
119 |
|
|
|
120 |
// PREPARE NEW PARENT
|
|
|
121 |
// update positions of all next elements
|
|
|
122 |
$sql[] = "
|
|
|
123 |
UPDATE ".$this->options['structure_table']."
|
|
|
124 |
SET ".$this->options['structure']["position"]." = ".$this->options['structure']["position"]." + 1
|
|
|
125 |
WHERE
|
|
|
126 |
".$this->options['structure']["parent_id"]." = ".(int)$parent[$this->options['structure']['id']]." AND
|
|
|
127 |
".$this->options['structure']["position"]." >= ".$position."
|
|
|
128 |
";
|
|
|
129 |
$par[] = false;
|
|
|
130 |
|
|
|
131 |
// update left indexes
|
|
|
132 |
$ref_lft = false;
|
|
|
133 |
if(!$parent['children']) {
|
|
|
134 |
$ref_lft = $parent[$this->options['structure']["right"]];
|
|
|
135 |
}
|
|
|
136 |
else if(!isset($parent['children'][$position])) {
|
|
|
137 |
$ref_lft = $parent[$this->options['structure']["right"]];
|
|
|
138 |
}
|
|
|
139 |
else {
|
|
|
140 |
$ref_lft = $parent['children'][(int)$position][$this->options['structure']["left"]];
|
|
|
141 |
}
|
|
|
142 |
$sql[] = "
|
|
|
143 |
UPDATE ".$this->options['structure_table']."
|
|
|
144 |
SET ".$this->options['structure']["left"]." = ".$this->options['structure']["left"]." + 2
|
|
|
145 |
WHERE
|
|
|
146 |
".$this->options['structure']["left"]." >= ".(int)$ref_lft."
|
|
|
147 |
";
|
|
|
148 |
$par[] = false;
|
|
|
149 |
|
|
|
150 |
// update right indexes
|
|
|
151 |
$ref_rgt = false;
|
|
|
152 |
if(!$parent['children']) {
|
|
|
153 |
$ref_rgt = $parent[$this->options['structure']["right"]];
|
|
|
154 |
}
|
|
|
155 |
else if(!isset($parent['children'][$position])) {
|
|
|
156 |
$ref_rgt = $parent[$this->options['structure']["right"]];
|
|
|
157 |
}
|
|
|
158 |
else {
|
|
|
159 |
$ref_rgt = $parent['children'][(int)$position][$this->options['structure']["left"]] + 1;
|
|
|
160 |
}
|
|
|
161 |
$sql[] = "
|
|
|
162 |
UPDATE ".$this->options['structure_table']."
|
|
|
163 |
SET ".$this->options['structure']["right"]." = ".$this->options['structure']["right"]." + 2
|
|
|
164 |
WHERE
|
|
|
165 |
".$this->options['structure']["right"]." >= ".(int)$ref_rgt."
|
|
|
166 |
";
|
|
|
167 |
$par[] = false;
|
|
|
168 |
|
|
|
169 |
// INSERT NEW NODE IN STRUCTURE
|
|
|
170 |
$sql[] = "INSERT INTO ".$this->options['structure_table']." (".implode(",", $this->options['structure']).") VALUES (?".str_repeat(',?', count($this->options['structure']) - 1).")";
|
|
|
171 |
$tmp = array();
|
|
|
172 |
foreach($this->options['structure'] as $k => $v) {
|
|
|
173 |
switch($k) {
|
|
|
174 |
case 'id':
|
|
|
175 |
$tmp[] = null;
|
|
|
176 |
break;
|
|
|
177 |
case 'left':
|
|
|
178 |
$tmp[] = (int)$ref_lft;
|
|
|
179 |
break;
|
|
|
180 |
case 'right':
|
|
|
181 |
$tmp[] = (int)$ref_lft + 1;
|
|
|
182 |
break;
|
|
|
183 |
case 'level':
|
|
|
184 |
$tmp[] = (int)$parent[$v] + 1;
|
|
|
185 |
break;
|
|
|
186 |
case 'parent_id':
|
|
|
187 |
$tmp[] = $parent[$this->options['structure']['id']];
|
|
|
188 |
break;
|
|
|
189 |
case 'position':
|
|
|
190 |
$tmp[] = $position;
|
|
|
191 |
break;
|
|
|
192 |
default:
|
|
|
193 |
$tmp[] = null;
|
|
|
194 |
}
|
|
|
195 |
}
|
|
|
196 |
$par[] = $tmp;
|
|
|
197 |
|
|
|
198 |
foreach($sql as $k => $v) {
|
|
|
199 |
try {
|
|
|
200 |
$this->db->query($v, $par[$k]);
|
|
|
201 |
} catch(Exception $e) {
|
|
|
202 |
$this->reconstruct();
|
|
|
203 |
throw new Exception('Could not create');
|
|
|
204 |
}
|
|
|
205 |
}
|
|
|
206 |
if($data && count($data)) {
|
|
|
207 |
$node = $this->db->insert_id();
|
|
|
208 |
if(!$this->rn($node,$data)) {
|
|
|
209 |
$this->rm($node);
|
|
|
210 |
throw new Exception('Could not rename after create');
|
|
|
211 |
}
|
|
|
212 |
}
|
|
|
213 |
return $node;
|
|
|
214 |
}
|
|
|
215 |
|
|
|
216 |
public function mv($id, $parent, $position = 0) {
|
|
|
217 |
$id = (int)$id;
|
|
|
218 |
$parent = (int)$parent;
|
|
|
219 |
if($parent == 0 || $id == 0 || $id == 1) {
|
|
|
220 |
throw new Exception('Cannot move inside 0, or move root node');
|
|
|
221 |
}
|
|
|
222 |
|
|
|
223 |
$parent = $this->get_node($parent, array('with_children'=> true, 'with_path' => true));
|
|
|
224 |
$id = $this->get_node($id, array('with_children'=> true, 'deep_children' => true, 'with_path' => true));
|
|
|
225 |
if(!$parent['children']) {
|
|
|
226 |
$position = 0;
|
|
|
227 |
}
|
|
|
228 |
if($id[$this->options['structure']['parent_id']] == $parent[$this->options['structure']['id']] && $position > $id[$this->options['structure']['position']]) {
|
|
|
229 |
$position ++;
|
|
|
230 |
}
|
|
|
231 |
if($parent['children'] && $position >= count($parent['children'])) {
|
|
|
232 |
$position = count($parent['children']);
|
|
|
233 |
}
|
|
|
234 |
if($id[$this->options['structure']['left']] < $parent[$this->options['structure']['left']] && $id[$this->options['structure']['right']] > $parent[$this->options['structure']['right']]) {
|
|
|
235 |
throw new Exception('Could not move parent inside child');
|
|
|
236 |
}
|
|
|
237 |
|
|
|
238 |
$tmp = array();
|
|
|
239 |
$tmp[] = (int)$id[$this->options['structure']["id"]];
|
|
|
240 |
if($id['children'] && is_array($id['children'])) {
|
|
|
241 |
foreach($id['children'] as $c) {
|
|
|
242 |
$tmp[] = (int)$c[$this->options['structure']["id"]];
|
|
|
243 |
}
|
|
|
244 |
}
|
|
|
245 |
$width = (int)$id[$this->options['structure']["right"]] - (int)$id[$this->options['structure']["left"]] + 1;
|
|
|
246 |
|
|
|
247 |
$sql = array();
|
|
|
248 |
|
|
|
249 |
// PREPARE NEW PARENT
|
|
|
250 |
// update positions of all next elements
|
|
|
251 |
$sql[] = "
|
|
|
252 |
UPDATE ".$this->options['structure_table']."
|
|
|
253 |
SET ".$this->options['structure']["position"]." = ".$this->options['structure']["position"]." + 1
|
|
|
254 |
WHERE
|
|
|
255 |
".$this->options['structure']["id"]." != ".(int)$id[$this->options['structure']['id']]." AND
|
|
|
256 |
".$this->options['structure']["parent_id"]." = ".(int)$parent[$this->options['structure']['id']]." AND
|
|
|
257 |
".$this->options['structure']["position"]." >= ".$position."
|
|
|
258 |
";
|
|
|
259 |
|
|
|
260 |
// update left indexes
|
|
|
261 |
$ref_lft = false;
|
|
|
262 |
if(!$parent['children']) {
|
|
|
263 |
$ref_lft = $parent[$this->options['structure']["right"]];
|
|
|
264 |
}
|
|
|
265 |
else if(!isset($parent['children'][$position])) {
|
|
|
266 |
$ref_lft = $parent[$this->options['structure']["right"]];
|
|
|
267 |
}
|
|
|
268 |
else {
|
|
|
269 |
$ref_lft = $parent['children'][(int)$position][$this->options['structure']["left"]];
|
|
|
270 |
}
|
|
|
271 |
$sql[] = "
|
|
|
272 |
UPDATE ".$this->options['structure_table']."
|
|
|
273 |
SET ".$this->options['structure']["left"]." = ".$this->options['structure']["left"]." + ".$width."
|
|
|
274 |
WHERE
|
|
|
275 |
".$this->options['structure']["left"]." >= ".(int)$ref_lft." AND
|
|
|
276 |
".$this->options['structure']["id"]." NOT IN(".implode(',',$tmp).")
|
|
|
277 |
";
|
|
|
278 |
// update right indexes
|
|
|
279 |
$ref_rgt = false;
|
|
|
280 |
if(!$parent['children']) {
|
|
|
281 |
$ref_rgt = $parent[$this->options['structure']["right"]];
|
|
|
282 |
}
|
|
|
283 |
else if(!isset($parent['children'][$position])) {
|
|
|
284 |
$ref_rgt = $parent[$this->options['structure']["right"]];
|
|
|
285 |
}
|
|
|
286 |
else {
|
|
|
287 |
$ref_rgt = $parent['children'][(int)$position][$this->options['structure']["left"]] + 1;
|
|
|
288 |
}
|
|
|
289 |
$sql[] = "
|
|
|
290 |
UPDATE ".$this->options['structure_table']."
|
|
|
291 |
SET ".$this->options['structure']["right"]." = ".$this->options['structure']["right"]." + ".$width."
|
|
|
292 |
WHERE
|
|
|
293 |
".$this->options['structure']["right"]." >= ".(int)$ref_rgt." AND
|
|
|
294 |
".$this->options['structure']["id"]." NOT IN(".implode(',',$tmp).")
|
|
|
295 |
";
|
|
|
296 |
|
|
|
297 |
// MOVE THE ELEMENT AND CHILDREN
|
|
|
298 |
// left, right and level
|
|
|
299 |
$diff = $ref_lft - (int)$id[$this->options['structure']["left"]];
|
|
|
300 |
|
|
|
301 |
if($diff > 0) { $diff = $diff - $width; }
|
|
|
302 |
$ldiff = ((int)$parent[$this->options['structure']['level']] + 1) - (int)$id[$this->options['structure']['level']];
|
|
|
303 |
$sql[] = "
|
|
|
304 |
UPDATE ".$this->options['structure_table']."
|
|
|
305 |
SET ".$this->options['structure']["right"]." = ".$this->options['structure']["right"]." + ".$diff.",
|
|
|
306 |
".$this->options['structure']["left"]." = ".$this->options['structure']["left"]." + ".$diff.",
|
|
|
307 |
".$this->options['structure']["level"]." = ".$this->options['structure']["level"]." + ".$ldiff."
|
|
|
308 |
WHERE ".$this->options['structure']["id"]." IN(".implode(',',$tmp).")
|
|
|
309 |
";
|
|
|
310 |
// position and parent_id
|
|
|
311 |
$sql[] = "
|
|
|
312 |
UPDATE ".$this->options['structure_table']."
|
|
|
313 |
SET ".$this->options['structure']["position"]." = ".$position.",
|
|
|
314 |
".$this->options['structure']["parent_id"]." = ".(int)$parent[$this->options['structure']["id"]]."
|
|
|
315 |
WHERE ".$this->options['structure']["id"]." = ".(int)$id[$this->options['structure']['id']]."
|
|
|
316 |
";
|
|
|
317 |
|
|
|
318 |
// CLEAN OLD PARENT
|
|
|
319 |
// position of all next elements
|
|
|
320 |
$sql[] = "
|
|
|
321 |
UPDATE ".$this->options['structure_table']."
|
|
|
322 |
SET ".$this->options['structure']["position"]." = ".$this->options['structure']["position"]." - 1
|
|
|
323 |
WHERE
|
|
|
324 |
".$this->options['structure']["parent_id"]." = ".(int)$id[$this->options['structure']["parent_id"]]." AND
|
|
|
325 |
".$this->options['structure']["position"]." > ".(int)$id[$this->options['structure']["position"]];
|
|
|
326 |
// left indexes
|
|
|
327 |
$sql[] = "
|
|
|
328 |
UPDATE ".$this->options['structure_table']."
|
|
|
329 |
SET ".$this->options['structure']["left"]." = ".$this->options['structure']["left"]." - ".$width."
|
|
|
330 |
WHERE
|
|
|
331 |
".$this->options['structure']["left"]." > ".(int)$id[$this->options['structure']["right"]]." AND
|
|
|
332 |
".$this->options['structure']["id"]." NOT IN(".implode(',',$tmp).")
|
|
|
333 |
";
|
|
|
334 |
// right indexes
|
|
|
335 |
$sql[] = "
|
|
|
336 |
UPDATE ".$this->options['structure_table']."
|
|
|
337 |
SET ".$this->options['structure']["right"]." = ".$this->options['structure']["right"]." - ".$width."
|
|
|
338 |
WHERE
|
|
|
339 |
".$this->options['structure']["right"]." > ".(int)$id[$this->options['structure']["right"]]." AND
|
|
|
340 |
".$this->options['structure']["id"]." NOT IN(".implode(',',$tmp).")
|
|
|
341 |
";
|
|
|
342 |
|
|
|
343 |
foreach($sql as $k => $v) {
|
|
|
344 |
//echo preg_replace('@[\s\t]+@',' ',$v) ."\n";
|
|
|
345 |
try {
|
|
|
346 |
$this->db->query($v);
|
|
|
347 |
} catch(Exception $e) {
|
|
|
348 |
$this->reconstruct();
|
|
|
349 |
throw new Exception('Error moving');
|
|
|
350 |
}
|
|
|
351 |
}
|
|
|
352 |
return true;
|
|
|
353 |
}
|
|
|
354 |
|
|
|
355 |
public function cp($id, $parent, $position = 0) {
|
|
|
356 |
$id = (int)$id;
|
|
|
357 |
$parent = (int)$parent;
|
|
|
358 |
if($parent == 0 || $id == 0 || $id == 1) {
|
|
|
359 |
throw new Exception('Could not copy inside parent 0, or copy root nodes');
|
|
|
360 |
}
|
|
|
361 |
|
|
|
362 |
$parent = $this->get_node($parent, array('with_children'=> true, 'with_path' => true));
|
|
|
363 |
$id = $this->get_node($id, array('with_children'=> true, 'deep_children' => true, 'with_path' => true));
|
|
|
364 |
$old_nodes = $this->db->get("
|
|
|
365 |
SELECT * FROM ".$this->options['structure_table']."
|
|
|
366 |
WHERE ".$this->options['structure']["left"]." > ".$id[$this->options['structure']["left"]]." AND ".$this->options['structure']["right"]." < ".$id[$this->options['structure']["right"]]."
|
|
|
367 |
ORDER BY ".$this->options['structure']["left"]."
|
|
|
368 |
");
|
|
|
369 |
if(!$parent['children']) {
|
|
|
370 |
$position = 0;
|
|
|
371 |
}
|
|
|
372 |
if($id[$this->options['structure']['parent_id']] == $parent[$this->options['structure']['id']] && $position > $id[$this->options['structure']['position']]) {
|
|
|
373 |
//$position ++;
|
|
|
374 |
}
|
|
|
375 |
if($parent['children'] && $position >= count($parent['children'])) {
|
|
|
376 |
$position = count($parent['children']);
|
|
|
377 |
}
|
|
|
378 |
|
|
|
379 |
$tmp = array();
|
|
|
380 |
$tmp[] = (int)$id[$this->options['structure']["id"]];
|
|
|
381 |
if($id['children'] && is_array($id['children'])) {
|
|
|
382 |
foreach($id['children'] as $c) {
|
|
|
383 |
$tmp[] = (int)$c[$this->options['structure']["id"]];
|
|
|
384 |
}
|
|
|
385 |
}
|
|
|
386 |
$width = (int)$id[$this->options['structure']["right"]] - (int)$id[$this->options['structure']["left"]] + 1;
|
|
|
387 |
|
|
|
388 |
$sql = array();
|
|
|
389 |
|
|
|
390 |
// PREPARE NEW PARENT
|
|
|
391 |
// update positions of all next elements
|
|
|
392 |
$sql[] = "
|
|
|
393 |
UPDATE ".$this->options['structure_table']."
|
|
|
394 |
SET ".$this->options['structure']["position"]." = ".$this->options['structure']["position"]." + 1
|
|
|
395 |
WHERE
|
|
|
396 |
".$this->options['structure']["parent_id"]." = ".(int)$parent[$this->options['structure']['id']]." AND
|
|
|
397 |
".$this->options['structure']["position"]." >= ".$position."
|
|
|
398 |
";
|
|
|
399 |
|
|
|
400 |
// update left indexes
|
|
|
401 |
$ref_lft = false;
|
|
|
402 |
if(!$parent['children']) {
|
|
|
403 |
$ref_lft = $parent[$this->options['structure']["right"]];
|
|
|
404 |
}
|
|
|
405 |
else if(!isset($parent['children'][$position])) {
|
|
|
406 |
$ref_lft = $parent[$this->options['structure']["right"]];
|
|
|
407 |
}
|
|
|
408 |
else {
|
|
|
409 |
$ref_lft = $parent['children'][(int)$position][$this->options['structure']["left"]];
|
|
|
410 |
}
|
|
|
411 |
$sql[] = "
|
|
|
412 |
UPDATE ".$this->options['structure_table']."
|
|
|
413 |
SET ".$this->options['structure']["left"]." = ".$this->options['structure']["left"]." + ".$width."
|
|
|
414 |
WHERE
|
|
|
415 |
".$this->options['structure']["left"]." >= ".(int)$ref_lft."
|
|
|
416 |
";
|
|
|
417 |
// update right indexes
|
|
|
418 |
$ref_rgt = false;
|
|
|
419 |
if(!$parent['children']) {
|
|
|
420 |
$ref_rgt = $parent[$this->options['structure']["right"]];
|
|
|
421 |
}
|
|
|
422 |
else if(!isset($parent['children'][$position])) {
|
|
|
423 |
$ref_rgt = $parent[$this->options['structure']["right"]];
|
|
|
424 |
}
|
|
|
425 |
else {
|
|
|
426 |
$ref_rgt = $parent['children'][(int)$position][$this->options['structure']["left"]] + 1;
|
|
|
427 |
}
|
|
|
428 |
$sql[] = "
|
|
|
429 |
UPDATE ".$this->options['structure_table']."
|
|
|
430 |
SET ".$this->options['structure']["right"]." = ".$this->options['structure']["right"]." + ".$width."
|
|
|
431 |
WHERE
|
|
|
432 |
".$this->options['structure']["right"]." >= ".(int)$ref_rgt."
|
|
|
433 |
";
|
|
|
434 |
|
|
|
435 |
// MOVE THE ELEMENT AND CHILDREN
|
|
|
436 |
// left, right and level
|
|
|
437 |
$diff = $ref_lft - (int)$id[$this->options['structure']["left"]];
|
|
|
438 |
|
|
|
439 |
if($diff <= 0) { $diff = $diff - $width; }
|
|
|
440 |
$ldiff = ((int)$parent[$this->options['structure']['level']] + 1) - (int)$id[$this->options['structure']['level']];
|
|
|
441 |
|
|
|
442 |
// build all fields + data table
|
|
|
443 |
$fields = array_combine($this->options['structure'], $this->options['structure']);
|
|
|
444 |
unset($fields['id']);
|
|
|
445 |
$fields[$this->options['structure']["left"]] = $this->options['structure']["left"]." + ".$diff;
|
|
|
446 |
$fields[$this->options['structure']["right"]] = $this->options['structure']["right"]." + ".$diff;
|
|
|
447 |
$fields[$this->options['structure']["level"]] = $this->options['structure']["level"]." + ".$ldiff;
|
|
|
448 |
$sql[] = "
|
|
|
449 |
INSERT INTO ".$this->options['structure_table']." ( ".implode(',',array_keys($fields))." )
|
|
|
450 |
SELECT ".implode(',',array_values($fields))." FROM ".$this->options['structure_table']." WHERE ".$this->options['structure']["id"]." IN (".implode(",", $tmp).")
|
|
|
451 |
ORDER BY ".$this->options['structure']["level"]." ASC";
|
|
|
452 |
|
|
|
453 |
foreach($sql as $k => $v) {
|
|
|
454 |
try {
|
|
|
455 |
$this->db->query($v);
|
|
|
456 |
} catch(Exception $e) {
|
|
|
457 |
$this->reconstruct();
|
|
|
458 |
throw new Exception('Error copying');
|
|
|
459 |
}
|
|
|
460 |
}
|
|
|
461 |
$iid = (int)$this->db->insert_id();
|
|
|
462 |
|
|
|
463 |
try {
|
|
|
464 |
$this->db->query("
|
|
|
465 |
UPDATE ".$this->options['structure_table']."
|
|
|
466 |
SET ".$this->options['structure']["position"]." = ".$position.",
|
|
|
467 |
".$this->options['structure']["parent_id"]." = ".(int)$parent[$this->options['structure']["id"]]."
|
|
|
468 |
WHERE ".$this->options['structure']["id"]." = ".$iid."
|
|
|
469 |
");
|
|
|
470 |
} catch(Exception $e) {
|
|
|
471 |
$this->rm($iid);
|
|
|
472 |
$this->reconstruct();
|
|
|
473 |
throw new Exception('Could not update adjacency after copy');
|
|
|
474 |
}
|
|
|
475 |
$fields = $this->options['data'];
|
|
|
476 |
unset($fields['id']);
|
|
|
477 |
$update_fields = array();
|
|
|
478 |
foreach($fields as $f) {
|
|
|
479 |
$update_fields[] = $f.'=VALUES('.$f.')';
|
|
|
480 |
}
|
|
|
481 |
$update_fields = implode(',', $update_fields);
|
|
|
482 |
if(count($fields)) {
|
|
|
483 |
try {
|
|
|
484 |
$this->db->query("
|
|
|
485 |
INSERT INTO ".$this->options['data_table']." (".$this->options['data2structure'].",".implode(",",$fields).")
|
|
|
486 |
SELECT ".$iid.",".implode(",",$fields)." FROM ".$this->options['data_table']." WHERE ".$this->options['data2structure']." = ".$id[$this->options['data2structure']]."
|
|
|
487 |
ON DUPLICATE KEY UPDATE ".$update_fields."
|
|
|
488 |
");
|
|
|
489 |
}
|
|
|
490 |
catch(Exception $e) {
|
|
|
491 |
$this->rm($iid);
|
|
|
492 |
$this->reconstruct();
|
|
|
493 |
throw new Exception('Could not update data after copy');
|
|
|
494 |
}
|
|
|
495 |
}
|
|
|
496 |
|
|
|
497 |
// manually fix all parent_ids and copy all data
|
|
|
498 |
$new_nodes = $this->db->get("
|
|
|
499 |
SELECT * FROM ".$this->options['structure_table']."
|
|
|
500 |
WHERE ".$this->options['structure']["left"]." > ".$ref_lft." AND ".$this->options['structure']["right"]." < ".($ref_lft + $width - 1)." AND ".$this->options['structure']["id"]." != ".$iid."
|
|
|
501 |
ORDER BY ".$this->options['structure']["left"]."
|
|
|
502 |
");
|
|
|
503 |
$parents = array();
|
|
|
504 |
foreach($new_nodes as $node) {
|
|
|
505 |
if(!isset($parents[$node[$this->options['structure']["left"]]])) { $parents[$node[$this->options['structure']["left"]]] = $iid; }
|
|
|
506 |
for($i = $node[$this->options['structure']["left"]] + 1; $i < $node[$this->options['structure']["right"]]; $i++) {
|
|
|
507 |
$parents[$i] = $node[$this->options['structure']["id"]];
|
|
|
508 |
}
|
|
|
509 |
}
|
|
|
510 |
$sql = array();
|
|
|
511 |
foreach($new_nodes as $k => $node) {
|
|
|
512 |
$sql[] = "
|
|
|
513 |
UPDATE ".$this->options['structure_table']."
|
|
|
514 |
SET ".$this->options['structure']["parent_id"]." = ".$parents[$node[$this->options['structure']["left"]]]."
|
|
|
515 |
WHERE ".$this->options['structure']["id"]." = ".(int)$node[$this->options['structure']["id"]]."
|
|
|
516 |
";
|
|
|
517 |
if(count($fields)) {
|
|
|
518 |
$up = "";
|
|
|
519 |
foreach($fields as $f)
|
|
|
520 |
$sql[] = "
|
|
|
521 |
INSERT INTO ".$this->options['data_table']." (".$this->options['data2structure'].",".implode(",",$fields).")
|
|
|
522 |
SELECT ".(int)$node[$this->options['structure']["id"]].",".implode(",",$fields)." FROM ".$this->options['data_table']."
|
|
|
523 |
WHERE ".$this->options['data2structure']." = ".$old_nodes[$k][$this->options['structure']['id']]."
|
|
|
524 |
ON DUPLICATE KEY UPDATE ".$update_fields."
|
|
|
525 |
";
|
|
|
526 |
}
|
|
|
527 |
}
|
|
|
528 |
//var_dump($sql);
|
|
|
529 |
foreach($sql as $k => $v) {
|
|
|
530 |
try {
|
|
|
531 |
$this->db->query($v);
|
|
|
532 |
} catch(Exception $e) {
|
|
|
533 |
$this->rm($iid);
|
|
|
534 |
$this->reconstruct();
|
|
|
535 |
throw new Exception('Error copying');
|
|
|
536 |
}
|
|
|
537 |
}
|
|
|
538 |
return $iid;
|
|
|
539 |
}
|
|
|
540 |
|
|
|
541 |
public function rm($id) {
|
|
|
542 |
$id = (int)$id;
|
|
|
543 |
if(!$id || $id === 1) { throw new Exception('Could not create inside roots'); }
|
|
|
544 |
$data = $this->get_node($id, array('with_children' => true, 'deep_children' => true));
|
|
|
545 |
$lft = (int)$data[$this->options['structure']["left"]];
|
|
|
546 |
$rgt = (int)$data[$this->options['structure']["right"]];
|
|
|
547 |
$pid = (int)$data[$this->options['structure']["parent_id"]];
|
|
|
548 |
$pos = (int)$data[$this->options['structure']["position"]];
|
|
|
549 |
$dif = $rgt - $lft + 1;
|
|
|
550 |
|
|
|
551 |
$sql = array();
|
|
|
552 |
// deleting node and its children from structure
|
|
|
553 |
$sql[] = "
|
|
|
554 |
DELETE FROM ".$this->options['structure_table']."
|
|
|
555 |
WHERE ".$this->options['structure']["left"]." >= ".(int)$lft." AND ".$this->options['structure']["right"]." <= ".(int)$rgt."
|
|
|
556 |
";
|
|
|
557 |
// shift left indexes of nodes right of the node
|
|
|
558 |
$sql[] = "
|
|
|
559 |
UPDATE ".$this->options['structure_table']."
|
|
|
560 |
SET ".$this->options['structure']["left"]." = ".$this->options['structure']["left"]." - ".(int)$dif."
|
|
|
561 |
WHERE ".$this->options['structure']["left"]." > ".(int)$rgt."
|
|
|
562 |
";
|
|
|
563 |
// shift right indexes of nodes right of the node and the node's parents
|
|
|
564 |
$sql[] = "
|
|
|
565 |
UPDATE ".$this->options['structure_table']."
|
|
|
566 |
SET ".$this->options['structure']["right"]." = ".$this->options['structure']["right"]." - ".(int)$dif."
|
|
|
567 |
WHERE ".$this->options['structure']["right"]." > ".(int)$lft."
|
|
|
568 |
";
|
|
|
569 |
// Update position of siblings below the deleted node
|
|
|
570 |
$sql[] = "
|
|
|
571 |
UPDATE ".$this->options['structure_table']."
|
|
|
572 |
SET ".$this->options['structure']["position"]." = ".$this->options['structure']["position"]." - 1
|
|
|
573 |
WHERE ".$this->options['structure']["parent_id"]." = ".$pid." AND ".$this->options['structure']["position"]." > ".(int)$pos."
|
|
|
574 |
";
|
|
|
575 |
// delete from data table
|
|
|
576 |
if($this->options['data_table']) {
|
|
|
577 |
$tmp = array();
|
|
|
578 |
$tmp[] = (int)$data['id'];
|
|
|
579 |
if($data['children'] && is_array($data['children'])) {
|
|
|
580 |
foreach($data['children'] as $v) {
|
|
|
581 |
$tmp[] = (int)$v['id'];
|
|
|
582 |
}
|
|
|
583 |
}
|
|
|
584 |
$sql[] = "DELETE FROM ".$this->options['data_table']." WHERE ".$this->options['data2structure']." IN (".implode(',',$tmp).")";
|
|
|
585 |
}
|
|
|
586 |
|
|
|
587 |
foreach($sql as $v) {
|
|
|
588 |
try {
|
|
|
589 |
$this->db->query($v);
|
|
|
590 |
} catch(Exception $e) {
|
|
|
591 |
$this->reconstruct();
|
|
|
592 |
throw new Exception('Could not remove');
|
|
|
593 |
}
|
|
|
594 |
}
|
|
|
595 |
return true;
|
|
|
596 |
}
|
|
|
597 |
|
|
|
598 |
public function rn($id, $data) {
|
|
|
599 |
if(!(int)$this->db->one('SELECT 1 AS res FROM '.$this->options['structure_table'].' WHERE '.$this->options['structure']['id'].' = '.(int)$id)) {
|
|
|
600 |
throw new Exception('Could not rename non-existing node');
|
|
|
601 |
}
|
|
|
602 |
$tmp = array();
|
|
|
603 |
foreach($this->options['data'] as $v) {
|
|
|
604 |
if(isset($data[$v])) {
|
|
|
605 |
$tmp[$v] = $data[$v];
|
|
|
606 |
}
|
|
|
607 |
}
|
|
|
608 |
if(count($tmp)) {
|
|
|
609 |
$tmp[$this->options['data2structure']] = $id;
|
|
|
610 |
$sql = "
|
|
|
611 |
INSERT INTO
|
|
|
612 |
".$this->options['data_table']." (".implode(',', array_keys($tmp)).")
|
|
|
613 |
VALUES(?".str_repeat(',?', count($tmp) - 1).")
|
|
|
614 |
ON DUPLICATE KEY UPDATE
|
|
|
615 |
".implode(' = ?, ', array_keys($tmp))." = ?";
|
|
|
616 |
$par = array_merge(array_values($tmp), array_values($tmp));
|
|
|
617 |
try {
|
|
|
618 |
$this->db->query($sql, $par);
|
|
|
619 |
}
|
|
|
620 |
catch(Exception $e) {
|
|
|
621 |
throw new Exception('Could not rename');
|
|
|
622 |
}
|
|
|
623 |
}
|
|
|
624 |
return true;
|
|
|
625 |
}
|
|
|
626 |
|
|
|
627 |
public function analyze($get_errors = false) {
|
|
|
628 |
$report = array();
|
|
|
629 |
if((int)$this->db->one("SELECT COUNT(".$this->options['structure']["id"].") AS res FROM ".$this->options['structure_table']." WHERE ".$this->options['structure']["parent_id"]." = 0") !== 1) {
|
|
|
630 |
$report[] = "No or more than one root node.";
|
|
|
631 |
}
|
|
|
632 |
if((int)$this->db->one("SELECT ".$this->options['structure']["left"]." AS res FROM ".$this->options['structure_table']." WHERE ".$this->options['structure']["parent_id"]." = 0") !== 1) {
|
|
|
633 |
$report[] = "Root node's left index is not 1.";
|
|
|
634 |
}
|
|
|
635 |
if((int)$this->db->one("
|
|
|
636 |
SELECT
|
|
|
637 |
COUNT(".$this->options['structure']['id'].") AS res
|
|
|
638 |
FROM ".$this->options['structure_table']." s
|
|
|
639 |
WHERE
|
|
|
640 |
".$this->options['structure']["parent_id"]." != 0 AND
|
|
|
641 |
(SELECT COUNT(".$this->options['structure']['id'].") FROM ".$this->options['structure_table']." WHERE ".$this->options['structure']["id"]." = s.".$this->options['structure']["parent_id"].") = 0") > 0
|
|
|
642 |
) {
|
|
|
643 |
$report[] = "Missing parents.";
|
|
|
644 |
}
|
|
|
645 |
if(
|
|
|
646 |
(int)$this->db->one("SELECT MAX(".$this->options['structure']["right"].") AS res FROM ".$this->options['structure_table']) / 2 !=
|
|
|
647 |
(int)$this->db->one("SELECT COUNT(".$this->options['structure']["id"].") AS res FROM ".$this->options['structure_table'])
|
|
|
648 |
) {
|
|
|
649 |
$report[] = "Right index does not match node count.";
|
|
|
650 |
}
|
|
|
651 |
if(
|
|
|
652 |
(int)$this->db->one("SELECT COUNT(DISTINCT ".$this->options['structure']["right"].") AS res FROM ".$this->options['structure_table']) !=
|
|
|
653 |
(int)$this->db->one("SELECT COUNT(DISTINCT ".$this->options['structure']["left"].") AS res FROM ".$this->options['structure_table'])
|
|
|
654 |
) {
|
|
|
655 |
$report[] = "Duplicates in nested set.";
|
|
|
656 |
}
|
|
|
657 |
if(
|
|
|
658 |
(int)$this->db->one("SELECT COUNT(DISTINCT ".$this->options['structure']["id"].") AS res FROM ".$this->options['structure_table']) !=
|
|
|
659 |
(int)$this->db->one("SELECT COUNT(DISTINCT ".$this->options['structure']["left"].") AS res FROM ".$this->options['structure_table'])
|
|
|
660 |
) {
|
|
|
661 |
$report[] = "Left indexes not unique.";
|
|
|
662 |
}
|
|
|
663 |
if(
|
|
|
664 |
(int)$this->db->one("SELECT COUNT(DISTINCT ".$this->options['structure']["id"].") AS res FROM ".$this->options['structure_table']) !=
|
|
|
665 |
(int)$this->db->one("SELECT COUNT(DISTINCT ".$this->options['structure']["right"].") AS res FROM ".$this->options['structure_table'])
|
|
|
666 |
) {
|
|
|
667 |
$report[] = "Right indexes not unique.";
|
|
|
668 |
}
|
|
|
669 |
if(
|
|
|
670 |
(int)$this->db->one("
|
|
|
671 |
SELECT
|
|
|
672 |
s1.".$this->options['structure']["id"]." AS res
|
|
|
673 |
FROM ".$this->options['structure_table']." s1, ".$this->options['structure_table']." s2
|
|
|
674 |
WHERE
|
|
|
675 |
s1.".$this->options['structure']['id']." != s2.".$this->options['structure']['id']." AND
|
|
|
676 |
s1.".$this->options['structure']['left']." = s2.".$this->options['structure']['right']."
|
|
|
677 |
LIMIT 1")
|
|
|
678 |
) {
|
|
|
679 |
$report[] = "Nested set - matching left and right indexes.";
|
|
|
680 |
}
|
|
|
681 |
if(
|
|
|
682 |
(int)$this->db->one("
|
|
|
683 |
SELECT
|
|
|
684 |
".$this->options['structure']["id"]." AS res
|
|
|
685 |
FROM ".$this->options['structure_table']." s
|
|
|
686 |
WHERE
|
|
|
687 |
".$this->options['structure']['position']." >= (
|
|
|
688 |
SELECT
|
|
|
689 |
COUNT(".$this->options['structure']["id"].")
|
|
|
690 |
FROM ".$this->options['structure_table']."
|
|
|
691 |
WHERE ".$this->options['structure']['parent_id']." = s.".$this->options['structure']['parent_id']."
|
|
|
692 |
)
|
|
|
693 |
LIMIT 1") ||
|
|
|
694 |
(int)$this->db->one("
|
|
|
695 |
SELECT
|
|
|
696 |
s1.".$this->options['structure']["id"]." AS res
|
|
|
697 |
FROM ".$this->options['structure_table']." s1, ".$this->options['structure_table']." s2
|
|
|
698 |
WHERE
|
|
|
699 |
s1.".$this->options['structure']['id']." != s2.".$this->options['structure']['id']." AND
|
|
|
700 |
s1.".$this->options['structure']['parent_id']." = s2.".$this->options['structure']['parent_id']." AND
|
|
|
701 |
s1.".$this->options['structure']['position']." = s2.".$this->options['structure']['position']."
|
|
|
702 |
LIMIT 1")
|
|
|
703 |
) {
|
|
|
704 |
$report[] = "Positions not correct.";
|
|
|
705 |
}
|
|
|
706 |
if((int)$this->db->one("
|
|
|
707 |
SELECT
|
|
|
708 |
COUNT(".$this->options['structure']["id"].") FROM ".$this->options['structure_table']." s
|
|
|
709 |
WHERE
|
|
|
710 |
(
|
|
|
711 |
SELECT
|
|
|
712 |
COUNT(".$this->options['structure']["id"].")
|
|
|
713 |
FROM ".$this->options['structure_table']."
|
|
|
714 |
WHERE
|
|
|
715 |
".$this->options['structure']["right"]." < s.".$this->options['structure']["right"]." AND
|
|
|
716 |
".$this->options['structure']["left"]." > s.".$this->options['structure']["left"]." AND
|
|
|
717 |
".$this->options['structure']["level"]." = s.".$this->options['structure']["level"]." + 1
|
|
|
718 |
) !=
|
|
|
719 |
(
|
|
|
720 |
SELECT
|
|
|
721 |
COUNT(*)
|
|
|
722 |
FROM ".$this->options['structure_table']."
|
|
|
723 |
WHERE
|
|
|
724 |
".$this->options['structure']["parent_id"]." = s.".$this->options['structure']["id"]."
|
|
|
725 |
)")
|
|
|
726 |
) {
|
|
|
727 |
$report[] = "Adjacency and nested set do not match.";
|
|
|
728 |
}
|
|
|
729 |
if(
|
|
|
730 |
$this->options['data_table'] &&
|
|
|
731 |
(int)$this->db->one("
|
|
|
732 |
SELECT
|
|
|
733 |
COUNT(".$this->options['structure']["id"].") AS res
|
|
|
734 |
FROM ".$this->options['structure_table']." s
|
|
|
735 |
WHERE
|
|
|
736 |
(SELECT COUNT(".$this->options['data2structure'].") FROM ".$this->options['data_table']." WHERE ".$this->options['data2structure']." = s.".$this->options['structure']["id"].") = 0
|
|
|
737 |
")
|
|
|
738 |
) {
|
|
|
739 |
$report[] = "Missing records in data table.";
|
|
|
740 |
}
|
|
|
741 |
if(
|
|
|
742 |
$this->options['data_table'] &&
|
|
|
743 |
(int)$this->db->one("
|
|
|
744 |
SELECT
|
|
|
745 |
COUNT(".$this->options['data2structure'].") AS res
|
|
|
746 |
FROM ".$this->options['data_table']." s
|
|
|
747 |
WHERE
|
|
|
748 |
(SELECT COUNT(".$this->options['structure']["id"].") FROM ".$this->options['structure_table']." WHERE ".$this->options['structure']["id"]." = s.".$this->options['data2structure'].") = 0
|
|
|
749 |
")
|
|
|
750 |
) {
|
|
|
751 |
$report[] = "Dangling records in data table.";
|
|
|
752 |
}
|
|
|
753 |
return $get_errors ? $report : count($report) == 0;
|
|
|
754 |
}
|
|
|
755 |
|
|
|
756 |
public function reconstruct($analyze = true) {
|
|
|
757 |
if($analyze && $this->analyze()) { return true; }
|
|
|
758 |
|
|
|
759 |
if(!$this->db->query("" .
|
|
|
760 |
"CREATE TEMPORARY TABLE temp_tree (" .
|
|
|
761 |
"".$this->options['structure']["id"]." INTEGER NOT NULL, " .
|
|
|
762 |
"".$this->options['structure']["parent_id"]." INTEGER NOT NULL, " .
|
|
|
763 |
"". $this->options['structure']["position"]." INTEGER NOT NULL" .
|
|
|
764 |
") "
|
|
|
765 |
)) { return false; }
|
|
|
766 |
if(!$this->db->query("" .
|
|
|
767 |
"INSERT INTO temp_tree " .
|
|
|
768 |
"SELECT " .
|
|
|
769 |
"".$this->options['structure']["id"].", " .
|
|
|
770 |
"".$this->options['structure']["parent_id"].", " .
|
|
|
771 |
"".$this->options['structure']["position"]." " .
|
|
|
772 |
"FROM ".$this->options['structure_table'].""
|
|
|
773 |
)) { return false; }
|
|
|
774 |
|
|
|
775 |
if(!$this->db->query("" .
|
|
|
776 |
"CREATE TEMPORARY TABLE temp_stack (" .
|
|
|
777 |
"".$this->options['structure']["id"]." INTEGER NOT NULL, " .
|
|
|
778 |
"".$this->options['structure']["left"]." INTEGER, " .
|
|
|
779 |
"".$this->options['structure']["right"]." INTEGER, " .
|
|
|
780 |
"".$this->options['structure']["level"]." INTEGER, " .
|
|
|
781 |
"stack_top INTEGER NOT NULL, " .
|
|
|
782 |
"".$this->options['structure']["parent_id"]." INTEGER, " .
|
|
|
783 |
"".$this->options['structure']["position"]." INTEGER " .
|
|
|
784 |
") "
|
|
|
785 |
)) { return false; }
|
|
|
786 |
|
|
|
787 |
$counter = 2;
|
|
|
788 |
if(!$this->db->query("SELECT COUNT(*) FROM temp_tree")) {
|
|
|
789 |
return false;
|
|
|
790 |
}
|
|
|
791 |
$this->db->nextr();
|
|
|
792 |
$maxcounter = (int) $this->db->f(0) * 2;
|
|
|
793 |
$currenttop = 1;
|
|
|
794 |
if(!$this->db->query("" .
|
|
|
795 |
"INSERT INTO temp_stack " .
|
|
|
796 |
"SELECT " .
|
|
|
797 |
"".$this->options['structure']["id"].", " .
|
|
|
798 |
"1, " .
|
|
|
799 |
"NULL, " .
|
|
|
800 |
"0, " .
|
|
|
801 |
"1, " .
|
|
|
802 |
"".$this->options['structure']["parent_id"].", " .
|
|
|
803 |
"".$this->options['structure']["position"]." " .
|
|
|
804 |
"FROM temp_tree " .
|
|
|
805 |
"WHERE ".$this->options['structure']["parent_id"]." = 0"
|
|
|
806 |
)) { return false; }
|
|
|
807 |
if(!$this->db->query("DELETE FROM temp_tree WHERE ".$this->options['structure']["parent_id"]." = 0")) {
|
|
|
808 |
return false;
|
|
|
809 |
}
|
|
|
810 |
|
|
|
811 |
while ($counter <= $maxcounter) {
|
|
|
812 |
if(!$this->db->query("" .
|
|
|
813 |
"SELECT " .
|
|
|
814 |
"temp_tree.".$this->options['structure']["id"]." AS tempmin, " .
|
|
|
815 |
"temp_tree.".$this->options['structure']["parent_id"]." AS pid, " .
|
|
|
816 |
"temp_tree.".$this->options['structure']["position"]." AS lid " .
|
|
|
817 |
"FROM temp_stack, temp_tree " .
|
|
|
818 |
"WHERE " .
|
|
|
819 |
"temp_stack.".$this->options['structure']["id"]." = temp_tree.".$this->options['structure']["parent_id"]." AND " .
|
|
|
820 |
"temp_stack.stack_top = ".$currenttop." " .
|
|
|
821 |
"ORDER BY temp_tree.".$this->options['structure']["position"]." ASC LIMIT 1"
|
|
|
822 |
)) { return false; }
|
|
|
823 |
|
|
|
824 |
if($this->db->nextr()) {
|
|
|
825 |
$tmp = $this->db->f("tempmin");
|
|
|
826 |
|
|
|
827 |
$q = "INSERT INTO temp_stack (stack_top, ".$this->options['structure']["id"].", ".$this->options['structure']["left"].", ".$this->options['structure']["right"].", ".$this->options['structure']["level"].", ".$this->options['structure']["parent_id"].", ".$this->options['structure']["position"].") VALUES(".($currenttop + 1).", ".$tmp.", ".$counter.", NULL, ".$currenttop.", ".$this->db->f("pid").", ".$this->db->f("lid").")";
|
|
|
828 |
if(!$this->db->query($q)) {
|
|
|
829 |
return false;
|
|
|
830 |
}
|
|
|
831 |
if(!$this->db->query("DELETE FROM temp_tree WHERE ".$this->options['structure']["id"]." = ".$tmp)) {
|
|
|
832 |
return false;
|
|
|
833 |
}
|
|
|
834 |
$counter++;
|
|
|
835 |
$currenttop++;
|
|
|
836 |
}
|
|
|
837 |
else {
|
|
|
838 |
if(!$this->db->query("" .
|
|
|
839 |
"UPDATE temp_stack SET " .
|
|
|
840 |
"".$this->options['structure']["right"]." = ".$counter.", " .
|
|
|
841 |
"stack_top = -stack_top " .
|
|
|
842 |
"WHERE stack_top = ".$currenttop
|
|
|
843 |
)) { return false; }
|
|
|
844 |
$counter++;
|
|
|
845 |
$currenttop--;
|
|
|
846 |
}
|
|
|
847 |
}
|
|
|
848 |
|
|
|
849 |
$temp_fields = $this->options['structure'];
|
|
|
850 |
unset($temp_fields["parent_id"]);
|
|
|
851 |
unset($temp_fields["position"]);
|
|
|
852 |
unset($temp_fields["left"]);
|
|
|
853 |
unset($temp_fields["right"]);
|
|
|
854 |
unset($temp_fields["level"]);
|
|
|
855 |
if(count($temp_fields) > 1) {
|
|
|
856 |
if(!$this->db->query("" .
|
|
|
857 |
"CREATE TEMPORARY TABLE temp_tree2 " .
|
|
|
858 |
"SELECT ".implode(", ", $temp_fields)." FROM ".$this->options['structure_table']." "
|
|
|
859 |
)) { return false; }
|
|
|
860 |
}
|
|
|
861 |
if(!$this->db->query("TRUNCATE TABLE ".$this->options['structure_table']."")) {
|
|
|
862 |
return false;
|
|
|
863 |
}
|
|
|
864 |
if(!$this->db->query("" .
|
|
|
865 |
"INSERT INTO ".$this->options['structure_table']." (" .
|
|
|
866 |
"".$this->options['structure']["id"].", " .
|
|
|
867 |
"".$this->options['structure']["parent_id"].", " .
|
|
|
868 |
"".$this->options['structure']["position"].", " .
|
|
|
869 |
"".$this->options['structure']["left"].", " .
|
|
|
870 |
"".$this->options['structure']["right"].", " .
|
|
|
871 |
"".$this->options['structure']["level"]." " .
|
|
|
872 |
") " .
|
|
|
873 |
"SELECT " .
|
|
|
874 |
"".$this->options['structure']["id"].", " .
|
|
|
875 |
"".$this->options['structure']["parent_id"].", " .
|
|
|
876 |
"".$this->options['structure']["position"].", " .
|
|
|
877 |
"".$this->options['structure']["left"].", " .
|
|
|
878 |
"".$this->options['structure']["right"].", " .
|
|
|
879 |
"".$this->options['structure']["level"]." " .
|
|
|
880 |
"FROM temp_stack " .
|
|
|
881 |
"ORDER BY ".$this->options['structure']["id"].""
|
|
|
882 |
)) {
|
|
|
883 |
return false;
|
|
|
884 |
}
|
|
|
885 |
if(count($temp_fields) > 1) {
|
|
|
886 |
$sql = "" .
|
|
|
887 |
"UPDATE ".$this->options['structure_table']." v, temp_tree2 SET v.".$this->options['structure']["id"]." = v.".$this->options['structure']["id"]." ";
|
|
|
888 |
foreach($temp_fields as $k => $v) {
|
|
|
889 |
if($k == "id") continue;
|
|
|
890 |
$sql .= ", v.".$v." = temp_tree2.".$v." ";
|
|
|
891 |
}
|
|
|
892 |
$sql .= " WHERE v.".$this->options['structure']["id"]." = temp_tree2.".$this->options['structure']["id"]." ";
|
|
|
893 |
if(!$this->db->query($sql)) {
|
|
|
894 |
return false;
|
|
|
895 |
}
|
|
|
896 |
}
|
|
|
897 |
// fix positions
|
|
|
898 |
$nodes = $this->db->get("SELECT ".$this->options['structure']['id'].", ".$this->options['structure']['parent_id']." FROM ".$this->options['structure_table']." ORDER BY ".$this->options['structure']['parent_id'].", ".$this->options['structure']['position']);
|
|
|
899 |
$last_parent = false;
|
|
|
900 |
$last_position = false;
|
|
|
901 |
foreach($nodes as $node) {
|
|
|
902 |
if((int)$node[$this->options['structure']['parent_id']] !== $last_parent) {
|
|
|
903 |
$last_position = 0;
|
|
|
904 |
$last_parent = (int)$node[$this->options['structure']['parent_id']];
|
|
|
905 |
}
|
|
|
906 |
$this->db->query("UPDATE ".$this->options['structure_table']." SET ".$this->options['structure']['position']." = ".$last_position." WHERE ".$this->options['structure']['id']." = ".(int)$node[$this->options['structure']['id']]);
|
|
|
907 |
$last_position++;
|
|
|
908 |
}
|
|
|
909 |
if($this->options['data_table'] != $this->options['structure_table']) {
|
|
|
910 |
// fix missing data records
|
|
|
911 |
$this->db->query("
|
|
|
912 |
INSERT INTO
|
|
|
913 |
".$this->options['data_table']." (".implode(',',$this->options['data']).")
|
|
|
914 |
SELECT ".$this->options['structure']['id']." ".str_repeat(", ".$this->options['structure']['id'], count($this->options['data']) - 1)."
|
|
|
915 |
FROM ".$this->options['structure_table']." s
|
|
|
916 |
WHERE (SELECT COUNT(".$this->options['data2structure'].") FROM ".$this->options['data_table']." WHERE ".$this->options['data2structure']." = s.".$this->options['structure']['id'].") = 0 "
|
|
|
917 |
);
|
|
|
918 |
// remove dangling data records
|
|
|
919 |
$this->db->query("
|
|
|
920 |
DELETE FROM
|
|
|
921 |
".$this->options['data_table']."
|
|
|
922 |
WHERE
|
|
|
923 |
(SELECT COUNT(".$this->options['structure']['id'].") FROM ".$this->options['structure_table']." WHERE ".$this->options['structure']['id']." = ".$this->options['data_table'].".".$this->options['data2structure'].") = 0
|
|
|
924 |
");
|
|
|
925 |
}
|
|
|
926 |
return true;
|
|
|
927 |
}
|
|
|
928 |
|
|
|
929 |
public function res($data = array()) {
|
|
|
930 |
if(!$this->db->query("TRUNCATE TABLE ".$this->options['structure_table'])) { return false; }
|
|
|
931 |
if(!$this->db->query("TRUNCATE TABLE ".$this->options['data_table'])) { return false; }
|
|
|
932 |
$sql = "INSERT INTO ".$this->options['structure_table']." (".implode(",", $this->options['structure']).") VALUES (?".str_repeat(',?', count($this->options['structure']) - 1).")";
|
|
|
933 |
$par = array();
|
|
|
934 |
foreach($this->options['structure'] as $k => $v) {
|
|
|
935 |
switch($k) {
|
|
|
936 |
case 'id':
|
|
|
937 |
$par[] = null;
|
|
|
938 |
break;
|
|
|
939 |
case 'left':
|
|
|
940 |
$par[] = 1;
|
|
|
941 |
break;
|
|
|
942 |
case 'right':
|
|
|
943 |
$par[] = 2;
|
|
|
944 |
break;
|
|
|
945 |
case 'level':
|
|
|
946 |
$par[] = 0;
|
|
|
947 |
break;
|
|
|
948 |
case 'parent_id':
|
|
|
949 |
$par[] = 0;
|
|
|
950 |
break;
|
|
|
951 |
case 'position':
|
|
|
952 |
$par[] = 0;
|
|
|
953 |
break;
|
|
|
954 |
default:
|
|
|
955 |
$par[] = null;
|
|
|
956 |
}
|
|
|
957 |
}
|
|
|
958 |
if(!$this->db->query($sql, $par)) { return false; }
|
|
|
959 |
$id = $this->db->insert_id();
|
|
|
960 |
foreach($this->options['structure'] as $k => $v) {
|
|
|
961 |
if(!isset($data[$k])) { $data[$k] = null; }
|
|
|
962 |
}
|
|
|
963 |
return $this->rn($id, $data);
|
|
|
964 |
}
|
|
|
965 |
|
|
|
966 |
public function dump() {
|
|
|
967 |
$nodes = $this->db->get("
|
|
|
968 |
SELECT
|
|
|
969 |
s.".implode(", s.", $this->options['structure']).",
|
|
|
970 |
d.".implode(", d.", $this->options['data'])."
|
|
|
971 |
FROM
|
|
|
972 |
".$this->options['structure_table']." s,
|
|
|
973 |
".$this->options['data_table']." d
|
|
|
974 |
WHERE
|
|
|
975 |
s.".$this->options['structure']['id']." = d.".$this->options['data2structure']."
|
|
|
976 |
ORDER BY ".$this->options['structure']["left"]
|
|
|
977 |
);
|
|
|
978 |
echo "\n\n";
|
|
|
979 |
foreach($nodes as $node) {
|
|
|
980 |
echo str_repeat(" ",(int)$node[$this->options['structure']["level"]] * 2);
|
|
|
981 |
echo $node[$this->options['structure']["id"]]." ".$node["nm"]." (".$node[$this->options['structure']["left"]].",".$node[$this->options['structure']["right"]].",".$node[$this->options['structure']["level"]].",".$node[$this->options['structure']["parent_id"]].",".$node[$this->options['structure']["position"]].")" . "\n";
|
|
|
982 |
}
|
|
|
983 |
echo str_repeat("-",40);
|
|
|
984 |
echo "\n\n";
|
|
|
985 |
}
|
|
|
986 |
}
|