UpdateAWorkingGusInstallation

Describe UpdateAWorkingGusInstallation here. e wanted to enter new data into GUS. Mass Spec data. This required the use of a plug-in that was provided by Martin Fraunholz. The plugin needed two perl modules that we did not have. DoTS::MassSpecSummary and DoTS::MassSpecFeature. These are plug-ins of the type that are automatically generated by a gus install with regards to the tables that are in the GUS schema

We did not have these modules in our $GUS_HOME/lib/perl/GUS/Model/DoTS/ directory, because we did not have these tables in our schema. Obviously this required us to update our gus installation.

So this is what we did:

1. Create the tables

• Very important to add the primary key constraints

2. Add the tables description to Core.TableInfo

• Very important to describe correctly the table for perl objects

automatic generation

3. Download GUS from CVS

4. Build

• If the build resets all sequences to 1, we have a clue on how to

fix them.

Describing the steps in detail:

1. Create the tables

If the table that you want to create has been incorporated into later versions of GUS, then chances are you can find the sql to create the table somewhere in the latest version of GUS from CVS.

Look in

$PROJECT_HOME/GUS/Model/schema/oracle/

There are 6 types of files containing the sql commands for each schema.

For example, for DoTS schema:

   dots-constraints.sql
   dots-indexes.sql
   dots-pkey-constraints.sql
   dots-sequences.sql
   dots-tables.sql
   dots-views.sql

• Create table commands

  Example:

create table dots.massspecsummary(
    mass_spec_summary_id  number(12) not null,
    aa_sequence_id number(12) not null,
    prediction_algorithm_id number(12) not null,
    external_database_release_id number(12) null,
    developmental_stage varchar2(20) not null,
    is_expressed number(1) not null,
    number_of_spans number(12) not null,
    sequence_count number(12) not null,
    spectrum_count number(12) not null,
    aa_seq_percent_covered float(22) not null,
    aa_seq_length number(12) not null,
    aa_seq_molecular_weight number(12) not null,
    aa_seq_pi float(22) not null,
    modification_date date not null,
    user_read number(1) not null,
    user_write number(1) not null,
    group_read number(1) not null,
    group_write number(1) not null,
    other_read number(1) not null,
    other_write number(1) not null,
    row_user_id number(12) not null,
    row_group_id number(3) not null,
    row_project_id number(4) not null,
    row_alg_invocation_id number(12) not null)
    storage( maxextents unlimited );

• Add the primary key constraints.

  If the table has a primary key, you must make sure to enter the constraint.

        alter table dots.massspecsummary add constraint PK_MASSSPECSUMARY
        primary key (MASS_SPEC_SUMMARY_ID);

• Add the foreign key constraints, create the views, indexes, and

sequences.

Again the sql for this can usually be found somewhere in

$PROJECT_HOME/GUS/Model/schema/oracle/

2. Add the tables description to Core.TableInfo

The description of the new tables then need to be entered into Core.TableInfo, since this is the place that the GUS installation will look for tables to automatically create modules for. It is imperative that you set the attributes correctly, otherwise the build will not work.

        table_id -> Unique identifier to the tables (automatic generated by
        SubmitRow plug-in).
        name -> Name of the table without the schema prefix 'Schema::'. eg: for
        DoTS::MassSpecSummary, the table name is MassSpecSummary.
        table_type -> controlled vocabulary, many to many, standard, version,
        view.
        primary_key_column -> Which is the column from this new table you're
        describing that identifies uniquely the data inside? (This column must
        be set as primary key on the database schema)
        database_id -> Here you supply information about the schemas Core, DoTS,
        SRes, RAD3, TESS. You must provide the database_id for the correct entry
        in the Core.DatabaseInfo table.
        is_versioned -> If the table is to be in a Ver schema.
        is_view -> This must be set to 1 if you're describing a view. Otherwise,
        set it to 0.
        view_on_table_id -> Insert the table_id of the table where your new view
        gets its data, null otherwise.
        superclass_table_id -> Insert the table_id of the table of which your
        new table is a child, null if it is not a child table.
        is_updatable -> this seems to always be set to 1.

Example:

ga GUS::Common::Plugin::SubmitRow --tablename Core::TableInfo --attrlist        "name,table_type,primary_key_column,database_id,is_versioned,is_view,is_updatable" --valuelist  "MassSpecSummary^^^standard^^^mass_spec_summary_id^^^5^^^0^^^0^^^1"

3. Download GUS from CVS

Go ahead and get the latest version. You might want to make a back up copy of what you presently have.

4. Build

If you have customized some modules, you might want to make back-ups of them in a temporary directory. The last step is to rebuild the GUS installation by executing the command:

        build GUS install -append

Our first attempt to rebuild reset all our sequences to 1. We developed a perl script to autofix all sequences based on the primary_keys current value. But this is not supposed to happen with the latest version of GUS from CVS. If it happens, it is an easy fix. Just ask for the script...