[ Master documentation index ]
Introduction
The Database Objects tab is used to explore all of your databases and
to show detailed information about specific objects. The tab has the
following layout.
Figure: Database Objects tab
The 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 Details area displays
information about the currently selected object in the tree. This view
depends on what type of object is selected. (See the Tree Objects section for more information).
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
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
|
Toggle button that determines
whether the Filter 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 Frame
|
Request to display the details
view in a separate frame for the selected object.
|
Right click menu
The right click menu contains the following operations:
All of these operations (except Copy
Name) are represented in the main menu bar.
Tree Objects
The Database Objects tree is, as mentioned earlier the core of
DbVisualizer as it is used to define database connections and get
connected. It is also used to browse the objects that are available in
databases. The following sections explain how the tree is built and
what object types are in it.
What objects are
displayed in the tree?
DbVisualizer supports a wide range of database products 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. DbVisualizer displays database
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, procedures, etc and
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 return 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. An example is
Oracle which supports objects such as triggers, users, table locks,
table space info, PL/SQL source code, etc. Getting information about
these objects is not supported by JDBC and the result is that
DbVisualizer cannot display them in the database objects tree.
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: The Database Objects Connections object
(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
operation. You make a database connection "Connect All" aware in the Properties for the Database
Connection.
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 Edit->Cut operation in the main
menu. This will remove the object from the tree and keep a copy of the
object on the clipboard. It will stay on the clipboard until the next
copy or cut operation is performed or until DbVisualizer is closed. To
copy a database connection select Edit->Copy
and then Edit->Paste.
Database Connection
details tabs
The following section briefly explains the tabs in the objects detail
view for a database connection.
Tab
|
Description
|
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.
|
Table
Types
|
The table types tab lists the types of tables that the database
supports. DbVisualizer use these types to group tables in the database
objects tree.
Note: Some drivers
report, for
example synonym as being a table type when it is something totally
different in the database. DbVisualizer only knows about tables and
relies on the information returned by the driver. |
Tables
|
The tables tab lists all of the
tables that are accessible in the
database. Some databases and/or drivers may not allow the listing of all
tables in the database so the list may in some situations not represent
the complete picture. |
References
|
Same as the tables tab except
that a reference graph is displayed for all tables based on any
referential integrity definitions. |
Catalog (aka
Database) object
The Catalog object is the
generic JDBC term for a Database
in Sybase, PostgreSQL, SQL Server and MySQL. It groups all objects for
a logical database. The detailed 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
|
Columns
|
This tab lists type information
about all columns in the table
|
Data
|
Read more in Data tab
|
#
Rows
|
Lists the table row count
|
Primary
Key
|
Shows the primary key
|
Indexes
|
Lists all
indexes for the table
|
Privileges
|
Displays
any privileges for the table
|
Row Id
|
Displays
the optimal set of columns that uniquely identifies a row
|
Versioned
|
Displays
columns that are automatically updated when any value in a row is
updated
|
References
|
Read more in References tab
|
Data tab
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 in the previous figure.
Tab
|
SQL Example
|
Set
Filter for Selection
|
ENAME
= 'WARD' and
JOB = 'SALESMAN'
|
select
* where
|
select
*
from SCOTT.EMP
where ENAME = 'WARD'
and JOB = 'SALESMAN'
|
insert
into
|
insert
into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (,'','',,'',,,)
|
insert
COPY into
|
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)
|
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'
|
delete where
|
delete from SCOTT.EMP
where ENAME = 'WARD'
and JOB =
'SALESMAN'
|
Drop Table
|
drop table SCOTT.EMP
|
Filtering
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).
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 tab
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
Procedure/Function object
The procedure/function object is probably the simplest since it shows
the name of the procedure or function in the tree, and in the detail
view lists the parameters that are used when calling it.
Figure: The function (and procedure) object
The detail view shows a list of column names for the function.
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
Filtering
The Filtering setup is located below the database objects tree. The
visible state of this pane can be controlled using the database objects
tool bar. Filtering is useful to limit the number of objects that
will appear in the tree. Individual filters can be defined on the
Catalog, Schema and Table levels.
Tree filters are managed per database connection object.
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.
Copyright © 2004 Minq Software AB. All rights reserved.
