=head1 NAME

tdsql - run SQL queries against a Teradata data warehouse server

=head1 SYNOPSIS

% tdsql [-h] [-H host] [-u user] [-p pass] [-d database] [-c charset] [-m mode] [-o file] [-f format] [-r] [sql] [args]

=head1 DESCRIPTION

Teradata ships with very little Unix command line support other than the BTEQ tool. In particular, it lacks an interactive command prompt tool (like mysql for MySQL, psql for PostgreSQL, or vsql for Vertica). The tdsql program aims to fill that gap.

This program runs an SQL query against the Teradata data warehouse, and then sends the output of the query either to stdout, or to an output file. The SQL can be provided on the command line, to stdin, or interactively via a command prompt that supports readline command line editing. Database credentials can be provided on the command line, or in the initialization file F<~/.tdsqlrc>.

=head2 Command-Line Options

The following optional command-line arguments are accepted:

=over 4

=item C<-h>, C<--help>

Display command line help and exit.

=item C<-H> I, C<--hostname=>I

Connect to this hostname (default localhost).

=item C<-u> I, C<--username=>I

Connect with this username (default your Unix username).

=item C<-p> I, C<--password=>I

Connect with this password (default is to prompt interactively for one).

=item C<-d> I, C<--database=>I

Connect initially to the given database (default is not in any database).

=item C<-c> I, C<--charset=>I

Specify character set; must be one of ASCII or UTF8

=item C<-m> I, C<--mode=>I

Specify mode; must be one of ANSI or TERADATA

=item C<-o> I, C<--output=>I

Write output to this file (default is to write to stdout).

=item C<-f> I, C<--format=>I

Write output in C (tab-separated, the default), C (comma-separated), C (pipe-separated), C pretty-printed boxed format (this is the slowest output format, as all output must be buffered before any output is printed), or C for vertical rows (like MySQL with \G).

=item C<-r>, C<--header>

Include a header row with text, csv, or vbar output. If a format other than text, csv, or vbar is specified, the header argument is ignored.

=back

The last optional command line arguments are the SQL to run, as a single string; and any arguments to pass to the SQL, if the SQL contains question mark (C<?>) positional placeholders. The default is to prompt for SQL interactively using a command line prompt, if stdin is a tty, or read stdin as verbatim SQL, if stdin is not a tty.

=head2 Initialization File

This script looks for a file C<~/.tdsqlrc> which is a file in YAML format that has the following structure (all contents optional):

hostname: I username: I password: I database: I charset: I mode: I

If the file is present, then any provided values are used as the default values for hostname, username, etc. This means you can avoid needing to continually repeat these from the command line. Note that if you record your password here, you should make sure the file has safe permissions (C<chmod 600 ~/.tdsqlrc>).

=head1 INSTALLATION

You can install this software simply by moving the F<bin/tdsql> program into your PATH (for example, in F</usr/local/bin>), and the man page in F<share/man/man1/tdsql.1> into your MANPATH (for example, in F</usr/local/share/man/man1>).

This software requires Perl (it is tested with Perl 5.12.3), and the following CPAN modules:

=over 4

=item *

DBI

=item *

Term::ReadKey

=item *

Term::ReadLine::Gnu

=item *

Text::CSV_XS

=back

It also relies on the DBD::Teradata module available here: Lhttp://www.presicient.com/tdatdbd/

That module in turn has these dependencies:

=over 4

=item *

Crypt:ECB

=item *

Crypt::Rijndael

=item *

Digest::SHA1

=back

=head1 EXAMPLES

% tdsql -H td -u johndoe

% echo 'SELECT col1, col2 FROM db1.table1' | tdsql -H td -u johndoe

% tdsql -o output.txt 'SELECT col1, col2 FROM db1.table1'

% tdsql -f box 'SELECT col1, col2 FROM db1.table1 WHERE col1 = ?' 'val1'

=head1 TODO

=over 4

=item *

Box format should not need to buffer all rows before starting display.

=item *

Recover from database connection timeouts.

=item *

Basic interaction should still work even without Term::ReadLine::Gnu.

=item *

Fall back to using stty if Term::ReadKey is not installed.

=back

=head1 AUTHOR

Andrew Ho Elt>F<ho@groupon.comE

=head1 SEE ALSO

Lhttps://github.com/groupon/tdsql

=head1 LICENSE

Copyright (c) 2012-2014, Groupon, Inc. All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

Neither the name of GROUPON nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.