Also, note that the SQL scripts give useful examples of using the cartridge functionality in each category.

It is safe to repeatedly run the scripts create.sql and clean.sql. The create.sql script creates a bunch of objects, types, etc. The clean.sql script removes them. Neither script will modify any other objects, types, or tables owned by c$dcischem or any other user on the system. The only components created by the create.sql script which are not removed by the clean.sql script are the license and progob tables. These two tables are preserved by the clean.sql script for convenience.

When one runs create.sql after clean.sql, one will see warnings that the tables license and progob already exist, however these warnings don't impact the outcome of the cartridge re-installation.

After running clean.sql, the database will be left in its original pre-cartridge state. One can then delete the license and progob tables, if desired, to eliminate all remnants of the cartridge.

Note that the user c$dcischem will continue to exist after running clean.sql and any non-cartridge tables, objects, etc. owned by c$dcischem will be unaffected by either clean.sql or create.sql.

The other way to remove the cartridge is to drop the user c$dcischem. Note that this does not remove public synonyms or roles, so these should be dropped manually (or via clean.sql).

  SQL> drop user c$dcischem cascade;

This will remove the user c$dcischem and all dependent objects. It will be necessary to repeat the entire installation procedure if this method is chosen.

The valid form for the operator which uses the index is:

  SQL> select * from <table> where exact(<column>, <SMILES>) = 1;

This index makes no assumptions about the content of the indexed column. The applications are responsible for normalizing both the data in the indexed column and and queries to the exact() operator. This allows the ddexact indextype to properly handle both Unique and Absolute SMILES without complication. In the following example, the USMILES column is normalized using smi2cansmi(smiles, 0) and the ASMILES column is normalized using smi2cansmi(smiles, 1). Note also that when performing the exact() query on a column, the query is normalized using the same method as the column data.

  SQL> create table test (id number, usmiles varchar2(4000),
         asmiles varchar2(4000));

  SQL> desc test;
  Name                               Null?    Type
  --------------------------------- -------- ----------------------------
  ID                                         NUMBER
  USMILES                                    VARCHAR2(4000)
  ASMILES                                    VARCHAR2(4000)

  SQL> insert into test values (1234, smi2cansmi('Cl/C=C/Cl', 0),
                                      smi2cansmi('Cl/C=C/Cl', 1));

  SQL> select * from test;
  
        ID  USMILES   ASMILES
  --------  -------   -------
    1234    ClC=CCl   Cl/C=C/Cl

  SQL> create index usmi_index on test(usmiles) indextype 
         is c$dcischem.ddexact;
  SQL> create index asmi_index on test(asmiles) indextype 
         is c$dcischem.ddexact;

  SQL> select * from test 
         where exact(usmiles, smi2cansmi('Cl/C=C/Cl', 0)) = 1;

        ID  USMILES   ASMILES
  --------  -------   -------
    1234    ClC=CCl   Cl/C=C/Cl

  SQL> select * from test 
         where exact(asmiles, smi2cansmi('Cl/C=C/Cl', 1)) = 1;

        ID  USMILES   ASMILES
  --------  -------   -------
    1234    ClC=CCl   Cl/C=C/Cl

NOTE: Because this indextype accesses the base table during a index scan, operations which modify the base table based on the results of the index scan aren't allowed. This is similar to the case where a trigger on a column attempts to modify the column data. The message which one receives is the following:

  SQL> delete from <table> where exact(smiles, 'CCN') = 1;

  ERROR at line 1:
  ORA-29903: error in executing ODCIIndexFetch() routine
  ORA-29400: data cartridge error
  ERROR ORA-04091: table C$DCISCHEM.T1 is mutating, trigger/function may
  not see it
  C$DCISCHEM-101: CARTRIDGE ERROR
  ORA-06512: at "C$DCISCHEM.DDEXACT_IM", line 159
  ORA-06512: at line 1

