Database Profile Framework

 DbVis Software
DbVisualizer 8.0 http://www.dbvis.com
support@dbvis.com

Master documentation index

Introduction

This document and the Database Profile Framework in general is appropriate only when using the licensed DbVisualizer Personal edition.

This document explains the database profile framework which is the base for how DbVisualizer presents information in the Database Objects tree and in the Object View. In addition, it is also used to define object actions, such as drop, rename, compile, create, comment, alter, etc.

What features in DbVisualizer relies on the database profile?

One of the most important and central features in DbVisualizer is the database objects tree, used to navigate databases, and the object view, showing details about specific objects. The general problem exploring any database is that they are all different with respect to the information describing what's in the database (also called system tables or database meta data). This basically means that it's rather complex to implement a multi-database support product, such as DbVisualizer, since each database must be handled specifically. All databases also support different object types, apart from the most common ones, such as table, view, index, etc.

The database profile framework is used to simplify the process of defining what information DbVisualizer will display and operate on for a specific database. Technically, a database profile is an XML document with all of the logic, structure and actions easily mapped to the visual components in DbVisualizer. Another great benefit of separating the database specific logic from the implementation of DbVisualizer is that anyone with some degree of domain knowledge can create a database profile. All that is needed is a text editor (preferably with XML support) and some ideas of what should be the final result.  

A great source for inspiration (except for this document) is all the existing database profiles that comes with DbVisualizer. All database profiles are (and must be) stored in the DBVIS-HOME/resources/profiles directory (this path is OS dependent).

The following figure illustrates which features in DbVisualizer are controlled by the database profile.


Figure: What the database profile controls in DbVisualizer

The red box at the left shows the database objects tree. This tree is used to navigate the objects in the database. Selecting an object in the tree shows the object view (blue box) for the selected object type. An object view may have several data views (green), showing object information. DbVisualizer shows these as labeled tabs. The green box in the screenshot shows the content of the data view labeled Columns. The type of viewer that is presenting the data in the screenshot is the grid viewer. Read more about all data viewers in the Viewers section.

Common to both the database objects tree and the object view are the SQL commands that are used to fetch the information from the database. The associated SQL is executed by DbVisualizer whenever a node in the tree is expanded (to expose any child objects) or when a node is selected (to fill the object data views).

Right-clicking the mouse on an object in the tree or clicking the Actions button in the object view shows a menu with all valid actions for the selected object. These are also defined per database profile and object type. Read more about the capabilities of actions in the Definition of user actions section.

How does DbVisualizer know what database profile to use?

DbVisualizer automatically load the appropriate database profile (XML file) based on the following:
  1. The Database Type for the database connection is matched with the information in the DBVIS-HOME/resources/database-mappings.xml file to find out if there is a database profile available. If it finds one, it is used.
  2. If there is no matching profile, the generic profile is used.  This is very basic profile and shows only rudimentary information about the objects in the database. This is also the profile used in the DbVisualizer Free edition for all databases.
A specific database profile can be selected manually for a database connection. This is done in the database connection properties. Manually choosing a profile requires that the profile supports the actual database. If it doesn't, various errors will be reported once the database objects tree is explored. (Whenever the profile is changed, you must reconnect the database connection).

XML structure

The mapping from the visual components in the user interface described earlier and the element definitions in the XML file is, briefly, as follows:
The XML for a database profile is quite simple, but there are a few things that need to be highlighted. All database connections loads a database profile from an XML file. If there is no matching database profile, the generic profile is used. This profile uses the standard JDBC metadata calls in order to obtain information about the structure and objects in the database. The generic profile is not one XML file, as the database specific profiles are, but instead four files:
All these files a referred in the generic.xml file via include statements, i.e.,  each of the above files are included in the generic.xml file when it is loaded. The reason for this file organization is that the four files above can also be included and extended in a specialized profile. See later for more information.

The XML structure used to represent the database profile is as follows (click on the link to read more about each specific section):

XML skeleton

The following is a minimal database profile XML file, showing its structure. 
<?xml version="1.0" encoding="UTF-8" ?>
  <!DOCTYPE DatabaseProfile SYSTEM "dbvis-defs.dtd" [
  <!ENTITY generic-commands SYSTEM "generic-commands.xml">
  <!ENTITY generic-view SYSTEM "generic-view.xml">
]>

<DatabaseProfile desc="Profile for Sybase ASE"
                 version="$Revision: 12744 $"
                 date="$Date: 2010-12-18 02:51:52 +0100 (Lör, 18 Dec 2010) $"
                 minver="6.5.7">

<!-- =================================================================== -->
<!-- Definition of the commands -->
<!-- =================================================================== -->

  <Commands>
    &generic-commands;
    ...
  </Commands>

<!-- =================================================================== -->
<!-- Definition of the object actions that are used by the tree -->
<!-- =================================================================== -->

  <ObjectsActionDef>
    ...
  </ObjectsActionDef>

<!-- =================================================================== -->
<!-- Definition of the database objects tree structure -->
<!-- =================================================================== -->

  <ObjectsTreeDef id="sybase-ase">
    ...
  </ObjectsTreeDef>

<!-- =================================================================== -->
<!-- Definition of the database objects views -->
<!-- =================================================================== -->

  <!-- Include the generic-view -->
  &generic-view;

  <ObjectsViewDef id="sybase-ase" extends="generic">
    ...
  </ObjectsViewDef>

</DatabaseProfile>

The name of the XML file (sybase-ase) and the values for the id attribute for the ObjectsTreeDef and ObjectsViewDef elements must be the same.

The first rows in the XML defines external dependencies and their URIs. The DOCTYPE identifier defines the DTD that is used to validate the XML. The ENTITY identifiers lists URIs for external references. In this case they identify the generic-commands.xml and generic-view.xml files. They can then be referenced in the XML as &generic-commands; and &generic-view;, which simply means that the related XML files are included in the final document when the profile is loaded.

The root of the database profile is the DatabaseProfile element. Continue to the next sections for information about the elements forming the database profile.

Tip: If you are using an XML editor to edit the profile it is very convenient to load the DTD in the editor, as you will then get color and error highlighting.

<DatabaseProfile>

The DatabaseProfile is the root element in the XML file. It is required and have the following attributes. 
<DatabaseProfile desc="Profile for Sybase ASE"
                 version="$Revision: 12744 $"
                 date="$Date: 2010-12-18 02:51:52 +0100 (Lör, 18 Dec 2010) $"
                 minver="6.5.7">

  ...
</DatabaseProfile>
The attributes specified for the DatabaseProfile element appear in the Database Profile list when selecting the connection properties for a database connection:

Figure: The list of available database profiles

<InitCommands> - Initialization commands

The InitCommands section define commands that are executed when the database profile is first loaded. These commands are typically used to determine characteristics of the target database. The result is stored in variables that can be used in conditions that are evaluated when the rest of the profile is loaded. A common use case is to find out the authorization level of the current user as defined by the database. If the user have limited privileges then make sure the supported object types, views and available actions in DbVisualizer matches the authorization.

Multiple commands may be defined in the InitCommands element and these are executed in serie from top. Conditional processing is also supported.

