[ Master documentation index ]
Introduction
Note:
The plug-in framework is supported only by the 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.
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:
- 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.
- 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).
The name of the loaded profile is listed in the Connection
tab status bar when the connection has been established.You can click the
profile link to display the Database
Profile list.
Figure:
The status bar in the Connection tab when connected
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 database objects tree
(green box) is described by the ObjectsTreeDef root
element. (The Database Connections node is mandatory and
its appearance cannot be controlled by the profile).
- The object views (green and
blue boxes) are
described by the ObjectsViewDef root
element.
- The commands used to execute
the SQL to get the
information for ObjectsTreeDef,
ObjectsViewDef
and optionally ObjectsActionDef
definitions are defined by the Commands
root element.
- All Actions
for an object are defined in the ObjectsActionDef root
element. (Actions are optional).
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:
- generic-commands.xml
- generic-actions.xml
- generic-tree.xml
- generic-view.xml
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):
- Commands
Defines the SQLs for the ObjectsTreeDef, ObjectsViewDef and
optionally ObjectsActionDef.
- ObjectsActionDef
(optional)
Defines actions for object types.
- ObjectsTreeDef
Defines the structure and what objects should be visible in the objects
tree.
- ObjectsViewDef
Defines the object views for a specific object type.
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: 1.23 $"
date="$Date: 2007/06/09 21:37:11 $"
minver="5.0">
<!-- =================================================================== -->
<!-- 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>
|
Note: 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: 1.23 $"
date="$Date: 2007/06/09 21:37:11 $"
minver="5.0">
...
</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
<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, suid, dbname, fullname, language, totcpu,
totio, pwdate from master.dbo.syslogins
]]>
</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 |
sa
|
1
|
master |
(null)
|
(null)
|
0
|
0 |
2005-02-24 23:59:14 |
| probe |
2 |
subsystemdb |
(null) |
(null) |
0 |
0 |
2005-02-25 00:01:15 |
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.dbname = master
sybase-ase.getLogins.fullname = (null)
sybase-ase.getLogins.language = (null)
sybase-ase.getLogins.totcpu = 0
sybase-ase.getLogins.totio = 0
sybase-ase.getLogins.pwdate = 2005-02-24 23:59:14
|
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, suid, dbname, fullname, language, totcpu,
totio, pwdate from master.dbo.syslogins
where name = '${name}' and suid = '${suid}'
]]>
</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>
<Column id="sybase-ase.getLogins.Name" index="1">
<Column id="sybase-ase.getLogins.suid" index="2">
</Output>
|
The Output
element can also be used to alter the
structure of columns in the result set by adding, renaming or
removing columns.
<Output>
<Column modelaction="add" index="THIS_IS_A_NEW_COLUMN" value="Rattle and Hum">
<Column modelaction="rename" index="ADDR" name="ADDRESS">
<Column modelaction="rename" index="2" name="PHONE">
<Column modelaction="drop" index="MOBILE_PHONE">
<Column modelaction="drop" index="4">
</Output>
|
(The rename and drop actions accepts either the name of the column or
index number starting from the left at index 1).
- The add
operation adds a new column to all rows. The value attribute
accepts variables using the ${...}
syntax.
- The rename
operation simply renames a column.
- The drop
operation drops the specified column.
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 (sa
and probe
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.
Note: 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.dbname})"
|
The example above results in the following labels:
sa (system)
probe (subsystemdb)
|
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:
- Catalog
- Table
- System Table
- View
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 getting row
count information via the select
count(*) SQL statement.
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.
<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, sa
and probe. 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 sa
or probe
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 Columns tab 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 first DataView
definition defines the top grid viewer and the command to get the
result set for it.
- The next DataView
is the nested
text viewer, specifying various input parameter for the viewer along
with the command to get the source for the trigger. The difference here
is that the input parameters for this command reference column names in
the top grid. Since this viewer is nested, it will automatically be
notified whenever an entry in the top grid is selected.
- The third nested DataView is presented as a tab next to the Source viewer, and
presents additional information about the selected trigger.
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:
- Script:
SELECT ALL
- Script: DROP
TABLE
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
Note: 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="<pixels>"/> |
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"/>
<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="<column-name>"/> |
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="<true/false>"/> |
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"/>
|
And here is a screenshot of the Constraint tab based on the previous definition.
Figure: The node-form viewer
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.
Note: information presented in the grid is obtained
automatically by the viewer via a traditional SELECT * FROM
<schema>.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"/>
|
And here is a screenshot of the Data tab based on the previous definition.
Figure: The table-data viewer
table-rowcount
The table-rowcount viewer shows the row count for a (table) object.
Note: The
row count is obtained automatically by the viewer via a traditional SELECT COUNT(*) FROM
<schema>.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:
- Adding
a DataView
Simply add the DataView
definition and it will be added
to the current list of DataView definitions
- Dropping
an existing DataView
Add the <DataView
type="xxx" action="drop">
to drop the dataview type named "xxx"
- Replacing
a DataView
Just add the DataView with the exact same type as in the
parent DataView. All the settings of the new DataView
will replace the old one
<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:
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 Tableinput.
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 Commandelement
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:
- type
this defines what object type the ActionGroup represents. This
attribute is valid only for top level action groups. An example
is the object of type Table,
the corresponding ActionGroup
will only be
displayed when the selected object is a Table.
- label
this attribute is required for nested action groups. This label is
displayed as the sub menu label for the nested action group. (The label
attribute have
no effect on top level action groups).
<Action>
The action element defines the action.
<Action id="oracle-table-drop"
icon="remove"
label="Drop Table..."
reload="true"
mode="execute"
resultsetaction="ask">
|
The attributes for an action are:
- id
the id for the action. The recommended syntax for the id is
"profileName-objectType-someGoodActionName"
- icon
specifies an optional icon that should be displayed next to
the label
in the menu
- label
the label for the action as it should appear in the menu in the action
dialog
- reload
specifies if the parent node (in the objects tree) should be reloaded
after successful execution. This is recommended for actions that change
the visual appearance of the object, such as remove, add or name change
- mode attribute,
can be set to any of these:
- execute
(default) - show the action dialog, process user input and execute the
final SQL within the scope of the action dialog
- script
show the action dialog, process user input and send the final SQL to
the SQL Commander
- script-immediate
will not show the action dialog but instead pass the final SQL directly
to the SQL Commander
- resultsetaction
attribute, is only valid in combination with mode="execute". It
can be set to any of:
- ask
if the final SQL
produced a result set, ask the user whether the result set
should be displayed in a window or copied as text to the SQL Commander
- show
if the final SQL produced a result set, show it
in a window
- script
if the final SQL produced a result set, copy it to the SQL
Commander.
- class
used to launch a custom class. The execute
attribute is
obsolete if this attribute is set
- classargs
optional attribute used to specify arguments to the action
hander
defined by the class attribute
<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"/>
|
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 and Values elements
(as well as in the Labels
element for list
and radio
styles). 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.
Referencing 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>
|
- The optional Default
element is used to define a default
value for the field. Variables, static text and Command elements can be used to define the default value.
- A text input is editable
by default. To make it read only just specify editable="false"
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 "***".
Note that the password in visible in plain text in the SQL Preview.
<Input label="Password" name="pw" style="password" editable="true"/>
|
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:
- All choices for a radio style are displayed on the screen
(better
overview of choices but suitable only for a limited number of choices)
- The args="vertical"
attribute can be specified for radio style to present the radio
choices vertically
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>
|
- This will create a check component with the label Cascade
Constraints
- Enabling the check box will
set the value of the variable
identified by name
(cascade) to the value of Value,
which is compact.
- If the check box is
unchecked, the variable value will be blank
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="Content" 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.
procedure-params
The procedure-params
style is a specialized style used primarily to manage parameters for
procedure and function object types.
<Input name="parameters" style="procedure-params"
args="procedure,defaultType=VARCHAR(20),parmOrder=direction|name|type,directions=IN|INOUT|OUT/>
|
This example doesn't specify the label attribute as we want the field
to extend the full width of the actions dialog. The procedure-params
style use the args
attribute extensively to customize the appearance and function of the
field. The following content in the args attribute is handled by the procedure-params
style:
- procedure
Defines what object type being handled: one of procedure or function
- defaultType=VARCHAR(20)
Defines the default data type when the user adds a new row in the list
- parmOrder=direction|name|type|default
Specifies the order of the parameters for each row as they will appear
in the final variable. The identifiers above are static but can be
ordered to comply with the desired order of the parameters
- directions=IN|INOUT|OUT
This lists the available values for the Direction column (if
it's present)
The resulting parameter list is created automatically by the control
and is available in the variable name specified in the example to be parameters.
<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:
- name
should specify the name of the new variable
- value
this should contain the expression that will be evaluated. The
expression is based on the OGNL toolkit provided by www.ognl.org. This is an
expression library that mimics most of what is being supported by Java.
Variables are referenced as #variableName.
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}.
Note: 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.
Note that the message text can be composed of HTML tags such
as <b>, <i>, <br>,
etc.
<Confirm>Really drop table ${table}?</Confirm>
|
<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>
|
- The Result
message will be displayed in a dialog after
successful execution.
- If the execution fails, a generic error dialog is displayed
and the Result
is not displayed.
<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:
- Which version of the database
it is
- The format of the database
URL
- The client environment i.e
Java versions, vendor, etc.
- User properties
- Database connection
properties
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:
- Conditional
processing when database connection is
established
<If>,
<ElseIf> and <Else> elements can be
specified almost everywhere in the profile.
- 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>
|
