Adding index-backed functions to JCart

Hi,

I would like to do something like the following to create several new functions, which extend the functions included in the JC_idxtype index, so that the functions will be evaluated using the JChem Index.  I see an example at https://www.chemaxon.com/jchem/doc/dev/cartridge/index.html#userdef referring to user-defined Java functions, but I am wondering if there is some way to do similar within PL\SQL.

CREATE OR REPLACE FUNCTION fISIN (smiles1 IN VARCHAR2, smiles2 IN VARCHAR2) RETURN NUMBER AS

BEGIN

---seems like I need to do something more here to have jc_compare run off the index?

     RETURN jcf.compare(smiles1,  smiles2, 't:u');

END;

CREATE OPERATOR ISIN BINDING(VARCHAR2, VARCHAR2) RETURN NUMBER USING fISIN;

alter indextype jc_idxtype add ISIN(VARCHAR2, VARCHAR2);

create public synonym ISIN for ISIN;

grant execute on JCHEM.ISIN to public;

What I am trying to do is create the three public synonyms listed below for operators which will use an JC_IDXTYP index when the synonym (or operator) is used in a WHERE clause.  

Hi,

User defined functions/operators are supported as described in the documentation. With one caveat: upgrade is not currently tested with user defined functions/operators and I expect them wiped out during upgrade. So having the prodecures of creating them (your functions/operators) in (easily repeatable) scripts as advised.

Reflecting on your proposed solution: you are probably aware that functions will not be executed in index scan mode, which will make the search potentially very slow. They will use index data, but they will still be orders of magnitude slower than operators executed in index scan mode.

My colleagues will provide help ASAP on how to formulate your particular search conditions (open valence and stuff) in terms of search options accepted by JChem functions/operators.

I should have added: My gut feeling is that it is not worth involving the user defined functions infrastructure just for the sake of adapting JChem functions to your internal API. In the typical case, I'd expect using simple wrapper functions to be the optimal solution for cases where functional execution is required. I'd expect operators to be typically needed for good perfomance in SQL queries. 

Hi,

Please, find information about the usage of JChem search options starting from this page: http://www.chemaxon.com/jchem/doc/user/query_searchoptions_index.html

We suggest the usage of the following search options for your questions:

Component(smiles1,smiles)2:

The desired result can be achieved by setting the Search type to Full fragment search: t:ff

http://www.chemaxon.com/jchem/doc/dev/cartridge/cartapi.html#jc_compare_toption

IsIn(smiles1,smiles2)

Use Substructure search: t:s.

Tautomer(smiles1,smiles2)

Use search option tautomerSearch:y

Or you can run Duplicate search t:d with tautomer duplicate filtering option: tdf:y

http://www.chemaxon.com/jchem/doc/dev/cartridge/cartapi.html#jc_compare_tautomerSearch

Substructure,
full structure and full fragment searches work on smarts, so you
should convert your structures to smarts by jc_molconvert operator:

http://www.chemaxon.com/jchem/doc/dev/cartridge/cartapi.html#jc_molconvert

Hi,

I am asking how to create a PL\SQL function that would be valid for:

alter indextype jc_idxtype add <MY NEW FUNCTION>(<INDEXED COLUMN NAME>, <SMILES QUERY>);

Mike,

This is a very involved section of the cartridge, and I do not have a complete understanding of this part,

but since I know you are on a deadline I will try to provide a partial answer anyway.

My colleagues my correct / expand my response later.

Probably Peter could have been more explicit above, but I think he meant this is not supported and not possible either.

User defined functions/operators are supported as described in the documentation.

I _think_ the reason why we can execute a Java user defined operator in index scan mode is because it integrates into the Java code of the JChem Server (a pure Java process), and this allows it to process all rows in a single call (between Orcle and the JChemServer).

The above is the only supported way to extend our catridge with domain index scan mode.

Otherwise it would be processed one by one anyway (much slower). 

The same (slow) performace can also be achived if you simply define your synonyms indepenently of our user defined function architecture.

Again, my colleages may expand / correct may answer later.

Best regards,

Szilard

Hi,

I think if there was unanything untold about this, Szilard told it. I can't add anything of substance.

Peter