GDBUtil Configuration

The configuration of GDBUtil is probably the most complicated thing with this application.

All configuration files are located in the config folder in the installation directory. 

There is one file to configure the database connections called gdbutil.rdbms. The database connections define the visible root nodes of the tree view.  There are one or more files to configure the branches of the root nodes. Those files end with .gdbutil.

The configuration is devided into the following parts:

• Database connection configuration
  • Database connection: connectionproperties
• Tree configuration
  • Tree configuration: node
  • Tree configuration: tree
  • Tree configuration: content
  • Tree configuration: chart
  • Tree configuration: query
  • Tree configuration: parameter

Database connection configuration

The file gdbutil.rdbms defines the database connections. This configuration file is validated against the file schema/rdbms.xsd and must not contain any errors! Errors are reported in the log file gdbutil.log in the application root directory. The sample configuration file gdbutil.rdbms is explained in detail.

All configuration elements for the database connections are contained in a gdbutil:rdbms element:
<gdbutil:rdbms
   xmlns:gdbutil="http://www.softomatix.de/gdbutil/rdbms"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://www.softomatix.de/gdbutil/rdbms rdbms.xsd"  
   gdbutil:escapechars="\\\"
   gdbutil:escapedchars="$. ">

 <gdbutil:connectionproperties ...>
     ...  
 </gdbutil:connectionproperties>

 <gdbutil:connectionproperties ...>
     ...  
 </gdbutil:connectionproperties>
</gdbutil:rdbms>

• gdbutil:escapechars: characters used as the escaping character(s) (normally a backslash)
• gdbutil:escapedchars: a sequence of characters which are escaped with the gdbutil:escapechars

Database connection: connectionproperties

A gdbutil:connectionproperties element is used to configure one database connection. Multiple gdbutil:connectionproperties may occure in one gdbutil.rdbms file.

A gdbutil:connectionproperties element is described as follows:
<gdbutil:connectionproperties
       gdbutil:driver="oracle.jdbc.OracleDriver"
       gdbutil:url="jdbc:oracle:thin:@//<host>:<port>/<service>"
       gdbutil:askpassword="true" >

   <gdbutil:key>oracle</gdbutil:key>
   <gdbutil:property gdbutil:name="user" gdbutil:value="<user name>"/>
   <!-- gdbutil:askpassword="false"
       <gdbutil:property gdbutil:name="password" gdbutil:value="<password of user>"/>
        -->
</gdbutil:connectionproperties>

• gdbutil:driver: the full class name of the database specific JDBC driver.
• gdbutil:url: database specific JDBC url
• gdbutil:askpassword: if true a password dialog opens and asks for the property name of the password property and the password itself.
                                           If false the password must be provided using another gdbutil:property with gdbutil:name="password".
• gdbutil:key: a key that must match the name of the connection specific configuration file (excluding '.gdbutil')
• gdbutil:property: additional, optional properties consisting of a property name and a property value. Multiple entries are allowed.

Tree configuration

The connection specific configuration file used in this sample must match the name oracle.gdbutil because of the gdbutil:key value. This file contains SQL statements to create the branches and the context menus for a particular root node. Every distinct branch configuration is placed in a separate file. One file may be used for different database connections. The statements are database specific and passed to the JDBC driver with only two modifications:

• The text between two '~' is treated as a path to a substitution value (explained later).
• Every character included in gdbutil:escapedchars is escaped with the gdbutil:escapechars

The oracle.gdbutil file is used as the sample for this explanation. The plsqllog.gdbutil file contains more advanced samples, e.g. chart-, procedure call- and user input parameter-configurations

The gdbutil:treeRoot element is the root element of every .gdbutil configuration file:

Each gdbutil:treeRoot element may contain one or more gdbutil:node elements. 

Tree configuration: node

A gdbutil:node element will be expanded to two tree levels. The higher level contains the name value of the gdbutil:node. The lower level will show the values of the gdbutil:tree SQL statement.

The gdbutil:node is described as follows:
<gdbutil:node gdbutil:name="Schemata">
  ...
