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

print <<EOSQL;
Create Schema vla4;
Create Sequence vla4.structure_id_seq;
Create Table vla4.sdf (id Integer
Default Nextval('vla4.structure_id_seq'), molfile Text);
Create Table vla4.structure (id Integer Primary Key
Default Nextval('vla4.structure_id_seq'), name Text, cansmiles
Text,
coord Numeric[][3], atom Integer[]);
Create Table vla4.property (id Integer References vla4.structure (id),
name Text, tvalue Text, nvalue Numeric);
Copy vla4.sdf (molfile) From Stdin;
EOSQL
while (<stdin>) {
if (/\$\$\$\$/) {
print;
} else {
s/\r//; chomp; print; print "\\n";
}
}
The script contains a few SQL statements needed to create the schema
and tables. Notice that a named sequence, val4.structure _ id _ seq
is created. This is used to create a new structure.id whenever a new
row is added to either the vla4.sdf or vla4.structure table. The
structure.id column is chosen as the primary key. The SDF file is read
from standard input and separated into individual molfiles using the $$$$
delimiter. The output from this script, named loader , is piped into the
psql command as follows.
perl loader <vla-4.sdf | psql mydb
The vla4.structure table is chosen to contain the primary key instead
of the vla4.sdf table . This allows the vla4.sdf table to be dropped at a
later time without upsetting the relational integrity of the overall schema.
It would be possible to define the vla4.sdf.id column as a foreign key to
the vla4.structure.id column when the tables are created. However,
if that were done it would not be possible to insert into the vla4.sdf
file without a corresponding row in the vla4.structure table. After the
vla4.structure table is populated (see below), a foreign key constraint
will be added to the vla4.sdf table.
At this point, the vla4.sdf table has been created in the database
named mydb . The molfile column contains the molfile for each structure
in the sdf file. The sdf.id column contains a unique integer that can be
used to relate the vla4.property table. Notice the use of the default
value nextval('vla4.structure _ id _ sql') in the SQL statement
that creates the vla4.sdf table. This causes the sdf.id column to contain