Re: [gnome-db] Oracle, Table Names and Progress

[Steve Fosdick <gnomedb pelvoux demon co uk>]

On 01 Feb 2003 17:39:49 +0100
Rodrigo Moya <rodrigo gnome-db org> wrote:

> On Sat, 2003-02-01 at 02:16, Steve Fosdick wrote:
> > 
> > Is this what the SCHEMA_DATABASES category is supposed to do - offer
> > different sets of tables/views which are available without changing
> > the connection to the server (including who we are logged in as)?
> >
> exactly. Usually, those would be real databases, as it is for postgres
> and mysql, for instance. I was thinking that the oracle provider could
> simulate databases by using Oracle's schemas.

When you mentioned postgres namespaces it ocuured to be that this may be
closer in concept to what we need to do with Oracle than using the
SCHEMA_DATABASES category.

I have looked at the postgres documentation for version 7.3 and my view
was still further confirmed - the documentation seems to call these
namesspaces schemas and these seem to map to oracle schemas (users) very
well, perhaps because they seem to be part of the SQL92 standard.

I have also looked at the implementation within the postgres provider
intending to use it as an example and I am not sure it is quite correct
for either postgres or oracle but rather than jump to conclusions I would
like to be sure.  I don't have postgres 7.3 installed (mine is 7.2.1) so
could someone who has please run the query below and send me the output?

SELECT c.relname, u.usename, pg_catalog.obj_description(c.oid), NULL
FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n
WHERE u.usesysid=c.relowner AND c.relkind = 'r' AND c.relnamespace=n.oid
AND pg_catalog.pg_table_is_visible (c.oid)
ORDER BY relname

[If you know the query from the where it appears in the postgres
provider I have deliberately missed out the 'AND n.nspname NOT IN
('pg_catalog', 'pg_toast')' phrase to guarantee that we get tables from
at least two namespaces.]

> hmm, right. Or we could just leave them unimplemented in oracle,
> although well, that would be a strange thing to have to call a
> (future) drop_user for a schema returned in the DATABASES schema. Not
> sure what to do, any other idea?

That would seems strange which is one reason I think namesspaces may be
a better way to go.

Steve.