Mercurial > repos > deepakjadmin > mayatool3_test2

diff docs/scripts/html/code/DBSchemaTablesToTextFiles.html @ 0:4816e4a8ae95 draft default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Uploaded
author deepakjadmin
date Wed, 20 Jan 2016 09:23:18 -0500
parents
children
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/docs/scripts/html/code/DBSchemaTablesToTextFiles.html	Wed Jan 20 09:23:18 2016 -0500
@@ -0,0 +1,312 @@
+<html>
+<head>
+<title>MayaChemTools:Code:DBSchemaTablesToTextFiles.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: DBSchemaTablesToTextFiles.pl,v $</span>
+   4 <span class="c"># $Date: 2015/02/28 20:46:19 $</span>
+   5 <span class="c"># $Revision: 1.31 $</span>
+   6 <span class="c">#</span>
+   7 <span class="c"># Author: Manish Sud &lt;msud@san.rr.com&gt;</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 &lt;http://www.gnu.org/licenses/&gt; 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">&quot;$FindBin::Bin/../lib&quot;</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">&quot;\n$ScriptName: Starting...\n\n&quot;</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> &lt; <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">&quot;$FindBin::Bin/$ScriptName&quot;</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"># Connect to database...</span>
+  59 <span class="k">my</span><span class="s">(</span><span class="i">$DBHandle</span><span class="s">)</span><span class="sc">;</span>
+  60 <span class="k">print</span> <span class="q">&quot;Connecting to $DBDriver:database=$DBName as $DBUser...\n&quot;</span><span class="sc">;</span>
+  61 <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>
+  62 
+  63 <span class="c"># Collect input parameters information...</span>
+  64 <span class="k">print</span> <span class="q">&quot;Checking input parameter(s)...\n&quot;</span><span class="sc">;</span>
+  65 <span class="k">my</span><span class="s">(</span><span class="i">@DBSchemaNames</span><span class="cm">,</span> <span class="i">@DBTableNames</span><span class="cm">,</span> <span class="i">@DBSQLStatements</span><span class="cm">,</span> <span class="i">@DBTextFiles</span><span class="cm">,</span> <span class="i">$SingleTextFileName</span><span class="s">)</span><span class="sc">;</span>
+  66 <span class="i">RetrieveDBInfo</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
+  67 
+  68 <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">numoutfiles</span>} =~ <span class="q">/^single$/i</span> <span class="s">)</span> <span class="s">{</span>
+  69   <span class="i">GenerateSingleTextFile</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
+  70 <span class="s">}</span>
+  71 <span class="k">else</span> <span class="s">{</span>
+  72   <span class="i">GenerateMultipleTextFiles</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
+  73 <span class="s">}</span>
+  74 <span class="k">print</span> <span class="q">&quot;\nDisconnecting from  $DBDriver:database=$DBName...\n&quot;</span><span class="sc">;</span>
+  75 <span class="i">DBDisconnect</span><span class="s">(</span><span class="i">$DBHandle</span><span class="s">)</span><span class="sc">;</span>
+  76 
+  77 <span class="k">print</span> <span class="q">&quot;$ScriptName:Done...\n\n&quot;</span><span class="sc">;</span>
+  78 
+  79 <span class="i">$EndTime</span> = <span class="w">new</span> <span class="w">Benchmark</span><span class="sc">;</span>
+  80 <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>
+  81 <span class="k">print</span> <span class="q">&quot;Total time: &quot;</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">&quot;\n&quot;</span><span class="sc">;</span>
+  82 
+  83 <span class="c">###############################################################################</span>
+  84 
+  85 <span class="c"># Generate multiple text files...</span>
+<a name="GenerateMultipleTextFiles-"></a>  86 <span class="k">sub </span><span class="m">GenerateMultipleTextFiles</span> <span class="s">{</span>
+  87   <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>
+  88   <span class="k">if</span> <span class="s">(</span><span class="i">@DBTextFiles</span> &gt; <span class="n">1</span><span class="s">)</span> <span class="s">{</span>
+  89     <span class="k">print</span> <span class="q">&quot;Generating text files...\n&quot;</span><span class="sc">;</span>
+  90   <span class="s">}</span>
+  91  <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>
+  92     <span class="i">$TextFile</span> = <span class="i">$DBTextFiles</span>[<span class="i">$Index</span>]<span class="sc">;</span>
+  93     <span class="i">$SQL</span> = <span class="i">$DBSQLStatements</span>[<span class="i">$Index</span>]<span class="sc">;</span>
+  94 
+  95     <span class="k">if</span> <span class="s">(</span><span class="i">@DBTextFiles</span> &gt; <span class="n">1</span><span class="s">)</span> <span class="s">{</span>
+  96       <span class="k">print</span> <span class="q">&quot;\nGenerating text file $TextFile...\n&quot;</span><span class="sc">;</span>
+  97     <span class="s">}</span>
+  98     <span class="k">else</span> <span class="s">{</span>
+  99       <span class="k">print</span> <span class="q">&quot;Generating text file $TextFile...\n&quot;</span><span class="sc">;</span>
+ 100     <span class="s">}</span>
+ 101 
+ 102     <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">&quot;&gt;$TextFile&quot;</span><span class="s">)</span> <span class="s">{</span>
+ 103       <span class="k">warn</span> <span class="q">&quot;Warning: Abandoning $TextFile generation: Couldn&#39;t open it: $! \n&quot;</span><span class="sc">;</span>
+ 104       <span class="k">next</span> <span class="j">TEXTFILE</span><span class="sc">;</span>
+ 105     <span class="s">}</span>
+ 106     <span class="k">print</span> <span class="q">&quot;Processing SQL statement \&quot;$SQL\&quot;...\n&quot;</span><span class="sc">;</span>
+ 107 
+ 108     <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>
+ 109       <span class="k">warn</span> <span class="q">&quot;Warning: Abandoning $TextFile generation...\n&quot;</span><span class="sc">;</span>
+ 110       <span class="k">next</span> <span class="j">TEXTFILE</span><span class="sc">;</span>
+ 111     <span class="s">}</span>
+ 112     <span class="k">close</span> <span class="w">TEXTFILE</span><span class="sc">;</span>
+ 113   <span class="s">}</span>
+ 114 <span class="s">}</span>
+ 115 
+ 116 <span class="c"># Generate single text file...</span>
+<a name="GenerateSingleTextFile-"></a> 117 <span class="k">sub </span><span class="m">GenerateSingleTextFile</span> <span class="s">{</span>
+ 118   <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="cm">,</span> <span class="i">$SchemaName</span><span class="cm">,</span> <span class="i">$TableName</span><span class="s">)</span><span class="sc">;</span>
+ 119 
+ 120   <span class="k">open</span> <span class="w">TEXTFILE</span><span class="cm">,</span> <span class="q">&quot;&gt;$SingleTextFileName&quot;</span> <span class="k">or</span> <span class="k">die</span> <span class="q">&quot;Couldn&#39;t open $SingleTextFileName: $! \n&quot;</span><span class="sc">;</span>
+ 121   <span class="k">print</span> <span class="q">&quot;Generating text file $SingleTextFileName...\n&quot;</span><span class="sc">;</span>
+ 122 
+ 123   <span class="j">SQL:</span> <span class="k">for</span> <span class="i">$Index</span> <span class="s">(</span><span class="n">0</span> .. <span class="i">$#DBSQLStatements</span><span class="s">)</span> <span class="s">{</span>
+ 124     <span class="i">$SchemaName</span> = <span class="i">$DBSchemaNames</span>[<span class="i">$Index</span>]<span class="sc">;</span>
+ 125     <span class="i">$TableName</span> = <span class="i">$DBTableNames</span>[<span class="i">$Index</span>]<span class="sc">;</span>
+ 126     <span class="i">$SQL</span> = <span class="i">$DBSQLStatements</span>[<span class="i">$Index</span>]<span class="sc">;</span>
+ 127 
+ 128     <span class="i">$TableName</span> = <span class="q">qq($SchemaName.$TableName)</span><span class="sc">;</span>
+ 129     <span class="i">$TableName</span> = <span class="i">QuoteAWord</span><span class="s">(</span><span class="i">$TableName</span><span class="cm">,</span> <span class="i">$OutQuote</span><span class="s">)</span><span class="sc">;</span>
+ 130     <span class="k">print</span> <span class="i">TEXTFILE</span> <span class="q">&quot;\n\n$TableName\n&quot;</span><span class="sc">;</span>
+ 131 
+ 132     <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>
+ 133       <span class="k">warn</span> <span class="q">&quot;Warning: Abandoning table $TableName ...\n&quot;</span><span class="sc">;</span>
+ 134       <span class="k">next</span> <span class="j">SQL</span><span class="sc">;</span>
+ 135     <span class="s">}</span>
+ 136   <span class="s">}</span>
+ 137   <span class="k">close</span> <span class="w">TEXTFILE</span><span class="sc">;</span>
+ 138 <span class="s">}</span>
+ 139 
+ 140 <span class="c"># Collect input parameters information...</span>
+<a name="RetrieveDBInfo-"></a> 141 <span class="k">sub </span><span class="m">RetrieveDBInfo</span> <span class="s">{</span>
+ 142   <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="cm">,</span> <span class="i">$FileDBPrefix</span><span class="s">)</span><span class="sc">;</span>
+ 143 
+ 144   <span class="c"># Setup out file ext...</span>
+ 145   <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">&quot;tsv&quot;</span> <span class="co">:</span> <span class="q">&quot;csv&quot;</span><span class="sc">;</span>
+ 146   <span class="i">$FileDBPrefix</span> = <span class="s">(</span><span class="i">$DBMode</span> =~ <span class="q">/^exportdata$/i</span><span class="s">)</span> ? <span class="q">&quot;Export&quot;</span> <span class="co">:</span> <span class="q">&quot;Describe&quot;</span><span class="sc">;</span>
+ 147 
+ 148   <span class="c"># Get user specified information...</span>
+ 149   <span class="i">$UserFileName</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span>
+ 150   <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">root</span>} &amp;&amp; <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>
+ 151     <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>
+ 152     <span class="k">if</span> <span class="s">(</span><span class="i">$RootFileName</span> &amp;&amp; <span class="i">$RootFileExt</span><span class="s">)</span> <span class="s">{</span>
+ 153       <span class="i">$UserFileName</span> = <span class="i">$RootFileName</span><span class="sc">;</span>
+ 154     <span class="s">}</span>
+ 155     <span class="k">else</span> <span class="s">{</span>
+ 156       <span class="i">$UserFileName</span> = <span class="i">$Options</span>{<span class="w">root</span>}<span class="sc">;</span>
+ 157     <span class="s">}</span>
+ 158   <span class="s">}</span>
+ 159   <span class="i">$SingleTextFileName</span> = <span class="q">&quot;&quot;</span><span class="sc">;</span>
+ 160   <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">numoutfiles</span>} =~ <span class="q">/^single$/i</span><span class="s">)</span> <span class="s">{</span>
+ 161     <span class="i">$SingleTextFileName</span> = <span class="i">$UserFileName</span> ? <span class="i">$UserFileName</span> <span class="co">:</span> <span class="s">(</span><span class="q">&quot;$FileDBPrefix&quot;</span> . <span class="q">&quot;SchemaTables&quot;</span><span class="s">)</span><span class="sc">;</span>
+ 162     <span class="i">$SingleTextFileName</span> .= <span class="q">&quot;.$FileExt&quot;</span><span class="sc">;</span>
+ 163   <span class="s">}</span>
+ 164 
+ 165   <span class="c"># For each input schema name: collect all the table names, set up appropriate</span>
+ 166   <span class="c"># SQL statements, and output file names...</span>
+ 167   <span class="c">#</span>
+ 168   <span class="k">my</span><span class="s">(</span><span class="i">$SchemaName</span><span class="cm">,</span> <span class="i">$SQL</span><span class="cm">,</span> <span class="i">$FileName</span><span class="cm">,</span> <span class="i">@SchemaTableNames</span><span class="cm">,</span> <span class="i">$TableName</span><span class="s">)</span><span class="sc">;</span>
+ 169   <span class="i">@DBSchemaNames</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
+ 170   <span class="i">@DBTableNames</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
+ 171   <span class="i">@DBSQLStatements</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
+ 172   <span class="i">@DBTextFiles</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
+ 173   <span class="i">@SchemaTableNames</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
+ 174   <span class="j">SCHEMANAME:</span> <span class="k">for</span> <span class="i">$SchemaName</span> <span class="s">(</span><span class="i">@ARGV</span><span class="s">)</span> <span class="s">{</span>
+ 175     <span class="i">$SchemaName</span> = <span class="s">(</span><span class="i">$DBDriver</span> =~ <span class="q">/^(mysql|Oracle)$/i</span><span class="s">)</span> ? <span class="k">uc</span><span class="s">(</span><span class="i">$SchemaName</span><span class="s">)</span> <span class="co">:</span> <span class="i">$SchemaName</span><span class="sc">;</span>
+ 176     <span class="k">if</span> <span class="s">(</span>!<span class="s">(</span><span class="i">@SchemaTableNames</span> = <span class="i">DBFetchSchemaTableNames</span><span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$DBHandle</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="s">)</span><span class="s">)</span><span class="s">)</span> <span class="s">{</span>
+ 177       <span class="k">warn</span> <span class="q">&quot;Warning: Ignoring schema $SchemaName...\n&quot;</span><span class="sc">;</span>
+ 178       <span class="k">next</span> <span class="j">SCHEMANAME</span><span class="sc">;</span>
+ 179     <span class="s">}</span>
+ 180     <span class="c"># Prepare SQL statement for each table.</span>
+ 181     <span class="k">for</span> <span class="i">$TableName</span> <span class="s">(</span><span class="i">@SchemaTableNames</span><span class="s">)</span> <span class="s">{</span>
+ 182       <span class="k">push</span> <span class="i">@DBSchemaNames</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="sc">;</span>
+ 183       <span class="k">push</span> <span class="i">@DBTableNames</span><span class="cm">,</span> <span class="i">$TableName</span><span class="sc">;</span>
+ 184       <span class="i">$SQL</span> = <span class="s">(</span><span class="i">$DBMode</span> =~ <span class="q">/^exportdata$/i</span><span class="s">)</span> ? <span class="i">DBSetupSelectSQL</span><span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$TableName</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="s">)</span> <span class="co">:</span> <span class="i">DBSetupDescribeSQL</span><span class="s">(</span><span class="i">$DBDriver</span><span class="cm">,</span> <span class="i">$TableName</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="s">)</span><span class="sc">;</span>
+ 185       <span class="k">push</span> <span class="i">@DBSQLStatements</span><span class="cm">,</span> <span class="i">$SQL</span><span class="sc">;</span>
+ 186       <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">numoutfiles</span>} =~ <span class="q">/^multiple$/i</span><span class="s">)</span> <span class="s">{</span>
+ 187         <span class="i">$FileName</span> = <span class="i">$UserFileName</span> ? <span class="s">(</span><span class="q">&quot;$UserFileName&quot;</span> . <span class="q">&quot;$TableName&quot;</span><span class="s">)</span> <span class="co">:</span> <span class="s">(</span><span class="q">&quot;$FileDBPrefix&quot;</span> . <span class="q">&quot;$SchemaName&quot;</span> . <span class="q">&quot;$TableName&quot;</span><span class="s">)</span><span class="sc">;</span>
+ 188         <span class="i">$FileName</span> .= <span class="q">&quot;.$FileExt&quot;</span><span class="sc">;</span>
+ 189         <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>
+ 190           <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>
+ 191             <span class="k">die</span> <span class="q">&quot;Error: The file $FileName already exists.\n&quot;</span><span class="sc">;</span>
+ 192           <span class="s">}</span>
+ 193         <span class="s">}</span>
+ 194         <span class="k">push</span> <span class="i">@DBTextFiles</span><span class="cm">,</span> <span class="i">$FileName</span><span class="sc">;</span>
+ 195       <span class="s">}</span>
+ 196     <span class="s">}</span>
+ 197   <span class="s">}</span>
+ 198 <span class="s">}</span>
+ 199 
+ 200 <span class="c"># Process option values...</span>
+<a name="ProcessOptions-"></a> 201 <span class="k">sub </span><span class="m">ProcessOptions</span> <span class="s">{</span>
+ 202 
+ 203   <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">&quot;&quot;</span><span class="s">)</span> <span class="sc">;</span>
+ 204   <span class="k">if</span> <span class="s">(</span><span class="i">$DBDriver</span><span class="s">)</span> <span class="s">{</span>
+ 205     <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>
+ 206       <span class="i">$DBDriver</span> = <span class="q">&quot;Oracle&quot;</span><span class="sc">;</span>
+ 207     <span class="s">}</span>
+ 208     <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>
+ 209       <span class="i">$DBDriver</span> = <span class="q">&quot;mysql&quot;</span><span class="sc">;</span>
+ 210     <span class="s">}</span>
+ 211     <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>
+ 212       <span class="i">$DBDriver</span> = <span class="q">&quot;Pg&quot;</span><span class="sc">;</span>
+ 213     <span class="s">}</span>
+ 214     <span class="k">else</span> <span class="s">{</span>
+ 215       <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>
+ 216         <span class="k">die</span> <span class="q">&quot;Error: The value specified, $DBDriver, for option \&quot;-d --dbdriver\&quot; is not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n&quot;</span><span class="sc">;</span>
+ 217       <span class="s">}</span>
+ 218       <span class="k">else</span> <span class="s">{</span>
+ 219         <span class="k">die</span> <span class="q">&quot;Error: The value specified, $DBDriver, using environment variable DBI_DRIVER not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n&quot;</span><span class="sc">;</span>
+ 220       <span class="s">}</span>
+ 221     <span class="s">}</span>
+ 222   <span class="s">}</span>
+ 223   <span class="k">else</span> <span class="s">{</span>
+ 224     <span class="i">$DBDriver</span> = <span class="q">&quot;mysql&quot;</span><span class="sc">;</span>
+ 225   <span class="s">}</span>
+ 226   <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">&quot;127.0.0.1&quot;</span><span class="s">)</span><span class="sc">;</span>
+ 227   <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">&quot;&quot;</span><span class="s">)</span><span class="sc">;</span>
+ 228   <span class="k">if</span> <span class="s">(</span>!<span class="i">$DBName</span><span class="s">)</span> <span class="s">{</span>
+ 229     <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>
+ 230       <span class="i">$DBName</span> = <span class="q">&quot;mysql&quot;</span><span class="sc">;</span>
+ 231     <span class="s">}</span>
+ 232   <span class="s">}</span>
+ 233   <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">&quot;&quot;</span><span class="s">)</span> <span class="sc">;</span>
+ 234   <span class="k">if</span> <span class="s">(</span>!<span class="i">$DBUser</span><span class="s">)</span> <span class="s">{</span>
+ 235     <span class="k">die</span> <span class="q">&quot;Error: No database username specified. Use \&quot;--dbusername\&quot; option or environment variable DBI_USER to enter a valid value.\n&quot;</span><span class="sc">;</span>
+ 236   <span class="s">}</span>
+ 237   <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">&quot;&quot;</span><span class="s">)</span> <span class="sc">;</span>
+ 238   <span class="k">if</span> <span class="s">(</span>!<span class="i">$DBPassword</span><span class="s">)</span> <span class="s">{</span>
+ 239     <span class="k">die</span> <span class="q">&quot;Error: No database password specified. Use \&quot;--dbpassword\&quot; option or environment variable DBI_PASS to enter a valid value.\n&quot;</span><span class="sc">;</span>
+ 240   <span class="s">}</span>
+ 241   <span class="i">$DBMode</span> = <span class="i">$Options</span>{<span class="w">mode</span>}<span class="sc">;</span>
+ 242   <span class="i">$ExportLOBs</span> = <span class="s">(</span><span class="i">$Options</span>{<span class="w">exportlobs</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>
+ 243   <span class="i">$ExportDataLabels</span> = <span class="s">(</span><span class="i">$DBMode</span> =~ <span class="q">/^describetable$/i</span><span class="s">)</span> ? <span class="n">1</span> <span class="co">:</span> <span class="s">(</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="s">)</span><span class="sc">;</span>
+ 244 
+ 245   <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">&quot;\t&quot;</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">&quot;\;&quot;</span> <span class="co">:</span> <span class="q">&quot;\,&quot;</span><span class="s">)</span><span class="sc">;</span>
+ 246   <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>
+ 247 
+ 248   <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> &amp;&amp; <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">&quot;&quot;</span><span class="sc">;</span>
+ 249 <span class="s">}</span>
+ 250 
+ 251 <span class="c"># Setup script usage  and retrieve command line arguments specified using various options...</span>
+<a name="SetupScriptUsage-"></a> 252 <span class="k">sub </span><span class="m">SetupScriptUsage</span> <span class="s">{</span>
+ 253 
+ 254   <span class="c"># Retrieve all the options...</span>
+ 255   <span class="i">%Options</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
+ 256   <span class="i">$Options</span>{<span class="w">mode</span>} = <span class="q">&quot;exportdata&quot;</span><span class="sc">;</span>
+ 257   <span class="i">$Options</span>{<span class="w">exportlobs</span>} = <span class="q">&quot;no&quot;</span><span class="sc">;</span>
+ 258   <span class="i">$Options</span>{<span class="w">exportdatalabels</span>} = <span class="q">&quot;yes&quot;</span><span class="sc">;</span>
+ 259   <span class="i">$Options</span>{<span class="w">numoutfiles</span>} = <span class="q">&quot;single&quot;</span><span class="sc">;</span>
+ 260   <span class="i">$Options</span>{<span class="w">outdelim</span>} = <span class="q">&quot;comma&quot;</span><span class="sc">;</span>
+ 261   <span class="i">$Options</span>{<span class="w">quote</span>} = <span class="q">&quot;yes&quot;</span><span class="sc">;</span>
+ 262 
+ 263   <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">&quot;dbdriver|d=s&quot;</span><span class="cm">,</span> <span class="q">&quot;dbhost=s&quot;</span><span class="cm">,</span> <span class="q">&quot;dbname=s&quot;</span><span class="cm">,</span> <span class="q">&quot;dbpassword=s&quot;</span><span class="cm">,</span> <span class="q">&quot;dbusername=s&quot;</span><span class="cm">,</span> <span class="q">&quot;exportdatalabels=s&quot;</span><span class="cm">,</span> <span class="q">&quot;exportlobs=s&quot;</span><span class="cm">,</span> <span class="q">&quot;help|h&quot;</span><span class="cm">,</span> <span class="q">&quot;mode|m=s&quot;</span><span class="cm">,</span> <span class="q">&quot;numoutfiles|n=s&quot;</span><span class="cm">,</span> <span class="q">&quot;outdelim=s&quot;</span><span class="cm">,</span> <span class="q">&quot;overwrite|o&quot;</span><span class="cm">,</span> <span class="q">&quot;quote|q=s&quot;</span><span class="cm">,</span> <span class="q">&quot;root|r=s&quot;</span><span class="cm">,</span> <span class="q">&quot;replacenullstr=s&quot;</span><span class="cm">,</span> <span class="q">&quot;workingdir|w=s&quot;</span><span class="s">)</span><span class="s">)</span> <span class="s">{</span>
+ 264     <span class="k">die</span> <span class="q">&quot;\nTo get a list of valid options and their values, use \&quot;$ScriptName -h\&quot; or\n\&quot;perl -S $ScriptName -h\&quot; command and try again...\n&quot;</span><span class="sc">;</span>
+ 265   <span class="s">}</span>
+ 266   <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>
+ 267     <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>
+ 268       <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{workingdir}, for option \&quot;-w --workingdir\&quot; is not a directory name.\n&quot;</span><span class="sc">;</span>
+ 269     <span class="s">}</span>
+ 270     <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">&quot;Error: Couldn&#39;t chdir $Options{workingdir}: $! \n&quot;</span><span class="sc">;</span>
+ 271   <span class="s">}</span>
+ 272   <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>
+ 273     <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{exportlobs}, for option \&quot;--exportdatalabels\&quot; is not valid. Allowed values: yes or no\n&quot;</span><span class="sc">;</span>
+ 274   <span class="s">}</span>
+ 275   <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>
+ 276     <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{exportlobs}, for option \&quot;--exportlobs\&quot; is not valid. Allowed values: yes or no\n&quot;</span><span class="sc">;</span>
+ 277   <span class="s">}</span>
+ 278   <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">numoutfiles</span>} !~ <span class="q">/^(single|multiple)$/i</span><span class="s">)</span> <span class="s">{</span>
+ 279     <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{mode}, for option \&quot;-n --numoutfiles\&quot; is not valid. Allowed values: single or multiple\n&quot;</span><span class="sc">;</span>
+ 280   <span class="s">}</span>
+ 281   <span class="k">if</span> <span class="s">(</span><span class="i">$Options</span>{<span class="w">mode</span>} !~ <span class="q">/^(exportdata|describetable)$/i</span><span class="s">)</span> <span class="s">{</span>
+ 282     <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{mode}, for option \&quot;-m --mode\&quot; is not valid. Allowed values: exportdata or describetable\n&quot;</span><span class="sc">;</span>
+ 283   <span class="s">}</span>
+ 284   <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>
+ 285     <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{outdelim}, for option \&quot;--outdelim\&quot; is not valid. Allowed values: comma, tab, or semicolon\n&quot;</span><span class="sc">;</span>
+ 286   <span class="s">}</span>
+ 287   <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>
+ 288     <span class="k">die</span> <span class="q">&quot;Error: The value specified, $Options{quote}, for option \&quot;-q --quote\&quot; is not valid. Allowed values: yes or no\n&quot;</span><span class="sc">;</span>
+ 289   <span class="s">}</span>
+ 290 <span class="s">}</span>
+ 291 
+<a name="EOF-"></a></pre>
+<p>&nbsp;</p>
+<br />
+<center>
+<img src="../../../images/h2o2.png">
+</center>
+</body>
+</html>