annotate mayachemtools/bin/DBSchemaTablesToTextFiles.pl @ 9:ab29fa5c8c1f draft default tip

Uploaded
author deepakjadmin
date Thu, 15 Dec 2016 14:18:03 -0500
parents 73ae111cf86f
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
1 #!/usr/bin/perl -w
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
2 #
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
3 # $RCSfile: DBSchemaTablesToTextFiles.pl,v $
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
4 # $Date: 2015/02/28 20:46:19 $
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
5 # $Revision: 1.31 $
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
6 #
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
7 # Author: Manish Sud <msud@san.rr.com>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
8 #
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
9 # Copyright (C) 2015 Manish Sud. All rights reserved.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
10 #
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
11 # This file is part of MayaChemTools.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
12 #
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
13 # MayaChemTools is free software; you can redistribute it and/or modify it under
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
14 # the terms of the GNU Lesser General Public License as published by the Free
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
15 # Software Foundation; either version 3 of the License, or (at your option) any
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
16 # later version.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
17 #
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
18 # MayaChemTools is distributed in the hope that it will be useful, but without
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
19 # any warranty; without even the implied warranty of merchantability of fitness
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
20 # for a particular purpose. See the GNU Lesser General Public License for more
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
21 # details.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
22 #
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
23 # You should have received a copy of the GNU Lesser General Public License
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
24 # along with MayaChemTools; if not, see <http://www.gnu.org/licenses/> or
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
25 # write to the Free Software Foundation Inc., 59 Temple Place, Suite 330,
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
26 # Boston, MA, 02111-1307, USA.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
27 #
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
28
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
29 use strict;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
30 use FindBin; use lib "$FindBin::Bin/../lib";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
31 use Getopt::Long;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
32 use File::Basename;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
33 use Text::ParseWords;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
34 use Benchmark;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
35 use FileUtil;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
36 use TextUtil;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
37 use DBUtil;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
38
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
39 my($ScriptName, %Options, $StartTime, $EndTime, $TotalTime);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
40
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
41 # Autoflush STDOUT
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
42 $| = 1;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
43
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
44 # Starting message...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
45 $ScriptName = basename($0);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
46 print "\n$ScriptName: Starting...\n\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
47 $StartTime = new Benchmark;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
48
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
49 # Get the options and setup script...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
50 SetupScriptUsage();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
51 if ($Options{help} || @ARGV < 1) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
52 die GetUsageFromPod("$FindBin::Bin/$ScriptName");
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
53 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
54
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
55 my($DBDriver, $DBHost, $DBName, $DBUser, $DBPassword, $DBMode, $ExportDataLabels, $ExportLOBs, $OutDelim, $OutQuote, $ReplaceNullStr);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
56 ProcessOptions();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
57
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
58 # Connect to database...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
59 my($DBHandle);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
60 print "Connecting to $DBDriver:database=$DBName as $DBUser...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
61 $DBHandle = DBConnect($DBDriver, $DBName, $DBHost, $DBUser, $DBPassword);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
62
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
63 # Collect input parameters information...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
64 print "Checking input parameter(s)...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
65 my(@DBSchemaNames, @DBTableNames, @DBSQLStatements, @DBTextFiles, $SingleTextFileName);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
66 RetrieveDBInfo();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
67
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
68 if ($Options{numoutfiles} =~ /^single$/i ) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
69 GenerateSingleTextFile();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
70 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
71 else {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
72 GenerateMultipleTextFiles();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
73 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
74 print "\nDisconnecting from $DBDriver:database=$DBName...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
75 DBDisconnect($DBHandle);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
76
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
77 print "$ScriptName:Done...\n\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
78
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
79 $EndTime = new Benchmark;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
80 $TotalTime = timediff ($EndTime, $StartTime);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
81 print "Total time: ", timestr($TotalTime), "\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
82
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
83 ###############################################################################
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
84
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
85 # Generate multiple text files...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
86 sub GenerateMultipleTextFiles {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
87 my($Index, $TextFile, $SQL);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
88 if (@DBTextFiles > 1) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
89 print "Generating text files...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
90 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
91 TEXTFILE: for $Index (0 .. $#DBTextFiles) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
92 $TextFile = $DBTextFiles[$Index];
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
93 $SQL = $DBSQLStatements[$Index];
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
94
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
95 if (@DBTextFiles > 1) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
96 print "\nGenerating text file $TextFile...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
97 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
98 else {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
99 print "Generating text file $TextFile...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
100 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
101
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
102 if (!open TEXTFILE, ">$TextFile") {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
103 warn "Warning: Abandoning $TextFile generation: Couldn't open it: $! \n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
104 next TEXTFILE;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
105 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
106 print "Processing SQL statement \"$SQL\"...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
107
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
108 if (DBSQLToTextFile($DBHandle, $SQL, \*TEXTFILE, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr)) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
109 warn "Warning: Abandoning $TextFile generation...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
110 next TEXTFILE;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
111 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
112 close TEXTFILE;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
113 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
114 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
115
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
116 # Generate single text file...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
117 sub GenerateSingleTextFile {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
118 my($Index, $TextFile, $SQL, $SchemaName, $TableName);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
119
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
120 open TEXTFILE, ">$SingleTextFileName" or die "Couldn't open $SingleTextFileName: $! \n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
121 print "Generating text file $SingleTextFileName...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
122
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
123 SQL: for $Index (0 .. $#DBSQLStatements) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
124 $SchemaName = $DBSchemaNames[$Index];
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
125 $TableName = $DBTableNames[$Index];
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
126 $SQL = $DBSQLStatements[$Index];
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
127
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
128 $TableName = qq($SchemaName.$TableName);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
129 $TableName = QuoteAWord($TableName, $OutQuote);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
130 print TEXTFILE "\n\n$TableName\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
131
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
132 if (DBSQLToTextFile($DBHandle, $SQL, \*TEXTFILE, $OutDelim, $OutQuote, $ExportDataLabels, $ExportLOBs, $ReplaceNullStr)) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
133 warn "Warning: Abandoning table $TableName ...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
134 next SQL;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
135 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
136 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
137 close TEXTFILE;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
138 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
139
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
140 # Collect input parameters information...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
141 sub RetrieveDBInfo {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
142 my($FileExt, $UserFileName, $FileDBPrefix);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
143
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
144 # Setup out file ext...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
145 $FileExt = ($Options{outdelim} =~ /^tab$/i) ? "tsv" : "csv";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
146 $FileDBPrefix = ($DBMode =~ /^exportdata$/i) ? "Export" : "Describe";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
147
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
148 # Get user specified information...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
149 $UserFileName = "";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
150 if ($Options{root} && (@ARGV == 1)) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
151 my($RootFileDir, $RootFileName, $RootFileExt) = ParseFileName($Options{root});
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
152 if ($RootFileName && $RootFileExt) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
153 $UserFileName = $RootFileName;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
154 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
155 else {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
156 $UserFileName = $Options{root};
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
157 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
158 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
159 $SingleTextFileName = "";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
160 if ($Options{numoutfiles} =~ /^single$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
161 $SingleTextFileName = $UserFileName ? $UserFileName : ("$FileDBPrefix" . "SchemaTables");
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
162 $SingleTextFileName .= ".$FileExt";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
163 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
164
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
165 # For each input schema name: collect all the table names, set up appropriate
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
166 # SQL statements, and output file names...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
167 #
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
168 my($SchemaName, $SQL, $FileName, @SchemaTableNames, $TableName);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
169 @DBSchemaNames = ();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
170 @DBTableNames = ();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
171 @DBSQLStatements = ();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
172 @DBTextFiles = ();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
173 @SchemaTableNames = ();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
174 SCHEMANAME: for $SchemaName (@ARGV) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
175 $SchemaName = ($DBDriver =~ /^(mysql|Oracle)$/i) ? uc($SchemaName) : $SchemaName;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
176 if (!(@SchemaTableNames = DBFetchSchemaTableNames($DBDriver, $DBHandle, $SchemaName))) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
177 warn "Warning: Ignoring schema $SchemaName...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
178 next SCHEMANAME;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
179 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
180 # Prepare SQL statement for each table.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
181 for $TableName (@SchemaTableNames) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
182 push @DBSchemaNames, $SchemaName;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
183 push @DBTableNames, $TableName;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
184 $SQL = ($DBMode =~ /^exportdata$/i) ? DBSetupSelectSQL($DBDriver, $TableName, $SchemaName) : DBSetupDescribeSQL($DBDriver, $TableName, $SchemaName);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
185 push @DBSQLStatements, $SQL;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
186 if ($Options{numoutfiles} =~ /^multiple$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
187 $FileName = $UserFileName ? ("$UserFileName" . "$TableName") : ("$FileDBPrefix" . "$SchemaName" . "$TableName");
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
188 $FileName .= ".$FileExt";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
189 if (!$Options{overwrite}) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
190 if (-e $FileName) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
191 die "Error: The file $FileName already exists.\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
192 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
193 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
194 push @DBTextFiles, $FileName;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
195 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
196 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
197 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
198 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
199
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
200 # Process option values...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
201 sub ProcessOptions {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
202
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
203 $DBDriver = $Options{dbdriver} ? $Options{dbdriver} : (exists $ENV{DBI_DRIVER} ? $ENV{DBI_DRIVER} : "") ;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
204 if ($DBDriver) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
205 if ($DBDriver =~ /^oracle$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
206 $DBDriver = "Oracle";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
207 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
208 elsif ($DBDriver =~ /^mysql$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
209 $DBDriver = "mysql";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
210 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
211 elsif ($DBDriver =~ /^(Pg|Postgres)$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
212 $DBDriver = "Pg";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
213 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
214 else {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
215 if ($Options{dbdriver}) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
216 die "Error: The value specified, $DBDriver, for option \"-d --dbdriver\" is not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
217 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
218 else {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
219 die "Error: The value specified, $DBDriver, using environment variable DBI_DRIVER not valid. Allowed values: MySQL, Oracle, Postgres or Pg\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
220 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
221 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
222 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
223 else {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
224 $DBDriver = "mysql";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
225 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
226 $DBHost = $Options{dbhost} ? $Options{dbhost} : (exists $ENV{DBI_HOST} ? $ENV{DBI_HOST} : "127.0.0.1");
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
227 $DBName = $Options{dbname} ? $Options{dbname} : (exists $ENV{DBI_NAME} ? $ENV{DBI_NAME} : "");
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
228 if (!$DBName) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
229 if ($DBDriver =~ /^mysql$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
230 $DBName = "mysql";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
231 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
232 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
233 $DBUser = $Options{dbusername} ? $Options{dbusername} : (exists $ENV{DBI_USER} ? $ENV{DBI_USER} : "") ;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
234 if (!$DBUser) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
235 die "Error: No database username specified. Use \"--dbusername\" option or environment variable DBI_USER to enter a valid value.\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
236 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
237 $DBPassword = $Options{dbpassword} ? $Options{dbpassword} : (exists $ENV{DBI_PASS} ? $ENV{DBI_PASS} : "") ;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
238 if (!$DBPassword) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
239 die "Error: No database password specified. Use \"--dbpassword\" option or environment variable DBI_PASS to enter a valid value.\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
240 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
241 $DBMode = $Options{mode};
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
242 $ExportLOBs = ($Options{exportlobs} =~ /^yes$/i) ? 1 : 0;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
243 $ExportDataLabels = ($DBMode =~ /^describetable$/i) ? 1 : (($Options{exportdatalabels} =~ /^yes$/i) ? 1 : 0);
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
244
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
245 $OutDelim = ($Options{outdelim} =~ /^tab$/i ) ? "\t" : (($Options{outdelim} =~ /^semicolon$/i) ? "\;" : "\,");
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
246 $OutQuote = ($Options{quote} =~ /^yes$/i) ? 1 : 0;
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
247
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
248 $ReplaceNullStr = (defined($Options{replacenullstr}) && length($Options{replacenullstr})) ? $Options{replacenullstr} : "";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
249 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
250
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
251 # Setup script usage and retrieve command line arguments specified using various options...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
252 sub SetupScriptUsage {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
253
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
254 # Retrieve all the options...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
255 %Options = ();
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
256 $Options{mode} = "exportdata";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
257 $Options{exportlobs} = "no";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
258 $Options{exportdatalabels} = "yes";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
259 $Options{numoutfiles} = "single";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
260 $Options{outdelim} = "comma";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
261 $Options{quote} = "yes";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
262
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
263 if (!GetOptions(\%Options, "dbdriver|d=s", "dbhost=s", "dbname=s", "dbpassword=s", "dbusername=s", "exportdatalabels=s", "exportlobs=s", "help|h", "mode|m=s", "numoutfiles|n=s", "outdelim=s", "overwrite|o", "quote|q=s", "root|r=s", "replacenullstr=s", "workingdir|w=s")) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
264 die "\nTo get a list of valid options and their values, use \"$ScriptName -h\" or\n\"perl -S $ScriptName -h\" command and try again...\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
265 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
266 if ($Options{workingdir}) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
267 if (! -d $Options{workingdir}) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
268 die "Error: The value specified, $Options{workingdir}, for option \"-w --workingdir\" is not a directory name.\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
269 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
270 chdir $Options{workingdir} or die "Error: Couldn't chdir $Options{workingdir}: $! \n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
271 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
272 if ($Options{exportdatalabels} !~ /^(yes|no)$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
273 die "Error: The value specified, $Options{exportlobs}, for option \"--exportdatalabels\" is not valid. Allowed values: yes or no\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
274 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
275 if ($Options{exportlobs} !~ /^(yes|no)$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
276 die "Error: The value specified, $Options{exportlobs}, for option \"--exportlobs\" is not valid. Allowed values: yes or no\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
277 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
278 if ($Options{numoutfiles} !~ /^(single|multiple)$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
279 die "Error: The value specified, $Options{mode}, for option \"-n --numoutfiles\" is not valid. Allowed values: single or multiple\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
280 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
281 if ($Options{mode} !~ /^(exportdata|describetable)$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
282 die "Error: The value specified, $Options{mode}, for option \"-m --mode\" is not valid. Allowed values: exportdata or describetable\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
283 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
284 if ($Options{outdelim} !~ /^(comma|semicolon|tab)$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
285 die "Error: The value specified, $Options{outdelim}, for option \"--outdelim\" is not valid. Allowed values: comma, tab, or semicolon\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
286 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
287 if ($Options{quote} !~ /^(yes|no)$/i) {
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
288 die "Error: The value specified, $Options{quote}, for option \"-q --quote\" is not valid. Allowed values: yes or no\n";
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
289 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
290 }
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
291
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
292 __END__
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
293
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
294 =head1 NAME
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
295
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
296 DBSchemaTablesToTextFiles.pl - Export table data from database SchemaName(s) into CSV/TSV text files
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
297
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
298 =head1 SYNOPSIS
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
299
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
300 DBSchemaTablesToTextFiles.pl SchemaName(s)...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
301
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
302 DBSchemaTablesToTextFiles.pl [B<-d, --dbdriver> mysql | Oracle| Postgres or Pg] [B<--dbhost > hostname]
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
303 [B<--dbname> databasename] [B<--dbpassword> password] [B<--dbusername> username]
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
304 [B<--exportdatalabels> yes | no] [B<--exportlobs> yes | no] [B<-h, --help>]
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
305 [B<-m, --mode> exportdata | describetable] [B<-n, --numoutfilesmode> single | multiple]
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
306 [B<-o, --overwrite>] [B<--outdelim> comma | tab | semicolon]
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
307 [B<-q, --quote> yes | no] [B<-r, --root> rootname] [B<--replacenullstr string>]
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
308 [B<-w --workingdir> dirname] SchemaName(s)...
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
309
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
310 =head1 DESCRIPTION
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
311
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
312 Export table data from database SchemaName(s) into CSV/TSV text files. Use B<-n --numoutfiles>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
313 option to control the number of text files generated for a database schema.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
314
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
315 =head1 OPTIONS
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
316
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
317 =over 4
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
318
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
319 =item B<-d, --dbdriver> I<mysql | Oracle | Postgres or Pg>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
320
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
321 Database driver name. Possible values: I<mysql, Oracle, Postgres or Pg>. Default: I<MySQL> or value of
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
322 environment variable DBI_DRIVER. This script has only been tested with MySQL, Oracle
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
323 and PostgreSQL drivers.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
324
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
325 =item B<--dbhost > I<hostname>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
326
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
327 Database host name. Default: I<127.0.0.1> for both MySQL and Oracle. For remote
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
328 databases, specify complete remote host domain: I<dbhostname.org> or something
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
329 like it.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
330
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
331 =item B<--dbname> I<databasename>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
332
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
333 Database name. Default: mysql for MySQL, postgres for PostgreSQL and none for Oracle.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
334 For connecting to local/remote Oracle databases, this value can be left undefined assuming
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
335 B<--dbhost> is correctly specified.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
336
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
337 =item B<--dbpassword> I<password>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
338
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
339 Database user password. Default: I<none> and value of environment variable DBI_PASS
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
340 is used for connecting to database.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
341
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
342 =item B<--dbusername> I<username>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
343
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
344 Database user name. Default: I<none> and value of environment variable DBI_USER is
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
345 used for connecting to database.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
346
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
347 =item B<--exportdatalabels> I<yes | no>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
348
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
349 This option is mode specific and controls exporting of column data labels during
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
350 exportdata mode. Possible values: I<yes or no>. Default: I<yes>.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
351
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
352 =item B<--exportlobs> I<yes | no>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
353
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
354 This option is mode specific and controls exporting of CLOB/BLOB or BYTEA data columns during
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
355 exportdata mode. Possible values: I<yes or no>. Default: I<no>.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
356
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
357 =item B<-h, --help>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
358
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
359 Print this help message.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
360
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
361 =item B<-m, --mode> I<exportdata | describetable>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
362
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
363 Data selection criterion from database. Possible values: I<exportdata or describetable>.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
364 Default value: I<exportdata>.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
365
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
366 =item B<-n, --numoutfilesmode> I<single | multiple>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
367
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
368 Number of CSV/TSV output files to generate: combine output into one file or generate
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
369 a different file for each table in a schema. Possible values: I<single or multiple>. Default:
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
370 I<single>.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
371
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
372 In a single output file, data for different tables is separated by a blank line.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
373
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
374 Single outfile option in I<exportdata> mode is quite useful for exporting data from all tables
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
375 in specifed schemas to one file which can be used for migrating data to another database
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
376 or simply provide a backup of data; during I<describetable> mode, it provides a means to
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
377 collect information about columns of all schema tables which can help in creation of these
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
378 tables on a different database server.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
379
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
380 =item B<-o, --overwrite>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
381
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
382 Overwrite existing files.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
383
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
384 =item B<--outdelim> I<comma | tab | semicolon>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
385
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
386 Output text file delimiter. Possible values: I<comma, tab, or semicolon>.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
387 Default value: I<comma>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
388
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
389 =item B<-q, --quote> I<yes | no>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
390
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
391 Put quotes around column values in output text file. Possible values: I<yes or
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
392 no>. Default value: I<yes>.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
393
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
394 =item B<-r, --root> I<rootname>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
395
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
396 New file name is generated using the root:<Root>.<Ext> and <Root><TableName>.<Ext>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
397 for I<single> and I<multiple> B<-n --numoutfiles> option values. Default file name for
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
398 I<single> B<-n --numoutfiles> option value: <Mode>SchemaTables.<Ext>. Default file
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
399 names for I<multiple> B<-n --numoutfiles> value: <Mode><SchemaName><TableName>.<Ext>.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
400 Based on B<-m --mode> option, I<Export> or I<Describe> <Mode> value is used. The csv and tsv
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
401 <Ext> values are used for comma/semicolon, and tab delimited text files respectively. This option is
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
402 ignored for multiple input schema names.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
403
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
404 =item B<--replacenullstr> I<string>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
405
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
406 Replace NULL or undefined row values with specified value. Default: I<none>.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
407
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
408 For importing output text files into MySQL database using "load data local infile '<tablename>.tsv'
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
409 into table <tablename>" command, use I<--raplacenullstr "NULL"> in conjunction with I<--exportdatalabels no>,
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
410 I<--quote no>, and I<--outdelim tab> options: it'll generate files for direct import into MySQL assuming
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
411 tables already exists.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
412
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
413 =item B<-w --workingdir> I<dirname>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
414
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
415 Location of working directory. Default: current directory.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
416
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
417 =back
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
418
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
419 =head1 EXAMPLES
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
420
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
421 To export data in all tables from mysql schema on a MySQL server running on a local machine
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
422 using username/password from DBI_USER and DBI_PASS environmental variables, type:
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
423
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
424 % DBSchemaTablesToTextFiles.pl mysql
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
425
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
426 To describe all tables in mysql and test schemas on a MySQL server running on a remote machine
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
427 using explicit username/password and capturing the ouput into a DescribeTables.csv file, type:
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
428
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
429 % DBSchemaTablesToTextFiles.pl --dbdriver mysql --dbuser <name>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
430 --dbpassword <pasword> --dbname mysql --dbhost
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
431 <mysqlhostname.org> -r DescribeTable -m describetable
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
432 -o mysql test
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
433
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
434 To describe all tables in SCOTT schema in Oracle running on a remote machine using explicit
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
435 username/password and capturing the ouput into a DescribeAllTable.tsv file, type:
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
436
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
437 % DBSchemaTablesToTextFiles.pl --dbdriver Oracle --dbuser <name>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
438 --dbpassword <pasword> --dbhost <oraclehostname.com>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
439 -r DescribeAllTable -m describetable --outdelim tab --quote no
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
440 -o SCOTT
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
441
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
442 To export data in all tables in mysql and test schemas on a MySQL server running at a local
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
443 machine using explicit username/password and capturing the data in TSV file for each table with empty
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
444 values substitued with NULL and clob/blob data, type:
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
445
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
446 % DBSchemaTablesToTextFiles.pl --dbdriver Oracle --dbuser <name>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
447 --dbpassword <pasword> -r ExportTables --outdelim tab --quote no
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
448 --replacenullstr "NULL" -m exportdata --exportlobs no --numoutfiles
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
449 multiple -o user user_info
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
450
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
451 =head1 AUTHOR
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
452
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
453 Manish Sud <msud@san.rr.com>
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
454
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
455 =head1 SEE ALSO
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
456
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
457 DBSQLToTextFiles.pl, DBTablesToTextFiles.pl
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
458
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
459 =head1 COPYRIGHT
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
460
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
461 Copyright (C) 2015 Manish Sud. All rights reserved.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
462
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
463 This file is part of MayaChemTools.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
464
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
465 MayaChemTools is free software; you can redistribute it and/or modify it under
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
466 the terms of the GNU Lesser General Public License as published by the Free
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
467 Software Foundation; either version 3 of the License, or (at your option)
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
468 any later version.
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
469
73ae111cf86f Uploaded
deepakjadmin
parents:
diff changeset
470 =cut