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