Trigger SMILES

The cansmiles function can also be used to enforce an SQL constraint that the cansmi column must contain valid canonical SMILES. SQL constrains like this are commonly used to maintain data integrity. For example, the SQL clause check (cansmi = cansmiles (cansmi)) can be used in the initial creation of the table. One might also consider using an SQL trigger to handle an insert or update to a column that is required to contain canonical SMILES. 

This is not recommended. Instead, a trigger is a better way to handle canonical SMILES. 

Using a domain ensures that only appropriate data can be inserted into a column. If an attempt is made to insert invalid data, an error is reported. The user is then responsible for correcting the value, if possible and trying the insert again. The SQL trigger mechanism automates this process. The following SQL will not only ensure that the cansmi column contains canonical SMILES, it will correct problems where possible. 

This canonicalize function uses NEW to refer to the row being inserted or updated. NEW.cansmi refers to the value under question. The canonical SMILES is computed and compared to NEW. cansmi. If they are not the same, the NEW.smi value is replaced by the canonical SMILES value and the NEW row is returned. This NEW row is used by the RDBMS in place of the original row. The create trigger command causes this operation to be put into effect in the RDBMS. 

Why use the domain to define a smiles data type, but use a trigger for canonical SMILES First, SMILES is either valid or not. It is not feasible to... 

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. 

The standardize function checks whether any smirks in the std smirks table, when used in the xform function, results in a modification 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. 

The next statement defines a t r igger function that will be used whenever data is inserted or updated in this table. This function performs three important functions. First, it modifies the SMILES to be inserted into the smi column so that it contains the result of the isosmiles function. The isosmiles function is similar to the cansmiles function, except that it retains any stereochemistry that might be contained within the SMILES. If two stereoisomers are entered into this table, each will have a unique isosmiles value, but the same cansmiles value. In this way, they can be kept distinct, but their identical canonical SMILES shows them to be stereoisomers. The trigger function also computes the fingerprint and inserts it into the table when the SMILES is inserted or updated. 

There is some overhead in the use of indexes, constraints, triggers, etc. as discussed here. The overhead is incurred when rows are inserted or updated in the table. However, the value of this approach is that the data in the table are well validated and can be searched more reliably and efficiently. Direct lookups of canonical or stereo SMILES is simple and quick because of the index on these columns. Using the fingerprint column speeds up substructure search. Tautomers can be readily selected using the column of simple graphs. 

Scheme 19.10 Copper-triggered formation oxazepinone involving an intermediate Smiles rearrangement.

Scheme 19.SI Consecutive Ugi-Smiles/Heck reaction towards indole derivatives. Scheme 19.S2 Ugi-Smiles couplings followed by palladium-triggered ring cleavage of...

---