TableModel

 

A table model is a model for a table and other UI widgets in a user interface. It is an intermediary that sits between the table on the screen and the actual data source. For example, the table model for a list of customers would be an Adaptor for the list of customers so that widgets on screen can be initialised to use the table as a model with little effort.

 

A Java version of this that I've written is here: datamodel.dev.java.net. It can use either a direct JDBC connection to an SQL database, or a collection of some sort. The collection would typically be persisted using Hibernate. The architecture is as flexible as possible to allow lots of different kinds of hacks. To use it, the user subclasses either JDBCDataModel or ListDataModel and defines behaviour in the subclass (i.e. columns, initialisation of new rows, etc). That project also includes many customised Swing widgets: text fields, drop-down boxes, etc.

 

Eventually I plan to write a Smalltalk equivalent.

 

Design

 

   UI widgets

          |

    TableModel

         |

 Data source (SQL or List).

 

 

UI Widgets

 

The UI widgets are initialised to use the TableModel and a column in that TableModel. That's all the initialisation and custom behaviour they need; the updating of values, validation of values (e.g. currency format), committing of values to database and so forth is all handled by the TableModel.

 

A Table can be put on the UI in much the same way. The TableModel defines which columns are "visible" so that they would appear on screen in a table.

 

Example widgets are:

• Buttons with special implementations: add, delete, commit/apply/ok, cancel/clear/reset.
• Text fields. These should turn pink/red when the table model's validation of the entered value fails (e.g. invalid characters entered). Specialised text fields include money fields, integer fields, float fields. Text fields could be single or multi-line.
• Date fields; these include input validation and a drop-down calendar. The behaviour of the calender regarding invalid dates should be defined in the column.
• Check boxes; these use a boolean column in the table model for their target. They derive their label from the column they're derived on.
• Combo box fields / drop-down fields. These get their selectable values from their column (which in DataModel is a ComboColumn). Those values may be fixed, or may come from another SQL table or list that could change over time.
• Labels: these are static labels that derive their name from the column they're defined on.
• Tables (of course). More about these below.
• Search widget. These include a list of columns to search under, the text area and a "search" button.
• Navigation widget. These have "tape recorder" controls allowing the user to go to the next and previous rows.

 

Tables

 

On-screen tables are the most powerful of the widgets above. They should support:

 

• Selecting the current row makes that row selected for all widgets using that table model as their model.
• Rows can be sorted by columns.
• Rows can be searched... somehow?
• Columns can be resized and re-arranged, but only on this table. This has no effect on the table model.
• Grouping by column?
• In-place editing is favourable.
• Importing and exporting data or selections of data (selection by cell, row, column, rectangle of cells, all cells).
• A table should ideally include adding and deleting controls. Whether these are visible and enabled should depend on the table model.

 

TableModel

 

The TableModel itself needs to be able to handle:

 

• Management of the "currently selected row".
• Edited but uncommitted values of the currently selected row.
• Having no row selected (the initial state): all widgets should appear disabled.
• Being in a state of having a new row which does not yet exist in a database.
• Having "sub-tables"; for example, one of the "columns" of a customer might be a reference to a list of items that customer has. If that list is another table, it must be updated when the selected customer changes.
• Adding a new value would involve initialising values in that new value, especially when adding a value might not actually create an instance as such (this is how the current implementation works - an array of new column values is used instead).
• Deleting a new value may or may not actually remove that item. Instead, it might simply set a "deleted" flag and cause the table to no longer be able to "see" that value (i.e. set a "deleted" flag in the SQL table and make the select statement filter these out).
• Searching for a particular value must be possible.
• Some sort order should be possible. Perhaps have a list of columns and their sort order?

 

In the DataModel implementation in Java, there are a series of ColumnDefinition subclasses which define custom behaviour for each column type (integer, string, date, combo box, etc).

 

It is very common for a column to include a foreign reference to another table. In this case, the column's value would only be an ID of some sort, and ideally the value from the foreign table would be displayed on screen. There are several cases:

 

