The following is a functional description of how the gui might be used; it doesn't say much about the literal appearance of the gui. It's meant to spur discussion, not to specify once and for all what gets implemented.

Initial inputs

1. path to an xml file describing the database
2. information required to connect to or create a physical database

The exact form of #2 depends on the physical realization. For a MySQL database, it consists of user id, password, host, and port. We could put this information into another file, separate from the one containing the database description. Might as well also be XML. Then the two filenames could be arguments to the gui, or the user could enter them after the gui started up.

Create or connect

For now, the only thing we need to support is connecting to the physical database. Once the connection is made, the application should check whether the schema of the physical database is equivalent or at least compatible with the one in the xml file. If it's not at least compatible the application has to abort or ask for a different set of connection parameters and try again.

At some point we might support creation of a physical database, in addition to connecting to an existing database, but this is not very high priority. We could also have some ability to display the database schema (as described in the xml file) before any association with a physical database, but I don't think it's worth the trouble. Let's hope anyone who wants to see what's in the xml description knows enough about xml and databases to just open the file in an editor and look!

Table selection

Once a connection has been made, the user should have access to a list of tables. This should be only the tables described in the xml file, which may be a proper subset of those in the physical database. In the initial implementation, all further operations are restricted to a single table (that is, each operation may mention only one table, but different operations may specify different tables). This is no real restriction (yet) because there only is one table.

Queries (SELECT)

In order to make a query the gui has to know

• table name
• column names for columns to be displayed (returned)
• additional constraints (WHERE clause), if any

As much as possible, for each part of this information the user should be presented with the set of allowable choices to choose from; typing data in by hand should not be required (though maybe it should be allowed for those who prefer it) unless absolutely necessary. So there should be a list of tablenames for the user to choose from. Once the table has been chosen, all column names can be presented so that the user can choose the ones to be returned, and so forth.

For the initial implementation of the gui, additional constraints may be built up of comparisons (the usual arithmetic comparisons =, <, etc.) and the logical operations NOT, AND, OR. Later we might also support EXISTS.

A comparison operation should be selectable (e.g. from a menu or set of radio buttons); we might also want to allow the user to type in a comparison. Since a comparison normally involves at least one column name, the column should be selectable from a list. Once a column name has been selected for one argument to a comparison, if the user wants to supply a constant for the other argument, e.g.

(s)he should be presented with a list of possibilities (for columns of enum datatype or for char or varchar columns which have a list of preferred values in their xml description) and a default value where one exists for the column.

When the user has entered all the information necessary for the query (or thinks so), (s)he will click on something saying "go" or "commit". At this point the entire query should be checked: are all column names legitimate? are values appropriate for the columns they're intended for? And so forth.

Add a new entry (INSERT)

• table
• column values for most columns

Update (UPDATE)

Niceties

• Cut and paste should be allowed wherever they make sense
• Display results from a query in new window (or tab); allow multiple results windows to be active concurrently.
• Provide browser when user needs to supply filename. However, also must allow typing since user can include environment variables of the form $(something)

What's missing

• Ability to accept some form of script input and maybe also direct query output to a file. Of course one can use the mysql command line program for this, but then none of the extra verification in rdbModel has been done.
• Log a session (at least a line for any command which accesses the physical database).
• Handle queries and updates which need to reference more than one table. This isn't an issue now, but, perhaps in some months, I'm thinking about adding another table (to keep track of validity periods of calibrations in a group rather than individually), in which case certain common operations would need to reference both tables.

Table columns, as defined for MySQL, look like this:

The table below reflects the point of view of the interactive user. We're only interested in those fields which cannot be sensibly set without human intervention. Some, like ser_no, can or must be set automatically to their proper values. Rows for these items are grey. They will not appear at all in the gui. Some others are only informational or unused. Users may set them, but they don't have much effect. These rows are yellow. If not set, they will get the default values appearing in the table. Of the remainder, some, like calib_type, although not enum as far as sql definition is concerned, are typically used as enum, with an option to supply something not in the standard list. This is denoted in the Type column of the table below by specifying enum and putting an ellipsis at the end of a list of specific values. Note Default here means something different than it did in the first table. Default of "none" means the user must supply something or the row will not be added.