The CGI approach [7] for accessing databases across the WWW has its benefits, including ease of implementation, portability of server software and the use of standard WWW browsers as clients. It also has its limitations:

• Sessions Problem
  In most client / server systems, the client stays connected to the server through multiple transactions. With CGI, one connection is made per call to the CGI program. For Web / database interaction, this means a new database connection per query or call sent via CGI. This leads to performance degradation, and limits the client / server functionality, making it impossible to create session-oriented database applications.
• Server Load / Scalability
  All communication between the client and backend database must go through the HTTP server, with a new process created for each request to execute a CGI script. This obviously also leads to performance degradation.
• Slow
  Web browsers cannot send requests to the server asynchronously. This means that clients have to wait for the server response, which includes the time for the server to start CGI processes.
• Limited Presentation Capabilities
  The presentation of the user interface and viewing of data is restricted by HTML.

This approach has been used with success by a team at Southampton University [7] to display both standard data structures and images. Therefore, unless complex data contained within the database can be converted into a WWW browser compatible form, such as an image, then only certain data can be viewed legibly.

Jexplorer [8] is a product written to access databases in a standard way using Java Database Connectivity (JDBC). It has been written in Java and so is platform independent.

Using standard meta-data contained in all commercial databases, it can show the user the field names and types which the database contains. These details can then be used to create queries to submit to the database, the results of which can be displayed in tabular form.

This method is a good basis for the package that is needed, but again has a number of limitations:

• Standard viewing mechanism
  It only allows viewing of standard data structures. There is no way of defining how to view other structures.
• Navigation problems
  To reach the data that the user requires, means probably navigating through other unnecessary data. In addition, the user will probably need to be told where to navigate to, to retrieve that information, as it may not be immediately obvious.
• Security problems
  All users will have access to the same data, and even with different user accounts, not all databases will allow different views per user. Thus controlling access is more difficult.

From the specification, what is actually needed is an extension to the idea of Jexplorer, which allows database administrators to select which objects within the database that users should be able to access, and how to view each one.

For each object, a viewer is provided and stored with information about the object. This set of information will be called a template. Each template is then stored in a template database, separately from the physical data, for access by a client user program. In this way, the physical data is kept completely separate and secure from the additional information provided by the templates.

Using the created templates a client user program can access and display information about each object, from which the client user can select objects to view. The selected objects can then be displayed using the viewer contained in the template.

A management tool to allow creation of these templates can use the same meta-data used by Jexplorer to present the selectable objects and the relationships between them to the database administrator, thus easing the setting up of the template database.

This template mechanism may be most useful for object-relational databases, where any amount of new data types can be defined. With these new types, the data contained within the database is of no use when viewed using a standard database viewer, but it will be very accessible with the extra viewers made available through the templates.

• Can design interface graphically
• Easy to set-up network socket connections
• Easy to set-up links to many different vendors’ databases through ODBC
• The developer knows it well

• Platform dependent (both program and database access)
• Not easy to load up separate modules and communicate with them (ActiveX files can be run, but communicating with them is more difficult to implement)

• Can design interface graphically
• Easy to set-up network socket connections
• Easy to set-up links to many different vendors’ databases through ODBC
• The developer knows it quite well, although has never done any work with databases through it
• Good modular structure

• Platform dependent in many ways, especially in terms of graphics, network sockets and accessing databases

• Platform independent
• Easy to set up network socket connections
• Easy to set-up links to many different vendors’ databases using JDBC
• Can recreate and execute modules after they have been converted to a flat file

• The developer has not used it very much before, but it is very similar to C++ in many ways
• Not as easy to set up an interface

There are also different development environments for Java to consider

3.2.3.1 IBM Visual Age for Java

3.2.3.2 Symantec Visual Café, Sun Java Workshop

• Easy to use graphical interface for setting up tables and queries
• Contains a field structure which can hold up to 32Mb of data per record (e.g. this structure could be useful for storing viewers)
• The developer knows it well
• Easy to transport databases between different machines for testing purposes

• No specific JDBC driver, and so could only be tested using ‘JDBC/ODBC Bridge’ JDBC driver

