view docs/scripts/man1/DBSchemaTablesToTextFiles.1 @ 0:4816e4a8ae95 draft default tip

Uploaded

.\" Automatically generated by Pod::Man 2.25 (Pod::Simple 3.22)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.ie \nF \{\
.    de IX
.    tm Index:\\$1\t\\n%\t"\\$2"
..
.    nr % 0
.    rr F
.\}
.el \{\
.    de IX
..
.\}
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
.    \" fudge factors for nroff and troff
.if n \{\
.    ds #H 0
.    ds #V .8m
.    ds #F .3m
.    ds #[ \f1
.    ds #] \fP
.\}
.if t \{\
.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
.    ds #V .6m
.    ds #F 0
.    ds #[ \&
.    ds #] \&
.\}
.    \" simple accents for nroff and troff
.if n \{\
.    ds ' \&
.    ds ` \&
.    ds ^ \&
.    ds , \&
.    ds ~ ~
.    ds /
.\}
.if t \{\
.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
.    \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
.    \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
.    \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
.    ds : e
.    ds 8 ss
.    ds o a
.    ds d- d\h'-1'\(ga
.    ds D- D\h'-1'\(hy
.    ds th \o'bp'
.    ds Th \o'LP'
.    ds ae ae
.    ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "DBSCHEMATABLESTOTEXTFILES 1"
.TH DBSCHEMATABLESTOTEXTFILES 1 "2015-03-29" "perl v5.14.2" "MayaChemTools"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
DBSchemaTablesToTextFiles.pl \- Export table data from database SchemaName(s) into CSV/TSV text files
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
DBSchemaTablesToTextFiles.pl SchemaName(s)...
.PP
DBSchemaTablesToTextFiles.pl [\fB\-d, \-\-dbdriver\fR mysql | Oracle| Postgres or Pg] [\fB\-\-dbhost \fR hostname]
[\fB\-\-dbname\fR databasename] [\fB\-\-dbpassword\fR password] [\fB\-\-dbusername\fR username]
[\fB\-\-exportdatalabels\fR yes | no] [\fB\-\-exportlobs\fR yes | no] [\fB\-h, \-\-help\fR]
[\fB\-m, \-\-mode\fR exportdata | describetable] [\fB\-n, \-\-numoutfilesmode\fR single | multiple]
[\fB\-o, \-\-overwrite\fR] [\fB\-\-outdelim\fR comma | tab | semicolon]
[\fB\-q, \-\-quote\fR yes | no] [\fB\-r, \-\-root\fR rootname] [\fB\-\-replacenullstr string\fR]
[\fB\-w \-\-workingdir\fR dirname] SchemaName(s)...
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
Export table data from database SchemaName(s) into \s-1CSV/TSV\s0 text files. Use \fB\-n \-\-numoutfiles\fR
option to control the number of text files generated for a database schema.
.SH "OPTIONS"
.IX Header "OPTIONS"
.IP "\fB\-d, \-\-dbdriver\fR \fImysql | Oracle | Postgres or Pg\fR" 4
.IX Item "-d, --dbdriver mysql | Oracle | Postgres or Pg"
Database driver name. Possible values: \fImysql, Oracle, Postgres or Pg\fR. Default: \fIMySQL\fR or value of
environment variable \s-1DBI_DRIVER\s0. This script has only been tested with MySQL, Oracle
and PostgreSQL drivers.
.IP "\fB\-\-dbhost \fR \fIhostname\fR" 4
.IX Item "--dbhost  hostname"
Database host name. Default: \fI127.0.0.1\fR for both MySQL and Oracle. For remote
databases, specify complete remote host domain: \fIdbhostname.org\fR or something
like it.
.IP "\fB\-\-dbname\fR \fIdatabasename\fR" 4
.IX Item "--dbname databasename"
Database name. Default: mysql for MySQL, postgres for PostgreSQL and none for Oracle.
For connecting to local/remote Oracle databases, this value can be left undefined assuming
\&\fB\-\-dbhost\fR is correctly specified.
.IP "\fB\-\-dbpassword\fR \fIpassword\fR" 4
.IX Item "--dbpassword password"
Database user password. Default: \fInone\fR and value of environment variable \s-1DBI_PASS\s0
is used for connecting to database.
.IP "\fB\-\-dbusername\fR \fIusername\fR" 4
.IX Item "--dbusername username"
Database user name. Default: \fInone\fR and value of environment variable \s-1DBI_USER\s0 is
used for connecting to database.
.IP "\fB\-\-exportdatalabels\fR \fIyes | no\fR" 4
.IX Item "--exportdatalabels yes | no"
This option is mode specific and controls exporting of column data labels during
exportdata mode. Possible values: \fIyes or no\fR. Default: \fIyes\fR.
.IP "\fB\-\-exportlobs\fR \fIyes | no\fR" 4
.IX Item "--exportlobs yes | no"
This option is mode specific and controls exporting of \s-1CLOB/BLOB\s0 or \s-1BYTEA\s0 data columns during
exportdata mode. Possible values: \fIyes or no\fR. Default: \fIno\fR.
.IP "\fB\-h, \-\-help\fR" 4
.IX Item "-h, --help"
Print this help message.
.IP "\fB\-m, \-\-mode\fR \fIexportdata | describetable\fR" 4
.IX Item "-m, --mode exportdata | describetable"
Data selection criterion from database. Possible values: \fIexportdata or describetable\fR.
Default value: \fIexportdata\fR.
.IP "\fB\-n, \-\-numoutfilesmode\fR \fIsingle | multiple\fR" 4
.IX Item "-n, --numoutfilesmode single | multiple"
Number of \s-1CSV/TSV\s0 output files to generate: combine output into one file or generate
a different file for each table in a schema. Possible values: \fIsingle or multiple\fR. Default:
\&\fIsingle\fR.
.Sp
In a single output file, data for different tables is separated by a blank line.
.Sp
Single outfile option in \fIexportdata\fR mode is quite useful for exporting data from all tables
in specifed schemas to one file which can be used for migrating data to another database
or simply provide a backup of data; during \fIdescribetable\fR mode, it provides a means to
collect information about columns of all schema tables which can help in creation of these
tables on a different database server.
.IP "\fB\-o, \-\-overwrite\fR" 4
.IX Item "-o, --overwrite"
Overwrite existing files.
.IP "\fB\-\-outdelim\fR \fIcomma | tab | semicolon\fR" 4
.IX Item "--outdelim comma | tab | semicolon"
Output text file delimiter. Possible values: \fIcomma, tab, or semicolon\fR.
Default value: \fIcomma\fR
.IP "\fB\-q, \-\-quote\fR \fIyes | no\fR" 4
.IX Item "-q, --quote yes | no"
Put quotes around column values in output text file. Possible values: \fIyes or
no\fR. Default value: \fIyes\fR.
.IP "\fB\-r, \-\-root\fR \fIrootname\fR" 4
.IX Item "-r, --root rootname"
New file name is generated using the root:<Root>.<Ext> and <Root><TableName>.<Ext>
for \fIsingle\fR and \fImultiple\fR \fB\-n \-\-numoutfiles\fR option values. Default file name for
\&\fIsingle\fR \fB\-n \-\-numoutfiles\fR option value: <Mode>SchemaTables.<Ext>. Default file
names for \fImultiple\fR \fB\-n \-\-numoutfiles\fR value: <Mode><SchemaName><TableName>.<Ext>.
Based on \fB\-m \-\-mode\fR option, \fIExport\fR or \fIDescribe\fR <Mode> value is used. The csv and tsv
<Ext> values are used for comma/semicolon, and tab delimited text files respectively. This option is
ignored for multiple input schema names.
.IP "\fB\-\-replacenullstr\fR \fIstring\fR" 4
.IX Item "--replacenullstr string"
Replace \s-1NULL\s0 or undefined row values with specified value. Default: \fInone\fR.
.Sp
For importing output text files into MySQL database using \*(L"load data local infile '<tablename>.tsv'
into table <tablename>\*(R" command, use \fI\-\-raplacenullstr \*(L"\s-1NULL\s0\*(R"\fR in conjunction with \fI\-\-exportdatalabels no\fR,
\&\fI\-\-quote no\fR, and \fI\-\-outdelim tab\fR options: it'll generate files for direct import into MySQL assuming
tables already exists.
.IP "\fB\-w \-\-workingdir\fR \fIdirname\fR" 4
.IX Item "-w --workingdir dirname"
Location of working directory. Default: current directory.
.SH "EXAMPLES"
.IX Header "EXAMPLES"
To export data in all tables from mysql schema on a MySQL server running on a local machine
using username/password from \s-1DBI_USER\s0 and \s-1DBI_PASS\s0 environmental variables, type:
.PP
.Vb 1
\&    % DBSchemaTablesToTextFiles.pl mysql
.Ve
.PP
To describe all tables in mysql and test schemas on a MySQL server running on a remote machine
using explicit username/password and capturing the ouput into a DescribeTables.csv file, type:
.PP
.Vb 4
\&    % DBSchemaTablesToTextFiles.pl \-\-dbdriver mysql \-\-dbuser <name>
\&      \-\-dbpassword <pasword> \-\-dbname mysql \-\-dbhost
\&      <mysqlhostname.org> \-r DescribeTable \-m describetable
\&      \-o mysql test
.Ve
.PP
To describe all tables in \s-1SCOTT\s0 schema in Oracle running on a remote machine using explicit
username/password and capturing the ouput into a DescribeAllTable.tsv file, type:
.PP
.Vb 4
\&    % DBSchemaTablesToTextFiles.pl \-\-dbdriver Oracle \-\-dbuser <name>
\&      \-\-dbpassword <pasword> \-\-dbhost <oraclehostname.com>
\&      \-r DescribeAllTable \-m describetable \-\-outdelim tab \-\-quote no
\&      \-o SCOTT
.Ve
.PP
To export data in all tables in mysql and test schemas on a MySQL server running at a local
machine using explicit username/password and capturing the data in \s-1TSV\s0 file for each table with empty
values substitued with \s-1NULL\s0 and clob/blob data, type:
.PP
.Vb 4
\&    % DBSchemaTablesToTextFiles.pl \-\-dbdriver Oracle \-\-dbuser <name>
\&      \-\-dbpassword <pasword> \-r ExportTables \-\-outdelim tab \-\-quote no
\&      \-\-replacenullstr "NULL" \-m exportdata \-\-exportlobs no  \-\-numoutfiles
\&      multiple \-o user user_info
.Ve
.SH "AUTHOR"
.IX Header "AUTHOR"
Manish Sud <[e-mail]>
.SH "SEE ALSO"
.IX Header "SEE ALSO"
DBSQLToTextFiles.pl, DBTablesToTextFiles.pl
.SH "COPYRIGHT"
.IX Header "COPYRIGHT"
Copyright (C) 2015 Manish Sud. All rights reserved.
.PP
This file is part of MayaChemTools.
.PP
MayaChemTools is free software; you can redistribute it and/or modify it under
the terms of the \s-1GNU\s0 Lesser General Public License as published by the Free
Software Foundation; either version 3 of the License, or (at your option)
any later version.