Re: Web-Based Reports (cgi using mod_perl)

>Date: Tue, 12 Jan 1999 17:04:44 -0700
>From: Dave Van Abel <dave(at)vanabel(dot)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(at)inescn(dot)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