• The foreign key only references a simple, single value and only one value at a time is retrieved, such as being a table containing only an ID and a string. In this case, the SQL statement should include a join to that table. There are two ways of editing this: edit the value in the joined table, or change the ID to be a new value (i.e. use a combo box).
• The foreign key might reference one entry in another table with many fields. Each of these could become another column in the local table, and may or may not be editable. Each of these might be editable.
• The foreign key might reference multiple entries in another table. Typically, a separate table model would be used in a child-parent relationship. Editing operations include adding and removing values.

 

Combo Boxes

 

A simple combo box is a link to a table that changes the ID of a field in the selected row to one of many alternatives. The alternatives could be:

• statically declared, e.g. a list of countries.
• retrieved from a column from anothere datamodel.
• dynamically declared depending on the value of another column.

 

Another type of combo box does not modify a field's value of the selected row, but rather actually changes the selected row to be another selected row. This allows a combo box to be used in place of a JTable.

 

Search Widgets

A search widget is a small widget with a column selector (a drop-down list), a text or date field, and a "Search" button. When the user enters text in the drop-down list, the model of the search widget is given a filter: the chosen column must contain text matching the query.

• The text entry should change to whatever is relevant for the current column:  a check-box for booleans, a date selector for dates, etc.
• The current search text should be stored in the table model. This search field might be set programatically, such as when the details of a particular customer are wanted on screen:  in this case, the application pops up the customer dialog and sets the "Customer ID"  field to that particular customer.
• If the search text changes (programatically or via some other mechanism), the values in the search boxes should change.
• Optionally, advanced searches might be allowed:  case-sensitivity, values in ranges (greater-than, less-than), multiple search items with boolean operators, regexes, lists (WHERE x in (1,2,3,4)  ), joins such as "SELECT  invoice_id FROM invoice, customer WHERE ... AND cust_city in (AUCKLAND, HAMILTON)".

   

Filtering

A distinction needs to be made between searching and filtering.

 

Searching doesn't modify the number of items shown, but selects the next item that matches.

 

Filtering changes the list of items to only show those matching a particular condition.

 

Joins

Parent/Children join (1:n)

This type of join occurs when a particular parent points to zero or more entries in another table. This is implemented by using a child table model that is updated with a filter every time a new item from the/a parent table model item is selected, and where the children of that parent are exclusive to that parent.

 

For example, a customer has many orders. The orders table model will have a filter on it to limit the items it shows to be those from the currently selected customer table model. In this case, any newly created child will belong to that currently selected parent. The child can not be associated with a different parent.

 

Associative join (n:n)

This is much the same as a parent/children join except that the associations between parents and children can change. Children can be associated with other parents.

 

For example, a users table model would be associated with a groups table model. In the database, an intermediary table would map one to the other. Users might belong to any number of the groups, and each group might have any number of the users as members.

 

1:1 join

This is where each parent has exactly one child. This might occur when another table has been made to suppliment information from the first table. This is also used in inheritance.

 

For example, each entry in a staff member table model might have an entry in the person table model, but not vice versa.

 

Selection table model

This is where a particular column in a table model would point to a particular entry in another table model, and that association could be changed. This is typically represented visually with a drop-down combo box, or a "select item" modal pop-up. This differs from the parent/children join in that there is only one entry from the child table model being referred to.

 

Usually, the values shown to the user will differ from the IDs used. The most efficient way to implement this would be to join two SQL tables to gather the user-presentable names for the joined IDs.

 

For example, a customer might be from a particular country. In this case, the "country" column of the customer table model would refer to an entry in the country table model.

 

Grouping

This is where a table would be presented where a parent is showen in a table column as a manipulatable tree, and each parent item has multiple children items.

 

For example, customers would have orders; customer name would be in the first column as a tree, and each branch would be in the second column as a list of each customer's orders.

 

The most efficient way of implementing this is to generate a SELECT statement with an appropriate JOIN and then process the results.

 

Testing

• When the application is initialised, which row should be selected? None?
• When there are no rows or a search with no rows is used, there is no row selected. All code must assume that getSelectedRow() may return null.
• When an item is added, it must also be selected (searched for).
• Don't commit anything to the database until the user hits "apply". Although - maybe it would be nice to commit something which can be undone if the user hits "cancel".
•