SQL Editor UI Specification for Promo G

Author: Andrei Badea

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

Use Cases and Scenarios

Create SQL Files

While creating a database application using JDBC, the user needs to create SQL files containing SQL statements. Examples of SQL statements are scripts for creating a database and populating it with data or SQL queries (SELECT statements).

Scenario:

  • The user invokes the File - New item from the main menu. The New File Wizard appears.
  • Selects Databases category and the SQL File file type and clicks next.
  • Enters the file name in the New SQL File Wizard Panel of the New File wizard and clicks the Finish button.
  • The newly created file is opened in the SQL Editor and selected in the Projects View.

Multiple Result Sets

The execution of an SQL statement might return more than one result set. The SQL editor currently displays only the first one. In the new version it will be possible to browse among all returned result sets.

Scenario:

  • The result is displayed in the SQL Editor Result Table. If more than one result set is returned, the user can browse among them using the Next Result Set Button or the Previous Result Set Button.

Change Table Data

The user needs to test a JDBC application during development, therefore he needs to have some testing data in the database. The SQL Editor allows the user to change the result set of an executed statement (change a row data, insert a new row or delete an existing row).

Scenario:

  • The user enters a query which retrieves the data of a table he needs to change (e.g., "SELECT * FROM table") and executes it.
  • In the SQL Editor result table the user changes the data in (possible several) columns of a result set row. After moving to another row or clicking the Apply Changes Button the modified data is saved to the database.
  • The user clicks the Insert Row button in the result toolbar. A new, empty row is added as the last row in the result table, which the user can fill with the data. The row is inserted into the database either when the user moves to another one or by using the Apply Changes button.
  • The user clicks the Delete Row button in the result toolbar. The Delete Row Dialog appears and if the user confirms it, the current row is deleted from the database.
  • The user may wish to apply the changes made to the database manually. In this case, before making any changes he clicks the Auto-apply Toggle Button. The user has to use the Apply Changes Button to apply the changes to the database.

Code Completion

While writing the SQL statements, the user needs assistance from the editor. The editor offers completion of SQL keywords (such as the SELECT or CREATE keywords) and database elements (such as table or column names).

Scenario:

  • The user presses Ctrl+Space in order to display the code completion. The code completion popup appears.
  • The user chooses an item from the code completion popup and presses Enter or clicks an item with the mouse.

Specification

SQL Editor Window

Figure: SQL Editor showing a statement and a result set returned by its execution.

  -----------
 / File1.sql \
+--------------------------------------------------------------------------------------------------+
| +----------------------------------------------------------------------------------------------+ |
| | Connection: |_test_on_localhost_|_v_| [ R ] [ S ] (standard toolbar buttons)                 | |
| +----------------------------------------------------------------------------------------------+ |
| +----------------------------------------------------------------------------------------------+ |
| | SELECT * FROM invoice INNER JOIN item ON invoice.id = item.invoice_id                        | |
| |                                                                                              | |
| |                                                                                              | |
| |                                                                                              | |
| |                                                                                              | |
| +----------------------------------------------------------------------------------------------+ |
| +----------------------------------------------------------------------------------------------+ |
| | Result Set: 1 out of 2 [ N ] [ P ] | [ AA ] [ I ] [ D ] [ A ] [ DC ]                         | |
| +----------------------------------------------------------------------------------------------+ |
| +------+---------+--------------+------+------------+---------+----------+---------------------+ |
| | id   | user_id | date         | id   | invoice_id | item_id | quantity |                     | |
| +------+---------+--------------+------+------------+---------+----------+                     | |
| | 1    | 23      | 10-JAN-05    | 3    | 1          | 45      | 1        |                     | |
| +------+---------+--------------+------+------------+---------+----------+                     | |
| | 1    | 23      | 10-JAN-05    | 4    | 1          | 50      | 3        |                     | |
| +------+---------+--------------+------+------------+---------+----------+                     | |
| | 3    | 50      | 11-JAN-05    | 3    | 3          | 20      | 2        |                     | |
| +------+---------+--------------+------+------------+---------+----------+                     | |
| |                                                                                              | |
| +----------------------------------------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+

Components:

  • Editor toolbar
  • Connection combo box - displays the list of connections from the Database Explorer. The last item of this combo box is "Add Connection", which displays the New Connection dialog (the one which appears when invoking the Connect Using action on a driver node in the Database Explorer).
  • R (Run) button - executes the SQL statement agains the connection selected in the Connection combo box. The button tooltip is Run file. Always enabled. This should be the Run - Run File - Run File menu action with the Shift+F6 shortcut.
  • S (Select in Explorer) button - selects the current connection in the Explorer. The button tooltip is Select Connection in Explorer. Always enabled.
  • Editor
  • Result toolbar
  • Result Set label - displays the index of the current result set and the total count of result sets that the execution of the statements in the editor returned.
  • Next Result Set button - displays the next result set. The button tooltip is Move to the next result set. Disabled if the execution returned only one result set or the current result set is the last one.
  • Previous Result Set button - displays the previous result set. The button tooltip is Move to the previous result set. Disabled if the execution returned only one result set or the current result set is the first one.
  • AA (Auto-apply) toggle button - if set, automatically applies to the database the changes (if any) made to a row when the cursor leaves this row. If not set, the user must apply the changes manually using the Apply Changes button. It is always only possible to change a single row. If the user changes a row when auto-apply is off, fails to apply the changes and starts making changes to another one (or uses the Insert Row or Delete Row buttons), the Apply Changes Dialog appears informing the user that the current changes need to be applied first. Also, if in the same situation the user performs an action which would cause the current result set to be thrown away (such as close the SQL Editor window), the Apply Changes Dialog appears. The button tooltip is Automatically apply changes when leaving a changed row. Enabled if the current result set is updateable. The default value is "on".
  • I (Insert Row) button - inserts a new, empty row in the result set. The button tooltip is Insert a new row. Enabled if the result set is updateable.
  • D (Delete Row) button - displays the Delete Row Dialog and deletes the current row(s) from the result set. The button tooltip is Delete the current row. Enabled if the result set is updateable and not empty.
  • A (Apply Changes) button - saves the changes made to the current row. The button tooltip is Apply Changes. Enabled if the result set is updateable and the current row has been changed.
  • DC (Discard Changes) button - saves the changes made to the current row. The button tooltip is Discard Changes. Enabled if the result set is updateable and the current row has been changed.
  • Result table - displays the current result set.