In order to perform this function, one must use two steps. One option would be:

  SQL> create table <temptable> (zapcol) as
          select rowid from <table> where exact(smiles, 'CCN') = 1;
  SQL> delete from <table> where rowid in 
          (select zapcol from <temptable>)

This indextype supports the graph() and tautomer() operators. It creates a tabular index of the graph and tautomeric information (hydrogen count and net charge) for rapid retrieval. The indexed search only requires an index lookup based on the graph of the query, followed by comparison of the net charge and hydrogen count for a tautomer search.

This indextype uses a special data table within Oracle to maintain its index data. This table is kept in sync with the base table in real time and strictly obeys the Oracle transaction model for DML operations. The name of the auxillary data table is: <index name> || '_DDT'.

The index can be created on a VARCHAR2 column with the following general statement:

  SQL> create index <index name> on <table> ( <SMILES column> )
         indextype is c$dcischem.ddgraph;

The valid forms for the operators to use the index are:

  SQL> select * from <table> where graph(<SMILES column>, <SMILES>) = 1;
  SQL> select * from <table> where tautomer(<SMILES column>, <SMILES>) = 1;

Unlike the ddexact index, the ddgraph indextype always interprets the column as SMILES. Invalid SMILES are rejected. There is no concern with the ddgraph indextype as to whether the SMILES are Unique or Absolute, as stereochemical and isomeric information are discarded for the graph() and tautomer() lookups.

NOTE: As with the ddexact indextype, this indextype accesses the base table during a index scan. See the previous section on the limitations to performing modifications of the base table based on the results of an index scan.

Reaction role index (ddrole):

This indextype supports component level searches for exact molecules within reactions by role. It creates a tabular index of each component in every reaction, by role. Component level searches are implemented simply by a index lookup of the component, followed by validation of the role.

This indextype uses a special data table within Oracle to maintain its index data. This table is kept in sync with the base table in real time and strictly obeys the Oracle transaction model for all DML operations. The name of the auxillary data table is: <index name> || '_DDT'.

The index can be created on a VARCHAR2 column with the following general statement:

  SQL> create index <index name> on <table> ( <SMILES column> )
         indextype is c$dcischem.ddrole;

The valid forms for the operators to use the index are:

  SQL> select * from <table> where reactant(<SMILES column>, <SMILES>) = 1;
  SQL> select * from <table> where agent(<SMILES column>, <SMILES>) = 1;
  SQL> select * from <table> where product(<SMILES column>, <SMILES>) = 1;

If a row value contains a molecule SMILES, that row is ignored for the ddrole index. Like the ddexact index, the ddrole index makes no assumptions about the canonicalization of the SMILES in the index column. Hence, the column must be normalized by the application to the appropriate SMILES and the query on the column must match that normalization (see the explanation re. ddexact above).

NOTE: As with the ddexact indextype, this indextype accesses the base table during a index scan. See the previous section on the limitations to performing modifications of the base table based on the results of an index scan.

SMARTS and Similarity search index (ddblob):

This index caches the SMILES and fingerprints in a single binary large object for rapid searching. Substructure and similarity searches are implemented through this blob-based image of the structural data. The blob is persistant; it is automatically stored in the Oracle database and retrieved for processing. The blob is kept synchronized with the base table in real time and strictly obeys the Oracle transaction model for all DML operations. The name of the auxillary data table which contains the BLOBs is: <index name> || '_DDT'.

The index can be created on either a SMILES or fingerprint column, provided that the fingerprints are all the same size. If the index is created on a SMILES column, all six searches are implemented on that column: contains(), matches(), tanimoto(), tversky(), euclid(), and fingertest(). If the index is created on a fingerprint column, the four fingerprint-specific searches (tanimoto(), tversky(), euclid(), and fingertest()) are supported for that column.

The index creation command takes an optional parameter 'fpsize'. This value is the number of bits that the blob index will use for fingerprint creation.

