class.tree.php 37 KB

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