sql.1

.\" Automatically generated by Pod::Man 4.11 (Pod::Simple 3.40)
.\"
.\" 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" ''
.    ds C`
.    ds C'
'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 >0, 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.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\"
.\" 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 "SQL 1"
.TH SQL 1 "2021-03-22" "20210322" "parallel"
.\" 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"
sql \- execute a command on a database determined by a dburl
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
\&\fBsql\fR [options] \fIdburl\fR [\fIcommands\fR]
.PP
\&\fBsql\fR [options] \fIdburl\fR < commandfile
.PP
\&\fB#!/usr/bin/sql\fR \fB\-\-shebang\fR [options] \fIdburl\fR
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
\&\s-1GNU\s0 \fBsql\fR aims to give a simple, unified interface for accessing
databases through all the different databases' command line
clients. So far the focus has been on giving a common way to specify
login information (protocol, username, password, hostname, and port
number), size (database and table size), and running queries.
.PP
The database is addressed using a \s-1DBURL.\s0 If \fIcommands\fR are left out
you will get that database's interactive shell.
.PP
\&\s-1GNU\s0 \fBsql\fR is often used in combination with \s-1GNU\s0 \fBparallel\fR.
.IP "\fIdburl\fR" 9
.IX Item "dburl"
A \s-1DBURL\s0 has the following syntax:
[sql:]vendor://
[[user][:password]@][host][:port]/[database][?sqlquery]
.Sp
See the section \s-1DBURL\s0 below.
.IP "\fIcommands\fR" 9
.IX Item "commands"
The \s-1SQL\s0 commands to run. Each argument will have a newline
appended.
.Sp
Example: \*(L"\s-1SELECT\s0 * \s-1FROM\s0 foo;\*(R" \*(L"\s-1SELECT\s0 * \s-1FROM\s0 bar;\*(R"
.Sp
If the arguments contain '\en' or '\ex0a' this will be replaced with a
newline:
.Sp
Example: \*(L"\s-1SELECT\s0 * \s-1FROM\s0 foo;\en \s-1SELECT\s0 * \s-1FROM\s0 bar;\*(R"
.Sp
If no commands are given \s-1SQL\s0 is read from the keyboard or \s-1STDIN.\s0
.Sp
Example: echo '\s-1SELECT\s0 * \s-1FROM\s0 foo;' | sql mysql:///
.IP "\fB\-\-db\-size\fR" 9
.IX Item "--db-size"
.PD 0
.IP "\fB\-\-dbsize\fR" 9
.IX Item "--dbsize"
.PD
Size of database. Show the size of the database on disk. For Oracle
this requires access to read the table \fIdba_data_files\fR \- the user
\&\fIsystem\fR has that.
.IP "\fB\-\-help\fR" 9
.IX Item "--help"
.PD 0
.IP "\fB\-h\fR" 9
.IX Item "-h"
.PD
Print a summary of the options to \s-1GNU\s0 \fBsql\fR and exit.
.IP "\fB\-\-html\fR" 9
.IX Item "--html"
\&\s-1HTML\s0 output. Turn on \s-1HTML\s0 tabular output.
.IP "\fB\-\-show\-processlist\fR" 9
.IX Item "--show-processlist"
.PD 0
.IP "\fB\-\-proclist\fR" 9
.IX Item "--proclist"
.IP "\fB\-\-listproc\fR" 9
.IX Item "--listproc"
.PD
Show the list of running queries.
.IP "\fB\-\-show\-databases\fR" 9
.IX Item "--show-databases"
.PD 0
.IP "\fB\-\-showdbs\fR" 9
.IX Item "--showdbs"
.IP "\fB\-\-list\-databases\fR" 9
.IX Item "--list-databases"
.IP "\fB\-\-listdbs\fR" 9
.IX Item "--listdbs"
.PD
List the databases (table spaces) in the database.
.IP "\fB\-\-show\-tables\fR" 9
.IX Item "--show-tables"
.PD 0
.IP "\fB\-\-list\-tables\fR" 9
.IX Item "--list-tables"
.IP "\fB\-\-table\-list\fR" 9
.IX Item "--table-list"
.PD
List the tables in the database.
.IP "\fB\-\-noheaders\fR" 9
.IX Item "--noheaders"
.PD 0
.IP "\fB\-\-no\-headers\fR" 9
.IX Item "--no-headers"
.IP "\fB\-n\fR" 9
.IX Item "-n"
.PD
Remove headers and footers and print only tuples. Bug in Oracle: it
still prints number of rows found.
.IP "\fB\-p\fR \fIpass-through\fR" 9
.IX Item "-p pass-through"
The string following \-p will be given to the database connection
program as arguments. Multiple \-p's will be joined with
space. Example: pass '\-U' and the user name to the program:
.Sp
\&\fI\-p \*(L"\-U scott\*(R"\fR can also be written \fI\-p \-U \-p scott\fR.
.IP "\fB\-r\fR" 9
.IX Item "-r"
Try 3 times. Short version of \fI\-\-retries 3\fR.
.IP "\fB\-\-retries\fR \fIntimes\fR" 9
.IX Item "--retries ntimes"
Try \fIntimes\fR times. If the client program returns with an error,
retry the command. Default is \fI\-\-retries 1\fR.
.IP "\fB\-\-sep\fR \fIstring\fR" 9
.IX Item "--sep string"
.PD 0
.IP "\fB\-s\fR \fIstring\fR" 9
.IX Item "-s string"
.PD
Field separator. Use \fIstring\fR as separator between columns.
.IP "\fB\-\-skip\-first\-line\fR" 9
.IX Item "--skip-first-line"
Do not use the first line of input (used by \s-1GNU\s0 \fBsql\fR itself
when called with \fB\-\-shebang\fR).
.IP "\fB\-\-table\-size\fR" 9
.IX Item "--table-size"
.PD 0
.IP "\fB\-\-tablesize\fR" 9
.IX Item "--tablesize"
.PD
Size of tables. Show the size of the tables in the database.
.IP "\fB\-\-verbose\fR" 9
.IX Item "--verbose"
.PD 0
.IP "\fB\-v\fR" 9
.IX Item "-v"
.PD
Print which command is sent.
.IP "\fB\-\-version\fR" 9
.IX Item "--version"
.PD 0
.IP "\fB\-V\fR" 9
.IX Item "-V"
.PD
Print the version \s-1GNU\s0 \fBsql\fR and exit.
.IP "\fB\-\-shebang\fR" 9
.IX Item "--shebang"
.PD 0
.IP "\fB\-Y\fR" 9
.IX Item "-Y"
.PD
\&\s-1GNU\s0 \fBsql\fR can be called as a shebang (#!) command as the first line of a script. Like this:
.Sp
.Vb 1
\&  #!/usr/bin/sql \-Y mysql:///
\&
\&  SELECT * FROM foo;
.Ve
.Sp
For this to work \fB\-\-shebang\fR or \fB\-Y\fR must be set as the first option.
.SH "DBURL"
.IX Header "DBURL"
A \s-1DBURL\s0 has the following syntax:
[sql:]vendor://
[[user][:password]@][host][:port]/[database][?sqlquery]
.PP
To quote special characters use %\-encoding specified in
http://tools.ietf.org/html/rfc3986#section\-2.1 (E.g. a password
containing '/' would contain '%2F').
.PP
Examples:
 mysql://scott:tiger@my.example.com/mydb
 sql:oracle://scott:tiger@ora.example.com/xe
 postgresql://scott:tiger@pg.example.com/pgdb
 pg:///
 postgresqlssl://scott@pg.example.com:3333/pgdb
 sql:sqlite2:////tmp/db.sqlite?SELECT * \s-1FROM\s0 foo;
 sqlite3:///../db.sqlite3?SELECT%20*%20FROM%20foo;
.PP
Currently supported vendors: MySQL (mysql), MySQL with \s-1SSL\s0 (mysqls,
mysqlssl), Oracle (oracle, ora), PostgreSQL (postgresql, pg, pgsql,
postgres), PostgreSQL with \s-1SSL\s0 (postgresqlssl, pgs, pgsqlssl,
postgresssl, pgssl, postgresqls, pgsqls, postgress), SQLite2 (sqlite,
sqlite2), SQLite3 (sqlite3).
.PP
Aliases must start with ':' and are read from
/etc/sql/aliases and ~/.sql/aliases. The user's own
~/.sql/aliases should only be readable by the user.
.PP
Example of aliases:
.PP
.Vb 12
\& :myalias1 pg://scott:tiger@pg.example.com/pgdb
\& :myalias2 ora://scott:tiger@ora.example.com/xe
\& # Short form of mysql://\`whoami\`:nopassword@localhost:3306/\`whoami\`
\& :myalias3 mysql:///
\& # Short form of mysql://\`whoami\`:nopassword@localhost:33333/mydb
\& :myalias4 mysql://:33333/mydb
\& # Alias for an alias
\& :m      :myalias4
\& # the sortest alias possible
\& :       sqlite2:////tmp/db.sqlite
\& # Including an SQL query
\& :query  sqlite:////tmp/db.sqlite?SELECT * FROM foo;
.Ve
.SH "EXAMPLES"
.IX Header "EXAMPLES"
.SS "Get an interactive prompt"
.IX Subsection "Get an interactive prompt"
The most basic use of \s-1GNU\s0 \fBsql\fR is to get an interactive prompt:
.PP
\&\fBsql sql:oracle://scott:tiger@ora.example.com/xe\fR
.PP
If you have setup an alias you can do:
.PP
\&\fBsql :myora\fR
.SS "Run a query"
.IX Subsection "Run a query"
To run a query directly from the command line:
.PP
\&\fBsql :myalias \*(L"\s-1SELECT\s0 * \s-1FROM\s0 foo;\*(R"\fR
.PP
Oracle requires newlines after each statement. This can be done like
this:
.PP
\&\fBsql :myora \*(L"\s-1SELECT\s0 * \s-1FROM\s0 foo;\*(R" \*(L"\s-1SELECT\s0 * \s-1FROM\s0 bar;\*(R"\fR
.PP
Or this:
.PP
\&\fBsql :myora \*(L"\s-1SELECT\s0 * \s-1FROM\s0 foo;\enSELECT * \s-1FROM\s0 bar;\*(R"\fR
.SS "Copy a PostgreSQL database"
.IX Subsection "Copy a PostgreSQL database"
To copy a PostgreSQL database use pg_dump to generate the dump and \s-1GNU\s0
\&\fBsql\fR to import it:
.PP
\&\fBpg_dump pg_database | sql pg://scott:tiger@pg.example.com/pgdb\fR
.SS "Empty all tables in a MySQL database"
.IX Subsection "Empty all tables in a MySQL database"
Using \s-1GNU\s0 \fBparallel\fR it is easy to empty all tables without dropping them:
.PP
\&\fBsql \-n mysql:/// 'show tables' | parallel sql mysql:/// \s-1DELETE FROM\s0 {};\fR
.SS "Drop all tables in a PostgreSQL database"
.IX Subsection "Drop all tables in a PostgreSQL database"
To drop all tables in a PostgreSQL database do:
.PP
\&\fBsql \-n pg:/// '\edt' | parallel \-\-colsep '\e|' \-r sql pg:/// \s-1DROP TABLE\s0 {2};\fR
.SS "Run as a script"
.IX Subsection "Run as a script"
Instead of doing:
.PP
\&\fBsql mysql:/// < sqlfile\fR
.PP
you can combine the sqlfile with the \s-1DBURL\s0 to make a
UNIX-script. Create a script called \fIdemosql\fR:
.PP
\&\fB#!/usr/bin/sql \-Y mysql:///\fR
.PP
\&\fB\s-1SELECT\s0 * \s-1FROM\s0 foo;\fR
.PP
Then do:
.PP
\&\fBchmod +x demosql; ./demosql\fR
.SS "Use \-\-colsep to process multiple columns"
.IX Subsection "Use --colsep to process multiple columns"
Use \s-1GNU\s0 \fBparallel\fR's \fB\-\-colsep\fR to separate columns:
.PP
\&\fBsql \-s '\et' :myalias '\s-1SELECT\s0 * \s-1FROM\s0 foo;' | parallel \-\-colsep '\et' do_stuff {4} {1}\fR
.SS "Retry if the connection fails"
.IX Subsection "Retry if the connection fails"
If the access to the database fails occasionally \fB\-\-retries\fR can help
make sure the query succeeds:
.PP
\&\fBsql \-\-retries 5 :myalias '\s-1SELECT\s0 * \s-1FROM\s0 really_big_foo;'\fR
.SS "Get info about the running database system"
.IX Subsection "Get info about the running database system"
Show how big the database is:
.PP
\&\fBsql \-\-db\-size :myalias\fR
.PP
List the tables:
.PP
\&\fBsql \-\-list\-tables :myalias\fR
.PP
List the size of the tables:
.PP
\&\fBsql \-\-table\-size :myalias\fR
.PP
List the running processes:
.PP
\&\fBsql \-\-show\-processlist :myalias\fR
.SH "REPORTING BUGS"
.IX Header "REPORTING BUGS"
\&\s-1GNU\s0 \fBsql\fR is part of \s-1GNU\s0 \fBparallel\fR. Report bugs to <bug\-parallel@gnu.org>.
.SH "AUTHOR"
.IX Header "AUTHOR"
When using \s-1GNU\s0 \fBsql\fR for a publication please cite:
.PP
O. Tange (2011): \s-1GNU SQL\s0 \- A Command Line Tool for Accessing Different
Databases Using DBURLs, ;login: The \s-1USENIX\s0 Magazine, April 2011:29\-32.
.PP
Copyright (C) 2008\-2010 Ole Tange http://ole.tange.dk
.PP
Copyright (C) 2010\-2021 Ole Tange, http://ole.tange.dk and Free
Software Foundation, Inc.
.SH "LICENSE"
.IX Header "LICENSE"
This program is free software; you can redistribute it and/or modify
it under the terms of the \s-1GNU\s0 General Public License as published by
the Free Software Foundation; either version 3 of the License, or
at your option any later version.
.PP
This program is distributed in the hope that it will be useful,
but \s-1WITHOUT ANY WARRANTY\s0; without even the implied warranty of
\&\s-1MERCHANTABILITY\s0 or \s-1FITNESS FOR A PARTICULAR PURPOSE.\s0  See the
\&\s-1GNU\s0 General Public License for more details.
.PP
You should have received a copy of the \s-1GNU\s0 General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.
.SS "Documentation license I"
.IX Subsection "Documentation license I"
Permission is granted to copy, distribute and/or modify this
documentation under the terms of the \s-1GNU\s0 Free Documentation License,
Version 1.3 or any later version published by the Free Software
Foundation; with no Invariant Sections, with no Front-Cover Texts, and
with no Back-Cover Texts.  A copy of the license is included in the
file \s-1LICENSES/GFDL\-1\s0.3\-or\-later.txt.
.SS "Documentation license \s-1II\s0"
.IX Subsection "Documentation license II"
You are free:
.IP "\fBto Share\fR" 9
.IX Item "to Share"
to copy, distribute and transmit the work
.IP "\fBto Remix\fR" 9
.IX Item "to Remix"
to adapt the work
.PP
Under the following conditions:
.IP "\fBAttribution\fR" 9
.IX Item "Attribution"
You must attribute the work in the manner specified by the author or
licensor (but not in any way that suggests that they endorse you or
your use of the work).
.IP "\fBShare Alike\fR" 9
.IX Item "Share Alike"
If you alter, transform, or build upon this work, you may distribute
the resulting work only under the same, similar or a compatible
license.
.PP
With the understanding that:
.IP "\fBWaiver\fR" 9
.IX Item "Waiver"
Any of the above conditions can be waived if you get permission from
the copyright holder.
.IP "\fBPublic Domain\fR" 9
.IX Item "Public Domain"
Where the work or any of its elements is in the public domain under
applicable law, that status is in no way affected by the license.
.IP "\fBOther Rights\fR" 9
.IX Item "Other Rights"
In no way are any of the following rights affected by the license:
.RS 9
.IP "\(bu" 9
Your fair dealing or fair use rights, or other applicable
copyright exceptions and limitations;
.IP "\(bu" 9
The author's moral rights;
.IP "\(bu" 9
Rights other persons may have either in the work itself or in
how the work is used, such as publicity or privacy rights.
.RE
.RS 9
.RE
.IP "\fBNotice\fR" 9
.IX Item "Notice"
For any reuse or distribution, you must make clear to others the
license terms of this work.
.PP
A copy of the full license is included in the file as cc\-by\-sa.txt.
.SH "DEPENDENCIES"
.IX Header "DEPENDENCIES"
\&\s-1GNU\s0 \fBsql\fR uses Perl. If \fBmysql\fR is installed, MySQL dburls will
work. If \fBpsql\fR is installed, PostgreSQL dburls will work.  If
\&\fBsqlite\fR is installed, SQLite2 dburls will work.  If \fBsqlite3\fR is
installed, SQLite3 dburls will work. If \fBsqlplus\fR is installed,
Oracle dburls will work. If \fBrlwrap\fR is installed, \s-1GNU\s0 \fBsql\fR will
have a command history for Oracle.
.SH "FILES"
.IX Header "FILES"
~/.sql/aliases \- user's own aliases with DBURLs
.PP
/etc/sql/aliases \- common aliases with DBURLs
.SH "SEE ALSO"
.IX Header "SEE ALSO"
\&\fBmysql\fR(1), \fBpsql\fR(1), \fBrlwrap\fR(1), \fBsqlite\fR(1), \fBsqlite3\fR(1), \fBsqlplus\fR(1)