WalkTree User Guide

Walk Tree allows easy navigation of relation database structures by showing all data navigation possibilities for a given data row.

Walk tree is configurable via an XML definition file

  • Data source definitions
  • Relationships
  • Tree style
  • TasksAutomatic XML file definition generators are available to source meta data from a variety of sources.

Java

Java 2 Runtime engine (1.2 or greater)

JAXB Runtime libraries (early adopter technology - ea-1.0)

Oracle

The Oracle JDBC libraries must appear in the CLASSPATH or be accessible from within the JVM runtime directory structure.

Due to the way in which the SQL is constructed Oracle 8 is required.

The following section describes the Walk Tree user interface and how to interact with the product.

Connection Dialog

If the product has been successfully installed then launching the JAR file will result in the following dialog.

All text fields must be completed.

The database server string corresponds to your SQL*Net v2 string as defined in your TNSNAME.ORA network configuration file.

When Connect is pressed an OCI8 connection will be established with the oracle database.

Pressing Cancel or Closing the dialog box will result in the exit of the Walk Tree program.

Open

Load an XML definition file into WalkTree.

Copy

Copy the currently selected item into the system clipboard.

Refresh

Use the refresh toolbar button when you wish to reload the XML definition file populating the current interface. Selecting this option will collapse the tree hierarchy browser.

Tree Navigator

The tree hierarchy navigator is used to traverse the data structures as defined in the XML definition file. This is the main panel in which interactions with the database occur.

The meaning of the icons displayed in the navigator are explained in the table^Icon ^Description ^

The parent icon - indicates that this data source contains data rows that are parents of the currently selected data row.
The child icon - Information that is dependent upon this data row as a parent.
A complex relation where the relationship type cannot be determined
Broken query - the SQL query failed to executed due to some sort of error. An SQL error dialog will be shown when this icon is generated. Once the dialog has been closed, it will not be displayed again unless this node is refreshed.
List of task that are available for this data row

Searching

The find button and text field can be used to navigate the tree to a data row. Only a single level of the tree will be searched for the entered text, when a data source node (i.e. Manager, Dept or Commissioned Employees) is selected its row data will be search, when a row data node is selected then all data at that level will be searched. Pressing find for a second time will NOT look further for other occurrences of the string.

The property panel shows all the available columns and their corresponding values, at the point in time, at which the query was executed, for a data source.

The property panel is divided into three main areas

  • Toolbar
  • Data source label
  • Data worksheet

Toolbar

If the software has been configure such that data changes have been disabled, only the refresh toolbar button will be enabled. The commit, rollback and delete toolbar buttons will appear greyed out and will not be accessible.

Refresh

When running in a multi-user environment it is possible that the information shown in the data worksheet may not be the latest available. The refresh button will retrieve new values from the database.

Commit

Any database change made to the property panel data will be posted to the database but not committed. To make any changes to the data permanent use the commit button.

Rollback

The rollback button allows changes that have been made to the data to be discarded and the original pre-change values restored.

Delete

The delete button will remove the currently viewed row of data from the database. The change will not become permanent until a commit action has been performed.

Data worksheet

This area displays the column names and values associated with the selected data row from the hierarchy browsers.

Column values may be updated by double clicking on the column data and entering new values.

Any table data element may associate with it a set of tasks, or PL/SQL code blocks. The task panel manages the execution and output from these tasks.

Each executed task runs within its own connection to the database, so that all commits and rollback that may occur will not affect any other executing task or indeed the main interface.

Once started a task will run in the background until it has either completed or some error occurs that will cause it to halt execution. Whilst running the status will show “running”. On completion the task will immediately be selected and the Task panel will be selected to show the completed task and its status either “completed” or “error”

Toolbar

Stop

To abort a running task before it completes, select the “running” task and use the stop toolbar button. The status will be changed to “abort” to indicate this action.

Find

To navigate the tree hierarchy browse to the data row associated with a task, first select the task and then use the find button. The tree navigator will highlight and select the associated task.

If the tree XML has been reloaded the associations between the task history list and the tree will be severed.

Execute

There are two ways to execute a task once the task has been selected in the tree browsers, either by using the right mouse button to display the context menu in the tree navigator, or by selecting the task tab an pressing the execute button.

Delete

To remove a task history item from this list select the task and use the trash can button. “Running” tasks cannot be deleted.

Task history table

All jobs are entered into the task history table. To review the output from a previously executed task selected the desired task. Tasks and their corresponding output may be deleted when no longer needed.

Output area

Task execution start, finish, and elapsed times will be recorded into this area. DBMS output will be place into this window as the job executes. In the event of an error during execution of the PL/SQL block, the code will be displayed here with line numbers and the corresponding Oracle error message.

The XML definition file drives the Walk Tree program it defines the data sources, the relationships and the tasks that may be executed.

the Basics

An XML document is a text file containing XML mark-up tags, which are pieces of text surrounded by a start-tag and an end-tag.

