1SQL(1)                             parallel                             SQL(1)
2
3
4

NAME

6       sql - execute a command on a database determined by a dburl
7

SYNOPSIS

9       sql [options] dburl [commands]
10
11       sql [options] dburl < commandfile
12
13       #!/usr/bin/sql --shebang [options] dburl
14

DESCRIPTION

16       GNU sql aims to give a simple, unified interface for accessing
17       databases through all the different databases' command line clients. So
18       far the focus has been on giving a common way to specify login
19       information (protocol, username, password, hostname, and port number),
20       size (database and table size), and running queries.
21
22       The database is addressed using a DBURL. If commands are left out you
23       will get that database's interactive shell.
24
25       GNU sql is often used in combination with GNU parallel.
26
27       dburl    A DBURL has the following syntax: [sql:]vendor://
28                [[user][:password]@][host][:port]/[database][?sqlquery]
29
30                See the section DBURL below.
31
32       commands The SQL commands to run. Each argument will have a newline
33                appended.
34
35                Example: "SELECT * FROM foo;" "SELECT * FROM bar;"
36
37                If the arguments contain '\n' or '\x0a' this will be replaced
38                with a newline:
39
40                Example: "SELECT * FROM foo;\n SELECT * FROM bar;"
41
42                If no commands are given SQL is read from the keyboard or
43                STDIN.
44
45                Example: echo 'SELECT * FROM foo;' | sql mysql:///
46
47       --db-size
48       --dbsize Size of database. Show the size of the database on disk. For
49                Oracle this requires access to read the table dba_data_files -
50                the user system has that.
51
52       --help
53       -h       Print a summary of the options to GNU sql and exit.
54
55       --html   HTML output. Turn on HTML tabular output.
56
57       --show-processlist
58       --proclist
59       --listproc
60                Show the list of running queries.
61
62       --show-databases
63       --showdbs
64       --list-databases
65       --listdbs
66                List the databases (table spaces) in the database.
67
68       --show-tables
69       --list-tables
70       --table-list
71                List the tables in the database.
72
73       --noheaders
74       --no-headers
75       -n       Remove headers and footers and print only tuples. Bug in
76                Oracle: it still prints number of rows found.
77
78       -p pass-through
79                The string following -p will be given to the database
80                connection program as arguments. Multiple -p's will be joined
81                with space. Example: pass '-U' and the user name to the
82                program:
83
84                -p "-U scott" can also be written -p -U -p scott.
85
86       -r       Try 3 times. Short version of --retries 3.
87
88       --retries ntimes
89                Try ntimes times. If the client program returns with an error,
90                retry the command. Default is --retries 1.
91
92       --sep string
93       -s string
94                Field separator. Use string as separator between columns.
95
96       --skip-first-line
97                Do not use the first line of input (used by GNU sql itself
98                when called with --shebang).
99
100       --table-size
101       --tablesize
102                Size of tables. Show the size of the tables in the database.
103
104       --verbose
105       -v       Print which command is sent.
106
107       --version
108       -V       Print the version GNU sql and exit.
109
110       --shebang
111       -Y       GNU sql can be called as a shebang (#!) command as the first
112                line of a script. Like this:
113
114                  #!/usr/bin/sql -Y mysql:///
115
116                  SELECT * FROM foo;
117
118                For this to work --shebang or -Y must be set as the first
119                option.
120

DBURL

122       A DBURL has the following syntax: [sql:]vendor://
123       [[user][:password]@][host][:port]/[database][?sqlquery]
124
125       To quote special characters use %-encoding specified in
126       http://tools.ietf.org/html/rfc3986#section-2.1 (E.g. a password
127       containing '/' would contain '%2F').
128
129       Examples:
130        mysql://scott:tiger@my.example.com/mydb
131        sql:oracle://scott:tiger@ora.example.com/xe
132        postgresql://scott:tiger@pg.example.com/pgdb
133        pg:///
134        postgresqlssl://scott@pg.example.com:3333/pgdb
135        sql:sqlite2:////tmp/db.sqlite?SELECT * FROM foo;
136        sqlite3:///../db.sqlite3?SELECT%20*%20FROM%20foo;
137
138       Currently supported vendors: MySQL (mysql), MySQL with SSL (mysqls,
139       mysqlssl), Oracle (oracle, ora), PostgreSQL (postgresql, pg, pgsql,
140       postgres), PostgreSQL with SSL (postgresqlssl, pgs, pgsqlssl,
141       postgresssl, pgssl, postgresqls, pgsqls, postgress), SQLite2 (sqlite,
142       sqlite2), SQLite3 (sqlite3).
143
144       Aliases must start with ':' and are read from /etc/sql/aliases and
145       ~/.sql/aliases. The user's own ~/.sql/aliases should only be readable
146       by the user.
147
148       Example of aliases:
149
150        :myalias1 pg://scott:tiger@pg.example.com/pgdb
151        :myalias2 ora://scott:tiger@ora.example.com/xe
152        # Short form of mysql://`whoami`:nopassword@localhost:3306/`whoami`
153        :myalias3 mysql:///
154        # Short form of mysql://`whoami`:nopassword@localhost:33333/mydb
155        :myalias4 mysql://:33333/mydb
156        # Alias for an alias
157        :m      :myalias4
158        # the sortest alias possible
159        :       sqlite2:////tmp/db.sqlite
160        # Including an SQL query
161        :query  sqlite:////tmp/db.sqlite?SELECT * FROM foo;
162