</gdbutil:node>

It has the following attributes:

• gdbutil:name: A name which will be used as the name of the tree node

Each gdbutil:node element has the following nested elements:

• gdbutil:tree: zero or one occurrence is allowed
• gdbutil:node: zero, one or more occurrences are allowed
• gdbutil:content: zero, one or more occurrences are allowed
• gdbutil:chart: zero, one or more occurrences are allowed
• gdbutil:noResult: zero, one or more occurrences are allowed

The gdbutil:tree element provides the SQL statement to expand the tree. The gdbutil:content element is used to create an entry of the context menu and - when executed - creates a table view with the result set of the contained gdbutil:query element. The gdbutil:chart element is used to create a chart view. The gdbutil:noResult is used to call a SQL command without returning a result set. Procedures can be called using this element.

The nested elements are optional, but the order must be preserved.

Tree configuration: tree

A gdbutil:tree element is used to provide the SQL statement to create the lower level of a gdbutil:node element. It has to contain a gdbutil:query element which holds the actual query.

A gdbutil:tree element is described as follows:
<gdbutil:tree gdbutil:name="segmentTypes" gdbutil:operation="select">
    ...
</gdbutil:tree>

The gdbutil:tree element has the following attributes:

• gdbutil:name: A name used internally - has to be unique in the scope of the gdbutil:node element.
• gdbutil:operation: The type of SQL statement. Currently only select and procedure are implemented.

Tree configuration: content

A gdbutil:content command creates a context menu entry. If executed a table view containing the result of the configured SQL statement is opened.

The gdbutil:content is described as follows:
<gdbutil:content
   gdbutil:name="On level Columns"
   gdbutil:operation="select"
>
    ...
</gdbutil:content>

The gdbutil:content has the following attributes:

• gdbutil:name: the name which will be displayed in the context menu
• gdbutil:operation: the type of SQL statement. Currently only 'SELECT' is implemented

The gdbutil:content has the following nested elements:
<gdbutil:query>test</gdbutil:query>
<gdbutil:parameter
   gdbutil:name="test parameter name"
   gdbutil:type="[string|long|int|bigdezimal|date|timestamp]"
   gdbutil:value="parent.<selected field name, e.g. label>"
/>
<!-- including typo -->

Tree configuration: chart

A gdbutil:chart command creates a chart view showing a diagram using the results of the configured SQL statement. Every execution of the SQL statement will create a new set of values to be displayed. As the default a chart view refreshes the data and therefore periodically selects new data.

The gdbutil:chart is described as follows:
<gdbutil:chart
   gdbutil:name="On level Columns"
   gdbutil:operation="select"
>
  ...
</gdbutil:chart>

The gdbutil:chart has the following attributes:

• gdbutil:name: the name which will be displayed in the context menu
• gdbutil:operation: the type of SQL statement. Currently only 'SELECT' is implemented

The gdbutil:chart has the following nested elements:
<gdbutil:query>select count(*) as "Objects", to_number(to_char(sysdate, 'ss')) as "Seconds", systimestamp as "Time" from user_objects</gdbutil:query>
<gdbutil:parameter
   gdbutil:name="test parameter name"
   gdbutil:type="[string|long|int|bigdezimal|date|timestamp]"
   gdbutil:value="parent.<selected field name, e.g. label>"
/>
<!-- including typo -->

The following parameters need to be specified:

• ChartTitel: The title of the chart. gdbutil:type="string".
• XLabel: The label of the X column. gdbutil:type="string".
• YLabel: The label of the Y column. gdbutil:type="string".
• DeltaValues: Controls whether each query result is related to the previous query result or the query result has absolute (independent) values. gdbutil:type="string".
• XColumn: The column name of the query containing the X column value, e.g. time. The case of the XColumn value and column name in the result set coming from db! must match.  gdbutil:type="string".
• RowNameColumn: The name of the column containing the label for that row. Only used when  ValueOrientationColumn==false. The case of the RowNameColumn value and column name in the result set coming from db! must match. gdbutil:type="string".
• MaxValues: The maximum number of query results kept in memory and are visible in the chart. gdbutil:type="string".
• TitleFontSize: The size of the title font. gdbutil:type="string".
• AxisFontSize: The size of the axis font. gdbutil:type="string".
• ValueOrientationColumn: Specifies whether the query result contains the chart values as column values of the one and only result row or whether multiple rows are returned each containing a value of the chart. gdbutil:type="string". Please see plsqllog.gdbutil file for samples to explain this parameter.
• ShowValues: Boolean to indicate whether the values are printed in the chart or not. gdbutil:type="string".
• ShowValueMarkers: Boolean to indicate whether the markers are shown in the chart. gdbutil:type="string".

Tree configuration: query

The gdbutil:query element contains the SQL query and optinal parameters. The special result set column name LABEL is used for a gdbutil:tree query to mark the selected column as the tree node label. Therefor every gdbutil:tree query needs one column named LABEL in the select statement. This is done using a column name alias. The value of LABEL must  result to uppercase letters!

The gdbutil:query is described as follows:
<gdbutil:query>select distinct segment_type as label from dba_segments</gdbutil:query>

The gdbutil:query SQL query may contain placeholders which are substituted with selected column values from gdbutil:query queries in upper tree levels. For example assume the gdbutil:node node of a database connection tree node has the name 'Schemata' and the gdbutil:tree statements selects all schemata.
Another nested gdbutil:node has the name 'Tables' and the gdbutil:tree should only select the table names of the schema where this tree node belongs to.
myDB |--- Schemata |--- Schema 1 |--- Tables |--- Table 1 (belonging to 'Schema 1') |--- Table 2 (belonging to 'Schema 1') |--- Table 3 (belonging to 'Schema 1') |--- Schema 2 |--- Tables |--- Table 1 (belonging to 'Schema 2')

In this case the select statement for the tables of 'Schema 1' (Table 1, Table 2, etc.) has to include the name of the schema ('Schema 1'). To accomplish that a placeholder can be used. A placeholder has to be enclosed in two '~' characters. It has to contain the number of levels to walk up the hierarchy and the name of a selected column. The special key word 'LABEL' can also be used which is usefull in most cases. 

To create the select statement for the gdbutil:tree element nested in the gdbutil:node element the following placeholder must be used:

• parent.parent.label
  The number of parent's lead to the tree node which label is used for substitution. The first parent result in the level 'Tables'. The second parent results in the level 'Schema 1'. The label names the selected column name to be used. The column names must be supplied in upper case.

A gdbutil:parameter describes parameters for bind variables or command properties and has the following attributes:

• gdbutil:name: A name used internally - must be unique.
• gdbutil:type: The data type of the parameter. The following data types are valid: string|long|int|bigdezimal|date|timestamp. The typo will be corrected some time.
• gdbutil:value: The variable to be substituted with a static value, with values coming from queries or - if not found - from user input.

Tree configuration: parameter

A gdbutil:parameter element is used to provide parameters for SQL statements (bind variables) or properties of a tree, content, chart or noResult element.

A gdbutil:parameter element is decribed as follows:
<gdbutil:parameter
   gdbutil:name="test parameter name"
   gdbutil:type="[string|long|int|bigdezimal|date|timestamp]"
   gdbutil:value="parent.<selected field name, e.g. label>"
/>
   <!-- including typo -->

A gdbutil:parameter element has the following attributes:

• gdbutil:name: the name of the parameter.
• gdbutil:type: the type of the parameter. The type is used for bind variables. All other gdbutil:parameter types need to be a string.
• gdbutil:value: the value of the parameter. The value may be static or dynamic. A dynamic parameter is used to specify a result column of a gdbutil:tree query element.

Each command (tree, content, chart, noResult) may have

• One and only one gdbutil:query
• Zero, one or more gdbutil:parameter

The structure of every configuration file is validated on application startup or configuration reload and must not contain any errors!

Note: To view the resulting SQL statements please look into the log file: gdbutil.log.