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 --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 --help
53 -h Print a summary of the options to GNU sql and exit.
54 --html HTML output. Turn on HTML tabular output.
56 --show-processlist
58 --proclist
59 --listproc
60 Show the list of running queries.
61 --show-databases
63 --showdbs
64 --list-databases
65 --listdbs
66 List the databases (table spaces) in the database.
67 --show-tables
69 --list-tables
70 --table-list
71 List the tables in the database.
72 --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 -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 -p "-U scott" can also be written -p -U -p scott.
85 -r Try 3 times. Short version of --retries 3.
87 --retries ntimes
89 Try ntimes times. If the client program returns with an error,
90 retry the command. Default is --retries 1.
91 --sep string
93 -s string
94 Field separator. Use string as separator between columns.
95 --skip-first-line
97 Do not use the first line of input (used by GNU sql itself
98 when called with --shebang).
99 --table-size
101 --tablesize
102 Size of tables. Show the size of the tables in the database.
103 --verbose
105 -v Print which command is sent.
106 --version
108 -V Print the version GNU sql and exit.
109 --shebang
111 -Y GNU sql can be called as a shebang (#!) command as the first
112 line of a script. Like this:
113 #!/usr/bin/sql -Y mysql:///
115 SELECT * FROM foo;
117 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 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 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 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 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 Example of aliases:
149 :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 sql sql:oracle://scott:tiger@ora.example.com/xe
168 If you have setup an alias you can do:
170 sql :myora
172 Run a query
174 To run a query directly from the command line:
175 sql :myalias "SELECT * FROM foo;"
177 Oracle requires newlines after each statement. This can be done like
179 this:
180 sql :myora "SELECT * FROM foo;" "SELECT * FROM bar;"
182 Or this:
184 sql :myora "SELECT * FROM foo;\nSELECT * FROM bar;"
186 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 pg_dump pg_database | sql pg://scott:tiger@pg.example.com/pgdb
192 Empty all tables in a MySQL database
194 Using GNU parallel it is easy to empty all tables without dropping
195 them:
196 sql -n mysql:/// 'show tables' | parallel sql mysql:/// DELETE FROM {};
198 Drop all tables in a PostgreSQL database
200 To drop all tables in a PostgreSQL database do:
201 sql -n pg:/// '\dt' | parallel --colsep '\|' -r sql pg:/// DROP TABLE
203 {2};
204 Run as a script
206 Instead of doing:
207 sql mysql:/// < sqlfile
209 you can combine the sqlfile with the DBURL to make a UNIX-script.
211 Create a script called demosql:
212 #!/usr/bin/sql -Y mysql:///
214 SELECT * FROM foo;
216 Then do:
218 chmod +x demosql; ./demosql
220 Use --colsep to process multiple columns
222 Use GNU parallel's --colsep to separate columns:
223 sql -s '\t' :myalias 'SELECT * FROM foo;' | parallel --colsep '\t'
225 do_stuff {4} {1}
226 Retry if the connection fails
228 If the access to the database fails occasionally --retries can help
229 make sure the query succeeds:
230 sql --retries 5 :myalias 'SELECT * FROM really_big_foo;'
232 Get info about the running database system
234 Show how big the database is:
235 sql --db-size :myalias
237 List the tables:
239 sql --list-tables :myalias
241 List the size of the tables:
243 sql --table-size :myalias
245 List the running processes:
247 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 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 Copyright (C) 2008-2010 Ole Tange http://ole.tange.dk
260 Copyright (C) 2010-2020 Ole Tange, http://ole.tange.dk and Free
262 Software Foundation, Inc.
263
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 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 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 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 Documentation license II
287 You are free:
288 to Share to copy, distribute and transmit the work
290 to Remix to adapt the work
292 Under the following conditions:
294 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 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 With the understanding that:
306 Waiver Any of the above conditions can be waived if you get
308 permission from the copyright holder.
309 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 Other Rights
316 In no way are any of the following rights affected by the
317 license:
318 • Your fair dealing or fair use rights, or other
320 applicable copyright exceptions and limitations;
321 • The author's moral rights;
323 • 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 Notice For any reuse or distribution, you must make clear to others
329 the license terms of this work.
330 A copy of the full license is included in the file as cc-by-sa.txt.
332
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
341 ~/.sql/aliases - user's own aliases with DBURLs
342 /etc/sql/aliases - common aliases with DBURLs
344
346 mysql(1), psql(1), rlwrap(1), sqlite(1), sqlite3(1), sqlplus(1)
34720201222 2020-12-21 SQL(1)