Database Tools Specification

Author: Winston Prakash Initial Draft: Jul 17, 2009

Introduction

The aim of database tool in the studio is to support Database Connection to popular database servers and do operations via the connection.

Nomenclature

Database

Collection of tables, views, indexes etc and their data.

Database Schema

Schema (data dictionary) defines the structure of the tables, views, indexes and their relationship in a database. For example "Schema design" means designing the structure of a database, its tables and other entities. A Schema Diagram (Entity Relationship (ER) diagram) shows the pictorial view of a database structure.

Database Server

A database server is a network server that allows users to connect and access a database. A single database server can cater to multiple databases. Database server itself maintains the information about databases it serves in tables. Some servers, like MySQL, allows queries to those tables to find out about all the databases served by the server, if the user is an admin user.

Database Connection

A database connection is created using a driver and a connection string. Some servers (Oracle, SQL Server) allow connections only to a specific database served by the server. Others (like MySQL, Derby) allows connection to access the Administrative tables, thus helps to get the information about all databases served by the server.

Server-less (file based) databases such as SQLite and Embedded Derby are connected directly by the driver with out a server.

Tools

• Database Explorer
• SQL Editor
• Query Builder
• Table data viewer and editor
• Table Designer
• View Designer
• Entity Relationship Diagram Viewer
• Schema designer (off-line mode, MySql Only)
• Database Migration Tool

Main functionalities

• Ability to add new Database Connections to Database Explorer
• Explore and view the structure of the database (tables, views, columns etc)
• Edit and execute and save SQL Script using SQL Editor
• Build Queries using Query Builder and view the results
• Create new database or drop database (if supported)
• Create new table or drop tables.
• View the data of a table
• Modify or append data to a table
• Export and import data to and from a table as CVS
• View the structure of database as a diagram (Entity Relationship diagram)
• Off-line design of database structure (Database Schema designer - MySql Only)
• Create derby Database file
• Create SQLite database file
• Database migration (Structure and data) from one database to another
• API for programmatic access of the database and its structure
• Extension points for other plugins to extend the functionalities

Database Explorer View

The main entry point for the Database tools in studio is the Database Explorer view at the left hand side

Following databases will be supported

• MySql
• Postgres
• Derby
• SQLite
• Oracle
• DB2
• SQLServer

Adding database connections to Database Explorer

In the Database Explorer view, user has the option to add new connection

Adding Server-based Database Connection

The network server based connection needs following parameters

• Host Name
• Database Name (Optional for some servers)
• Port Number (Optional)
• User Name
• Password (Optional)

If the database type is of network server, then following dialog is provided to create the connection.

Adding Server-less Database Connection

For server less database, the driver directly connects to a file (SQLite) or directory (Derby). In that case the connection dialog switches to the following format

User can browse the file or directory and select the appropriate database.

Testing the Database Connection

Before dismissing the dialog and adding the connection to the Database explorer user has the option to test the connection.

A success message is displayed if the connection succeeds

A failure message is displayed if the connection fails

Removing Database Connection from explorer

The connections added to the Database Explorer view can be removed using the context menu

A confirmation dialog pops up to confirm the action

Exploring the database structure

Once the connection is added to the Database Explorer, it is ready to be explored. User can drill down the tree to view to different levels

(Per previous comment, the structure below seems more like Connections -> Instances -> Databases - DW ) (Fixed Now - Winston)

Database Level

Table Level

Column Level

The column icons are changed to show if the column is a Primary Key or a Foreign Key or an Index Key

Viewing and Modifying Table Data

The Database Tools provides support for viewing and editing the Table Data. The "Table Data Viewer" is invoked from the Table node in the Database Explorer as shown below

The data is fetched and displayed in the editor

Creating and Dropping Database

Database Tools offers support to create or drop database where ever supported by the underlying connection.

Create Database

The create database dialog is invoked using the context menu item from the Connection Node

In the dialog user can specify the new database name

Clicking on the OK button would immediately create the new database and add it to the Database Explorer

Image:DbTools CreateDatabase added.png

Drop Database

The drop database dialog menu item appears in the context menu of the database node

A confirmation dialog appears asking to confirm the action

Creating, Dropping, Truncating and Copying Table

Database Tools also provides support for adding new tables to a database, view the structure of existing table and delete the existing tables from the database

Creating Table

Create table action is invoked from the Database Node or the Table Group Node.

The action invokes the Table Designer dialog

User can do the following operations in the dialog

