Batch Searching Button

This script is associated with a Query structure table. It reads in an SDF of query structures and runs a search for each one against a Markush structures table. Each query is inserted into the query table with a many to many relationship to the Markush structures table. This allows each query to be browsed in a form view to quickly look at results. The username, date, and query name is also inserted to the table for reference.

This button is used on the Markush demo data set, shown on the Queries form. The Queries table has a many to many relationship with the VMNS table (containing the structures for searching), and has the VMNS table as a child. This would be easy to adapt to any other system which could benefit from batch searching. All queries remain in the database with their relationship, meaning that results can be viewed and searched in groups that share a data base. If privacy is a concern, the script could be adapted to automatically export the results (using Export/Import Query Results Script as a guideline), and then clearing the table. In this case the Export/Import Query Results Script could be used to reimport the search results in a local database copy for analysis.

A more advanced option for keeping queries on a shared database private would be to take advantage of table visibility options associated with user-names, so that the query table is viewable only by one group or person.

/** Batch Searching of a Structure Table button, from the Query form
 *
 * @author Erin Bolstad (ebolstad@chemaxon.com)
 * Jan 2012
 */

import javax.swing.SwingUtilities
import com.im.df.api.dml.*
import com.im.commons.progress.*
import com.im.df.api.chem.MarvinStructure
import chemaxon.formats.MolImporter
import chemaxon.struc.Molecule
import com.im.df.api.support.*
import com.im.df.api.util.DIFUtilities
import static com.im.df.query.JChemSearchConstants.*
import chemaxon.sss.search.JChemSearchOptions
import chemaxon.sss.SearchConstants
import chemaxon.sss.search.options.HomologyTranslationOption
import javax.swing.*
import java.awt.GridBagConstraints
import groovy.swing.SwingBuilder
import org.openide.NotifyDescriptor
import org.openide.DialogDisplayer

evaluate = { widget ->
    if (SwingUtilities.isEventDispatchThread()) {
        Thread.start() {
            evaluateImpl(widget)
        }
    } else {
        evaluateImpl(widget)
    }
}

