view mayachemtools/docs/scripts/man1/DBSQLToTextFiles.1 @ 0:73ae111cf86f draft

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 "DBSQLTOTEXTFILES 1"
.TH DBSQLTOTEXTFILES 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"
DBSQLToTextFiles.pl \- Export data from MySQL, Oracle or PostgreSQL database into CSV/TSV text files
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
DBSQLToTextFiles.pl SQLFileName(s) | SQLSelectStatement(s)...
.PP
DBSQLToTextFiles.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 SQLStatement | SQLFile] [\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] SQLFileName(s)  |  SQLSelectStatement(s)...
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
Export data from MySQL, Oracle or PostgreSQL database into \s-1CSV/TSV\s0 text files. Based on \fB\-m \-\-mode\fR
option value, two methods of data selection are availble: in line \s-1SQL\s0 select statement(s), or
\&\s-1SQL\s0 file name(s) containing \s-1SQL\s0 select statement(s). All command line parameters must
correspond to similar mode; mixing of parameters for different modes is not supported.
.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, Oracle and PostgreSQL. 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 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 \fISQLStatement | SQLFile\fR" 4
.IX Item "-m, --mode SQLStatement | SQLFile"
Data selection criterion from database. Two different command line parameter methods
are available: in line \s-1SQL\s0 statement(s) specification or file name(s) containing \s-1SQL\s0 select
statement(s). This value determines how command line parameters are processed.
.Sp
Possible values: \fISQLStatement or SQLFile\fR. Default value: \fISQLStatement\fR
.Sp
In SQLFile mode, \s-1SQL\s0 file contains select statements delimited by \fI;\fR. And the lines starting
with \fI#\fR or \fI\-\fR are ignored.
.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><No>.<Ext>. Default new file
file names: SQLStatement<No>.<Ext>, or <SQLFileName><StatementNo>.<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 parameters.
.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_info table 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
\&    % DBSQLToTextFiles.pl \-o "select * from user_info"
.Ve
.PP
To describe user table in a MySQL server running on a remote machine using explicit
username/password and capturing the output into a UserTable.csv file, type:
.PP
.Vb 3
\&    % DBSQLToTextFiles.pl \-\-dbdriver mysql \-\-dbuser <name> \-\-dbpassword
\&      <pasword> \-\-dbname mysql \-\-dbhost <mysqlhostname.org> \-r UserTable
\&      \-m SQLStatement \-o "select * from 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 AllTable.tsv file, type:
.PP
.Vb 3
\&    % DBSQLToTextFiles.pl \-\-dbdriver Oracle \-\-dbuser <name> \-\-dbpassword
\&      <pasword> \-\-dbhost <oraclehostname.com> \-r AllTable \-m SQLStatement
\&      \-\-outdelim tab \-\-quote no \-o "select * from all_tables"
.Ve
.PP
To run all \s-1SQL\s0 statement in a file sample.sql on a local Oracle host and capturing output
in a SampleSQL.csv file, type:
.PP
.Vb 2
\&    % DBSQLToTextFiles.pl \-\-dbdriver Oracle \-\-dbuser <name> \-\-dbpassword
\&      <pasword> \-r SampleSQL \-m SQLFile \-o sample.sql
.Ve
.SH "AUTHOR"
.IX Header "AUTHOR"
Manish Sud <[e-mail]>
.SH "SEE ALSO"
.IX Header "SEE ALSO"
DBSchemaTablesToTextFiles.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.