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

[Steve Fosdick <gnomedb pelvoux demon co uk>]

On Wed, 5 Feb 2003 09:24:57 +0100
Vivien Malerba <malerba gnome-db org> wrote:

> Hi!
> 
> Instead of trying to solve the problem in a specific way for Oracle or
> Postgres, here is a point of view from what information I would expect
> to find from the providers to use with mergeant.

Thanks for joining the discussion Vivien - for me the whole point of
raising the issue on the list was to ensure that whatever solution is
adopted works naturally with as many applications and databases as
possible.

> I would like to have the following  kind of information, for every
> object we want information on (Tables, views, sequences, triggers,
> ...):
> -> information on how to get the fully qualified name (ie in
>    postgres schema.table_name for example), this means getting
>    separately the name of the object and the name of the "namespace"
>    the object belongs to (so that namespace_name.object_name is the
>    fully qualified name of the object)

I think you would need to have the GDA_CONNECTION_SCHEMA calls return
an extra column in their gda data models.  You can't get the short name
first and go back later to get the fully qualified name because the
short name is not guaranteed to be unique - it can exist in more than
one namespace.  This is true of Oracle and Postgres and therefore
probably other databases too.

> -> for every object, a flag indicating if the object can be accessed
> using only the object name (without prepending the namespace name).

I think providers should find it easy to provide this flag but I'm not
sure what purpose it would serve.  Is it to provide a way to work with
providers that don't have namespaces?  If so surely the provider will
just return NULL for the namespace so the application will then know it
doesn't have to specify it in subsequent calls.

> -> for every object, a list of flags describing what the user which is
>    connected can do on the objects (select, ...)

Have you thought what flags?  We want to be sure to cover all the bases.
The ones that spring to mind are: SELECT, UPDATE, INSERT, DELETE,
ALTER, DROP.

> This kind of "logical view" would be IMHO very close to what Oracle
> and Postgres implement and allow the following features to be easily
> implemented:
> -> by default show only objects which the user has rights on and which
>    do not need to have the namespace prepended to be accessed

Ah, was that what that flag above was supposed to do?  Are you intending
that the application rather than the provider should filter the list to
present only those which are in the user's default namespace?

> -> show objects of a selected namespace as an option
> -> all the objects as an option.

This sounds sensible.  I think the "all objects" option would still have
to be "all object on which the connected user has some privilege".  if
not we would run into problems, either getting the complete list or in
describing the objects once they have been returned. 
 
> This idea necessitates the following implementations:
> -> implement the GDA_CONNECTION_SCHEMA_NAMESPACES schema to get
>    all the namespaces

Rodrigo Moya <rodrigo gnome-db org> wrote:

> hmm, but namespaces is PostgreSQL nomenclature. For Oracle, that would
> be schemas, for others might be different. Anyway, namespaces sounds
> ok to me, so if people implementing Oracle/other providers agree on
> using that name, let's do it.

When I checked the PostgeSQL 7.3 documentation it seemed to call them
schemas too and I got the distinct impression that schemas is a concept
from the SQL92 standard.  That said I am not bothered what we call them,
only that we have a clean implemenation that works.

> -> a modification of most of the schemas to add a column to return the
>    namespace to which the object belong to, and a column to describe
>    the rights on the object (except for the
>    GDA_CONNECTION_SCHEMA_FIELDS and
>    GDA_CONNECTION_SCHEMA_PARENT_TABLES schemas)

That sounds fine.

One of the questions in my mind when we started this discussion was
whether any idea of a current namespace/schema (as opposed to the
default one for the database connection) should be maintained by the
provider (like the current database) or by the application.  Your
suggestion is that the application do it and if you're happy with than
then so am I - it is more work within the application but also more
flexible.

Steve.