Re: [GUSDEV] Preliminary 3.5 Schema

[Michael S. <msa...@pc...>]

All,

The latest proposed GUS 3.5 schema is attached.  This draft has numerous 
changes since the previous release, including the addition of a few dozen 
missing views, the changes outlined below, and other cleanup.

This draft has a small error in which the first subclass for a particular 
superclass appears twice.  I'm trying to track down and resolve that, but 
it appears to only affect this simple text reports, not the actual DDL. 
Schemas and tables are in alphabetical order, but capitalization is 
wreaking a little bit of havok on that.  I hope to fix that in a future 
draft as well.

There are some additional PROT changes that this draft does not include.  I 
will send another draft Wednesday with those changes.

Please submit comments/changes/etc. as soon as possible.  The 3.5 schema is 
scheduled to be frozen at the end of this week and all resources will turn 
at that time to preparing the application level code for release.

Thanks,

Mike

DoTS:

* Drop DoTS.PlasmoAPFeature view

* Drop DoTS.MassSpecSummary

* DoTS.GeneFeatureSeqOverlap.SIMILARITY_ID will have a foreign key 
constraint to DoTS.SIMILARITY

* DoTS.NASequenceImp.SOURCE_NA_SEQUENCE_ID will have a foreign key 
constraint to DoTS.NASequenceImp

* DoTS.SecondaryStructure.AA_SEQUENCE_ID will have a foreign key constraint 
to DoTS.AASequenceImp.  DoTS.SecondaryStructure.PRED_ALG_INVOCATION_ID will 
have a foreign key constraint to Core.AlgorithmInvocation.

* DoTS.SecondaryStructureCall.Secondary_Structure_ID will have a foreign 
key constraint to DoTS.SecondaryStructure

* DoTS.AASequenceEnzymeClass.AA_Sequence_ID will have a foreign key 
constraint to DoTS.AASequenceImp, and 
DoTS.AASequenceEnzymeClass.EC_ASSIGNMENT_CONTACT_ID will have a foreign key 
constraint to SRES.CONTACT

* DoTS.ConsistentAlignment.SIMILARITY_ID will have a foreign key constraint 
to DoTS.Similarity

* DoTS.RejectedMotif.External_Database_Id will have a foreign key 
constraint to SRes.ExternalDatabase. 
DoTS.RejectedMotif.External_Database_release_id will have a foreign key 
constraint to SRes.ExternalDatabaseRelease

* DoTS.Similarity.Subject_Table_ID and DoTS.Similarity.Query_Table_id will 
have foreign key constraints to core.tableinfo

* DoTS.PlasmoMap has been renamed to DoTS.MicroSatelliteMap

Prot:

* Prot.OntologyEntry has been removed

* Prot.StudyAssay has been added.

* Prot.StudyDesignAssay has been added

* Prot.StudyBioMaterial has been added

* Prot.AcquisitionChannel has been added

SRes:

* SRes.ExternalDatabaseKeyword has been moved to DoTS

RAD:

* RAD.ScanAlyzeElementResult.top,left,bot,right have had _coordinate 
appended to them

* The RAD.DataTransformationResult has been added as a view on 
RAD.AnalysisResultImp

* RAD.ReporterArray has been renamed to RAD.ArrayDesign.  This impacts the 
following changes:
  - RAD.ArrayAnnotation -> RAD.ArrayDesignAnnotation
  - RAD.ArrayGroup -> RAD.ArrayDesignGroup
  - RAD.ArrayGroupLink -> RAD.ArrayDesignGroupLink
  - and associated column changes

* RAD.CompositeElementGus has been dropped

* RAD.MageDocumentation and RAD.Mage_ML have been dropped.

* RAD.Channel has been removed

Study:

* Study.Ontology* tables have been dropped.  This includes ontology, 
ontologyannotation, ontologyrelationship, ontologyterm, ontologytype.  This 
DOES NOT include study.ontologyentry.

* Study.BioSample, Study.BioSource, and Study.LabeledExtract have been 
added as views on Study.BioMaterial

* Moved Study.BioMaterialMeasurement to Prot and a copy to RAD.

* Study.Parameter* tables have been moved to prot.  This includes 
parameter, parameterinstance, parameterizable, parameterizableinstance, and 
paramterizablelink

