Reactions and Transformations - Design and Use of Relational Databases in Chemistry - page 104

Examples of the is_std_smiles and make_std_smiles func-
tions are not shown here because neither of these approaches is ideal. In
the first case, using a check constraint, the nonstandard SMILES would
not be inserted, but the user would still be responsible for standardiz-
ing the SMILES and attempting the insert again. The second case using
a function is better, but it would still be possible to accidentally insert a
SMILES directly without the make_std_smiles function.
A better way to ensure chemical integrity of the SMILES is to use the
SQL trigger mechanism. An SQL trigger allows a function to intercept
data before it is inserted or updated and modify it if necessary.
Consider the following SQL, which uses PostgreSQL syntax.
Create Table atable (smiles text, id integer);
Create Function standardize() Returns Trigger As $EOSQL$
Declare
std_smiles Text;
smirks Text;
std Record;
Begin
For std In Select * from std_smirks Loop
std_smiles = xform(NEW.smiles, std.smirks);
If std_smiles != NEW.smiles Then
NEW.smiles = std_smiles;
End If;
End Loop;
Return NEW;
End;
$EOSQL$ Language plpgsql;
Create Trigger standardize Before Insert Or Update On atable
For Each Row Execute Procedure standardize();
The standardize function checks whether any smirks in the std _
smirks table, when used in the xform function, results in a modifica-
tion of the input SMILES stored in NEW.smiles . If the xform function
does return a transformed SMILES, then that transformed value is used
in place of the value the user attempted to insert. Finally, the trigger is
created using the standardized function to possibly modify any SMILE
before inserting or updating a table.
9.3.2 Multi-Component Transformations
SMIRKS allows one to express a multicomponent transformation as well
as unimolecular transformation as discussed previously. The following
SMIRKS shows how to transform a combination of an acid chloride and
an amine into an amide.
[C:1][C:2]([O,Cl:3])=[O:4].[C:5][N:6][H:99]>>[C:5][N:6][C:2]([C:1])=[O:4].[*:3][H:99]