       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