Eidetic Overview

Eidetic is a Perl CGI program for managing web interfaces to databases. It was developed originally two years ago, but was redesigned last year and over the past three months has been completely rewritten. A major motivation for the rewrite was a desire to replace the original database interface with DBI, and to incorporate a more sophisticated template system.

This article provides a brief overview of Eidetic's design principles and main features, and provides examples of how it is used at the Open Source Development Lab, and how it will be used at WorldForge and integrated with its software. I would also like to invite anyone with an interest in this to join me in further phases of development of Eidetic, and hope that this article provides sufficient detail regarding the objectives we'll be pursuing.

The vision for Eidetic is to provide a simple to use tool for creating, maintaining, and using database tables, online. The niche it is intended to fill is for small companies or open source projects that have specific table needs but do not wish to expend resources programming a number of database tools. One can realize the magnitude of this niche by noting how frequently companies are forced to turn to spreadsheet applications for personnel rosters, merchandise listings, and so forth, or how often open source projects find themselves manually maintaining similar kinds of information in raw HTML or text files.

One of the core principles is to maintain a firm distinction between the interface and code. An oft-cited complaint about Perl CGI's in the past has been the embedding of HTML into the Perl code, resulting in the undesireable situation of having to have a Perl coder on hand in order to make minor tweaks to web pages. At the opposite extreme are meta-languages like PHP and ASP, which instead of embedding HTML in the code, embed logic into the HTML, resulting in code which is just as difficult (or moreso) to maintain.

The approach Eidetic uses to realize this principle is to use Template-Toolkit, a widely used template system created by Andy Wardley. The current release of Eidetic supports the basic TT2 syntax, plus some Eidetic-specific extensions for embedding several tables into one page, generating commands, and creating dynamic pull-down select, radio, and checkbox lists; future releases will provide a more complete integration of TT2. TT2 syntax is reminiscent of PHP or ASP. Tags are identified with \[\% and \%\]. The syntax is well documented on Wardley's site, www.template-toolkit.org.

A second design principle for Eidetic was to make the database interface as simple as possible, and automate most if not all of the work one would need to do to manipulate database tables. In order to achieve this, an abstraction layer was built atop DBI to wrap typical database commands required by CGI programs into general purpose CGI commands: 'display', 'edit', 'store', and 'remove'. (Other commands can be added, but these four tend to represent the principle functionality required by most table-manipulation CGI's.)

The 'display' command allows viewing of a single record or a range of records. The selection of records to be displayed is controlled by 'on', 'query', and 'filter'. The 'on' parameter allows for specification of a particular record ID to display, or a range of ID's. For example, on=4-10 will display records 4 through 10 inclusive, on=15,18-20,25 will display records 15,18,19,20, and 25 (if they exist), and on=0- will display all records. The display command has a built in paging function, so that if there are more than 'maxitems' records. By default, maxitems is 25. When more than 'maxitems' records are available, "Next" and/or "Prev" links will be attached to the bottom of the page so that the user can browse through the lists. This command uses TT2-style files to define the look and feel of the page that the information is placed in.

The 'filter' property allows for specification of SQL filters that can be applied to the data. At present, this allows SQL WHERE code to be directly specified. For example, filter=descriptor='Tux2' OR descriptor='ACPI' will select records with descriptor set to either Tux2 or ACPI. The 'sort' property allows specification of the field that sorting should be done on. Reverse sorting is not currently implemented.

It is also possible to specify 'canned' queries via the 'query' command. Presently, these canned queries must be set up in the Eidetic perl code, but in a future release this will be configurable via a config file.

The 'edit' command works in much the same way as the 'display' command, except that it displays the data in a web form with widgets. It first looks for hand coded form templates, thus allowing users to manually optimize the page look and feel. If no such templates exist, then it will automatically generate one. The edit forms must use a 'd_' wart on the front of any application specific widgets.

'store' is set up to accept information from any form utilizing the 'd_' wart convention, and it places the information into the appropriate database table after performing data validity checking (currently, this is rather crudely implemented).

'remove' marks a record as being invalid; it does not permanently delete, although to the user's perspective it will be as good as gone. The reason for this approach is to provide a measure of protection against accidental deletion.

The abstraction layer loads the database table's schema from the database and parses it to determine field names, default values, and data types, and is able to use this information to create generic output pages and input forms. Currently, these automatically generated pages are not visually appealing, and are reserved mainly for administrative purposes. As well, the amount of type info that can be gleaned from SQL is limited (for example, there is not an easy way to specify a field is of type "phone number" or "email address"). One approach under consideration for dealing with these two issues is to introduce a meta-table that contains data regarding the fields in the various database tables. (This will be reminiscent of how database fields were handled in Eidetic 1.x)

Database tables managed by Eidetic are required to have the following seven fields: uid (unique ID) - this is an ID number either generated algorithmically or auto-incremented, used as the primary key for the table. This will probably be renamed to 'id' in a subsequent release, and restricted to auto-increment only. rsf (record state flag) - This is used by Eidetic to track the existance state of a field. Currently it is allowed to be either 0 (invalid), or 1 (valid). This is used for record removal - instead of being truly deleted, the records are simply marked. This is done in order to allow restoration in the case of an accident. Final purging can be performed periodically if table size needs to be managed. descriptor - this is a short tag used to describe or name the row. It is used by automatically generated drop down boxes, autogenerated lists, etc. Currently it is limited to 20 characters, but this may be expanded to 30 characters in a subsequent release. created_by - This is used to track the user ID of the individual who created the record created_date - This field is set to the record's creation date. last_updated_by - This keeps track of the ID of the last person modifying the record last_updated_date - This is set to the date of the last update of the record.

Any other database table fields can be specified, and will be treated as application data. A convention is to lowercase the seven Eidetic field names and upcase the first letter of application field names, but this is not a requirement. Field names containing '_uid' are treated as foreign keys into another database table. The naming of the field is used to automatically determine which table to link to, in one of two ways: The simple method is to use the form '[table]_uid', which will be
sufficient for most situations. The alternate method is to use the form '*_uid_[table]', which is useful if a table contains multiple foreign keys to the same table.

A planned near-term feature for Eidetic is "mapping tables", which contain the junction information that is required when normalizing a many-to-many relationship. These tables differ from the standard Eidetic tables in that they have two ID fields (which are named [table1]_uid and [table2]_uid) and the rsf field, but do not have the descriptor or creation and update fields.

Eidetic is currently used on the Open Source Development Lab website, where it fulfils several different needs: Project listings, user accounts, hardware scheduling, and a test automation system called the Scalable Test Platform (STP).

Eidetic 1.0 had been in use at WorldForge for a variety of purposes, including news, game design data, and task lists. Some desired applications for Eidetic 2.0 at WorldForge include a meeting schedule and agenda/summary system, an upload manager, and game content management. It is hoped that E2 can be interfaced to the game server and used to manage submissions of game content - archetype definitions, media, and other world data.

Bryce Harrington
September 1st, 2001