• Used by the university for many database applications, and is the database of choice for use on the IBM SP2 parallel computer
• Contains a field structure which can be defined to hold as much data as is necessary (e.g. this structure could be useful for storing viewers)
• It is an object-relational database, and so supports user-defined types and functions for creating and accessing complex data structures
• Has specific JDBC drivers written for it for both local and network access
• Has a scripting language to ease setting up of databases on multiple machines

• The developer has never used it before
• Not easy to transport databases between different machines for testing purposes

The client user program can be connected directly to the template and data databases using JDBC (Figure 3.5.1). Unfortunately, the user then needs to know the exact URL for this database, along with the driver to use, and a user name and password to gain access. All of this, the database administrator would have to set-up and distribute, and any movement of the data, or structure of the data, would mean updating the client user program every time.

Figure 3.5.1

In contrast, the client user program could connect to a separate program running on the server, that in turn is connected to the template database using JDBC (Figure 3.5.2). This server program would then have to be given the URL, driver, user name and password to use, but these details would only need to be known by the database administrator. They would not need to be distributed, as the client user program would only need to know the IP address and port number on which the server program was running to be able to connect to it.

Figure 3.5.2

This sets up the connection to the template database very simply, and allows the data and structure of it to be changed, without having to update every client user program. The server program could then be updated, and still run on the same machine and port number, without the client user program even knowing anything had happened.

When accessing the data database (Figure 3.5.3), connection information is again needed, but this can be stored separately with the templates for download as necessary. Thus, it is again only known by the database administrator. In this way, a viewer can connect to the data database using a standard method provided by the API. This will allow the default viewing mechanism to be implemented simply as well.

Figure 3.5.3

With this structure, the database administrator has complete control over movement of data and template information without affecting the external client users, as long as the host name and port number of the server program do not change.

The viewer programs to be used by the client will need to be downloaded from the server to allow them to be executed. To reduce the amount of downloads necessary, a local cache of viewers will be built up, and if the server ever updates a viewer, the new version will be downloaded into the cache. In this way, the client will be able to use the same viewer repeatedly, without wasting time waiting for it to be downloaded every time from the server.

Separate from the client and server programs, there needs to be a management tool, to allow setting up of the templates. This tool will need to be able to access the template database, along with the structure of the data databases directly. It will only be used by the database administrator and so any change of location or user name / password combination for accessing the database will be known. A two-tier structure (Figure 3.5.4) is probably the simplest, and easiest to use and implement in this instance.

Figure 3.5.4

Figure 4.1.1

The client user program will take direct input from the user through the keyboard and mouse.

Communication will take place between the client and the server, to allow retrieval of any information necessary so that the user can access the viewers, and ultimately the actual data of the database.

There is a local cache of previously used viewers, so that the same viewer does not need to be downloaded more than once.

There are issues involved with the client user program, relating to the versioning of downloaded viewers. A mechanism needs to be implemented to allow viewers that are currently in the cache, to be updated as necessary. A version number and last updated date should suffice.

Other issues relate to communication with the server, which needs to be controlled so that information is not mixed up by sending multiple commands at any one time.

Figure 4.1.2

The server program will take direct input from the user through the keyboard and mouse to allow it to be started. Once started, it will read data from the template database and viewers, and communicate with any attached clients, with the only other user input being needed to stop the program when all users have disconnected.

Figure 4.1.3

The tool will take direct input from the user through the keyboard and mouse.

It will read in viewer data from local files as necessary, and read meta-data from the data databases. Templates for each object, along with any options necessary, will be stored within the template database.

The data and template databases may be stored locally, or externally, as the JDBC mechanism will work with either.

Each package has one or more public classes that can be accessed from outside the package and a standard naming convention is followed throughout. All classes within a package that contain methods have their names prefixed with ADBV. Any classes that only store data, and as such, do not need any methods, do not have the ADBV prefix.

This package contains the classes needed for the Client program. The class ADBVClient can be executed to start the Client program. This package also contains the public interface ADBVViewerInterface, which must be implemented by all viewer applications.

