
-- get depth

CALL sp_tree_depth('world_tree', 1, @a);
SELECT @a AS 'sublevels below Earth';

-- get all subnodes

CALL sp_tree_get_subnodes('world_tree', 210, 'res_tbl');
SELECT COUNT(*) AS 'subnodes of Germany' FROM res_tbl;
DROP TEMPORARY TABLE res_tbl;

-- get the parent

CALL sp_tree_get_parent('world_tree', 210, @id);
SELECT @id as 'parent_id of Germany';

-- delete all subnodes

BEGIN;
CALL sp_tree_depth('world_tree', 210, @a);
SELECT @a AS 'tree depth of subtree Germany before delete';
CALL sp_tree_delete_subnodes('world_tree', 210);
CALL sp_tree_depth('world_tree', 210, @a);
SELECT @a AS 'tree depth of subtree Germany after delete';
CALL sp_tree_get_subnodes('world_tree', 210, 'res_tbl');
SELECT COUNT(*) AS 'nodes of subtree Germany' FROM res_tbl;
DROP TEMPORARY TABLE res_tbl;
ROLLBACK;
CALL sp_tree_depth('world_tree', 210, @a);
SELECT @a AS 'tree depth of subtree Germany rolled back';

-- delete node

BEGIN;
CALL sp_tree_depth('world_tree', 210, @a);
SELECT @a AS 'tree depth of subtree Germany before delete';

CALL sp_tree_delete_subnodes('world_tree', 210);
CALL sp_tree_depth('world_tree', 210, @a);
SELECT @a AS 'tree depth of subtree Germany after delete';
CALL sp_tree_get_subnodes('world_tree', 210, 'res_tbl');
SELECT COUNT(*) AS 'nodes of subtree Germany' FROM res_tbl;
DROP TEMPORARY TABLE res_tbl;

CALL sp_tree_delete_node('world_tree', 210);
CALL sp_tree_depth('world_tree', 210, @a);
SELECT @a AS 'tree depth of subtree Germany after node-delete';

ROLLBACK;
CALL sp_tree_depth('world_tree', 210, @a);
SELECT @a AS 'tree depth of subtree Germany rolled back';


