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 i wish to have. 
the contents of the tree nodes are in reality records of a separate table.
By the help of the library i will connect these records to a tree 
structure that i will create and control by the library


there is more than one way to create tree hierarchies 
for this tutorial i will chose one of them 
in brief: first of all i will create nodes 
then i will set the hierarchical relationships
between the nodes

CREATE SAMPLE DATA TABLE
In this table we have only the plain data
without hierarchical information
the nodes data for the tutorial are simply varchar fields, in a real situation
it is possible to be any valid record set

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

#insert into category values (101, 'category 1');
INSERT 0 1
#insert into category values (102, 'category 1.1');
INSERT 0 1
#insert into category values (103, 'category 1.2');
INSERT 0 1
#insert into category values (104, 'category 1.2.1');
INSERT 0 1





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

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


add nodes without structure

for the manipulation of nodes we need a node identificator.
By terms of library we have two ids
a global node id that is unique for all trees registered in library
and an id that is unique inside the tree, named as node_sn
in feature versions of the library the use of global id will be reduced 
i would propose the use of node_sn when that is possible
the global ids are automatically assigned by the library
there is no way to have control over this ids
for the node_sn we have options similar to the postgres table serial ids 
that are connected to sequences
it is possible to take a free node_sn with the use of:
tree.get_node_next_sn(<TREE_NAME>)
alternately you can use your own numbering schema and continue by setting 
the next free node_sn with:
SELECT tree.set_node_next_sn(<TREE_NAME>,<VALUE>)
if you wand to see the status of node_sn sequence, you will make the use of:
SELECT * from tree.get_node_sn_sequence(<TREE_NAME>)

for this example i will use my own numbering schema with the use of: 
tree.add_orphan_sn_node(<tree_name>,<node_sn>,<data_id>) 
The arguments of tree.add_orphan_sn_node are: 
1) tree_name: tree name 
2) node_sn  : serial number of node (unique per tree)
3) data_id  : the node data id (the id of row at category table)


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

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

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

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


#SELECT tree.set_node_next_sn('t1',10)
 set_node_next_sn 
------------------
               10
(1 row)




add hierarchical information with the use of: 
SELECT tree.set_root_node(<tree_name>,<node_sn>)
and
SELECT tree.set_parentship( t1'<parent_node_sn>,<child_node_sn>)'


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

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

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

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


the tree structure is registered within the library
now it is possible to access the tree by the help of 
the tree node retrival library functions 


A graphical representation of the tree structure that is finally created:

           (1->101)
           /     \
        (2->102)  (3->103)
          |
        (4->104)
 
 (a,b) : (node_id, category.id)


for information about the data structures that the functions return
look at functions.txt












NODE RETRIVAL
-------------

get the tree nodes

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

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

get the parent node
SELECT tree.get_parent(2);
get the 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
--------------

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>101</li>
 <ul>
 <li>102</li>
 <ul>
 <li>104</li>
 </ul>
 <li>103</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)





for information about the data structures that the functions return
look at functions.txt





METADATA 
--------

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.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);