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