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