I have a MPTT (modified preorder tree traversal) like this:
(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:
- Food/Meat branch will always exist
- Food/Meat subtree must be preserved because users can now edit its contents
- 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)