You can create the tables and sample data using the scripts found under the ./scripts folder of the VirtualTreeNavigator installation folder, or here.

Tutorial 0. Hello World

The Simplest VirtualTreeNavigator script you can write, will create a static taxonomy that contains a single node. Static taxonomies do not need a connection to a database, so you can get yourself started without any database connections to worry about.

The example below, contains a taxonomy with a title of "hello world". The taxonomy defines level 0 as the result of a simple static text query which is defined right inside the "taxdef" node. The query has a single data entry with an attribute called "name" and a value of "hello world". The "name" attribute is used for both the identifier (nodeidcol) and name (nodenamecol) of the taxonomy node we are creating, and we have given the node a type called "node".

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes" ?> <taxonomy id="hello world"> <!-- =============================================================== --> <!-- == Taxonomy definition == --> <!-- =============================================================== --> <taxdef level="0"> <query nodetype="node" nodeidcol="name" nodenamecol="name"> <text columns="name"> <![CDATA[ "hello world" ]]> </text> </query> </taxdef> </taxonomy>

Alternatively, the example below, defines the same query as above as a named query which then in turn is refered to in the "taxdef" XML node. The declaration use the query reference "getRoot" at level zero (0) of the virtual taxonomy.

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes" ?> <taxonomy id="hello world v2"> <!-- =============================================================== --> <!-- == Queries == --> <!-- =============================================================== --> <query name="getRoot" nodetype="node" nodeidcol="name" nodenamecol="name"> <text columns="name"> <![CDATA[ "hello world" ]]> </text> </query> <!-- =============================================================== --> <!-- == Taxonomy definition == --> <!-- =============================================================== --> <taxdef level="0"> <query refid="getRoot" /> </taxdef> </taxonomy>

Tutorial 1. Simple Recursive Database Structure

Some data models contain recursive data structures, i.e. trees by simple means of connecting every row in a table to another row, usually referred to as "parent row". This simple data modelling technique observes the main attributes of a strict taxonomy, that a node can have only one parent.

The figure below illustates such a structure. The PRODUCT table contains a recursive relationship to itself.

Simple data model

To fully traverse the taxonomy defined by the PRODUCT table in VirtualTreeNavigator, the following taxonomy definition is required. Note, that the two queries are required, one to retrieve the first level of the taxonomy (i.e. products with no "parents") and another to retrieve the "children" of each product regardless of the product id, name or level in the hierarchy.

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes" ?> <taxonomy id="simple"> <query name="getRoots" nodetype="hierarchy_root" nodeidcol="product_id" nodenamecol="product_name"> <sql> <![CDATA[ select * from product where parent_product_id is null order by product_name ]]> </sql> </query> <query name="getChildren" nodetype="product" nodeidcol="product_id" nodenamecol="product_name"> <sql> <![CDATA[ select * from product where parent_product_id = ? order by product_name ]]> </sql> </query> <!-- Root level --> <taxdef level="0"> <query refid="getRoots" /> </taxdef> <!-- default level --> <taxdef> <query refid="getChildren"> <argument columnname="product_id" /> </query> </taxdef> </taxonomy>

Tutorial 2. Nodes with types

Often, one of the most important attributes for a taxonomy node is its "type", a categorisation or grouping that determines how this product behaves in the taxonomy or what additional data it may contain. The node type can be one of the node's attributes as illustrated in the figure below:

Products with type

The difference between the following taxonomy definition and the one above is that the nodetype attributes of the query node have been replaced with the nodetypecol attribute which retrieves the type of each node from a column in the result set of the query.

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes" ?> <taxonomy id="simple_with_type"> <query name="getRoots" nodetypecol="product_type_id" nodeidcol="product_id" nodenamecol="product_name"> <sql> <![CDATA[ select * from product where parent_product_id is null order by product_name ]]> </sql> </query> <query name="getChildren" nodetypecol="product_type_id" nodeidcol="product_id" nodenamecol="product_name"> <sql> <![CDATA[ select * from product where parent_product_id = ? order by product_name ]]> </sql> </query> <!-- Root level --> <taxdef level="0"> <query refid="getRoots" /> </taxdef> <!-- default level --> <taxdef> <query refid="getChildren"> <argument columnname="product_id" /> </query> </taxdef> </taxonomy>

Oracle Script for Sample Tables and Data

-- Oracle script for the creation of sample tables and data create table PRODUCT ( product_id number(10,0) not null, product_type_id number(10,0) not null, product_name varchar2(20) not null, parent_product_id number(10,0) null, constraint product_pk primary key (product_id) ); create table PRODUCT_TYPE ( product_type_id number(10,0) not null, product_type_name varchar2(20) not null, constraint product_type_pk primary key (product_type_id) ); create table ADDITIONAL ( product_id number(10,0) not null, name1 varchar2(20) null, name2 varchar2(20) null, name3 varchar2(20) null ) alter table PRODUCT add constraint parent_product_fk foreign key (parent_product_id) references product (product_id); alter table PRODUCT add constraint prodtype_product_fk foreign key (product_type_id) references product_type (product_type_id); insert into product_type (product_type_id, product_type_name) values (100, 'Group A Products'); insert into product_type (product_type_id, product_type_name) values (200, 'Group B Products'); insert into product_type (product_type_id, product_type_name) values (300, 'Group C Products'); insert into product_type (product_type_id, product_type_name) values (400, 'Group D Products'); insert into product_type (product_type_id, product_type_name) values (500, 'Group E Products'); insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 1, 100, 'Product A', null); insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 2, 200, 'Product B', null); insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 3, 300, 'Product C1', 1); insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 4, 300, 'Product C2', 1); insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 5, 300, 'Product C3', 1); insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 6, 400, 'Product D1', 2); insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 7, 400, 'Product D2', 2); insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 8, 500, 'Product E1', 6); insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 9, 500, 'Product E2', 6); insert into product (product_id, product_type_id, product_name, parent_product_id) values (10, 500, 'Product E3', 7); insert into ADDITIONAL (product_id, name1, name2, name3) values (1, 'value11', null, null); insert into ADDITIONAL (product_id, name1, name2, name3) values (2, 'value21', 'value212', 'value213'); insert into ADDITIONAL (product_id, name1, name2, name3) values (3, 'value31', null, 'value313'); insert into ADDITIONAL (product_id, name1, name2, name3) values (3, 'value32', 'value322', 'value323');