Mercurial > repos > deepakjadmin > mayatool3_test2
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 |