1SQL(1) parallel SQL(1)
2
6 sql - execute a command on a database determined by a dburl
7
9 sql [options] dburl [commands]
10 sql [options] dburl < commandfile
12 #!/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 The database is addressed using a DBURL. If commands are left out you
23 will get that database's interactive shell.
24 GNU sql is often used in combination with GNU parallel.
26 dburl A DBURL has the following syntax: [sql:]vendor://
28 [[user][:password]@][host][:port]/[database][?sqlquery]
29 See the section DBURL below.
31 commands The SQL commands to run. Each argument will have a newline
33 appended.
34 Example: "SELECT * FROM foo;" "SELECT * FROM bar;"
36 If the arguments contain '\n' or '\x0a' this will be replaced
38 with a newline:
39 Example: "SELECT * FROM foo;\n SELECT * FROM bar;"
41 If no commands are given SQL is read from the keyboard or
43 STDIN.
44 Example: echo 'SELECT * FROM foo;' | sql mysql:///
46 --csv (beta testing)
48 CSV output.
49 --db-size
51 --dbsize Size of database. Show the size of the database on disk. For
52 Oracle this requires access to read the table dba_data_files -
53 the user system has that.
54 --help
56 -h Print a summary of the options to GNU sql and exit.
57 --html HTML output. Turn on HTML tabular output.
59 --json (beta testing)
61 --pretty (beta testing)
62 Pretty JSON output.
63 --list-databases
65 --listdbs
66 --show-databases
67 --showdbs
68 List the databases (table spaces) in the database.
69 --listproc
71 --proclist
72 --show-processlist
73 Show the list of running queries.
74 --list-tables
76 --show-tables
77 --table-list
78 List the tables in the database.
79 --noheaders
81 --no-headers
82 -n Remove headers and footers and print only tuples. Bug in
83 Oracle: it still prints number of rows found.
84 -p pass-through
86 The string following -p will be given to the database
87 connection program as arguments. Multiple -p's will be joined
88 with space. Example: pass '-U' and the user name to the
89 program:
90 -p "-U scott" can also be written -p -U -p scott.
92 --precision <rfc3339|h|m|s|ms|u|ns>
94 Precision of timestamps.
95 Specifiy the format of the output timestamps: rfc3339, h, m,
97 s, ms, u or ns.
98 -r Try 3 times. Short version of --retries 3.
100 --retries ntimes
102 Try ntimes times. If the client program returns with an error,
103 retry the command. Default is --retries 1.
104 --sep string
106 -s string
107 Field separator. Use string as separator between columns.
108 --skip-first-line
110 Do not use the first line of input (used by GNU sql itself
111 when called with --shebang).
112 --table-size
114 --tablesize
115 Size of tables. Show the size of the tables in the database.
116 --verbose
118 -v Print which command is sent.
119 --version
121 -V Print the version GNU sql and exit.
122 --shebang
124 -Y GNU sql can be called as a shebang (#!) command as the first
125 line of a script. Like this:
126 #!/usr/bin/sql -Y mysql:///
128 SELECT * FROM foo;
130 For this to work --shebang or -Y must be set as the first
132 option.
133
135 A DBURL has the following syntax: [sql:]vendor://
136 [[user][:password]@][host][:port]/[database][?sqlquery]
137 To quote special characters use %-encoding specified in
139 http://tools.ietf.org/html/rfc3986#section-2.1 (E.g. a password
140 containing '/' would contain '%2F').
141 Examples:
143 mysql://scott:tiger@my.example.com/mydb
145 influxdb://scott:tiger@influxdb.example.com/foo
146 sql:oracle://scott:tiger@ora.example.com/xe
147 postgresql://scott:tiger@pg.example.com/pgdb
148 pg:///
149 postgresqlssl://scott@pg.example.com:3333/pgdb
150 sql:sqlite2:////tmp/db.sqlite?SELECT * FROM foo;
151 sqlite3:///../db.sqlite3?SELECT%20*%20FROM%20foo;
152 Currently supported vendors: MySQL (mysql), MySQL with SSL (mysqls,
154 mysqlssl), Oracle (oracle, ora), PostgreSQL (postgresql, pg, pgsql,
155 postgres), PostgreSQL with SSL (postgresqlssl, pgs, pgsqlssl,
156 postgresssl, pgssl, postgresqls, pgsqls, postgress), SQLite2 (sqlite,
157 sqlite2), SQLite3 (sqlite3), InfluxDB 1.x (influx, influxdb), InfluxDB
158 with SSL (influxdbssl, influxdbs, influxs, influxssl)
159 Aliases must start with ':' and are read from /etc/sql/aliases and
161 ~/.sql/aliases. The user's own ~/.sql/aliases should only be readable
162 by the user.
163 Example of aliases:
165 :myalias1 pg://scott:tiger@pg.example.com/pgdb
167 :myalias2 ora://scott:tiger@ora.example.com/xe
168 # Short form of mysql://`whoami`:nopassword@localhost:3306/`whoami`
169 :myalias3 mysql:///
170 # Short form of mysql://`whoami`:nopassword@localhost:33333/mydb
171 :myalias4 mysql://:33333/mydb
172 # Alias for an alias
173 :m :myalias4
174 # the sortest alias possible
175 : sqlite2:////tmp/db.sqlite
176 # Including an SQL query
177 :query sqlite:////tmp/db.sqlite?SELECT * FROM foo;
178
180 Get an interactive prompt
181 The most basic use of GNU sql is to get an interactive prompt:
182 sql sql:oracle://scott:tiger@ora.example.com/xe
184 If you have setup an alias you can do:
186 sql :myora
188 Run a query
190 To run a query directly from the command line:
191 sql :myalias "SELECT * FROM foo;"
193 Oracle requires newlines after each statement. This can be done like
195 this:
196 sql :myora "SELECT * FROM foo;" "SELECT * FROM bar;"
198 Or this:
200 sql :myora "SELECT * FROM foo;\nSELECT * FROM bar;"
202 Copy a PostgreSQL database
204 To copy a PostgreSQL database use pg_dump to generate the dump and GNU
205 sql to import it:
206 pg_dump pg_database | sql pg://scott:tiger@pg.example.com/pgdb
208 Empty all tables in a MySQL database
210 Using GNU parallel it is easy to empty all tables without dropping
211 them:
212 sql -n mysql:/// 'show tables' | parallel sql mysql:/// DELETE FROM {};
214 Drop all tables in a PostgreSQL database
216 To drop all tables in a PostgreSQL database do:
217 sql -n pg:/// '\dt' | parallel --colsep '\|' -r sql pg:/// DROP TABLE
219 {2};
220 Run as a script
222 Instead of doing:
223 sql mysql:/// < sqlfile
225 you can combine the sqlfile with the DBURL to make a UNIX-script.
227 Create a script called demosql:
228 #!/usr/bin/sql -Y mysql:///
230 SELECT * FROM foo;
232 Then do:
234 chmod +x demosql; ./demosql
236 Use --colsep to process multiple columns
238 Use GNU parallel's --colsep to separate columns:
239 sql -s '\t' :myalias 'SELECT * FROM foo;' | parallel --colsep '\t'
241 do_stuff {4} {1}
242 Retry if the connection fails
244 If the access to the database fails occasionally --retries can help
245 make sure the query succeeds:
246 sql --retries 5 :myalias 'SELECT * FROM really_big_foo;'
248 Get info about the running database system
250 Show how big the database is:
251 sql --db-size :myalias
253 List the tables:
255 sql --list-tables :myalias
257 List the size of the tables:
259 sql --table-size :myalias
261 List the running processes:
263 sql --show-processlist :myalias
265
267 GNU sql is part of GNU parallel. Report bugs to <bug-parallel@gnu.org>.
268
270 When using GNU sql for a publication please cite:
271 O. Tange (2011): GNU SQL - A Command Line Tool for Accessing Different
273 Databases Using DBURLs, ;login: The USENIX Magazine, April 2011:29-32.
274 Copyright (C) 2008-2010 Ole Tange http://ole.tange.dk
276 Copyright (C) 2010-2022 Ole Tange, http://ole.tange.dk and Free
278 Software Foundation, Inc.
279
281 This program is free software; you can redistribute it and/or modify it
282 under the terms of the GNU General Public License as published by the
283 Free Software Foundation; either version 3 of the License, or at your
284 option any later version.
285 This program is distributed in the hope that it will be useful, but
287 WITHOUT ANY WARRANTY; without even the implied warranty of
288 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
289 General Public License for more details.
290 You should have received a copy of the GNU General Public License along
292 with this program. If not, see <http://www.gnu.org/licenses/>.
293 Documentation license I
295 Permission is granted to copy, distribute and/or modify this
296 documentation under the terms of the GNU Free Documentation License,
297 Version 1.3 or any later version published by the Free Software
298 Foundation; with no Invariant Sections, with no Front-Cover Texts, and
299 with no Back-Cover Texts. A copy of the license is included in the
300 file LICENSES/GFDL-1.3-or-later.txt.
301 Documentation license II
303 You are free:
304 to Share to copy, distribute and transmit the work
306 to Remix to adapt the work
308 Under the following conditions:
310 Attribution
312 You must attribute the work in the manner specified by the
313 author or licensor (but not in any way that suggests that they
314 endorse you or your use of the work).
315 Share Alike
317 If you alter, transform, or build upon this work, you may
318 distribute the resulting work only under the same, similar or
319 a compatible license.
320 With the understanding that:
322 Waiver Any of the above conditions can be waived if you get
324 permission from the copyright holder.
325 Public Domain
327 Where the work or any of its elements is in the public domain
328 under applicable law, that status is in no way affected by the
329 license.
330 Other Rights
332 In no way are any of the following rights affected by the
333 license:
334 • Your fair dealing or fair use rights, or other
336 applicable copyright exceptions and limitations;
337 • The author's moral rights;
339 • Rights other persons may have either in the work
341 itself or in how the work is used, such as publicity
342 or privacy rights.
343 Notice For any reuse or distribution, you must make clear to others
345 the license terms of this work.
346 A copy of the full license is included in the file as cc-by-sa.txt.
348
350 GNU sql uses Perl. If mysql is installed, MySQL dburls will work. If
351 psql is installed, PostgreSQL dburls will work. If sqlite is
352 installed, SQLite2 dburls will work. If sqlite3 is installed, SQLite3
353 dburls will work. If sqlplus is installed, Oracle dburls will work. If
354 rlwrap is installed, GNU sql will have a command history for Oracle.
355
357 ~/.sql/aliases - user's own aliases with DBURLs
358 /etc/sql/aliases - common aliases with DBURLs
360
362 mysql(1), psql(1), rlwrap(1), sqlite(1), sqlite3(1), sqlplus(1),
363 influx(1)
36420221122 2022-11-22 SQL(1)