Database Explorer UI Specification

Author: Filip Rachunek
$Revision: 1.1.1.1 $
$Date: 2009/10/29 16:49:53 $

Table of Contents

1. Introduction
1.1 Overview of Functionality
1.2 Context
1.3 References
1.4 Caveats/Structure
2. Database Explorer
2.1 Databases Node
2.1.1 Properties
2.1.2 Defaults
2.1.3 Actions
2.1.3.1 Enable/disable debug
2.2 Drivers Node
2.2.1 Properties
2.2.2 Actions
2.2.2.1 Add Driver
2.2.3 Driver Nodes Actions
2.2.3.1 Connect Using ...
2.2.3.2 Delete
2.3 Connection Node
2.3.1 Properties
2.3.2 Actions
2.3.2.1 Connect
2.3.2.2 Disconnect
2.3.2.3 Execute Command ...
2.3.2.4 Delete
2.4 Tables Node
2.4.1 Properties
2.4.2 Actions
2.4.2.1 Create Table ...
2.4.2.2 Recreate Table ...
2.4.2.3 Execute Command ...
2.4.2.4 Refresh
2.5 Views Node
2.5.1 Properties
2.5.2 Actions
2.5.2.1 Add View ...
2.5.2.2 Execute Command ...
2.5.2.3 Refresh
2.6 Procedures Node
2.6.1 Properties
2.6.2 Actions
2.6.2.1 Execute Command ...
2.6.2.2 Refresh
2.7 Concrete Table Node
2.7.1 Properties
2.7.2 Actions
2.7.2.1 Create Table ...
2.7.2.2 Add Column ...
2.7.2.3 Refresh
2.7.2.4 Grab Structure ...
2.7.2.5 Recreate Table ...
2.7.2.6 View Data ...
2.7.2.7 Execute Command ...
2.7.2.8 Delete
2.8 Foreign Keys Node
2.8.1 Properties
2.8.2 Actions
2.8.2.1 Execute Command ...
2.9 Indexes Node
2.9.1 Properties
2.9.2 Actions
2.9.2.1 Add Index ...
2.9.2.2 Execute Command ...
3. Settings
3.1Global Options
3.1.1 Connect SAMPLE database
3.1.2 Debug Mode
3.1.3 Fetch Limit
3.1.4 Fetch Step
4. System-Wide UI Impact

1. Introduction

1.1 Overview of Functionality

The Database Explorer provides on-line access to DMBS sources. It allows user to execute SQL commands, display the results and manage databases themselves by modifying their structure (adding and removing tables and columns, generating indexes, grabbing and recreating table structures, etc.). The module supports a lot of widely-used databases (Oracle, IBM DB2, Microsoft SQL Server, PointBase, Sybase, ...), the whole list can be found in Drivers Node section.

1.2 Context

The Database Explorer is a NetBeans module. It provides UI for registration of drivers and adaptors, maintains a list of on-line and off-line database connections and allow user to display and modify all database elements (tables, columns, indexes, keys, ...).

1.4 Caveats/Structure

Each Database structure represents set of elements which can be organized in hierarchical structure. Live JDBC connection should have this structure:

-Databases
    -Drivers
    -{Connection}*
        -Tables
            -{Table}*
                -Foreign keys
                    -{Foreign key}*
                        -{Column}*
                -Indexes
                    -{Index}*
                        -{Column}*
        -Views
            -{View}*
                -{Column}*
        -Procedures
            -{Procedure}*
                -{Parameter}

2. Database Explorer

The purpose of the Database Explorer is to manage the live database connections, browse and modify the schemas in these databases, and view the data in the tables and views. It will be also possible to send arbitrary SQL statements to the database and view their result.

Browsing of off-line database schemas is supported by the DB Schema module and is not part of this specification.

2.1 Databases Node

The "Databases" node is the top-level node of all database-related nodes in Runtime tab. Its hierarchical structure contains "Drivers" subnode to provide templates for connecting to known DBMS and connection nodes representing already created (connected and disconnected) JDBC connections.

2.1.1 Properties

No properties.

2.1.2 Defaults

Upon first IDE startup, the user is supplied with a connection node which enables to use the Pointbase database contained in the Sun Java System Application Server bundled with NetBeans. If this node is deleted by user, it can be re-created by "Connect Using ..." action applied on the Pointbase driver node in "Drivers" subtree.

