Re: [GUSDEV] Re: PsiPred view

[Angel P. <an...@ma...>]

Hello,

Continuing this discussion, I believe that mike was talking about the 
GUS/DBAdmin tool that is uses the canonical XML version of the schema. 
You can find this file in :

${GUS_HOME}/project_home/GusSchema/Definition/config/gus_schema.xml

In this file you will notice that the main superclass tables have 
subclass entries that correspond to the feature views. These superclass 
entries contain columns that are common to all sub-views, while the 
subclass entries contain only those columns specific to a feature.

In this case CONFIDENCE and SECONDARY_STRUCTURE. Thus the proper way to 
add this to your schema would be to first create the subclass entry in 
the gus_schema.xml file: (diff attached that you can apply to the file):

 <subclass id="DoTS/AAFeature/PsiPred" name="PsiPred" versioned="true" 
tablespace="USERS" categoryRef="AA Sequence Features" updatable="true">
              <columns>
                <column id="DoTS/PsiPred/CONFIDENCE" name="CONFIDENCE"
nullable="true" length="0" precision="0" type="CLOB"/>
                <column id="DoTS/PsiPred/SECONDARY_STRUCTURE" 
name="SECONDARY_STRUCTURE"
nullable="true" length="0" precision="0" type="CLOB"/>
              </columns>
            </subclass>


Then you would produce the schema with dbaDumpSchema:
 dbaDumpSchema -sourceType xml -source gus_schema.xml -targetType 
oracle  -target gus_schema.sql

Also a few more handy options for this tool:

dbaDumpSchema -sourceType [db|xml] [-source file] -targetType 
[oracle|postgres|simple|xml|hbm|hbm3] -target file

Where "hbm|hbm3" are Hibernate mapping files for versions 2.x and 3.x, 
respectively. Hibernate is a Java O/R library, which I am quite fond of 
right now.

You'll notice that the table and view definitions for AAFeature now 
include CLOBs in the SQL. You will still need to create migration 
scripts, but this goes a long way to getting the changes into the 
release cycle so that everyone can benefit. In addition to 
dbaDumpSchema, there is the dbaCompareSchemas script that compares two 
schema in the XML format to further assist you with migrations.

It would have been a much harder task for me to create the Hibernate 3 
mapping classes if the DBAdmin project did not exist, so in the hopes of 
keeping it alive (now that Mike is leaving) I urge everyone to use this 
as the preferred method of schema change requests.

Lastly, this was *never* filed with Bugzilla as a feature request. 
Please do so, or risk it getting lost in the ether.

-angel

Jian Lu wrote:

> Chris,
>
> Happy New Year!
>
> Sorry for late reply since I was on vacation. We have this view which 
> has been used for our annotation pipeline. Here is the SQL.
>
> CREATE VIEW DOTS.PSIPRED AS SELECT
>    AA_FEATURE_ID,
>    AA_SEQUENCE_ID,
>    FEATURE_NAME_ID,
>    PARENT_ID,
>    NA_FEATURE_ID,
>    SUBCLASS_VIEW,
>    SEQUENCE_ONTOLOGY_ID,
>    DESCRIPTION,
>    PFAM_ENTRY_ID,
>    MOTIF_AA_SEQUENCE_ID,
>    REPEAT_TYPE_ID,
>    EXTERNAL_DATABASE_RELEASE_ID,
>    SOURCE_ID,
>    PREDICTION_ALGORITHM_ID,
>    IS_PREDICTED,
>    REVIEW_STATUS_ID,
>    CLOB1 AS CONFIDENCE,
>    CLOB2 AS SECONDARY_STRUCTURE,
>    MODIFICATION_DATE, USER_READ, USER_WRITE, GROUP_READ, GROUP_WRITE, 
> OTHER_READ, OTHER_WRITE, ROW_USER_ID, ROW_GROUP_ID, ROW_PROJECT_ID, 
> ROW_ALG_INVOCATION_ID
>    FROM DOTS.AAFEATUREIMP
>    WHERE SUBCLASS_VIEW='PsiPred'
>    WITH CHECK OPTION;
>
> Thank you.
>
> Jian
>
> Chris Stoeckert wrote:
>
>> Dear Jian,
>>
>> Awhile back you had requested the addition of two CLOB attributes to 
>> AAFeatureImp in order to capture ouput from Psipred. I had approved 
>> that request but learnt from Mike that with the way we are managing 
>> schema changes to GUS now "the schema representation abstracts the 
>> imp tables away, so the clobs would be added automatically when used 
>> by a subclass.  Until there's a subclass that's going to have a clob 
>> column, I can't force them into the imp table."
>>
>> This simply means that we need to submit a specific view such as for 
>> Psipred that has clobs as needed. Can you supply that view (or if you 
>> prefer a more general view) of AAFeatureImp?
>>
>> Shailesh, where and how are we storing Psipred output you generated 
>> for PlasmoDB in GUS?
>>
>> Thanks,
>> Chris
>>
>> Chris Stoeckert, Ph.D.
>>
>> Research Associate Professor, Dept. of Genetics
>>
>> 1415 Blockley Hall, Center for Bioinformatics
>>
>> 423 Guardian Dr., University of Pennsylvania
>>
>> Philadelphia, PA 19104
>>
>> Ph: 215-573-4409 FAX: 215-573-3111
>>
>>
>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc. Do you grep through log 
> files
> for problems?  Stop!  Download the new AJAX search engine that makes
> searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
> http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev



-- 

Angel Pizarro
Director, Bioinformatics Facility
Institute for Translational Medicine and Therapeutics
University of Pennsylvania
806 BRB II/III
421 Curie Blvd.
Philadelphia, PA 19104-6160

P: 215-573-3736
F: 215-573-9004