       1 #!/usr/bin/perl -w
       2 #
       3 # $RCSfile: DBTablesToTextFiles.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 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 = $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     elsif ($DBDriver =~ /^(Pg|Postgres)$/i) {
     157       $DBDriver = "Pg";
     158     }
     159     else {
     160       if ($Options{dbdriver}) {
     161         die "Error: The value specified, $DBDriver, for option \"-d --dbdriver\" is not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n";
     162       }
     163       else {
     164         die "Error: The value specified, $DBDriver, using environment variable DBI_DRIVER not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n";
     165       }
     166     }
     167   }
     168   else {
     169     $DBDriver = "mysql";
     170   }
     171   $DBHost = $Options{dbhost} ? $Options{dbhost} : (exists $ENV{DBI_HOST} ? $ENV{DBI_HOST} : "127.0.0.1");
     172   $DBName = $Options{dbname} ? $Options{dbname} : (exists $ENV{DBI_NAME} ? $ENV{DBI_NAME} : "");
     173   if (!$DBName) {
     174     if ($DBDriver =~ /^mysql$/i) {
     175       $DBName = "mysql";
     176     }
     177     elsif ($DBDriver =~ /^pg|Postgres$/i) {
     178       $DBName = "postgres";
     179     }
     180   }
     181   $DBUser = $Options{dbusername} ? $Options{dbusername} : (exists $ENV{DBI_USER} ? $ENV{DBI_USER} : "") ;
     182   if (!$DBUser) {
     183     die "Error: No database username specified. Use \"--dbusername\" option or environment variable DBI_USER to enter a valid value.\n";
     184   }
     185   $DBPassword = $Options{dbpassword} ? $Options{dbpassword} : (exists $ENV{DBI_PASS} ? $ENV{DBI_PASS} : "") ;
     186   if (!$DBPassword) {
     187     die "Error: No database password specified. Use \"--dbpassword\" option or environment variable DBI_PASS to enter a valid value.\n";
     188   }
     189   $DBMode = $Options{mode};
     190   $ExportLOBs = ($Options{exportlobs} =~ /^yes$/i) ? 1 : 0;
     191   $ExportDataLabels = ($DBMode =~ /^describetable$/i) ? 1 : (($Options{exportdatalabels} =~ /^yes$/i) ? 1 : 0);
     192
     193   $OutDelim = ($Options{outdelim} =~ /^tab$/i ) ? "\t" : (($Options{outdelim} =~ /^semicolon$/i) ? "\;" : "\,");
     194   $OutQuote = ($Options{quote} =~ /^yes$/i) ? 1 : 0;
     195
     196   $ReplaceNullStr = (defined($Options{replacenullstr}) && length($Options{replacenullstr})) ? $Options{replacenullstr} : "";
     197 }
     198
     199 # Setup script usage  and retrieve command line arguments specified using various options...
     200 sub SetupScriptUsage {
     201
     202   # Retrieve all the options...
     203   %Options = ();
     204   $Options{mode} = "exportdata";
     205   $Options{exportlobs} = "no";
     206   $Options{exportdatalabels} = "yes";
     207   $Options{outdelim} = "comma";
     208   $Options{quote} = "yes";
     209   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")) {
     210     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";
     211   }
     212   if ($Options{workingdir}) {
     213     if (! -d $Options{workingdir}) {
     214       die "Error: The value specified, $Options{workingdir}, for option \"-w --workingdir\" is not a directory name.\n";
     215     }
     216     chdir $Options{workingdir} or die "Error: Couldn't chdir $Options{workingdir}: $! \n";
     217   }
     218   if ($Options{exportdatalabels} !~ /^(yes|no)$/i) {
     219     die "Error: The value specified, $Options{exportlobs}, for option \"--exportdatalabels\" is not valid. Allowed values: yes or no\n";
     220   }
     221   if ($Options{exportlobs} !~ /^(yes|no)$/i) {
     222     die "Error: The value specified, $Options{exportlobs}, for option \"--exportlobs\" is not valid. Allowed values: yes or no\n";
     223   }
     224   if ($Options{mode} !~ /^(exportdata|describetable)$/i) {
     225     die "Error: The value specified, $Options{mode}, for option \"-m --mode\" is not valid. Allowed values: exportdata, or describetable\n";
     226   }
     227   if ($Options{outdelim} !~ /^(comma|semicolon|tab)$/i) {
     228     die "Error: The value specified, $Options{outdelim}, for option \"--outdelim\" is not valid. Allowed values: comma, tab, or semicolon\n";
     229   }
     230   if ($Options{quote} !~ /^(yes|no)$/i) {
     231     die "Error: The value specified, $Options{quote}, for option \"-q --quote\" is not valid. Allowed values: yes or no\n";
     232   }
     233 }
     234