These XML tags look similar to HTML tags, but they are different in significant ways: XML tags are extensible and indicate the meaning of the text enclosed within them. For example, you could not define a tag <tableList> in HTML; you must use tags already defined in the HTML specification, and a set of HTML tags can only define the format of the text it enclosed. The <tableList> tag can indicate to a parser that the information contained within it is a list of tables, which allows you to do much more with your data, including searching and archiving. You can also restrict what kinds of data a tableList can contain.

XML tags are case sensitive

Defining a Data Source

A data source is any XML object that can return rows of data. Data sources may be static, in the case of a table, or dynamic, for any valid SQL select statement, and are given the following tag respectively.

<table ...
<sql ...
    

Attributes

The following sets of attributes define and control the behaviour of a data source.

name attribute

This is a mandatory attribute.

The name attribute uniquely identifies a data source XML node. The contents of this tag are case sensitive and all references to it via the linkto attribute must be of the same case.

The name attribute will appear in the Tree browsers to identify this data source if no label attribute is present.

In the case of a <table> tag the name corresponds to the name of an oracle Table, View or Materialised view in the database.

<table name="emp" ...
    

For a dynamic data source, ie the <sql> tag, it must simply be unique within the XML definition file.

<sql name="emp_select" ...
    
key attribute

This is a mandatory attribute.

The primary key, or compound primary keys, of a data source are listed in the key attribute. This is used to uniquely identify a row of data within a data source. If the primary key is not identified properly you will get SQL exceptions when you attempt to view the data for a selected row in the tree browser as multi record can match this key.

The contents of this attribute are a comma-separated list of columns.\\key = “column[,column…]”

<table name="emp" key="empno"
    
label attribute

This is an optional attribute.

The label attribute can be used to override the default name that appears in the Tree browser.\\[label = “ string ”]

<table name="emp" key="empno" label="Employee" ...
    

All references to the data source must still reference this XML node object by name.

root attribute

This is an optional Boolean attribute.

The root attribute controls whether or this data source appears as a top-level object in the tree browsers, the first set of data sources shown when the XML definition file is loaded.

You must have at least one data source with root=“true” otherwise you will have no entry points into your data.

If this attribute is not present it is assumed to be root=“false”\\[root = “ true | false ” ]

<table name="emp" key="empno" label="Employee" root="false">
    
updateable attribute

This is an optional Boolean attribute.

The updateable attribute control whether the user is allowed to modify the data of this data source.

If this attribute is not present it is assumed to be root=“true” for a <table> and root=“false” for an <sql> data source.\\[updateable = “ true | false ” ]

Static data source

A static data source is defined as any database object where a simple “select * from ” for the object will return its contents. Database object that can fall into this class are

  • Tables
  • Views
  • Materialised ViewsIn it simplest form a static XML data source definition could be defined as:
<table name="emp" key="empno"/>
    

Dynamic data source

A dynamic data is constructed from any valid SQL select statement. It must however not contain an “order by” statement. This will be defined later using a <style> tag.

A dynamic data source is defined by using the <sql> tag and defining the select statement within a <select> tag.

<sql name="emp_select" key="empno">
  <select>
    select * from emp where mgr is null
  </select>
</sql>
    

Styling

A style is applied to a data source and defines the way in which the row data is displayed in the Tree browser. When a data source does not have a style the primary key of the data source will be displayed. Multiple styles may be defined for a data source and each must appear with in the <StyleList> tag.

<styleList>
  <style ... />
  <style ... />
</styleList>
    

name attribute

This is a mandatory attribute

Multiple styles can be created for a data source, each must be named into a style group.

At the present only a single style group is allowed, named DEFAULT.

<style name="DEFAULT" ...
     

display attribute

This is a mandatory attribute

The display attribute contains any valid SQL expression to construct a string that will be displayed in the Tree Browser to represent this the row data of this data source.

<style name="DEFAULT" name="empno||' '||ename" ...
     

orderby attribute

This is an optional attribute.

The orderby attribute defines the column order for the display attribute.

<style name="DEFAULT" name="empno||' '||ename" orderby="ename">
     

Relationships

A relationship tag is used to indicate a linkage between two or more data source nodes.

Many relationships may be defined for a data source each must appear within a group of relationship list tags

<relationshipList>
  <relationship .../>
  <relationship .../>
</relationshipList>

linkto attribute

This is a mandatory attribute.

The name of the data source that this relationship references.

<relationship name="dept" ...
   

type attribute

This is a mandatory attribute

There are two different types of relationship joins simple and complex.

A column join normally appears to link child data source to its parent.

column

A simple relationship join or column join can be used when nothing more than the foreign key columns are used to join to a parent data source.

<relationship linkto="emp" type="column">deptno</relationship>

This join will use the value of the deptno column of the current data source and attempt to find a corresponding dept row.

If the relationship was to be described using the “where” type the resultant where clause would be described as follows

where dept:key = (select deptno from emp where empno = emp:key)

The column defined by the key attribute of the data source named dept must equal the value of the deptno column for the currently selected row.

where

A complex join is used when full control over the SQL is needed to link two data sources together.

The following example show how a link the dept data to the emp data only when the employee is a MANAGER.

<table name="dept" key="deptno"
  <relationship linkto="emp" type="where">
    emp.deptno = ?
    and emp.mgr = 'MANAGER'
  </relationship>
</table>

The question mark placeholder is substituted for each value stored for the primary key. If the primary key consists of many columns, a compound primary key, then multiple question marks would be used for each primary key value.

<table name="dept" key="deptno,deptgrp"
  <relationship linkto="emp" type="where">
    emp.deptno = ?
    and emp.deptgrp = ?
    and emp.mgr = 'MANAGER'
  </relationship>
</table>

label attribute

The label attribute allows the default name that appears in the Tree browser to be changed. This is useful when a data source has multiple references to the same table.

<referenceList>
  <reference linkto="emp" label="Manages"/>
  <reference linkto="emp" label="Is Managed by"/>
</referenceList>

Tasks

Tasks allow any arbitrary PL/SQL block to be executed for the context of the currently selected data row.

Every data source may optionally have a list of associated tasks. Each task will appear nested within a set of <taskList> tags.

<taskList>
  <task ...
  <task ...
</taskList>

name attribute

This is a mandatory parameter.

The name attribute identifies a PL/SQL task that can be executed for an instance of a data source row. It will appear in the Tree Browser under the Task List node.

parameter substitution

Information about the current instance of row data that we are executing this PL/SQL block for may be access via the following pseudo blocks.

Using the following nomenclature may access them

:block.field

The substitution in the PL/SQL does not honour quotes and will also parse strings substituting the corresponding block values. The following code fragment will have the primary key value for empno replaced.

dbms_output.put_line('The primary key value is :in.empno');
in pseudo block

his block contains key information for the instance of row data in which this PL/SQL block is executing. Only those columns defined within the data source key are accessible. If you require other row values it is suggest you perform a select statement on the base table using the accessible primary key.

<table name="emp" key="empno">
  <taskList>
    <task name="Task 1">
      declare
        empkey emp.empno&nbsp;type := :in.empno;
      begin
        dbms_output.put_line(empkey);
      end;
    </task>
  </taskList>
</table>
meta pseudo block

This block contains the attributes of the data source for which the row data was generated. There are two attributes

:meta.name
:meta.key

If we have a data source named emp, as seen below, and a task was defined that accessed the meta block then the values can be described as:

<table name="emp" key="empno">
:meta.name = 'emp'
:meta.key = 'empno'

The following example XML definition file may be used with the default ubiquitous scott/tiger account, to demonstrate the type of XML configurations that are possible.

<sourceList updateable="false">

<sql name="managers" label="Manager" key="empno" root="true">
  <select>
    select *
    from emp
    where job in ('MANAGER', 'PRESIDENT')
  </select>
  <referenceList>
    <reference linkto="dept" type="column">deptno</reference>
     <reference linkto="emp" label="Employees" type="where">
       mgr = ?
     </reference>
  </referenceList>
  <styleList>
    <style name="DEFAULT" display="empno||' '||ename" orderby="empno"/>
  </styleList>
</sql>

<table name="dept" key="deptno" root="true">
  <referenceList>
    <reference linkto="emp" label="Commissoned Employees" type="where">
      deptno = ? and comm is not null
    </reference>
  </referenceList>
  <styleList>
    <style name="DEFAULT" display="deptno||' '||dname" orderby="deptno"/>
  </styleList>
</table>

<table name="emp" key="empno" root="true" updateable="true">
  <referenceList>
    <reference linkto="dept" type="column">deptno</reference>

    <reference linkto="emp" label="Managed by (short)" type="column">
    mgr
    </reference>

    <reference linkto="emp" label="Managed by (long)" type="where">
     empno = (select mgr from emp where empno=?)
    </reference>

    <reference linkto="emp" label="Manages" type="where">
      mgr=?
    </reference>
</reference>
  </referenceList>
  <styleList>
    <style name="DEFAULT" display="empno||' '||ename" orderby="empno"/>
  </styleList>
  <taskList>
    <task name="Pay Rise 20%">
      declare
        empkey emp.empno%type := :in.empno;
      begin
        dbms_output.put_line('Updating the :meta.name table');
        dbms_output.put_line('For primary key :meta.key');
        dbms_output.put_line('Increase by 20% employee id '||empkey);
        update emp
        set sal = sal * 1.2
        where empno = empkey;

        commit;
      end;
    </task>
    <task name="Pay Cut 20%">
      begin
        dbms_output.put_line('Updating the :meta.name table');
        dbms_output.put_line('For primary key :meta.key');
        dbms_output.put_line('Pay cut by 20% employee id '||:in.empno);
        update emp
        set sal = sal * .8
        where empno = :in.empno;

        commit;
      end;
    </task>
  </taskList>
</table>

</sourceList>