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