The following sample is from the HP Neoview database profile. The main purpose with its InitCommands is to first determine the database version by quering a system table (this information is not properly available by the Neoview JDBC driver). Based on the database version a condition controls which of two queries will be executed to find out a property from the database. The result of the executed query is stored in a the METACAT variable.
<InitCommands>
   <Command id="neoview.getDbVersion" method="runBeforeConditionsEval">
      <SQL>
         <![CDATA[
SELECT SUBSTRING(SYSTEM_VERSION FROM 10)
FROM (GET VERSION OF SYSTEM) V(SYSTEM_VERSION)
         ]]>
      </SQL>
      <Output id="DBVERSION" index="1"/>
   </Command>

   <Command id="neoview.getMaster">
      <If test="#DBVERSION gte 2400">
         <SQL>
            <![CDATA[
SELECT MIN(SYSTEM_CATALOGS) AS MASTER_CAT
FROM (GET SYSTEM CATALOGS) V(SYSTEM_CATALOGS)
WHERE SYSTEM_CATALOGS LIKE _ISO88591'NONSTOP_SQLMX_%'
           ]]>
         </SQL>
      </If>
      <Else>
         <SQL>
            <![CDATA[
SELECT 'NONSTOP_SQLMX_${#dp.METACAT}'
FROM (VALUES(1)) AS T1
           ]]>
         </SQL>
      </Else>
      <Output id="METACAT" index="1"/>
   </Command>
</InitCommands>
Commands in InitCommands are processed in two phases, the first phase execute commands that have the method="runBeforeConditionsEval" attribute set. After the first execution phase are all conditions evaluated and processed, the last execution phase will take care of all commands with no method="runBeforeConditionsEval" attribute set.

Here is an example how the METACAT variable is used in the rest of the database profile:
<Command id="neoview.getCatalogs">
   <SQL>
      <![CDATA[
SELECT
    TRIM(CAT_NAME) AS CATALOG_NAME
FROM
${METACAT}.SYSTEM_SCHEMA.CATSYS C
WHERE
    CAT_NAME     NOT LIKE _ISO88591'NONSTOP_SQLMX_%'
    AND CAT_NAME NOT IN (_ISO88591'NSMWEB', _ISO88591'NVSCRIPT', _ISO88591'METRIC', 
                         _ISO88591'MATRIX', _ISO88591'GENUSCAT', _ISO88591'MANAGEABILITY')
ORDER BY
    CATALOG_NAME
FOR READ UNCOMMITTED ACCESS
      ]]>
   </SQL>
</Command>

<Commands> - The SQLs used to interact with the database

This element contains all Command elements with SQL sub element. A Command element is identified by a unique id attribute, which is then referred in ObjectsTreeDef, ObjectsViewDef and (optionally) ObjectsActionDef definitions. 
<Commands>
  &generic-commands;

  <Command>
    ...
  </Command>

</Commands>
The first statement in the <Commands> element is:

    &generic-commands;

This means that the generic-commands entity defined at the top of the XML file is included in the XML i.e., all its definitions are accessible from the ObjectsTreeDef, ObjectsViewDef and ObjectsActionDef. If you don't plan to use any of the generic command, simply ignore this include statement.

<Command>

The Command element specifies the SQL associated with the command. In most cases, the SQL should return a result set with 0 or several rows. (The exception is actions which not necessarily need to return a result set, e.g., a "drop" action). The following command queries for login information in Sybase ASE. 
<Command id="sybase-ase.getLogins">
   <SQL>
      <![CDATA[
select name "Name", suid "SUID", dbname "Default Database", fullname "Full Name",
language "Default Language", totcpu "CPU Time", totio "I/O Time", pwdate "Password Set"
from master.dbo.syslogins order by 1
      ]]>
   </SQL>
</Command>
The id for this command is sybase-ase.getLogins. The reason for prefixing the id with the name of the profile is for maintainability. Since the generic-commands.xml file is included in most profiles, it is good to use unique prefixes for all commands so that they do not conflict with the commands in the generic-commands.xml file.

Result set

The result set for the previous query looks as follows:

name suid dbname fullname language totcpu totio pwdate
jstask
 3
 master
 (null)
 (null)
 0
 10
 2009-12-22 09:53:50
probe 2
 subsystemdb (null) (null) 0 0 2009-12-22 08:37:35
sa 1
 master (null) (null) 182
 168723
 2009-12-22 08:36:54

The way DbVisualizer handles the result set depends on whether the command is executed as a request in the database objects tree (ObjectsTreeDef) or in the object view (ObjectsViewDef). If executed in the database objects tree, each row in the result set will be represented by a new node in the tree. If executed in the object view, it is the viewer component that decides how the result will be presented. For more information on how a result set is used in the ObjectsTreeDef or ObjectsViewDef, read the specific sections.

Another important difference between the database objects tree and the object view is that the tree is a hierarchical structure of objects while the object view presents information about a specific object. An object that is inserted in the database objects tree is a 1..1 mapping to a row from the actual result set. The end user will see these objects (nodes) by some descriptive label, as defined in the ObjectsTreeDef. However, all data for the row from the original result set is stored with the object in the tree and may be used in the label, variables, conditions, etc. This is not the case in the ObjectViewDef.

The following example put some light on this. Consider the previous result set and that it's used to create objects in the database objects tree. The end user will see the following in DbVisualizer. The visible name for each row is the name column in the result set.


Figure: Sample of the Logins node having two child nodes

Each of the sa and probe nodes have all their respective data from the result set associated with the nodes. The data is referenced as commandId.columnName, i.e., sybase-ase.getLogins.name, sybase-ase.getLogins.dbname, etc. All associated data for the sa node in the example is listed next:
sybase-ase.getLogins.Name = sa
sybase-ase.getLogins.suid = 1
sybase-ase.getLogins.Default Database = master
sybase-ase.getLogins.Full Name = (null)
sybase-ase.getLogins.Default Language = (null)
sybase-ase.getLogins.CPU Time = 182
sybase-ase.getLogins.I/O Time = 168716
sybase-ase.getLogins.Password Set = 2009-12-22 08:36:54.576
The DataNode definition presenting sa and probe in the previous screenshot example use the associated data for the label as follows:

label="${sybase-ase.getLogins.name}"

<Input> - Setting command input 

There are two types of Commands: with and without dynamic input. The difference is that dynamic input Commands accepts input data that is typically used to form the WHERE clause in SELECT SQLs. The previous example illustrates a static SQL (without dynamic data).

To allow for dynamic input, just add variables at the positions in the statement that should get dynamic values. The following is an extension of the previous example that allows for dynamic input.
<Command id="sybase-ase.getLogins">
   <SQL>
      <![CDATA[
select name "Name", suid "SUID", dbname "Default Database", fullname "Full Name",
language "Default Language", totcpu "CPU Time", totio "I/O Time", pwdate "Password Set"
from master.dbo.syslogins where name = '${name}' and suid = '${suid}' order by 1
      ]]>
   </SQL>
</Command>
The example above adds two input variables: ${name} and ${suid}. Values for these variables should then be supplied wherever the command is referred for execution via the Input element.

The following is an example from the ObjectsTreeDef and its use of the sybase-ase.getLogins command: 
<GroupNode type="Logins" label="Logins">
  <DataNode type="Login" label="${sybase-ase.getLogins.Name} isLeaf="true">
    <SetVar name="objectname" value="${sybase-ase.getLogins.Name}">
    <Command idref="sybase-ase.getLogins">
      <Input name="name" value="sa">
      <Input name="suid" value="${sybase-ase.getProcesses.suid}">
    </Command>
  </DataNode>
</GroupNode>
(Note that the Command element refers the command via the idref attribute which will be matched with the corresponding id for the Command).

