Re: [Gusdev-gusdev] LoadNRDB newbie questions:

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hi Michael,

Try granting select to public. Was the temp table created?

I don't think the null error is due to the problem with granting
permissions. As you probably know, each nrdb record has a defline with
each of the databases that have that sequence. The databases are listed by
an abreviation, e.g. gb, emb, dbj, pir, etc. and the number of
abreviations is not static.  That is why there is such a long list of
database names and abreviations on the command line (--sourceDB). I should
have explained to you that you will have to supply a list that conforms to
what is in your database The external_database_release_id in the NRDBEntry
table is for each of these databases and not the NRDB database itself (you
have 135 for that and it is in the ExternalNASequence table).

The plugin fetches the external_database_release_id using the following 
SQL query:

select r.external_database_release_id from sres.externaldatabase d, 
sres.externaldatabaserelease r where upper(d.name) like ? and 
d.external_database_id=r.external_database_id and upper(r.version) = 
'UNKNOWN'

You will have to first enter each of the databases into ExternalDatabase 
and ExternalDatabaseRelease and then supply them on the command line using 
the model that I gave you.

The kicker is that you have to check this each time you are loading as not 
only do the number of databsaes change but also they can have different 
defline formats which means --sourceDB will have to change and the plugin 
will have to be modified to accomodate the additional format. NCBI gives 
you the information in its README files on the ftp site. 

                                                    Debbie

 On Thu, 29 May 2003, MICHAEL LUCHTAN wrote:

> Hey Debbie
> Thanks for the reply.  I tried to run the plugin with these arguments:
> 
> **********************************************************************
>  ga GUS::Common::Plugin::LoadNRDB --testnumber1=10 --testnumber2=10
> --nrdb=/scratch/NCBI/nr
> --gitax=/scratch/luchtan/taxonomy/gi_taxid_prot.dmp --extDbRelId=135
> --verbose --maketemp --temp_login=luchtan --temp_password=xxxxx
> --dbi_str='dbi:Oracle:host=mango.ctegd.uga.edu;sid=GUS' --plugin --delete
> --sourceDB 'GENBANK(NRDB):gb,EMBL DATA LIBRARY (NRDB):emb,DDBJ
> (NRDB):dbj,NBRF PIR (NRDB):pir,PROTEIN RESEARCH FOUNDATION
> (NRDB):prf,SWISS-PROT (NRDB):sp,BROOKHAVEN PROTEIN DATA BANK
> (NRDB):pdb,PATENTS (NRDB):pat,GENINFO BACKBONE ID (NRDB):bbs,GENERAL
> DATABASE IDENTIFIER (NRDB):gnl,NCBI REFERENCE SEQUENCE (NRDB):ref,LOCAL
> SEQUENCE IDENTIFIER:lcl,GENPEPT:genpept'
> ***********************************************************************
> 
> I am a little confused about what user to set the select permissions to
> instead of 'gusrw'.  I tried to code it as 'luchtan', but it said that I
> couldn't grant/revoke priveleges to/from myself:
> 
> ***********************************************************************
> DBD::Oracle::db do failed: ORA-01749: you may not GRANT/REVOKE privileges
> to/from yourself (DBD ERROR: OCIStmtExecute) at
> /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 222
> ***********************************************************************
> 
> and then I get all these errors like:
> 
> ***********************************************************************
> DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
> ("LUCHTAN"."NRDBTEMP"."EXTERNAL_DB_REL_ID") (DBD ERROR: OCIStmtExecute) at
> /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 271, <NRDB>
> line 1.
> DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
> ("LUCHTAN"."NRDBTEMP"."EXTERNAL_DB_REL_ID") (DBD ERROR: OCIStmtExecute) at
> /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 271, <NRDB>
> line 11.
> ************************************************************************
> 
> Which I assume is a result of me not knowing who to grant permissions to.
> In my gus config file there is a readOnlyDatabaseLogin.  Should it be that
> user?
> 
> 
> So the plugin still ends up crashing.  Here is the rest of the output:
> (Any help would be much, much appreciate)
> **********************************************************************
> 
> 
> prepareAndExecute: select max(set_num) from luchtan.NRDBTemp
> Thu May 29 16:08:30 2003	 entries in temp table
> 
> 
> RetrieveFromDB: select * from DoTS.ExternalAASequence where  description =
> ? and  external_database_release_id = ? and  secondary_identifier = ? and
> source_id = ?
>   bindValues (unnamed protein product [Oryza sativa (japonica
> cultivar-group)], 135, 7228451, BAA92411.1)
> 
> RetrieveFromDB: select * from DoTS.NRDBEntry where
> external_database_release_id is NULL and  source_id = ?
>   bindValues (BAA92411.1)
> 
> 
> sqlExec:
>     INSERT INTO DoTS.ExternalAASequence (  description, row_user_id,
> user_write, group_write, secondary_identifier, row_project_id,
> external_database_release_id, subclass_view, group_read, sequence,
> aa_sequence_id, row_group_id, other_read, length, source_id,
> modification_date, user_read, row_alg_invocation_id, other_write )
>     VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, ?,
> ?, ? )
>   bindValues (unnamed protein product [Oryza sativa (japonica
> cultivar-group)], 6, 1, 1, 7228451, 2, 135, ExternalAASequence, 1,
> MCSYIRYDTPKLFTHVTKTPPKNQVSNSINDVGSRRATDRSVASCSSEKSVGTMSVKNASSISFEDIEKSISNWKIPKVNIKEIYHVDTDIHKVLTLNLQTSGYELELGSENISVTYRVYYKAMTTLAPCAKHYTPKGLTTLLQTNPNNRCTTPKTLKWDEITLPEKWVLSQAVEPKSMDQSEVESLIETPDGDVEITFASKQKAFLQSRPSVSLDSRPRTKPQNVVYATYEDNSDEPSISDFDINVIELDVGFVIAIEEDEFEIDKDLLKKELRLQKNRPKMKRYFERVDEPFRLKIRELWHKEMREQRKNIFFFDWYESSQVRHFEEFFKGKNMMKKEQKSEAEDLTVIKKVSTEWETTSGNKSSSSQSVSPMFVPTIDPNIKLGKQKAFGPAISEELVSELALKLNNLKVNKNINEISDNEKYDMVNKIFKPSTLTSTTRNYYPRPTYADLQFEEMPQIQNMTYYNGKEIVEWNLDGFTEYQIFTLCHQMIMYANACIANGNKEREAANMIVIGFSGQLKGWWNNYLNETQRQEILCAVKRDDQGRPLPDRDGNGNPTELKEGFHMEEKDEPIQEDDQVVGTIQKYTKQKWYAEVMYRFIDGSYFQHITLIDSGADVNCIREDEILDQLVQTKREQVVNSIYLHDNSFPKSMDLPDQKITEKRAKLQDIPHHEERLLDYREKKSRDGQDKLPMEVEQSMATNKNTKILLRAWLLST,
> 2, 3, 1, 719, BAA92411.1, 1, 252, 0)
>  DbiHandle:sqlExec:insert succeeded 1 row(s)
> 
> 
> sqlExec:
>     INSERT INTO DoTS.NRDBEntry (  description, row_user_id, user_write,
> group_write, nrdb_entry_id, is_preferred, row_project_id, taxon_id,
> external_database_release_id, group_read, aa_sequence_id, row_group_id,
> other_read, gid, source_id, modification_date, user_read,
> row_alg_invocation_id, other_write )
>     VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, '', ?, ?, ?, ?, ?, ?, SYSDATE, ?,
> ?, ? )
>   bindValues (unnamed protein product [Oryza sativa (japonica
> cultivar-group)], 6, 1, 1, 2, 1, 2, 142988, 1, 2, 3, 1, 7228451,
> BAA92411.1, 1, 252, 0)
> DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
> ("DOTS"."NRDBENTRY"."EXTERNAL_DATABASE_RELEASE_ID") (DBD ERROR:
> OCIStmtExecute) at /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line
> 144, <NRDB> line 11.
> 
> 
> sqlExec:
>     UPDATE  Core.AlgorithmInvocation
>     SET
> 	end_time = SYSDATE,
> 	row_alg_invocation_id = ?,
> 	modification_date = SYSDATE
>     WHERE   algorithm_invocation_id = ?
>   bindValues (252, 252)
>  DbiHandle:sqlExec:insert succeeded 1 row(s)
> 
>  SQL ERROR!! involving
> 
>     INSERT INTO DoTS.NRDBEntry (  description, row_user_id, user_write,
> group_write, nrdb_entry_id, is_preferred, row_project_id, taxon_id,
> external_database_release_id, group_read, aa_sequence_id, row_group_id,
> other_read, gid, source_id, modification_date, user_read,
> row_alg_invocation_id, other_write )
>     VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, '', ?, ?, ?, ?, ?, ?, SYSDATE, ?,
> ?, ? )
>  Values: unnamed protein product [Oryza sativa (japonica cultivar-group)],
> 6, 1, 1, 2, 1, 2, 142988, 1, 2, 3, 1, 7228451, BAA92411.1, 1, 252, 0 at
> /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 184
> 
> GUS::ObjRelP::DbiDbHandle::death('GUS::ObjRelP::DbiDbHandle=HASH(0x85e7ea4)',
> '^J SQL ERROR!! involving^J ^J    INSERT INTO DoTS.NRDBEntry (  desc...')
> called at /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 147
> 
> GUS::ObjRelP::DbiDbHandle::sqlExec('GUS::ObjRelP::DbiDbHandle=HASH(0x85e7ea4)',
> 'GUS::ObjRelP::DbiDbHandle::st=HASH(0x417b0b58)', 'ARRAY(0x419d7d00)', '^J
> INSERT INTO DoTS.NRDBEntry (  description, row_user_id, use...') called at
> /home/gus_home/lib/perl/GUS/ObjRelP/DbiRow.pm line 674
> 
> GUS::ObjRelP::DbiRow::quote_and_insert('GUS::Model::DoTS::NRDBEntry=HASH(0x1043ae48)',
> 'HASH(0xfd8bd54)') called at /home/gus_home/lib/perl/GUS/ObjRelP/DbiRow.pm
> line 621
> 
> GUS::ObjRelP::DbiRow::insert('GUS::Model::DoTS::NRDBEntry=HASH(0x1043ae48)')
> called at /home/gus_home/lib/perl/GUS/Model/GusRow.pm line 1677
> 
> GUS::Model::GusRow::submit('GUS::Model::DoTS::NRDBEntry=HASH(0x1043ae48)',
> undef, 1) called at /home/gus_home/lib/perl/GUS/Model/GusRow.pm line 1765
> 
> GUS::Model::GusRow::submitChildrenInClass('GUS::Model::DoTS::ExternalAASequence=HASH(0xf24a708)',
> 'GUS::Model::DoTS::NRDBEntry') called at
> /home/gus_home/lib/perl/GUS/Model/GusRow.pm line 1746
> 
> GUS::Model::GusRow::submitAllChildren('GUS::Model::DoTS::ExternalAASequence=HASH(0xf24a708)')
> called at /home/gus_home/lib/perl/GUS/Model/GusRow.pm line 1684
> 
> GUS::Model::GusRow::submit('GUS::Model::DoTS::ExternalAASequence=HASH(0xf24a708)')
> called at /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 345
> 
> GUS::Common::Plugin::LoadNRDB::makeNRDBAndExternalAASequence('GUS::Common::Plugin::LoadNRDB=HASH(0x84bf844)',
> 'HASH(0x84f5eb4)', 'HASH(0x84f5bf0)', 135) called at
> /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 127
> 
> GUS::Common::Plugin::LoadNRDB::run('GUS::Common::Plugin::LoadNRDB=HASH(0x84bf844)',
> 'HASH(0x8765564)') called at
> /home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 389
> 	eval {...} called at
> /home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 385
> 
> GUS::PluginMgr::GusApplication::doMajorMode_Run('GUS::PluginMgr::GusApplication=HASH(0x80fbb0c)',
> 'GUS::Common::Plugin::LoadNRDB') called at
> /home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 284
> 
> GUS::PluginMgr::GusApplication::doMajorMode('GUS::PluginMgr::GusApplication=HASH(0x80fbb0c)',
> 'GUS::Common::Plugin::LoadNRDB') called at
> /home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 193
> 
> GUS::PluginMgr::GusApplication::parseAndRun('GUS::PluginMgr::GusApplication=HASH(0x80fbb0c)',
> 'ARRAY(0x8105184)') called at /home/gus_home/bin/ga line 11
> 
> 
> 
> Michael Luchtan
> http://www.cs.uga.edu/~luchtan
> 
> 
> On Thu, 29 May 2003, Deborah F. Pinney wrote:
> 
> >
> > Hi Michael,
> >
> > These are the arguments that I used:
> >
> > --temp_login "pinney" --sourceDB 'GENBANK
> > (NRDB):gb,EMBL DATA LIBRARY (NRDB):emb,DDBJ (NRDB):dbj,NBRF PIR
> > (NRDB):pir,PROTEIN RESEARCH FOUNDATION (NRDB):prf,SWISS-PROT
> > (NRDB):sp,BROOKHAVEN PROTEIN DATA BANK (NRDB):pdb,PATENTS
> > (NRDB):pat,GENINFO BACKBONE ID (NRDB):bbs,GENERAL DATABASE IDENTIFIER
> > (NRDB):gnl,NCBI REFERENCE SEQUENCE (NRDB):ref,LOCAL SEQUENCE
> > IDENTIFIER:lcl,GENPEPT:genpept' --temp_password "xxxxxxx" --dbi_str
> > 'dbi:Oracle:host=erebus.pcbi.upenn.edu;sid=gusdev'
> > --gitax /ptolemy/gi_taxid_prot.dmp --nrdb /ptolemy/nr --extDbRelId 4194
> > --maketemp --plugin --delete
> >
> >
> > The temp table is necessary for updating and even though you are only
> > filling the table for the first time, you will need to include this. I
> > think Jonathan answered the general questions about temp tables and why
> > you might need them. The plugin actually truncates, drops, creates the
> > nrdbtemp table and grants select permission to gusrw. That last one is an
> > error that will be fixed in the future but for now you may want to hard
> > code the appropriate login. I included --dbi_str  because I thought that
> > this could and might vary from your config file.
> >
> > The plugin runs in three sections, make the temp table, insert and update
> > into NRDBEntry and ExternalAASequence, and delete obsolete entries in
> > those two tables. Each section can be run separately and each is specified
> > as --maketemp, --plugin, --delete. They should be run sequentially but
> > for testing and in case of failure, they can be run at separate times. You
> > could skip the --delete.
> >
> > I hope this helps.
> >
> >                                                         Debbie
> >
> > On Thu, 29 May 2003, MICHAEL LUCHTAN wrote:
> >
> > > Hello All:
> > > I am trying to load the genbank nrdb database into GUS so that I will have
> > > what I blast against in the db so that I can load blast similarities.
> > > I have some questions regarding this plugin.
> > > What is the temp table for?  Why is it necessary?  Why must I proide
> > > another login to the database to create this table?  I would think that it
> > > could just use the login/information provided in my gus config file.
> > >
> > > Can anyone clarify the args --plugin, and --maketemp?  i.e. what happens
> > > if these are/are not provided?
> > >
> > > Which arguements are required?
> > >
> > >
> > > Also, I have tried to run the plugin with the following command:
> > > ga GUS::Common::Plugin::LoadNRDB --testnumber1=10 --testnumber2=10
> > > --nrdb=/scratch/NCBI/nr
> > > --gitax=/scratch/luchtan/taxonomy/gi_taxid_prot.dmp --extDbRelId=135
> > > --verbose --maketemp --temp_login=luchtan --temp_password=*******
> > > --dbi_str=dbi:Oracle:host=mango.ctegd.uga.edu;sid=GUS
> > >
> > > But I get an error like this:
> > >
> > > Thu May 29 12:01:35 2003	**COMMIT TURNED OFF**
> > >
> > > Thu May 29 12:01:35 2003	Testing on 10 insertions
> > >                 into temp table
> > >
> > > Thu May 29 12:01:35 2003	Testing on 10 insertions
> > >                 into NRDBEntry/ExternalAASequence
> > >
> > > There are 0 entries in the database hash
> > > Thu May 29 12:02:40 2003	There are 2900500 gi to taxon_id pairs
> > >
> > > DBI->connect(host=mango.ctegd.uga.edu) failed: ORA-12154: TNS:could not
> > > resolve service name (DBD ERROR: OCIServerAttach) at
> > > /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 216
> > >
> > >
> > > sqlExec:
> > >     UPDATE  Core.AlgorithmInvocation
> > >     SET
> > > 	end_time = SYSDATE,
> > > 	row_alg_invocation_id = ?,
> > > 	modification_date = SYSDATE
> > >     WHERE   algorithm_invocation_id = ?
> > >   bindValues (245, 245)
> > >  DbiHandle:sqlExec:insert succeeded 1 row(s)
> > > Can't call method "do" on an undefined value at
> > > /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 219.
> > >
> > >
> > >
> > > I might be kind of "fresh" with regards to my interaction with Oracle, so
> > > I am not sure if the dbi_str argument is in the correct format.  Can
> > > anyone help me with this?
> > >
> > >
> > > Michael Luchtan
> > > http://www.cs.uga.edu/~luchtan
> > >
> > >
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.net email is sponsored by: eBay
> > > Get office equipment for less on eBay!
> > > http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5
> > > _______________________________________________
> > > Gusdev-gusdev mailing list
> > > Gus...@li...
> > > https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
> > >
> >
> >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.net email is sponsored by: eBay
> > Get office equipment for less on eBay!
> > http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5
> > _______________________________________________
> > Gusdev-gusdev mailing list
> > Gus...@li...
> > https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
> >
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: eBay
> Get office equipment for less on eBay!
> http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
> 