
SAMPLE TREE DATA STRUCTURE FOR THE TUTORIAL:

             category 1
           /          \
          /            \
   category 1.2     category 1.2
        |
        |
   category 1.2.1


this is the final structure that we want to have. 




CREATE SAMPLE DATA TABLE
In this table we have only the plain data
without hirerachical information

#CREATE TABLE category (id integer primary key, cat_name varchar);
CREATE TABLE

 #insert into category values (1, 'category 1');
INSERT 0 1
 #insert into category values (2, 'category 1.1');
INSERT 0 1
 #insert into category values (3, 'category 1.2');
INSERT 0 1
 #insert into category values (4, 'category 1.2.1');
INSERT 0 1





CREATE THE TREE STRUCTURE
-------------------------



FIRST WAY:
---------

first create nodes then set the hierarchy

the output of previus commands affects the input of future commands.

for the data structures that the functions returns look at functions.txt


create tree
SELECT tree.create_tree('t1', 'categories hierarchy 1');
 create_tree 
-------------
           1
(1 row)

add nodes without structure

#SELECT tree.add_orphan_node('t1',1);
 add_orphan_node 
-----------------
 (1,1,0,1,,)
(1 row)

#SELECT tree.add_orphan_node('t1',2);
 add_orphan_node 
-----------------
 (2,2,0,2,,)
(1 row)

#SELECT tree.add_orphan_node('t1',3);
 add_orphan_node 
-----------------
 (3,3,0,3,,)
(1 row)

#SELECT tree.add_orphan_node('t1',4);
 add_orphan_node 
-----------------
 (4,4,0,4,,)
(1 row)


add hierarchy information
#SELECT tree.set_root_node(1);
 set_root_node 
---------------
 t
(1 row)

#SELECT tree.set_parentship(1,2);
 set_parentship 
----------------
 t
(1 row)

#SELECT tree.set_parentship(1,3);
 set_parentship 
----------------
 t
(1 row)

#SELECT tree.set_parentship(2,4);
 set_parentship 
----------------
 t
(1 row)


 RESULT:
           (1->1)
           /     \
        (2->2)  (3->3)
          |
        (4->4)
 
 (a,b) : (node_id, category.id)
        










SECOND WAY:
-----------
create nodes with hierarchy information at once

the output of previus commands affects the input of future commands.

for the data structures that the functions returns look at functions.txt

#SELECT tree.create_tree('t2', 'categories hierarchy 2');
 create_tree 
-------------
           2
(1 row)


#SELECT tree.add_root_node( t2',1);'
 add_root_node 
---------------
 (5,1,0,5,,)
(1 row)

#SELECT tree.add_node(5,2);
   add_node   
--------------
 (6,2,0,6,1,)
(1 row)

#SELECT tree.add_node(5,3);
   add_node   
--------------
 (7,3,0,7,1,)
(1 row)

#SELECT tree.add_node(6,4);
   add_node   
--------------
 (8,4,0,8,2,)
(1 row)


RESULT:
           (5->1)
           /     \
        (6->2)  (7->3)
          |
        (8->4)

(a,b) : (node_id, category.id)









THIRD WAY: 
-----------
first create nodes with names (the nodes id are created also at back-end)
then set the hierarchy information
(this way has overhead of the name retrival)

the output of previus commands DOES NOT affect the input of future commands.

for the data structures that the functions returns look at functions.txt

#SELECT tree.create_tree('t3', 'categories hierarchy 2');
 create_tree 
-------------
           3
(1 row)


#SELECT tree.add_named_orphan_node('t3','n1',1);
 add_named_orphan_node 
-----------------------
 (9,1,0,9,,)
(1 row)

#SELECT tree.add_named_orphan_node('t3','n2',2);
 add_named_orphan_node 
-----------------------
 (10,2,0,10,,)
(1 row)

#SELECT tree.add_named_orphan_node('t3','n3',3);
 add_named_orphan_node 
-----------------------
 (11,3,0,11,,)
(1 row)

#SELECT tree.add_named_orphan_node('t3','n4',4);
 add_named_orphan_node 
-----------------------
 (12,4,0,12,,)
(1 row)


