Re: [Gusdev-gusdev] Preliminary 3.0 schema files in CVS.

[Jonathan C. <cra...@pc...>]

Arnaud-

Arnaud Kerhornou wrote:
> Can you clarify the structure of the Oracle instance ?
> As far as I understand GUS30 has 5 namespaces which actually are 
> implemented in Oracle as schema names or in other words as users.  To 

Yes, GUS30 currently has 5 namespaces (Core,SRes,DoTS,TESS,RAD3), each
of which is mapped to an Oracle user/schema of the same name.  Four of
the five namespaces (Core,SRes,DoTS,TESS) actually map to two Oracle
users, one of which is used to store the "version" tables for that
namespace.  In other words, the current mapping from GUS30 namespaces
to Oracle users/schemas looks like this:

Core  -> Core,CoreVer
SRes  -> SRes,SResVer
DoTS  -> DoTS,DoTSVer
TESS  -> TESS,TESSVer
RAD3  -> RAD3

Note that the capitalization *is* significant, because while Oracle is
case-insensitive (and, on occasion, just plain insensitive :), we do store
case-sensitive names in Core.DatabaseInfo.name and, furthermore, the Perl
and Java package names (which correspond to the GUS namespaces) are
case-sensitive too.

Given that each GUS instance maps to these 9 Oracle schemas, one question
is how to support multiple GUS instances running in the same Oracle
instance.  The schema names have to be distinct, so you would have to do
something like the following (where GUS1 and GUS2 can be considered
logical names for the two GUS instances):

GUS1 -> GUS1Core,GUS1CoreVer,GUS1SRes,GUS1SResVer,etc.
GUS2 -> GUS2Core,GUS2CoreVer,GUS2SRes,GUS2SResVer,etc.

I'm using a common prefix (GUS1 and GUS2, respectively) to group the schemata,
instead of a common suffix, for two reasons:

   1. Schemas named in this manner will be grouped correctly when sorted
      lexicographically (e.g., by an SQL ORDER BY clause)
   2. It's possible that some of the code assumes that the name of the
      "version" schema (e.g. DoTSVer) can be obtained simply by appending
      "Ver" to the main schema name (e.g. DoTS).

However, I should note that while the schema creation scripts were designed
to allow you to rename the Oracle schemas (e.g. you can rename Core to MyCore
and CoreVer to MyCoreVer), I don't think we can guarantee (yet) that the
rest of the code will support this.  If we ever want to support multiple
GUS instances in the same Oracle instance, this will have to be done, but
we're not there yet.

> access the different schemata there are two users, GUSrw, which has 
> read/write access to all of them, and GUSdevReadOnly, which has read 
> access only.
> Is that correct ?

That's how our copy of GUS is configured, but I haven't included these users
(GUSrw and GUSdevreadonly) in the schema creation scripts.  It's quite
possible that other sites will have other requirements, and so perhaps
this issue would be best addressed by providing a script that makes it
easy to create a new Oracle user with SELECT and/or UPDATE/INSERT/DELETE
permissions on a set of specified GUS namespaces.  I already have a script
that makes it easy to grant permissions on a set of tables, so perhaps
this, in addition to some documentation, will suffice.

Jonathan