Migration guide to JChem PostgreSQL Cartrdige from JChem Oracle Cartridge

    The aim of this page is to provide help for migrating applications based on JChem Oracle Cartridge (JOC) API to JChem PostgreSQL (JPC) API by collecting their different solutions for the main functionality areas. This page supplements the information available on the below linked video and pages.

    Migration demo

    Comparison documentation

    JChem Oracle Cartridge API documentation

    JChem Oracle Cartridge Developer guide

    JChem PostgreSQL API documentation

    General migration guide

    How to apply in JPC the business logic set as CREATE INDEX parameters in JOC

    JOC provides many CREATE INDEX parameters for setting different business logic to the given structure column. JPC does not provide any CREATE INDEX parameter. The business logic settings can be applied differently, mainly by the configuration of Molecule types.

    JOC also provides some ALTER INDEX parameters, but ALTER INDEX cannot be applied on JPC domain indexes (chemindex and sortedchemindex).

    JOC Parameter(s) JPC solution Comment
    JChem table specific parameters JChemPropertiesTable
    regenerateTable    		 N/A JChem tables are not supported in JPC
    type of structures (molecules, queries, reactions, any structures, markush) structureType specification is not needed
    markush type is not supported
    stereo assumption mode (absolute or relative) absoluteStereo in Molecule type file:
    stereoAssumption parameter
    standardizer configuration std_config
    std_conf:sql    		 in Molecule type file:
    standardizerAction setting or
    standardizerFile parameter
    fingerprint configuration pat_length
    fp_size
    fp_bit    		 in Molecule type file the following parameters:
    fpLenght
    fpEdges
    fpOnes
    tautomer mode tautomerEqualityMode in Molecule type file:
    tautomer parameter
    duplicate filtering duplicateFiltering N/A recommended solution:
    execute duplicate search against the table before inserting a structure into it
    if multiple structures are to be inserted, batch this operation using a temporary table
    considering tautomers in duplicate search by default TDF in Molecule type file:
    tautomer parameter
    apply the Molecule type file name as subtype of the molecule type column    		 Note, that in this case the specified tautomer mode will be used in case of substructure and full fragment searches as well.
    chemical term columns autoCalcCt standard SQL using the appropriate chemterm function see example here
    specifying table name where non indexable structures are stored errorTableName this method is provided See here how to check for invalid structures in the table or in the index.

    How to migrate jc_compare search cases

    In JOC, there are many search options implemented. See their list at jc_compare. JPC has only a few search options. The table below contains mainly only those JOC search options which have their JPC equivalent or almost equivalent.

    General search syntax in JOC

    SELECT * FROM <tablename> where <JOC_owner>.jc_compare(<column>, '<query_structure>', '<parameters>')=1;

    where

    • <parameters>: all the available search types, the additional options and their possible combinations

    General search syntax in JPC

    SELECT * FROM <tablename> where '<query_structure>' <operator> <column>;

    or

    SELECT * FROM <tablename> where query_transform('<query_structure>', '<parameters>') <operator> <column>;

    where

    • <operator>: |=|, |<|, |<=|, |>|, |~|, |<~|, |~>|
    • <parameters>: options (IGNOREISOTOPE, IGNORECHARGE, DBSMARKEDONLY, IGNORETETRAHEDRALSTEREO and their combinations)
    JOC Parameters JPC solution Comment
    duplicate search t:d |=| in JOC jc_equals also provides duplicate search
    substructure search t:s |<| in JOC jc_contains also provides substructure search
    full fragment search t:ff |<=| works the same way in JPC as in JOC
    full structure search t:f N/A recommended solution:
    FULLFRAGMENT search with an additional condition for fragment count matching - using fragmentCount chemical term - requiring the same fragment count for the query and target
    see example here
    superstructure search t:u |>| works the same way in JPC as in JOC
    similarity search t:i |~|
    |<~|
    |~>|    		 in JOC jc_tanimoto and jc_dissimilarity also provide similarity search
    dissimilarityThreshold
    simThreshold    		 dissimilarity threshold can be defined as shown here
    dissimilarityMetric N/A only Tanimoto metric is supported in JPC
    tautomer search tautomerSearch in Molecule type file:
    tautomer parameter
    the type file name applied in <type>_search    		 JPC provides variety of tautomer combinations for duplicate and substructure search: GENERIC, NORMAL_CANONIC_GENERIC_HYBRID, NORMAL_CANONIC_NORMAL_GENERIC_HYBRID
    see details here
    isotope isotope IGNOREISOTOPE
    only in substructure and full fragment search
    charge charge IGNORECHARGE
    only in substructure and full fragment search
    double bond stereo doubleBondStereo DBSMARKEDONLY
    only in substructure and full fragment search
    tetrahedral stereo ignore tetrahedral stereo IGNORETETRAHEDRALSTEREO
    only in substructure and full fragment search

    To be also taken into account:

    JOC: Those SMILES/SMARTS strings which can be interpreted even in SMILES and SMARTS, the default interpretation mode is SMARTS in case of query structures. JPC: Those SMILES/SMARTS strings which can be interpreted even in SMILES and SMARTS, the default interpretation mode is SMILES in case of query structures.

    How to migrate jc_standardize cases

    Refers to jc_standardizeb and their function analogues as well. In JPC, the standardization actions are defined in the Molecule type files. These Molecule type files must be defined upfront and need to be initialized by running service jchem-psql init.

    Syntax in JOC

    jc_standardize(structure IN VARCHAR2/CLOB/BLOB, param IN VARCHAR2) RETURN VARCHAR2/CLOB/BLOB;

    where param can be the standardizer action string or configuration

    Syntax in JPC

    standardize(structure::molecule('<type>'))

    where <type> is the name of Molecule type file containing the relevant standardizer configuration

    How to migrate jc_molconvert cases

    Refers to jc_molconvert analogues (like jc_molconvertc, jcf.molconvert, jcf.moconvertc, ...) as well.

    Syntax in JOC

    jc_molconvert (structure IN VARCHAR2/BLOB/CLOB, options_outputformat IN VARCHAR2) RETURN VARCHAR2/BLOB/CLOB;

    Syntax in JPC

    molconvert('structure', 'format')

    How to migrate jc_evaluate cases

    Refers to jc_evaluate analogues (like jc_evaluate_x, jcf.evaluate, jcf.evaluate_x, ...) as well.

    Syntax in JOC

    jc_evaluate ('structure' IN VARCHAR2/CLOB/BLOB, expression IN VARCHAR2, options IN VARCHAR2)

    where options refer to the chemical term expression

    Syntax in JPC

    chemterm('chemical_term','structure')

    How to migrate structure checkers/fixers

    Syntax in JOC

    Structure checker/fixer operations are executed as chemical terms.

    SELECT jc_evaluate_x('structure', 'chemTerms:check("checkerAction")') from dual;

SELECT jc_evaluate_x('structure', 'chemTerms:fix("checkerAction->fixerAction")') from dual;

    Syntax in JPC

    Structure checker/fixer operations are executed as chemical terms.

    SELECT chemterm('check(''checkerAction’')','molecule');
SELECT chemterm('fix(''checkerAction->fixerAction'')','molecule');