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

Search in database - filtering 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

Memory search DB structures Manipulation queries DB search Hits Memory search

Search in memory 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(“C 1CCCCC1 ”); molSearch.setQuery(query); Molecule target = MolImporter.importMol(“OC 1CCCCC1 ”); 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(); } or 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

Hit retrieval and visualization DB structures Manipulation queries DB search Hits Memory search

Search in database - results 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. // 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) 12

Visualization Substructure hit coloring and alignment Coloring Coloring & Alignment Query 13 13

Visualization Partial cleaning Alignment Alignment & Partial clean 14

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

Links 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