1SQL(1) parallel SQL(1)
2
3
4
6 sql - execute a command on a database determined by a dburl
7
9 sql [options] dburl [commands]
10
11 sql [options] dburl < commandfile
12
13 #!/usr/bin/sql --shebang [options] dburl
14
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
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
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
265 GNU sql is part of GNU parallel. Report bugs to <bug-parallel@gnu.org>.
266
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
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
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
355 ~/.sql/aliases - user's own aliases with DBURLs
356
357 /etc/sql/aliases - common aliases with DBURLs
358
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)