[gnome-db] Oracle, Table Names and Progress

[Steve Fosdick <gnomedb pelvoux demon co uk>]

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.

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.

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

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?

Shall I post a patch here of what I've done so far?

Steve.