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-2010 Ole Tange http://ole.tange.dk
260
261       Copyright (C) 2010-2020 Ole Tange, http://ole.tange.dk and Free
262       Software Foundation, Inc.
263

LICENSE

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

DEPENDENCIES

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

FILES

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

SEE ALSO

346       mysql(1), psql(1), rlwrap(1), sqlite(1), sqlite3(1), sqlplus(1)
347
348
349
35020200722                          2020-07-22                            SQL(1)
Impressum