       1 #!/usr/bin/perl -w
       2 #
       3 # File: DBTablesToTextFiles.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 DBUtil;
      34
      35 my($ScriptName, %Options, $StartTime, $EndTime, $TotalTime);
      36
      37 # Autoflush STDOUT
      38 $| = 1;
      39
      40 # Starting message...
      41 $ScriptName = basename($0);
      42 print "\n$ScriptName: Starting...\n\n";
      43 $StartTime = new Benchmark;
      44
      45 # Get the options and setup script...
      46 SetupScriptUsage();
      47 if ($Options{help} || @ARGV < 1) {
      48   die GetUsageFromPod("$FindBin::Bin/$ScriptName");
      49 }
      50
      51 my($DBDriver, $DBHost, $DBName, $DBUser, $DBPassword, $DBMode, $ExportDataLabels, $ExportLOBs, $OutDelim, $OutQuote, $ReplaceNullStr);
      52 ProcessOptions();
      53
      54 # Collect input parameters information...
      55 print "Checking input parameter(s)...\n";
      56 my(@DBSQLStatements, @DBTextFiles);
      57 RetrieveDBInfo();
      58
      59 # Connect to database...
      60 my($DBHandle);
      61 print "Connecting to $DBDriver:database=$DBName as $DBUser...\n";
      62 $DBHandle = DBConnect($DBDriver, $DBName, $DBHost, $DBUser, $DBPassword);
      63
      64 # Generate text files...
      65 if (@DBTextFiles > 1) {
      66   print "Generating text files...\n";
      67 }
      68 my($Index, $TextFile, $SQL);
      69 TEXTFILE: for $Index (0 .. $#DBTextFiles) {
      70   $TextFile = $DBTextFiles[$Index];
      71   $SQL = $DBSQLStatements[$Index];
      72
      73   if (@DBTextFiles > 1) {
      74     print "\nGenerating text file $TextFile...\n";
      75   }
      76   else {
      77     print "Generating text file $TextFile...\n";
      78   }
      79   print "Processing SQL statement \"$SQL\"...\n";
      80
      81   if (!open TEXTFILE, ">$TextFile") {
      82     warn "Warning: Abandoning $TextFile generation: Couldn't open it: $! \n";
      83     next TEXTFILE;
      84   }
      85
      86   if (DBSQLToTextFile($DBHandle, $SQL, \*TEXTFILE, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr)) {
      87     warn "Warning: Abandoning $TextFile generation...\n";
      88     next TEXTFILE;
      89   }
      90   close TEXTFILE;
      91 }
      92 print "\nDisconnecting from  $DBDriver:database=$DBName...\n";
      93 DBDisconnect($DBHandle);
      94
      95 print "$ScriptName:Done...\n\n";
      96
      97 $EndTime = new Benchmark;
      98 $TotalTime = timediff ($EndTime, $StartTime);
      99 print "Total time: ", timestr($TotalTime), "\n";
     100
     101 ###############################################################################
     102
     103 # Collect input parameters information...
     104 sub RetrieveDBInfo {
     105   my($FileExt, $UserFileName, $FileDBPrefix);
     106
     107   # Setup out file ext...
     108   $FileExt = ($Options{outdelim} =~ /^tab$/i) ? "tsv" : "csv";
     109
     110   # Get user specified information...
     111   $UserFileName = "";
     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   $FileDBPrefix = ($DBMode =~ /^exportdata$/i) ? "Export" : "Describe";
     122
     123   my($TableName, $SQL, $FileName);
     124   # Go over all the input parameters...
     125   @DBSQLStatements = ();
     126   @DBTextFiles = ();
     127   for $TableName (@ARGV) {
     128     $TableName = $TableName;
     129     $SQL = ($DBMode =~ /^exportdata$/i) ? DBSetupSelectSQL($DBDriver, $TableName) : DBSetupDescribeSQL($DBDriver, $TableName);
     130     push @DBSQLStatements, $SQL;
     131     $FileName = $UserFileName ? $UserFileName : ("$FileDBPrefix" . "$TableName");
     132     $FileName .= ".$FileExt";
     133     if (!$Options{overwrite}) {
     134       if (-e $FileName) {
     135         die "Error: The file $FileName already exists.\n";
     136       }
     137     }
     138     push @DBTextFiles, $FileName;
     139   }
     140 }
     141
     142 # Process option values...
     143 sub ProcessOptions {
     144
     145   $DBDriver = $Options{dbdriver} ? $Options{dbdriver} : (exists $ENV{DBI_DRIVER} ? $ENV{DBI_DRIVER} : "") ;
     146   if ($DBDriver) {
     147     if ($DBDriver =~ /^oracle$/i) {
     148       $DBDriver = "Oracle";
     149     }
     150     elsif ($DBDriver =~ /^mysql$/i) {
     151       $DBDriver = "mysql";
     152     }
     153     elsif ($DBDriver =~ /^(Pg|Postgres)$/i) {
     154       $DBDriver = "Pg";
     155     }
     156     else {
     157       if ($Options{dbdriver}) {
     158         die "Error: The value specified, $DBDriver, for option \"-d --dbdriver\" is not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n";
     159       }
     160       else {
     161         die "Error: The value specified, $DBDriver, using environment variable DBI_DRIVER not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n";
     162       }
     163     }
     164   }
     165   else {
     166     $DBDriver = "mysql";
     167   }
     168   $DBHost = $Options{dbhost} ? $Options{dbhost} : (exists $ENV{DBI_HOST} ? $ENV{DBI_HOST} : "127.0.0.1");
     169   $DBName = $Options{dbname} ? $Options{dbname} : (exists $ENV{DBI_NAME} ? $ENV{DBI_NAME} : "");
     170   if (!$DBName) {
     171     if ($DBDriver =~ /^mysql$/i) {
     172       $DBName = "mysql";
     173     }
     174     elsif ($DBDriver =~ /^pg|Postgres$/i) {
     175       $DBName = "postgres";
     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