When indexing a SMILES column, the blob will use the 'fpsize' parameter for the creation size for fingerprints within the internal blob index or a default value of 512 bits if the 'fpsize' is not specified.

When indexing a fingerprint column, all fingerprints within that column must match the 'fpsize' parameter. If the 'fpsize' parameter is not specified, the size of the first fingerprint encountered by the index code is used. Any fingerprints which do not match this size are rejected by the index.

The query for tanimoto(), tversky(), euclid(), and fingertest() can be either a fingerprint or a SMILES. If a SMILES, it will be used to create a normal fingerprint of the appropriate size for the index which will be used for comparison. If the query is a fingerprint, the size of the fingerprint must match the size of the fingerprint in the column (or in the blob for a SMILES index). The indexes and operators never perform automatic folding of fingerprints during searches.

The index can be created on a VARCHAR2 column with the following general statements:

  -- Default internal fingerprint size of 512 bits

  SQL> create index <index name> on 
         <table> ( <SMILES column> ) indextype is c$dcischem.ddblob;

  -- Specify internal fingerprint size of 64 bits

  SQL> create index <index name> on 
         <table> ( <SMILES column> ) indextype is c$dcischem.ddblob
         parameters ('fpsize=64');

  -- Fpsize taken from size of data in FP column (fpsize parameter, if it were
     specified, must match the size of data in the column)

  SQL> create index <index name> on 
         <table> ( <FP column> ) indextype is c$dcischem.ddblob;

The valid forms for the operators to use the index are:

  SQL> select * from <table> 
         where contains(<SMILES column>, <SMILES query>) = 1;
  SQL> select * from <table>
         where matches(<SMILES column>, <SMARTS query>) = 1;
  SQL> select * from <table>
         where tanimoto(<SMILES or FP column>, <SMILES or FP>) > 0.8;
  SQL> select * from <table>
         where tversky(<SMILES or FP column>, <SMILES or FP>, 0.5, 0.5) > 0.8;
  SQL> select * from <table>
         where euclid(<SMILES or FP column>, <SMILES or FP>) < 0.2;
  SQL> select * from <table>
         where fingertest(<SMILES or FP column>, <SMILES or FP>) = 1;

For a simple table, one need not create a fingerprint column or use the fingerprint indexes. One only needs to create an index on a SMILES column to enable all structural searching functions.

  -- We specified a preferred size for the internally generated fingerprints
  -- as 1024 bits, overriding the default of 512 bits.  These fingerprints
  -- are internal to the blob and aren't visible.

  SQL> create index asmi_blob_index on test(asmiles) indextype is
         c$dcischem.ddblob parameters ('fpsize=1024');
  
  SQL> select * from test where matches(asmiles, '[N;H1,H2]ccC=O') = 1;
  SQL> select * from test where tanimoto(asmiles, 'c1ccccc1') > 0.8;

  -- A fingerprint can be used directly as a query for tanimoto(),
  -- euclid(), fingertest() and tversky() instead of a SMILES.  The 
  -- fingerprint size must match the size of the fingerprints used in
  -- index creation.  In this case, 1024 bits.

  SQL> select * from test where 
         tanimoto(asmiles, smi2fp('OC(=O)CS', 0, 7, 1024)) > 0.8;

If one wants structural searches on multiple SMILES columns within a single table, then an index must be created on each SMILES column in the table. Furthermore, we can create and index fingerprint columns also. The index automatically will adjust fingerprint operations based on the size of the data in the fingerprint column. So, for example, if a column of fingerprints is added to our test table and populated:

  SQL> alter table test add (fp varchar (15));
  SQL> update test set fp = smi2fp(usmiles, 0, 7, 64);

  -- This index, on the FP column, uses the size of the fingerprints in 
  -- the column (64 bits, in this case) in index creation.

  SQL> create index fp_blob_index on test(fp) indextype is
         c$dcischem.ddblob;

  -- As with an index on a SMILES column, a SMILES query or a fingerprint
  -- can be used as a query for tanimoto(), euclid(), fingertest() and
  -- tversky().

  SQL> select smi2fp('OC(=O)CS', 0, 7, 64) from dual;

  SMI2FP('OC(=O)CS',0,7,64)
  --------------------------
  IM9vA4w127g.2

  -- The following two queries give identical results.

  SQL> select * from test where tanimoto(fp, 'IM9vA4w127g.2') > 0.8;
  SQL> select * from test where tanimoto(fp, 'OC(=O)CS') > 0.8;

