Table Standardizer

This script takes a tables of structures and applies a pre-defined standardizer XML. The resulting structures are output into a new BLOB column, as well as the index and name of standardizer tasks into two new columns. However, the user must change the new BLOB column to be a structure rendered after the script has completed. Doing this automatically from within a script requires calling the column XML file and performing a complicated replacement. This is currently in development. However, there is a pop up window at the end of the script which prompts the user to do so.

/** Table Standardizer
*
* @author Erin Bolstad ([email protected])
* Dec 2011
*/
import chemaxon.formats.MolExporter
import chemaxon.reaction.Standardizer
import com.im.commons.db.ddl.DBDatabaseInfo.ColumnSQLType;
import com.im.commons.progress.*
import com.im.df.api.capabilities.*
import com.im.df.api.support.*
import com.im.df.api.util.DIFUtilities
import groovy.swing.SwingBuilder
import java.awt.*
import javax.swing.*
 
def ety = dataTree.rootVertex.entity
def edp = ety.schema.dataProvider.getEntityDataProvider(ety)
def rows = edp.getRowCount(env)
def rs = ety.schema.dataProvider.getDefaultResultSet(dataTree, false, DFEnvironmentRO.DEV_NULL)
def parentVS = rs.getVertexState(dataTree.rootVertex)
def ids = parentVS.ids
println "Entity has $rows rows"
 
