plonedbflay, a Plone-based database browser

Introduction

plonedbflay is Plone 3.x add-on product that generates a simple - but productive - web interface for databases. The target user is a secretary, rather than a database administrator. (You will need a database administrator to configure the script initially.)

plonedbflay let users view data, edit data, copy data, and delete data in multiple databases.

The copy feature is especially useful as a time-saver! For some reason, many other database tools leave this feature out...

plonedbflay is designed primarily for use with Postgresql. The code supports mysql, but it hasn't really been tested thoroughly.

Notable Features

The basic features:

• View, edit, copy, delete data in multiple databases.
• Generates query forms for searching through the database.
• Pre-defined queries can be used instead of a query form.
• Can pre-populate query form fields with foreign keys, or
• can pre-populate query form fields with existing data.
• Can auto-increment serial-type fields when adding new data.
• Pre-defined sorts, or click on field label to sort by that field.
• *, % and spaces are interpreted as multi-character wildcards.

Fancy features for advanced applications:

• Apply regexp translations to certain fields - for instance, to convert part numbers into weblinks to related external websites (like an online catalog). You can see an example of this in the screenshot above - the Digi-Key stock numbers (which happen to end with "-ND") are formed into hyperlinks to the Digi-Key web site!
• Prefix data in certain fields with a URL (for instance, when the database stores a file name)

The demo site implements a manufacturing parts database. The data is bogus - add / edit / delete whatever you want. (Foreign keys are used on the supplier, productcategory, and manufacturer fields, so the allowed values are limited in these fields.)

• Full query form example

• A "quick search" field is implemented here (try searching for "74HCT"):

   

   

  If you click submit with a blank form field, the full query form is brought up.

Here are two examples of plonedbflay pre-configured to dump an entire table (i.e., no query form is presented - just the data!):

• List all manufacturers.

• List all suppliers.

Installation

Configuration

Installation adds a "DBFlay Config" content type to Plone, so you can add this to any folder. Once you have done so, the "Edit" tab is available for setting the basic configuration of your database instance. (You can have many "DBFlay Config" instances, to access different databases/tables). The main entries should be self-explanatory. Sample values, with annotations:

Host: www.avtechpulse.com
(The name or IP address of your Postgresql database server.
This entry is required.)

Database: office
(The database within Postgresql.
This entry is required.)

User: demo
(Connect as this user.
This entry is required.)

Password: demo
(Connect with this password.
This entry is required.)

Table Name: parts
(Database tables to access.
This entry is required.)

Allow List of All: no
(Just list everything in the table? If not, a query form is provided.
A yes/no value is required.)

Key Fields: id
(You must specify a primary key.
This entry is required.)

Auto-Increment Fields: id
(When you add a new item, numeric fields can be auto-incremented.
The entry is optional.)

Query Fields: supplier, productcategory, manufacturer, partnumber, notes, id
(What fields should the query form allow searches against.
The entry is optional - all fields are used by default.)

List Fields: *
(Which fields should be returned from searches?
The entry is optional - all fields are used by default.)

Prepopulate Fields:
(In query forms, do you want certain fields to be 
drop-down boxes based on the existing data?
This entry is optional.)

List Order:supplier ASC, productcategory ASC, sortingoverride ASC,
partnumber ASC, qty DESC
(How should the data be sorted?
This entry is optional.)

Where Clause:
(You can skip the query form, and just use this SQL where clause.
This entry is optional.)

URL Attachment: file, http://www.domain.avtechpulse.com/intranet/quotes/
(Here, data in the "file" column are prefixed with the shown base URL.
This entry is optional.)

Foreign Keys: supplier, suppliername AS supplier FROM suppliers_table, 
productcategory, productcategory from productcategorylookup,
manufacturer, manufacturer from manufacturer_table
(A list of SQL foreign keys. 
These fields will be drop-down boxes in query forms.
This entry is optional.)

Translation Condition Field: supplier
(Here, the supplier field will be checked to see if it
is equal to a particular value in the keys below. If it
does, a regex translation will be applied to the field
specified below (a different field, generally). 
This entry is optional.)

Translation Field: partnumber
(Here, the partnumber field will have a regular 
expression substitution applied to the data, if the
supplier field (specified above) meets the criteria given
below. This entry is optional.)

Translation Regex Patterns: DIGI-KEY, s/([A-Z0-9\-\.\/]+\-ND)/<a href="http:\/\/ca.digikey.com\/scripts\/DkSearch\/dksus.dll?PName?Name=$1\&Site=CA">$1<\/a>/
(When the "supplier" field specified above has the value "DIGI-KEY",
then the perl substitution (starting with "s/") will be applied to the
data in the "partnumber" field. In this example, this feature is used to insert 
hyperlinks on part numbers, pointing to the Digi-Key web site.
This entry is optional.)

Running a Query

Try using the above settings in the "Edit" tab. Then click on the "Run Query" tab. This will bring up a query form.

You might wish to add a form on an external page, to implement a "quick search" feature, instead of relying on the script to generate its own query form. This is easily accomplished. For instance, if the full query form cab be normally accessed at

http://www.avtechpulse.com:8080/demo/db/parts/rundbflay

then a "quick search form" would look like:

<form method="post" 
	action="http://www.avtechpulse.com:8080/demo/db/parts/rundbflay">
	<input type="text" name="entered_data_text_partnumber" size="25">
	<input type="submit" value="Part Number Search" name="Submit">
</form>

Note the special naming of the "entered_data_text_partnumber" text field. The name is specially constructed to tell the script that it is user-entered data ("entered_data") of text type ("text") that relates to the "partnumber" field in the database. The easiest way to tell the proper name for a particular form field is to bring up the basic query form (using

http://www.avtechpulse.com:8080/demo/config/parts/rundbflay

in this example) and studying the generated HTML code.

Important Note: Plone has a "safe_html" filter that normally strips out <form> and <input> tags from pages by default. If you want to use the "quick search" form fields, you must re-enable these tags. In Plone 2.1.2, these tags can be enabled at /portal_transforms/safe_html/manage_main (apparently - I haven't tried it, I have an earlier version). In earlier versions, you must edit the ..../Products/CMFDefault/utils.py file by adding the "form" and "input" tags to the VALID_TAGS array (and then restart Zope).

Internally, the script communicates with itself using CGI requests. Some variables are stored in "hidden" form fields. This makes the use of server-enabled session tracking unnecessary, and keeps things simple. It also means that things still work (mostly) if the user navigates with the "back" button in the web browser.

db_flay is designed and tested for use with Postgresql databases. It may work with other databases, but changes might be necessary. Specifically, db_flay uses SQL keywords such as "ILIKE" and "LIMIT" which might not be implemented in other databases. Beware!

Security

plonedbflay is subject to the usual Plone workflow / permissions settings. If you aren't familiar with them, then buy a copy of McKay's "The Definitive Guide to Plone". No additional security is implemented.

Download

The files, included dependencies, are available here. The key file is DBConfig-0.4.5.tgz. The other files are API references, install guides, and dependencies.

History

Versions 1.00-1.03 of db_flay were written in Perl by me, Dr. Michael J. Chudobiak (mjc@avtechpulse.com), for internal use in my company.

Version 2.00 of db_flay was rewritten in Python by Dr. Hui Zhou, (hzhou@hzsolution.net).

Dr. Zhou then adapted db_flay for use with Plone (= plonedbflay). The first public release of plonedbflay is 0.3.

Eurotux was contracted to upgrade db_flay to work with Plone 3.x. This resulted in version 0.4.4.

To Do