db_flay 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.) db_flay 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.)
db_flay lets 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...
I have a PhD, but I can't quite comprehend Ruby on Rails and all the other snazzy "frameworks". Surely there must be something simpler, that a PhD can administer and a secretary can use? Well, hopefully db_flay is it!
That being said, db_flay has been adapted for use within the Plone CMS. See plonedbflay for details.
The basic features:
Fancy features for advanced applications:
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.)
A "quick search" field is implemented here (try searching for "74ACT"):
If you click submit with a blank form field, the full query form is brought up.
Here are two examples of db_flay pre-configured to dump an entire table (i.e., no query form is presented - just the data!):
Versions 1.00-1.03 were coded in Perl. Versions 2.00 and higher are in Python. Version 1.03 can still be downloaded, but the Perl codebase will not be maintained.
All normal configuration is handled through separate configuration files, which should be in the same directory as this script. The script should be called initially as:
http://your_server_path/db_flay.py?CONFIG=sampleand db_flay will read the configuration file sample.conf. (The ".conf" extension is added by the script. Do not include it in the URL). db_flay will remember which configuration file to keep using until you specifically tell it to use another configuration file. (Replace the "your_server_path" part of the URL with the correct domain name and path to the script, of course.)
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, using this general approach:
<form method="post" action="http://your_server_path/db_flay.py"> <input type="hidden" name="CONFIG" value="sample"> <input type="text" name="entered_data_text_partnumber" size="25"> <input type="submit" value="Part Number Search" name="Submit"> </form>
In this case, the config file is specified using a hidden variable (CONFIG). 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://your_server_path/db_flay.py?CONFIG=samplein this example) and studying the generated HTML code.
db_flay.py should live in its own directory, along with the configuration file(s). Your web server (I use Apache on FC4) should allow Python scripts to execute in this directory. Putting db_flay somewhere in the "cgi-local" directory will probably make it "just work", at least with common Linux distributions.
The db_flay.py script uses the Python time, cgi, re, sys, psycopg, and os.path modules. Most systems have all of them except for psycopg. On Fedora Core 4 pyschopg can be installed by running:
yum install python-psycopg
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. (Friends don't let friends use mySQL!). 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!
There isn't any, really. For additional security, consider using plonedbflay instead.
Access to this script should be limited to authorized users using the web server and filesystem permission schemes. This script is designed for modifying data, so anyone who can access it can delete your data! Also, be aware that the configuration files store the database user name and password in cleartext.
This script is intended for use on small internal intranets. Making it available via the public internet would be a very bad idea.
Screenshots are here. Don't worry about the Avtech logo - you customize the page start and page end HTML in the configuration file(s). My own site happens to use that logo.
Versions 1.00-1.03 were written by me, Dr. Michael J. Chudobiak (mjc@avtechpulse.com).
Version 2.00 was converted from Perl to Python by Dr. Hui Zhou, (hzhou@hzsolution.net) as a "RentACoder" job for me (Mike Chudobiak). Hui did a great job, and he is available for hire.
See the new, improved, more sophisticated version: plonedbflay. Development efforts are now focused on plonedbflay.
db_flay is named after Flay, manservant to Lord Sepulchrave, 76th Earl of Groan. You know - from Gormenghast.
db_flay is a web application. If you want to write local (non-web) applications, take a look at Axis. (Sounds like "Access" - get it?) Axis is a suite of open-source, cross-platform Perl modules that combine to provide a RAD design tool for database access. Looks pretty nifty to me!
I lasted edited this on January 21, 2008.