MayaChemTools

   1 #!/usr/bin/perl -w
   2 #
   3 # $RCSfile: DBSQLToTextFiles.pl,v $
   4 # $Date: 2015/02/28 20:46:19 $
   5 # $Revision: 1.32 $
   6 #
   7 # Author: Manish Sud <msud@san.rr.com>
   8 #
   9 # Copyright (C) 2015 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 TextUtil;
  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);
 110 
 111   # Setup out file ext...
 112   $FileExt = ($Options{outdelim} =~ /^tab$/i) ? "tsv" : "csv";
 113 
 114   # Get user specified information...
 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 
 125   my($Param, $SQL, $SQLNo, $FileName);
 126   # Go over all the input parameters...
 127   @DBSQLStatements = ();
 128   @DBTextFiles = ();
 129   $SQLNo = 0;
 130   PARAM: for $Param (@ARGV) {
 131     if ($DBMode =~ /^SQLStatement$/i) {
 132       $SQLNo++;
 133       $SQL = $Param;
 134       $FileName = ($Options{root} && (@ARGV == 1)) ? $UserFileName : ("SQLStatement" . "$SQLNo");
 135       $FileName .= ".$FileExt";
 136       if (!$Options{overwrite}) {
 137         if (-e $FileName) {
 138           die "Error: The file $FileName already exists.\n";
 139         }
 140       }
 141       push @DBSQLStatements, $SQL;
 142       push @DBTextFiles, $FileName;
 143     }
 144     elsif ($DBMode =~ /^SQLFile$/i) {
 145       # Read SQL file...
 146       my($SQLFile) = $Param;
 147       if (! -e $Param) {
 148         warn "Warning: Ignoring file $SQLFile: It doesn't exist\n";
 149         next PARAM;
 150       }
 151       if (!open SQLFILE, "$SQLFile" ) {
 152         warn "Warning: Ignoring file $SQLFile: Couldn't open it: $! \n";
 153         next PARAM;
 154       }
 155       my($Line, $SQLString);
 156       $SQLString = "";
 157       LINE: while ($Line = GetTextLine(\*SQLFILE)) {
 158            # Ignore comments line...
 159         if ($Line =~ /^#/ || $Line =~ /^-/) {
 160           next LINE;
 161         }
 162         $SQLString .= $Line;
 163       }
 164       close SQLFILE;
 165       # Extract select SQL statements...
 166       my($SQLFileDir, $SQLFileName, $SQLFileExt) = ParseFileName($SQLFile);
 167       my(@SQLSplits) = split "\;", $SQLString;
 168       $SQLNo = 0;
 169       SQLSPLIT: for $SQL (@SQLSplits) {
 170         $SQLNo++;
 171         $FileName = ($Options{root} && (@ARGV == 1)) ? ("$UserFileName" . "$SQLNo") : ("$SQLFileName" . "SQLStatement" . "$SQLNo");
 172         $FileName .= ".$FileExt";
 173         if (!$Options{overwrite}) {
 174           if (-e $FileName) {
 175             die "Error: The file $FileName already exists.\n";
 176           }
 177         }
 178         push @DBSQLStatements, $SQL;
 179         push @DBTextFiles, $FileName;
 180       }
 181     }
 182   }
 183 }
 184 
 185 # Process option values...
 186 sub ProcessOptions {
 187 
 188   $DBDriver = $Options{dbdriver} ? $Options{dbdriver} : (exists $ENV{DBI_DRIVER} ? $ENV{DBI_DRIVER} : "") ;
 189   if ($DBDriver) {
 190     if ($DBDriver =~ /^Oracle$/i) {
 191       $DBDriver = "Oracle";
 192     }
 193     elsif ($DBDriver =~ /^mysql$/i) {
 194       $DBDriver = "mysql";
 195     }
 196     elsif ($DBDriver =~ /^(Pg|Postgres)$/i) {
 197       $DBDriver = "Pg";
 198     }
 199     else {
 200       if ($Options{dbdriver}) {
 201         die "Error: The value specified, $DBDriver, for option \"-d --dbdriver\" is not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n";
 202       }
 203       else {
 204         die "Error: The value specified, $DBDriver, using environment variable DBI_DRIVER not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n";
 205       }
 206     }
 207   }
 208   else {
 209     $DBDriver = "mysql";
 210   }
 211   $DBHost = $Options{dbhost} ? $Options{dbhost} : (exists $ENV{DBI_HOST} ? $ENV{DBI_HOST} : "127.0.0.1");
 212   $DBName = $Options{dbname} ? $Options{dbname} : (exists $ENV{DBI_NAME} ? $ENV{DBI_NAME} : "");
 213   if (!$DBName) {
 214     if ($DBDriver =~ /^mysql$/i) {
 215       $DBName = "mysql";
 216     }
 217     elsif ($DBDriver =~ /^pg|Postgres$/i) {
 218       $DBName = "postgres";
 219     }
 220   }
 221   $DBUser = $Options{dbusername} ? $Options{dbusername} : (exists $ENV{DBI_USER} ? $ENV{DBI_USER} : "") ;
 222   if (!$DBUser) {
 223     die "Error: No database username specified. Use \"--dbusername\" option or environment variable DBI_USER to enter a valid value.\n";
 224   }
 225   $DBPassword = $Options{dbpassword} ? $Options{dbpassword} : (exists $ENV{DBI_PASS} ? $ENV{DBI_PASS} : "") ;
 226   if (!$DBPassword) {
 227     die "Error: No database password specified. Use \"--dbpassword\" option or environment variable DBI_PASS to enter a valid value.\n";
 228   }
 229   $DBMode = $Options{mode};
 230   $ExportLOBs = ($Options{exportlobs} =~ /^yes$/) ? 1 : 0;
 231   $ExportDataLabels = ($Options{exportdatalabels} =~ /^yes$/i) ? 1 : 0;
 232 
 233   $OutDelim = ($Options{outdelim} =~ /^tab$/i ) ? "\t" : (($Options{outdelim} =~ /^semicolon$/i) ? "\;" : "\,");
 234   $OutQuote = ($Options{quote} =~ /^yes$/i) ? 1 : 0;
 235 
 236   $ReplaceNullStr = (defined($Options{replacenullstr}) && length($Options{replacenullstr})) ? $Options{replacenullstr} : "";
 237 }
 238 
 239 # Setup script usage  and retrieve command line arguments specified using various options...
 240 sub SetupScriptUsage {
 241 
 242   # Retrieve all the options...
 243   %Options = ();
 244   $Options{mode} = "SQLStatement";
 245   $Options{exportlobs} = "no";
 246   $Options{exportdatalabels} = "yes";
 247   $Options{outdelim} = "comma";
 248   $Options{quote} = "yes";
 249 
 250   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")) {
 251     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";
 252   }
 253   if ($Options{workingdir}) {
 254     if (! -d $Options{workingdir}) {
 255       die "Error: The value specified, $Options{workingdir}, for option \"-w --workingdir\" is not a directory name.\n";
 256     }
 257     chdir $Options{workingdir} or die "Error: Couldn't chdir $Options{workingdir}: $! \n";
 258   }
 259   if ($Options{exportdatalabels} !~ /^(yes|no)$/i) {
 260     die "Error: The value specified, $Options{exportlobs}, for option \"--exportdatalabels\" is not valid. Allowed values: yes or no\n";
 261   }
 262   if ($Options{exportlobs} !~ /^(yes|no)$/i) {
 263     die "Error: The value specified, $Options{exportlobs}, for option \"--exportlobs\" is not valid. Allowed values: yes or no\n";
 264   }
 265   if ($Options{mode} !~ /^(SQLStatement|SQLFile)$/i) {
 266     die "Error: The value specified, $Options{mode}, for option \"-m --mode\" is not valid. Allowed values: SQLStatement or SQLFile\n";
 267   }
 268   if ($Options{outdelim} !~ /^(comma|semicolon|tab)$/i) {
 269     die "Error: The value specified, $Options{outdelim}, for option \"--outdelim\" is not valid. Allowed values: comma, tab, or semicolon\n";
 270   }
 271   if ($Options{quote} !~ /^(yes|no)$/i) {
 272     die "Error: The value specified, $Options{quote}, for option \"-q --quote\" is not valid. Allowed values: yes or no\n";
 273   }
 274 }
 275