EXAMPLES

164   Get an interactive prompt
165       The most basic use of GNU sql is to get an interactive prompt:
166
167       sql sql:oracle://scott:tiger@ora.example.com/xe
168
169       If you have setup an alias you can do:
170
171       sql :myora
172
173   Run a query
174       To run a query directly from the command line:
175
176       sql :myalias "SELECT * FROM foo;"
177
178       Oracle requires newlines after each statement. This can be done like
179       this:
180
181       sql :myora "SELECT * FROM foo;" "SELECT * FROM bar;"
182
183       Or this:
184
185       sql :myora "SELECT * FROM foo;\nSELECT * FROM bar;"
186
187   Copy a PostgreSQL database
188       To copy a PostgreSQL database use pg_dump to generate the dump and GNU
189       sql to import it:
190
191       pg_dump pg_database | sql pg://scott:tiger@pg.example.com/pgdb
192
193   Empty all tables in a MySQL database
194       Using GNU parallel it is easy to empty all tables without dropping
195       them:
196
197       sql -n mysql:/// 'show tables' | parallel sql mysql:/// DELETE FROM {};
198
199   Drop all tables in a PostgreSQL database
200       To drop all tables in a PostgreSQL database do:
201
202       sql -n pg:/// '\dt' | parallel --colsep '\|' -r sql pg:/// DROP TABLE
203       {2};
204
205   Run as a script
206       Instead of doing:
207
208       sql mysql:/// < sqlfile
209
210       you can combine the sqlfile with the DBURL to make a UNIX-script.
211       Create a script called demosql:
212
213       #!/usr/bin/sql -Y mysql:///
214
215       SELECT * FROM foo;
216
217       Then do:
218
219       chmod +x demosql; ./demosql
220
221   Use --colsep to process multiple columns
222       Use GNU parallel's --colsep to separate columns:
223
224       sql -s '\t' :myalias 'SELECT * FROM foo;' | parallel --colsep '\t'
225       do_stuff {4} {1}
226
227   Retry if the connection fails
228       If the access to the database fails occasionally --retries can help
229       make sure the query succeeds:
230
231       sql --retries 5 :myalias 'SELECT * FROM really_big_foo;'
232
233   Get info about the running database system
234       Show how big the database is:
235
236       sql --db-size :myalias
237
238       List the tables:
239
240       sql --list-tables :myalias
241
242       List the size of the tables:
243
244       sql --table-size :myalias
245
246       List the running processes:
247
248       sql --show-processlist :myalias
249

