SQL Functions

New SQL functions and data types can be used to extend a relational database. This is explained in Chapter 10 using PostgreSQL as an example. Ways in which three-dimensional molecular structures can be stored are examined in Chapter 11. This chapter also advocates using an RDBMS instead of molecular structure files and shows how this transition might be accomplished. 

Notice that single quotes are required for text values and that the built-in SQL function current timestamp can be used to supply a valid timestamp. The order of the columns need not be the same as the order used in the create command. However, the order of the data in the Values clause of the Insert command must correspond exactly with the order of the columns as named in the command. There is a short-cut syntax of the Insert command that does not require column names. It should be avoided in favor of the syntax example above. There are other... 

One type of SQL function is simply a collection of SQL statements. The input data type must be defined along with the data type of the result of... 

Once this function is created, it can be used just like any standard SQL function. This function uses PostgreSQL syntax. 

This chapter focused primarily on SMILES and canonical SMILES. It is feasible and common to use SMILES as the internal representation of molecular structure. Using the SQL functions described in this chapter,... 

Another choice for the internal representation of molecular structure is a molfile. It would be possible to construct SQL functions like those described in this chapter that would operate on this type of data. One disadvantage of molfiles is their greater size compared with SMILES. One advantage is that it is possible to store atomic coordinates, which is not possible with SMILES. There are other molecular file formats, but these are substantially the same as a molfile, except perhaps for specific atom types that may be of use in some database applications. 

Consider the extended SQL functions smiles to molfile and molfile to smiles. Having these functions available as SQL extensions allows one to export a molfile from a table containing SMILES. For example ... 

The (logical AND) operator and the (logical NOT) operator are used along with a nonstandard SQL function nbits set. This function and other related similarity functions are contained in the Appendix. The suitability of fragment keys, path-based or circular fingerprints, for any particular purpose is the subject of ongoing research.6... 

The following function is analogous to the fragment key function above. It uses a relational table to define fragments, a function to match SMILES and SMARTS (in this case count matches), and an aggregate SQL function to tally the results over all matched fragments. 

This chapter describes some of the aspects of SMIRKS and shows how it can be integrated into a relational database using new structural query language (SQL) functions. It discusses ways in which chemical transformations and reactions can be used to improve the robustness and usefulness of a chemical relational database. 

Suppose it is decided that the valence 5, noncharge-separated representation of the nitro group is to be used throughout the database. The SMIRKS [0 2]=[N+ 1][0- 3] [0 2]=[N+0 1]=[0+0 3], when applied to any charge-separated nitro group will transform it into the proper form. This is accomplished by creating another new SQL function, xform(smiles, smarts). As with the cansmiles and matches functions, this is an extension to standard SQL. Some form of this transformation function is... 

Since the range data type is not a standard SQL data type, the standard SQL operators cannot be used with this data type. However, new operators can be defined using SQL functions. Since the range data type is so similar to the float data type, implicit conversion to float is appropriate. This automatically allows many of the standard SQL operators to work with exact(=) range values. 

It is possible to write PostgreSQL functions in C. Because PostgreSQL itself is written in C, any extension functions can take advantage of the internal representation of RDMBS data. In fact, examples on which C functions might be based are the very functions used by the PostgreSQL RDMBS for processing standard SQL data. This puts any C functions on an equal footing with any built-in SQL functions. For this reason, C functions are the best choice when issues of speed are important. 

This defines the SQL function oe smiles, which is realized by calling the C function oe anysmiles in the shared object file gnova.so. The definition of the input arguments and return values is done in the same way as for a function written in any other procedural language. 

It may also be desirable to store the atomic coordinates read from these files. The purpose of parsing the coordinates from the file and putting them into a separate column is to enable use of the coordinates from within the database. If the column is properly defined as a numeric or float column, this will also ensure that the coordinates are proper numbers. If there is no need for atomic coordinates, it is not necessary to create a column for these. Later sections of this chapter will discuss ways in which these atomic coordinates might be used in SQL functions. 

There are a great many more elaborate methods that might be used to align three-dimensional molecular structures. Each of these could be implemented as new SQL functions and used in SQL statements like the ones above to produce the alignment to be applied to each structure of interest. 

While atomic coordinates form the fundamental structure of a molecule, many methods prefer to represent a three-dimensional structure as a surface or a shape. Of course, these are ultimately computed from the atomic coordinates and perhaps atomic partial charges. It may be possible to represent these molecular surfaces or shapes as an array of three-dimensional coordinates. These could be stored as a column in the database analogous to the array of atomic coordinates. It might be necessary to create another data type, perhaps a composite data type, to store molecular surfaces or shapes. Once these representations are stored, they can be used in new SQL functions to assist in searching based on molecular surface or shape. 

Most new client programs will benefit from a judicious use of both new server-side SQL functions and new client functions. It is wise to carefully consider which operations are best done on the RDBMS server and which are done using a client program. There are several suggestions to consider in designing the best system for a project. 

Writing a Web application to help such users search or update a database is more than simply offering a text box for them to type in SQL statements. The focus of section is not to show how a full Web application can be developed that uses SQL and an RDBMS server. There are some useful SQL functions that can benefit any Web application. 

This SQL function can then be used by any application that needs to include a Marvin sketcher. For example, a php application would include this code. 

The marvin sketch SQL function can be called from any client program using database access methods described in Chapter 5. The marvin sketch program can be modified to allow specification of the sketch applet size, loading an initial SMILES, or any other option provided by the Marvin sketch applet. 

Most Web applications that search a database will need to know the names of the columns in a table. Rather than coding this into the application, consider writing an SQL function to provide this information. The following SQL function returns the names of the columns in a particular table. 

It is beyond the scope of this book to present an entire Web application. The purpose of this discussion is to show how creating and using SQL functions can facilitate the creation of Web applications. The final example shows how SMILES stored in a database can be formatted for display using Marvin. The marvin view SQL function is defined as ... 

It might be tempting to add additional columns to the structure table to hold defined properties of each structure. Not all properties of a structure are appropriate for a table of structures. Some properties, for example, molecular weight and molecular formula are fixed properties of a structure with a unique value. These might be added as columns to the structure table. However, they could also be kept in another table related to the structure table. Consider also how often these values will be needed or if they will be searched. It is possible to easily compute these properties when needed, using SQL functions that take a SMILES argument. 

The following SQL function returns a compound id that can be used when processing data to be imported from an external source. The function returns the structure.id of an existing compound, or creates a new entry in the structure table for a new compound if necessary and returns that new structure.id. 

This Appendix contains structured query language (SQL) functions and tables too large or complex for the explanatory nature of the earlier chapters. These functions and tables are practical, rather than explanatory. They all follow PostgreSQL syntax. Some of them require the core functions described in Chapter 7 of this book, for example, match, cansmiles, and count matches. Those functions are available in the CHORD product from gNova, Inc. This Appendix also contains a PerlMol implementation, a FROWNS implementation, and an OpenBabel implementation of the core functions for PostgreSQL. 

Several SQL functions are discussed in the earlier chapters. This section shows the code needed to define these functions and make them available for use in a PostgreSQL database. 

---