Thread: RE: [INTERFACES] A few more JDBC meta-data questions...

Most databases require that you use SQL to query for metadata.  If you are
using something like DAO on a Win32 platform, then you get a nice object
hierarchy which you can use to discover the data model, but otherwise you
are reduced to SQL.  And DAO can't give you a list of databases!!  This is
something that you have to have a connection for, bit of a catch22.  
However, this can normally still be written in a reasonably modular fashion.
If you have a set of interfaces which define the functionality that you
require, it is not difficult to create a different set of classes per db
platform.  You have to know which platform you are using in order to load
the driver, so you can load the correct set of classes at the same time.

Thoughts...


MikeA

PS: Useful set of queries.

>> 
>> Thus spake Mark Dzmura
>> > 1. Is it possible to programatically enumerate or discover 
>> the databases in a running database??
>> > 
>> > 2. Is it possible to programmaticaly enumerate or discover 
>> the tables in a database??
>> 
>> -- get list of databases                             
>> SELECT datname FROM pg_database;                     
>>     
>> -- get list of tables including views but not system tables
>> SELECT relname FROM pg_class
>>     WHERE relkind = 'r' AND                          
>>         relname !~ '^Inv' AND     
>>         relname !~ '^pg_';
>>     
>> And for completeness;                                
>>     
>> -- get list of attributes and types for a specific table
>> SELECT pg_attribute.attname, pg_type.typname
>>     FROM pg_class, pg_attribute, pg_type
>>     WHERE pg_class.relname = 'tablename' AND         
>>         pg_attribute.attnum > 0 AND
>>         pg_attribute.attrelid = pg_class.oid AND     
>>         pg_attribute.atttypid = pg_type.oid; 
>>     
>> -- get list of tables and primary keys (assumes no complex keys)
>> -- only includes tables with primary keys so no views
>> SELECT pg_class.relname, pg_attribute.attname        
>>     FROM pg_class, pg_attribute, pg_index    
>>     WHERE pg_class.oid = pg_attribute.attrelid AND
>>         pg_class.oid = pg_index.indrelid AND
>>         pg_index.indkey[0] = pg_attribute.attnum AND
>>         pg_index.indisprimary = 't';

Thus spake Ansley, Michael
> Most databases require that you use SQL to query for metadata.  If you are

Certainly true of PostgreSQL.

> using something like DAO on a Win32 platform, then you get a nice object
> hierarchy which you can use to discover the data model, but otherwise you
> are reduced to SQL.  And DAO can't give you a list of databases!!  This is

While SQL has its warts, it's not bad as a base.  Those queries I included
come from my PyGreSQL stuff (http://www.druid.net/pygresql/) so that I
simply do db.get_databases(), db.get_tables() or db.get_attnames(table)
to get the information.

> something that you have to have a connection for, bit of a catch22.  
> However, this can normally still be written in a reasonably modular fashion.

As someone else pointed out, you always have template1 to query against
to get the full list of databases from.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.