--On April 21, 2005 3:20:15 PM -0400 Michael Saffitz 
<msa...@pc...> wrote:

>
> Since releasing the preliminary 3.5 schema, the following changes have
> been proposed.  The list below only covers App, SRes, and Core-- other
> schema changes will come early next week.
>
> The scheduled freeze date for the 3.5 schema is 4/29.  If there are no
> objections to the items below, Core and App will be frozen for 3.5 on
> 4/25 (more SRes changes may be coming early next week).
>
> Consult the road map for full changes.  The list below are just new
> changes since sending this previous email.  They'll be added to the road
> map after feedback from the list is received.
>
> App:
>
> * The App schema has been deprecated.  The remaining few tables that were
> in app will be moved to non-GUS project specific schema (i.e. a Study
> Annotator schema).
>
> Core:
>
> * The workflow tables have been deprecated as they were never actually
> used.  This includes core.workflowstatus, core.workflowedge,
> core.workflowinit, core.workflowedgesql, core.workflow,
> core.workflowblackboard, and core.workflowstatus
>
> * core.generaldocumentation is tentatively deprecated, pending the lack
> of objections from this list.
>
> * core.userdatabase is tenetively deprecated, pending the lack of
> objections from this list.
>
> SRes (tentative, pending more review)
>
> * SRes.SequenceOntology.SO_ID will be a VARCHAR(12), not a NUMBER(10)
>
> * SRes.MGEDOntologyRelationship will be named SRes.OntologyRelationship
>
> * SRes.MGEDOntologyTerm will be named SRes.OntologyTerm
>
> * SRes.BibliographicReference.authors will be nullable, to support the
> alternative BibRefAuthor linking table.
> SRes.BibliographicReference.External_Database_Release_ID will gain the FK
> the ExternalDatabaseRelease table
>
> * SRes.BibRefAnnotation is tentatively deprecated, pending the lack of
> objections from this list.
>
> Thanks,
>
> Mike
>
>
> --On April 7, 2005 1:53:44 PM -0400 Michael Saffitz
> <msa...@pc...> wrote:
>
>>
>> All,
>>
>> Development work on the GUS 3.5 Schema is nearing completion, and we'd
>> like to take this opportunity to solicit community feedback and provide
>> the 3.5 schema for review.
>>
>> With the 3.5 Schema nearly finalized, we're now turning our attention to
>> ensuring compatibility in the object layer and with plugins for 3.5. Once
>> done, GUS 3.5 will be ready for release.  It's my hope that that will be
>> as soon as possible, but I'm hesitant to give a firm date.
>>
>> The attached file contains the 3.5 schema, in a simple text summary
>> format.  When reviewing the file, please keep the following in mind:
>>
>> * I've done a very preliminary analysis of the file--- there may be
>> glaring mistakes/omissions.
>>
>> * All tables and views in GUS 3.5 are versioned, and the version
>> tables/views are not displayed.
>>
>> * All tables and views in GUS 3.5 contain the standard housekeeping
>> columns, which are also not displayed.
>>
>> * Subclass tables (marked by "extends superclass" where superclass is the
>> name of the parent table) only have the columns added by the subclass
>> displayed.  For example, if the superclass has columns a, b, c, and d,
>> then the subclass will display the column e but have all five columns in
>> the database.
>>
>> * This is an object-centric display-- the method of subclassing (i.e. use
>> of imp tables) is not changing.  As a result, Imp tables are not
>> displayed, nor are super/sub classes flagged as views.
>>
>> * The Type of a column with a foreign key is the name of the foreign key
>> table.  For example, a table with a column NA_FEATURE_ID with a foreign
>> key to DoTS.NAFEATURE will have a type of DoTSS.NAFEATURE.  This is the
>> only way foreign keys are displayed in this view.
>>
>> * All tables first column serves as the Primary key.  There is no
>> denotation for the primary key (although it will also appear as an index
>> against the first column).  Some tables are missing primary keys in this
>> document-- it will be fixed soon.
>>
>> * All indexes for a table are displayed.
>>
>> Please let me know if you have any questions/concerns.
>>
>> Thanks,
>>
>> Mike
>
>
>
>
>
>
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev