Appendix - Design and Use of Relational Databases in Chemistry - page 208

This script can be used as follows.
perl smiloader drugs <drugs.smi | psql mydb
A.9.2 Sdfloader
#! /usr/bin/perl
$schema = $ARGV[0];
die "Schema name required\nusage: loader schema\n" unless
($schema);
print <<EOSQL;
Drop Schema If Exists $schema Cascade;
Create Schema $schema;
Create Sequence $schema.structure_id_seq;
Create Table $schema.sdf (id Integer Default Nextval('$schema.
structure_id_seq'), molfile Text);
Create Table $schema.structure (id Integer Primary Key Default
Nextval('$schema.structure_id_seq'), name Text, isosmiles Text,
cansmiles Text, fp Bit Varying, coords Numeric[][3], atoms
Integer[]);
Create Table $schema.property (id Integer References $schema.
structure (id), name Text, tvalue Text, nvalue Numeric);
Copy $schema.sdf (molfile) From Stdin;
EOSQL
while (<stdin>) {
if (/\$\$\$\$/) {
print;
} else {
s/\r//; chomp; print; print "\\n";
}
}
print <<EOSQL;
\\.
set search_path=openbabel;
Insert Into $schema.structure (id, name, isosmiles, coords, atoms)
Select id, (molfile_mol(molfile)).* from $schema.sdf;
Update $schema.structure Set cansmiles=cansmiles(isosmiles) Where
valid(isosmiles);
Update $schema.structure Set fp=fp(cansmiles) Where
valid(cansmiles);
Alter Table $schema.sdf Add Constraint sdf_id_fk Foreign Key (id)
References $schema.structure (id);
Insert into $schema.property (id, name, tvalue)