Three-Dimensional Molecular Structure Tables - Design and Use of Relational Databases in Chemistry - page 128

a unique serial integer each time a row is inserted. The structure.id
uses the same sequence, so that it may be kept in relational integrity with
the vla4.sdf table. Eventually, the sdf.id column will become a pri-
mary foreign key having a one-to-one relationship with structure.id .
This ensures that there must be exactly one sdf row for each structure
row. The property.id column has a foreign key constraint related to the
primary key column vla4.structure.id . This ensures relational integ-
rity among the tables in this vla4 schema.
The next step is to parse the molfile data into separate columns of the
table. The molfile _ mol function expects a molfile and returns a com-
posite data type named mol . This data type is defined as:
Create Type openbabel.mol As (name Text, cansmiles Text, coords
Float[][3], atoms Integer[]);
This composite type is created when the openbabel schema and its asso-
ciated functions are created using the code contained in the Appendix.
The atoms integer array is a map of the ordering of the atoms as they
occur in the input file and the order as they occur in the canonical Smiles.
This is not used here, but may be useful for other purposes. It is not neces-
sary to issue the create type SQL command again during the loading
of the data from the vla-4.sdf file. The data type may be used through-
out the database, once it is created. It does not belong only to the schema
openbabel , it simply resides there because its use is associated with
functions in that schema. If this data type is used throughout the data-
base, it might be moved to a more public schema.
The following SQL distributes the data returned from the molfile _
mol function into the columns of vla4.structure .
Insert Into vla4.structure (id, name, cansmiles, coords, atoms)
Select id, (openbabel.molfile_mol(molfile)).* from vla4.sdf;
Notice that the individual elements of the composite data type value
returned by the openbabel.molfile _ mol function must be used.
For example, (openbabel.molfile _ mol(molfile)).cansmiles
refers to the cansmiles element of the composite value returned by mol-
file _ mol function. The function return value (openbabel.mol-
file _ mol(molfile)).* refers collectively to all the elements of the
composite data type. The function return value openbabel.molfile _
mol(molfile) refers to the single composite data value itself. This cannot
be used in the insert statement shown before, since the insert requires
5 data values: id , name , cansmiles , coords , and atoms . At this point,
the table vla4.structure looks like the sample shown in Figure 11.2.
Only the first 30 rows are shown and the data in each column is truncated
for this preview.