#!/usr/bin/perl # mkmolfg.pl N. Haider, 2003 # example script which generates the MySQL table "molfg" # within the database "moldb": molfg contains 8-character # codes for the functional groups contained in each molecule; # these codes are listed and described in checkmol.pas; # the checkmol binary must be installed in /usr/local/bin) # ATTENTION: an already existing table "molfg" will be erased! use DBI(); $user = "moldb-admin"; $password = "topsecret"; $cmd="#!/bin/sh cd /data/htdocs/moldb/molfiles for i in [0-9][0-9][0-9][0-9][0-9].mol do mol_id=`echo \$i |sed -e s/\.mol//g` echo -n \${mol_id}: /usr/local/bin/checkmol -c \$i echo \"\" done "; open (INFILE, "$cmd |") || die ("cannot open input file!"); my $dbh = DBI->connect("DBI:mysql:database=moldb;host=localhost", $user, $password, {'RaiseError' => 1}); # Drop table 'molfg'. This may fail, if 'molfg' doesn't exist. # Thus we put an eval around it. eval { $dbh->do("DROP TABLE molfg") }; print "Dropping molfg failed: $@\n" if $@; # Create a new table 'molfg'. This must not fail, thus we don't # catch errors. $dbh->do("CREATE TABLE molfg (mol_id int(11), fgcode char(8), KEY mol_id (mol_id,fgcode)) TYPE=MyISAM"); # Now write data into the table. while ($line = ) { $line =~ s/\n//g; # remove end-of-line character @myrec = split (/\:/, $line); # mol_id and rest are separated by a colon $mol_id = $myrec[0]; $codeline = $myrec[1]; print ("$line\n"); @code = split (/\;/, $codeline); # functional group codes are separated by semicolons $codecount = 0; while ($code[$codecount]) { $fg = $code[$codecount]; $dbh->do("INSERT INTO molfg VALUES ($mol_id, \"$fg\" )"); $codecount = $codecount + 1; } # end while ($code.... } # end while ($line.... # Disconnect from the database. $dbh->disconnect();