Data View: Beta Release

Introduction

Data_View is a pure Java application for viewing tables of data records available from a database server. It is intended as a general database "browser". It requires no expertise in database systems operations to access a database, nor a knowledge of the database structure to discover the data organization and examine its contents. A graphical interface makes simple queries simple, and complex queries possible and easy to save and reproduce.

The purpose of this release is is to gather feedback from users about their experience of the basic functionality and user interface paradigm. This is a rough prototype design with a relatively awkward organization of the user interface elements. The organization of the GUI will be underging significant changes.

The small Data_View window that manages Database connections will be subsumed as an enhanced dialog box into the database access window. The latter will become the primary Data_View application where the menu bar will be relocated.

The management of the Configuration will be expanded. Each logical database connection will be configured under a unique connection name which may contain numerous configuration parameters that can override a corresponding set of general defaults. A configuration/preferences management window will be created.

A thorough on-line help manual integrated with a context sensitive help mechanism is in the works. Writing this from a user perspective without getting too technical is a big challenge. I'm definitely not the right person for this job; any good writers who would like to contribute would be most welcome.

The SQL syntax editor is currently quite simple minded. There are plans to support embedded variable specifications that would be filled in via labeled text fields in the user interface. It would also be desirable to implement a comprehensive syntax analyzer; though this is a daunting task considering the significant variability in SQL syntax in use. The current file save/load mechanism will be integrated into the configuration management to provide for named SQL syntax sequences.

The database connection is currently configured to be in read-only mode. Database connections may be queried (to the extent that the user has access permission with the database server), but the "Update" button and "Insert" tab pane are disabled. The ability to change the database contents - including the creation of new catalogs, tables, and fields - will be provided once the mechanism of secure user authentication and the issues of ensuring data integrity have been carefully considered.

The data table panes will be enhanced with additional features. They will be able to write the table contents to a local disk file. They will also be provisioned with custom field editors - both standard and configurable - to enable easy viewing and editing of fields containing large amounts of text, as well as viewing and manipulating image files referenced by field values.

The Conditions field of the Select pane is likely to be replaced with a more graphically intuitive interface. Table field references and constant values, and operations between them, will be defined in fields supported by pull-down lists, and operations between these fields and between rows of fields will be defined in fields also supported by pull-down lists. Named conditional expressions saved in the database configuration will also be supported.

Joins between tables will be supported by a new dialog box that graphically guides the user in defining field joining relationships and the type of join operation. The class implementing this capability is currently in alpha development. Of course, named join relationships will also be possible.

Behind the user interface, Data_View employs a database independent Java Database package developed at PIRL. This package is under development to provide support for local data table text files that will act just like a database server. This will enable standalone databases bundled with Data_View to be distributed on CD/DVD media and be fully functional as long as a Java environment is available; these could be used, for example, on notebook systems in non-networked environments.

Installation

System requirements: A networked computer providing a Java run-time environment (JRE) that implements at least version 1.3 of the Sun Microsystems Java Foundation Classes (JFC) API (Check the Sun site for no-cost Java distributions and documentation).

Download the Data_View.jar file from the PIRL distribution site. Note: Data_View.jar is a link to the file with the current version number. On well-behaved systems (i.e. with a complete JRE implementation) this is all you need. Just run the jar file to launch the Data_View application. On Unix systems the shell command line is:

java -jar Data_View.jar &
On Apple Mac OS-X systems you can just double-click on the Data_View.jar icon.

Microsoft Windows systems will also launch the Data_View application with a double-click on the Data_View.jar icon. However, it's been found on the Microsoft Windows system that we used for testing that its JRE was unable to correctly find in the jar various support files used by the application. In particular, the default configuration file included in the jar is not found. In this case you should download the Database.conf file and place it next to the Data_View.jar file.

Use

When Data_View starts and can find the preconfigured Database.conf file it will automatically open a connection to the PIRL MySQL database server and pre-select several fields from the MGSC table of the MOC catalog. Just press the Select button and the first 50 records will be obtained and displayed in the data table pane.

