Search in database - filtering Calculated columns Can be created - - PowerPoint PPT Presentation

Calculated columns

• Can be created with

JChem Manager:

• or with Cartridge:

jchem_table_pkg.create_jctable('myjctable', 'JChemProperties', 16, 2, 6, ', RECNO NUMBER, DESCR VARCHAR2(4000)', 'aromatize', 1, 'ctcolcfg:logp=logp();rotbl_bnd_cnt=rotatableBondCount()'); CREATE INDEX jc_idx ON myjctable(cd_smiles) INDEXTYPE IS jc_idxtype;

• Calculated column values can be used in filter queries:

jcSearchOptions.setFilterQuery("SELECT cd_id FROM search_example" + " WHERE logp > 0.3"); (CalculatedColumnsSearchExample.java)

1

Search in database - filtering

Pre-filtering using Chemical Terms

 „On the fly” filtering:

 arbitrary Chemical Terms expression can be used  calculated column is not required in the structure table  usually much slower

jcSearchOptions.setChemTermsFilter("logp() > 0.3"); jcSearchOptions.setChemTermsFilter("pka(h(0)) > 2"); (ChemicalTermsFilteringExample.java)

2

Search in database - filtering

Memory search

DB

structures queries DB search Hits Manipulation Memory search

Search methods:

 setSearchOptions(MolSearchOptions)  setQuery(Molecule)  setTarget(Molecule)  isMatching()  findFirst(), findNext()  findAll()

(MemorySearchExample.java)

4

Search in memory

• setSearchOptions(MolSearchOptions)

// create search options object

MolSearchOptions searchOptions = new MolSearchOptions(SearchConstants.SUBSTRUCTURE); // set parameters searchOptions.setMarkushEnabled(true); searchOptions.setDoubleBondStereoMatchingMode( SearchConstants.DBS_NONE); // create searcher with pre-aromatization and set options in searcher MolSearch molSearch = new StandardizedMolSearch(); molSearch.setSearchOptions(searchOptions);

(MemorySearchExample.java)

5

Search in memory

• setQuery(Molecule), setTarget(Molecule)

// set query and target Molecule query = MolImporter.importMol(“C1CCCCC1”); molSearch.setQuery(query); Molecule target = MolImporter.importMol(“OC1CCCCC1”); molSearch.setTarget(target);

(MemorySearchExample.java)

6

Search in memory

• isMatching()

boolean matching = molSearch.isMatching();

if (matching) { // query matches target }

(MemorySearchExample.java)

7

Search in memory

• findFirst(), findNext()

int[] hit = molSearch.findFirst();

while (hit != null) { // do something with the hit hit = molSearch.findNext(); }

• r findAll()

(MemorySearchExample.java)

8

Search in memory

Cartridge examples



SELECT jc_contains('O=C1C=CNC=C1', 'n1ccccc1') FROM dual;



SELECT jc_compare('O=C1C=CNC=C1', 'n1ccccc1', 't:s') FROM dual;



SELECT jc_compare('Brc1ccccc1C=C', 'Cc1ccccc1Br', 't:i simThreshold:0.9') FROM dual;



SELECT jc_tanimoto('Brc1ccccc1C=C', 'Cc1ccccc1Br') FROM dual;



SELECT jc_matchcount('OC(CC)=O', 'CC') FROM dual;

9

Search in memory

Hit retrieval and visualization

DB

structures queries DB search Hits Manipulation Memory search

Retrieving results 1.

// Retrieve and store cd_id values of hits int[] cdIds = jChemSearch.getResults(); // Create a prepared statement for SQL queries PreparedStatement ps = connectionHandler.getConnection() .prepareStatement("SELECT cd_formula, cd_molweight from " + strTable + " WHERE cd_id = ?"); for (int i = 0; i < cdIds.length; i++) { // Set SQL parameter ps.setInt(1, cdIds[i]); // Execute query and print fields ResultSet rs = ps.executeQuery(); if (rs.next()) { System.out.printf("ID: %d, formula: %s, mass: %.3f\n", cdId, rs.getString(1), rs.getDouble(2)); } } (RetrievingDatabaseFieldsExample.java)

11

Search in database - results

Retrieving results 2.

12

// Specify database fields to retrieve ArrayList<String> fieldNames = new ArrayList<String>(); fieldNames.add("cd_formula"); fieldNames.add("cd_molweight"); // Create an empty ArrayList for returned database field values ArrayList<Object[]> fieldValues = new ArrayList<Object[]>(); // Retrieve result molecules // (the fieldValues list will also be filled) results = jChemSearch.getHitsAsMolecules( cdIds, options, fieldNames, fieldValues); // Print results for (int i = 0; i < cdIds.length; i++) { String formula = (String) fieldValues.get(i)[0]; Double mass = (Double) fieldValues.get(i)[1]; System.out.printf("ID: %d, formula: %s, mass: %.3f\n", cdIds[i], formula, mass); } (RetrievingDatabaseFieldsExample.java)

Search in database - results

Substructure hit coloring and alignment

13 13

Coloring Coloring & Alignment

Visualization

Query

Partial cleaning

14

Alignment Alignment & Partial clean

Visualization

 MolSearch

// create coloring options HitColoringAndAlignmentOptions coloringOptions = new HitColoringAndAlignmentOptions(); // set parameters coloringOptions.setColoringEnabled(true); coloringOptions.setHitColor(Color.RED); coloringOptions.setNonHitColor(Color.GREEN); coloringOptions.setAlignmentMode( HitColoringAndAlignmentOptions.ALIGNMENT_ROTATE); // create HitDisplayTool hdt = new HitDisplayTool(coloringOptions, searchOptions, query); // get decorated result Molecule result = hdt.getHit(target); (HitColoringExample.java, RotateExample.java)

15

Visualization

JChem Users Guide http://www.chemaxon.com/jchem/doc/user/ JChem Developers Guide http://www.chemaxon.com/jchem/doc/guide/ JChem Base JSP demo page http://www.chemaxon.com/jchem/examples/db_search/index.jsp API documentation http://www.chemaxon.com/jchem/doc/api/index.html

16

Links