ADBVClient

This is the main client class to be executed. It creates a user interface, with which the user can select the server with which to connect. It contains functions to allow some of other client classes to access the cache and retrieve current information about server connection information.

ADBVClientCache

This class contains all of the information about cached viewers. The details about each viewer are stored in instances of the CacheInfo class (see below).

ADBVClientClassLoader

This class contains functions to allow creation of a specific Java class from an array of bytes.

ADBVClientClassThread

This class contains three main functions that can be accessed by an external viewer. These form part of the ADBV API, which is explained in more detail in the User Documentation presented in Appendix 3. One version of this class will be created for every viewer loaded, to act as an interface between the selection window (ADBVClientWindow) and the viewer itself. ADBVclassClosing should be called by all viewers when they are closed by the user so that ADBV will not try and stop them itself. ADBVgetConnection allows creation of a java.sql.Connection object to the database containing a specific table and using a specific user name and password if necessary. ADBVgetTblName returns the table which was selected to start this viewer (e.g. this is useful if one viewer is used for more than one table, as the Default Viewer is). println can be used by the viewer as well, to allow a message to be displayed in the main client window. The other functions allow the calling ADBVClientWindow to communicate with each class thread, such as to stop it. Each thread is also given a unique identifier to allow it to be distinguished from other threads easily.

ADBVClientWindow

This class creates the window that will be the focus of the user. It allows them to select a table for viewing and start up the related viewer. Many of these public functions are called from ADBVClientClassThreads. Some just pass on the call to the related ADBVClient to do the actual processing. Other functions allow the calling ADBVClient to communicate directly with this selection window.

ADBVDefaultViewer

This class implements the ADBVViewerInterface defined below, and just displays whatever table is necessary using a standard list view type display. The file ADBV.gui is also required for this class to be used successfully.

ADBVViewerInterface

This interface must be implemented by all viewer applications. It forms the basis for the ADBV API, which is explained in more detail in the User Documentation of Appendix 3. ADBVsetClient is called to allow the viewer to communicate directly with the client program. ADBVstartView is called when the class has been loaded and created, and after ADBVsetClient has been called. ADBVstopView is called by the client if the viewer has to be closed down automatically (e.g. if the user logs off from the server without closing down the viewer).

CacheInfo

This class is used by the ADBVClientCache class above to store details about each viewer currently in the local cache.

ClassInfo

This class is used by the ADBVClientWindow to store details about each of the currently executing viewers and the ADBVClientClassThread class associated with them.

TableStore

This class is used to store local information about each table that the client can view. Each table entry is then added into a Vector data structure.

This package contains the classes needed for the Server program. The class ADBVServer can be executed to start the Server program. Once the server has been started, it starts listening for clients using a single ADBVServerListener thread. For every client that then connects, a new ADBVServerThread structure is created with which the new client can communicate directly.

ADBVServer

This is the main server class to be executed. It creates the user interface to allow connection to a template database, and starts a single ADBVServerListener thread to wait for clients wishing to connect.

ADBVServerListener

This class runs as a separate thread, to listen for clients wanting to connect to this server. Once a client is accepted, a new ADBVServerThread process is created and the client is directed to talk to that process directly. The clientsConnected function returns a boolean to say whether any clients are currently connected. The stopThread function is called by an ADBVServerThread when it wishes to be stopped (i.e. when its associated client disconnects). The stopListener function is called by the main ADBVServer class to stop this process from listening for any more clients.

ADBVServerThread

For each connected client, there is one instance of this class. Communication between the client and server is done through this instance, and all commands are picked up by the run method within the thread, which then passes any command arguments on to a separate function to execute the command.

TableStore

This class is used to store local information about each table contained in the template database. As with ADBVClient.TableStore, each table entry is then added into a Vector data structure.

This package contains the classes needed for the Tool program. The class ADBVTool can be executed to start the Tool program. The tool allows templates to be set up for one or more data databases within any one template database.

ADBVTool

This is the main Tool class to be executed. It creates the user interface to allow connection to any template database, or displaying of the editors necessary for editing templates and their associated viewers.

