[ 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 briefly means
that it's rather
complex for a product such as DbVisualizer since each database must be
handled specifically. All existing database products do in addition
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 databases. Technically is a database
profile an XML document keeping all of the logic, structure and actions
easily mapped to the visual components in DbVisualizer. Another great
benefit separating the database specific logic from the implementation
of DbVisualizer is that anyone with some degree of domain knowledge may
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 what features in DbVisualizer that is
controlled by the database profile.
Figure:
What the database profile control 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 will show the object
view
(blue box) specifically for the selected object type. An object view
may have
several data views
(green) showing object information. DbVisualizer show
these as labeled tabs.
The green box
shows in this screen shot
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 for 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 Actionsbutton
in the object view will show a menu will all valid actions for the
selected object. These are 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 then it is used.
- If there is no matching
profile then the generic
profile will be used. (This is very basic profile and shows
only rudimentary
information about the objects in the database). This is the profile
used in the DbVisualizer Free edition.
A specific database profile can be manually set for a database
connection. This is defined in the database connection
properties. Manually choosing a profile requires that the
profile supports the actual database. If it doesn't then various errors
will be reported once the database objects tree is explored. (Whenever
the profile is changed you must re-connect the actual database
connection).
The name of the loaded profile is listed in the Connection
tab status bar when the connection has been established. Click the
profile link and the Database
Profile list will be displayed.
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 in order to get the
information for ObjectsTreeDef,
ObjectsViewDef
and optionally ObjectsActionDef
definitions are defined by the Commands
root element.
- All Actions
for an object is 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 then the generic
profile is
used. This profile uses the standard JDBC meta data calls in order to
obtain information about the structure and objects in the database. The
generic profile is not one XML file as database specific profiles 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 as
include statements i.e. each of the above files will be included in the
generic.xml file when loaded. The reason for this is that these files
can be
included and extended in a specialized profile. See later for more
information.
The XML structure used to represent the database profile is organized
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 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.21 $"
date="$Date: 2006/10/05 15:08:34 $"
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 name
attribute for the ObjectsTreeDef
and ObjectsViewDef
elements must be the same.
The first rows in the XML defines external dependencies and their
URI's. The DOCTYPE
identifier defines the DTD that is used to verify the XML with. The ENTITY
identifiers lists URI's for external references. In this case they
identify the generic-commands.xml
and generic-view.xml
files. They can
then be referred in the XML as &generic-commands;
and &generic-view;
and simply means that the related XML files will be
included in the final document once 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 then it is very convenient loading the DTD
in the editor as you then will 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.21 $"
date="$Date: 2006/10/05 15:08:34 $"
minver="5.0">
...
</DatabaseProfile>
|
The attributes specified for the DatabaseProfile
element will 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 keeps 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 (optionallty) 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 will be included in the XML i.e. all its
definitions
will be accessible from the ObjectsTreeDef,
ObjectsViewDef
and ObjectsActionDef.
If you don't plan to use any of the generic
command
then simply ignore this include statement.
<Command>
The Command element identifies the SQL associated
with
the command. The SQL should in most cases return a result set with
0 or several rows. (The exception is actions which not necessarily
need to return a result set). 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 easier to set unique prefixes
for all commands so that
they are not mixed with the commands in the generic-commands.xml
file.
Result
set
The result set for the previous query look 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 then each row in the result set will be
represented by a new
node in the tree.
If executed in the object view then it's the actual viewer component
that decides how the result will be presented. For more information how
a result set is used in the
ObjectsTreeDef
or ObjectsViewDef
then read the specific sections.
Another important difference between the database objects tree and
object view is that the tree is a hierarchical structure of objects
while object view presents information about a specific object. An
object that is inserted in the database objects tree is a 1..1 mapping
with 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 are 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 is as follows:
| label="${sybase-ase.getLogins.name}" |
<Input>
- Setting
command input
There are two types of Commands, with or 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 to the previous
example allowing 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 we introduced how
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
the variable ${sybase-ase.getProcesses.suid} means
that DbVisualizer will first look if the variable is found in the
current object. If it doesn't exist it will continue looking
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 will use the value
of it and stop searching.
<Output>
- Redefine command output
As mentioned earlier is a specific column value in a result set row
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>
|
Another option using the Output
element is to alter the
structure of columns in the result set by either 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 left at index 1).
- The add
operation is used to add 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 pre-defined 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 will be
presented and what 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 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 that has been expanded (sa
and probe
child objects) to illustrate how DataNode
objects look. 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 when either the tool tip for a tree node is displayed
and in the object view header. The type is also used to identify what
icon will be used to represent the object type.
There are no limitation on the number of levels in the ObjectsTreeDef.
A good rule is however to keep it simple, clean and intuitive.
The DataNode
definitions are the most important objects in the ObjectTreeDef.
These also defines what object tree filters are available for each
object type, if
overlay'ed icons should appear (and the criteria), etc. Read the next
sections for details.
<GroupNode>
- Static objects used for grouping
The GroupNode
element is used to represent 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 though 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 then these
will not appear. The result may be 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
have in this example the same attributes
as the GroupNode,
the type is however "Login"
instead of "Logins"
as for the GroupNode.
This difference is important once
the user click on either of the objects since the the Object View will
show the appropriate views based on object type. The DataNode definition
can
be seen as a template as the associated command will fetch rows of data
from the database and DbVisualizer will use 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 defines by the idref
attribute what command should be executed. The command in this case and
in the Result
set
section produced 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
composition of several variables. (It's even possible to specify static
text in the label):
label="${sybase-ase.getLogins.Name} (${sybase-ase.getLogins.dbname})"
|
Will result in following being displayed:
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 then show an overlay icon for the node
errorstate="condition"
- If condition is true then show an overlay icon for the node
stop-label-hot-equal="value" - The node will be a leaf if label don't match this value
or
variable
is-empty-output="continue/stop" - If result set is empty then use this to control whether child
GroupNode/DataNodes should be added anyway or ignored
|
The Command
definition in this example is basic 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 its 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 variable(s) specified in the Input elements will
be
searched based on the same 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 what data for a DataNode that are
allowed to use in filters. This filter functionality is commonly
refered 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 are one or several
columns that can be used to filter on.
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 for the filter as it will appear in the
object
type drop down box. The Column
element then define the index
which should be either a column name in the result set or an index
number representing the actual column. The name
attribute specifies the name of the column as it will appear 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.
DbVisualizer relies on some object types as these have special meaning.
Two examples are the Catalog
and Schema
object types. For DataNode
objects that you now will represent these types there must a SetVar identifying
them. The name attribute should then be set to "catalog" and "schema".
<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
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.
The rowcount
setting is optional and control whether the object supports getting row
count via the select
count(*) SQL statement. This setting is also used to identify
if the object is allowed for use in the Query Builder.
<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
for the selected object.
What 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 what object view will 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.
What is not obvious in the screenshot is the object types for these
objects. The Logins node is of type Logins
while the sub nodes are Login types.
The ObjectView
XML definitions shows the views for 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 have the following attributes
<!-- Include the generic-view -->
&generic-view;
<ObjectsViewDef id="Views" 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 will be included in the XML i.e. all its
definitions
will be accessible as is. An example is the ObjectView
definition in the generic-view.xml file for the Table
object type. It contains a lot of DataView elements
that identifies 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 briefly done by adding for example a extends="generic"
attribute in the ObjectsViewDef
element. Then by using
the exact same object type in the extended ObjectView
you will get this behavior. Read more about extending ObjectView's in the Extending ObjectView
section.
<ObjectView>
The ObjectView
element is identified by 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 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, what 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>
|
This definition will be presented in DbVisualizer as described in the introduction
of the ObjectsViewDef
section. These three data
view
elements have the viewer
attribute. It identifies how the data in the view will be presented.
See 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
being presented 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 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>
|
Screenshot of 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 offering 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
will be presented as a tab next to the Source viewer and
presents info 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 will appear in the right click menu in
the grid. The value for the menuItem
is the label for the item while the child Inputspecifies
the SQL command that will be produced for all selected rows when the
menu item is selected. The result of a custom menu item is that the
grid viewer will create 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 pre-defined 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 it to be an object. This is
needed for DbVisualizer to determine whether delimited identifiers
should be used and if identifiers should be qualified as defined
in connection properties for the actual 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 will be 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 then this viewer will fetch the
data
in the actual column for each row and present the combined data in the
text viewer.
Here is a sample 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> |
Screenshot of the previous definition.
Figure: The text viewer
Specify what column to
browse
The text viewer automatically picks the data in first column. This
behaviour 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 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 will format
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
Presents row(s) from
a result set in a form. If several rows are in the result then these
are presented in a list. Selecting one row from the list will present
all columns and data for that row in a form.
Here is a sample 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> |
Screenshot of the previous definition.
Figure: The form viewer
node-form
Presents all
data associated with the selected object (variables).
Here is a sample XML for the node-form viewer:
<DataView
type="Constraint" label="Constraint" viewer="node-form"/>
|
Screenshot of the previous definition.
Figure: The node-form viewer
table-refs
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 XML for the table-refs viewer:
<DataView
type="References" label="References" viewer="table-refs"/>
|
Screenshot of the previous definition.
Figure: The table-refs viewer
tables-refs
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 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>
|
Screenshot of the previous definition.
Figure: The tables-refs viewer
table-data
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 XML for the table-data viewer:
<DataView type="Data"
label="Data" viewer="table-data"/>
|
Screenshot of the previous definition.
Figure: The table-data viewer
table-rowcount
This 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 XML for the table-rowcount viewer:
<DataView
type="RowCount" label="Row Count" viewer="table-rowcount"/>
|
Screenshot of the previous definition.
Figure: The table-rowcount viewer
<Command>
Please read
the Command
section earlier as the capabilities here are the same.
<Message>
The Messageelement
is very simple as it defines a
message that will appear at the top of the viewer. The Message element
is used to explain what is 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 XML 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>
|
Screenshot of the previous definition.
Figure: The appearance of a Message in a viewer
Extending ObjectView
An
existing ObjectView
definition made 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 accomplish extensions the object type specified in
the ObjectView
type attribute must match the type in the
parent profile. Now you have the following options:
- 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 have clarified how to define what objects should
appear in the objects tree and what views will be displayed when
selecting an object in the tree. The ObjectsActionDef
section
in the profile defines what operations are available for the
object types defined in the ObjectTreeDef.
Object actions are very powerful as they offers an extensive number of
features used to define actions for almost
any type of object operation.
In DbVisualizer is the object type actions menu 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 listed
in the previous screenshot are expressed in the ObjectsActionDef
section.
The implementation for these actions are either expressed completely in
XML 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 actual database
connection. At the bottom there is a Show SQL
control that when enabled will show 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 Cancel
the action completely.
Variables
Variables
are used to reference
data for the object for which the
action was launched and 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:
Here follows 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. In the second New Table Name field
should the new name of the table be entered.
If the Show SQL
control is enabled you will see any edits in the dialog being directly
reflected in the final SQL Preview.
Figure:
The default action handler
The complete action definition for the previous Rename Table action
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) in should be
reloaded when
the action is executed.
The next block of elements are Input
fields defining the data for the action. As you can see in the sample
there is a variable ${catalog}
in the Default
element for the Database
input and the ${objectname}
variable in the Default
element for the Table
input. The values for these variables are fetched from the actual
object in the objects tree. Briefly are values for variables retrieved
by first checking if the variable is in the scope of the action dialog
i.e. another input field, then the actual object for which the action
was launched is checked, if the variable is not found the action then
asks all of the parent objects until it reach the root object in the
tree (Connections node). If a variable is not found its value will be (null).
In the previous sample XML will the value of ${catalog} be the
name of the database in which the table object is stored. The ${objectname} will
present the current name of the table (these variables are described in
the ObjectsTreeDef section).
The New Database
input field is a
list component and shows a list of databases based on the result set
of the specified SQL command. The Default
setting for the database will
be the same as 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 should list
the final SQL that will be executed by the action. The SQL is in
this sample combined with static SQL along 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 define in what order the actions will appear
in the
menu and where any separators should appear. ActionGroup
elements can be nested and will as so appear as sub menu(s).
<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 actual object is a Table.
- label
this attribute is required for nested action groups. This label will be
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 will be displayed next to
the label
in the menu
- label
the label for the action as it will 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 then a query will ask 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 then show it
in a window
- script
if the final SQL produced a result set then copy it to the SQL
Commander.
- class
used to launch a custom class. The execute
attribute is
obsolete if class 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 as it will appear in
the actions dialog. The label attribute is recommended and is presented
to the left of input field. If label is not specified then the input
field will occupy the complete width of the action dialog. All input
fields are editable by default and then requires the name attribute. This
should specify the identity of the variable in which the user input
will be stored.
A minimal definition of an input field is the following. It will show a
read only text field control labeled Table.
<Input label="Size" editable="false"/>
|
If changing the input field to be editable we also need to supply the name attribute with
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 with the input field and is displayed as a tool
tip 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 a 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
(also in Labels
element for list
and radio
styles). An alternative of embedding the SQL as in the previous example
is to refer 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 can be used here and Command (SQL)
expressions
- 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 deafault
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 then this should list all choices as they will
appear in the actions dialog. Consider these as being the labels shown
for 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 sub element to Values. The result
of the show databases
SQL will be presented in the list component.
To make the list editable then 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 is 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 and will 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 enable state
indicates that the Value
for the input will be set in
the final
variable. If the check box is disabled then the variable 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 then the variable will be blank
separator
(visual divider between input controls)
The separator
style is not really an input element and is rather used to visually
divide the fields in the in the actions dialog. If the label
attribute is specified then 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 an alternate label 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 costumize 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. Can be 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 identifers above are static but can be
ordered to comply with the wanted 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 list 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 then a blank value is being set in 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 will be displayed for 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 will show 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
probably 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 then
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 briefly means that a profile can adjust
its content based on conditions. A few examples:
- What 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 to adopt the profile for
different versions of the database (and/or JDBC driver). Up to
DbVisualizer 4.3.3 was a profile tested with a "minimum"
of a database version. Accessing for example an Oracle 8
database using the Oracle profile supplied with DbVisualizer works most
of the time but fails is some situations since it require at least
Oracle 9. Another advantage with 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 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 fail. To control what message
should appear conditions are used.
DbVisualizer determines what If elements should
be
executed in
what phase by the type
attribute. If this attribute has the
value type="runtime"
then it will be processed in the second
phase. If it is not specified or set to type="load"
if 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 briefly means that if the major version of the database being
accessed is less then or equal to 8 the first SQL will be used. If the
version is equal to 9 then the second SQL is used, the last SQL will be
used for all other version. The test attribute may contain conditions
that are AND'ed or OR'ed. 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 re-phrase 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 will be processed for all commands. If it is
part of a specific Command
it will be processed only
for that
command.
Current
limitations
- The SQL's
in the profile must be statements that
DbVisualizer can execute with JDBC. It can not contain any executables,
scripts or OS specific calls
- It is not possible to
specify conditions or compound commands i.e.
all needed to execute a command must be expressed in a single SQL
statement.
Copyright © 2006 Onseven Software AB. All rights reserved.