2.1.3 Actions

2.1.3.1 Enable/disable debug

The user can turn on/off writing debug message into console. In this case it means that if a debug mode is enabled, DB Explorer shows all executed commands and their parameters.

2.2 Drivers Node

As mentioned above, the Drivers node is a child element of the main Databases node. It holds all drivers and allows the user to add a custom driver with all necessary attributes.

2.2.1 Properties

No properties.

2.2.2 Actions

2.2.2.1 Add Driver

The user can create a new driver node by right-clicking on the Drivers node, and selecting the Add Driver menu item.  The user is shown a simple dialog where he/she can type the driver name (which will be used as a title of new driver node), the driver class and the driver JAR files. Clicking OK on that dialog adds the driver node to the Drivers node.

2.2.3 Driver Nodes Actions

2.2.3.1 Connect Using ...

This action invokes a new connection dialog. The driver name is pre-filled and the user must only specify the database URL, username and password. There is also optional panel where user can specify a schema to load tables from.

2.2.3.2 Delete

The user can permanently delete the selecter driver node from Drivers subtree. Use this action carefully because it can be undone only by specifying all necessary parameters again in "Add Driver ..." dialog.

2.2.3.3 Customize

This action opens a dialog similar to the Add Driver dialog, in which the user can edit the driver properties (name, driver class and the list of driver JAR files).

2.3 Connection Node

2.3.1 Properties

The connection node property sheet contains four editable properties:

• Database URL - URL of this database connection
• Driver - JDBC driver class used for this connection
• Schema - a schema to load tables/views from
• User - user name in the database

There are also many non-editable properties which display database meta data (catalog separator, driver version, etc.).

2.3.2 Actions

2.3.2.1 Connect

This and the next action are enabled only on disconnected Connection node. The "Connect" action uses the selected connection node properties to re-establish previously closed connection. If "Remember password during this session" was not selected or the session was closed before closing the connection, a dialog with username and password textfields appears and the required values must be entered. After clicking "OK", the IDE tries to connect to DBMS. If the connection is established, connection node changes from "broken icon" to "normal icon", otherwise an error notification is thrown.

2.3.2.2 Disconnect

This action is enabled only on "connected" connection nodem closes the active connection and changes the node icon from "normal icon" to "broken icon".

2.3.2.3 Execute Command ...

The user can use the connection node to run any SQL command supported by the corresponding driver and see the results in a window that appears. The window contains a combo box to maintain previously used commands for faster re-execution.

2.3.2.4 Delete

The user can delete the selected connection node after invoking this action and clicking "Yes" in the confirmation dialog that appears. If this action is used on living connection, the connection is closed before the node is deleted. The action cannot be undone, only by opening a new connection with the same parameters.

2.4 Tables Node

2.4.1 Properties

No properties.

2.4.2 Actions

2.4.2.1 Create Table ...

This action can be used to create a new table in the database. It opens a dialog where the user must specify table name and at least one column with all required parameters (column name and data type, other parameters are optional). It is also possible to add more columns to the table, remove the selected ones or change the table owner (which is set by default to the schema used during the process of creating the database connection). After clicking the "OK" button, this table is created in the database and added to subnodes of Tables Node. If the information provided by the user is not sufficient (e.g. no table name is specified or no columns are used), an error message is shown, table is not created and the user is returned to "Create Table" dialog.

2.4.2.2 Recreate Table ...

The user can recreate a table from a disk using previously saved table structure (see 2.7.2.3 - Grab Structure). The command opens standard JFileChooser dialog where the user can search for table structure files, select one of them and open it. After clicking the "Open" button, the table is created in the database and added to subnodes of Tables Node.

2.4.2.3 Execute Command ...

See 2.3.2.3 for details.

2.4.2.4 Refresh ...

This action runs SQL command to obtain all available tables from the corresponding database and refreshes the list of subnodes under Tables Node.

2.5 Views Node

2.5.1 Properties

No properties.

2.5.2 Actions

2.5.2.1 Add View ...

The user can add a new view to the list of view nodes by using this command. It invokes a dialog where one must specify a view name and SQL expression to create the view. After clicking "OK" button, the entered SQL expression is executed and in case of no errors the new view is created in the database and the corresponding node is added to the Views subtree. Otherwise an error notification is displayed and the user is returned to the "Add View" dialog.