#SELECT tree.set_root_node('n1');
 set_root_node 
---------------
 t
(1 row)

#SELECT tree.set_parentship('n1','n2');
 set_parentship 
----------------
 t
(1 row)

#SELECT tree.set_parentship('n1','n3');
 set_parentship 
----------------
 t
(1 row)

#SELECT tree.set_parentship('n2','n4');
 set_parentship 
----------------
 t
(1 row)


 RESULT:
           (n1->1)
           /     \
        (n2->2)  (n3->3)
          |
        (n4->4)

 (a,b) : (node_name, category.id)





the three tree creation methods can be mixed up






NODE RETRIVAL
-------------
for the data structures that the functions returns look at functions.txt

get tree nodes

SELECT * FROM tree.get_nodes('t1');
SELECT * FROM tree.get_leaf_nodes('t1');
SELECT * FROM tree.get_internal_nodes('t1');
SELECT * FROM tree.get_internal_nodes('t1');

get childs of a node
SELECT * from tree.get_childs(1);

get parend node
SELECT tree.get_parent(2);
get path from node to root
SELECT * FROM tree.get_path(4,true);

combine the real data with the tree  structure.
SELECT * FROM public.category where id in   (SELECT node_data_id FROM tree.get_leaf_nodes('t1'));
SELECT * FROM public.category WHERE id = (tree.get_parent(2)).id;

TREE TRAVERSAL
--------------

for the data structures that the functions returns look at functions.txt
simple traversal
SELECT * from tree.traverse_tree('t1') ;

traversal with level
SELECT * from tree.traverse_tree_level('t1');

traversal with groups
SELECT * from tree.traverse_group(1,true); ;

#SELECT
 CASE
  WHEN group_tag = 1 THEN '<ul>'
  WHEN group_tag = 2 THEN '</ul>'
  WHEN group_tag is null THEN  '<li>'  || node_data_id  || '</li>'
 END AS tags
 from tree.traverse_group(1,8,true) t ;
    tags    
------------
 <ul>
 <li>1</li>
 <ul>
 <li>2</li>
 <ul>
 <li>4</li>
 </ul>
 <li>3</li>
 </ul>
 </ul>
(10 rows)



#SELECT
 CASE
  WHEN group_tag = 1 THEN '<ul>'
  WHEN group_tag = 2 THEN '</ul>'
  WHEN group_tag is null THEN  '<li>'  || d.cat_name  || '</li>'
 END AS tags
 from tree.traverse_group(1,8,true) t LEFT JOIN public.category d ON (t.node_data_id =  d.id) order by traversal_index ;

          tags           
-------------------------
 <ul>
 <li>category 1</li>
 <ul>
 <li>category 1.1</li>
 <ul>
 <li>category 1.2.1</li>
 </ul>
 <li>category 1.2</li>
 </ul>
 </ul>
(10 rows)


HTML RENDERING:

       *  category 1
             o category 2
                + category 4
             o category 3


SELECT 
 CASE
  WHEN group_tag = 1 THEN '<ul>' 
  WHEN group_tag = 2 THEN '</ul>' 
 END AS grou_tags, d.cat_name  
 from tree.traverse_group(1,8,true) t LEFT JOIN public.category d ON (t.node_data_id =  d.id) order by traversal_index ;

 grou_tags |    cat_name    
-----------+----------------
 <ul>      | 
           | category 1
 <ul>      | 
           | category 1.1
 <ul>      | 
           | category 1.2.1
 </ul>     | 
           | category 1.2
 </ul>     | 
 </ul>     | 
(10 rows)








METADATA 
--------
for the data structures that the functions returns look at functions.txt

SELECT tree.set_node_name(3,'my_interesting_node');
SELECT tree.get_node('my_interesting_node');
SELECT tree.get_node_id('my_interesting_node');



VIEWS SAMPLE 
------------


CREATE VIEW public.category_view AS
SELECT td.*, 
 n.id as treenode_id,
 n.tree_id as treenode_tree_id,
 n.weight as treenode_weight,
 n.internal_weight as treenode_internal_weight,
 n.level as treenode_level
from public.category td
JOIN tree.get_nodes('t1') n ON (n.node_data_id = td.id);
