Add a molecular descriptor to an existing structural index

Molecular Descriptors license may be required.

By specifying parameters through a temporary database table

    1. Specify the MD parameters in a table:

      1. Create a temporary table to hold the MD parameters:

        create table md_conf (name varchar2(4000), commnt varchar2(4000), config clob)
      2. Create a row in the md_conf with the name, an optional comment and the xml configuration of the molecular descriptor.
        One way to do this is through Oracle's SQL*Loader utitilty:

        1. Create the data record to load in a file (called, say, inhouse-mdconf.dat) with the name, the comment and the configuration file name separated with commas:

          inhouse,Some comment here,inhouse-config.xml,

          where inhouse-config.xml is contains the molecular descriptor's configuration xml. (The syntax of molecular descriptors' configuration xmls is described here.)

        2. Create the loader control file (called, say, inhouse-sqlldr.ctrl) with content similar to this:

          LOAD DATA
          INFILE 'inhouse-mdconf.dat'
          APPEND
             INTO TABLE md_conf 
                FIELDS TERMINATED BY ','
                   (name      CHAR(200),
                    commnt    CHAR(4000),
                    ext_fname FILLER CHAR(40),
                    config    LOBFILE(ext_fname) TERMINATED BY EOF)
        3. Start the SQL*Loader similarily to this:

          sqlldr <jcart-user-schema>/<password> control=inhouse-sqlldr.ctrl
    2. Alter the index on the table using the ADDMD index parameter to specify the SQL query returning the MD spec:

      ALTER INDEX jcxmytable PARAMETERS('sep=~ ADDMD=select * from md_conf where name=''inhouse''')

    By specifying parameters through files on a filesystem local to the JChem Server

    1. Create a file specifying the number of descriptors to use (in the following example we assume just one) and for each descriptor:

      1. the name of the descriptors

      2. the comment for the descriptors

      3. the path to the descriptor specification XML file; similarly to the following: descriptor.count=1 dname.1=inhouse mdcomment.1=some comment mdsettingsfile.1=inhouse-config.xml

    2. Alter the index on the table using the ADDMD index parameter to specify the path to the file you've just created:

      ALTER INDEX jcxmytable PARAMETERS('sep=~ ADDMD=/home/jchem/config/md_descriptor_descriptor')

    Add alternative screening configurations

    Alternative screening configurations can be added — optionally — to an already existing molecular descriptor using the ADDMDCONF index parameter:

        ALTER INDEX <index-name> PARAMETERS('sep=~ ADDMDCONF=<descriptor-name>:<config-locator>')

    where <config-locator> is a specification of the screening configuration data through either of the methods (SQL query or operating system file) described for MD addition above. For example, if the configuration is specified in the md_conf table:

        ALTER INDEX jcxmytable PARAMETERS('sep=~ ADDMDCONF=inhouse:select * from md_conf where name=''d2OptPfEwan''')

    Similarity search by a molecular descriptor

    Specify the descriptor to use for the similarity search using the descriptorName option:

    1. Using jc_compare

      1. Queries on tables

        select id from mytable where jc_compare(structure, 'Brc1ccccc1',
             't:t dissimilarityThreshold:0.2 descriptorName:inhouse') = 1;
      2. Queries FROM DUAL In addition to the descriptor name, specify the index with which the descriptor is associated using the idxName option:

        select jc_compare(structure, 'Brc1ccccc1',
             't:t dissimilarityThreshold:0.2 descriptorName:inhouse idxName:jxcmytable') = 1;

    2. Using jc_dissimilarity

      1. Queries on tables

        select id from mytable where jc_dissimilarity(structure, 'Brc1ccccc1',
                 'descriptorName:inhouse') < 0.2;
      2. Queries FROM DUAL In addition to the descriptor name, specify the index with which the descriptor is associated using the idxName option:

        select jc_dissimilarity(structure, 'Brc1ccccc1',
                 'descriptorName:inhouse idxName:jxcmytable') = < 0.2;

      An alternative screening configuration can be optonally specified for the descriptor using the screenConfig option.

      • Specifying the name of a configuration associated with this particular descriptor:

        select id from mytable where jc_dissimilarity(structure, 'Brc1ccccc1',
             'descriptorName:inhouse screenConfig:d2OptPfEwan') < 0.2;
      • Specifying a SQL select statement which returns the configuration itself:

        select id from mytable where jc_dissimilarity(structure, 'Brc1ccccc1',
             'sep=~ t:t~descriptorName:phrmForH1~screenConfig:select config from md_conf where name = ''d2OptPfEwan''') < 0.2;