• Click the Add & Remove button to add or remove columns. The added columns will appear in the List above. Double clicking on the table also adds a column

• Modify the properties of the columns by doing the following
  • Mark the Column as Primary Key
  • Change the name of Column
  • Select data type from drop down list
  • Specify if the Column can be null
  • Specify a Default Value

User can view the SQL that would be generated to create the tables in the SQL View tab. However, the view is read only. Clicking on the Edit SQL button would close the dialog and open the SQL Editor in the editor area.

Clicking on the "Create", creates the table and adds it immediately to the Database Node.

Dropping Table

User drops a table using the menu item "Drop Table" available in the corresponding Table Node.

A confirmation dialog pops up to confirm the action, with a warning user will loose all the data, the table will be removed from the database and the operation can not be reverted.

On clicking the button Yes, if successfully removed, the table disappears from the Table Group Node.

Truncating Table

User truncates a table (remove all the table data) using the menu item "Truncate Table" available in the corresponding Table Node.

A confirmation dialog pops up to confirm the action, with a warning user will loose all the data (the table will not be removed from the database though) and the operation can not be reverted.

On clicking the button Yes, if successful, all the Table Data will be removed. When user tries to view the table data using the menu item "View Table Data", the view will show empty table.

Copying Table

User copies a table (including all the table data) using the menu item "Copy Table", available in the corresponding Table Node.

A input dialog pops up for the user to provide name for the duplicate table.

On clicking the button Ok, if successful, the created table will appear in the Table Group Node. When user tries to view the table data using the menu item "View Table Data", the data view will be exactly same as the original table data view.

Creating Database Views

[TBD]

Migrating database structure and data

One possible real word database action is database migration. Assume a user is developing an application simultaneously designing database tables required by the application in a development database. When the application is deployed, user might want to migrate the database structure along with the data to the production database server. Database Tools provides a simple support to achieve this. The Database Migration Tool is invoked from the Connection Node or the Database Node.

The Database Migration dialogs appears as shown below

User can take the following actions in the dialog

• Select Source Connection
• Select target Connection
• Select source database from which the structure and data will be migrated
• Select target database to which the migration takes place
  • Check the option if the database should be created if not exists
• Select list of source tables to be migrated
  • Should the tables be created first or migrate data between existing tables ony
  • Specify if the same tables exist in target database should they be dropped

Editing and Executing SQL

The SQL Editor can be invoked from any of the nodes from a particular connection in the Database Explorer.

The editor is opened in the Editor area.

Executing the SQL

To execute the SQL, users clicks on the Execute button in the toolbar.

SQL Execute Result

The SQL Editor has a Result Area, which appears at the bottom of the SQL Editor. The result area appears only when the SQL is executed. It would have tabs to show data of a table, if SELECT statement is executed or an error tab is shown if the execution has errors. If the SQL script execution results in update, modify or deletion of table rows, then number of rows affected will be displayed

Data Result

Error Result

Success Result for Update, delete, modify

Creating Queries using Query Builder

Most common database operation is executing SQL queries against a database connection. User has two choices.

• Open the SQL Editor and hand code the SQL query and then execute it
• Open the Query builder, build the query visually and then execute the query.

Both ways user get to view the result immediately. The queries created by Query Builder can be saved for later design. Opening Query Builder

Like the SQL Editor, Query Builder could be opened from any of the node in a particular connection. The query being built can only be executed in that connection.

The Query builder opens in the Editor Area. Query Builder has three View Areas

• Design Canvas where operations like dropping tables, selecting columns, specifying joins etc are performed

• Column based query customization view, where further query customization related to selected columns takes place

• SQL View, where the generated SQL can be viewed and modified

Building the Query

Adding the tables

As suggested by the informational message, to start building the query user need to start dragging table from the Databse Explorer and drop it on to the Query Builder canvas. After the table is dropped on to the designer, the canvas displays the structure of the table

Generated SQL

The generated SQL is s simple SELECT statement where all the table columns are included in the query with a wild card ("*").

Selecting the columns

In a Query user might want to include only selected columns. This is easy to achieve. Canvas displays all the columns of a table with a check box. So to include only certain columns select those columns using Check Box.

Generated SQL

Now the generated SELECT Statement would include only the selected columns instead including all the columns using wild card.

Customizing the joins

