[ Master documentation index ]
Introduction
The Database Objects Tree
is used to explore databases and browse details about objects.
What objects that may be explored and what object actions that exist is
database dependent.
Figure: Database Objects tab
The Database Objects Tree to the left
is the place to setup new database connections and establish
connections. Once connected expand the database connection object
and explore the objects available. The right Object View area displays
detailed information about the currently selected object in the tree.
The Filter setup pane below the tree is used to control what objects are displayed in the
tree. It is handy in order to limit the number of objects.
Object actions used to typically create, alter, drop, etc. may
exist for the objects in the tree. This is controlled by what database
being connected and what database profile is used. Check coming
sections for more information.
Tip 1: 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.
Tip 2: All object names in the tree can be dragged to any editable text fields including the SQL Commander editor.
Create Database Connection
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 which is used to setup
and establish a database connection. The other two objects are Folder and Connections Overview. The following sections describe these objects in more detail.
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 will appear.
Figure: Add database connection
It is always recommended to use the connection wizard when creating new
database connections as it hides the complexity loading drivers and
syntax of database URLs.
(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
Database Properties or in the Connections overview.
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 database and
schema
The (Default)
indicator after the
name of a database or schema in the tree indicates that it is the
default database or schema. This is determined per database at connect.
Figure: The (default) indicator for database and schema objects
Tip: In the Connection Properties you can define that only default database or schema should 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
detailed information
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 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.
Organizing Database Connections in Folders
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 nodes.
Figure: The database objects tree and the folder object type
Connections
overview
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 Objects Tree
Standard Actions
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.
Figure: Objects tree toolbar
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.
|
Show/Hide Tree Filter
|
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 is object added at 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.
|
The right click menu for an object and the Database main menu lists object specific actions. The following actions are always available for all objects:
Figure: Standard right click menu actions for all objects
Object Actions
An object in the objects tree may have object specific actions attached to it. These actions are accessible via any of:
- Right click menu in the objects tree
- Via the Database->Selected Object main menu
- Via the Actions menu button in the object view
Here is an example of the actions menu launched via the Actions menu button:
Figure: Object actions menu
Common Object Actions
There are a few actions that appear for some object types in all
database profiles. These are most often valid for plain table object
types and offers related functionality. Read the following sections for
more information.
Create Table
The create table action shows the Create Table assistant dialog. it is
used to setup the columns their characteristics and primary keys for a
new table. The final SQL that the assistant produce is then executed in
the SQL Commander. Read more about this feature in Create Table and Index Assistants.
Create Index
The create index action shows the Create Index assistant dialog. it is
used to setup columns for new table indexes. The final SQL that the assistant produce is then executed in
the SQL Commander. Read more about this feature in Create Table and Index Assistants.
Import Table Data
Import Table Data shows a dialog used to import a CSV file into the
actual table. Various configurations how the source file is organized
and data mapping are offered. Read more in Export, Import and Print.
Script Object to SQL Editor
Use this action to create pre-defined SQL statements based on the
source table and its columns. The created statement is copied to the
current SQL editor in the SQL Commander. Here are a couple of examples:
Script Object to SQL Editor -> Select
SELECT
COUNTRY_ID,
COUNTRY_NAME,
REGION_ID
FROM
HR.COUNTRIES
|
Script Object to SQL Editor -> Insert
INSERT
INTO
HR.COUNTRIES
(
COUNTRY_ID,
COUNTRY_NAME,
REGION_ID
)
VALUES
(
'',
'',
0
) |
The Script Object to SQL Editor is also used to generate the DDL for Table and View objects. (This is only supported in the database specific profiles).
Script Object to New SQL Editor
This is the same as Script Object to SQL Editor with the difference that the SQL is copied to a new SQL editor instead of current.
Objects Tree Filtering
The Filtering setup is activated via the Database->Show/Hide Tree Filter menu choice and the filter pane appear below the objects tree. 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 of database (catalog), schema, table and procedure 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 15 filters can be defined per object type.
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.
Database Profiles
A Database Profile
is the foundation in DbVisualizer used to express database specific
support. A database profile is briefly a definition of what information
should be presented in the database objects tree and in the various
object views. In addition it define actions for the object types
defined in the profile. DbVisualizer loads the matching database
profile at connect. If no matching profile is found or if running
DbVisualizer Free a Generic profile is loaded with rudimentary database support.
Database Specific Support
DbVisualizer Personal currently offer database specific support
(database profiles) for the following databases (click links for
details):
Since each of the specialized database profiles handles different object 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 root nodes in the majority of profiles:
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. This is an example of the DBA sub
tree.
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 the Plug-in Framework document.
Generic 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 DbVisualizer Free display objects based on what JDBC offers
in terms
of database objects (aka meta
data information). DbVisualizer does this simply by asking the actual
JDBC driver for all schemas, databases, 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 doesn't offer that much support for getting meta data
information about all 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.
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.
Database (Catalog) 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.
Object Views
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.
Figure: The Row Count view
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 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
DDL Viewer
DDL Viewer tabs appear only for Table and View objects and for databases that have specialized database profiles.
Figure: The DDL viewer for a table
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
Procedure Editor
The procedure editor is used to browse, edit and compile procedures,
functions, packages and package bodies. The procedure contains the
source editor and options to display parameter info and
compilation error list. If error(s) occur during compilation
then click the error and the related row in the source editor
will be highlighted.
More information is can be read in the Procedure Editor document.
Figure: The procedure editor for functions, procedures, packages and package bodies
Copyright © 2006 Onseven Software AB. All rights reserved.