ADBVToolConnect

This class creates a window to allow selection of the data database to create templates about at any one time.

ADBVToolOptions

This class creates a window to allow updating of the options about the current template database.

ADBVToolTemplates

This class creates a window to allow editing of the templates contained in the current template database. On entry to the screen, the user is presented with the ADBVToolConnect window to make the initial data database connection.

ADBVToolViewers

This class creates a window to allow editing of the viewers to be used with the templates contained in the current template database.

ClassStore

This class stores a copy of all of the information about the viewers loaded into the current template database. This allows the information to be displayed quickly and simply, without having to query the template database every time the details need to be viewed.

TableStore

This class stores a copy of all of the information about the templates loaded into the current template database. This allows the information to be displayed quickly and simply, without having to query the template database every time the details need to be viewed.

ADBVButton

This class extends java.awt.Button to allow creation of buttons in a certain location on the screen and in a certain state with one single constructor call.

ADBVDDL

This class extends java.awt.Choice to allow creation of drop down lists in certain locations on the screen and in a certain state with one single constructor call.

ADBVLabel

This class extends java.awt.Label to allow creation of labels in certain locations on the screen and in a certain state with one single constructor call.

ADBVListBox

This class extends java.awt.List to allow creation of list boxes in certain locations on the screen with one single constructor call.

ADBVMsgBox

This class allows displaying of a message box with up to four lines of message and with three different button layouts - just an OK button; an OK and a Cancel button; a Yes and a No button. In each case, the runMsg function returns a value to specify which button was pressed by the user. This class is used throughout the three packages above to display error messages and questions to the user.

ADBVTextArea

This class extends java.awt.TextArea to allow creation of text areas in certain locations on the screen and in a certain state with one single constructor call.

ADBVTextField

This class extends java.awt.TextField to allow creation of text fields in certain locations on the screen and in a certain state with one single constructor call.

ADBVWAdapter

This class is used by all windows throughout the ADBV package. It extends the standard Java AWT window event handler allowing windows to be closed and, if necessary, notified that they are being closed.

ADBVWAdapterInterface

This interface is used by some of the windows in the ADBV package. The shuttingWindow function is called when the window is being closed, so that the related class can tidy up as necessary.

There is one external file, relating to the Client program, which is needed to store details about viewers in the local cache. This cache will store each class’ name, version and update date. To simplify reading and writing of the cache details, this file is just a flat text file, with one cache entry per line, with each of the three fields (class name, version, update date) separated by character 0.

To signify the end of the cache entries, a single ‘.’ on its own at the beginning of a new line will be placed at the end of the file.

To allow storing of template information, two database tables are needed. In addition, an extra table to store options is also needed.

T_CLASSES

Table T_CLASSES is used to store information and data about the external viewers.

It is currently created using the following SQL statement:

      CREATE TABLE T_CLASSES (
        IDCLASS        INTEGER    NOT NULL DEFAULT 0 PRIMARY KEY,
        IDPARENTCLASS  INTEGER    NOT NULL DEFAULT 0,
        CLASSNAME      VARCHAR    (255),
        DESCRIPTION    VARCHAR    (255),
        COPYRIGHT      VARCHAR    (255),
        VERSION        INTEGER    NOT NULL DEFAULT 0,
        UPDATEDATE     DATE,
        CLASSFILENAME  VARCHAR    (255),
        CLASSLOADED    SMALLINT   NOT NULL DEFAULT 0,
        CLASSDATA      BLOB       (65536)
      )
      

IDCLASS stores a unique value for linking each table to a viewer.

IDPARENTCLASS stores a value to be used for linking classes together. This field is currently not used, but is included so that more than one class can be downloaded to create a viewer. The primary class will be the only one executed, and the others will be stored on the local file system so that they can be used by this primary class in the usual way.

CLASSNAME stores the name of the class to be loaded.

DESCRIPTION stores a description about this viewer.

COPYRIGHT stores copyright information about this viewer.

VERSION stores a version number for the viewer.

UPDATEDATE stores a date value signifying the last time that details about this viewer were changed.

