[ Master documentation index ]
Introduction
The Database Objects Tree
is used to explore databases and browse details about objects.
Which objects that may be explored and which 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 Object View area to
the right 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 comes in handy when you have many schemas or tables in your
database and want to limit the number of visible objects.
For some object types, there are actions
(small dialogs for performing a task) for common operations, such as
creating, altering, and dropping database object. Which actions are
available depends on the database you are connected to and the database
profile used for the connection. More about this in sections below.
Tip 1: The
Database Objects Tree
is always visible to the left. If the currently selected main tab is
the SQL Commander, 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 to the SQL Commander
editor.
Create
a Database Connection
There
are a few objects that always appear in the tree independent of the
edition of DbVisualizer and the database profile in use. 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
We recommend that you always use the connection wizard when you create
a new
database connection, as it hides the complexity of loading drivers and
the 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, you just need to double click on the
object to establish the connection.
Tip 2: You
can use the Database->Connect
All
main menu
choice 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 "Database Connection". The Connection Alias
can be used
to provide a name that is more descriptive. 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. The default is determined when you
connect to the database.
Figure: The (default)
indicator for database and schema objects
Tip: In the
Connection Properties you can define that only the default database or
schema should be visible in the tree.
Remove
and copy
database connection objects
To remove a database connection, select the Database->Remove Database
Connection
operation in the main
menu. You can copy a database connection with Database->Duplicate
Database Connection.
Database
Connection
detailed information
The following section describes the tabs in the Objects View for a
database connection briefly.
Tab
|
Description
|
Connection
|
This tab is always
enabled and
is used to setup the details for a database connection. You can also
connect, disconnect and reconnect using the buttons in this tab.
|
Database
Info
|
When connected, the
Database Info tab shows various information supplied
by the driver. Much of this information is low level, even
though some of it 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. See the next section for more
information about search.
|
Search
The Search
tab is used to search among the objects in the tree by object name.
Note that if you have tree filters or any other property that limits
the content of the tree enabled, the search is performed only for those
objects that match the filters.
The types of objects that are searchable depends on the database you
are connected to. For instance, columns are included in the tree for
some databases but not for others.
Figure: The Search tab
Search by specifying the name of the object, or name pattern,
and press the Search
button. You can use asterisk (*) as a wildcard in a
pattern, or you can use a regular expression pattern if you enable it
by checking the Regular Expression checkbox. You can also specify where
in the tree to start the search, and whether to do a case sensitive
search.
You can search operation with Stop
button in the grid toolbar. Use the Show
Object Path toolbar toggle button to include or exclude a
column for the complete path for each found
object in the grid. This path is the same
as if navigating to each object manually in the objects tree. Other
grid toolbar buttons let you export and print the search result grid.
Note: The
search may take some
time to perform the first time since all objects defined in
the actual database profile are examined.
Tip: Double
click on a row to see detailed information about a
specific object. This will
display all information about the object in a separate window.
Organizing
Database Connections in Folders
If you work with many database connections, you can use folder object
to organize and group them in the tree. Folder objects can have child
folder objects in an unlimited hierarchy. Use the Database->Create Folder
and Database->Remove
Folder menu choices to create and remove folder objects.
You can use the Database->Move
Up/Down
main menu choices to move the folders (and database connections) in
the tree, or you can just drag and drop to the nodes to a new location.
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. When selected, it
displays an overview of all database connections in the Object Details
view.
Here you can see the basic settings and states for your database
connections. For more information, see the Load
JDBC
Driver and Get Connected chapter.
Figure: Connections object
Database
Objects Tree
Standard
Actions
The Database Objects toolbar buttons are used to do tree related
operations. These are individually enabled or disabled based on the
currently selected
object.
Figure: Objects tree toolbar
Description of the buttons from the left:
Tool
bar button
|
Description
|
Reload
|
Reloads the
currently selected
object by asking for new information about the object
from the database. This is useful if new objects have been created or
removed.
|
| Stop |
Stops the current tree operation, for instance
connecting to a database or expanding a node. |
Show/Hide
Tree Filter
|
Is a toggle button
that determines
whether the Filter management pane will be displayed below the tree.
|
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 node is selected, 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 for the selected object in a separate window.
|
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 offer related functionality. Read the following sections for
more information.
Create
Table
The Create Table action launches the Create Table feature. You
use it to create a table, optionally with a primary key, foreign keys
and other constraints. Read more about this feature in Create
and Alter Table.
Create
Index
The Create Index action launches the Create Index assistant dialog,
where you can select columns to include in a new index for a table. See
the Create
and Alter Table for more information.
Import
Table Data
Import Table Data launches a dialog where you can specify
a CSV
file to be imported into a table. Various configurations for 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
) |
For databases with DbVisualizer database
specific profiles, the Script
Object to SQL
Editor action menu also contains an entry for generating
the DDL for
Table and View
objects.
Script
Object to New SQL Editor
This is the same as Script Object to SQL Editor,
except that the SQL is copied to a new SQL editor instead
of to the current editor.
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 Activate Filters
checkbox in the Object 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: A
common requirement is 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 way is to do this with the Show only default Database or
Schema
property in the Properties
tab for the Database Connection object. You can read more about this in the Tool
Properties section.
Show
Table Row Count
The Database->Show/Hide
Table Row
Count menu choice decides whether the number of rows
for table objects will be listed
after the name of the table in the tree.
Note:
Enabling this property results in a performance degradation.
Database
Profiles
A Database
Profile is
the foundation for database specific
support in DbVisualizer. A database profile is, somewhat simplified, a
definition of which kind of information that is presented in the
database objects tree and in the various
object views for a specific database engine. In addition, the profile
defines the actions for the object
types
defined in the profile. DbVisualizer loads the matching database
profile when you connect to a database. If no matching profile is
found, or if you are running
DbVisualizer Free, DbVisualizer uses a Generic profile with just
the general database information and actions included.
Database
Specific Support
DbVisualizer Personal currently offer database specific support
(database profiles) for the following databases (click links for
details):
The specialized database profiles define different
object types, so the database objects tree may look
different
depending on which database you are connected to. 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
the profiles:
User objects are, for example, tables, views, triggers, and functions,
while DBA objects most often are objects that require administration
privileges in the
database in order to access them. DbVisualizer puts all DBA
objects under the DBA
Views
tree node. If you connect to a database using an account with
insufficient privileges to access a DBA
object, you may see error messages if you try to select nodes under the
DBA Views node. 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. The
nature of the databases and what they support differ from
vendor
to
vendor, so the appearance and structure of the tree
below the
Database Connection objects for different databases differ as well. The
generic database profile (the only profile available in DbVisualizer
Free) displays objects based on what JDBC
offers
in terms
of database information (aka metadata information). DbVisualizer asks
the
JDBC driver for all schemas, databases, tables and procedures, and then
builds the tree based on what the driver returns.
The advantage of using JDBC to get database metadata is that
it is a standard way to access the information, independent of the
database engine type; the JDBC driver layer hides the proprietary
details about where and how the information is really stored. The
drawback with using JDBC is
that JDBC doesn't offer access to all metadata
a database may hold. While the information presented by the generic
profile, with its reliance on JDBC, is sufficient for many tasks, a
database specific profile offers far more details as well as more
features. If you use DbVisualizer Free with one of the databases
supported by database specific profiles, you may want 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 database supports these objects. The Procedures
object always appear in the tree, regardless of if the database
connection supports procedures or not.
The following sections describe the objects provided by the generic
profile.
Catalog/Database
object
Catalog is
the term used in JDBC
and some database engines for a logical grouping of database objects.
Other database engines, e.g., Sybase, PostgreSQL, SQL Server and MySQL
use the term Database
for, more or less, the same purpose. Both terms are used in
interchangeably in DbVisualizer.
The Object View for a Catalog object in the generic profile 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 object depend on
the capabilities of the JDBC driver. Typically, a child object
represents a type of table that the driver use to categorize the tables
in a catalog, e.g., regular tables or system tables. For instance, the
example
in the figure above shows a MySQL database connection with catalog
objects
as its child object. The catalog child objects are TABLE and LOCAL TEMPORARY,
because these are the table types that the MySQL JDBC driver
supports (these table types
are the same as those listed in the Table
Types tab when selecting a database connection object).
For other databases, you may see child objects representing other table
types.
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 Script: SELECT ALL
to
create a select script for the selected tables, copied to the current
SQL Commander where it can be executed.
Schema
object
The generic profile Schema
object tree and view are organized
in the same way as for the Catalog objects. There is in fact no
difference
except that the
schema objects are in another level in the tree and is represented by a
different 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 generic profile Table
Type
object has been
briefly described 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, since its Object View
shows not only a
lot of information about the table but also the data the table holds.
This is
also the place where you can edit the table data.
Figure: The view for Table
objects
The Object View for a table object contains the following tabs:
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
|
Grants
|
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
|
| Navigator |
Read
more in Navigator tab |
Procedure
object
The Procedure object shows
the name of the procedure or function in the tree, and 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 View tab shows
detailed information about the selected tree object. The Object View
may contain several sub tabs, depending on the current database
profile and the type of the object selected in the tree. There may also
be several representations of the same information, providing different
views of the information. The following sections describe the different
views, or visual presentation forms, provided by DbVisualizer.
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 adding 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 of data, only the
form is displayed.
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 toolbar buttons from the left:
- Reload the data from the database
- Stop loading the data from the database
- Export the data to file
- Print the data
- Copy the data to SQL Commander
- Format the SQL
Figure: The Source view
Table
Row Count
The row count view is really simple: it only shows the number of rows
in the selected object.
Figure: The Row Count view
Table
Data
You use the Data
tab to browse
the data in the table and to do various data related operations. This
view is based on the generic
grid, but it 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, you can also
use a filter limit the data to the rows that match
the filter. The data tab is the place to do edits in
DbVisualizer Personal.
Figure: The Data tab for
Table objects
Right-click
menu
The Data tab grid right-click menu contains some operations in addition
to those the standard grid right-click menu. The additional operations
are primarily for creating
SQL statements based on the current selection.
Choosing any of these creates the appropriate SQL and then
switch
the view to the SQL Commander tab. You must use these
operations to edit table data in the
DbVisualizer Free edition. With the DbVisualizer Personal edition, you
can instead use inline and form based editing. (Information about the
standard
right click menu operations are available in the Getting
Started and General Overview document).
You can generate SQL with either static values as they appear in
the grid, or with DbVisualizer variables.
A variable is essentially a placeholder for a value in an SQL
statement. When the statement is executed, DbVisualizer locates all
variables and presents them in a dialog where you can enter or modify
values for the variables. DbVisualizer replaces the variable
placeholders with the new values before executing the statement.
Variables can
be used in any SQL statement and DbVisualizer relies on them heavily.
(Read more about variables in the SQL Commander
document).
Whether to use variables in the SQL statements generated by the
right-click menu SQL
operations depends on the Table Data->Include Variables in SQL
setting in Tool
Properties,
under the General tab. By default, variables are being used in
the
statement. If you disable the property, static values are
instead
used in the generated statement.
Here is 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 HR.COUNTRIES
where COUNTRY_NAME = $$COUNTRY_NAME (where)||Brazil||String||where
nullable ds=40 dt=VARCHAR $$
|
Include Variables in SQL is
disabled
|
select *
from HR.COUNTRIES
where COUNTRY_NAME = 'Brazil'
|
The following lists the generated SQL for each of the operations
based on the selection of COUNTRY_NAME = Brazil, with variables
disabled.
Operation
|
SQL Example
|
Script:
SELECT ALL
|
select *
from HR.COUNTRIES |
Script:
SELECT ALL WHERE
|
select *
from HR.COUNTRIES
where COUNTRY_NAME = 'Brazil' |
| Script:
SELECT ALL WITH FILTER |
select
*
from HR.COUNTRIES
where REGION_ID = 1 // If this is the filter, see below |
Script:
INSERT INTO TABLE
|
insert into
HR.COUNTRIES
(COUNTRY_ID, COUNTRY_NAME, REGION_ID)
values ('', '', ) |
Script:
INSERT COPY INTO TABLE
|
insert into
HR.COUNTRIES
(COUNTRY_ID, COUNTRY_NAME, REGION_ID)
values ('BR', 'Brazil', 2) |
Script:
UPDATE WHERE
|
update
HR.COUNTRIES
set COUNTRY_ID = 'BR',
COUNTRY_NAME = 'Brazil',
REGION_ID = 2
where COUNTRY_NAME = 'Brazil' |
Script:
DELETE WHERE
|
delete from
HR.COUNTRIES
where COUNTRY_NAME = 'Brazil' |
Where
Filter
The filter capability in the Data tab lets you limit the number of rows
in the grid, using the same syntax as for an SQL WHERE clause.
The Filter menu button in the grid toolbar contains all operations
related to using a filter.
Figure: Filter
menu
The top entries in the menu are previously used filters for the table,
if any. The checkbox is selected for the filter that is currently in
use. The filters are saved between DbVisualizer sessions, and you can
toggle between them by selecting them from the menu. You use the Use No Filter choice
to disable all filters for the table, and the Clear Filter List to
permanently remove all filters for the table.
To create a new filter, select Configure
Filter to launch the Filter Configuration dialog.
Figure: The Data tab Filter
Configuration dialog
The Filter Configuration dialog contains one Filter area and a Sort
area.
The Filter area is composed of two parts. The upper one is used to
define a condition for a single column. You can use the two lists to
select the column name and
an operator, and enter the value of the column in the 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 Sort area is similar to the Filter area. You can select column
names and sort order from the two lists, and click the Add button to
add the sort criteria for the single column to the complete criteria.
Click the Use
Filter button to apply the filter and save it, and close
the dialog by clicking the Close
button.
If you often need to tweak the filter conditions and want a more
compact user interface, you can use the inline filter view. Use the Show/Hide Inline Filter
choice in the Filter menu to toggle the visibility of the inline filter.
Figure: Data tab
with the Inline filter enabled
The inline filter is displayed above the grid. You can edit the
condition in the text field and use Ctrl-Enter
or click the Use Filter
button to apply the modified condition. Instead of manually typing column names in the field use the Ctrl-Space key binding to show a list of available columns.
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.
With a Quick filter, you can easily list only those
rows in the grid that match the entered search string.
The following figure shows data that matches the search string "d". Matching cells
are highlighted.
Figure: Using the Quick
Filter
Entering successive characters will narrow the result even further, as
in the following figure.
Figure: Refining the
filtering
The Quick Filter pull-down menu (click on
the down arrow next to the magnifying glass) lets you choose if the
filter should match cells in all columns or just one selected
column, case or case insensitive matching, and where in the
cell the value must
match.
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
The 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 for a Table object shows how the table references other tables
(e.g., Imported Keys) and how other tables reference the selected table
(i.e., Exported Keys), based on primary and foreign key declarations.
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
Navigator
The Navigator
tab provides an interactive way to navigate in data by
following primary key and foreign key references.
Figure: The Navigator tab
showing two navigation cases
The tab contains a graphic view showing navigation cases (paths through
the data) at the top and a data grid showing the data for the
navigation case selected in the graph. You navigate in the data by
selecting the row in the grid that holds the key value you want to
follow, e.g., a specific department in the example shown in the figure,
and then select a primary or foreign key relationship from the Related
Table list above the grid. This creates a new navigation case in the
graph and updates the grid with the corresponding data.
How to use the navigator is described in more detail in the Data Navigation
section.
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, click the
error to highlight the related row in the
source editor.
Note that the procedure editor currently only support Oracle database.
More information is can be found in the Procedure Editor
document.
Figure: The procedure editor for functions,
procedures, packages and package bodies
Copyright © 2007 Onseven Software AB. All rights reserved.
