Connection, database RDBMS

An introduction to SQL is provided in Chapter 3, but with an emphasis on examples relevant to chemical information rather than business information, which is often used in other books. Chapter 4 discusses some of the RDBMS that are available, namely Oracle, MySQL, and PostgreSQL. All of them use SQL to insert, delete, update, and select data. Chapter 5 shows ways in which client programs, including Web-based applications, are used to connect to the database server. Chapter 6 examines ways in which RDBMS are typically used to handle numerical and textual chemical information using relational tables. An example of using data files from the PubChem project is included. 

Because of the flexibility and familiarity of Web browsers, Web-based applications have become very popular and powerful. There are many Web-based interfaces to RDBMS. For example, phpPgAdmin1 is a popular Web application for PostgreSQL. PhpMyAdmin2 is popular with users of MySQL. These applications allow one to connect to a chosen database server, browse the schemas and tables in the database, and enter SQL commands. The output from SQL commands, especially table output, is formatted nicely. Other operations, such as creating, altering, or dropping tables and schemas, are also provided. These use an HTML form interface, with text boxes, radio buttons, check boxes, and other form elements familiar to all uses of the Web. 

It is also possible to use Excel to select data from an RDBMS using ODBC. The ODBC connection is first set up using the server name, database name, and user login information. Then a dialog window is used to select the desired rows and columns from the database. The returned values are inserted into the Excel table. Figure 5.3 show a sample session using Excel to... 

The connect function opens the connection to the RDBMS using the appropriate database name, host name, username, and password. The query and getresult methods execute the SQL statement and get the results. There are other methods available to get results, but these are discussed elsewhere.13... 

There are many uses for a chemical relational database. Some of these have been mentioned in earlier chapters. In this chapter, three general types of applications will be discussed. The purpose is not to present complete working applications, but to indicate important issues to consider when designing such applications. Sample schemas are proposed. The use within each application of the core functions described earlier is discussed. Each of these applications might be developed as a Web application or a client application on a user s desktop. Any computer language might be used, although the ability to connect to an RDBMS is essential. 

If SMILES is used to store molecular structures in a relational database management system(RDBMS), it may be necessary to extract the symbol and bond information for some client programs that expect a connection table. The smiles to symbol and smiles to bonds function shown in the next sections allow the symbol and bond information in a SMILES to be extracted as an array. Some client programs may prefer to process this information in rows, as if they were records in a file. The following plpgsql functions can be used to present the array elements as rows. Two functions are shown ctable (connection table) and symbol coords. The symbol coords function requires an array of coordinates in addition to the symbols. 

CAS = Chemical Abstracts Service CT = connection table HTSS = hierarchical tree substructure search lO = input-output RDBMS = relational database management system SEMA = stereochemically extended Morgan algorithm. 

The portion of the search that Involves exact structures and exact structural fragments is executed in two phases. First an 8-byte hash value is calculated from the canonlcallzed (10) connection table for each exact fragment and structure. The query facility of the RDBMS is then used to search the database for all compounds that contain structures or fragments with the calculated hash values. Next, the connection tables for qualifying compounds are retrieved and the relevant attributes for the atom and bond descriptors are compared to determine if the structures do, in fact, match the target structure. 

---