CLASSFILENAME stores the location of where the viewer is stored on the local file system. This is currently not used, but is intended to allow databases that cannot store viewers within them to use local files instead.

CLASSLOADED contains a boolean value to signify whether or not a viewer is currently loaded into the CLASSDATA field.

CLASSDATA stores the actual Java application to be used as a viewer.

T_TABLE

Table T_TABLE is used to store the template information for each object.

It is currently created using the following SQL statement:

      CREATE TABLE T_TABLE (
        IDTABLE        INTEGER    NOT NULL DEFAULT 0 PRIMARY KEY,
        IDCLASS        INTEGER    NOT NULL DEFAULT 0,

        TABLENAME      VARCHAR    (255),
        DESCRIPTION    VARCHAR    (255),
        CON_CLASS      VARCHAR    (255),
        CON_URL        VARCHAR    (255),
        CON_USER       VARCHAR    (255),
        CON_PASSWORD   VARCHAR    (255),
        UPDATEDATE     DATE
      )
      

IDTABLE stores a unique value for each object for which a template is required.

IDCLASS contains a value for referring to the viewer to be used.

TABLENAME stores the name of the object this template to which is referring

DESCRIPTION stores a description about this object, which will be presented the client users.

CON_CLASS stores the class name of the driver to use for connecting to the data database containing this object.

CON_URL stores the URL of the database to use for connecting to the data database containing this object.

CON_USER stores the User Name for use when connecting to the data database containing this object.

CON_PASSWORD stores the Password for use when connecting to the data database containing this object.

UPDATEDATE stores a date value signifying the last time that details about this template were changed.

The two template tables (T_CLASSES and T_TABLES) are related by the IDCLASS field (Figure 4.3.1):

Figure 4.3.1

T_OPTIONS

Table T_OPTIONS is used to store details about options relating to the template database

It is currently created using the following SQL statement:

      CREATE TABLE T_OPTIONS (
        AUTO_STARTUP   SMALLINT   NOT NULL DEFAULT 0,
        PORT_NUMBER    INTEGER    NOT NULL DEFAULT 8205,
        HAS_TABLES     SMALLINT   NOT NULL DEFAULT 1
      )
      

AUTO_STARTUP stores a boolean value to signify whether or not the client program will automatically show the first template specified using its viewer, or allow the user to choose which tables to view.

PORT_NUMBER stores the value of the port number that the server program should listen for clients on

HAS_TABLES stores a boolean value that is used to show that the three tables (T_TABLES, T_CLASSES, T_OPTIONS) have been created. This value is checked each time that either the tool or the server programs connect to the template database.

This section presents a plan for the testing of the Abstract Database Viewer. In using this plan, any inadequacies in the package will be found and reported on in the Test Report presented in section 6.2.

The general areas that need to be covered in the test plan are as follows:

• Program execution
  Check that the program executes successfully
• Tool
  Check that the tool allows template databases to be created and manipulated successfully
• Server
  Check that the server program works successfully
• Client
  Check that the client program works successfully
• Client / Server
  Check that the client / server interaction works correctly and successfully
• System
  Check that the whole system has a common look and feel and that all errors are contained within the programs

The two example tables below show the general layout of the tables used in the test plan.

The first shows all the tests to be carried out, ordered by test number and showing the corresponding test script numbers and the function that is being examined in each case.

The second table shows actual test scripts to be carried out, ordered by test script number. A description is given of how the test should be performed, followed by the criteria to be met in order for the test to pass.

These tests assume that the tool program has been started

These tests assume that the server program has been started

These tests assume that all programs have been started

This section presents a report of the testing of the Abstract Database Viewer as described in the plan of section 6.1.

The two example tables below show the general layout of the tables used in the test report.

The first table references the tests (as described above) by test number and script number and indicates whether this test passed or failed. If the test failed, then a report number is given, which links this table to the second table.

Test No.	Script No.	Pass / Fail	Report No.
EG1	1	Pass
EG2	2	Fail	1

