|
0
|
1 <html>
|
|
|
2 <head>
|
|
|
3 <title>MayaChemTools:Code:DBSchemaTablesToTextFiles.pl</title>
|
|
|
4 <meta http-equiv="content-type" content="text/html;charset=utf-8">
|
|
|
5 <link rel="stylesheet" type="text/css" href="../../../css/MayaChemToolsCode.css">
|
|
|
6 </head>
|
|
|
7 <body leftmargin="20" rightmargin="20" topmargin="10" bottommargin="10">
|
|
|
8 <br/>
|
|
|
9 <center>
|
|
|
10 <a href="http://www.mayachemtools.org" title="MayaChemTools Home"><img src="../../../images/MayaChemToolsLogo.gif" border="0" alt="MayaChemTools"></a>
|
|
|
11 </center>
|
|
|
12 <br/>
|
|
|
13 <pre>
|
|
|
14 1 #!/usr/bin/perl -w
|
|
|
15 2 <span class="c">#</span>
|
|
|
16 3 <span class="c"># $RCSfile: DBSchemaTablesToTextFiles.pl,v $</span>
|
|
|
17 4 <span class="c"># $Date: 2015/02/28 20:46:19 $</span>
|
|
|
18 5 <span class="c"># $Revision: 1.31 $</span>
|
|
|
19 6 <span class="c">#</span>
|
|
|
20 7 <span class="c"># Author: Manish Sud <msud@san.rr.com></span>
|
|
|
21 8 <span class="c">#</span>
|
|
|
22 9 <span class="c"># Copyright (C) 2015 Manish Sud. All rights reserved.</span>
|
|
|
23 10 <span class="c">#</span>
|
|
|
24 11 <span class="c"># This file is part of MayaChemTools.</span>
|
|
|
25 12 <span class="c">#</span>
|
|
|
26 13 <span class="c"># MayaChemTools is free software; you can redistribute it and/or modify it under</span>
|
|
|
27 14 <span class="c"># the terms of the GNU Lesser General Public License as published by the Free</span>
|
|
|
28 15 <span class="c"># Software Foundation; either version 3 of the License, or (at your option) any</span>
|
|
|
29 16 <span class="c"># later version.</span>
|
|
|
30 17 <span class="c">#</span>
|
|
|
31 18 <span class="c"># MayaChemTools is distributed in the hope that it will be useful, but without</span>
|
|
|
32 19 <span class="c"># any warranty; without even the implied warranty of merchantability of fitness</span>
|
|
|
33 20 <span class="c"># for a particular purpose. See the GNU Lesser General Public License for more</span>
|
|
|
34 21 <span class="c"># details.</span>
|
|
|
35 22 <span class="c">#</span>
|
|
|
36 23 <span class="c"># You should have received a copy of the GNU Lesser General Public License</span>
|
|
|
37 24 <span class="c"># along with MayaChemTools; if not, see <http://www.gnu.org/licenses/> or</span>
|
|
|
38 25 <span class="c"># write to the Free Software Foundation Inc., 59 Temple Place, Suite 330,</span>
|
|
|
39 26 <span class="c"># Boston, MA, 02111-1307, USA.</span>
|
|
|
40 27 <span class="c">#</span>
|
|
|
41 28
|
|
|
42 29 <span class="k">use</span> <span class="w">strict</span><span class="sc">;</span>
|
|
|
43 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>
|
|
|
44 31 <span class="k">use</span> <span class="w">Getopt::Long</span><span class="sc">;</span>
|
|
|
45 32 <span class="k">use</span> <span class="w">File::Basename</span><span class="sc">;</span>
|
|
|
46 33 <span class="k">use</span> <span class="w">Text::ParseWords</span><span class="sc">;</span>
|
|
|
47 34 <span class="k">use</span> <span class="w">Benchmark</span><span class="sc">;</span>
|
|
|
48 35 <span class="k">use</span> <span class="w">FileUtil</span><span class="sc">;</span>
|
|
|
49 36 <span class="k">use</span> <span class="w">TextUtil</span><span class="sc">;</span>
|
|
|
50 37 <span class="k">use</span> <span class="w">DBUtil</span><span class="sc">;</span>
|
|
|
51 38
|
|
|
52 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>
|
|
|
53 40
|
|
|
54 41 <span class="c"># Autoflush STDOUT</span>
|
|
|
55 42 <span class="i">$|</span> = <span class="n">1</span><span class="sc">;</span>
|
|
|
56 43
|
|
|
57 44 <span class="c"># Starting message...</span>
|
|
|
58 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>
|
|
|
59 46 <span class="k">print</span> <span class="q">"\n$ScriptName: Starting...\n\n"</span><span class="sc">;</span>
|
|
|
60 47 <span class="i">$StartTime</span> = <span class="w">new</span> <span class="w">Benchmark</span><span class="sc">;</span>
|
|
|
61 48
|
|
|
62 49 <span class="c"># Get the options and setup script...</span>
|
|
|
63 50 <span class="i">SetupScriptUsage</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
64 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>
|
|
|
65 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>
|
|
|
66 53 <span class="s">}</span>
|
|
|
67 54
|
|
|
68 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>
|
|
|
69 56 <span class="i">ProcessOptions</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
70 57
|
|
|
71 58 <span class="c"># Connect to database...</span>
|
|
|
72 59 <span class="k">my</span><span class="s">(</span><span class="i">$DBHandle</span><span class="s">)</span><span class="sc">;</span>
|
|
|
73 60 <span class="k">print</span> <span class="q">"Connecting to $DBDriver:database=$DBName as $DBUser...\n"</span><span class="sc">;</span>
|
|
|
74 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>
|
|
|
75 62
|
|
|
76 63 <span class="c"># Collect input parameters information...</span>
|
|
|
77 64 <span class="k">print</span> <span class="q">"Checking input parameter(s)...\n"</span><span class="sc">;</span>
|
|
|
78 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>
|
|
|
79 66 <span class="i">RetrieveDBInfo</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
80 67
|
|
|
81 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>
|
|
|
82 69 <span class="i">GenerateSingleTextFile</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
83 70 <span class="s">}</span>
|
|
|
84 71 <span class="k">else</span> <span class="s">{</span>
|
|
|
85 72 <span class="i">GenerateMultipleTextFiles</span><span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
86 73 <span class="s">}</span>
|
|
|
87 74 <span class="k">print</span> <span class="q">"\nDisconnecting from $DBDriver:database=$DBName...\n"</span><span class="sc">;</span>
|
|
|
88 75 <span class="i">DBDisconnect</span><span class="s">(</span><span class="i">$DBHandle</span><span class="s">)</span><span class="sc">;</span>
|
|
|
89 76
|
|
|
90 77 <span class="k">print</span> <span class="q">"$ScriptName:Done...\n\n"</span><span class="sc">;</span>
|
|
|
91 78
|
|
|
92 79 <span class="i">$EndTime</span> = <span class="w">new</span> <span class="w">Benchmark</span><span class="sc">;</span>
|
|
|
93 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>
|
|
|
94 81 <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>
|
|
|
95 82
|
|
|
96 83 <span class="c">###############################################################################</span>
|
|
|
97 84
|
|
|
98 85 <span class="c"># Generate multiple text files...</span>
|
|
|
99 <a name="GenerateMultipleTextFiles-"></a> 86 <span class="k">sub </span><span class="m">GenerateMultipleTextFiles</span> <span class="s">{</span>
|
|
|
100 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>
|
|
|
101 88 <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>
|
|
|
102 89 <span class="k">print</span> <span class="q">"Generating text files...\n"</span><span class="sc">;</span>
|
|
|
103 90 <span class="s">}</span>
|
|
|
104 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>
|
|
|
105 92 <span class="i">$TextFile</span> = <span class="i">$DBTextFiles</span>[<span class="i">$Index</span>]<span class="sc">;</span>
|
|
|
106 93 <span class="i">$SQL</span> = <span class="i">$DBSQLStatements</span>[<span class="i">$Index</span>]<span class="sc">;</span>
|
|
|
107 94
|
|
|
108 95 <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>
|
|
|
109 96 <span class="k">print</span> <span class="q">"\nGenerating text file $TextFile...\n"</span><span class="sc">;</span>
|
|
|
110 97 <span class="s">}</span>
|
|
|
111 98 <span class="k">else</span> <span class="s">{</span>
|
|
|
112 99 <span class="k">print</span> <span class="q">"Generating text file $TextFile...\n"</span><span class="sc">;</span>
|
|
|
113 100 <span class="s">}</span>
|
|
|
114 101
|
|
|
115 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">">$TextFile"</span><span class="s">)</span> <span class="s">{</span>
|
|
|
116 103 <span class="k">warn</span> <span class="q">"Warning: Abandoning $TextFile generation: Couldn't open it: $! \n"</span><span class="sc">;</span>
|
|
|
117 104 <span class="k">next</span> <span class="j">TEXTFILE</span><span class="sc">;</span>
|
|
|
118 105 <span class="s">}</span>
|
|
|
119 106 <span class="k">print</span> <span class="q">"Processing SQL statement \"$SQL\"...\n"</span><span class="sc">;</span>
|
|
|
120 107
|
|
|
121 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>
|
|
|
122 109 <span class="k">warn</span> <span class="q">"Warning: Abandoning $TextFile generation...\n"</span><span class="sc">;</span>
|
|
|
123 110 <span class="k">next</span> <span class="j">TEXTFILE</span><span class="sc">;</span>
|
|
|
124 111 <span class="s">}</span>
|
|
|
125 112 <span class="k">close</span> <span class="w">TEXTFILE</span><span class="sc">;</span>
|
|
|
126 113 <span class="s">}</span>
|
|
|
127 114 <span class="s">}</span>
|
|
|
128 115
|
|
|
129 116 <span class="c"># Generate single text file...</span>
|
|
|
130 <a name="GenerateSingleTextFile-"></a> 117 <span class="k">sub </span><span class="m">GenerateSingleTextFile</span> <span class="s">{</span>
|
|
|
131 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>
|
|
|
132 119
|
|
|
133 120 <span class="k">open</span> <span class="w">TEXTFILE</span><span class="cm">,</span> <span class="q">">$SingleTextFileName"</span> <span class="k">or</span> <span class="k">die</span> <span class="q">"Couldn't open $SingleTextFileName: $! \n"</span><span class="sc">;</span>
|
|
|
134 121 <span class="k">print</span> <span class="q">"Generating text file $SingleTextFileName...\n"</span><span class="sc">;</span>
|
|
|
135 122
|
|
|
136 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>
|
|
|
137 124 <span class="i">$SchemaName</span> = <span class="i">$DBSchemaNames</span>[<span class="i">$Index</span>]<span class="sc">;</span>
|
|
|
138 125 <span class="i">$TableName</span> = <span class="i">$DBTableNames</span>[<span class="i">$Index</span>]<span class="sc">;</span>
|
|
|
139 126 <span class="i">$SQL</span> = <span class="i">$DBSQLStatements</span>[<span class="i">$Index</span>]<span class="sc">;</span>
|
|
|
140 127
|
|
|
141 128 <span class="i">$TableName</span> = <span class="q">qq($SchemaName.$TableName)</span><span class="sc">;</span>
|
|
|
142 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>
|
|
|
143 130 <span class="k">print</span> <span class="i">TEXTFILE</span> <span class="q">"\n\n$TableName\n"</span><span class="sc">;</span>
|
|
|
144 131
|
|
|
145 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>
|
|
|
146 133 <span class="k">warn</span> <span class="q">"Warning: Abandoning table $TableName ...\n"</span><span class="sc">;</span>
|
|
|
147 134 <span class="k">next</span> <span class="j">SQL</span><span class="sc">;</span>
|
|
|
148 135 <span class="s">}</span>
|
|
|
149 136 <span class="s">}</span>
|
|
|
150 137 <span class="k">close</span> <span class="w">TEXTFILE</span><span class="sc">;</span>
|
|
|
151 138 <span class="s">}</span>
|
|
|
152 139
|
|
|
153 140 <span class="c"># Collect input parameters information...</span>
|
|
|
154 <a name="RetrieveDBInfo-"></a> 141 <span class="k">sub </span><span class="m">RetrieveDBInfo</span> <span class="s">{</span>
|
|
|
155 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>
|
|
|
156 143
|
|
|
157 144 <span class="c"># Setup out file ext...</span>
|
|
|
158 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">"tsv"</span> <span class="co">:</span> <span class="q">"csv"</span><span class="sc">;</span>
|
|
|
159 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">"Export"</span> <span class="co">:</span> <span class="q">"Describe"</span><span class="sc">;</span>
|
|
|
160 147
|
|
|
161 148 <span class="c"># Get user specified information...</span>
|
|
|
162 149 <span class="i">$UserFileName</span> = <span class="q">""</span><span class="sc">;</span>
|
|
|
163 150 <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>
|
|
|
164 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>
|
|
|
165 152 <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>
|
|
|
166 153 <span class="i">$UserFileName</span> = <span class="i">$RootFileName</span><span class="sc">;</span>
|
|
|
167 154 <span class="s">}</span>
|
|
|
168 155 <span class="k">else</span> <span class="s">{</span>
|
|
|
169 156 <span class="i">$UserFileName</span> = <span class="i">$Options</span>{<span class="w">root</span>}<span class="sc">;</span>
|
|
|
170 157 <span class="s">}</span>
|
|
|
171 158 <span class="s">}</span>
|
|
|
172 159 <span class="i">$SingleTextFileName</span> = <span class="q">""</span><span class="sc">;</span>
|
|
|
173 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>
|
|
|
174 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">"$FileDBPrefix"</span> . <span class="q">"SchemaTables"</span><span class="s">)</span><span class="sc">;</span>
|
|
|
175 162 <span class="i">$SingleTextFileName</span> .= <span class="q">".$FileExt"</span><span class="sc">;</span>
|
|
|
176 163 <span class="s">}</span>
|
|
|
177 164
|
|
|
178 165 <span class="c"># For each input schema name: collect all the table names, set up appropriate</span>
|
|
|
179 166 <span class="c"># SQL statements, and output file names...</span>
|
|
|
180 167 <span class="c">#</span>
|
|
|
181 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>
|
|
|
182 169 <span class="i">@DBSchemaNames</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
183 170 <span class="i">@DBTableNames</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
184 171 <span class="i">@DBSQLStatements</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
185 172 <span class="i">@DBTextFiles</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
186 173 <span class="i">@SchemaTableNames</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
187 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>
|
|
|
188 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>
|
|
|
189 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>
|
|
|
190 177 <span class="k">warn</span> <span class="q">"Warning: Ignoring schema $SchemaName...\n"</span><span class="sc">;</span>
|
|
|
191 178 <span class="k">next</span> <span class="j">SCHEMANAME</span><span class="sc">;</span>
|
|
|
192 179 <span class="s">}</span>
|
|
|
193 180 <span class="c"># Prepare SQL statement for each table.</span>
|
|
|
194 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>
|
|
|
195 182 <span class="k">push</span> <span class="i">@DBSchemaNames</span><span class="cm">,</span> <span class="i">$SchemaName</span><span class="sc">;</span>
|
|
|
196 183 <span class="k">push</span> <span class="i">@DBTableNames</span><span class="cm">,</span> <span class="i">$TableName</span><span class="sc">;</span>
|
|
|
197 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>
|
|
|
198 185 <span class="k">push</span> <span class="i">@DBSQLStatements</span><span class="cm">,</span> <span class="i">$SQL</span><span class="sc">;</span>
|
|
|
199 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>
|
|
|
200 187 <span class="i">$FileName</span> = <span class="i">$UserFileName</span> ? <span class="s">(</span><span class="q">"$UserFileName"</span> . <span class="q">"$TableName"</span><span class="s">)</span> <span class="co">:</span> <span class="s">(</span><span class="q">"$FileDBPrefix"</span> . <span class="q">"$SchemaName"</span> . <span class="q">"$TableName"</span><span class="s">)</span><span class="sc">;</span>
|
|
|
201 188 <span class="i">$FileName</span> .= <span class="q">".$FileExt"</span><span class="sc">;</span>
|
|
|
202 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>
|
|
|
203 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>
|
|
|
204 191 <span class="k">die</span> <span class="q">"Error: The file $FileName already exists.\n"</span><span class="sc">;</span>
|
|
|
205 192 <span class="s">}</span>
|
|
|
206 193 <span class="s">}</span>
|
|
|
207 194 <span class="k">push</span> <span class="i">@DBTextFiles</span><span class="cm">,</span> <span class="i">$FileName</span><span class="sc">;</span>
|
|
|
208 195 <span class="s">}</span>
|
|
|
209 196 <span class="s">}</span>
|
|
|
210 197 <span class="s">}</span>
|
|
|
211 198 <span class="s">}</span>
|
|
|
212 199
|
|
|
213 200 <span class="c"># Process option values...</span>
|
|
|
214 <a name="ProcessOptions-"></a> 201 <span class="k">sub </span><span class="m">ProcessOptions</span> <span class="s">{</span>
|
|
|
215 202
|
|
|
216 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">""</span><span class="s">)</span> <span class="sc">;</span>
|
|
|
217 204 <span class="k">if</span> <span class="s">(</span><span class="i">$DBDriver</span><span class="s">)</span> <span class="s">{</span>
|
|
|
218 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>
|
|
|
219 206 <span class="i">$DBDriver</span> = <span class="q">"Oracle"</span><span class="sc">;</span>
|
|
|
220 207 <span class="s">}</span>
|
|
|
221 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>
|
|
|
222 209 <span class="i">$DBDriver</span> = <span class="q">"mysql"</span><span class="sc">;</span>
|
|
|
223 210 <span class="s">}</span>
|
|
|
224 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>
|
|
|
225 212 <span class="i">$DBDriver</span> = <span class="q">"Pg"</span><span class="sc">;</span>
|
|
|
226 213 <span class="s">}</span>
|
|
|
227 214 <span class="k">else</span> <span class="s">{</span>
|
|
|
228 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>
|
|
|
229 216 <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>
|
|
|
230 217 <span class="s">}</span>
|
|
|
231 218 <span class="k">else</span> <span class="s">{</span>
|
|
|
232 219 <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>
|
|
|
233 220 <span class="s">}</span>
|
|
|
234 221 <span class="s">}</span>
|
|
|
235 222 <span class="s">}</span>
|
|
|
236 223 <span class="k">else</span> <span class="s">{</span>
|
|
|
237 224 <span class="i">$DBDriver</span> = <span class="q">"mysql"</span><span class="sc">;</span>
|
|
|
238 225 <span class="s">}</span>
|
|
|
239 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">"127.0.0.1"</span><span class="s">)</span><span class="sc">;</span>
|
|
|
240 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">""</span><span class="s">)</span><span class="sc">;</span>
|
|
|
241 228 <span class="k">if</span> <span class="s">(</span>!<span class="i">$DBName</span><span class="s">)</span> <span class="s">{</span>
|
|
|
242 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>
|
|
|
243 230 <span class="i">$DBName</span> = <span class="q">"mysql"</span><span class="sc">;</span>
|
|
|
244 231 <span class="s">}</span>
|
|
|
245 232 <span class="s">}</span>
|
|
|
246 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">""</span><span class="s">)</span> <span class="sc">;</span>
|
|
|
247 234 <span class="k">if</span> <span class="s">(</span>!<span class="i">$DBUser</span><span class="s">)</span> <span class="s">{</span>
|
|
|
248 235 <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>
|
|
|
249 236 <span class="s">}</span>
|
|
|
250 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">""</span><span class="s">)</span> <span class="sc">;</span>
|
|
|
251 238 <span class="k">if</span> <span class="s">(</span>!<span class="i">$DBPassword</span><span class="s">)</span> <span class="s">{</span>
|
|
|
252 239 <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>
|
|
|
253 240 <span class="s">}</span>
|
|
|
254 241 <span class="i">$DBMode</span> = <span class="i">$Options</span>{<span class="w">mode</span>}<span class="sc">;</span>
|
|
|
255 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>
|
|
|
256 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>
|
|
|
257 244
|
|
|
258 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">"\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>
|
|
|
259 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>
|
|
|
260 247
|
|
|
261 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> && <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>
|
|
|
262 249 <span class="s">}</span>
|
|
|
263 250
|
|
|
264 251 <span class="c"># Setup script usage and retrieve command line arguments specified using various options...</span>
|
|
|
265 <a name="SetupScriptUsage-"></a> 252 <span class="k">sub </span><span class="m">SetupScriptUsage</span> <span class="s">{</span>
|
|
|
266 253
|
|
|
267 254 <span class="c"># Retrieve all the options...</span>
|
|
|
268 255 <span class="i">%Options</span> = <span class="s">(</span><span class="s">)</span><span class="sc">;</span>
|
|
|
269 256 <span class="i">$Options</span>{<span class="w">mode</span>} = <span class="q">"exportdata"</span><span class="sc">;</span>
|
|
|
270 257 <span class="i">$Options</span>{<span class="w">exportlobs</span>} = <span class="q">"no"</span><span class="sc">;</span>
|
|
|
271 258 <span class="i">$Options</span>{<span class="w">exportdatalabels</span>} = <span class="q">"yes"</span><span class="sc">;</span>
|
|
|
272 259 <span class="i">$Options</span>{<span class="w">numoutfiles</span>} = <span class="q">"single"</span><span class="sc">;</span>
|
|
|
273 260 <span class="i">$Options</span>{<span class="w">outdelim</span>} = <span class="q">"comma"</span><span class="sc">;</span>
|
|
|
274 261 <span class="i">$Options</span>{<span class="w">quote</span>} = <span class="q">"yes"</span><span class="sc">;</span>
|
|
|
275 262
|
|
|
276 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">"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">"numoutfiles|n=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>
|
|
|
277 264 <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>
|
|
|
278 265 <span class="s">}</span>
|
|
|
279 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>
|
|
|
280 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>
|
|
|
281 268 <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>
|
|
|
282 269 <span class="s">}</span>
|
|
|
283 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">"Error: Couldn't chdir $Options{workingdir}: $! \n"</span><span class="sc">;</span>
|
|
|
284 271 <span class="s">}</span>
|
|
|
285 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>
|
|
|
286 273 <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>
|
|
|
287 274 <span class="s">}</span>
|
|
|
288 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>
|
|
|
289 276 <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>
|
|
|
290 277 <span class="s">}</span>
|
|
|
291 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>
|
|
|
292 279 <span class="k">die</span> <span class="q">"Error: The value specified, $Options{mode}, for option \"-n --numoutfiles\" is not valid. Allowed values: single or multiple\n"</span><span class="sc">;</span>
|
|
|
293 280 <span class="s">}</span>
|
|
|
294 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>
|
|
|
295 282 <span class="k">die</span> <span class="q">"Error: The value specified, $Options{mode}, for option \"-m --mode\" is not valid. Allowed values: exportdata or describetable\n"</span><span class="sc">;</span>
|
|
|
296 283 <span class="s">}</span>
|
|
|
297 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>
|
|
|
298 285 <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>
|
|
|
299 286 <span class="s">}</span>
|
|
|
300 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>
|
|
|
301 288 <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>
|
|
|
302 289 <span class="s">}</span>
|
|
|
303 290 <span class="s">}</span>
|
|
|
304 291
|
|
|
305 <a name="EOF-"></a></pre>
|
|
|
306 <p> </p>
|
|
|
307 <br />
|
|
|
308 <center>
|
|
|
309 <img src="../../../images/h2o2.png">
|
|
|
310 </center>
|
|
|
311 </body>
|
|
|
312 </html>
|