2.5.2.2 Execute Command ...

See 2.3.2.3 for details.

2.5.2.3 Refresh

This action runs SQL command to obtain all available views from the corresponding database and refreshes the list of subnodes under Views Node.

2.6 Procedures Node

2.6.1 Properties

No properties.

2.6.2 Actions

2.6.2.1 Execute Command ...

See 2.3.2.3 for details.

2.6.2.2 Refresh

This action runs SQL command to obtain all available procedures from the corresponding database and refreshes the list of subnodes under Procedures Node.

2.7 Concrete Table Node

The user can open the table node and browse a subtree of table columns. Each column has a set of read-only properties which display information about the column:

• Column size
• Data type
• Decimal digits
• Default value
• Name
• Notes
• Nulls allowed
• Position
• Type

2.7.1 Properties

Properties of the concrete table are not editable and only display information about the table:

• Catalog
• Column type
• Name
• Notes
• Schema

2.7.2 Actions

2.7.2.1 Create Table...

See 2.4.2.1 for details.

2.7.2.2 Add Column ...

The user can add new columns to the existing table by calling this action on the selected table node. The "Add Column" dialog is displayed and the user must specify all required values (name and type). There are also optional values to define a new column:

• Size - the column size
• Scale - the column scale (for numeric type columns)
• Deafult - the column default value to be used when "null" value is inserted
• Primary key - check if this column is a primary key
• Unique - check if the column values must be unique
• Null - check if null values are allowed for this column
• Index - check if this column will be added to a selected index
• Check - check if additional constraints will be applied

After clicking "OK" button, the SQL command is executed to create the column in the database. In case of no errors, the column is created and a new column node is added to the corresponding table subnodes. Otherwise an error notification is displayed and the user is returned to the "Add Column" dialog.

2.7.2.3 Refresh

This action runs SQL command to obtain all available columns from the corresponding database and refreshes the list of subnodes under this table node.

2.7.2.4 Grab Structure ...

The user can save the selected table structure into a file for later use (see 2.4.2.2. - Recreate Table). The default extension of table structure file is .grab.

2.7.2.5 Recreate Table ...

See 2.4.2.2 for details.

2.7.2.6 View Data ...

This command works like Execute Command but it also runs a SQL query to display all rows of all columns of the corresponding table and shows the result in the Execute Command window.

2.7.2.7 Execute Command ...

See 2.3.2.4 for details.

2.7.2.8 Delete

This action calls SQL command to delete the selected table from the database. If the command is successful, the table node is removed from the Tables subtree, otherwise an error message is displayed.

2.8 Foreign Keys Node

2.8.1 Properties

No properties.

2.8.2 Actions

2.8.2.1 Execute Command ...

See 2.3.2.4 for details.

2.9 Indexes Node

2.9.1 Properties

No properties.

2.9.2 Actions

2.9.2.1 Add Index ...

The user can add a new index for the corresponding table. Invoking this action shows a dialog where one must specify the index name, choose whether the index will be unique or not, and select one or more columns to apply the index on. Pressing "OK" button runs SQL command to generate the index in the database. If no error occurs, the index is created and added to Indexes subtree. Otherwise an error notification is displayed.

2.9.2.2 Execute Command ...

See 2.3.2.4 for details.

3. Settings

The user can set several global database options which can be modified in Tools -> Options -> IDE Configuration - Server and External Tool Settings -> Database Explorer property sheet.

3.1 Global Options

3.1.1 Connect SAMPLE database

If this property is set to true (default value), IDE establishes a connection to Pointbase's SAMPLE database during startup.

3.1.2 Debug Mode

True value of this option means that the debug mode is on and database commands will display their output in the console.

3.1.3 Fetch Limit

This property sets the initial number of fetched rows for a query. If this limit is exceeded, the user will be asked whether to continue fetching rows or not. Default value is 100.

3.1.4 Fetch Step

This property specifies the number of additional rows that will be fetched when the Fetch Limit is exceeded. Default value is 200.

4. System-Wide UI Impact

Database Explorer does not impact UI of any other module, it only affects the functionality of JDBC client, DB Schema and other modules depending on it.