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 --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
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
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
251 GNU sql is part of GNU parallel. Report bugs to <bug-parallel@gnu.org>.
252
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
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
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
343 ~/.sql/aliases - user's own aliases with DBURLs
344
345 /etc/sql/aliases - common aliases with DBURLs
346
348 mysql(1), psql(1), rlwrap(1), sqlite(1), sqlite3(1), sqlplus(1)
349
350
351
35220180322 2018-03-22 SQL(1)