[ Master documentation index ]
Introduction
The Database Objects Tree in conjunction with the Object View tab is used to explore all of your databases and
to show detailed information about selected objects.
Figure: Database Objects tab
The Database Objects Tree to the left
is the place to define new database connection objects and establish
connections. Once connected click the database connection object
and explore the child objects that are available. The right Object View area displays
when selected information about the currently selected object in the tree.
The tab tool bar buttons are
used to perform various operations on the tree and the objects in it.
The Tree Filter settings are
used to control by name what objects are displayed in the
tree. It is handy in order to limit the number of objects.
All object views except the table Data editing facility are used to
browse the database. This means that DbVisualizer Personal can be used
to browse the source for functions, procedures, triggers, etc but it
does not allow manipulation of such other then using the SQL Commander
to perform any changes.
Tip: The Database Objects Tree
is always visible to the left. If the currently selected main tab is
the SQL Commander then you can double click on an object in the tree to
automatically switch to the Object View tab.
Database Profiles
Since DbVisualizer Personal 4.1 there are a collection of new database
specific objects represented both in the objects tree and in the
various objects views. The definition of what is shown is defined in a database profile
and DbVisualizer Personal currently supports specialized profiles for the following databases:
- Oracle
- DB2
- Sybase ASE
- SQL Server
- MySQL
- PostgreSQL
- Informix
The information displayed is specifically per each of these databases
and the following document will cover Oracle as an example. For
databases where no specialized database profile exist or when using
DbVisualizer Free, a generic database profile is used. It displays
basic information about catalogs (aka databases), schemas, tables and
procedures.
Objects tree for Oracle, DB2, SQL Server, Informix, Sybase ASE, PostgreSQL and MySQL
The following shows what each database profile displays in the objects tree.
Oracle
|
DB2
|
SQL Server
|
|
|
|
Informix
|
Sybase ASE
|
PostgreSQL
|
|
|
|
MySQL
|
|
Figure: Overview of what objects each supported database profile displays
The generic database profile when used for an Oracle connection look as follows:
Figure: The generic database profile when applied to an Oracle database connection
The appearance of the generic database profile may include schema objects and/or catalog objects depending on whether the actual database supports these objects or not. The Procedures object always appear in the tree independent on whether the database connection supports procedures or not.
Note: The generic database profile is always used in DbVisualizer Free.
Read more about the generic tree objects in the Generic Database Profile section.
Database Objects Tree
Tab tool bar
operations
The Database Objects tool bar buttons are used to do tree related
operations. These are individually enabled or disabled based on what
object is currently selected.
Description of the buttons from the left:
Tool
bar button
|
Description
|
Reload
|
Reloads the currently selected
object by asking the JDBC driver to fetch information for the object
from the database. This is useful if new objects have been created or
removed.
|
Toggle
display of Filter setup
|
Is a toggle button that determines
whether the Filter management pane will be displayed below the tree or not.
|
Create
Database Connection
|
Adds a new Database Connection
object in the tree. The location of the new object is determined based
on the current selection. If no selection then the new object added
ti the end of the list.
|
Create
Folder
|
Creates a new folder object.
|
Show
in Window
|
Request to display the details
view in a separate window for the selected object.
|
Right click menu operations
The right click menu contains the following operations:
Figure: The tree right click menu
Filtering
The Filtering setup is located below the database objects tree. The
visible state of this pane can be controlled using the filter button in the database objects
tool bar. Filtering is useful to limit the number of objects that
will appear in the tree.
Tree filters are managed per database connection object. What can be
filtered is defined per database profile. The generic database profile
supports filtering on catalog, schema and table names.
The unfiltered schema objects for an Oracle connection.
|
The same objects but now filtered based on all schema names starting with "O" or "S".
|
Filter defined as all names that do not start with "O" and "S".
|
|
|
|
Figure: Examples of tree filter settings
An active filter for a database
connection is represented by the funnel icon just before the database
connection name. The active state for a filter is defined using the Active box in the name filter pane. A filter can only be activated if there are any filters defined.
Up to 5 filters can be defined per catalog, schema or the table
objects. Removing a filter definition always removes the last
definition in the list.
Tip: It is often desired to
list only the default schema or catalog (database) in the database
objects tree. This can be accomplished using the filtering
functionality but the recommended place to do this is in the properties
tab for the database connection. Please read more about the Show only default Database or Schema in Tool Properties document.
Show Table Row Count
The Show Table Row Count setting below the database objects tree
defines whether the number of rows for table objects will be listed
after the name of the table.
Note: Enabling this property results in a performance degradation.
Standard Tree Objects
There
are a few objects that always appear in the tree independent of what
edition of DbVisualizer and database profile that is used. The most
important object is the Database Connection. It is used to setup
and establish a database connection. The following sections explains
these standard tree objects.
Connections
object
The Connections object is the root object in the tree and acts as a holder for all database
connections and folders. The purpose of this is that when selected it
displays an overview of all database connections in the details view.
Here you can see the basic settings and states for your database
connections. Read more about it in the Load JDBC
Driver and Get Connected document.
Figure: Connections object
Database Connection object
The Database Connection object
is the root object for a connection. Before exploring or accessing a
database you need to establish the connection. Create a new database
connection using the Database->Add Database Connection
main menu choice and the following appear.
Figure: Add database connection
(Detailed information on how to establish a connection is provided in the Load JDBC
Driver and Get Connected document).
Tip 1: Once a database
connection
has been setup properly then you just need to double click on the
object to establish the connection.
Tip 2: The Database->Connect All
main menu
choice is used to connect all enabled database connections with a
single click. You make a database connection "Connect All" -aware in the
Properties for the Database
Connection or in the Connections overview.
Connection Alias
The name of the database connection object as it appears in the tree is
by default the URL of the connection. The Connection Alias can be used
to override this name to something more descriptive and shorter. Either
enter the new name in the Alias field in the Connection sub tab
or click on the name in the tree and start editing the name.
Default databases and
schemas
The (default)
indicator in the
name of a database or schema in the tree indicates that it is the
default database or schema. The default is determined by whether the
database was supplied in the URL during connect. A default schema is
the same as the schema in Oracle that the user logged in as.
Figure: The (default) indicator for catalog (aka database) and schema objects
Tip: The
Properties sub tab in the connection settings can be used to specify
that only default databases or schemas will be visible in the tree.
Remove and copy
database connection objects
To remove a database connection then select the Database->Remove Database Connection operation in the main
menu. To
copy a database connection select Database->Duplicate Database Connection.
Database Connection
details tabs
The following section briefly explains the tabs in the objects view for a database connection.
Tab
|
Description
|
Connection
|
This tab is always enabled and
is used to setup the details for a database connection. This is also
the place to control the connection state.
|
Database
Info
|
When connected, the database info tab shows various information supplied
by the driver. Much of this info is low level even though some may be
useful. |
Data
Types
|
The data types tab lists all
data types supported by the database.
|
Search
|
The
search tab is used to search among the objects in the tree. Search
operates on the content in the tree based on if there are any filter
defined or if any other setting has been set that effects the content
of the tree. See next section for more information about search.
|
Search
The Search
tab is used to search among the objects in the tree by object name.
This result will depend on if there are any tree filter defined or if
any other property has been set that affects the content of the tree.
The search operation is case insensitive.
Figure: The Search tab
Search by specifying the name of the object or part of the name and press the Search button. The search operation can be stopped using the standard Stop button in the main tool bar. The Show Object Path
check box is used to define whether the complete path for each found
object should be displayed in the result or not. This path is the same
as if navigating to each object manually in the objects tree.
Note: The search may take some
time to perform the first time since all objects that are defined in
the actual database profile are examined.
Tip: Detailed information of a
specific object can be examined by double clicking on a row. This will
display all information about the object in a separate window.
Folder object
The folder object is used to organize and group database connections.
It allows child folder objects in an unlimited hierarchy. You can
either use the View->Move Up/Down
main menu choices to organize the folders (and database connections) in
the tree, or you can also use drag and drop to move things around.
Figure: The database objects tree and the folder object type
Object View Types
The object views in the right area of the Database Objects tab shows
detailed information about the selected tree object. The object view
may contain several object view tabs depending on the current database
profile. There are also several representations of a view to better
illustrate the information. The following sections explains each of
these visual presentation forms.
Grid
The grid view is the most common one as it displays the data in a standard grid style.
Figure: The Grid view
Form
The form view extends the grid view by a form below the grid. Click on
a row in the grid and the information is displayed in the form.
Figure: The Form view
If there is only one row in the result will no grid appear but only the form.
Source
The source view is typically used to show the source for functions,
procedures, triggers, etc. It is based on a read only editor with SQL
syntax coloring. The sub tool bar buttons from the left:
- Export data to file
- Wrap long lines
- Copy the data to SQL Commander
Figure: The Source view
Table Row Count
The row count view is really simple as it only shows the number of rows in the selected object.
Table Data
The Data tab is used to browse
the data in the table and to do various data related operations. This
view is based on the generic
grid but adds a few more visual components to limit the max number
of rows, the width of text columns and the collection of data tab
specific operations in the right click menu. In addition it is also
possible to set a filter that will ensure that only the rows that match
the filter will be displayed. The data tab is the place to do edits in
DbVisualizer Personal.
Figure: The Data tab for Table objects
Right click menu
The right click menu in the data tab grid menu adds some operations
into the standard right click menu. These are primarily used to create
SQL statements based on the current selection.
Choosing any of these will create the appropriate SQL and then switch
the view to the SQL Commander tab. These
operations are used to edit table data in the
DbVisualizer Free edition since the inline and form based editors are
specifically for DbVisualizer Personal. (Information about the standard
right click menu operations are available in the Getting
Started and General Overview document).
The generated SQL can contain either static values as they appear in
the grid or DbVisualizer variables.
A variable is essentially used as a place holder for a value in an SQL
statement. Once the statement is executed DbVisualizer will locate all
variables and present them in a dialog. The values for the variables
can then be entered or modified and DbVisualizer will in the final SQL
replace the variable place holders with the new values. Variables can
be used in any SQL statement and DbVisualizer relies heavily on them.
(Read more about variables in the Executing SQL
statements in the SQL Commander document).
The use of variables in the SQL statements generated by the SQL
operations in the right click menu depends on the Table Data->Include Variables in
SQL
setting in Tool Properties.
This setting is by default true (include variables) and will result in
variables being used in the statement. Disabling the property will
result in static SQL in the generated statement.
Here follows an example with the Include
Variables in SQL setting enabled and then disabled. The SQL is
generated when the select * where
operation is selected based on the selection in the previous figure.
Include
Variables in SQL is enabled
|
select *
from SCOTT.EMP
where ENAME = $$ENAME (where)||WARD||String||where ds=10 dt=VARCHAR nullable $$
and JOB = $$JOB (where)||SALESMAN||String||where ds=9 dt=VARCHAR nullable $$
|
Include Variables in SQL is disabled
|
select *
from SCOTT.EMP
where ENAME = 'WARD'
and JOB = 'SALESMAN'
|
The following lists the generated SQL for each of the operations
based on the selection of ENAME = WARD and JOB = SALESMAN.
Operation
|
SQL Example
|
Set
Filter for Selection
|
ENAME = 'WARD' and
JOB = 'SALESMAN'
|
Script: SELECT ALL
|
select *
from SCOTT.EMP
|
Script: SELECT WHERE
|
select *
from SCOTT.EMP
where ENAME = 'WARD'
and JOB = 'SALESMAN'
|
Script: INSERT INTO TABLE
|
insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (,'','',,'',,,)
|
Script: INSERT COPY INTO TABLE
|
insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698,
'1981-02-22 00:00:00.0', 1250, 500, 30)
|
Script: UPDATE WHERE
|
update SCOTT.EMP
set EMPNO = 7521,
ENAME = 'WARD',
JOB = 'SALESMAN',
MGR = 7698,
HIREDATE = '1981-02-22 00:00:00.0',
SAL = 1250,
COMM = 500,
DEPTNO = 30
where ENAME = 'WARD'
and JOB = 'SALESMAN'
|
Script: DELETE WHERE
|
delete from SCOTT.EMP
where ENAME = 'WARD'
and JOB = 'SALESMAN'
|
Script: DROP TABLE
|
drop table SCOTT.EMP
|
Where Filter
The filter capability in the Data tab is used to form the where clause
that will limit the number of rows in the grid.
Figure: The Data tab filter
The filter area is composed of two parts. The upper one is used to
define the where clause for a single column. The available columns and
operators are selected from two lists. The value of the column is
specified in a text field. You can use Ctrl-Enter
while editing the value to force a reload of the grid based on that
single filter. The lower part displays the complete filter and the
buttons are used to control whether the newly entered filter will be AND'ed or OR'ed with the complete filter. The
buttons change appearance based on whether there is any filter or not.
While in the complete filter you can use Ctrl-Enter to force a reload based
on the complete filter. The right click menu lists the last 20 filters
that have been applied to the grid.
Figure: The filter history right click menu
To reset the use of the filter select the Reload operation in the data tab
tool bar.
(The visible
state of the filter pane is controlled using the Filter toggle button
in the data tab tool bar).
Quick Filter
The quick filter acts on the data that is already in the grid as opposed of a where filter
which is used to limit the number of rows fetched from the database.
Quick filter is convenient as it is used to quickly list only those
rows that match the entered search string.
The following figure shows data that matches the search string "d". Matching cells are highlighted.
Figure: The filter history right click menu
Entering successive characters will narrow the result even further as in the following figure.
Figure: The filter history right click menu
When the Instant Filtering
control is enabled then is the grid filtered while entering new
characters. Having a lot of rows in the grid may slow down the search
if having Instant Filtering enabled. If it is disabled then you must
press the Filter button in order to apply the filter.
Monitor row count
Read more about the Monitor Row Count
and Monitor Row Count Difference
in Monitor and
Charts.
Editing
Read about data editing in Edit
Table Data
References
The references tab is used to
visualize the references from the table and what tables reference it. Use the sub tabs at the bottom of the display to show
either view. The following shows the references from the table.
Figure: The references graph showing imported keys for a table
The following shows the references to the table.
Figure: The references graph showing exported keys for a table
Generic Database Profile
DbVisualizer supports a wide range of databases and since the
nature of these and what they support is different from vendor to
vendor so will the appearance and the structure of the tree below the
database
connection objects look different. The generic database profile and so
DbVisualizer Free displays objects based on what JDBC offers in terms
of database objects (aka meta
data information). DbVisualizer does this by simply asking the actual
JDBC driver for all schemas, catalogs, tables and procedures. It then
builds the tree based on what it gets.
The advantage of using JDBC to get meta data about the database is that
it's the responsibility
of the driver to perform the operations in order to get the
requested information. The drawback of letting the driver do this is
that JDBC does not offer that much support for getting meta data
information about the objects in a database i.e. the object types that
are presented in the tree are sufficient for most database while there
are obvious objects that are missing for some databases. The solution is simply to upgrade to the DbVisualizer Personal edition.
Catalog (aka
Database) object
The Catalog object is the
generic JDBC term for a Database
in for example Sybase, PostgreSQL, SQL Server and MySQL. It groups all objects for
a logical database. The object view for a catalog is a pane with two
tabs, Tables and References. The tables tab lists all
the tables that are located in the catalog while references shows the
exact same list of tables but instead as a referential integrity graph.
Figure: The view for Catalog objects
The child objects shown for a catalog depends on
the capabilities of the JDBC driver. Normally you will see a list of
the supported table types that groups the tables of these types. The
number within parentheses is the number of tables. The example
shows a MySQL database. The driver reports that it can handle the table
types, TABLE and LOCAL TEMPORARY. (These table types
are the same as those listed in the Table
Types tab when selecting a database connection object.
Tip 1: You can double click on
a catalog object to display the detail view in a separate window.
Tip 2: Select one or several
rows (cells) in the tables grid and then choose Database->Build Select Script to
create a select script for the selected tables.
Schema object
The Schema object is organized
in the same way as the Catalog objects. There is in fact no difference except that the
schema objects are in another level in the tree and represented by
another icon.
The following screen shot shows the information for the selected schema
with the Reference tab selected.
Figure: The view for Schema objects
Table type object
The Table Type object has been
briefly explained earlier. The name and the number of table type
objects are determined by the driver as DbVisualizer asks for the
supported table types. When DbVisualizer retrieves all tables it checks
each table's type and puts them into the matching table type object. The
reason is simply to make the tree easier to browse.
Figure: Example of table type objects for PostgreSQL
Note: Even though the figure above lists objects as INDEX, SEQUENCE, VIEW, etc are all treated as tables by DbVisualizer.
Table object
The Table object is probably
the most frequently accessed object in the tree as when selected it shows not only a
lot of information about the table but also the data in it. This is
also the place where data edits are performed.
Figure: The view for Table objects
The detailed view for table objects displays
Tab
|
Description
|
Info
|
Brief information about the table object
|
Columns
|
This tab lists type information
about all columns in the table
|
Data
|
Read more in Data tab
|
Row Count
|
Lists the table row count
|
Primary
Key
|
Shows the primary key
|
Indexes
|
Lists all
indexes for the table
|
Table Privileges
|
Displays
any privileges for the table
|
Row Id
|
Displays
the optimal set of columns that uniquely identifies a row
|
References
|
Read more in References tab
|
Data tab
Read more about the Data tab in the Table Data section.
References tab
Read more about the References tab in the References section.
Procedure object
The procedure object is probably the simplest since it shows
the name of the procedure or function in the tree, and in the object view lists the parameters that are used when calling it.
Figure: The procedure object
The object view shows a list of column names for the selected procedure.
Specialized Database Profiles
Specialized database profiles are
available for Oracle, Sybase, SQL Server, Informix, DB2, MySQL and
PostgreSQL. Since each of these databases supports different types will
the database objects tree look different. The structure and
organization of a database profile is also something that may impact
the layout of the tree even though the provided ones are similar in
their structure. There are two groups of objects in the the tree:
User objects are for example, tables, views, triggers, functions, etc.
while DBA objects most probably requires certain privileges in the
database in order to access them. DbVisualizer organizes all DBA
objects in the DBA Views tree object. If privileges are not sufficient to access a DBA object may this result in an error.
Figure: The DBA Views tree object
Note: Database profiles are
defined in XML and it is quite easy to extend and modify them. Read more in Plug-in Framework.
Copyright © 2006 Onseven Software AB. All rights reserved.
