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

[Rodrigo Moya <rodrigo gnome-db org>]

On Thu, 2003-01-30 at 22:07, Steve Fosdick wrote:
> The first problem I have been trying to address with the Oracle provider
> is that some table names appear in the list of tables returned from
> get_schema(GDA_CONNECTION_SCHEMA_TABLES) but when
> get_schema(GDA_CONNECTION_SCHEMA_FIELDS) is called for the same table
> Oracle claims that the table doesn't exist.
> 
> The problem is related to the fact that in Oracle each user has their
> own schema so tables they create are in a separate name space to each
> other, but a user can grant another use access to his tables.
> 
> The get_schema(GDA_CONNECTION_SCHEMA_TABLES) method queries an Oracle
> view called ALL_TABLES which lists all tables which the calling user has
> the privilege to use.  This will normally include all tables owned by
> the calling user plus any that others have granted access to.
> 
> When asking Oracle to describe a table though, as done for
> get_schema(GDA_CONNECTION_SCHEMA_FIELDS) oracle looks only in the
> calling user's schema for the table concerned.
> 
what do you mean, it queries ALL_TABLES, or another view/table? (soory,
but don't have the code handy right now)

> I was able to implement a partial solution by getting Oracle to check
> for global synonyms - if what was supposed to be a table name turned out
> to be a global synonym, new code in gda-oracle-provider.c would look up
> the synonym and describe the table it points to.  This seemed to fix the
> majority of errors as most of the tables in ALL_TABLES not owned by the
> calling user are system tables for which synonyms do exist, but there
> are still some problems.
> 
> There is a related issue in that, because each schema is a separate name
> space, it is possible for the same table name to appear more than once
> in ALL_TABLES each time with a different owner but by the time the table
> list appears in mergeant one of the aparrant duplicates has been
> discarded even though they are two distinct tables.
> 
> So, how to make the table list onbtained from
> get_schema(GDA_CONNECTION_SCHEMA_TABLES) consistent with what can later
> be described with get_schema(GDA_CONNECTION_SCHEMA_FIELDS).  Here are
> some possibilities - what do you think:
> 
> 1. Use the USER_TABLES view instead of ALL_TABLES.  This would restrict
> the table list to those tables owned by the calling user rather than
> those accessible to the calling user.  The list of tables would be
> shorter but all would be describable.
> 
I'm not sure what would be best for Oracle, but other providers are just
returning the list of tables owned by the current user, or, in some
others, the tables availables in the current database.

We don't have a concrete rule for this in libgda, maybe we should add it

> 2. Have get_schema(GDA_CONNECTION_SCHEMA_TABLES) return fully qualified
> names for all the tables, in the form: schema.table  - these names would
> then be describle and tables of the same name in different schemas would
> now have distinct names.  The disadvantage is that the tables names in a
> typical list display such as in mergeant now look less clean and have
> redundant info in them.
> 
> 3. Have get_schema(GDA_CONNECTION_SCHEMA_TABLES) return short names for
> those tables in the user's own schema and fully qualified ones for those
> in other schemas.
> 
> 4. Have two names for a table - an internal name (the fully qualified
> one) and a display name (the short one).
> 
> 5. Have the Oracle provider cache the result of querying ALL_TABLES so
> it can deduce the owner when given a short table name.  This wouldn't
> resolve the issue of tables with the same name in more than one schema -
> we'd have to allow the table in the user's own schema to hide the other
> one(s).
> 
> 6. Require applications to provide the table owner when calling
> get_schema(GDA_CONNECTION_SCHEMA_FIELDS).
> 
hmm, this doesn't seem right, since some providers don't even know what
an owner is. Although, well, they could just ignore the parameter. But I
think it's not a good idea to have this requirement on clients.

> Obviously 4 and 6 would require changes outside the scope of the Oracle
> provider.  What do you think?
> 
> Appart from the above I have added the missing column (default value) to
> the output of get_schema(GDA_CONNECTION_SCHEMA_FIELDS) and now get a
> useable properties dialog from mergeant's tables view.  That does raise
> another question though - what should a provider return in columns which
> have no meaning for the database concerned - is there a convention for
> NULL values?
> 
yes, you should create a value of the expected type of value NULL, since
Mergeant checks for the types of the columns. Although I guess we should
be using gda_value_new_null (creates GdaValue's of type
GDA_VALUE_TYPE_NULL) for this kind of fields. Since setting right now
null values for types other than strings is quite difficult.


> Shall I post a patch here of what I've done so far?
> 
I'd say yes, since it might be easier later on to commit/review your
next set of patches.

As soon as you send some patch, please apply for a CVS account (mail
cvsmaster gnome org and add me on the CC).

cheers