Applications - Design and Use of Relational Databases in Chemistry - page 157

a table of failed SMILES can be maintained. This can be accomplished by
modifying the add _ new _ structure function as follows.
Create Table error_log (smi text,
attempt timestamp(0) Default current_timestamp );
Create Function add_new_structure() Returns Trigger As $EOSQL$
Begin
NEW.smi = isosmiles(NEW.smi);
NEW.cansmi = cansmiles(NEW.smi);
NEW.fp = fp(NEW.smi);
Return NEW;
Exception
When OTHERS Then
Insert Into error_log (smi) Values (NEW.smi);
Return Null;
End;
$EOSQL$ Language plpgsql;
The use of the exception clause traps errors in any of the isosmiles ,
cansmiles , or fp functions. The creation of the error _ log table is
shown above to contain these errors for later inspection and correction.
The id column is defined as a primary key. This causes an index to be
created, which will facilitate joining the structure table with other tables
yet to be created. The smiles column is defined to be unique, which also
automatically creates an index. This column will not be used as a key, but
the unique index will allow fast lookups on this table if a particular struc-
ture is desired. The final definition of this schema creates an index on the
cansmiles column. This will not be a unique index, but it will allow fast
lookup of structures by canonical SMILES.
Searching for structures in the structure table can be done in many ways,
but several important methods are discussed here. First, a structure can be
located directly using the following structured query language (SQL).
Select id, smi from structure Where cansmi=cansmiles
('c1ccccc1C(=O)NC'));
The actual value of the SMILES, here c1ccccc1C(=O)NC would come from
the user, perhaps using a drawing widget or other method. The use of
the cansmiles function assures that the SMILES string value will corre-
spond to a value stored in the cansmiles column. Recall that the cansmi
column was populated using the cansmiles function in the trigger func-
tion shown above. If the SMILES input by the user is an isomeric SMILES,
the SQL above will locate all isomers. Related stereoisomers all share the
same canonical SMILES. If it is desired to locate only the isomeric SMILES,
the following SQL could be used.
Select id, smi From structure Where smi=isosmiles('F[C@H](C)Cl'));