It is also possible to use the Data_View application without any configuration file: After the small Data_View window appears, check the Confirm Connection option in the Preferences menu. Then enter MySQL in the server Type field and hit Connect. When the Database Access dialog box appears enter PIRL.LPL.Arizona.edu in the Server name field, and leave the other two fields blank (you must remove your system User name); then click OK.

The database fields Available for selection are listed by catalog and table in the left Fields display, and those Chosen for the next database selection are listed in the right display. Clicking on entries in either list (use shift-click for a continuous range and/or control-click for discontinuous entries) will highlight them. To Include or Exclude them from the Chosen list press the corresponding button above the respective list.

The Conditions field accepts the usual WHERE syntax of SQL (the keyword WHERE is not required, however). The syntax follows this pattern:

<field reference>   <operator>   <field reference>   [ [ <boolean> ] ... ]

<field reference>
A database field reference, or a constant value which must be in single or double quotes if it is not numeric. A field reference may be simply the name of a field in a Chosen database table, or it may be qualified with its catalog and table names - <catalog>.<table>.<field> - to disambiguate the reference.
<operator>
A relational operator such as =, !=, <, >, <= or >=. The operator LIKE is used for text comparisons with the % (percent) character in the text standing for zero or more of any characters.
<boolean>
A logical operator such as AND or OR. It is used to connect sequences of primitive relational operations.

The Limit field is used to limit the number of records returned from a database selection. The MGSC table contains over 78,000 records. Requesting a large number of possible records can take many minutes to fulfill (actually, the database responds in seconds, but a lot of Java work must be done to prepare the records for display), and can exhaust the memory available to your JRE (see your Java documentation, or your systems administrator, for advice if this becomes a problem).

When the Select button is pressed any Conditions will be applied during selection of the Chosen fields from the database. If the conditions syntax is malformed, an Error dialog will appear with, perhaps, a hint about the source of the problem. A successful selection will display the data in the table pane. Scroll bars will appear if the amount of data is too large to fit in the table display area. The columns are named for the data fields that were chosen and are listed in the order they appear in the Chosen list. The column widths can be changed by dragging the divider between the column names, and columns can be reordered in the table by dragging the column name left or right.

Pressing the SQL... button will open a Select SQL syntax editor window. It will be pre-loaded with the SQL syntax representing the currently Chosen fields and Conditions. This is currently a simple text editor with the usual edit operations plus undo and redo, and some keyword color highlighting (on systems where the JFC implementation supports this). A pop-up menu in the text editing area includes all of the main menu entries. Selecting one of the Operations entries will insert its syntax at the current cursor location. Menu items are provided to Save the SQL text to a local disk file, or to Open a file in the editor; this is very helpful for anything more than simple database access operations. Pressing the Execute button will execute the SQL on the database server, with the results appearing in the data table. Pressing Execute, Cancel or Clear after the SQL text has been modified will produce a Check dialog box asking if you would like to save the text.

The Reset button is a quick way to clear all of the Chosen fields and Conditions text.

The Connect button in the Data_View window may be used repeatedly to open multiple, simultaneous connections to one or more database servers. The Preferences menu item controls the use of the Confirm Connection dialog and the appearance of tooltips associated with the GUI elements. The File menu may be used to Save the configuration to a local disk file and Open a new configuration from a file.

Contact

The About entry in the Help menu contains identification and developer contact information for the application. In general, when operational problems occur, an Error dialog appears describing the problem and identifying the software classes involved. Problem reports should include all this information. Comments and suggestions that could improve this application's usefulness are welcome. If you would like to receive notices when upgrades become available, please let me know.

Bradford Castalia  
Senior Systems Analyst Castalia@Arizona.edu
Planetary Image Research Laboratory 520-621-4824
Department of Planetary Sciences 1629 E. University Blvd.
University of Arizona Tucson, Arizona 85721-0092

"Build an image in your mind, fit yourself into it."