It is possible to just include more than one table and their columns in a query. But the result would be a Cartesian Join. That is the result set would contain rows that are join of every row of a table with every row of other tables in the query, which would not be the desirable result. SQL JOIN clause allows the user to combine data from multiple tables in to a single result set row in a desirable way. Creating joins in the Query Builder is very easy. User need to draw and connect two columns from different table to create the JOIN

From the Join type selection dialog usr selects the join type. If not canceled, the join line will appear in the diagram

Generated SQL

The generated SQL would contain the appropriate JOIN clause in the SELECT statement. Note, in the Query Builder canvas multiple connection lines are created. So the JOIN includes AND clause for multiple columns

Customizing the Query based on selected Columns

The query may be further customized to display results based on each column. For this user uses the Selected Column List view at the bottom of the Query Builder.

ORDER BY

In the Query "ORDER BY" clause is used to specify how the columns should be sorted in the result set. Adding the "ORDER BY" clause to the generated query is done via selecting one of the options "ASC" or "DESC" from the drop down in the Sort column of the Selected Column List view .

Generated SQL Generated SQL included the ORDER BY clause. Also it is possible to specify in what order the columns should be included in the ORDER BY via the Sort Order column of the Selected Column List view.

GROUP BY

Another important aspect of Query is to group rows in to sets (one row per set) and then summarize that data in various ways. This is done via "GROUP BY" clause. Query Builder's Selected Column List view allows to do this by a simple check box. User need to check the box in the 'Group By' Column.

Generated SQL

Functions

Apart from general query clauses SQL SELECT statement also support functions to manipulate and transform fetched data. Query Builder supports some of the common functions such as "AVG", "COUNT", "MAX", "MIN", "STDDEV", "SUM", "VARIANCE". To apply the function to a database column user need to select the function from the drop down list in the Selected Column List view as shown below.

]

Viewing the structure of database as diagram

For reporting purpose user might want to view, rearrange the layout and export the Entity Relationship (Schema Diagram), which shows the table structure and relationship among tables and fields. This is achieved via the context menu item

The diagram is shown in an editor. The relationship between the tables are shown as connection lines. The arrow of the connection line points from the Foreign Key to the primary key.

Rearranging the tables

The tables can be rearranged by selecting a table and then drag and place it at desirable location. The entity relationship connection will be automatically re-arranged based on the new location of the table. A transparent rectangle image gives feedback on where the table will be moved

Resizing the diagram

The diagram can be resized using the drop down menu in the toolbar filled with possible resize ratio.

Another option to resize is using Ctrl or Command + Mouse Scroll)

Exporting the diagram as PNG

Right click on the diagram and from the context menu select

User will be asked to select a location and file name to save the image.

Designing Database Structure in off-line mode

In the previous sections the creation of database, designing of tables etc are discussed. Those operations are in "on-line" mode, in the sense, the tables are created immediately in the underlying database. Any modification to table structure also immediately reflected in the underlying database. There are situations when a user might want to design database structure in off-line mode. That is, user get to design the tables, its columns and their relationship etc in a design surface. The advantages are

• The created tables and their structure are not send to the database immediately.
• The tables can be dropped from the design with no effect to the database
• The design can be passed to other team members for further suggestion and comments
• The design can be saved as an image for reporting purpose
• The over all generated SQL can be reviewed by peers
• The same diagram can be executed in several databases

Once the user is satisfied with the over-all design of the database, the completely structure can be created in the server with a single execution.

The database schema designer can be opened from any of the nodes inside a Database Connection Node.

Schema designer open in the Editor area with the hint what the user should be doing next

Creating the Tables

User clicks on the Table Palette item and then clicks on the canvas to add a table to the designer. The dropped tables can be resized and re-located.

To remove a table, right click on the canvas and select Delelete

Adding Columns to Tables

The next action is to add Columns to the Table. This is done by clicking on the Column Palette Item and then click on the Table to add a Column

Modifying Table Properties

The Column properties can be modified in the "Column Properties View", which appears in the bottom of the designer when a Column is selected in the canvas. The desired column properties can be modified in that view.

Inline editing Table and Column Name

Schema designer allows the user to in-line edit the Table and Column name by double clicking on the names. Also the column name can be changed in the "Column Properties view" also

Creating Entity Relationship

The Entity Relationship (Foreign Key Relationship) is created between tables by dragging and connecting two columns in two different tables. The source column is marked as ForeignKey and the target column is usually a Primary Key.

Viewing and executing the generated SQL

Once the design is satisfactory the generated SQL can be reviewed via the SQL view Tab. The generated SQL can be executed to create the corresponding schema, table and columns