Which Jcart table column to index for SMILES queries?

 

 

Mike,

I'm having a difficult time figuring out best practices for indexing jcart tables, and I can't figure out which column of a jchem table is the preferred format to index, and the implications of indexing different data types.  If there is a document on this, could someone point me to it?   Further, I don't understand why there are so many native columns in a jChem Cartrige table and which columns I should be interested in using. 

I suggest to consider using regular structure tables and much of this confusion will vanish -- like the example in this post: https://www.chemaxon.com/forum/viewpost43025.html#43025 . The example in this post uses default settings for the index including the fingerprint settings. For custom fingerprint settings create the JChem index with the fp_size, fp_bit and pat_length index parameters:

create index jcxwombat on wombat(structure) indextype is jchem.jc_idxtype
             parameters('fp_size=16,fp_bit=2,path_length=6');

ORA-29900: operator binding does not exist

We provide (VARCHAR2, VARCHAR2), (BLOB, BLOB) and (CLOB, CLOB) bindings for JC_EQUALS (, JC_CONTAINS and a couple of other operators) on the assumption that the target and query type will be same. Casting the query to the type of the target should help:

select cd_smiles from structure_jc1 where jc_equals(cd_structure, to_blob('CCCCCCCCCC[N+](C)(C)CCCS([O-])(=O)=O')) = 1;

In most cases, you can avoid having binding issues by using JC_COMPARE with which we provide more bindings:

select cd_smiles from structure_jc1 where jc_compare(cd_structure, 'CCCCCCCCCC[N+](C)(C)CCCS([O-])(=O)=O', 't:d') = 1;

Peter

A little background on the two table types:

When I approach a new development task, I like to sit down and read the background documentation first.  All of Chemaxon's support staff is very knowlageable, and VERY VERY helpful and responsive, but I don't like having to do work by trial/error/forumpost/response/trial....

Where is the comprehensive,correct documentation for the cartridge?

I thought it would be at: https://www.chemaxon.com/jchem/doc/dev/cartridge/index.html, so I read this thouroughly,

But now I am finding out that :

1). The document on the structure caching mechanism at http://www.chemaxon.com/jchem/doc/dev/cartridge/index.html was incorrect and out of date, and I had to get Szilard's input  to understand how the caching is refreshed.

2).  the example for how to build a correct jChem table is at  https://www.chemaxon.com/forum/viewpost43025.html#43025 style="font-size: 13px; line-height: 17px; text-align: -webkit-auto;"> , not in the docs.

3). "Slide #20 and #21 of the JChem Cartridge (JCC)  technical presentation http://www.chemaxon.com/conf/JChem_Cartridge.ppt." Is where I need to go to find the preferred table structure, even though reading the documenation at http://www.chemaxon.com/jchem/doc/dev/cartridge lead me to the conclusion that JChem Base structure tables were the best way to create a JChem Table.

4). I could not get jc_equals to work because I did not have Peter's very important note, that the query type must match the target type.  Where is that in the docs?  I know it specifies that the functions are overloaded, but I don't see where it says they are only overloaded for matching types.

5). clearly after Peter's post, jc_compare() is the only search function we ever want to use, now that I look at other examples, I see this, but on an initial reading of the documentation, It's not so clear that jc_compare() is the best way to go, and I'd even ask why publish all those other search functions when jc_compare() does it all, AND is overloaded to handle multiple types.

JChem Cartrige has LOTS of options, and LOTS of possible functionality, two table types, multiple columns that could be indexed, and multiple functions to do an equivalent search, if you multiply all the possible options, there are hundereds of different ways to do the exact same thing.  How do I navigate through all these possible options and solutions to find the solution that is best for my implementation, when I can't find the documentation to understand all the possibilities? 

Hi Guys,

I need a quick-start best practices guide for the following:

I have a chemical structure table of approximately 16 Million unique, distinct "molecules", represented as SMILES, Varchar(4000) no "reactions", no "queries", no "markush" structures, just small molecules. This table contains only a integer primary key and SMILES.

I would like to index these SMILES with a JChem Cartrige index, so that we can perform indexed substructure, tanimoto, superstructure, exact... matches against the table, both on the Absolute and Canonical (no sterio) smiles.

I would like to construct the indexing, so the results are similar to Daycart search results with the Daylight default FP.

The target for the searches will currently be SMILES or SMARTS, but later we'd like to be able to use chemaxon format for more complex matching.

I am happy to either build a new table, or just index the existing table.

Could I please get examples for the following:

Your reccomendation for building and indexing the structure table (or just indexing it if you reccomend using the existing table).

Some example searches against the table.

Thank you,

~mike

 

SQL> select cd_smiles from structure_jc1 where jc_equals(cd_structure, to_blob('CCCCCCCCCC[N+](C)(C)CCCS([O-])(=O)=O')) = 1;

select cd_smiles from structure_jc1 where jc_equals(cd_structure, to_blob('CCCCCCCCCC[N+](C)(C)CCCS([O-])(=O)=O')) = 1

                                                                                    *

ERROR at line 1:

ORA-29902: error in executing ODCIIndexStart() routine

ORA-01465: invalid hex number

I am sorry, this must work:

select cd_smiles from structure_jc1 where jc_equals(cd_structure, utl_raw.cast_to_raw('CCCCCCCCCC[N+](C)(C)CCCS([O-])(=O)=O')) = 1;

(Do you really want to work the JCB tables, BLOBs and stuff?)

P.

Hi Mike,

Your input about the documentation  started a long conversation and change of mails between JChem Cartridge developer and support people, it was very useful for us! We are going to improve are documentation based on these.

The first step we have taken is about your #4 and #5 comments:

We deprecate jc_equals and jc_contains in version 5.11 in favor of jc_compare. The only thing why we had jc_equals and jc_contains is the evolution of JChem Cartridge: fist only these features were available through Cartridge and jc_compare came only later with more funcionality. So we will advise the use of jc_compare in the documentation as you suggested.

Regards,

Tamas