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

[Vivien Malerba <malerba gnome-db org>]

On Sat, Feb 08, 2003 at 01:30:27PM +0000, Steve Fosdick wrote:
> 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.

Right, that's the point.

> 
> > 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.
> 

Yes, I agree with you on that. We could add a column right after the
"name" column (in the schemas that return information on an object , I'm talking
about the column which returns the name of the object) which contains
the namespace in which that object is located (or empty if the DBMS does
not support namespaces).

> > -> 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.
> 
> > 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?

I would like to have the same working environment the user has when he
simply runs the 'psql' (the equivalent of Oracle's sqlplus utility for Postgres):
there is a "default" envorinment in which many objects are accessible
using only their name (and not the namespace name). The other objects
are then accessible using the namespace.name notation.

I think this way of working should be transfered to mergeant (or any
other client application): users usually work in a "restricted"
environment to avoid having all the time too much objects to work with.
So the idea is to display objects which belong to this "restricted"
environment (objects which can be designed using only their name) and
also offer the possibility to "see" all the objects the user can work
on.

To enable this behaviour, the provider has to give an hint to the client
application to define the "restricted" environment. This is what this
flag is for.

> 
> > -> 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. 
>  

Right.

> > 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.


IMHO the client application has to receive an hint to know what the
default namespace for the user is, so the client application can start
with a nice current namespace. However, after the initialisation, it's
up to the client application to decide what is displayed (and for a safe measure,
the client can decide tu ALWAYS use the namespace.name notation).

Cheers,

Vivien