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

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