view docs/scripts/man1/DBTablesToTextFiles.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 "DBTABLESTOTEXTFILES 1"
.TH DBTABLESTOTEXTFILES 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"
DBToTextFiles.pl \- Export data from database TableName(s) into CSV/TSV text files
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
DBTablesToTextFiles.pl TableName(s)...
.PP
DBTablesToTextFiles.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\-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] TableName(s)...
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
Export data from MySQL, Oracle or PostgreSQL database tables into \s-1CSV/TSV\s0 text files. Or perform
describe on all tables and store its output into \s-1CSV/TSV\s0 text files. A text file is generated
for each table.
.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\-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>. Default new file
file names: <Mode><TableName>.<Ext>. The csv and tsv <Ext> values are used
for comma/semicolon, and tab delimited text files respectively.This option is
ignored for multiple input table 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 all data in user and user_info tables from 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
\&    % DBTablesToTextFiles.pl \-o user user_info
.Ve
.PP
To describe user and user_info tables in a MySQL server running on a remote machine using explicit
username/password and capturing the output into a DescribeTables.csv file, type:
.PP
.Vb 3
\&    % DBTablesToTextFiles.pl \-\-dbdriver mysql \-\-dbuser <name> \-\-dbpassword
\&      <pasword> \-\-dbname mysql \-\-dbhost <mysqlhostname.org>
\&      \-r DescribeTable \-m describetable \-o user user_info
.Ve
.PP
To describe table all_tables in Oracle running on a remote machine using explicit
username/password and capturing the output into a DescribeAllTable.tsv file, type:
.PP
.Vb 3
\&    % DBTablesToTextFiles.pl \-\-dbdriver Oracle \-\-dbuser <name> \-\-dbpassword
\&      <pasword> \-\-dbhost <oraclehostname.com> \-r DescribeAllTable
\&      \-m describetable \-\-outdelim tab \-\-quote no \-o all_tables
.Ve
.PP
To export all data in user and user_info tables from MySQL server running on a local machine
using explicit username/password and capturing the data in ExportTables.tsv file with empty
values substituted with \s-1NULL\s0 and no column labels, type:
.PP
.Vb 4
\&    % DBTablesToTextFiles.pl \-\-dbdriver Oracle \-\-dbuser <name> \-\-dbpassword
\&      <pasword> \-r ExportTables \-\-outdelim tab \-\-quote no \-\-replacenullstr
\&      "\eN" \-m exportdata \-\-exportlobs no  \-\-exportdatalabels no \-o
\&      user user_info
.Ve
.SH "AUTHOR"
.IX Header "AUTHOR"
Manish Sud <[e-mail]>
.SH "SEE ALSO"
.IX Header "SEE ALSO"
DBSchemaTablesToTextFiles.pl, DBSQLToTextFiles.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.