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 CSV output.
48 --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 --help
55 -h Print a summary of the options to GNU sql and exit.
56 --html HTML output. Turn on HTML tabular output.
58 --json
60 --pretty Pretty JSON output.
61 --list-databases
63 --listdbs
64 --show-databases
65 --showdbs
66 List the databases (table spaces) in the database.
67 --listproc
69 --proclist
70 --show-processlist
71 Show the list of running queries.
72 --list-tables
74 --show-tables
75 --table-list
76 List the tables in the database.
77 --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 -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 -p "-U scott" can also be written -p -U -p scott.
90 --precision <rfc3339|h|m|s|ms|u|ns>
92 Precision of timestamps.
93 Specifiy the format of the output timestamps: rfc3339, h, m,
95 s, ms, u or ns.
96 -r Try 3 times. Short version of --retries 3.
98 --retries ntimes
100 Try ntimes times. If the client program returns with an error,
101 retry the command. Default is --retries 1.
102 --sep string
104 -s string
105 Field separator. Use string as separator between columns.
106 --skip-first-line
108 Do not use the first line of input (used by GNU sql itself
109 when called with --shebang).
110 --table-size
112 --tablesize
113 Size of tables. Show the size of the tables in the database.
114 --verbose
116 -v Print which command is sent.
117 --version
119 -V Print the version GNU sql and exit.
120 --shebang
122 -Y GNU sql can be called as a shebang (#!) command as the first
123 line of a script. Like this:
124 #!/usr/bin/sql -Y mysql:///
126 SELECT * FROM foo;
128 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 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 Examples:
141 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 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 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 Example of aliases:
163 :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 sql sql:oracle://scott:tiger@ora.example.com/xe
182 If you have setup an alias you can do:
184 sql :myora
186 Run a query
188 To run a query directly from the command line:
189 sql :myalias "SELECT * FROM foo;"
191 Oracle requires newlines after each statement. This can be done like
193 this:
194 sql :myora "SELECT * FROM foo;" "SELECT * FROM bar;"
196 Or this:
198 sql :myora "SELECT * FROM foo;\nSELECT * FROM bar;"
200 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 pg_dump pg_database | sql pg://scott:tiger@pg.example.com/pgdb
206 Empty all tables in a MySQL database
208 Using GNU parallel it is easy to empty all tables without dropping
209 them:
210 sql -n mysql:/// 'show tables' | parallel sql mysql:/// DELETE FROM {};
212 Drop all tables in a PostgreSQL database
214 To drop all tables in a PostgreSQL database do:
215 sql -n pg:/// '\dt' | parallel --colsep '\|' -r sql pg:/// DROP TABLE
217 {2};
218 Run as a script
220 Instead of doing:
221 sql mysql:/// < sqlfile
223 you can combine the sqlfile with the DBURL to make a UNIX-script.
225 Create a script called demosql:
226 #!/usr/bin/sql -Y mysql:///
228 SELECT * FROM foo;
230 Then do:
232 chmod +x demosql; ./demosql
234 Use --colsep to process multiple columns
236 Use GNU parallel's --colsep to separate columns:
237 sql -s '\t' :myalias 'SELECT * FROM foo;' | parallel --colsep '\t'
239 do_stuff {4} {1}
240 Retry if the connection fails
242 If the access to the database fails occasionally --retries can help
243 make sure the query succeeds:
244 sql --retries 5 :myalias 'SELECT * FROM really_big_foo;'
246 Get info about the running database system
248 Show how big the database is:
249 sql --db-size :myalias
251 List the tables:
253 sql --list-tables :myalias
255 List the size of the tables:
257 sql --table-size :myalias
259 List the running processes:
261 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 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 Copyright (C) 2008-2010 Ole Tange http://ole.tange.dk
274 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 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 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 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 Documentation license II
301 You are free:
302 to Share to copy, distribute and transmit the work
304 to Remix to adapt the work
306 Under the following conditions:
308 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 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 With the understanding that:
320 Waiver Any of the above conditions can be waived if you get
322 permission from the copyright holder.
323 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 Other Rights
330 In no way are any of the following rights affected by the
331 license:
332 • Your fair dealing or fair use rights, or other
334 applicable copyright exceptions and limitations;
335 • The author's moral rights;
337 • 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 Notice For any reuse or distribution, you must make clear to others
343 the license terms of this work.
344 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 /etc/sql/aliases - common aliases with DBURLs
358
360 mysql(1), psql(1), rlwrap(1), sqlite(1), sqlite3(1), sqlplus(1),
361 influx(1)
36220230822 2023-08-24 SQL(1)