1 #!/usr/bin/perl -w
   2 #
   3 # $RCSfile: DBTablesToTextFiles.pl,v $
   4 # $Date: 2011/12/16 00:03:30 $
   5 # $Revision: 1.26 $
   6 #
   7 # Author: Manish Sud <msud@san.rr.com>
   8 #
   9 # Copyright (C) 2004-2012 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 strict;
  30 use FindBin; use lib "$FindBin::Bin/../lib";
  31 use Getopt::Long;
  32 use File::Basename;
  33 use Text::ParseWords;
  34 use Benchmark;
  35 use FileUtil;
  36 use DBUtil;
  37 
  38 my($ScriptName, %Options, $StartTime, $EndTime, $TotalTime);
  39 
  40 # Autoflush STDOUT
  41 $| = 1;
  42 
  43 # Starting message...
  44 $ScriptName = basename($0);
  45 print "\n$ScriptName: Starting...\n\n";
  46 $StartTime = new Benchmark;
  47 
  48 # Get the options and setup script...
  49 SetupScriptUsage();
  50 if ($Options{help} || @ARGV < 1) {
  51   die GetUsageFromPod("$FindBin::Bin/$ScriptName");
  52 }
  53 
  54 my($DBDriver, $DBHost, $DBName, $DBUser, $DBPassword, $DBMode, $ExportDataLabels, $ExportLOBs, $OutDelim, $OutQuote, $ReplaceNullStr);
  55 ProcessOptions();
  56 
  57 # Collect input parameters information...
  58 print "Checking input parameter(s)...\n";
  59 my(@DBSQLStatements, @DBTextFiles);
  60 RetrieveDBInfo();
  61 
  62 # Connect to database...
  63 my($DBHandle);
  64 print "Connecting to $DBDriver:database=$DBName as $DBUser...\n";
  65 $DBHandle = DBConnect($DBDriver, $DBName, $DBHost, $DBUser, $DBPassword);
  66 
  67 # Generate text files...
  68 if (@DBTextFiles > 1) {
  69   print "Generating text files...\n";
  70 }
  71 my($Index, $TextFile, $SQL);
  72 TEXTFILE: for $Index (0 .. $#DBTextFiles) {
  73   $TextFile = $DBTextFiles[$Index];
  74   $SQL = $DBSQLStatements[$Index];
  75 
  76   if (@DBTextFiles > 1) {
  77     print "\nGenerating text file $TextFile...\n";
  78   }
  79   else {
  80     print "Generating text file $TextFile...\n";
  81   }
  82   print "Processing SQL statement \"$SQL\"...\n";
  83 
  84   if (!open TEXTFILE, ">$TextFile") {
  85     warn "Warning: Abandoning $TextFile generation: Couldn't open it: $! \n";
  86     next TEXTFILE;
  87   }
  88 
  89   if (DBSQLToTextFile($DBHandle, $SQL, \*TEXTFILE, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr)) {
  90     warn "Warning: Abandoning $TextFile generation...\n";
  91     next TEXTFILE;
  92   }
  93   close TEXTFILE;
  94 }
  95 print "\nDisconnecting from  $DBDriver:database=$DBName...\n";
  96 DBDisconnect($DBHandle);
  97 
  98 print "$ScriptName:Done...\n\n";
  99 
 100 $EndTime = new Benchmark;
 101 $TotalTime = timediff ($EndTime, $StartTime);
 102 print "Total time: ", timestr($TotalTime), "\n";
 103 
 104 ###############################################################################
 105 
 106 # Collect input parameters information...
 107 sub RetrieveDBInfo {
 108   my($FileExt, $UserFileName, $FileDBPrefix);
 109 
 110   # Setup out file ext...
 111   $FileExt = ($Options{outdelim} =~ /^tab$/i) ? "tsv" : "csv";
 112 
 113   # Get user specified information...
 114   $UserFileName = "";
 115   if ($Options{root} && (@ARGV == 1)) {
 116     my($RootFileDir, $RootFileName, $RootFileExt) = ParseFileName($Options{root});
 117     if ($RootFileName && $RootFileExt) {
 118       $UserFileName = $RootFileName;
 119     }
 120     else {
 121       $UserFileName = $Options{root};
 122     }
 123   }
 124   $FileDBPrefix = ($DBMode =~ /^exportdata$/i) ? "Export" : "Describe";
 125 
 126   my($TableName, $SQL, $FileName);
 127   # Go over all the input parameters...
 128   @DBSQLStatements = ();
 129   @DBTextFiles = ();
 130   for $TableName (@ARGV) {
 131     $TableName = uc $TableName;
 132     $SQL = ($DBMode =~ /^exportdata$/i) ? DBSetupSelectSQL($DBDriver, $TableName) : DBSetupDescribeSQL($DBDriver, $TableName);
 133     push @DBSQLStatements, $SQL;
 134     $FileName = $UserFileName ? $UserFileName : ("$FileDBPrefix" . "$TableName");
 135     $FileName .= ".$FileExt";
 136     if (!$Options{overwrite}) {
 137       if (-e $FileName) {
 138         die "Error: The file $FileName already exists.\n";
 139       }
 140     }
 141     push @DBTextFiles, $FileName;
 142   }
 143 }
 144 
 145 # Process option values...
 146 sub ProcessOptions {
 147 
 148   $DBDriver = $Options{dbdriver} ? $Options{dbdriver} : (exists $ENV{DBI_DRIVER} ? $ENV{DBI_DRIVER} : "") ;
 149   if ($DBDriver) {
 150     if ($DBDriver =~ /^oracle$/i) {
 151       $DBDriver = "Oracle";
 152     }
 153     elsif ($DBDriver =~ /^mysql$/i) {
 154       $DBDriver = "mysql";
 155     }
 156     else {
 157       if ($Options{dbdriver}) {
 158         die "Error: The value specified, $DBDriver, for option \"-d --dbdriver\" is not valid. Allowed values: MySQL or Oracle\n";
 159       }
 160       else {
 161         die "Error: The value specified, $DBDriver, using environment variable DBI_DRIVER not valid. Allowed values: MySQL or Oracle\n";
 162       }
 163     }
 164   }
 165   else {
 166     $DBDriver = "mysql";
 167   }
 168   $DBHost = $Options{dbhost} ? $Options{dbhost} : "127.0.0.1";
 169   $DBName = $Options{dbname} ? $Options{dbname} : "";
 170   if (!$DBName) {
 171     if ($DBDriver =~ /^mysql$/i) {
 172       $DBName = "mysql";
 173     }
 174     elsif ($DBDriver =~ /^Oracle$/i) {
 175       $DBName = exists $ENV{ORACLE_SID} ? $ENV{ORACLE_SID} : "";
 176     }
 177   }
 178   $DBUser = $Options{dbusername} ? $Options{dbusername} : (exists $ENV{DBI_USER} ? $ENV{DBI_USER} : "") ;
 179   if (!$DBUser) {
 180     die "Error: No database username specified. Use \"--dbusername\" option or environment variable DBI_USER to enter a valid value.\n";
 181   }
 182   $DBPassword = $Options{dbpassword} ? $Options{dbpassword} : (exists $ENV{DBI_PASS} ? $ENV{DBI_PASS} : "") ;
 183   if (!$DBPassword) {
 184     die "Error: No database password specified. Use \"--dbpassword\" option or environment variable DBI_PASS to enter a valid value.\n";
 185   }
 186   $DBMode = $Options{mode};
 187   $ExportLOBs = ($Options{exportlobs} =~ /^yes$/i) ? 1 : 0;
 188   $ExportDataLabels = ($DBMode =~ /^describetable$/i) ? 1 : (($Options{exportdatalabels} =~ /^yes$/i) ? 1 : 0);
 189 
 190   $OutDelim = ($Options{outdelim} =~ /^tab$/i ) ? "\t" : (($Options{outdelim} =~ /^semicolon$/i) ? "\;" : "\,");
 191   $OutQuote = ($Options{quote} =~ /^yes$/i) ? 1 : 0;
 192 
 193   $ReplaceNullStr = (defined($Options{replacenullstr}) && length($Options{replacenullstr})) ? $Options{replacenullstr} : "";
 194 }
 195 
 196 # Setup script usage  and retrieve command line arguments specified using various options...
 197 sub SetupScriptUsage {
 198 
 199   # Retrieve all the options...
 200   %Options = ();
 201   $Options{mode} = "exportdata";
 202   $Options{exportlobs} = "no";
 203   $Options{exportdatalabels} = "yes";
 204   $Options{outdelim} = "comma";
 205   $Options{quote} = "yes";
 206   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")) {
 207     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";
 208   }
 209   if ($Options{workingdir}) {
 210     if (! -d $Options{workingdir}) {
 211       die "Error: The value specified, $Options{workingdir}, for option \"-w --workingdir\" is not a directory name.\n";
 212     }
 213     chdir $Options{workingdir} or die "Error: Couldn't chdir $Options{workingdir}: $! \n";
 214   }
 215   if ($Options{exportdatalabels} !~ /^(yes|no)$/i) {
 216     die "Error: The value specified, $Options{exportlobs}, for option \"--exportdatalabels\" is not valid. Allowed values: yes or no\n";
 217   }
 218   if ($Options{exportlobs} !~ /^(yes|no)$/i) {
 219     die "Error: The value specified, $Options{exportlobs}, for option \"--exportlobs\" is not valid. Allowed values: yes or no\n";
 220   }
 221   if ($Options{mode} !~ /^(exportdata|describetable)$/i) {
 222     die "Error: The value specified, $Options{mode}, for option \"-m --mode\" is not valid. Allowed values: exportdata, or describetable\n";
 223   }
 224   if ($Options{outdelim} !~ /^(comma|semicolon|tab)$/i) {
 225     die "Error: The value specified, $Options{outdelim}, for option \"--outdelim\" is not valid. Allowed values: comma, tab, or semicolon\n";
 226   }
 227   if ($Options{quote} !~ /^(yes|no)$/i) {
 228     die "Error: The value specified, $Options{quote}, for option \"-q --quote\" is not valid. Allowed values: yes or no\n";
 229   }
 230 }
 231