If the result set is updateable, it is possible to edit it. (Not all result sets are updateable, e.g., queries containing views or joins.) Changes can be applied to the database in two ways:

  • automatically when the user leaves a modified row when the Auto-apply toggle button is on
  • manually by the user using the Apply Changes button

When a row is changed its background is painted in light green and text in the modified cells of this row is painted in dark green.

SQL Editor Code Completion

Figure: The code completion of the SQL Editor

Description:

The code completion offers the completion of database elements (schema, table, column, and function names) and SQL keywords (SELECT, INSERT, etc.).

When a SELECT statement is written, column names are usually entered before the list of tables the columns belong to. Because of this, the code completion behaves differently based on whether there is a FROM clause in the statement or not (the pipe marks the place where the code completion is invoked):

SELECT |

The code completion list contains:

  • suitable keywords
  • the list of tables from the current schema
  • the list of columns from these tables
  • the list of schemas

SELECT xyz.|

The code completion list contains:

  • the list of tables from the xyz schema
  • the list of columns from the xyz table in the current schema

SELECT | ... FROM ...

The code completion list contains:

  • suitable keywords
  • the list of tables used in the FROM clause (or their aliases if any)
  • the list of columns from these tables
  • the list of remaining tables from the current schema
  • the list of columns from these tables
  • the list of schemas

The list of columns contains an "(all columns)" item which inserts the comma-separated list of all columns in the respective table. Each column is prepended by the namespace before the place where the completion was invoked (such as "xyz." or "xyz.abc.").

It will be possible to choose the completion item using "." as well (along to Enter), which will do the same thing as Enter, but append a dot to the inserted text and leave the completion window open.

Apply Changes Dialog

Figure: The "Apply Changes" dialog box

+-------------------------------------------------------------------------+
| Apply Changes                                                           |
+-------------------------------------------------------------------------+
|                                                                         |
| Data in the current result set have been changed.                       |
| Do you want to apply the changes to the database or discard them?       |
|                                                                         |
|                                      [[ Apply ]] [ Discard ] [ Cancel ] |
+-------------------------------------------------------------------------+

This dialog can appear in two circumstances:

  • when the user changes a row and performs an action which would cause the changes to be lost (move to another result set, execute a statement, close the SQL editor tab). In this case the message is Data in the current result set have been changed.
  • when the user changes a row and (without first applying the changes) start making changes to another row (or inserts or deletes a row). In this case the msessage is Data in another row have been changed. These changes must be applied to the database before (making changes to|inserting|deleting) this row.

The question Do you want to apply the changes to the database or discard them? always appears at the bottom of the dialog.

Delete Row Dialog

Figure: The "Delete Row" dialog box

+-------------------------------------------------------------------------+
| Delete Row                                                              |
+-------------------------------------------------------------------------+
|                                                                         |
| Do you want to delete the current row(s) from the database?             |
|                                                                         |
|                                                        [[ Yes ]] [ No ] |
+-------------------------------------------------------------------------+

New File Wizard - New SQL File

Figure: The SQL File type in the New File wizard

...
Web Services
Databases
  DB Schema
  SQL File
  ...
Sun Resources
...

Figure: The New SQL File panel of the New File wizard

+---------------------------------------------------------------------------------+
|                                                                                 |
| Name and Location                                                               |
| ------------------------------------------------------------------------------- |
|                                                                                 |
| File Name: |_newSQLFile_______________________________________________________| |
|                                                                                 |
| Project:      |_Project1______________________________________________________| |
| Folder:       |_src/resources/__________________________________| [ Browse... ] |
|                                                                                 |
| Created file: |_/home/username/projects/Project1/src/resources/newSQLFile.sql_| |
|                                                                                 |
+---------------------------------------------------------------------------------+

SQL File Node

Figure: SQL File node contextual menu

Open
--------------------
Cut
Copy
Paste
--------------------
Delete
Rename...
--------------------
Save as Template...
--------------------
Tools >
--------------------
Properties

Project Features

About this Project

DB was started in November 2009, is owned by Antonin Nebuzelsky, and has 113 members.
By use of this website, you agree to the NetBeans Policies and Terms of Use (revision 20140418.2d69abc). © 2013, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo
 
 
Close
loading
Please Confirm
Close