REPORTING BUGS

251       GNU sql is part of GNU parallel. Report bugs to <bug-parallel@gnu.org>.
252

AUTHOR

254       When using GNU sql for a publication please cite:
255
256       O. Tange (2011): GNU SQL - A Command Line Tool for Accessing Different
257       Databases Using DBURLs, ;login: The USENIX Magazine, April 2011:29-32.
258
259       Copyright (C) 2008,2009,2010 Ole Tange http://ole.tange.dk
260
261       Copyright (C) 2010,2011,2012,2013,2014,2015,2016,2017,2018 Ole Tange,
262       http://ole.tange.dk and Free Software Foundation, Inc.
263

LICENSE

265       Copyright (C) 2007,2008,2009,2010,2011 Free Software Foundation, Inc.
266
267       This program is free software; you can redistribute it and/or modify it
268       under the terms of the GNU General Public License as published by the
269       Free Software Foundation; either version 3 of the License, or at your
270       option any later version.
271
272       This program is distributed in the hope that it will be useful, but
273       WITHOUT ANY WARRANTY; without even the implied warranty of
274       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
275       General Public License for more details.
276
277       You should have received a copy of the GNU General Public License along
278       with this program.  If not, see <http://www.gnu.org/licenses/>.
279
280   Documentation license I
281       Permission is granted to copy, distribute and/or modify this
282       documentation under the terms of the GNU Free Documentation License,
283       Version 1.3 or any later version published by the Free Software
284       Foundation; with no Invariant Sections, with no Front-Cover Texts, and
285       with no Back-Cover Texts.  A copy of the license is included in the
286       file fdl.txt.
287
288   Documentation license II
289       You are free:
290
291       to Share to copy, distribute and transmit the work
292
293       to Remix to adapt the work
294
295       Under the following conditions:
296
297       Attribution
298                You must attribute the work in the manner specified by the
299                author or licensor (but not in any way that suggests that they
300                endorse you or your use of the work).
301
302       Share Alike
303                If you alter, transform, or build upon this work, you may
304                distribute the resulting work only under the same, similar or
305                a compatible license.
306
307       With the understanding that:
308
309       Waiver   Any of the above conditions can be waived if you get
310                permission from the copyright holder.
311
312       Public Domain
313                Where the work or any of its elements is in the public domain
314                under applicable law, that status is in no way affected by the
315                license.
316
317       Other Rights
318                In no way are any of the following rights affected by the
319                license:
320
321                ·        Your fair dealing or fair use rights, or other
322                         applicable copyright exceptions and limitations;
323
324                ·        The author's moral rights;
325
326                ·        Rights other persons may have either in the work
327                         itself or in how the work is used, such as publicity
328                         or privacy rights.
329
330       Notice   For any reuse or distribution, you must make clear to others
331                the license terms of this work.
332
333       A copy of the full license is included in the file as cc-by-sa.txt.
334

DEPENDENCIES

336       GNU sql uses Perl. If mysql is installed, MySQL dburls will work. If
337       psql is installed, PostgreSQL dburls will work.  If sqlite is
338       installed, SQLite2 dburls will work.  If sqlite3 is installed, SQLite3
339       dburls will work. If sqlplus is installed, Oracle dburls will work. If
340       rlwrap is installed, GNU sql will have a command history for Oracle.
341

FILES

343       ~/.sql/aliases - user's own aliases with DBURLs
344
345       /etc/sql/aliases - common aliases with DBURLs
346

SEE ALSO

348       mysql(1), psql(1), rlwrap(1), sqlite(1), sqlite3(1), sqlplus(1)
349
350
351
35220180322                          2018-03-22                            SQL(1)
Impressum