Re: [GUSDEV] InsertSequenceOntology bug?

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

Joanin-

this looks to be a nest of bugs:

1) the purpose of storing soCvsVersion is that, originally at least, 
that value was available in the downloaded file.   if we use an 
automated system (workflow) to download it, we do not know in advance 
what it will be.   so it should be extracted from the .obo file and 
inserted.   however, the plugin is no longer doing that extraction.   if 
this use case is no longer active, then i don't see any reason to write 
or read this value.

2) instead it is taking two optional values on the command line, version 
and so version, and, as you point out, using only the former.   we 
should make the fix you suggest, so that we write the version into the 
soVersion column

3) the SequenceOntology table does not have a foreign key to 
ExternalDatabaseRelease.   Instead it has a column soVersion, storing 
its "external database release" inline.  This is not the standard design 
pattern in GUS.   I don't know why we did it this way, perhaps because 
the table is dedicated to SequenceOntology, so we always know what 
ExternalDatabase it is.   if i could go back in time, i would recommend 
we stick to the pattern even for this table

4) the ISF plugin allows the soCvsVersion to be optoinal.  instead you 
can provide the value in ExternalDatabase.name for the SO.  it then does 
a look-up to find the ExternalDatabaseRelease.version of SO.  this 
allows us to leverage the common use case that there is only one SO in 
the database, so its name is sufficient, we don't really care about its 
version.    however, in this case ISF is still querying using the 
soCvsVersion.   instead, it should either:
    a) remove that condition from the where clause altogether.   using 
the --soExtDbRlsName effectively means "ignore version"
    b) change that condition to read the soVersion instead of soCvsVersion

you can work around this problem by using --soExtDbRlsName  instead of 
--soCvsVersion when you call ISF

steve

On 4/12/2012 12:44 AM, Nicolas Joannin wrote:
> Hello,
>
> I am proceeding with inserting data into my gusdb.
> I downloaded the latest so.obo file from www.sequenceontology.org 
> <http://www.sequenceontology.org> and added that data to the db (see 
> output below).
>
> Once that done, I tried to insert sequence features but ran into the 
> following error message: ERROR: Can't find primary key for SO term 'gene'
>
> Going through the code to find the error, I've manually checked the 
> output of the following sql code:
> select term_name, sequence_ontology_id from 
> sres.SequenceOntology where so_cvs_version = '$soCvsVersion'
>
> In my case the soCvsVersion is 1.324. And the result was nothing. So I 
> checked the table in question, and found it full of data, but with an 
> erroneous soCvsVersion: the InsertSequenceOntologyOBO plugin seems to 
> have used the soVersion as both the soVersion and the soCvsVersion.
>
> Is this a bug in that plugin or is it supposed to be that way?
>
> If it is a bug, I found in the InsertSequenceOntologyOBO.pm file the 
> following code (lines 170 to 178):
>
>    my $soTerm = GUS::Model::SRes::SequenceOntology->
>      new({'so_id' => $obo->{'id'},
>  'ontology_name' => 'sequence',
>  'so_version' => $soVer,
> *'so_cvs_version' => $soVer,*
>  'term_name' => $obo->{'name'},
>  'definition' => $definition });
>    return $soTerm;
>
>
> Is there a specific way to update the table (I don't see any UNDO for 
> this plugin)? Or should I just use plain sql UPDATE for this?
>
> Best regards,
> Nicolas
>
>
>
> Nicolas-work-iMac:~ Nicolas$ ga 
> GUS::Supported::Plugin::InsertSequenceOntologyOBO --inputFile 
> ~/Data_for_GUS/so.obo --soVersion 1.2 --soCvsVersion 1.324 --commit
> Wed Apr 11 15:51:53 2012DSNdbi:Pg:dbname=varDB_2012
> Wed Apr 11 15:51:53 
> 2012PLUGINGUS::Supported::Plugin::InsertSequenceOntologyOBO
> Wed Apr 11 15:51:53 2012ARGalgoinvo1
> Wed Apr 11 15:51:53 2012ARGcomment
> Wed Apr 11 15:51:53 2012ARGcommit1
> Wed Apr 11 15:51:53 2012ARGdebug0
> Wed Apr 11 15:51:53 2012ARGgroup
> Wed Apr 11 15:51:53 
> 2012ARGgusconfigfile/Users/Nicolas/GUS/gus_home/config/gus.config
> Wed Apr 11 15:51:53 2012ARGhelp0
> Wed Apr 11 15:51:53 2012ARGhelpHTML0
> Wed Apr 11 15:51:53 2012ARGinputFile/Data_for_GUS/so.obo
> Wed Apr 11 15:51:53 2012ARGproject
> Wed Apr 11 15:51:53 2012ARGsoCvsVersion1.324
> Wed Apr 11 15:51:53 2012ARGsoExtDbRlsName
> Wed Apr 11 15:51:53 2012ARGsoVersion1.2
> Wed Apr 11 15:51:53 2012ARGsqlVerbose0
> Wed Apr 11 15:51:53 2012ARGuser
> Wed Apr 11 15:51:53 2012ARGverbose0
> Wed Apr 11 15:51:53 2012ARGveryVerbose0
> Wed Apr 11 15:51:53 2012ARGworkflowstepid0
> Wed Apr 11 15:51:53 2012AlgInvocationId29
> Wed Apr 11 15:51:53 2012COMMITcommit on
> Wed Apr 11 15:51:54 2012Submitted 100 terms
> Wed Apr 11 15:51:54 2012Submitted 200 terms
> Wed Apr 11 15:51:54 2012Submitted 300 terms
> Wed Apr 11 15:51:54 2012Submitted 400 terms
> Wed Apr 11 15:51:54 2012Submitted 500 terms
> Wed Apr 11 15:51:54 2012Submitted 600 terms
> Wed Apr 11 15:51:54 2012Submitted 700 terms
> Wed Apr 11 15:51:54 2012Submitted 800 terms
> Wed Apr 11 15:51:54 2012Submitted 900 terms
> Wed Apr 11 15:51:55 2012Submitted 1000 terms
> Wed Apr 11 15:51:55 2012Submitted 1100 terms
> Wed Apr 11 15:51:55 2012Submitted 1200 terms
> Wed Apr 11 15:51:55 2012Submitted 1300 terms
> Wed Apr 11 15:51:55 2012Submitted 1400 terms
> Wed Apr 11 15:51:55 2012Submitted 1500 terms
> Wed Apr 11 15:51:55 2012Submitted 1600 terms
> Wed Apr 11 15:51:55 2012Submitted 1700 terms
> Wed Apr 11 15:51:55 2012Submitted 1800 terms
> Wed Apr 11 15:51:56 2012Submitted 1900 terms
> Wed Apr 11 15:51:56 2012Submitted 2000 terms
> Wed Apr 11 15:51:56 2012Submitted 2100 terms
> Wed Apr 11 15:51:56 2012TIME3 sec  (0:00:03)
> Wed Apr 11 15:51:56 2012RESULTInserted 2120 terms into SequenceOntology
> Wed Apr 11 15:51:56 2012Rows WrittenSRes.SequenceOntology: 2120
> Wed Apr 11 15:51:56 2012AlgInvocationId29
> Wed Apr 11 15:51:56 2012COMMITcommit on
>
>
>
> ------------------------------------------------------------------------------
> For Developers, A Lot Can Happen In A Second.
> Boundary is the first to Know...and Tell You.
> Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
> http://p.sf.net/sfu/Boundary-d2dvs2
>
>
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev