1 #!/usr/bin/perl -w
   2 #
   3 # $RCSfile: DBTablesToTextFiles.pl,v $
   4 # $Date: 2008/01/30 21:44:44 $
   5 # $Revision: 1.19 $
   6 #
   7 # Author: Manish Sud <msud@san.rr.com>
   8 #
   9 # Copyright (C) 2004-2008 Manish Sud. All rights reserved.
  10 #
  11 # This file is part of MayaChemTools.
  12 #
  13 # MayaChemTools is free software; you can redistribute it and/or modify it under
  14 # the terms of the GNU Lesser General Public License as published by the Free
  15 # Software Foundation; either version 3 of the License, or (at your option) any
  16 # later version.
  17 #
  18 # MayaChemTools is distributed in the hope that it will be useful, but without
  19 # any warranty; without even the implied warranty of merchantability of fitness
  20 # for a particular purpose.  See the GNU Lesser General Public License for more
  21 # details.
  22 #
  23 # You should have received a copy of the GNU Lesser General Public License
  24 # along with MayaChemTools; if not, see <http://www.gnu.org/licenses/> or
  25 # write to the Free Software Foundation Inc., 59 Temple Place, Suite 330,
  26 # Boston, MA, 02111-1307, USA.
  27 #
  28 
  29 use 5.006;
  30 use strict;
  31 use FindBin; use lib "$FindBin::Bin/../lib";
  32 use Getopt::Long;
  33 use File::Basename;
  34 use Text::ParseWords;
  35 use Benchmark;
  36 use FileUtil;
  37 use DBUtil;
  38 
  39 my($ScriptName, %Options, $StartTime, $EndTime, $TotalTime);
  40 
  41 # Autoflush STDOUT
  42 $| = 1;
  43 
  44 # Starting message...
  45 $ScriptName = basename($0);
  46 print "\n$ScriptName: Starting...\n\n";
  47 $StartTime = new Benchmark;
  48 
  49 # Get the options and setup script...
  50 SetupScriptUsage();
  51 if ($Options{help} || @ARGV < 1) {
  52   die GetUsageFromPod("$FindBin::Bin/$ScriptName");
  53 }
  54 
  55 my($DBDriver, $DBHost, $DBName, $DBUser, $DBPassword, $DBMode, $ExportDataLabels, $ExportLOBs, $OutDelim, $OutQuote, $ReplaceNullStr);
  56 ProcessOptions();
  57 
  58 # Collect input parameters information...
  59 print "Checking input parameter(s)...\n";
  60 my(@DBSQLStatements, @DBTextFiles);
  61 RetrieveDBInfo();
  62 
  63 # Connect to database...
  64 my($DBHandle);
  65 print "Connecting to $DBDriver:database=$DBName as $DBUser...\n";
  66 $DBHandle = DBConnect($DBDriver, $DBName, $DBHost, $DBUser, $DBPassword);
  67 
  68 # Generate text files...
  69 if (@DBTextFiles > 1) {
  70   print "Generating text files...\n";
  71 }
  72 my($Index, $TextFile, $SQL);
  73 TEXTFILE: for $Index (0 .. $#DBTextFiles) {
  74   $TextFile = $DBTextFiles[$Index];
  75   $SQL = $DBSQLStatements[$Index];
  76 
  77   if (@DBTextFiles > 1) {
  78     print "\nGenerating text file $TextFile...\n";
  79   }
  80   else {
  81     print "Generating text file $TextFile...\n";
  82   }
  83   print "Processing SQL statement \"$SQL\"...\n";
  84 
  85   if (!open TEXTFILE, ">$TextFile") {
  86     warn "Warning: Abandoning $TextFile generation: Couldn't open it: $! \n";
  87     next TEXTFILE;
  88   }
  89 
  90   if (DBSQLToTextFile($DBHandle, $SQL, \*TEXTFILE, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr)) {
  91     warn "Warning: Abandoning $TextFile generation...\n";
  92     next TEXTFILE;
  93   }
  94   close TEXTFILE;
  95 }
  96 print "\nDisconnecting from  $DBDriver:database=$DBName...\n";
  97 DBDisconnect($DBHandle);
  98 
  99 print "$ScriptName:Done...\n\n";
 100 
 101 $EndTime = new Benchmark;
 102 $TotalTime = timediff ($EndTime, $StartTime);
 103 print "Total time: ", timestr($TotalTime), "\n";
 104 
 105 ###############################################################################
 106 
 107 # Collect input parameters information...
 108 sub RetrieveDBInfo {
 109   my($FileExt, $UserFileName, $FileDBPrefix);
 110 
 111   # Setup out file ext...
 112   $FileExt = ($Options{outdelim} =~ /^tab$/i) ? "tsv" : "csv";
 113 
 114   # Get user specified information...
 115   $UserFileName = "";
 116   if ($Options{root} && (@ARGV == 1)) {
 117     my($RootFileDir, $RootFileName, $RootFileExt) = ParseFileName($Options{root});
 118     if ($RootFileName && $RootFileExt) {
 119       $UserFileName = $RootFileName;
 120     }
 121     else {
 122       $UserFileName = $Options{root};
 123     }
 124   }
 125   $FileDBPrefix = ($DBMode =~ /^exportdata$/i) ? "Export" : "Describe";
 126 
 127   my($TableName, $SQL, $FileName);
 128   # Go over all the input parameters...
 129   @DBSQLStatements = ();
 130   @DBTextFiles = ();
 131   for $TableName (@ARGV) {
 132     $TableName = uc $TableName;
 133     $SQL = ($DBMode =~ /^exportdata$/i) ? DBSetupSelectSQL($DBDriver, $TableName) : DBSetupDescribeSQL($DBDriver, $TableName);
 134     push @DBSQLStatements, $SQL;
 135     $FileName = $UserFileName ? $UserFileName : ("$FileDBPrefix" . "$TableName");
 136     $FileName .= ".$FileExt";
 137     if (!$Options{overwrite}) {
 138       if (-e $FileName) {
 139 	die "Error: The file $FileName already exists.\n";
 140       }
 141     }
 142     push @DBTextFiles, $FileName;
 143   }
 144 }
 145 
 146 # Process option values...
 147 sub ProcessOptions {
 148 
 149   $DBDriver = $Options{dbdriver} ? $Options{dbdriver} : (exists $ENV{DBI_DRIVER} ? $ENV{DBI_DRIVER} : "") ;
 150   if ($DBDriver) {
 151     if ($DBDriver =~ /^oracle$/i) {
 152       $DBDriver = "Oracle";
 153     }
 154     elsif ($DBDriver =~ /^mysql$/i) {
 155       $DBDriver = "mysql";
 156     }
 157     else {
 158       if ($Options{dbdriver}) {
 159 	die "Error: The value specified, $DBDriver, for option \"-d --dbdriver\" is not valid. Allowed values: MySQL or Oracle\n";
 160       }
 161       else {
 162 	die "Error: The value specified, $DBDriver, using environment variable DBI_DRIVER not valid. Allowed values: MySQL or Oracle\n";
 163       }
 164     }
 165   }
 166   else {
 167     $DBDriver = "mysql";
 168   }
 169   $DBHost = $Options{dbhost} ? $Options{dbhost} : "127.0.0.1";
 170   $DBName = $Options{dbname} ? $Options{dbname} : "";
 171   if (!$DBName) {
 172     if ($DBDriver =~ /^mysql$/i) {
 173       $DBName = "mysql";
 174     }
 175     elsif ($DBDriver =~ /^Oracle$/i) {
 176       $DBName = exists $ENV{ORACLE_SID} ? $ENV{ORACLE_SID} : "";
 177     }
 178   }
 179   $DBUser = $Options{dbusername} ? $Options{dbusername} : (exists $ENV{DBI_USER} ? $ENV{DBI_USER} : "") ;
 180   if (!$DBUser) {
 181     die "Error: No database username specified. Use \"--dbusername\" option or environment variable DBI_USER to enter a valid value.\n";
 182   }
 183   $DBPassword = $Options{dbpassword} ? $Options{dbpassword} : (exists $ENV{DBI_PASS} ? $ENV{DBI_PASS} : "") ;
 184   if (!$DBPassword) {
 185     die "Error: No database password specified. Use \"--dbpassword\" option or environment variable DBI_PASS to enter a valid value.\n";
 186   }
 187   $DBMode = $Options{mode};
 188   $ExportLOBs = ($Options{exportlobs} =~ /^yes$/i) ? 1 : 0;
 189   $ExportDataLabels = ($DBMode =~ /^describetable$/i) ? 1 : (($Options{exportdatalabels} =~ /^yes$/i) ? 1 : 0);
 190 
 191   $OutDelim = ($Options{outdelim} =~ /^tab$/i ) ? "\t" : (($Options{outdelim} =~ /^semicolon$/i) ? "\;" : "\,");
 192   $OutQuote = ($Options{quote} =~ /^yes$/i) ? 1 : 0;
 193 
 194   $ReplaceNullStr = (defined($Options{replacenullstr}) && length($Options{replacenullstr})) ? $Options{replacenullstr} : "";
 195 }
 196 
 197 # Setup script usage  and retrieve command line arguments specified using various options...
 198 sub SetupScriptUsage {
 199 
 200   # Retrieve all the options...
 201   %Options = ();
 202   $Options{mode} = "exportdata";
 203   $Options{exportlobs} = "no";
 204   $Options{exportdatalabels} = "yes";
 205   $Options{outdelim} = "comma";
 206   $Options{quote} = "yes";
 207   if (!GetOptions(\%Options, "dbdriver|d=s", "dbhost=s", "dbname=s", "dbpassword=s", "dbusername=s", "exportdatalabels=s", "exportlobs=s", "help|h",  "mode|m=s", "outdelim=s", "overwrite|o", "quote|q=s", "root|r=s", "replacenullstr=s", "workingdir|w=s")) {
 208     die "\nTo get a list of valid options and their values, use \"$ScriptName -h\" or\n\"perl -S $ScriptName -h\" command and try again...\n";
 209   }
 210   if ($Options{workingdir}) {
 211     if (! -d $Options{workingdir}) {
 212       die "Error: The value specified, $Options{workingdir}, for option \"-w --workingdir\" is not a directory name.\n";
 213     }
 214     chdir $Options{workingdir} or die "Error: Couldn't chdir $Options{workingdir}: $! \n";
 215   }
 216   if ($Options{exportdatalabels} !~ /(^(yes|no)$)/i) {
 217     die "Error: The value specified, $Options{exportlobs}, for option \"--exportdatalabels\" is not valid. Allowed values: yes or no\n";
 218   }
 219   if ($Options{exportlobs} !~ /(^(yes|no)$)/i) {
 220     die "Error: The value specified, $Options{exportlobs}, for option \"--exportlobs\" is not valid. Allowed values: yes or no\n";
 221   }
 222   if ($Options{mode} !~ /(^(exportdata|describetable)$)/i) {
 223     die "Error: The value specified, $Options{mode}, for option \"-m --mode\" is not valid. Allowed values: exportdata, or describetable\n";
 224   }
 225   if ($Options{outdelim} !~ /^(comma|semicolon|tab)$/i) {
 226     die "Error: The value specified, $Options{outdelim}, for option \"--outdelim\" is not valid. Allowed values: comma, tab, or semicolon\n";
 227   }
 228   if ($Options{quote} !~ /^(yes|no)$/i) {
 229     die "Error: The value specified, $Options{quote}, for option \"-q --quote\" is not valid. Allowed values: yes or no\n";
 230   }
 231 }
 232