error using stag-storenode.pl to update data into chado

classic Classic list List threaded Threaded
5 messages Options
wyu
Reply | Threaded
Open this post in threaded view
|

error using stag-storenode.pl to update data into chado

wyu
Hi all,

I used the script stag-storenode.pl to load an obo file into database.  It works well for the first time loading.  But when I updated a cvterm that kept original dbxref_id, but its is_obsolete was changed,  and reloaded the obo file, I got an error as follows:

DBD::Pg::st execute failed: ERROR:  duplicate key value violates unique constraint "cvterm_c1" [for Statement "INSERT INTO cvterm (dbxref_id, cv_id, name, is_obsolete, definition) VALUES (?, ?, ?, ?, ?)" with ParamValues: 1='18', 2='5', 3='quality-status', 4='1', 5='quality status'] at /usr/lib/perl5/site_perl/5.8.8/DBIx/DBStag.pm line 3322.

The cvterm table has two unique constraints, one is dbxref_id, another is (cv_id, name, is_obsolete). It looks like the script did not check the unique constraint of cvterm , dbxref_id, to generate a "INSERT INTO" statement rather than "UPDATE cvterm".

How can I fix the problem?

Regards,

Visiya
Reply | Threaded
Open this post in threaded view
|

Re: error using stag-storenode.pl to update data into chado

Naama Menda
hi Wu,

stag-storenode.pl does not update ontologies. It can only load a new ontology.
You might want to try gmod_load_cvterms.pl (use the -u option for updating an existing ontology)
 https://gmod.svn.sourceforge.net/svnroot/gmod/schema/trunk/chado/bin/cxgn/

If you use this script, please give feedback in case you encounter any issues!

Naama Menda
Boyce Thompson Institute for Plant Research
Tower Rd
Ithaca NY 14853
USA

(607) 254 3569
Sol Genomics Network
http://solgenomics.net/
[hidden email]


On Mon, Oct 4, 2010 at 7:34 PM, wyu <[hidden email]> wrote:

Hi all,

I used the script stag-storenode.pl to load an obo file into database.  It
works well for the first time loading.  But when I updated a cvterm that
kept original dbxref_id, but its is_obsolete was changed,  and reloaded the
obo file, I got an error as follows:

DBD::Pg::st execute failed: ERROR:  duplicate key value violates unique
constraint "cvterm_c1" [for Statement "INSERT INTO cvterm (dbxref_id, cv_id,
name, is_obsolete, definition) VALUES (?, ?, ?, ?, ?)" with ParamValues:
1='18', 2='5', 3='quality-status', 4='1', 5='quality status'] at
/usr/lib/perl5/site_perl/5.8.8/DBIx/DBStag.pm line 3322.

The cvterm table has two unique constraints, one is dbxref_id, another is
(cv_id, name, is_obsolete). It looks like the script did not check the
unique constraint of cvterm , dbxref_id, to generate a "INSERT INTO"
statement rather than "UPDATE cvterm".

How can I fix the problem?

Regards,

Visiya
--
View this message in context: http://gmod.827538.n3.nabble.com/error-using-stag-storenode-pl-to-update-data-into-chado-tp1633188p1633188.html
Sent from the Chado mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Virtualization is moving to the mainstream and overtaking non-virtualized
environment for deploying applications. Does it make network security
easier or more difficult to achieve? Read this whitepaper to separate the
two and get a better understanding.
http://p.sf.net/sfu/hp-phase2-d2d
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema


------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
wyu
Reply | Threaded
Open this post in threaded view
|

Re: error using stag-storenode.pl to update data into chado

wyu
Hi Naama,

Thank you so much for your advice. The script gmod_load_cvterms.pl works very well for updating the ontology. I customized the script by using a complete Id field from obo file as the dbxref's accession instead of removing the prefix. It has satisfied my requirement very well. I really appreciate your script.

Sincerely,

Visiya
Reply | Threaded
Open this post in threaded view
|

Re: error using stag-storenode.pl to update data into chado

Naama Menda
hi Visiya,

I'm glad the script worked for updating your ontology.
I'm not sure if it makes much difference to store  "db_name:$numeric_accessio" in dbxref , or just $numeric_accession, but some functions try to load just the numeric value in dbxref, e.g. add_secondary_dbxref in Bio::Chado::Schema::Cv::Cvterm.
Also , some dbxref accessions are strings, like the predicate terms .
Anyway, you can always reconstruct the  full accession with the db name prefix using db.name.


-Naama



On Tue, Oct 12, 2010 at 3:09 PM, wyu <[hidden email]> wrote:

Hi Naama,

Thank you so much for your advice. The script gmod_load_cvterms.pl works
very well for updating the ontology. I customized the script by using a
complete Id field from obo file as the dbxref's accession instead of
removing the prefix. It has satisfied my requirement very well. I really
appreciate your script.

Sincerely,

Visiya
--
View this message in context: http://gmod.827538.n3.nabble.com/error-using-stag-storenode-pl-to-update-data-into-chado-tp1633188p1689856.html
Sent from the Chado mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema


------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
wyu
Reply | Threaded
Open this post in threaded view
|

Re: error using stag-storenode.pl to update data into chado

wyu
Hi Naama,

The reason is that the db name is different from the Id's prefix in
psi-mi.obo.  The db name is PSI-MI, but the prefix is  MI for MI:0001.  
In this case, I can not reconstruct the full accession with the db name
prefix. In my project, I just use some tables of Chado schema including
db, dbxref, cvterm, and cv.

BTW, I didn't find the version column in dbxref was loaded.  Should the
version be the message "format-version" in obo file?

Visiya

Naama Menda wrote:

> hi Visiya,
>
> I'm glad the script worked for updating your ontology.
> I'm not sure if it makes much difference to store  
> "db_name:$numeric_accessio" in dbxref , or just $numeric_accession,
> but some functions try to load just the numeric value in dbxref, e.g.
> add_secondary_dbxref in Bio::Chado::Schema::Cv::Cvterm.
> Also , some dbxref accessions are strings, like the predicate terms .
> Anyway, you can always reconstruct the  full accession with the db
> name prefix using db.name <http://db.name>.
>
>
> -Naama
>
>
>
> On Tue, Oct 12, 2010 at 3:09 PM, wyu <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>
>     Hi Naama,
>
>     Thank you so much for your advice. The script gmod_load_cvterms.pl
>     <http://gmod_load_cvterms.pl> works
>     very well for updating the ontology. I customized the script by
>     using a
>     complete Id field from obo file as the dbxref's accession instead of
>     removing the prefix. It has satisfied my requirement very well. I
>     really
>     appreciate your script.
>
>     Sincerely,
>
>     Visiya
>     --
>     View this message in context:
>     http://gmod.827538.n3.nabble.com/error-using-stag-storenode-pl-to-update-data-into-chado-tp1633188p1689856.html
>     Sent from the Chado mailing list archive at Nabble.com.
>
>     ------------------------------------------------------------------------------
>     Beautiful is writing same markup. Internet Explorer 9 supports
>     standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
>     Spend less time writing and  rewriting code and more time creating
>     great
>     experiences on the web. Be a part of the beta today.
>     http://p.sf.net/sfu/beautyoftheweb
>     _______________________________________________
>     Gmod-schema mailing list
>     [hidden email]
>     <mailto:[hidden email]>
>     https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
>


------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema