JChem Cartridge User defined function example

getatomcount

The example can be found in the <JChem home>/examples/cartridge/user_def_func/getatomcount directory.

Consider the GetAtomCount Java class that returns the atom count of a structure. We would like to calculate the atom count of structures comming from a database table using the GetAtomCount Java class.

First of all we have to create a PL/SQL operator and function that can be used in SQL select statements. The operator gets the structure from a table as a parameter. For example:

SQL> select getatomcount(cd_smiles) from jchemtable;
Now create the PL/SQL function and operator (getatomcount_sql.sql):
SQL> 
CREATE FUNCTION getatomcount_func(query VARCHAR2) RETURN NUMBER AS
  BEGIN
    return to_number(jchem_core_pkg.send_user_func('GetAtomCount', '', query));
  END;
/
show errors;

CREATE OPERATOR getatomcount BINDING(VARCHAR2) RETURN NUMBER
  USING getatomcount_func;

The getatomcount_func function sends the query parameter to the GetAtomCount external Java class using the send_user_func function.

The GetAtomCount class has to implement the JChemCartModul interface. It's doFunc function gets the parameters sent by the getatomcount_func PL/SQL function (query).

Using ChemAxon's Molecule and MolHandler classes we can count the number of atoms in a structure. Let's see the GetAtomCount Java class (GetAtomCount.java):

import chemaxon.util.MolHandler;
import chemaxon.struc.Molecule;
import chemaxon.jchem.cartridge.JChemCartModule;

public class GetAtomCount implements JChemCartModule {

    public Object doFunc(String[] args) throws Exception {
	MolHandler mh = new MolHandler(args[0]);
	return new Integer(mh.getMolecule().getAtomCount());
    }
}

Compile the java file and include the parent directory of the the GetAtomCount.class file in the JCART_XCLASSPATH environment variable before starting JChem Server. Now our new function is ready to call from PL/SQL, for example:

SQL> select getatomcount(cd_smiles) from jchemtable;

Now makes the evaluation of the operator appearing in the where cluse faster.

Open the reg_user_def_ops.sql file from the cartridge directory of the JChem package with a text editor. Insert the name of the getatomcount operator:

CREATE OR REPLACE INDEXTYPE jc_idxtype
FOR
-----------------------------------------
-- START LISTING OPERATORS
-----------------------------------------
   getatomcount(VARCHAR2),
-----------------------------------------
-- STOP LISTING OPERATORS
-----------------------------------------
   jc_contains(VARCHAR2, VARCHAR2),
   jc_equals(VARCHAR2, VARCHAR2),
   jc_matchcount(VARCHAR2, VARCHAR2),
   jc_dissimilarity(VARCHAR2, VARCHAR2),
   jc_tanimoto(VARCHAR2, VARCHAR2),
   jc_logp(VARCHAR2),
   jc_logd(VARCHAR2, NUMBER),
   jc_pka(VARCHAR2, VARCHAR2, NUMBER),
   jc_tpsa(VARCHAR2),
   jc_molweight(VARCHAR2),
   jc_formula(VARCHAR2),
   jc_formula_eq(VARCHAR2, VARCHAR2)
USING jc_idxtype_im;

Save the file and close it. Drop all indexes (drop index idx_name) you've created before with the jc_idxtype indextype. Open the edited reg_user_def_ops.sql file in a PL/SQL worksheet and execute it. Now create your indexes again. An index has to be created on the table on which you want to use the getatomcount operator.

SQL> create index my_ind on jchemtable(cd_smiles) indextype is jc_idxtype;

If you've created the index on the table execute the following insert statement:

SQL> insert into jc_idx_udop values('getatomcount', 'GetAtomCount', '', '$1');

Finally another Java class is needed to evaluate the relational operator in the where clause. This class has to implement the JChemCartEvalModul interface. The name of this class has to be: GetAtomCount_eval.

The eval function of the class gets the value calculated by GetAtomCount and gets the parameters of the relational condition in the "opStr" String. The function has to decide whether the calculated value is appropiate to the relational condition or not. The GetAtomCount_eval class:

import java.util.StringTokenizer;
import chemaxon.jchem.cartridge.JChemCartEvalModule;

public class GetAtomCount_eval implements JChemCartEvalModule {

    public boolean eval(Object o, String opStr) throws Exception {
	int result = ((Integer)o).intValue();
	boolean isOk = false; 

	StringTokenizer st = new StringTokenizer(opStr, "/");
	String start = st.nextToken();
	String stop = st.nextToken();
	int op = Integer.parseInt(st.nextToken());

	if(stop.equals("null")) {
	    isOk = op == 4 ? result >= Integer.parseInt(start) : 
			     result > Integer.parseInt(start);
	} else if(start.equals("null")) {
	    isOk = op == 8 ? result <= Integer.parseInt(stop) : 
			     result < Integer.parseInt(stop);
	} else {
	    isOk = op == 13 ? result == Integer.parseInt(start) :
		   op == 12 ? result <= Integer.parseInt(stop) &&
			      result >= Integer.parseInt(start) :
		   op == 8  ? result <= Integer.parseInt(stop) && 
			      result > Integer.parseInt(start) :
		   op == 4  ? result < Integer.parseInt(stop) && 
			      result >= Integer.parseInt(start) :
		   result < Integer.parseInt(stop) && 
		   result > Integer.parseInt(start);
	}
	return isOk;
    }
}

Compile the java file and include the parent directory of the GetAtomCount.class file in the JCART_XCLASSPATH environment variable before starting JChem Server. The getatomcount operator is ready to evaluate faster then before if it is in the where clause.

SQL> select count(*) from jchemtable where getatomcount(cd_smiles) < 14;