Re: [XMLPipeDB-developer] System table subquery

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Worked on the code during the past few days and have committed some changes
tonight.
Also successfully exported without error.

UniProtDatabaseProfile.java

   - clean up of some comments and old code
   - cleaned up logging code for getSystemTableManager()
   - Worked on getRelationshipTableManager()
    - [Uniprot - X conditional]
       - rewrote SQL programmatically
         - used looping to create correct setStrings
         - added logging to surface details
         - -[X - X conditional]
       - rewrite of programmatic SQL is in progress (created stringbuilder
         etc. in preparation)
         - added logging
         - -[Species - X or X - Species]
       - added minimal logging to be expanded upon

DatabaseProfile.java

   - cleaned up comments
   - reviewed getRelationsTableManager() and added some logging to it

ExportToGenMAPP.java

   - cleaned up code and comments for readability

Appreciate any feedback as usual =D

Richard

On Mon, Sep 19, 2011 at 6:22 PM, John David N. Dionisio <do...@lm...>wrote:

> 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>
>
>
>
> ------------------------------------------------------------------------------
> All the data continuously generated in your IT infrastructure contains a
> definitive record of customers, application performance, security
> threats, fraudulent activity and more. Splunk takes this data and makes
> sense of it. Business sense. IT sense. Common sense.
> http://p.sf.net/sfu/splunk-d2dcopy1
>  _______________________________________________
> xmlpipedb-developer mailing list
> xml...@li...
> https://lists.sourceforge.net/lists/listinfo/xmlpipedb-developer
>