Re: [XMLPipeDB-developer] System table subquery
Brought to you by:
kdahlquist,
zugzugglug
Menu
▾
▴
Re: [XMLPipeDB-developer] System table subquery
|
From: John D. N. D. <do...@lm...> - 2011-09-20 01:22:48
|
Very cool; looks like we can move on now. Dr. Dahlquist and I suspect that the relationship tables may not actually be as hard as they seem; just a matter of tweaking the initial queries (which you're more comfortable doing now!) so that they return the corresponding records for all of the requested species. Onward we go :) John David N. Dionisio, PhD Associate Professor, Computer Science Associate Director, University Honors Program Loyola Marymount University On Sep 19, 2011, at 11:09 AM, Kam Dahlquist wrote: > Hi, > > LMU can accept 20 MB attachments now; I don't know how big your file is zipped, but that's an option. You could also use LionShare. > > Glad to see success! > > Kam > > At 06:05 PM 9/18/2011, you wrote: >> Tried to post my latest gdb export to the biodb wiki but receiving log in error (per separate email) >> >> But, I wanted to let you know I verified that each improper system table does in fact contain the id and species name (samples of each): >> >> >> Pfam >> ID Species Date >> PF02866 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> PF07050 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> PF03479 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> PF02317 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> PF03279 |Pseudomonas aeruginosa| 9/14/2011 >> PF09922 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> PF04205 |Pseudomonas aeruginosa| 9/14/2011 >> PF03379 |Pseudomonas aeruginosa| 9/14/2011 >> PF05389 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> >> >> RefSeq >> ID Species Date >> YP_039973 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> YP_040411 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> YP_039521 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> NP_253798 |Pseudomonas aeruginosa| 9/14/2011 >> NP_254101 |Pseudomonas aeruginosa| 9/14/2011 >> NP_248930 |Pseudomonas aeruginosa| 9/14/2011 >> NP_251503 |Pseudomonas aeruginosa| 9/14/2011 >> NP_249960 |Pseudomonas aeruginosa| 9/14/2011 >> NP_253220 |Pseudomonas aeruginosa| 9/14/2011 >> YP_040877 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> NP_253802 |Pseudomonas aeruginosa| 9/14/2011 >> >> >> GeneId >> ID Species Date >> 879140 |Pseudomonas aeruginosa| 9/14/2011 >> 2859243 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> 879337 |Pseudomonas aeruginosa| 9/14/2011 >> 2860668 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> 881899 |Pseudomonas aeruginosa| 9/14/2011 >> 882312 |Pseudomonas aeruginosa| 9/14/2011 >> 2860009 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> 881520 |Pseudomonas aeruginosa| 9/14/2011 >> 2861152 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> 881596 |Pseudomonas aeruginosa| 9/14/2011 >> >> >> InterPro >> ID Species Date >> IPR022522 |Pseudomonas aeruginosa| 9/14/2011 >> IPR003538 |Pseudomonas aeruginosa| 9/14/2011 >> IPR016379 |Pseudomonas aeruginosa| 9/14/2011 >> IPR016920 |Pseudomonas aeruginosa| 9/14/2011 >> IPR000477 |Pseudomonas aeruginosa| 9/14/2011 >> IPR008948 |Pseudomonas aeruginosa| 9/14/2011 >> IPR005415 |Pseudomonas aeruginosa| 9/14/2011 >> IPR009651 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> IPR007895 |Pseudomonas aeruginosa| 9/14/2011 >> IPR008231 |Pseudomonas aeruginosa| 9/14/2011 >> IPR004558 |Pseudomonas aeruginosa| 9/14/2011 >> IPR011067 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> IPR006358 |Pseudomonas aeruginosa| 9/14/2011 >> >> >> PDB >> ID Species Date >> 2ZWS |Pseudomonas aeruginosa| 9/14/2011 >> 2F9I |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> 2EXV |Pseudomonas aeruginosa| 9/14/2011 >> 3L34 |Pseudomonas aeruginosa| 9/14/2011 >> 1EZM |Pseudomonas aeruginosa| 9/14/2011 >> 2WYB |Pseudomonas aeruginosa| 9/14/2011 >> 2F1L |Pseudomonas aeruginosa| 9/14/2011 >> 1D7L |Pseudomonas aeruginosa| 9/14/2011 >> 1Y12 |Pseudomonas aeruginosa| 9/14/2011 >> 2IXH |Pseudomonas aeruginosa| 9/14/2011 >> 1XAG |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> 2IXI |Pseudomonas aeruginosa| 9/14/2011 >> >> >> EMBL >> ID Species Date >> AJ003006 |Pseudomonas aeruginosa| 9/14/2011 >> BX571856 |Staphylococcus aureus (strain MRSA252)| 9/14/2011 >> AJ633619 |Pseudomonas aeruginosa| 9/14/2011 >> AJ633602 |Pseudomonas aeruginosa| 9/14/2011 >> U07359 |Pseudomonas aeruginosa| 9/14/2011 >> X54201 |Pseudomonas aeruginosa| 9/14/2011 >> AY899300 |Pseudomonas aeruginosa| 9/14/2011 >> AB085582 |Pseudomonas aeruginosa| 9/14/2011 >> AB075926 |Pseudomonas aeruginosa| 9/14/2011 >> AF306766 |Pseudomonas aeruginosa| 9/14/2011 >> X99471 |Pseudomonas aeruginosa| 9/14/2011 >> M21093 |Pseudomonas aeruginosa| 9/14/2011 >> >> >> Richard >> >> On Wed, Sep 14, 2011 at 6:50 PM, Richard Brous <rbr...@gm...> wrote: >> OK, going to commit my changes to UniProtDatabaseProfile: getSystemTableManager() >> >> Here is some logging info to confirm result.next() within the while loop is both id and species name: >> >> 3070712 [Thread-4] INFO edu.lmu.xmlpipedb.gmbuilder.databasetoolkit.profiles.UniProtDatabaseProfile - getSystemTableManager(): while loop: ID:: IPR006314 Species:: Staphylococcus aureus (strain MRSA252) >> 3070712 [Thread-4] INFO edu.lmu.xmlpipedb.gmbuilder.databasetoolkit.profiles.UniProtDatabaseProfile - getSystemTableManager(): while loop: ID:: IPR013840 Species:: Staphylococcus aureus (strain MRSA252) >> 3070728 [Thread-4] INFO edu.lmu.xmlpipedb.gmbuilder.databasetoolkit.profiles.UniProtDatabaseProfile - getSystemTableManager(): while loop: ID:: IPR015887 Species:: Pseudomonas aeruginosa >> 3070728 [Thread-4] INFO edu.lmu.xmlpipedb.gmbuilder.databasetoolkit.profiles.UniProtDatabaseProfile - getSystemTableManager(): while loop: ID:: IPR016148 Species:: Pseudomonas aeruginosa >> >> Richard >> >> On Wed, Sep 14, 2011 at 3:20 PM, Richard Brous <rbr...@gm...> wrote: >> Solid progress made and I now have a compilable working copy. >> >> I'm now working through how to plug the results of the query into the proper slots under the while loop. I had stumbled on an error where I was supplying the column name instead of the actual data from the tuple. Solved that and am hoping the current export will be the last before I commit to sourceforge. >> >> Richard >> >> >> On Mon, Sep 12, 2011 at 10:59 PM, Richard Brous <rbr...@gm...> wrote: >> Spent the weekend reviewing sql and I have achieved some clarity. >> >> I'm still working through things but not at least I have better context and can ask intelligent questions. >> >> The sub query was a big help, I'm not sure how long it would have taken me to do all the joins using ON to return "hjid | species name" >> >> Dondi - I'll stop by after theory tomorrow to discuss further. >> >> Thanks. >> >> Richard >> >> >> On Thu, Sep 8, 2011 at 5:24 PM, John David N. Dionisio <do...@lm...> wrote: >> Hi Rich, >> >> As discussed in our meeting, here is a first step toward the new system table query: >> >> SELECT entrytype.hjid, organismnametype.value FROM entrytype INNER JOIN organismtype ON (entrytype.organism = organismtype.hjid) inner join organismnametype on (organismtype.hjid = organismnametype.organismtype_name_hjid) INNER JOIN dbreferencetype ON(dbreferencetype.organismtype_dbreference_hjid = organismtype.hjid) WHERE dbreferencetype.type = 'NCBI Taxonomy' and (id = '90371'); >> >> (substitute the "id = " clause accordingly) >> >> John David N. Dionisio, PhD >> Associate Professor, Computer Science >> Associate Director, University Honors Program >> Loyola Marymount University >> >> >> >> >> ------------------------------------------------------------------------------ >> Why Cloud-Based Security and Archiving Make Sense >> Osterman Research conducted this study that outlines how and why cloud >> computing security and archiving is rapidly being adopted across the IT >> space for its ease of implementation, lower cost, and increased >> reliability. Learn more. http://www.accelacomm.com/jaw/sfnl/114/51425301/ >> _______________________________________________ >> xmlpipedb-developer mailing list >> xml...@li... >> https://lists.sourceforge.net/lists/listinfo/xmlpipedb-developer >> >> >> >> >> >> > <ATT00001..txt><ATT00002..txt> |