There is no magic with this definition, since the ${name} variable in the final SQL will be replaced with string "sa".

The value for the ${suid} definition will in this case get the value of the sybase-ase.getProcesses.suid when the SQL is executed. So where is this variable defined? As explained in the Result Set section, all the data for a row in the result set is associated with the objects in the database objects tree. In addition, it is possible to use all the data kept by the current object and all its parent objects (as presented in the objects tree) in the input to commands. So to evaluate the ${sybase-ase.getProcesses.suid} variable, DbVisualizer first looks for the variable in the current object. If it doesn't exist, it continues to look through the parent objects until it reaches the root, which is the Connections object in the objects tree. If the variable is not found, it will be set to the string representation for null, which is (null) by default. Whenever a matching variable is found, DbVisualizer uses its value and stops searching.

<Output> - Redefine command output

As mentioned earlier, a specific column value in a result set row is referenced by the name of the column prefixed by the command id. Sometimes this is not desirable and the Output definition can be used to change this behavior. The following identifies a column in the result set by its index number, starting from 1, and then force its name to be set to the value of the id attribute. 
<Output id="sybase-ase.getLogins.Name" index="1">
<Output id="sybase-ase.getLogins.suid" index="2">
The Output element can also be used to alter the structure of columns in the result set by adding, renaming or removing columns. 
<Output modelaction="add" index="THIS_IS_A_NEW_COLUMN" value="Rattle and Hum">
<Output modelaction="rename" index="2" name="PHONE">
<Output modelaction="drop" index="MOBILE_PHONE">
<Output modelaction="removeisnullrows" index="4">
<Output modelaction="removerowsifequalto" index="ORDINAL_POSITION" value="0"/>
(All model actions except add accepts either the name of the column or index number starting from the left at index 1).
The rename operation is primarily used when building a custom command that is supposed to be used by a viewer that requires predefined input by specific column names. Read more in the ObjectsViewDef section.

<ObjectsTreeDef> - Definition of the Database Objects Tree

The ObjectsTreeDef element section controls how the database objects tree should be presented and which commands should be executed to form its content (nodes). The mapping between the graphical representation in DbVisualizer and its ObejctsTreeDef XML is as straight forward as it can be:
<ObjectsTreeDef id="sybase-ase">
  <GroupNode type="Databases">
    <DataNode type="Catalog">
      <GroupNode type="Tables">
        <DataNode type="Table"/>
      </GroupNode>
      <GroupNode type="SystemTables">
        <DataNode type="SystemTable"/>
      </GroupNode>
      <GroupNode type="Views">
        <DataNode type="View"/>
      </GroupNode>
      <GroupNode type="Users"/>
      <GroupNode type="Groups">
        <DataNode type="Group"/>
      </GroupNode>
      <GroupNode type="Types"/>
      <GroupNode type="Triggers">
        <DataNode type="Trigger"/>
      </GroupNode>
      <GroupNode type="Procedures">
        <DataNode type="Procedure"/>
      </GroupNode>
    </DataNode>
  </GroupNode>

  <GroupNode type="DBA">
    <GroupNode type="ServerInfo"/>
    <GroupNode type="Logins">
      <DataNode type="Login"/>
    </GroupNode>
    <GroupNode type="Devices">
      <DataNode type="Device"/>
    </GroupNode>
    <GroupNode type="RemoteServers"/>
    <GroupNode type="Processes"/>
    <GroupNode type="ServerRoles">
      <DataNode type="ServerRole"/>
    </GroupNode>
    <GroupNode type="Transactions"/>
    <GroupNode type="Locks"/>
  </GroupNode>
</ObjectsTreeDef>

Figure: The visual database objects tree and its XML definition

The screenshot shows all nodes representing the GroupNode definitions in the ObjectsTreeDef. One exception is the Logins object, which has been expanded (jstask, probe and sa child objects) to illustrate what DataNode objects look like. The ObjectsTreeDef in the example has been simplified to show only the type attribute. (The label of the nodes as they appear in the visual tree is not listed in the ObjectsTreeDef example). The type attribute is primarily used internally in the profile as an identifier between the ObjectsTreeDef and the ObjectsViewDef. The type is also visible in the DbVisualizer GUI, in the tooltip for a tree node and in the object view header. The type is also used to identify the icon used to represent the object type.

There are no limitation on the number of levels in the ObjectsTreeDef. A good rule of thumb is, however, to keep it simple, clean and intuitive.

The DataNode definitions are the most important objects in the ObjectTreeDef. They also define which object tree filters are available for each object type, if overlayed icons should appear (and the criteria), etc. Read the next sections for details.

<GroupNode> - Static objects used for grouping

The GroupNode element is represents a static object in the tree. These don't have any associated SQL and appear only once where they are defined. A GroupNode is primarily used for structural and grouping purposes. The GroupNode element have the following attributes. 
<GroupNode type="SystemTables" label="System Tables" isLeaf="false">
  ... 
</GroupNode>
The isLeaf attribute is optional and controls whether the GroupNode may have any child objects or not. It can always be set to true, but the effect in the visual database objects tree is then that the expand icon to the left of the group node icon will always be displayed, even if it can never have any child objects. The default setting for isLeaf is false.

If isLeaf is set to false and there are child Group and/or Data -nodes, these will not appear. The result may cause some frustration during the design...

<DataNode> - Dynamic objects created via SQL

The DataNode element feeds the tree with nodes produced by a Command. The example in the Command section querying for all logins in Sybase ASE look as follow in the ObjectsTreeDef: 
<GroupNode type="Logins" label="Logins">
  <DataNode type="Login" label="${sybase-ase.getLogins.Name}" isLeaf="true">
    <Command idref="sybase-ase.getLogins"/> 
  </DataNode>
</GroupNode>
First, there is a GroupNode element with the purpose to group all child objects in a Logins node. The DataNode has, in this example, the same attributes as the GroupNode, the type is however "Login" instead of "Logins" (as it is for the GroupNode). This difference is important when the user selects one of the objects, since the the Object View shows the appropriate views based on the object type.

The DataNode definition can be seen as a template, as the associated command fetches rows of data from the database and DbVisualizer uses the DataNode definition to create one node per row in the result set.

The label attribute for the data node is somewhat different, as it introduces the use of a variable (or several). The real value for the label will, in this example, be the value in the Name column produced by the sybase-ase.getLogins command, as you can see in the Command definition (variable names are automatically prefixed with the command id).

The Command element uses the idref attribute to identify the command that should be executed. The command in this case and in the Result set section produces a result set with 2 rows and 8 columns. The result will be two nodes each, with the label of the Name column in the result set.

Figure: Sample of the Logins node having two child nodes

The label can be changed by setting it to any other valid variable or a combination of several variables. (It's even possible to specify static text in the label): 
label="${sybase-ase.getLogins.Name} (${sybase-ase.getLogins.Default Database})"
The example above results in the following labels: 
jstask (master)
probe (subsystemdb)
sa (master)
The complete set of attributes for the DataNode element is: 
                type="value"         - The type of node (required)
          actiontype="value"         - Object type used for object actions (optional)
               label="value"         - The visual label (required)
              isLeaf="true/false"    - Specifies if the node can have child objects (default true)
                sort="col1,col2"     - A comma separated list of names/variables used for sorting
drop-label-not-equal="value"         - Do not add the node if the label is not equal to this value
                                       or variable
           warnstate="condition"     - If condition is true, show an overlay icon for the node
          errorstate="condition"     - If condition is true, show an overlay icon for the node
stop-label-hot-equal="value"         - The node will be a leaf if the label doesn't match this value
                                       or variable 
     is-empty-output="continue/stop" - If result set is empty, use this to control whether child
                                       GroupNode/DataNodes should be added anyway or ignored
The Command definition in the example above is simple, since it doesn't use any variables in the SQL. Continue reading the next section for details about passing input data to commands.

<Command>

Commands are referenced in the DataNode definition by the idref attribute. Sometimes it is required that a specific DataNode must supply input to a command. This is done by adding Input elements as children to the Command. 
<DataNode type="Login" label="${sybase-ase.getLogins.Name}" isLeaf="true">
  <Command idref="sybase-ase.getLogins"> 
    <Input name="name" value="sa">
    <Input name="suid" value="${sybase-ase.getProcesses.suid}">
  </Command>
</DataNode>
The value for a variable specified in an Input element is evaluated using the strategy outlined in the Result set section.
<Filter>
The Filter element is specific for Command elements that appear in the ObjectsTreeDef section. A filter define which data for a DataNode that is allowed to use in filters. This filter functionality is commonly referred as the Database Objects Tree Filtering in DbVisualizer. The filtering setup appears below the database objects tree, and the following example shows that filtering may be specified for these object types:
For each of the Filter definitions, one or several columns can be as part of the filtering criteria.


Figure: Screen shot showing the filter pane

<DataNode type="Views" label="${sybase-ase.getViews.Name}" isLeaf="true">
  <Command idref="sybase-ase.getViews"> 
    <Filter type="View" name="View Table">
      <Column index="TABLE_NAME" name="Name"/>
    </Filter> 
  </Command>
</DataNode>
The previous filter definition specifies a filter for the View object type. The name specifies the name of the filter as it appears in the object type drop-down list. The nested Column element defines the index, which should be either a column name in the result set or an index number for the column. The name attribute specifies the name of the column as it appears in the filter pane.

Several Column elements may be specified for a Filter element.

<SetVar>

The SetVar element is needed in the ObjectsTreeDef for DataNode's. Some object types have special meaning in DbVisualizer. Two examples are the Catalog and Schema object types. For DataNode objects, you must use SetVar elements to identify them, with name attributes set to "catalog" and "schema", respectively. 
<DataNode type="Catalog" label="${getCatalogs.TABLE_CAT}" isLeaf="false">
  <SetVar name="catalog" value="${getCatalogs.TABLE_CAT}"> 
</DataNode>
All non Catalog or Schema DataNode's must use SetVar to set the "objectname" variable: 
<DataNode type="Views" label="${sybase-ase.getViews.Name}" isLeaf="true">
  <SetVar name="objectname" value="${sybase-ase.getViews.Name}"> 
  <SetVar name="rowcount" value="true/false"> 
</DataNode>
The objectname variable is used to identify the object represented by the data node, so that it can be uniformly referenced in object views and object actions. Its value should be the identifier for the object as it is identified in the database, e.g., a table name or view name.

The rowcount variable is optional and controls whether the object supports showing row count information when Show/Hide Table Row Count right-click menu choice is enabled for the database connection.

Another optional variable (not shown in the example above) is named acceptInQB. If set to true, nodes of this type can be used in the Query Builder. It should only be set to true for object types representing tabular data that can be queried with an SQL SELECT statement, such as tables, views, materialized views, etc.

<SetVar> variables are by default invisible in for example the NodeFormViewer. If you want to override this behavior then add the  action attribute and set its value to show. If you want to drop a variable completely from the node simply set the action attribute to drop.

<ObjectsViewDef> - Definition of the Object Views

The ObjectsViewDef element defines all views for the object types in the objects tree. These views are displayed in the Object View area for the selected object. Which views should appear when selecting a node in the tree is based on the object type for the tree node and the corresponding object view definition.

When an object is selected in the tree (sa in the screenshot below), its complete information is passed to the object view handler (right in the sample). This handler determines, based on the object type, which object view should be used to present the information. When the object view is found, all data views are created as tabs in the user interface. The selected object and its information is passed to each of the data views for processing and presentation. The following shows how the Object View look in DbVisualizer and its accompanying ObjectView definitions.


 
<ObjectView type="Logins">
  <DataView type="Logins" label="Logins" 
            viewer="grid">
    <Command idref="sybase-ase.getLogins"/>
  </DataView>
</ObjectView>

<ObjectView type="Login"> 
  <DataView type="Info" label="Info" 
            viewer="node-form"/>
  <DataView type="Databases" label="Databases" 
            viewer="grid">
    <Command idref="sybase-ase.getLoginDatabases"/>
  </DataView>
  <DataView type="Roles" label="Roles" 
            viewer="grid">
    <Command idref="sybase-ase.getLoginRoles"/>
  </DataView>
</ObjectView>

Figure: The visual database objects tree, object view and the XML definition

The screenshot shows both the Logins node and its child nodes, jstask, probe and sa. From the GroupNode and DataNode declaration examples in the previous sections, we know that these nodes are instances of the object types Logins (the Login node) and Login (the two sub nodes, sa and probe).

The ObjectView XML definitions shows the data views for these two types, Logins and Login. Clicking on the node labeled Logins in the tree will show the object view for the <ObjectView type="Logins"> definition while clicking on the node labeled jstask, probe or sa will show the object view for the <ObjectView type="Login"> .

The example shows sa being selected. Its DataView definitions are (by label):
These views are presented in DbVisualizer as tabs. The label of each tab is the label defined in the DataView and the icons are defined by the respective object type.

The ObjectsViewDef root element has the following attributes: 
<!-- Include the generic-view --> 
&generic-view; 

<ObjectsViewDef id="sybase-ase" extends="generic" >
  ...
</ObjectsViewDef>
The first statement for the ObjectsViewDef elements is:

    &generic-view;

This simply means that the generic-view entity defined at the top of the XML file is included in the XML, i.e., all its definitions are accessible as is. One example is the ObjectView definition in the generic-view.xml file for the Table object type. It contains a lot of DataView elements that identify all viewers for the Table. If you now want to use the generic Table DataView's but add a new Abbreviations data view, then simply extend the generic Table DataView. This is done by adding a extends="generic" attribute in the ObjectsViewDef element. By using the exact same object type in the extended ObjectView, you will then get this behavior. Read more about extending ObjectView's in the Extending ObjectView section.

<ObjectView>

The ObjectView element is associated with an object type and groups all DataView elements that appear when the object type is selected in the database objects tree. Here follows the ObjectView definition for the Login object type. 
<ObjectView type="Login">
  ...
</ObjectView>
This element is simple as its only attribute is the type attribute. The type attribute value is used when a node is clicked in the database objects tree to map the object of the type clicked and its ObjectView.

<DataView>

The DataView element is as important as the DataNode is in the ObjectsTreeDef. It defines how the viewer should be labeled in DbVisualizer, which viewer (presentation form) it should use, commands and other things. The following is the DataView definitions for the Login object type. (The ObjectView element is part of the sample just for clarification). 
<ObjectView type="Login">
  <DataView type="Info" label="Info" viewer="node-form"/>
  <DataView type="Databases" label="Databases" viewer="grid">
    <Command idref="sybase-ase.getLoginDatabases"/>
  </DataView>
  <DataView type="Roles" label="Roles" viewer="grid">
    <Command idref="sybase-ase.getLoginRoles"/>
  </DataView>
</ObjectView>
The elements are used to define how the object is presented in DbVisualizer, as described in the introduction of the ObjectsViewDef section. All three data view elements have a viewer attribute, which identifies how the data in the view should be be presented, e.g., as a grid or a form. See the next section for a list of viewers.

Viewers

The viewer attribute for a DataView specifies how the data for the view should be presented. The following sections walk through the supported viewers.

The following sample illustrates the viewer attribute. 
<ObjectView type="Login">
  <DataView type="Info" label="Info" viewer="node-form"/>
</ObjectView>
DataView definitions may be nested and the viewers are then presented with the nested DataView in the lower part of the screen.
grid
The grid viewer presents a result set in a grid, with standard grid features such as search, copy, fit, export, etc. The result set is presented exactly as it is produced by the Command and any optional Output processing.

Here is a sample of the XML for the grid viewer: 
<DataView type="Columns" label="Columns" viewer="grid">
  <Command idref="oracle.getColumns">
    <Input name="owner" value="${schema}"/>
    <Input name="table" value="${objectname}"/>
  </Command>
</DataView>
And here is a screenshot of the standard grid viewer created from the previous definition.


Figure: The grid viewer

The nesting capability for grid viewers is really powerful, as it can be used to create a drill-down view of the data. Consider the scenario with a grid viewer showing all Trigger objects. Wouldn't it be nice to offer the user the capability to display the trigger source when selecting a row in the list? This is easily accomplished with the following: 
<DataView type="Trigger" label="Triggers" viewer="grid">
  <Command idref="oracle.getTriggers">
    <Input name="owner" value="${schema}"/>
    <Input name="table" value="${objectname}"/>
  </Command>
  <DataView type="Source" label="Source" viewer="text">
    <Input name="dataColumn" value="text"/>
    <Input name="formatSQL" value="true"/>
    <Command idref="oracle.getTriggerSource">
      <Input name="owner" value="${OWNER}"/>
      <Input name="name" value="${TRIGGER_NAME}"/>
    </Command>
  </DataView>
  <DataView type="Info" label="Info" viewer="node-form"/>
</DataView>
The following screenshot illustrates the above sample:


Figure: Example use of nested DataViews

Adding custom menu items in the grid

The menuItem parameter specifies entries that should appear in the right-click menu in the grid. The value for the menuItem is the label for the item, while the child Input element specifies the SQL command that should be produced for all selected rows when the menu item is selected. The result of a custom menu item is that the grid viewer creates a statement that it copies to the SQL Commander; it will never execute the produced SQL in the scope of the viewer.

The following is an example with two menu items:
The variables in the SQL statement should identify column names in the result set. The user may select any columns in the visual grid and choose a custom menu item. It is only the actual rows that are picked from the selection as the columns are predefined by the menuItem declaration.

The variables specified in these examples starts with ${schema=...} and ${object=...}. These defines that the first variable represents a schema variable while the second defines an object. This is needed for DbVisualizer to determine whether delimited identifiers should be used and if identifiers should be qualified, as defined in the connection properties for the database. 
<Input name="menuItem" value="Script: SELECT ALL">
  <Input name="command" value="select * from ${schema=OWNER}${object=TABLE_NAME}"/>
</Input>

<Input name="menuItem" value="Script: DROP TABLE">
  <Input name="command" value="drop table ${schema=OWNER}${object=TABLE_NAME}"/>
</Input>
Here is a sample:


Figure: Custom menu items in grid viewer

The result of selecting a menu item defined as a menuItem input parameter is that the specified command is copied to the current SQL editor.

Setting initial max column width

Some result sets may contain columns with very wide data. The following parameter sets an initial maximum column width for all columns in the grid.
 
<Input name="columnWidth" value=""/>
text
The text viewer presents data from one column in a result set in a text browser (read only editor). This viewer is typically used to present large chunks of data, such as source code, SQL statements, etc. If the result set contains several rows, the text viewer reads the data in the column for each row and present the combined data.

Here is a sample of the XML for the text viewer: 
<DataView type="Source" label="Source" viewer="text">
  <Input name="dataColumn" value="text"/>
  <Input name="formatSQL" value="true"/>
  <Input name="newline" value=""/>
  <Command idref="oracle.getTriggerSource">
    <Input name="owner" value="${schema}"/>
    <Input name="name" value="${objectname}"/>
  </Command>
</DataView>
And here is a screenshot of the Source tab based on the previous definition.


Figure: The text viewer

Specify what column to browse

By default, the text viewer uses the data in first column. This behavior can be controlled by using the dataColumn input parameter. Simply specify the name of the column in the result set or its index (starting at 1 from the left). 
<Input name="dataColumn" value=""/>
Enable SQL formatting of the data

The text viewer includes the SQL Formatting toolbar button, which when pressed formats the content in the viewer. The formatSQL input parameter is used to control whether formatting should be enabled by default. If formatSQL is not specified, no initial formatting is made. 
<Input name="formatSQL" value=""/>
Adding newline to each row

Defines the static text that should separate every row in the grid. A "\n" somewhere in the value will be converted to a true newline in the final output. The default behavior is not to add a newline sequence for each row.
<Input name="newline" value="\n"/>
form
The form viewer presents row(s) from a result set in a form. If several rows are in the result, they are presented in a list. Selecting one row from the list presents all columns and data for that row in a form.

Here is a sample of the XML for the form viewer: 
<DataView type="Info" label="Info" viewer="form">
  <Command idref="oracle.getTable">
    <Input name="owner" value="${schema}"/>
    <Input name="table" value="${objectname}"/>
  </Command>
</DataView>
And here is a screenshot of the Info tab based on the previous definition.


Figure: The form viewer

node-form
The node-form viewer presents all data associated with the selected object (variables).

Here is a sample of the XML for the node-form viewer: 
<DataView type="Constraint" label="Constraint" viewer="node-form">
  <Input name="hidecolumn" value="oracle.getKeys.TABLE_OWNER"/>
</DataView>
And here is a screenshot of the Constraint tab based on the previous definition.


Figure: The node-form viewer

Hiding columns

There may be data associated with the object that you don't want to present in the node form for the user. The hidecolumn input parameter control what data for the object that should be invisible and you may repeat the this option as many times you like to handle multiple hidden variables. 
<Input name="hidecolumn" value="oracle.getKeys.TABLE_OWNER"/>
table-refs
The table-refs viewer shows the references graph for the current object (this must be an object supporting referential integrity constraints, such as a Table),

Here is a sample of the XML for the table-refs viewer: 
<DataView type="References" label="References" viewer="table-refs"/>
And here is a screenshot of the References tab based on the previous definition.


Figure: The table-refs viewer

tables-refs
The tables-refs viewer shows the references graph for several tables in the result set (the result set must contain objects supporting referential integrity constraints, such as a Table).

Here is a sample of the XML for the tables-refs viewer: 
<DataView type="References" label="References" viewer="tables-refs">
  <Command idref="getTables">
    <Input name="catalog" value="${catalog}"/>
    <Input name="schema" value="${schema}"/>
    <Input name="table" value="${objectname}"/>
    <Input name="type" value="${tableType}"/>
  </Command>
</DataView>
And here is a screenshot of the References tab based on the previous definition.


Figure: The tables-refs viewer

table-data
The table-data viewer shows the data for a table in a grid with editing features.

Information presented in the grid is obtained automatically by the viewer via a traditional SELECT * FROM table statement, i.e., the object type having this viewer defined must be able to support getting a result set via this SQL statement.

Here is a sample of the XML for the table-data viewer: 
<DataView type="Data" label="Data" viewer="table-data">
  <Input name="disableEdit" value="<true/false>"/>
</DataView>
And here is a screenshot of the Data tab based on the previous definition.


Figure: The table-data viewer

Disable data editing

The default strategy for the table-data viewer is to automatically check whether the data can be edited or not. If editing is allowed a few related buttons will appear in the toolbar. However, sometimes you may want to disable editing completely for the table-data viewer. Do this with the following input element: 

<Input name="disableEdit" value=""/>
table-rowcount
The table-rowcount viewer shows the row count for a (table) object.

The row count is obtained automatically by the viewer via a traditional SELECT COUNT(*) FROM table statement, i.e., the object type having this viewer defined must be able to support getting a result set via this SQL statement.

Here is a sample of the XML for the table-rowcount viewer: 
<DataView type="RowCount" label="Row Count" viewer="table-rowcount"/>
And here is a screenshot of the Row Count tab based on the previous definition.


Figure: The table-rowcount viewer

<Command>

Please read the Command section above, as the capabilities of this element are the same when used with a data view.

<Message>

The Message element is very simple: it defines a message that should appear at the top of the viewer. The Message element is used to define the text for a description of the data presented in the viewer. The text in the message may contain common HTML tags such as <b> (bold), <i> (italic), <br> (line break), etc.

Here is a sample of the XML for using the Message element in a grid viewer: 
<ObjectView type="RecycleBin">
  <DataView type="RecycleBin" label="Recycle Bin" viewer="grid">
    <Command idref="oracle.getRecycleBin">
      <Input name="schema" value="${schema}"/>
      <Input name="login_schema" value="${dbvis-defaultCatalogOrSchema}"/>
    </Command>
    <Message>
      <![CDATA[
<html>
These are the tables currently in the recycle bin for this schema. Right click on a bin
table in objects tree to restore or permanently purge it.<br>
<b>Note: The recycle bin is always empty if not looking at the bin for your
login schema (default).</b>
</html>
      ]]>
    </Message>
  </DataView>
</ObjectView>
And here is a screenshot of the Recycle Bin tab based on the previous definition.


Figure: The appearance of a Message in a viewer

Extending ObjectView

An existing ObjectView definition in, for example, the generic-view.xml file can be extended in a database profile by using a few action attributes for each of the DataView elements. To extend a definition, the object type specified in the ObjectView type attribute must match the type in the parent profile. You have the following options when extending a definition:

<ObjectsActionDef> - Definition of user actions

The previous sections describe how to define which objects should appear in the objects tree, and which views should be displayed when selecting an object in the tree. The ObjectsActionDef section in the profile defines which operations are available for the object types defined in the ObjectTreeDef. Object actions are very powerful, as they offer an extensive number of features to define actions for almost any type of object operation.

In DbVisualizer, the object type actions menu is accessed via the right-click menu in the objects tree or via the Actions button in the object view:


Figure: The Actions menu for the selected object

All of the operations for the selected Table object in the figure above are expressed in the ObjectsActionDef section. The implementation for these actions are either declared completely with XML elements via standard object actions, or via specialized action handlers. (The API for action handlers is not yet documented). The following screenshot shows the dialog appearing when executing an action via the default action handler:


Figure: The default action handler

The first field in the dialog, Database Connection, is always present and shows the alias of the database connection the selected object is associated with. At the bottom, there is a Show SQL control that, when enabled, displays the final SQL for the action. The bottom right buttons are used to run the action (the label of the button may be Execute or Script based on the action mode), or to Cancel the action completely.

Variables

Variables are used to reference data for the object for which the action was launched, and the data for all its parent objects in the objects tree. Variables are also used to reference input data specified by the user in the actions dialog. Variables are typically used in the Command, Confirm, Result and SetVar elements.

Variables are specified in the following format:

${variableName}

The following is an example for a Rename Table action. It first shows the name of the database connection (which is always present) along with information about the table being renamed. The last two input fields should be entered by the user and identify the new name of the table. The New Database control is a list from which the user should select the name of the new database. The new table name should be entered in the New Table Name field.

If the Show SQL control is enabled, you will see any edits in the dialog being reflected directly in the final SQL Preview.


Figure: The default action handler

The complete action definition for the previous Rename Table action is as follows: 
<Action id="mysql-table-rename" label="Rename Table" reload="true" icon="rename">
  <Input label="Database" style="text" editable="false">
    <Default>${catalog}</Default>
  </Input>

  <Input label="Table" style="text" editable="false">
    <Default>${objectname}</Default>
  </Input>

  <Input label="New Database" name="newCatalog" style="list">
    <Values>
      <Command><SQL><![CDATA[show databases]]></SQL></Command>
    </Values>
    <Default>${catalog}</Default>
  </Input>

  <Input label="New Table Name" name="newTable" style="text"/>

  <Command>
    <SQL>
      <![CDATA[
rename table `${catalog}`.`${objectname}` 
to `${newCatalog}`.`${newTable}`
      ]]>
    </SQL>
  </Command>

  <Confirm>
    <![CDATA[
Confirm rename of ${catalog}.${objectname} to ${newCatalog}.${newTable}?
    ]]>
  </Confirm>

  <Result>
    <![CDATA[
Table ${catalog}.${objectname} renamed to ${newCatalog}.${newTable}!
    ]]>
  </Result>
</Action>
First, there is the Action element with some attributes specifying the label of the action, icon and whether the objects tree (and the current object view) should be reloaded after the action has been executed.

The next block of elements are Input fields defining the data for the action. As you can see in the example, there is a  ${catalog} variable in the Default element for the Database input and an ${objectname} variable in the Default element for the Table input. The values for these variables are fetched from the selected object in the objects tree. Variables are evaluated by first checking if the variable is in the scope of the action dialog (i.e., another input field), then if the variable is defined for the object for which the action was launched, and then if it is defined for any of the parent objects until the root object in the tree (Connections node) is reached. If a variable is not found, its value is set to (null).

In the previous XML sample, the value of the ${catalog} variable is the name of the database in which the table object is stored. The ${objectname} is the current name of the table (these variables are described in the ObjectsTreeDef section).

The New Database input field is a list component which shows a list of databases based on the result set of the specified SQL command. The Default setting for the database will be the database in which the table is currently stored based on the ${catalog} variable. 

The New Table Name input field is a simple text field in which the user may enter any text.

Both the New Database and New Table Name fields are editable and should be specified by the user. This data is then accessible via the variables specified in the name attribute, i.e., newCatalog and newTable.

The Command element declares the SQL statement that should be executed by the action. In this example, the SQL combines static text with variables. 

<ActionGroup>

The ActionGroup element is a container and groups ActionGroup, Action and Separator elements. It is used to define what actions should be present for a particular object type. It also defines in what order the actions should appear in the menu and where any separators should be. ActionGroup elements can be nested to create sub menus. 
<ActionGroup type="Table">
The attributes for an ActionGroup are:

<Action>

The action element defines the action. 
<Action      id = "oracle-table-drop"
           icon = "remove"
          label = "Drop Table..."
         reload = "false"
           mode = "execute"
 processmarkers = "false"
     resulttype = "resultset"
   resultaction = "ask"
hideif          = "<condition>">
The attributes for an action are:

<Input>

An Input element specifies the characteristics of a visible field component for the actions dialog. The label attribute is recommended and is presented to the left of input field. If a label is not specified, the input field will occupy the complete width of the action dialog. All input fields are editable by default. The name attribute is required for editable fields and should specify the identity of the variable in which the user input is stored.

This is a minimal definition of an input field. It will show a read-only text field control labeled Size. 
<Input label="Size" editable="false"/>
If the input field is changed to be editable, the name attribute must be used to specify the identifier for the variable name. 
<Input label=Size" editable="true" name="theSize"/>
Any input element may contain the tip attribute. It is used to briefly document the purpose of the input field and is displayed as a tooltip when the user hovers the mouse pointer over it. 
<Input label=Size" editable="true" name="theSize" tip="Please enter the size of the new xxx"/>
The hideif attribute is useful to limit what <Input> fields should appear for an action. The condition specified in the hideif attribute have the same syntax as described in the <SetVar> section. Example:
<Input label="Unit" hideif="#dataMap.get('actionlevel') neq 'toplevel'">
Input fields can be aligned on a single row with the linebreak attribute. The default behavior is that every input field is displayed on a single row. Use the linebreak="false" attribute to define that the next input field will be arranged on the same line. To re-start the automatic line breaking feature you must use the linebreak="true" attribute. 
<Input name="size" label="Size" style="number" linebreak="false">
  <Default>10</Default>
</Input>
<Input name="unit" style="list" linebreak="true">
  <Labels>KB|MB</Labels>
  <Values>K|M</Values>
  <Default>M</Default>
</Input>
The previous example shows the use of the linebreak attribute. The size number field and the unit list will appear on the same line.

Specifying the default value as a result from an SQL statement is a trivial task: 
<Input label=Size" editable="true" name="theSize">
  <Default>
    <Command>
      <SQL>
select size from systables where tablename = '${objectname}' 
      </SQL>
    </Command>
  </Default>
</Input>
Since Default here will execute an SQL statement, it will automatically pick the value in the first row's first column and present it as the default. SQL may be specified in the Default element when used for all styles while SQL in Values and the Labels elements are valid only for list and radio styles). In some rare situations it may not be possible to express a SQL statement that will return a single column that will be displayed for Values, Labels and Default. An example is when data is collected via a stored procedure. To solve this problem specify the column attribute that takes the value either by the actual column name or column index:
<Input label=Size" editable="true" name="theSize">
  <Default column="2">
    <Command idref="getSize">
      <Input name"objectname" value="${objectname}"/>
    </Command>
  </Default>
</Input>
or by column name:
<Input label=Size" editable="true" name="theSize">
  <Default column="THE_SIZE>
    <Command idref="getSize">
      <Input name"objectname" value="${objectname}"/>
    </Command>
  </Default>
</Input>
An alternative to embedding the SQL in the element body, as in the previous example, is to refer to a command via the standard idref attribute: 
<Input label=Size" editable="true" name="theSize">
  <Default>
    <Command idref="getSize">
      <Input name"objectname" value="${objectname}"/>
    </Command>
  </Default>
</Input>
Instead of having duplicated SQLs in multiple actions, consider replacing these with Command elements referred via the idref attribute.

Referring commands in actions via the idref attribute is recommended when the same SQL is used in several actions. Use Input elements to pass parameters to the command.

The following sections presents the supported styles that can be used in the Input element.
text (single line)
The text style is used to present single-line data in a text field. 
<Input label="Enter your userid" name="userid" style="text">
  <Default>agneta</Default>
</Input>
text-editor (multi line)
A text-editor field is the same as the text style except that it presents a multi-line field. 
<Input label="Description" name="desc" style="text-editor" editable="true" args="height=50"/>
The args="height=50" attribute defines the height (in DLU) for the text-editor. The default height is 30 DLU's. 
number
A number style is the same as text except that it only accept number values. 
<Input label="Size" name="size" style="number" editable="true"/>
password 
A password field is the same as text except that it masks the value as "***". 
<Input label="Password" name="pw" style="password" editable="true"/>

Note that the password in visible in plain text in the SQL Preview.

list (large number of choices)
The list style displays a list of choices in a drop-down component. The list can be editable, meaning that the field showing the selection may be editable by the user. Here is a sample XML for the list style. 
<Input label="Select index type" name="type" style="list">
  <Values>Pizza|Pasta|Burger</Values>
  <Default>Pasta</Default>
</Input>
The Values element should, for static entries, list all choices separated by a vertical bar (|) character. A Default value can either list the name of the default choice or the index number (first choice starts at 0). In the example above, setting Default to {2} would set Burger to the default selection.

It is also possible to use the Labels element. If present, this should list all choices as they will appear in the actions dialog. Consider these as being the labels shown to the user, while Values in this case should list the choices that will go into the final SQL via the variable. Here is an example: 
<Input label="Select index type" name="type" style="list">
  <Values>Pizza|Pasta|Burger</Values>
  <Labels>Pizza the French style|Pasta Bolognese|Texas Burger</Labels>
  <Default>Pasta</Default>
</Input>
If the users selects Texas Burger then the value for variable type will be Burger.

The following shows how to use SQL to feed the list of values: 
<Input label="New Database" name="newCatalog" style="list">
  <Values>
    <Command>
      <SQL>
        <![CDATA[
show databases
        ]]>
      </SQL>
    </Command>
  </Values>
  <Default>${catalog}</Default>
</Input>
Here a Command element is specified as a sub element to Values. The result of the show databases SQL will be presented in the list component.

To make the list editable, specify the attribute editable="true".
radio (limited number of choices)
The radio style displays a list of choices organized as button components. The only difference between the radio and list styles are:
See the list style for complete capabilities of the radio style.
check (true/false, on/off, selected/unselected) 
The check style is suitable for yes/no, true/false, here/there types of input. Its enabled state indicates that the Value for the input will be set in the final variable. If the check box is disabled, the variable value is blank 
<Input label="Cascade Constraints" name="cascade" style="check">
  <Values>compact</Values>
</Input>
separator (visual divider between input controls) 
The separator style is not really an input element but is instead used to visually divide the fields in the in the actions dialog. If the label attribute is specified, it will be presented to the left of the separator line. If no label is specified, only the separator is displayed. 
<Input label="Parameters" style="separator"/>
The separator is a useful substitute for the standard label presented to the left of every input field. Here is a sample:


Figure: Sample showing separators and wide fields

The previous figure shows the use of separators and two fields that extend to the full width of the action dialog. The separators for Parameters and Source are here used as alternatives to labels for the fields below them.
grid (configurable multi row inputs)
The grid input style is presented as a grid with user controls to add, remove and move rows. The columns that should appear in the grid are defined by using any of the primitive styles: text, number, password, check, list and radio. The grid style is useful for data that allows the user to define multiple entries. Examples are, defining columns that should appear in a table index, setup data files for a tablespace or databank.

This example shows a grid style definition that will ask the user for parameters that will be part of a create procedure action. 
<Input name="parameters" style="grid">
  <Arg name="output" value="${direction} ${name} ${type}${_default}"/>
  <Arg name="newline" value=", "/>

  <Input name="name" label="Name" style="text">
    <Default>parm</Default>
  </Input>
  <Input name="direction" label="Direction" style="list">
    <Values>IN|INOUT|OUT</Values>
    <Default>IN</Default>
  </Input>
  <Input name="type" label="Type" style="text">
    <Default>nvarchar(20)</Default>
  </Input>
</Input>
Here is how it looks:



The sub elements for the grid style is different from the other input styles as it accepts sub <Input> elements. These input styles defines what columns should appear in the grid and the first input style will appear to the leftmost and the last in the rightmost column.

This example doesn't specify the label attribute as we want the grid to extend the full width of the actions dialog. The grid style use the <Arg> elements to customize the appearance and function of the field. The following arguments are handled by the grid style:
The resulting parameter list is created automatically by the control and is available in the variable name specified in the example to be parameters.

The <SetVar> element in the context of a grid style is used to process the data that will appear as defined by the <Arg name="output"> element. It is used to process the data for every row in the grid. Let's say that the output must contain the word "default" if the value in a column named "Default" is entered. <SetVar> is used to handle this: 
<SetVar name="_default" value='#default.equals("") ? "" : " default " + #default'/>
The #default input value is here evaluated and if it is not empty the " default " text s prefixed to the value of the #default value. The result is stored in the "_default" variable which is also refered in the output argument above.

<SetVar>

The SetVar element is very powerful, as it is used to do conditional processing and create new variables based on the content of other variables.

Consider an SQL statement for creating new users in the database: 
create user 'user' identified by 'password'
In this case it is quite easy to map the user field to an Input element for the action since it is a required field. The question arise for password which is optional. The identified by clause should only be part of the final SQL if the password is entered by the user. The solution for this scenario is to use the SetVar element. Here is the complete action definition: 
<Action id="mydb-user-create" label="Create User" reload="true" icon="add">
  <Input label="Userid" name="userid" style="text"/>
  <Input label="Password" name="password" style="password"/>

  <SetVar name="_password" value='#password.equals("") ? "" : " identified by \"" + #password + "\""'/>

  <Command>
    <SQL>
      <![CDATA[
create user ${userid} ${_password}
      ]]>
    </SQL>
  </Command>
</Action>
The SetVar element accepts two attributes:
The expression in the example above checks whether the password variable is empty. If it is empty, a blank value is being assigned to the _password variable. If it is not empty, the value for _password will be set to identified by "theEnteredPassword".

The SQL in the Command element now refer the new ${_password} variable instead of the original ${password}.
 

It is recommended that variables produced via SetVar elements are prefixed with an underline (_) to highlight were they come from.

<Confirm>

The Confirm element is displayed to the user when a request to Execute the action is made. If there are only read-only input fields in the action, this message is displayed in the body of the action dialog. The message is displayed in a confirmation dialog if there are editable fields.  
<Confirm>Really drop table ${table}?</Confirm>

Note that the message text can be composed of HTML tags such as <b>, <i>, <br>, etc.

<Result>

The Result element is optional and if specified, it is shown in a dialog after successful execution.

NOTE: Result elements are currently not displayed in DbVisualizer. It is however recommend that you specify these as they will most likely appear in some way or another in a future version. If you want to test the appearance of Result elements then open the DBVIS-HOME/resources/dbvis-custom.xml file in a text editor and make sure dbvis.showactionresult is set to true. 
<Result>Table ${table} has been dropped!</Result>

<Command>

The Command element specifies the SQL code that is executed by the action. 
<Command>
  <SQL>
    <![CDATA[
drop table ${table} mode ${mode} including constraints ${includeconstraints}
    ]]>
  </SQL>
</Command>

Conditional processing

Conditional processing means that a profile can adjust its content based on certain conditions. A few examples:
Conditional processing is especially useful for adapting the profile for different versions of the database (and/or JDBC driver). Another use for the conditional processing is to replace generic error messages with more user friendly messages.

Programmers familiar with if, else if and else will easily learn the conditional elements.

Depending on in which of the two phases the conditions should be processed, some restrictions and rules apply. Please read the following sections for more information.

When are conditional expressions processed?

There are two phases when conditions are processed:
  1. Conditional processing when database connection is established
    <If>, <ElseIf> and <Else> elements can be specified almost everywhere in the profile.
  2. Conditional processing during command execution
    The <OnError> element is used to define a message that will appear in DbVisualizer if a command fails. Conditions are used to control what message should appear.
DbVisualizer uses the type attribute to determine which If elements should be executed in which phase. If this attribute has the value runtime, it will be processed in the second phase. If it is not specified or set to load, it will be processed in the first phase.

Conditional processing when database connection is established

The following example shows the use of conditions that are processed during connect of the database connection. 
<Command id="sybase-ase.getLogins">
  <If test="#DatabaseMetaData.getDatabaseMajorVersion() lte 8">
    <SQL>
      <![CDATA[
select name from master.dbo.syslogins
      ]]>
    </SQL>
  </If>
  <ElseIf test="#DatabaseMetaData.getDatabaseMajorVersion() eq 9">
    <SQL>
      <![CDATA[
select name, suid from master.dbo.syslogins
      ]]>
    </SQL>
  </ElseIf>
  <Else>
    <SQL>
      <![CDATA[
select name, suid, dbname from master.dbo.syslogins
      ]]>
    </SQL>
  </Else>
</Command>
The above means that if the major version of the database being accessed is less then or equal to 8, the first SQL is used. If the version is equal to 9, the second SQL is used, and the last SQL is be used for all other version. The test attribute may contain conditions that are ANDed or ORed. Conditions can contain multiple evaluations, combined using parenthesis. The If, ElseIf and Else elements may be placed anywhere in the XML file.

Here is another example that controls whether certain nodes will appear in the database objects tree or not. 
<!-- Getting Table Engines was added in MySQL 4.1 --> 
<If test="(#dm.getDatabaseMajorVersion() eq 4 and #dm.getDatabaseMinorVersion() gte 1) 
          or #dm.getDatabaseMajorVersion() gte 5"> 
  <GroupNode type="TableEngines" label="Table Engines" isLeaf="true"/>

  <!-- "Errors" was added in MySQL 5 --> 
  <If test="#dm.getDatabaseMajorVersion() gte 5"> 
    <GroupNode type="Errors" label="Errors" isLeaf="true"/> 
  </If> 
</If>
As you can see, this example contains nested uses of If.

Conditional processing during command execution

Using conditional processing to evaluate any errors from a Command may be useful to rephrase error messages to be more user friendly. 
<Commands> 
  <OnError> 
    <!-- The ORA-942 error means "the table or view doesn't exist" --> 
    <!-- It is catched here since these errors typically indicates -->  
    <!-- that the user don't have privileges to access the SYS and/or --> 
    <!-- V$ tables. --> 
    <If test="#result.getErrorCode() eq 942" context="runtime"> 
      <Message> 
        <![CDATA[ 
You don't have the required privileges to view this object. 
        ]]> 
      </Message> 
    </If> 
    <ElseIf test="#result.getErrorCode() eq 17008" context="runtime"> 
      <Message> 
        <![CDATA[ 
Your connection with the database server has been interrupted! 
Please <a href="connect" action="connect">reconnect</a> to re-establish the connection. 
        ]]> 
      </Message> 
    </ElseIf> 
  </OnError>

  ...

</Commands>
The OnError element can be used in Commands and Command elements. If used in Commands element, its conditions are processed for all commands. If it is part of a specific Command, it is processed only for that command.

Current limitations


Copyright © 2011 DbVis Software AB. All rights reserved.