There are some additional sublties explained using the following example queries:

  -- This screens and searches using the index; internally it uses 1024 bit
  -- fingerprints.  The output is generated using the functional form, at 
  -- its default of 512 bits.  Remember that the functional forms will use a
  -- fingerprint size of 512 bits unless one or both of the parameters is a 
  -- fingerprint.  The tanimoto values output by the functional form may not
  -- match our search criteria based on the folding-related differences
  -- between 1024-bit and 512-bit fingerprints.

  SQL> select smiles, tanimoto(asmiles, 'NCCc1ccccc1') from test
         where tanimoto(asmiles, 'NCCc1ccccc1') > 0.8;

  -- This screens and searches using the index; internally using 1024 bit
  -- fingerprints.  The output is generated using the functional form; this
  -- time calculated at 1024 bits.  By passing a fingerprint of 1024 bits
  -- to the functional form of tanimoto(), we force its use of 1024 bits.
  -- In this case, the tanimoto values output by the functional form will
  -- exactly match those used by the index.

  SQL> select smiles, tanimoto(asmiles, smi2fp('NCCc1ccccc1', 0, 7, 1024))
         from test where tanimoto(asmiles, 'NCCc1ccccc1') > 0.8;

  -- This searches using the index f1, using the 128 bit fingerprints from
  -- the actual column for comparison.  Again, the tanimoto values computed
  -- by the functional form are calculated at 512 bits and will likely not
  -- match those used by the index for the query.

  SQL> select smiles, tanimoto(fp, 'NCCc1ccccc1') from test
         where tanimoto(fp, 'NCCc1ccccc1') > 0.8;

For example, consider the clogp program object. The code for the clogp example is included in the CONTRIB subdirectory. It can be defined to the system with:

  SQL> insert into progob values
     ('clogp', '/oracle/o815/progob/clogptalk.sh', NULL);

This defines a program object with the symbolic name 'clogp'. It corresponds to the absolute path: '/oracle/o815/progob/clogptalk.sh', which must be an executable program object. In this case, the arguments to the program object (whitespace delimited) are NULL.

The file '/oracle/o815/progob/clogptalk.sh' looks like the following:

  #!/bin/sh

  DY_ROOT=/usr3/thor/day462/v462
  export DY_ROOT

  DY_LICENSEDATA=/usr/local/daylight/dy_license.dat
  export DY_LICENSEDATA

  LD_LIBRARY_PATH=/opt/SUNWspro/SC2.0:/usr/openwin/lib:/usr/lib:$DY_ROOT/lib
  export LD_LIBRARY_PATH

  $DY_ROOT/bin/clogptalk

When Oracle executes the program object, the environment is empty. Hence, all required environment variables must be defined in a shell wrapper before calling the actual program object.

Since program objects are initially executed under the Oracle userid, a strict security policy must be obeyed to prevent access to unauthorized Oracle privileges. The policy, checked and enforced by the cartridge code before executing any external program object, is the following:

1. If the program object is executable and SUID, then it is OK. (SUID requires privileges of the destination UID, so this means that whoever turned on the SUID bit has either root or owner privilege on the file.
2. If the program object is executable by and owned by Oracle, and the directory is not group or world writeable, then OK.
3. Otherwise, fail.

Running program objects are persistant on the server, and are executed on a per-user-session basis. The first time a program object is accessed within a user session it is started, and the program object will continue to run until the user session is terminated. If the program object crashes during the user session, the cartridge will restart it and retry the transaction. Because of the ability to restart program objects, program objects ideally should be stateless. All data required for a transaction should be passed to the program object within that transaction.

Program object communication through the ddpackage.fprogob() function is via VARCHAR2 or CLOB datatypes. The string types can contain one or more delimited lines of data. Because of this line-oriented data communication, most program objects will require a PL/SQL wrapper function to convert from Oracle datatypes to line-oriented data and to parse returned results. Continuing the clogp example, consider a simple function which will calculate clogp:

  create function fclogp (sosdata in varchar2) return number
   as
    v1 varchar2(4000);
    rc number;
    off1 number;
    off2 number;
   begin
    v1 := c$dcischem.ddpackage.fprogob('clogp', sosdata);
    off1 := instr(v1, ' ', 1, 1);
    off2 := instr(v1, ' ', 1, 2);
    rc := to_number(substr(v1, off1, off2 - off1));
    return rc;
   end;

Executing the fprogob() function directly using the clogptalk program object results in a full line of data being returned from the program object for each SMILES:

  SQL> select ddpackage.fprogob('clogp', 'c1ccccc1') from dual;

  DDPACKAGE.FPROGOB('CLOGP','C1CCCCC1')
  -----------------------------------------------------------------------------
  c1ccccc1 2.142 0 LogPstar: 2.13

The wrapper function fclogp() simply parses out the second field, converts it to a number, and returns it. The wrapper function hides the details from the user and allows the function to return the desired numeric value.

  SQL> select fclogp('c1ccccc1') from dual;

  FCLOGP('C1CCCCC1')
  ------------------
               2.142

More complex examples (eg. MOLFILE -> SMILES conversion) are included in the CONTRIB directory.

Once the optimizer code is enabled, one can view the cost estimates provided to Oracle and the execution plan chosen using the "explain plan" command in SQL.

The command "analyze index <indexname> compute statistics" will collect and store information about the size of the index table for the ddgraph, ddrole, and ddexact indextypes. This size information will then be used to refine the cost estimates provided to the Oracle optimizer. The ddblob indextype does not require a separate "analyze index" command; the blob index always knows its exact size.

Note that the cost and selectivity estimates are not going to be reasonable in all cases; developers may find that forcing a particular execution order will result in better query performance. Oracle SQL supports a number of hints which allow the developer to control the execution order. See the section on "Optimizer Hints" in the Oracle SQL Reference manual for more details.

It can also be useful to specify the functional form of a query in order to eliminate consideration of the index path for the execution plan. Consider the following query:

  SQL> select * from <table> where
          graph(smiles, 'c1ccccc1') = 1 and
          contains(smiles, 'C') = 1;

By default, (assuming that both required indexes exist) this query will use indexes for both the graph and contains search, and then will merge the results. In most cases, it would be much more efficient to perform the graph index search, then perform the functional version of contains() on the handful of hits returned from the graph search. The following query will force the contains() operation to occur functionally:

  SQL> select * from <table> where
          graph(smiles, 'c1ccccc1') = 1 and
          ddpackage.fcontains(smiles, 'C') = 1;

In order to disable the cartridge optimizer code, any computed statistics must be dropped with "analyze index <indexname> delete statistics". Then, The cartridge optimizer code can be disabled with the following commands:

  SQL> connect c$dcischem/secret
  SQL> disassociate statistics with indextypes ddblob using ddoptimizer;
  SQL> disassociate statistics with indextypes ddgraph using ddoptimizer;
  SQL> disassociate statistics with indextypes ddrole using ddoptimizer;
  SQL> disassociate statistics with indextypes ddexact using ddoptimizer;
  SQL> disassociate statistics with packages ddpackage using ddoptimizer;

Once the cartridge optimizer code is disabled, cost estimates revert to their default values of zero cost.