Post

2 followers Follow
0
Avatar

How to find all documents containing a note with a certain value?

Dear support,

I am a Java developer at Naturalis and have taken Geneious plugin development over from Reinier Kartowikromo.

I am trying to use the Geneious API to find all documents that have been annotated with a (custom) note with a particular value.

Originally we attempted to do this with hard-core SQL queries directly against the database, bypassing the Geneious API. But I cannot imagine that's how you intend it to be done. Yet I can't find anything in the Geneious Javadocs that seems to provide a solution. Ideally I'm looking for an API somewhat like this:

List<AnnotatedPluginDocument> getDocumentsWithNote(DocumentNoteField field, Object value)

Could you tell me how to go about this? NB We are still using Geneious 8.

 

Informatiemanagement

Please sign in to leave a comment.

6 comments

0
Avatar

Hi Iris,

You want something like 

service.retrieve(Query.Factory.createFieldQuery(field, Condition.EQUAL, "valueToSearchFor"))

See https://assets.geneious.com/developer/geneious/javadoc/latest/com/biomatters/geneious/publicapi/databaseservice/DatabaseService.html#retrieve-com.biomatters.geneious.publicapi.databaseservice.Query-jebl.util.ProgressListener- and other variants in the DatabaseService class.

You'll be able to get a reference to the root database services by calling 

PluginUtilities.getWritableDatabaseServiceRoots()

and a list of possible search fields by calling

db.getSearchFields()

Cheers,
Matthew

Matthew Cheung 0 votes
Comment actions Permalink
0
Avatar

Hello Matthew,

I have tried something like that, but the search field I want to query is not present in the list returned by db.getSearchFields() __even though__ it is clearly present in the GUI. See attached screen shot. There are fields there like "Extract plate ID" and "Sample plate ID" that we have added as custom document note fields. I __can__ query them in the GUI. But this snippet of code does not yield the field I want to programmatically search on:

    List<WritableDatabaseService> svcs = PluginUtilities.getWritableDatabaseServiceRoots();
    for(WritableDatabaseService svc:svcs) {
      System.out.println("Folder: " + svc.getFolderName());
      System.out.println("Name  : " + svc.getName());
      System.out.println("Class  : " + svc.getClass());
      for(QueryField qf : svc.getSearchFields()) {
        System.out.println("\tField  : " + qf.field.getName() + "(" + qf.field.getCode() + ")");       
      }
      System.out.println();
    }

 

 

 

OUTPUT:

Folder: Local
Name  : Folder: Local
Class  : class com.biomatters.plugins.local.LocalDatabaseService
    Field  : Any Field(ALL)
    Field  : Common Name(commonName)
    Field  : %GC Primer Bind(percentGc)
    Field  : Bin(bin)
    Field  : Sequence Length(sequence_length)
    Field  : PDB name(PDBname)
    Field  : Min Sequence Length(minimumSequenceLength)
    Field  : Taxonomy(taxonomy)
    Field  : Document type(documentclass)
    Field  : Created(cache_created)
    Field  : Approx. Consensus Length(consensusSequenceLength)
    Field  : Is Forward Read(isForwardRead)
    Field  : Name(cache_name)
    Field  : MQ%(mediumQualityPercent)
    Field  : % Identical Sites(percentage_identical)
    Field  : Ref Seq Name(referenceSequenceName)
    Field  : # Nucleotide Sequences With Quality(nucleotideSequenceWithQualityCount)
    Field  : Ambiguities(abiguities)
    Field  : Height(height)
    Field  : Alignment score(alignment_score)
    Field  : Max Sequence Length(maximumSequenceLength)
    Field  : strain(strain)
    Field  : db_xref(db_xref)
    Field  : Replicon type(vNTREPLTYPE)
    Field  : Primer-Dimer(primerDimer)
    Field  : Organism(organism)
    Field  : Size(document_size)
    Field  : # Sequences(number_of_sequences)
    Field  : Topology(topology)
    Field  : # Nucleotide Sequences With Mates(nucleotideSequenceWithMatesCount)
    Field  : Genetic Code(geneticCode)
    Field  : # Nucleotides(nucleotidesCount)
    Field  : Other Aliases(OtherAliases)
    Field  : Taxa ID(TaxID)
    Field  : LQ%(lowQualityPercent)
    Field  : Modified(modified_date)
    Field  : % Pairwise Identity(percentage_similarity)
    Field  : Alignment options(alignment_options)
    Field  : Mean Coverage(meanCoverage)
    Field  : Genetic Source(GeneticSource)
    Field  : First Residues(sequence_residues)
    Field  : Alignment method(Alignment_method)
    Field  : GID(gid)
    Field  : Post-Trim(postTrimLength)
    Field  : # Indel Disagreements(indelDisagreements)
    Field  : URN(display_urn)
    Field  : Description(description)
    Field  : Accession(accession)
    Field  : Content(content)
    Field  : Oligo Type(oligoType)
    Field  : Sequence Annotations(sequenceAnnotations)
    Field  : Extra-chromosome replication(vNTEXTCHREPL)
    Field  : Failed Binning Fields(BinFailReason)
    Field  : Free end gaps(freeEndGaps)
    Field  : No. tips(ntips)
    Field  : Ref Seq Length(referenceSequenceLength)
    Field  : HQ%(highQualityPercent)
    Field  : sub_strain(sub_strain)
    Field  : Tm(meltingPoint)
    Field  : Molecule Type(molType)
    Field  : Tree builder(Tree_builder)
    Field  : No. nodes(nnodes)
    Field  : Trim Parameters(trimParams.trimParams)
    Field  : Hairpin(hairpin)
    Field  : # Disagreements(disagreements)

