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';