comparison docs/scripts/txt/DBSchemaTablesToTextFiles.txt @ 0:4816e4a8ae95 draft default tip

Uploaded
author deepakjadmin
date Wed, 20 Jan 2016 09:23:18 -0500
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:4816e4a8ae95
1 NAME
2 DBSchemaTablesToTextFiles.pl - Export table data from database
3 SchemaName(s) into CSV/TSV text files
4
5 SYNOPSIS
6 DBSchemaTablesToTextFiles.pl SchemaName(s)...
7
8 DBSchemaTablesToTextFiles.pl [-d, --dbdriver mysql | Oracle| Postgres or
9 Pg] [--dbhost hostname] [--dbname databasename] [--dbpassword password]
10 [--dbusername username] [--exportdatalabels yes | no] [--exportlobs yes
11 | no] [-h, --help] [-m, --mode exportdata | describetable] [-n,
12 --numoutfilesmode single | multiple] [-o, --overwrite] [--outdelim comma
13 | tab | semicolon] [-q, --quote yes | no] [-r, --root rootname]
14 [--replacenullstr string] [-w --workingdir dirname] SchemaName(s)...
15
16 DESCRIPTION
17 Export table data from database SchemaName(s) into CSV/TSV text files.
18 Use -n --numoutfiles option to control the number of text files
19 generated for a database schema.
20
21 OPTIONS
22 -d, --dbdriver *mysql | Oracle | Postgres or Pg*
23 Database driver name. Possible values: *mysql, Oracle, Postgres or
24 Pg*. Default: *MySQL* or value of environment variable DBI_DRIVER.
25 This script has only been tested with MySQL, Oracle and PostgreSQL
26 drivers.
27
28 --dbhost *hostname*
29 Database host name. Default: *127.0.0.1* for both MySQL and Oracle.
30 For remote databases, specify complete remote host domain:
31 *dbhostname.org* or something like it.
32
33 --dbname *databasename*
34 Database name. Default: mysql for MySQL, postgres for PostgreSQL and
35 none for Oracle. For connecting to local/remote Oracle databases,
36 this value can be left undefined assuming --dbhost is correctly
37 specified.
38
39 --dbpassword *password*
40 Database user password. Default: *none* and value of environment
41 variable DBI_PASS is used for connecting to database.
42
43 --dbusername *username*
44 Database user name. Default: *none* and value of environment
45 variable DBI_USER is used for connecting to database.
46
47 --exportdatalabels *yes | no*
48 This option is mode specific and controls exporting of column data
49 labels during exportdata mode. Possible values: *yes or no*.
50 Default: *yes*.
51
52 --exportlobs *yes | no*
53 This option is mode specific and controls exporting of CLOB/BLOB or
54 BYTEA data columns during exportdata mode. Possible values: *yes or
55 no*. Default: *no*.
56
57 -h, --help
58 Print this help message.
59
60 -m, --mode *exportdata | describetable*
61 Data selection criterion from database. Possible values: *exportdata
62 or describetable*. Default value: *exportdata*.
63
64 -n, --numoutfilesmode *single | multiple*
65 Number of CSV/TSV output files to generate: combine output into one
66 file or generate a different file for each table in a schema.
67 Possible values: *single or multiple*. Default: *single*.
68
69 In a single output file, data for different tables is separated by a
70 blank line.
71
72 Single outfile option in *exportdata* mode is quite useful for
73 exporting data from all tables in specifed schemas to one file which
74 can be used for migrating data to another database or simply provide
75 a backup of data; during *describetable* mode, it provides a means
76 to collect information about columns of all schema tables which can
77 help in creation of these tables on a different database server.
78
79 -o, --overwrite
80 Overwrite existing files.
81
82 --outdelim *comma | tab | semicolon*
83 Output text file delimiter. Possible values: *comma, tab, or
84 semicolon*. Default value: *comma*
85
86 -q, --quote *yes | no*
87 Put quotes around column values in output text file. Possible
88 values: *yes or no*. Default value: *yes*.
89
90 -r, --root *rootname*
91 New file name is generated using the root:<Root>.<Ext> and
92 <Root><TableName>.<Ext> for *single* and *multiple* -n --numoutfiles
93 option values. Default file name for *single* -n --numoutfiles
94 option value: <Mode>SchemaTables.<Ext>. Default file names for
95 *multiple* -n --numoutfiles value:
96 <Mode><SchemaName><TableName>.<Ext>. Based on -m --mode option,
97 *Export* or *Describe* <Mode> value is used. The csv and tsv <Ext>
98 values are used for comma/semicolon, and tab delimited text files
99 respectively. This option is ignored for multiple input schema
100 names.
101
102 --replacenullstr *string*
103 Replace NULL or undefined row values with specified value. Default:
104 *none*.
105
106 For importing output text files into MySQL database using "load data
107 local infile '<tablename>.tsv' into table <tablename>" command, use
108 *--raplacenullstr "NULL"* in conjunction with *--exportdatalabels
109 no*, *--quote no*, and *--outdelim tab* options: it'll generate
110 files for direct import into MySQL assuming tables already exists.
111
112 -w --workingdir *dirname*
113 Location of working directory. Default: current directory.
114
115 EXAMPLES
116 To export data in all tables from mysql schema on a MySQL server running
117 on a local machine using username/password from DBI_USER and DBI_PASS
118 environmental variables, type:
119
120 % DBSchemaTablesToTextFiles.pl mysql
121
122 To describe all tables in mysql and test schemas on a MySQL server
123 running on a remote machine using explicit username/password and
124 capturing the ouput into a DescribeTables.csv file, type:
125
126 % DBSchemaTablesToTextFiles.pl --dbdriver mysql --dbuser <name>
127 --dbpassword <pasword> --dbname mysql --dbhost
128 <mysqlhostname.org> -r DescribeTable -m describetable
129 -o mysql test
130
131 To describe all tables in SCOTT schema in Oracle running on a remote
132 machine using explicit username/password and capturing the ouput into a
133 DescribeAllTable.tsv file, type:
134
135 % DBSchemaTablesToTextFiles.pl --dbdriver Oracle --dbuser <name>
136 --dbpassword <pasword> --dbhost <oraclehostname.com>
137 -r DescribeAllTable -m describetable --outdelim tab --quote no
138 -o SCOTT
139
140 To export data in all tables in mysql and test schemas on a MySQL server
141 running at a local machine using explicit username/password and
142 capturing the data in TSV file for each table with empty values
143 substitued with NULL and clob/blob data, type:
144
145 % DBSchemaTablesToTextFiles.pl --dbdriver Oracle --dbuser <name>
146 --dbpassword <pasword> -r ExportTables --outdelim tab --quote no
147 --replacenullstr "NULL" -m exportdata --exportlobs no --numoutfiles
148 multiple -o user user_info
149
150 AUTHOR
151 Manish Sud <msud@san.rr.com>
152
153 SEE ALSO
154 DBSQLToTextFiles.pl, DBTablesToTextFiles.pl
155
156 COPYRIGHT
157 Copyright (C) 2015 Manish Sud. All rights reserved.
158
159 This file is part of MayaChemTools.
160
161 MayaChemTools is free software; you can redistribute it and/or modify it
162 under the terms of the GNU Lesser General Public License as published by
163 the Free Software Foundation; either version 3 of the License, or (at
164 your option) any later version.
165