Web-Based Reports (cgi using mod_perl)

Date: Tue, 12 Jan 1999 17:04:44 -0700
From: Dave Van Abel <dave@vanabel.com>
Subject: Web-Based Reports (cgi using mod_perl)

Here is what I want to do:

CGI script opens a dbms
Reads in all table names
Reads each field of table
may as well get indices, too, if can be done
Now create web page that has selection of table & fields for report.
Eventually add SELECT criteria.
User now selects TABLE & FIELDS for report
Now execute report to browser.

Remember that you can list tables (and other entities) with

select tablename from pg_tables
where tableowner='cgi_db_admin'
and tablename like 'cgi_db_%';

(pg_table is just a view, but saves some time and writing...
tableowner cgi_db_admin is used only as an example; you could
of course use only 'cgi_db_' or 'foo_' to identify all the tables you
want to list in the web.
)

and you can list attributes of each table with

select relname, attname
from pg_attribute, pg_class, pg_tables
where
attrelid=pg_class.oid and -- relate attributes to each table
relname=tablename and -- only the relations selected in...
tablename like 'cgi_db_%' and -- tables starting with 'cgi_db_'
attname like 'cgi_%' -- attributes starting with 'cgi_'
;

You could also hide attributes using something like
attname not in ('cmax','xmax','cmin','xmin','oid','ctid');

I've just tested this... it works! :)

The rest of the process, cgi, generate html, process forms, etc, would be
as usual.... I suggest you use a list for tables and check boxes for
attributes.

Of course one problem arises: attributes list should update in real time
as user selects table name... for this, you can use java or javascript. If
you can only use cgi/form/html, the user must first select the table,
submit, and only then have a list of attributes...

Hope this helps!

Any alternative ideas ?

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
`````````````````````````````````````````````
Silvio Emanuel Nunes Barbosa de Macedo
mailto:smacedo@inescn.pt

INESC - Porto - Grupo CAV
Pc da Republica, 93 R/C Tel:351 2 209 42 21
4000 PORTO PORTUGAL Fax:351 2 208 41 72