gusdev-gusdev

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

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

I've just committed the preliminary GUS 3.0 schema into the shared CVS
repository on cvs.sanger.ac.uk (in GUS/Model/schema/oracle).  It's
preliminary because I haven't done a full test yet (i.e., check out a
clean copy from CVS and use it to create a new GUS instance.)  In fact,
I know there's at least one bug that was probably introduced by some
changes that Steve and I made to the build system earlier today.  These
changes should make it much easier to install the schema, because the
user/DBA is now presented with a single file that has to be customized
(with Oracle passwords, tablespace names, quotas, etc.).  Once that file
is customized, building the system will generate a site-specific set of
schema installation scripts that can then be run directly from SQL*PLUS,
without further modification.  However, I believe that these changes are
interacting in an undesirable way with another part of the build system,
and we have to debug the problem (which I don't think will take long.)
In the meantime, there is a simple workaround; when the build process
fails--complaining that somedirectory/Model/Core does not exist--simply
create that directory (and the corresponding ones for the other GUS
namespaces) and re-run the build command.

In any case, there are also a few other things that need to be cleaned up.
The installation documentation needs to be brought up to date and, as
mentioned above, I have to finish testing what's now in CVS before tagging
it as an official release.  In particular, I suspect that the schema
creation files may contain some illegal identifiers (e.g., some
automatically-generated names may exceed the Oracle-imposed 30 character
limit.)  We plan to tag the first release as version '3.0-1.0', to
indicate that the GUS schema version is 3.0 and the code version is 1.0;
this convention should make it easy to tell whether the schema has changed
in any given release.  We also plan to keep migration scripts (e.g. to
convert a GUS 3.0 database instance into a GUS 3.1 database instance) in
GUS/Model/schema/oracle/migrate.  Eventually, when we add support for
MySQL or PostgresQL, those files will go in GUS/Model/mysql or
GUS/Model/postgresql.  By the way, "Model" is Steve's abbreviation for
"data model", the idea being that this directory encompasses everything
that relates to our data model.  This includes both the database schema
and also any behavior associated with the "objects" defined in the schema
(i.e., the Perl and Java code.)

I think that's all I have to report for now.  Arnaud, I'm afraid I
haven't done anything about the repeat regions yet; it's on my the short
list of things to do once we get this initial release done.  If you have
any questions about the build process/install scripts before I wake up
today, give Steve a call.  Once the schema files have been successfully
"built", look at $GUS_HOME/schema/oracle/create-db.sh; this script uses
SQL*PLUS to run each of the individual .sql files in the correct order.
The output of each .sql file is logged to a corresponding .log file,
although I have yet to implement any kind of checking mechanism (for
example, to grep through the .log file and check that the correct number
of tables/views were created.)

Jonathan

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

[Arnaud K. <ax...@sa...>]

Hi Jonathan

Great, thanks for all you've done!

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

cheers
Arnaud

Jonathan Crabtree wrote:

>I've just committed the preliminary GUS 3.0 schema into the shared CVS
>repository on cvs.sanger.ac.uk (in GUS/Model/schema/oracle).  It's
>preliminary because I haven't done a full test yet (i.e., check out a
>clean copy from CVS and use it to create a new GUS instance.)  In fact,
>I know there's at least one bug that was probably introduced by some
>changes that Steve and I made to the build system earlier today.  These
>changes should make it much easier to install the schema, because the
>user/DBA is now presented with a single file that has to be customized
>(with Oracle passwords, tablespace names, quotas, etc.).  Once that file
>is customized, building the system will generate a site-specific set of
>schema installation scripts that can then be run directly from SQL*PLUS,
>without further modification.  However, I believe that these changes are
>interacting in an undesirable way with another part of the build system,
>and we have to debug the problem (which I don't think will take long.)
>In the meantime, there is a simple workaround; when the build process
>fails--complaining that somedirectory/Model/Core does not exist--simply
>create that directory (and the corresponding ones for the other GUS
>namespaces) and re-run the build command.
>
>In any case, there are also a few other things that need to be cleaned up.
>The installation documentation needs to be brought up to date and, as
>mentioned above, I have to finish testing what's now in CVS before tagging
>it as an official release.  In particular, I suspect that the schema
>creation files may contain some illegal identifiers (e.g., some
>automatically-generated names may exceed the Oracle-imposed 30 character
>limit.)  We plan to tag the first release as version '3.0-1.0', to
>indicate that the GUS schema version is 3.0 and the code version is 1.0;
>this convention should make it easy to tell whether the schema has changed
>in any given release.  We also plan to keep migration scripts (e.g. to
>convert a GUS 3.0 database instance into a GUS 3.1 database instance) in
>GUS/Model/schema/oracle/migrate.  Eventually, when we add support for
>MySQL or PostgresQL, those files will go in GUS/Model/mysql or
>GUS/Model/postgresql.  By the way, "Model" is Steve's abbreviation for
>"data model", the idea being that this directory encompasses everything
>that relates to our data model.  This includes both the database schema
>and also any behavior associated with the "objects" defined in the schema
>(i.e., the Perl and Java code.)
>
>I think that's all I have to report for now.  Arnaud, I'm afraid I
>haven't done anything about the repeat regions yet; it's on my the short
>list of things to do once we get this initial release done.  If you have
>any questions about the build process/install scripts before I wake up
>today, give Steve a call.  Once the schema files have been successfully
>"built", look at $GUS_HOME/schema/oracle/create-db.sh; this script uses
>SQL*PLUS to run each of the individual .sql files in the correct order.
>The output of each .sql file is logged to a corresponding .log file,
>although I have yet to implement any kind of checking mechanism (for
>example, to grep through the .log file and check that the correct number
>of tables/views were created.)
>
>Jonathan
>
>
>
>  
>

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

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

[Steve F. <st...@pc...>]

folks-

i have fixed the bug jonathan mentions below.

i am putting some finishing touches on the build system (stuff relating 
to the new configuration process).  i will be checking it in within the 
hour probably.  i'll let you know then.

in order to get going with the schema, you need to install gus and the 
build system.  see http://www.gusdb.org/BuildSystemSetup.html

steve

Jonathan Crabtree wrote:

>I've just committed the preliminary GUS 3.0 schema into the shared CVS
>repository on cvs.sanger.ac.uk (in GUS/Model/schema/oracle).  It's
>preliminary because I haven't done a full test yet (i.e., check out a
>clean copy from CVS and use it to create a new GUS instance.)  In fact,
>I know there's at least one bug that was probably introduced by some
>changes that Steve and I made to the build system earlier today.  These
>changes should make it much easier to install the schema, because the
>user/DBA is now presented with a single file that has to be customized
>(with Oracle passwords, tablespace names, quotas, etc.).  Once that file
>is customized, building the system will generate a site-specific set of
>schema installation scripts that can then be run directly from SQL*PLUS,
>without further modification.  However, I believe that these changes are
>interacting in an undesirable way with another part of the build system,
>and we have to debug the problem (which I don't think will take long.)
>In the meantime, there is a simple workaround; when the build process
>fails--complaining that somedirectory/Model/Core does not exist--simply
>create that directory (and the corresponding ones for the other GUS
>namespaces) and re-run the build command.
>
>In any case, there are also a few other things that need to be cleaned up.
>The installation documentation needs to be brought up to date and, as
>mentioned above, I have to finish testing what's now in CVS before tagging
>it as an official release.  In particular, I suspect that the schema
>creation files may contain some illegal identifiers (e.g., some
>automatically-generated names may exceed the Oracle-imposed 30 character
>limit.)  We plan to tag the first release as version '3.0-1.0', to
>indicate that the GUS schema version is 3.0 and the code version is 1.0;
>this convention should make it easy to tell whether the schema has changed
>in any given release.  We also plan to keep migration scripts (e.g. to
>convert a GUS 3.0 database instance into a GUS 3.1 database instance) in
>GUS/Model/schema/oracle/migrate.  Eventually, when we add support for
>MySQL or PostgresQL, those files will go in GUS/Model/mysql or
>GUS/Model/postgresql.  By the way, "Model" is Steve's abbreviation for
>"data model", the idea being that this directory encompasses everything
>that relates to our data model.  This includes both the database schema
>and also any behavior associated with the "objects" defined in the schema
>(i.e., the Perl and Java code.)
>
>I think that's all I have to report for now.  Arnaud, I'm afraid I
>haven't done anything about the repeat regions yet; it's on my the short
>list of things to do once we get this initial release done.  If you have
>any questions about the build process/install scripts before I wake up
>today, give Steve a call.  Once the schema files have been successfully
>"built", look at $GUS_HOME/schema/oracle/create-db.sh; this script uses
>SQL*PLUS to run each of the individual .sql files in the correct order.
>The output of each .sql file is logged to a corresponding .log file,
>although I have yet to implement any kind of checking mechanism (for
>example, to grep through the .log file and check that the correct number
>of tables/views were created.)
>
>Jonathan
>
>
>
>
>-------------------------------------------------------
>This SF.NET email is sponsored by:
>SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See!
>http://www.vasoftware.com
>_______________________________________________
>Gusdev-gusdev mailing list
>Gus...@li...
>https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>
>
>  
>