Daylight Summer School 2001, June 5-7, Santa Fe, NM

DayCart Worksheet -- WITH HINTS

The Daylight Chemistry Cartridge provides chemical intelligence to an Oracle server. The interface is PL/SQL and the cartridge includes both functions as well as indexes. We'll be using SQLPLUS, which is an Oracle serial application, to execute SQL and PL/SQL commands. Oracle and DayCart has already been installed and tables have been created.

Starting SQLPLUS:

telnet day0, login mug/coffee
sqlplus mug/coffee
Some sample SQLPLUS to try:
select smi, tanimoto(smi, 'NCCc1ccccc1') from nci
where tanimoto(smi, 'NCCc1ccccc1') > 0.9
order by tanimoto(smi, 'NCCc1ccccc1') desc;

Oracle Searching Functions

  1. Find Dopamine (NCCc1ccc(O)c(O)c1) in the Oracle table "nci"

    The function utilized for an exact match search is:

    ddpackage.fexact (a IN VARCHAR2, b IN VARCHAR2) => NUMBER
    operator exact (a IN VARCHAR2, b IN VARCHAR2) => NUMBER
    

    The SQL command is:

    Select * from nci where exact (smi, smi2cansmi('NCCc1ccc(O)c(O)c1',0)) = 1;
    

    The exact, graph, and role operators expect the query to be canonicalized in the same fashion as the column being searched.

  2. Do a similarity search for Dopamine in nci imposing a tanimoto/euclidean filter of 0.7/0.05

    The function utilized for a tanimoto search is:

    ddpackage.ftanimoto (fp_or_smi1 IN VARCHAR2, fp_or_smi2 IN VARCHAR2) => NUMBER
    operator tanimoto (fp_or_smi1 IN VARCHAR2, fp_or_smi2 IN VARCHAR2) => NUMBER
    

    The SQL command is:
    Select smi, tanimoto (smi, 'NCCc1ccc(O)c(O)c1')  from nci
      where tanimoto(smi, 'NCCc1ccc(O)c(O)c1') > 0.7;
    

    The function utilized for a euclidean search is:

    ddpackage.feuclid (fp_or_smi1 IN VARCHAR2, fp_or_smi2 IN VARCHAR2) => NUMBER
    operator euclid (fp_or_smi1 IN VARCHAR2, fp_or_smi2 IN VARCHAR2) => NUMBER
    

    The SQL command is:

    Select * from nci where euclid (smi, 'NCCc1ccc(O)c(O)c1') < 0.05;
    

  3. Count the number of compounds in the nci table which have thiophenol as a substructure

    The function utilized for a substructre search is:
    function ddpackage.fcontains (smiles1 IN VARCHAR2, smiles2 IN VARCHAR2) => NUMBER
    operator contains (smiles1 IN VARCHAR2, smiles2 IN VARCHAR2) => NUMBER
    

    The SQL command is:
    Select count(1) from nci where contains(smi,'Sc1ccccc1') = 1;
    

  4. Perform a SMARTS query on the nci table

    The function utilized for a SMARTS query is:
    function ddpackage.fmatches (smiles IN VARCHAR2, smarts IN VARCHAR2) => NUMBER
    operator matches (smiles IN VARCHAR2, smarts IN VARCHAR2) => NUMBER
    

    The SQL command is:
    Select smi from nci where matches(smi,'[N,O]ccC=O') = 1;
    

  5. Find all the reactions from the reaction table "chemsynth" which used phenol as a reagent. Do the same except look for phenol as a product.

    The function utilized for a reactant search is:
    function ddpackage.freactant (smiles1 IN VARCHAR2, smiles2 IN VARCHAR2) => NUMBER
    operator reactant (smiles1 IN VARCHAR2, smiles2 IN VARCHAR2) => NUMBER
    

    The SQL command is:
    Select * from chemsynth where reactant(smi, smi2cansmi('Oc1ccccc1', 0)) = 1;
    

Oracle Molecule/Reaction Functions

  1. Find all compounds in the tcm01_mol table which have a molecular weight greater than 700.

    In this case we'll calculate it 'on the fly' to compare the values.

    The function utilized to calculate a molecular weight is:

    function ddpackage.fsmi2amw (smiles IN VARCHAR2) => NUMBER
    operator smi2amw (smiles IN VARCHAR2) => NUMBER
    

    The SQL:

    Select smi from tcm01_mol where smi2amw(smi) > 700
    

  2. Find all compounds in the tcm01_mol table which share the same molecular formula with Dopamine.

    Again, we'll calculate it 'on the fly' to compare the values. This one will take a few seconds as it's calculating the molecular formula for every SMILES in the TCM01_MOL table for comparison.

    The function utilized to calculate a molecular weight is:

    function ddpackage.fsmi2mf(smiles IN VARCHAR2) => NUMBER
    operator smi2mf(smiles IN VARCHAR2) => NUMBER
    
    The SQL command is:
    Select smi from tcm01_mol where smi2mf(smi) = smi2mf('NCCc1ccc(O)c(O)c1');
    

Daylight Chemical Information Systems Inc.
support@daylight.com