SQL API

It could happen that you want to do some SQL queries by yourself. It must not become a nightmare. That’s why here you can find some informations about the model used by Baobab and the functions or procedures that you could call if you need them.

Please note that each tree has his own set of tables and functions, and whenever you find the term “GENERIC” you must replace it with your forest name. (tables or functions without “GENERIC” in it are shared between forests).

Note

Each table holds indeed a forest, with zero, one or many trees.

TABLE GENERIC

This is in fact the main Baobab table, holding the nodes’ informations. Unless you altered it to suit your needs, this is the structure.

TABLE GENERIC (
   tree_id INTEGER UNSIGNED NOT NULL,
   id      INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   lft     INTEGER NOT NULL CHECK (lft > 0),
   rgt     INTEGER NOT NULL CHECK (rgt > 1),
   INDEX(tree_id),
   INDEX(lft)
)

Note

You can alter it as much as you want as long there are these 4 fields in any order.

VIEW GENERIC_AdjTree

This view holds the id of each parent of GENERIC nodes. Root has parent NULL.

VIEW GENERIC_AdjTree (tree_id,parent,child,lft)

TABLE Baobab_ForestsNames

This table store the set of names in use from Baobab. If you construct a new “GENERIC” table, you must insert his name here, and remove it if that table is dropped.

TABLE Baobab_ForestsNames (
    name VARCHAR(200) PRIMARY KEY
)

TABLE Baobab_Errors

This table contains general errors that could occur in some situations. “code” is the error number and it’s associated to a unique codename “name” and to a human understandable message “msg”.

TABLE Baobab_Errors (
   code   INTEGER UNSIGNED NOT NULL PRIMARY KEY,
   name   VARCHAR(50)      NOT NULL,
   msg    TINYTEXT         NOT NULL,
   CONSTRAINT unique_codename UNIQUE (name)
)

FUNCTION Baobab_getErrCode

Return the error id associated to an error codename.

FUNCTION Baobab_getErrCode(x TINYTEXT) RETURNS INT

PROCEDURE Baobab_GENERIC_DropTree

The name of this function is unfortunate. It destroys a node and all of the nodes in his subtree. If update_numbers is 1 then close the gap created.

Baobab_GENERIC_DropTree (
         IN node INTEGER UNSIGNED,
         IN update_numbers INTEGER)

PROCEDURE Baobab_GENERIC_AppendChild

Add a child as last right sibling in a choosen tree. Returns the id of the new node created and the id of his tree. If choosen_tree is 0 a new tree will be created.

Baobab_GENERIC_AppendChild(
         IN choosen_tree INTEGER UNSIGNED,
         IN parent_id INTEGER UNSIGNED,
         OUT new_id INTEGER UNSIGNED,
         OUT cur_tree_id INTEGER UNSIGNED)

PROCEDURE Baobab_GENERIC_insertAfter

Insert a new node at the right side of a given id.

Baobab_GENERIC_insertAfter(
         IN sibling_id INTEGER UNSIGNED,
         OUT new_id INTEGER UNSIGNED,
         OUT error_code INTEGER UNSIGNED)

Note

You cannot insert a node before or after root node

PROCEDURE Baobab_GENERIC_insertBefore

Insert a new node at the left side of a given id.

Baobab_GENERIC_insertBefore(
         IN sibling_id INTEGER UNSIGNED,
         OUT new_id INTEGER UNSIGNED,
         OUT error_code INTEGER UNSIGNED)

Note

You cannot insert a node before or after root node

PROCEDURE Baobab_GENERIC_InsertChildAtIndex

Insert a new node as nth child of an existing node. You can use indexes starting from 0 or -1 (will start counting from the right side).

Baobab_GENERIC_InsertChildAtIndex(
         IN parent_id INTEGER UNSIGNED,
         IN idx INTEGER,
         OUT new_id INTEGER UNSIGNED,
         OUT error_code INTEGER UNSIGNED)

Note

The new parent must have at least an existing child

PROCEDURE Baobab_GENERIC_getNthChild

Retrieve the id of the nth child of a given parent. Negative indexes are allowed.

Baobab_GENERIC_getNthChild(
         IN parent_id INTEGER UNSIGNED,
         IN idx INTEGER,
         OUT nth_child INTEGER UNSIGNED,
         OUT error_code INTEGER UNSIGNED)

PROCEDURE Baobab_GENERIC_MoveSubtreeBefore

Move a node and his subtree to the left of another node.

Baobab_GENERIC_MoveSubtreeBefore(
     IN node_id_to_move INTEGER UNSIGNED,
     IN reference_node INTEGER UNSIGNED,
     OUT error_code INTEGER UNSIGNED)

PROCEDURE Baobab_GENERIC_MoveSubtreeAfter

Move a node and his subtree to the right of another node.

Baobab_GENERIC_MoveSubtreeAfter(
         IN node_id_to_move INTEGER UNSIGNED,
         IN reference_node INTEGER UNSIGNED,
         OUT error_code INTEGER UNSIGNED)

PROCEDURE Baobab_GENERIC_MoveSubtreeAtIndex

Move a node and his subtree as the nth child of a given node. Negative indexes are allowed.

Baobab_GENERIC_MoveSubtreeAtIndex(
         IN node_id_to_move INTEGER UNSIGNED,
         IN parent_id INTEGER UNSIGNED,
         IN idx INTEGER,
         OUT error_code INTEGER)

PROCEDURE Baobab_GENERIC_Close_Gaps

Close gaps caused by removing a node or a subtree.

Baobab_GENERIC_Close_Gaps(
         IN choosen_tree INTEGER UNSIGNED)