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.