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 Terms 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

• : 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

• : |=|, |<|, |<=|, |>|, |~|, |<~|, |~>|
• : 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 Terms - 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 _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 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 Terms 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');