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


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');