Re: 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