Folder: Searches
Name  : Searches
Class  : class com.biomatters.plugins.local.LocalDatabaseService
    Field  : Any Field(ALL)
    Field  : Common Name(commonName)
    Field  : %GC Primer Bind(percentGc)
    Field  : Bin(bin)
    Field  : Sequence Length(sequence_length)
    Field  : PDB name(PDBname)
    Field  : Min Sequence Length(minimumSequenceLength)
    Field  : Taxonomy(taxonomy)
    Field  : Document type(documentclass)
    Field  : Created(cache_created)
    Field  : Approx. Consensus Length(consensusSequenceLength)
    Field  : Is Forward Read(isForwardRead)
    Field  : Name(cache_name)
    Field  : MQ%(mediumQualityPercent)
    Field  : % Identical Sites(percentage_identical)
    Field  : Ref Seq Name(referenceSequenceName)
    Field  : # Nucleotide Sequences With Quality(nucleotideSequenceWithQualityCount)
    Field  : Ambiguities(abiguities)
    Field  : Height(height)
    Field  : Alignment score(alignment_score)
    Field  : Max Sequence Length(maximumSequenceLength)
    Field  : strain(strain)
    Field  : db_xref(db_xref)
    Field  : Replicon type(vNTREPLTYPE)
    Field  : Primer-Dimer(primerDimer)
    Field  : Organism(organism)
    Field  : Size(document_size)
    Field  : # Sequences(number_of_sequences)
    Field  : Topology(topology)
    Field  : # Nucleotide Sequences With Mates(nucleotideSequenceWithMatesCount)
    Field  : Genetic Code(geneticCode)
    Field  : # Nucleotides(nucleotidesCount)
    Field  : Other Aliases(OtherAliases)
    Field  : Taxa ID(TaxID)
    Field  : LQ%(lowQualityPercent)
    Field  : Modified(modified_date)
    Field  : % Pairwise Identity(percentage_similarity)
    Field  : Alignment options(alignment_options)
    Field  : Mean Coverage(meanCoverage)
    Field  : Genetic Source(GeneticSource)
    Field  : First Residues(sequence_residues)
    Field  : Alignment method(Alignment_method)
    Field  : GID(gid)
    Field  : Post-Trim(postTrimLength)
    Field  : # Indel Disagreements(indelDisagreements)
    Field  : URN(display_urn)
    Field  : Description(description)
    Field  : Accession(accession)
    Field  : Content(content)
    Field  : Oligo Type(oligoType)
    Field  : Sequence Annotations(sequenceAnnotations)
    Field  : Extra-chromosome replication(vNTEXTCHREPL)
    Field  : Failed Binning Fields(BinFailReason)
    Field  : Free end gaps(freeEndGaps)
    Field  : No. tips(ntips)
    Field  : Ref Seq Length(referenceSequenceLength)
    Field  : HQ%(highQualityPercent)
    Field  : sub_strain(sub_strain)
    Field  : Tm(meltingPoint)
    Field  : Molecule Type(molType)
    Field  : Tree builder(Tree_builder)
    Field  : No. nodes(nnodes)
    Field  : Trim Parameters(trimParams.trimParams)
    Field  : Hairpin(hairpin)
    Field  : # Disagreements(disagreements)

Folder: geneious
Name  : Folder: geneious
Class  : class com.biomatters.plugins.serverDatabase.ServerDatabaseService


Informatiemanagement 0 votes
Comment actions Permalink
0
Avatar

In other words - looking again at the screen shot -  how would I programmatically find all documents where (for example) the "Extract plate ID"  equals 16027-05?

Informatiemanagement 0 votes
Comment actions Permalink
0
Avatar

Hi Iris,

That's strange.  It should have appeared under the Folder: geneious.  Even stranger is that there were no fields at all.

Did the output get truncated?

Another possibility is that indexing is not working on the shared database.  You can test this by:

  1. dragging one of the documents into your local folders
  2. Waiting a minute for the indexer to do it's thing
  3. Restart Geneious (to make sure that the list of fields is fresh)
  4. Check to see if the field is listed by your code under Folder:Local

Let me know what the situation is.

Cheers,
Matthew

Matthew Cheung 0 votes
Comment actions Permalink
0
Avatar

Hi Iris,

I've just become aware that you've got an open support ticket with us about indexing not working in your shared database.  That is most likely the cause of what you're seeing. 

Once that issue is resolved,  you'll be able to programatically search using the methods I mentioned earlier.  

For now you could try testing your code on a fresh shared database or with your local database.

Cheers,
Matthew

Matthew Cheung 0 votes
Comment actions Permalink
0
Avatar

Hello Matthew

I figured it out how I can retireve the search fields I want to use in a query, although I don't really understand the why of it

(See screenshot again)_ This produces the search fields:

    DatabaseService ds = (DatabaseService) PluginUtilities
        .getGeneiousService("geneious@jdbc:mysql:__145.136.241.66:3306_geneious/Ayco");

 

This does not:

    DatabaseService ds = (DatabaseService) PluginUtilities
        .getGeneiousService("geneious@jdbc:mysql:__145.136.241.66:3306_geneious");

 

By the way I am Ayco :-) Not Iris. I do Java development at Naturalis.

Informatiemanagement 0 votes
Comment actions Permalink