// Prompt for name of structure column and output column
def colInputs = new SwingBuilder()
colInputs.setVariable('properties',[:])
def vars = colInputs.variables
def frame = colInputs.dialog(title:'Column Constructor', modal:true) {
panel () {
gridBagLayout()
label(text:"Use this window to build or specify the input and output columns of the standardizing script.", constraints:gbc(
gridy:0,
gridwidth:2,
insets:[20,10,0,10]))
label(text:"You will then be prompted for a standardization configuration XML", constraints:gbc(
gridy:1,
gridwidth:2,
insets:[5,10,30,10]))
label(text:"Input existing structure column name", constraints:gbc(
gridx:0,
gridy:2,
insets:[2,10,2,10]))
textField(id:'origStruc', text:"Structure", name:"Original Structure", constraints:gbc(
gridx:1,
gridy:2,
fill:GridBagConstraints.HORIZONTAL,
insets:[2,10,2,10]))
label(text:"Check the box if the column needs to be created, otherwise, enter the existing name", constraints:gbc(
gridwidth:2,
gridy:5,
insets:[28,10,5,10]))
checkBox(id:'ssCheck', text:"Standardized Structures",
horizontalTextPosition: SwingConstants.LEFT, constraints:gbc(
gridx:0,
gridy:6,
anchor:LINE_END,
insets:[2,10,2,10]))
textField(id:'ssName', text:"Standardized Structure", constraints:gbc(
gridx:1,
gridy:6,
fill:GridBagConstraints.HORIZONTAL,
insets:[2,10,2,10]))
checkBox(id:'apTaskIndexCheck', text:"Applied Task Indices",
horizontalTextPosition: SwingConstants.LEFT, constraints:gbc(
gridx:0,
gridy:7,
anchor:LINE_END,
insets:[2,10,2,10]))
textField(id:'apTaskIndexName', text:"Applied Task Indicies", constraints:gbc(
gridx:1,
gridy:7,
fill:GridBagConstraints.HORIZONTAL,
insets:[2,10,2,10]))
checkBox(id:'apTaskCheck', text:"Applied Tasks",
horizontalTextPosition: SwingConstants.LEFT, constraints:gbc(
gridx:0,
gridy:8,
anchor:LINE_END,
insets:[2,10,2,10]))
textField(id:'apTaskName', text:"Applied Tasks", constraints:gbc(
gridx:1,
gridy:8,
fill:GridBagConstraints.HORIZONTAL,
insets:[2,10,2,10]))
button(id:'run', label: "Standardize!", constraints:gbc(
gridx:0,
gridy:9,
anchor:LINE_END,
insets:[10,0,10,0]),
actionPerformed: {
vars.buttonResults = 'ok'
dispose()})
button(id:'cancel', label: "Cancel", constraints:gbc(
gridx:1,
gridy:9,
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') {
origStrucCol = vars.origStruc.text
ssColCheck = vars.ssCheck.selected
ssColName = vars.ssName.text
apTaskIndxCheck = vars.apTaskIndexCheck.selected
apTaskIndexColName = vars.apTaskIndexName.text
apTaskColCheck = vars.apTaskCheck.selected
apTaskColName = vars.apTaskName.text
}
 
// Create the fields if need be
def lock = DIFUtilities.getLockable(dataTree).obtainLock('Standardizing')
def envRW = EnvUtils.createDefaultEnvironmentRW(lock, 'Standardizing', true)
 
def fldMol = ety.fields.items.find { it.name == origStrucCol }
try {
if (!ssColCheck) {
FLDSTSTRUC = ety.fields.items.find { it.name == ssColName }
if (FLDSTSTRUC == null) {
print "Column $ssColName not found! Quitting...\n"
return
}
} else {
def Class[] req1 = [ DBFieldCapability.class, DFFieldTextCapability.class ]
def nt = DIFUtilities.findFirstAppropriateNewType(ety.getFields().getNewTypes(), false, req1, new Class[0]);
nt.options.newDFItemNameSafe = ssColName
def col = nt.options.columns[0];
def nativeTypeBlob = null;
 
// search for BLOB native type
for (def nativeType : col.allNativeTypeDefinitions) {
if (nativeType.columnType == ColumnSQLType.BLOB) {
nativeTypeBlob = nativeType;
}
}
if (nativeTypeBlob != null) {
// resize default BLOB native type and assign it to column type definition
col.nativeTypeDefinition = nativeTypeBlob.reSize(20000);
}
def newBinField = nt.create(envRW).iterator().next()
FLDSTSTRUC = ety.fields.items.find { it.name == ssColName }
}
 
if (!apTaskColCheck) {
FLDAPTASKS = ety.fields.items.find { it.name == apTaskColName }
if (FLDAPTASKS == null) {
print "Column $apTaskColName not found! Quitting... \n"
return
}
} else {
def Class[] req = [ DFFieldTextCapability.class ]
nt = DIFUtilities.findFirstAppropriateNewType(ety.getFields().getNewTypes(), false, req, new Class[0]);
nt.options.newDFItemNameSafe = apTaskColName
assert nt.options.valid : 'Error: ' + nt.options.errorMessage
def appliedTasksField = nt.create(envRW).iterator().next()
FLDAPTASKS = ety.fields.items.find { it.name == apTaskColName }
}
 
if (!apTaskIndxCheck) {
FLDAPTASKINDEX = ety.fields.items.find { it.name == apTaskIndexColName }
if (FLDAPTASKINDEX == null) {
Print "Column $apTaskIndexColName not found! Quitting... \n"
return
}
} else {
def Class[] req2 = [ DFFieldTextCapability.class ]
nt = DIFUtilities.findFirstAppropriateNewType(ety.getFields().getNewTypes(), false, req2, new Class[0]);
nt.options.newDFItemNameSafe = apTaskIndexColName
assert nt.options.valid : 'Error: ' + nt.options.errorMessage
def appliedTaskIndex = nt.create(envRW).iterator().next()
FLDAPTASKINDEX = ety.fields.items.find { it.name == apTaskIndexColName }
}
 
// Input the standardizer XML
def chooser = new JFileChooser()
chooser.setDialogTitle('Select XML file containing the standardization rules')
if (chooser.showOpenDialog(null)==JFileChooser.APPROVE_OPTION) {
File fileName = chooser.getSelectedFile()
FILENAME = fileName
NAME = fileName.getCanonicalPath()
} else {
return }
 
def standFile = FILENAME
print "Standardization file is $standFile \n"
 
}finally{
lock?.release()
envRW?.feedback.finish()
}
 
def lock2 = edp.lockable.obtainLock('inserting data')
def envRW2 = EnvUtils.createDefaultEnvironmentRW(lock2, 'Standardizing', true)
 
try {
def standFile = FILENAME
def fldStStruc = FLDSTSTRUC
def fldApTasks = FLDAPTASKS
def fldApTaskIndex = FLDAPTASKINDEX
 
ids.each { id ->
// Get mol from table
def molData = parentVS.getData([id], DFEnvironmentRO.DEV_NULL)
getMol = molData[id][fldMol.id]
nativeMol = getMol.getNative()
 
Standardizer standardizer = new Standardizer(standFile)
standardizer.setFinalClean()
 
def molStr = MolExporter.exportToFormat(nativeMol, "mol")
vals = [(fldStStruc.id):molStr]
 
String [] stanIDs = standardizer.getAppliedTaskIDs()
String results
n=0
for (String item: stanIDs) {
if (n==0) {
results = item
n++
} else{
results = results + ', ' + item
}}
vals.putAt(fldApTasks.id, results)
 
String [] stanIndex = standardizer.getAppliedTaskIndexes()
String resultsidx
m=0
for (String item: stanIndex) {
if (m==0) {
resultsidx = item
m++
} else {
resultsidx = resultsidx + ', ' + item
}}
vals.putAt(fldApTaskIndex.id, resultsidx)
 
DFUpdateDescription ud = DFUpdateDescription.create(ety, id, vals)
submitList = new Collections ()
submitList = submitList.singletonList(ud)
edp.update(submitList, DFUndoConfig.OFF, envRW2)
print "Finished standardizing ID $id \n"
}
}finally{
lock2?.release()
envRW2?.feedback.finish()
}
 
def message = "Standardization of table is complete! \n \n You will need to convert the standardized structure column to a structure renderer: \n Right click on the column title and select Customize Widget Settings. \n In the table that pops up, Column Properties will be open. Change 'Cell Renderer' to 'Structure Renderer' \n \n For ease of reading, you may want to change the Applied Tasks/ID columns to a multi-line renderer."
JOptionPane.showMessageDialog(null, message, "Finished!", JOptionPane.INFORMATION_MESSAGE)