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       --csv    CSV output.
48
49       --db-size
50       --dbsize Size of database. Show the size of the database on disk. For
51                Oracle this requires access to read the table dba_data_files -
52                the user system has that.
53
54       --help
55       -h       Print a summary of the options to GNU sql and exit.
56
57       --html   HTML output. Turn on HTML tabular output.
58
59       --json
60       --pretty Pretty JSON output.
61
62       --list-databases
63       --listdbs
64       --show-databases
65       --showdbs
66                List the databases (table spaces) in the database.
67
68       --listproc
69       --proclist
70       --show-processlist
71                Show the list of running queries.
72
73       --list-tables
74       --show-tables
75       --table-list
76                List the tables in the database.
77
78       --noheaders
79       --no-headers
80       -n       Remove headers and footers and print only tuples. Bug in
81                Oracle: it still prints number of rows found.
82
83       -p pass-through
84                The string following -p will be given to the database
85                connection program as arguments. Multiple -p's will be joined
86                with space. Example: pass '-U' and the user name to the
87                program:
88
89                -p "-U scott" can also be written -p -U -p scott.
90
91       --precision <rfc3339|h|m|s|ms|u|ns>
92                Precision of timestamps.
93
94                Specifiy the format of the output timestamps: rfc3339, h, m,
95                s, ms, u or ns.
96
97       -r       Try 3 times. Short version of --retries 3.
98
99       --retries ntimes
100                Try ntimes times. If the client program returns with an error,
101                retry the command. Default is --retries 1.
102
103       --sep string
104       -s string
105                Field separator. Use string as separator between columns.
106
107       --skip-first-line
108                Do not use the first line of input (used by GNU sql itself
109                when called with --shebang).
110
111       --table-size
112       --tablesize
113                Size of tables. Show the size of the tables in the database.
114
115       --verbose
116       -v       Print which command is sent.
117
118       --version
119       -V       Print the version GNU sql and exit.
120
121       --shebang
122       -Y       GNU sql can be called as a shebang (#!) command as the first
123                line of a script. Like this:
124
125                  #!/usr/bin/sql -Y mysql:///
126
127                  SELECT * FROM foo;
128
129                For this to work --shebang or -Y must be set as the first
130                option.
131

DBURL

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

EXAMPLES

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

REPORTING BUGS

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

AUTHOR

268       When using GNU sql for a publication please cite:
269
270       O. Tange (2011): GNU SQL - A Command Line Tool for Accessing Different
271       Databases Using DBURLs, ;login: The USENIX Magazine, April 2011:29-32.
272
273       Copyright (C) 2008-2010 Ole Tange http://ole.tange.dk
274
275       Copyright (C) 2010-2023 Ole Tange, http://ole.tange.dk and Free
276       Software Foundation, Inc.
277

LICENSE

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

DEPENDENCIES

348       GNU sql uses Perl. If mysql is installed, MySQL dburls will work. If
349       psql is installed, PostgreSQL dburls will work.  If sqlite is
350       installed, SQLite2 dburls will work.  If sqlite3 is installed, SQLite3
351       dburls will work. If sqlplus is installed, Oracle dburls will work. If
352       rlwrap is installed, GNU sql will have a command history for Oracle.
353

FILES

355       ~/.sql/aliases - user's own aliases with DBURLs
356
357       /etc/sql/aliases - common aliases with DBURLs
358

SEE ALSO

360       mysql(1), psql(1), rlwrap(1), sqlite(1), sqlite3(1), sqlplus(1),
361       influx(1)
362
363
364
36520230822                          2023-08-24                            SQL(1)
Impressum