The second table shows what went wrong and gives an indication as to the importance of fixing the problem. The ‘Report No.’ column links back to the ‘Report No.’ column in the first table.

The fix priority values can be 0, 1, 2 or 3, where each priority has the following meaning:

0. A fault which has been fixed (original priority given in brackets)
1. A major fault that needs to be fixed
2. A less major fault, but still a problem which needs to be fixed
3. A minor annoyance, which it would be nice to fix.

Test No.	Script No.	Pass / Fail	Report No.
PE1	1	Pass
	2	Pass
PE2	3	Pass
T1	4	Pass
	5	Pass
	6	Pass
	7	Pass
T2	8	Pass
	36	Pass
	37	Pass
T3	9	Pass
	10	Fail	1
	11	Pass
	55	Pass
T4	12	Pass
	13	Pass
	14	Fail	2
T5	15	Pass
	16	Fail	3
	17	Pass
	18	Pass
	19	Pass
	20	Pass
	21	Pass
T6	22	Pass
	23	Pass
T7	24	Fail	4
	55	Pass
T8	25	Pass
T9	32	Pass
	33	Pass
T10	26	Pass
T11	4	Pass
	5	Pass
	6	Pass
	27	Pass
T12	28	Pass
T13	29	Pass
T14	30	Pass
T15	31	Pass
T16	33	Pass
T17	34	Pass
	58	Pass
T18	35	Pass
	58	Pass
S2	4	Pass
	5	Pass
	6	Pass
	38	Pass
S2	39	Pass
	56	Pass
	57	Pass
S3	40	Pass
	43	Pass
S4	41	Pass
	57	Pass
C1	42	Pass
	43	Pass
	44	Pass
	45	Pass
C2	57	Pass
C3	46	Pass / Fail	5
	50	Pass / Fail	5
	52	Pass / Fail	5
	55	Pass / Fail	5
	58	Pass / Fail	5
C4	47	Pass
C5	48	Pass
	49	Pass
	50	Pass
	51	Pass
	52	Fail	6
C6	50	Pass
	53	Pass
	54	Pass
	55	Pass
	58	Pass
C7	53	Pass
C8	58	Pass
SYS1	60	Pass
SYS2	61	Pass

Report No.	Report Description	Fix Priority
1	Fields were editable, even though no viewer was selected	0 (2)
2	The viewer information was not saved, but no message was given as to why, and ‘blah’ was left as the version number	0 (2)
3	The filename was not reported as being invalid until after the program had tried to load the file	0 (2)
4	Viewer details not updated	0 (1)
5	Under IBM Visual Age for Java, which uses a version of JDK 1.1.2, the test passed without error. Under JDK 1.1.5, the Java application would not load properly, due to an Illegal Access Exception when the program tried to create a new instance of it.	0 (1)
6	All windows, except Default Viewer ones are closed.	0 (1)

Some development of a security mechanism is needed. This should be through client users sending a user name and password combination to the server for verification before they can gain access to the data.

Some development of a mechanism by which multiple Java classes can be stored for use by a single viewer is also needed. This could be done in one of two ways

1. Using separate records in the template database
   Each separate module could be stored in a separate record in the template database. These could be linked to a primary module that would interact with ADBV. The current T_CLASSES table contains this link, and comments in the code show where changes are necessary for this to be implemented.
2. Using the Java Archive (JAR) file structure
   This structure allows multiple modules to be stored in one flat file. This file could then be loaded into the template database and accessed using a similar mechanism to that currently implemented.

Communication between client and server systems in ADBV currently uses network sockets. Alternatively, the Common Object Request Broker Architecture (CORBA) [4, 11] could be used, allowing the viewer applications to be programming language independent. In addition, the client user would not need to enter the physical location of the server program, as the ORB provides the necessary mechanisms for location transparency [4].

Currently, the structure of the templates is a very simple link between object and viewer, which has to be created by the database administrator. This is done using the management tool, which displays the available objects, by reading a subset of the meta-data [2] contained within the database. The primary-key and foreign-key meta-data about each table could also be used to display the actual relationships between different tables, leading to a more meaningful representation of the data being created.