evaluateImpl = { widget ->
    def rs = widget.form.resultSet
    def dataTree = rs.dataTree

    // Script assumes that table infrastructure is already set up with relationships, and script is on the Query Results table
    def queryEty = dataTree.rootVertex.entity
    def queryEdp = queryEty.schema.dataProvider.getEntityDataProvider(queryEty)
    def schema = queryEty.schema
    def queryVS = rs.rootVertexState
    def VMNSvertex = dataTree.rootVertex.edges.find { it.destination.entity.name == 'VMNS' }
    def VMNSety = VMNSvertex.destination.entity
    def VMNSfld = VMNSety.fields.items.find { it.name == 'Markush structure' }
    def VMNSedp = VMNSety.schema.dataProvider.getEntityDataProvider(VMNSety)

    def queryStrucFld = queryEty.fields.items.find { it.name == 'Query structure' }
    def queryIdFld = queryEty.fields.items.find { it.name == 'CdId' }
    def queryNameFld = queryEty.fields.items.find { it.name == 'Query Name' }
    def queryDateFld = queryEty.fields.items.find { it.name == 'Query Date' }
    def queryUserFld = queryEty.fields.items.find { it.name == 'User' }
    def queryHitsFld = queryEty.fields.items.find { it.name == 'Hits' }

    assert queryStrucFld != null
    assert queryIdFld != null
    assert queryNameFld != null
    assert queryDateFld != null
    assert queryUserFld != null
    assert queryHitsFld != null

    QUERYSTRUCFLD = queryStrucFld
    QUERYIDFLD = queryIdFld
    QUERYDATEFLD = queryDateFld
    QUERYUSERFLD = queryUserFld
    QUERYNAMEFLD = queryNameFld
    QUERYHITSFLD = queryHitsFld

    def userName = schema.getUsername()
    USERNAME = userName

    def timeStamp = new Date()
    TIMESTAMP = timeStamp

    rel = DIFUtilities.findUsagesInRelationships(queryEty)
    firstRel = rel.get(0)
    FIRSTREL = firstRel

    // Get name of the search
    def querySel = new SwingBuilder()
    querySel.setVariable('properties',[:])
    def vars = querySel.variables
    def frame = querySel.dialog(title:'Batch Query', modal:true) {
        panel () {
            gridBagLayout()
            label(text:"Enter query name:", constraints:gbc(
                    gridx:0,
                    gridy:0,
                    insets:[10,10,10,0]))
            textField(id:'newName', constraints:gbc(
                    gridx:1,
                    ipadx:200,
                    gridy:0,
                    fill:GridBagConstraints.HORIZONTAL,
                    insets:[10,5,10,10]))
            translation = buttonGroup()
            radioButton(id:'broadTrans', text:"Homology Broad Translation", buttonGroup:translation, selected:true, constraints:gbc(
                    gridx:0,
                    gridy:1,
                    gridwidth:2,
                    anchor:LINE_START))
            radioButton(id:'narrowTrans', text:"Homology Narrow Translation", buttonGroup:translation, constraints:gbc(
                    gridx:0,
                    gridy:2,
                    gridwidth:2,
                    anchor:LINE_START))
            button(id:'ok', label: "OK", constraints:gbc(
                    gridx:0,
                    gridy:3,
                    anchor:LINE_END,
                    insets:[10,0,10,0]),
                actionPerformed: {
                    vars.buttonResults = 'ok'
                    dispose()})
            button(id:'cancel', label: "Cancel", constraints:gbc(
                    gridx:1,
                    gridy:3,
                    anchor:LINE_START,
                    insets:[10,0,10,0]),
                actionPerformed: {
                    vars.buttonResults = 'quit'
                    dispose()})
        }
    }

    frame.pack()
    frame.setLocationRelativeTo(null)
    frame.show()

    def chosenAction = vars.buttonResults

    if (chosenAction == 'quit') {
        return
    }

    if (chosenAction == 'ok') {
        searchName = vars.newName.text
        homoGroup = (vars.broadTrans.selected ? 'Homology Broad Translation' : 'Homology Narrow Translation')
    }

    switch(homoGroup) {
        case "Homology Broad Translation":
        jcopts = new JChemSearchOptions(SearchConstants.SUBSTRUCTURE)
        jcopts.homologyBroadTranslation = HomologyTranslationOption.ALL
        break
        case "Homology Narrow Translation":
        jcopts = new JChemSearchOptions(SearchConstants.SUBSTRUCTURE)
        jcopts.homologyNarrowTranslation = HomologyTranslationOption.NONE
        break
    }
    JCOPTS = jcopts
    SEARCHNAME = searchName

    // Prompt for file of SDFs to search against
    def chooser = new JFileChooser()
    chooser.setDialogTitle('Select SDF containing the query molecules')
    if (chooser.showOpenDialog(null)==JFileChooser.APPROVE_OPTION) {
        File fileName = chooser.getSelectedFile()
        NAME = fileName.getCanonicalPath()
    } else {
        return
    }

    importer = new MolImporter(NAME)
    importer.grabbingEnabled = true
    mol = new Molecule()

    name = NAME
    queryNum = 1

    while (importer.read(mol)) {

        String molStr = importer.grabbedMoleculeString
        def queryMol = new MarvinStructure(mol)
        QUERYMOL = queryMol
        MOLPASS = mol

        def queryIDs = queryEdp.queryForIds(DFTermExpression.ALL_DATA, null, DFEnvironmentRO.DEV_NULL)

        def id = queryIDs.max() + 1
        ID = id

        // Build and execute substructure query
        def queryMsg = "Running query $queryNum"
        def envQuery = EnvUtils.createDefaultEnvironmentRO(queryMsg, true)

        try {
            jcopts = JCOPTS
            queryMol = QUERYMOL

            def q = DFTermsFactory.createFieldOperatorValueExpr(Operators.STRUCTURE_EXACT, VMNSfld, [(JCHEM_SEARCH_OPTIONS): jcopts], queryMol)
            List resultIds = VMNSedp.queryForIds(q, SortDirective.EMPTY, envQuery)
            RESULTIDS = resultIds
        } finally {
            envQuery?.feedback.finish()
        }

        List resultIds = RESULTIDS

        def lockRL = DIFUtilities.getLockable(queryEdp).obtainLock('Query')
        def envRL = EnvUtils.createDefaultEnvironmentRW(lockRL, 'Updating Relationship Data', true)

        try {
            id = ID
            resultIds = RESULTIDS
            firstRel = FIRSTREL

            // Everything inserted here
            mol = MOLPASS
            queryStrucFld = QUERYSTRUCFLD
            queryIdFld = QUERYIDFLD
            queryMol = QUERYMOL
            queryDateFld = QUERYDATEFLD
            queryUserFld = QUERYUSERFLD
            queryNameFld = QUERYNAMEFLD
            queryHitsFld = QUERYHITSFLD
            timeStamp = TIMESTAMP
            userName = USERNAME
            searchName = SEARCHNAME

            //Update the Query Results table
            def hitNum = resultIds.size()
            vals =[(queryStrucFld.id):queryMol]
            vals.putAt(queryIdFld.id, id)
            vals.putAt(queryDateFld.id, timeStamp)
            vals.putAt(queryUserFld.id, userName)
            vals.putAt(queryHitsFld.id, hitNum)
            vals.putAt(queryNameFld.id, searchName)
            queryEdp.insert(vals, null, envRL)

            if (resultIds.isEmpty() == false) {
                resultIds.each { hit ->
                    def x = id
                    def y = hit
                    DIFUtilities.connectRelationalData(firstRel.forward, x, y, envRL)
                }
            }
        } finally {
            lockRL?.release()
            envRL?.feedback.finish()
        }

        queryNum++
    }
    importer.close()

    def message = "Batch searching done! To view the new queries, please go to the Query Grid View, and select 'Show All' when in query mode (click Query at the upper left corner of the form)"
    NotifyDescriptor d = new NotifyDescriptor.Message(message)
    DialogDisplayer.getDefault().notify(d)
}