#!/usr/bin/perl # mkmolstat.pl N. Haider, 2003 # example script which generates the MySQL table "molstat" # within the database "moldb": molstat contains (e.g.) number of # atoms, bonds, rings, etc. for each molecule; # field identification codes are listed and described in checkmol.pas; # the checkmol binary must be installed in /usr/local/bin) # ATTENTION: an already existing table "molstat" 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 -X \$i 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 'molstat'. This may fail, if 'molstat' doesn't exist. # Thus we put an eval around it. eval { $dbh->do("DROP TABLE molstat") }; print "Dropping molstat failed: $@\n" if $@; # Create a new table 'molstat'. This must not fail, thus we don't # catch errors. $createcmd="CREATE TABLE molstat ( mol_id int(11) NOT NULL default '0', n_atoms smallint(6) NOT NULL default '0', n_bonds smallint(6) NOT NULL default '0', n_rings smallint(6) NOT NULL default '0', n_QA smallint(6) NOT NULL default '0', n_QB smallint(6) NOT NULL default '0', n_chg smallint(6) NOT NULL default '0', n_C1 smallint(6) NOT NULL default '0', n_C2 smallint(6) NOT NULL default '0', n_C smallint(6) NOT NULL default '0', n_CHB1p smallint(6) NOT NULL default '0', n_CHB2p smallint(6) NOT NULL default '0', n_CHB3p smallint(6) NOT NULL default '0', n_CHB4 smallint(6) NOT NULL default '0', n_O2 smallint(6) NOT NULL default '0', n_O3 smallint(6) NOT NULL default '0', n_N1 smallint(6) NOT NULL default '0', n_N2 smallint(6) NOT NULL default '0', n_N3 smallint(6) NOT NULL default '0', n_S smallint(6) NOT NULL default '0', n_SeTe smallint(6) NOT NULL default '0', n_F smallint(6) NOT NULL default '0', n_Cl smallint(6) NOT NULL default '0', n_Br smallint(6) NOT NULL default '0', n_I smallint(6) NOT NULL default '0', n_P smallint(6) NOT NULL default '0', n_B smallint(6) NOT NULL default '0', n_Met smallint(6) NOT NULL default '0', n_X smallint(6) NOT NULL default '0', n_b1 smallint(6) NOT NULL default '0', n_b2 smallint(6) NOT NULL default '0', n_b3 smallint(6) NOT NULL default '0', n_bar smallint(6) NOT NULL default '0', n_C1O smallint(6) NOT NULL default '0', n_C2O smallint(6) NOT NULL default '0', n_CN smallint(6) NOT NULL default '0', n_XY smallint(6) NOT NULL default '0', n_r3 smallint(6) NOT NULL default '0', n_r4 smallint(6) NOT NULL default '0', n_r5 smallint(6) NOT NULL default '0', n_r6 smallint(6) NOT NULL default '0', n_r7 smallint(6) NOT NULL default '0', n_r8 smallint(6) NOT NULL default '0', n_r9 smallint(6) NOT NULL default '0', n_r10 smallint(6) NOT NULL default '0', n_r11 smallint(6) NOT NULL default '0', n_r12 smallint(6) NOT NULL default '0', n_r13p smallint(6) NOT NULL default '0', n_rN smallint(6) NOT NULL default '0', n_rN1 smallint(6) NOT NULL default '0', n_rN2 smallint(6) NOT NULL default '0', n_rN3p smallint(6) NOT NULL default '0', n_rO smallint(6) NOT NULL default '0', n_rO1 smallint(6) NOT NULL default '0', n_rO2p smallint(6) NOT NULL default '0', n_rS smallint(6) NOT NULL default '0', n_rX smallint(6) NOT NULL default '0', n_rar smallint(6) NOT NULL default '0', PRIMARY KEY (mol_id) ) TYPE=MyISAM COMMENT='Molecular statistics';"; $dbh->do($createcmd); # 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 ("$mol_id,$codeline\n"); # echo whole line as a progress indicator if (index($codeline,"unknown") < 0) { $dbh->do("INSERT INTO molstat VALUES ( $mol_id,$codeline ) "); } } # end while ($line.... # Disconnect from the database. $dbh->disconnect();