Partial replace on a modified preorder tree traversal from script

291 Views Asked by At

I have a MPTT (modified preorder tree traversal) like this:

enter image description here

enter image description here

(taken from SitePoint)

My real tree has thousands of nodes and a more complex structure, but this is a simplification that shows the problem.

Until this moment, every time I need to change this tree I used a shell script that looks like this:

#!/bin/bash
mysql -umyuser -pmypass database < tree.sql

and executes this (tree.sql):

DROP TABLE IF EXISTS `tree`;
CREATE TABLE `tree` (
  `parent` varchar(32) DEFAULT NULL,
  `title` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

LOCK TABLES `tree` WRITE;
INSERT INTO `tree` VALUES (....),(....)....

(NOTE: I use a shell script to do this because there are multiple instalations of the same PHP application in multiple servers and this is an easy/fast way to update all them, but if is required I can move the update to PHP).

But now, the requirements state that:

  1. Food/Meat branch will always exist
  2. Food/Meat subtree must be preserved because users can now edit its contents
  3. Other parts of the tree must be updated (Food/Fruit in this case but the app is agnostic to the structure).

So, given this I cannot execute a DROP TABLE, I need to retrieve a branch (Food/Meat) and insert it the tree after the DROP TABLE, CREATE TABLE and INSERT TABLE are executed.

The question is: how to retrieve a branch from shell? and how to insert it again? (Note that if it's not possible to do it from shell script I can create two small PHP scripts, but still I have no idea of how to get the branch and insert it again, any idea welcome)

0

There are 0 best solutions below