pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedA few more JDBC meta-data questions...
5+ messages / 3 participants
[nested] [flat]
* A few more JDBC meta-data questions...
@ 1999-08-16 23:58 Mark Dzmura <mdz@phalanx.phalanx.net>
1999-08-17 11:19 ` Re: [INTERFACES] A few more JDBC meta-data questions... D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Dzmura @ 1999-08-16 23:58 UTC (permalink / raw)
To: pgsql-interfaces
Peter,
Thanks for your incisive replies to my earlier query.. I still have two questions.
(I wouldn't ask them if I was able to find the answers elsewhere, but I haven't
found them in the JDBC 1.2 or 2.0 specs, or the 2 books in my possession which
give some coverage to JDBC...)
From JDBC,
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??
For the latter, I believe that I could use regular queries against postgresq's system catalogs,
but I am primarily interested in portable approaches - generic SQL and JDBC which stand
a good chance of (eventually) working with other databases.
Thanks,
Mark Dzmura
--
)))) This email routed via a wireless gateway!! ((((
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] A few more JDBC meta-data questions...
1999-08-16 23:58 A few more JDBC meta-data questions... Mark Dzmura <mdz@phalanx.phalanx.net>
@ 1999-08-17 11:19 ` D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 0 replies; 5+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-08-17 11:19 UTC (permalink / raw)
To: mdz@phalanx.phalanx.net; +Cc: pgsql-interfaces
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';
--
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [INTERFACES] A few more JDBC meta-data questions...
@ 1999-08-17 11:39 Ansley, Michael <Michael.Ansley@intec.co.za>
1999-08-17 11:54 ` Re: [INTERFACES] A few more JDBC meta-data questions... D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 1 reply; 5+ messages in thread
From: Ansley, Michael @ 1999-08-17 11:39 UTC (permalink / raw)
To: 'D'Arcy\" \"J.M.\" Cain' <darcy@druid.net>; mdz@phalanx.phalanx.net; +Cc: pgsql-interfaces
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';
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] A few more JDBC meta-data questions...
1999-08-17 11:39 RE: [INTERFACES] A few more JDBC meta-data questions... Ansley, Michael <Michael.Ansley@intec.co.za>
@ 1999-08-17 11:54 ` D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 0 replies; 5+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-08-17 11:54 UTC (permalink / raw)
To: Michael.Ansley@intec.co.za; +Cc: pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [INTERFACES] A few more JDBC meta-data questions...
@ 1999-08-17 12:15 Ansley, Michael <Michael.Ansley@intec.co.za>
0 siblings, 0 replies; 5+ messages in thread
From: Ansley, Michael @ 1999-08-17 12:15 UTC (permalink / raw)
To: pgsql-interfaces
>> As someone else pointed out, you always have template1 to
>> query against
>> to get the full list of databases from.
That would have been me ;-)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-08-17 12:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-16 23:58 A few more JDBC meta-data questions... Mark Dzmura <mdz@phalanx.phalanx.net>
1999-08-17 11:19 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-08-17 11:39 RE: [INTERFACES] A few more JDBC meta-data questions... Ansley, Michael <Michael.Ansley@intec.co.za>
1999-08-17 11:54 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-08-17 12:15 RE: [INTERFACES] A few more JDBC meta-data questions... Ansley, Michael <Michael.Ansley@intec.co.za>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox