3 # Copyright (C) 2008-2010 Ole Tange, http://ole.tange.dk
5 # Copyright (C) 2010-2023 Ole Tange, http://ole.tange.dk and
6 # Free Software Foundation, Inc.
8 # This program is free software; you can redistribute it and/or modify
9 # it under the terms of the GNU General Public License as published by
10 # the Free Software Foundation; either version 3 of the License, or
11 # (at your option) any later version.
13 # This program is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 # General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with this program; if not, see <http://www.gnu.org/licenses/>
20 # or write to the Free Software Foundation, Inc., 51 Franklin St,
21 # Fifth Floor, Boston, MA 02110-1301 USA
23 # SPDX-FileCopyrightText: 2008-2023 Ole Tange, http://ole.tange.dk and Free Software and Foundation, Inc.
24 # SPDX-License-Identifier: GPL-3.0-or-later
25 # SPDX-License-Identifier: GFDL-1.3-or-later
29 sql - execute a command on a database determined by a dburl
33 B<sql> [options] I<dburl> [I<commands>]
35 B<sql> [options] I<dburl> < commandfile
37 B<#!/usr/bin/sql> B<--shebang> [options] I<dburl>
41 GNU B<sql> aims to give a simple, unified interface for accessing
42 databases through all the different databases' command line
43 clients. So far the focus has been on giving a common way to specify
44 login information (protocol, username, password, hostname, and port
45 number), size (database and table size), and running queries.
47 The database is addressed using a DBURL. If I<commands> are left out
48 you will get that database's interactive shell.
50 GNU B<sql> is often used in combination with GNU B<parallel>.
56 A DBURL has the following syntax:
58 [[user][:password]@][host][:port]/[database][?sqlquery]
60 See the section DBURL below.
64 The SQL commands to run. Each argument will have a newline
67 Example: "SELECT * FROM foo;" "SELECT * FROM bar;"
69 If the arguments contain '\n' or '\x0a' this will be replaced with a
72 Example: "SELECT * FROM foo;\n SELECT * FROM bar;"
74 If no commands are given SQL is read from the keyboard or STDIN.
76 Example: echo 'SELECT * FROM foo;' | sql mysql:///
88 Size of database. Show the size of the database on disk. For Oracle
89 this requires access to read the table I<dba_data_files> - the user
97 Print a summary of the options to GNU B<sql> and exit.
102 HTML output. Turn on HTML tabular output.
112 =item B<--list-databases>
116 =item B<--show-databases>
120 List the databases (table spaces) in the database.
127 =item B<--show-processlist>
129 Show the list of running queries.
132 =item B<--list-tables>
134 =item B<--show-tables>
136 =item B<--table-list>
138 List the tables in the database.
143 =item B<--no-headers>
147 Remove headers and footers and print only tuples. Bug in Oracle: it
148 still prints number of rows found.
151 =item B<-p> I<pass-through>
153 The string following -p will be given to the database connection
154 program as arguments. Multiple -p's will be joined with
155 space. Example: pass '-U' and the user name to the program:
157 I<-p "-U scott"> can also be written I<-p -U -p scott>.
160 =item B<--precision> <I<rfc3339|h|m|s|ms|u|ns>>
162 Precision of timestamps.
164 Specifiy the format of the output timestamps: rfc3339, h, m, s, ms, u
170 Try 3 times. Short version of I<--retries 3>.
173 =item B<--retries> I<ntimes>
175 Try I<ntimes> times. If the client program returns with an error,
176 retry the command. Default is I<--retries 1>.
179 =item B<--sep> I<string>
181 =item B<-s> I<string>
183 Field separator. Use I<string> as separator between columns.
186 =item B<--skip-first-line>
188 Do not use the first line of input (used by GNU B<sql> itself
189 when called with B<--shebang>).
192 =item B<--table-size>
196 Size of tables. Show the size of the tables in the database.
203 Print which command is sent.
210 Print the version GNU B<sql> and exit.
217 GNU B<sql> can be called as a shebang (#!) command as the first line of a script. Like this:
219 #!/usr/bin/sql -Y mysql:///
223 For this to work B<--shebang> or B<-Y> must be set as the first option.
229 A DBURL has the following syntax:
231 [[user][:password]@][host][:port]/[database][?sqlquery]
233 To quote special characters use %-encoding specified in
234 http://tools.ietf.org/html/rfc3986#section-2.1 (E.g. a password
235 containing '/' would contain '%2F').
239 mysql://scott:tiger@my.example.com/mydb
240 influxdb://scott:tiger@influxdb.example.com/foo
241 sql:oracle://scott:tiger@ora.example.com/xe
242 postgresql://scott:tiger@pg.example.com/pgdb
244 postgresqlssl://scott@pg.example.com:3333/pgdb
245 sql:sqlite2:////tmp/db.sqlite?SELECT * FROM foo;
246 sqlite3:///../db.sqlite3?SELECT%20*%20FROM%20foo;
248 Currently supported vendors: MySQL (mysql), MySQL with SSL (mysqls,
249 mysqlssl), Oracle (oracle, ora), PostgreSQL (postgresql, pg, pgsql,
250 postgres), PostgreSQL with SSL (postgresqlssl, pgs, pgsqlssl,
251 postgresssl, pgssl, postgresqls, pgsqls, postgress), SQLite2 (sqlite,
252 sqlite2), SQLite3 (sqlite3), InfluxDB 1.x (influx, influxdb), InfluxDB
253 with SSL (influxdbssl, influxdbs, influxs, influxssl)
255 Aliases must start with ':' and are read from
256 /etc/sql/aliases and ~/.sql/aliases. The user's own
257 ~/.sql/aliases should only be readable by the user.
261 :myalias1 pg://scott:tiger@pg.example.com/pgdb
262 :myalias2 ora://scott:tiger@ora.example.com/xe
263 # Short form of mysql://`whoami`:nopassword@localhost:3306/`whoami`
265 # Short form of mysql://`whoami`:nopassword@localhost:33333/mydb
266 :myalias4 mysql://:33333/mydb
269 # the sortest alias possible
270 : sqlite2:////tmp/db.sqlite
271 # Including an SQL query
272 :query sqlite:////tmp/db.sqlite?SELECT * FROM foo;
276 =head2 Get an interactive prompt
278 The most basic use of GNU B<sql> is to get an interactive prompt:
280 B<sql sql:oracle://scott:tiger@ora.example.com/xe>
282 If you have setup an alias you can do:
289 To run a query directly from the command line:
291 B<sql :myalias "SELECT * FROM foo;">
293 Oracle requires newlines after each statement. This can be done like
296 B<sql :myora "SELECT * FROM foo;" "SELECT * FROM bar;">
300 B<sql :myora "SELECT * FROM foo;\nSELECT * FROM bar;">
303 =head2 Copy a PostgreSQL database
305 To copy a PostgreSQL database use pg_dump to generate the dump and GNU
308 B<pg_dump pg_database | sql pg://scott:tiger@pg.example.com/pgdb>
311 =head2 Empty all tables in a MySQL database
313 Using GNU B<parallel> it is easy to empty all tables without dropping them:
315 B<sql -n mysql:/// 'show tables' | parallel sql mysql:/// DELETE FROM {};>
318 =head2 Drop all tables in a PostgreSQL database
320 To drop all tables in a PostgreSQL database do:
322 B<sql -n pg:/// '\dt' | parallel --colsep '\|' -r sql pg:/// DROP TABLE {2};>
325 =head2 Run as a script
329 B<sql mysql:/// < sqlfile>
331 you can combine the sqlfile with the DBURL to make a
332 UNIX-script. Create a script called I<demosql>:
334 B<#!/usr/bin/sql -Y mysql:///>
336 B<SELECT * FROM foo;>
340 B<chmod +x demosql; ./demosql>
343 =head2 Use --colsep to process multiple columns
345 Use GNU B<parallel>'s B<--colsep> to separate columns:
347 B<sql -s '\t' :myalias 'SELECT * FROM foo;' | parallel --colsep '\t' do_stuff {4} {1}>
350 =head2 Retry if the connection fails
352 If the access to the database fails occasionally B<--retries> can help
353 make sure the query succeeds:
355 B<sql --retries 5 :myalias 'SELECT * FROM really_big_foo;'>
358 =head2 Get info about the running database system
360 Show how big the database is:
362 B<sql --db-size :myalias>
366 B<sql --list-tables :myalias>
368 List the size of the tables:
370 B<sql --table-size :myalias>
372 List the running processes:
374 B<sql --show-processlist :myalias>
377 =head1 REPORTING BUGS
379 GNU B<sql> is part of GNU B<parallel>. Report bugs to <bug-parallel@gnu.org>.
384 When using GNU B<sql> for a publication please cite:
386 O. Tange (2011): GNU SQL - A Command Line Tool for Accessing Different
387 Databases Using DBURLs, ;login: The USENIX Magazine, April 2011:29-32.
389 Copyright (C) 2008-2010 Ole Tange http://ole.tange.dk
391 Copyright (C) 2010-2023 Ole Tange, http://ole.tange.dk and Free
392 Software Foundation, Inc.
396 This program is free software; you can redistribute it and/or modify
397 it under the terms of the GNU General Public License as published by
398 the Free Software Foundation; either version 3 of the License, or
399 at your option any later version.
401 This program is distributed in the hope that it will be useful,
402 but WITHOUT ANY WARRANTY; without even the implied warranty of
403 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
404 GNU General Public License for more details.
406 You should have received a copy of the GNU General Public License
407 along with this program. If not, see <http://www.gnu.org/licenses/>.
409 =head2 Documentation license I
411 Permission is granted to copy, distribute and/or modify this
412 documentation under the terms of the GNU Free Documentation License,
413 Version 1.3 or any later version published by the Free Software
414 Foundation; with no Invariant Sections, with no Front-Cover Texts, and
415 with no Back-Cover Texts. A copy of the license is included in the
416 file LICENSES/GFDL-1.3-or-later.txt.
419 =head2 Documentation license II
427 to copy, distribute and transmit the work
435 Under the following conditions:
441 You must attribute the work in the manner specified by the author or
442 licensor (but not in any way that suggests that they endorse you or
443 your use of the work).
447 If you alter, transform, or build upon this work, you may distribute
448 the resulting work only under the same, similar or a compatible
453 With the understanding that:
459 Any of the above conditions can be waived if you get permission from
460 the copyright holder.
462 =item B<Public Domain>
464 Where the work or any of its elements is in the public domain under
465 applicable law, that status is in no way affected by the license.
467 =item B<Other Rights>
469 In no way are any of the following rights affected by the license:
475 Your fair dealing or fair use rights, or other applicable
476 copyright exceptions and limitations;
480 The author's moral rights;
484 Rights other persons may have either in the work itself or in
485 how the work is used, such as publicity or privacy rights.
491 For any reuse or distribution, you must make clear to others the
492 license terms of this work.
496 A copy of the full license is included in the file as cc-by-sa.txt.
500 GNU B<sql> uses Perl. If B<mysql> is installed, MySQL dburls will
501 work. If B<psql> is installed, PostgreSQL dburls will work. If
502 B<sqlite> is installed, SQLite2 dburls will work. If B<sqlite3> is
503 installed, SQLite3 dburls will work. If B<sqlplus> is installed,
504 Oracle dburls will work. If B<rlwrap> is installed, GNU B<sql> will
505 have a command history for Oracle.
510 ~/.sql/aliases - user's own aliases with DBURLs
512 /etc/sql/aliases - common aliases with DBURLs
517 B<mysql>(1), B<psql>(1), B<rlwrap>(1), B<sqlite>(1), B<sqlite3>(1),
518 B<sqlplus>(1), B<influx>(1)
524 use File
::Temp qw
/tempfile tempdir/;
527 # Quote for other shells (Bourne compatibles)
529 # $string = string to be quoted
531 # $shell_quoted = string quoted as needed by the shell
533 if($s =~ /[^-_.+a-z0-9\/]/i
) {
534 $s =~ s/'/'"'"'/g; # "-quote single quotes
535 $s = "'$s'"; # '-quote entire string
536 $s =~ s/^''//; # Remove unneeded '' at ends
537 $s =~ s/''$//; # (faster than s/^''|''$//g)
549 my $pass_through_options = (defined $::opt_p
) ?
join(" ",@
{$::opt_p
}) : "";
550 my $dburl_or_alias = shift;
551 if (not defined $dburl_or_alias) { Usage
("No DBURL given"); exit -1; }
552 my %dburl = parse_dburl
(get_alias
($dburl_or_alias));
554 my $interactive_command;
557 my $database_driver = database_driver_alias
($dburl{'databasedriver'});
558 if($database_driver eq "mysql" or
559 $database_driver eq "mysqlssl") {
560 ($batch_command,$interactive_command) =
561 mysql_commands
($database_driver,%dburl);
562 } elsif($database_driver eq "postgresql" or
563 $database_driver eq "postgresqlssl") {
564 ($batch_command,$interactive_command) =
565 postgresql_commands
($database_driver,%dburl);
566 } elsif($database_driver eq "oracle") {
567 ($batch_command,$interactive_command) =
568 oracle_commands
($database_driver,%dburl);
569 } elsif($database_driver eq "sqlite" or
570 $database_driver eq "sqlite3") {
571 ($batch_command,$interactive_command) =
572 sqlite_commands
($database_driver,%dburl);
573 } elsif($database_driver eq "influx" or
574 $database_driver eq "influxssl") {
575 ($batch_command,$interactive_command) =
576 influx_commands
($database_driver,%dburl);
581 $retries ||= defined $::opt_retries ?
$::opt_retries
: undef;
582 $retries ||= defined $::opt_retry ?
$::opt_retry
* 3 : undef;
585 if(defined $::opt_processlist
) {
586 unshift @ARGV, processlist
($database_driver,%dburl);
589 if(defined $::opt_tablelist
) {
590 unshift @ARGV, tablelist
($database_driver,%dburl);
593 if(defined $::opt_dblist
) {
594 unshift @ARGV, dblist
($database_driver,%dburl);
597 if(defined $::opt_dbsize
) {
598 unshift @ARGV, dbsize
($database_driver,%dburl);
601 if(defined $::opt_tablesize
) {
602 unshift @ARGV, tablesize
($database_driver,%dburl);
606 if($dburl{'query'}) {
608 ($fh,$queryfile) = tempfile
(SUFFIX
=> ".sql");
609 print $fh $dburl{'query'},"\n";
611 my $qfile = shell_quote
($queryfile);
612 $batch_command = "(cat $qfile;rm $qfile; cat) | $batch_command";
619 # SQL Commands given as arguments:
621 $::opt_debug
and print "[ | $batch_command]\n";
622 $::opt_verbose
and print "[ | $batch_command]\n";
623 if($database_driver eq "influx" or $database_driver eq "influxssl") {
624 # Influx currently cannot read commands from stdin
628 $::opt_debug
and print "[$batch_command -execute $_]\n";
629 system("$batch_command -execute ".shell_quote
($_));
632 open(M
,"| $batch_command") ||
633 die("mysql/psql/sqlplus/influx not in path");
642 if (is_stdin_terminal
()) {
644 $::opt_debug
and print "[$interactive_command]\n";
645 $::opt_verbose
and print "[$interactive_command]\n";
646 system("$interactive_command");
648 # Let the command read from stdin
649 $::opt_debug
and print "[$batch_command]\n";
650 $::opt_verbose
and print "[$batch_command]\n";
651 if($database_driver eq "influx" or $database_driver eq "influxssl") {
652 # Influx currently cannot read commands from stdin
656 $::opt_debug
and print "[$batch_command -execute $_]\n";
657 system("$batch_command -execute ".shell_quote
($_));
660 system("$batch_command");
665 } while (--$retries and $err);
667 $queryfile and unlink $queryfile;
669 $Global::Initfile
&& unlink $Global::Initfile
;
673 $Global::version
= 20230522;
674 $Global::progname
= 'sql';
676 # This must be done first as this may exec myself
677 if(defined $ARGV[0] and ($ARGV[0]=~/^-Y/ or $ARGV[0]=~/^--shebang /)) {
678 # Program is called from #! line in script
679 $ARGV[0]=~s/^-Y //; # remove -Y if on its own
680 $ARGV[0]=~s/^-Y/-/; # remove -Y if bundled with other options
681 $ARGV[0]=~s/^--shebang //; # remove --shebang if it is set
682 my $argfile = pop @ARGV;
683 # exec myself to split @ARGV into separate fields
684 exec "$0 --skip-first-line < $argfile @ARGV";
686 Getopt
::Long
::Configure
("bundling","require_order");
687 GetOptions
("passthrough|p=s@" => \
$::opt_p
,
688 "sep|s=s" => \
$::opt_s
,
689 "html" => \
$::opt_html
,
690 "show-processlist|proclist|listproc|showqueries|show-queries"
691 => \
$::opt_processlist
,
692 "show-tables|showtables|listtables|list-tables|".
693 "tablelist|table-list|show-measurements|showmeasurements|".
694 "list-measurements|listmeasurements" => \
$::opt_tablelist
,
696 "listdb|listdbs|list-db|list-dbs|list-database|".
697 "list-databases|listdatabases|listdatabase|showdb|".
698 "showdbs|show-db|show-dbs|show-database|show-databases|".
699 "showdatabases|showdatabase" => \
$::opt_dblist
,
700 "db-size|dbsize" => \
$::opt_dbsize
,
701 "table-size|tablesize" => \
$::opt_tablesize
,
702 "json|j" => \
$::opt_json
,
703 "pretty" => \
$::opt_pretty
,
704 "csv" => \
$::opt_csv
,
705 "precision=s" => \
$::opt_precision
,
706 "noheaders|no-headers|n" => \
$::opt_n
,
707 "r" => \
$::opt_retry
,
708 "retries=s" => \
$::opt_retries
,
709 "debug|D" => \
$::opt_debug
,
710 # Shebang #!/usr/bin/parallel -Yotheroptions
711 "Y|shebang" => \
$::opt_shebang
,
712 "skip-first-line" => \
$::opt_skip_first_line
,
714 "help|h" => \
$::opt_help
,
715 "version|V" => \
$::opt_version
,
716 "verbose|v" => \
$::opt_verbose
,
719 if(defined $::opt_help
) { die_usage
(); }
720 if(defined $::opt_version
) { version
(); exit(0); }
721 $Global::debug
= $::opt_debug
;
724 sub database_driver_alias
{
726 my %database_driver_alias = ("mysql" => "mysql",
727 "mysqls" => "mysqlssl",
728 "mysqlssl" => "mysqlssl",
729 "oracle" => "oracle",
731 "oracles" => "oraclessl",
732 "oras" => "oraclessl",
733 "oraclessl" => "oraclessl",
734 "orassl" => "oraclessl",
735 "postgresql" => "postgresql",
736 "pgsql" => "postgresql",
737 "postgres" => "postgresql",
738 "pg" => "postgresql",
739 "postgresqlssl" => "postgresqlssl",
740 "pgsqlssl" => "postgresqlssl",
741 "postgresssl" => "postgresqlssl",
742 "pgssl" => "postgresqlssl",
743 "postgresqls" => "postgresqlssl",
744 "pgsqls" => "postgresqlssl",
745 "postgress" => "postgresqlssl",
746 "pgs" => "postgresqlssl",
747 "sqlite" => "sqlite",
748 "sqlite2" => "sqlite",
749 "sqlite3" => "sqlite3",
750 "influx" => "influx",
751 "influxdb" => "influx",
752 "influxssl" => "influxssl",
753 "influxdbssl" => "influxssl",
754 "influxs" => "influxssl",
755 "influxdbs" => "influxssl",
757 return $database_driver_alias{$driver};
761 my ($database_driver,%opt) = (@_);
762 find_command_in_path
("mysql") || die ("mysql not in path");
763 if(defined($::opt_s
)) { die "Field separator not implemented for mysql" }
764 my $password = defined($opt{'password'}) ?
"--password=".$opt{'password'} : "";
765 my $host = defined($opt{'host'}) ?
"--host=".$opt{'host'} : "";
766 my $port = defined($opt{'port'}) ?
"--port=".$opt{'port'} : "";
767 my $user = defined($opt{'user'}) ?
"--user=".$opt{'user'} : "";
768 my $database = defined($opt{'database'}) ?
$opt{'database'} : $ENV{'USER'};
769 my $html = defined($::opt_html
) ?
"--html" : "";
770 my $no_headers = defined($::opt_n
) ?
"--skip-column-names" : "";
772 if ($database_driver eq "mysqlssl") { $ssl="--ssl"; }
773 my($credential_fh,$tmp) = tempfile
(SUFFIX
=> ".sql");
775 print $credential_fh ("[client]\n",
776 $user && "user=$opt{'user'}\n",
777 $password && "password=$opt{'password'}\n",
778 $host && "host=$opt{'host'}\n");
779 close $credential_fh;
781 # Prepend with a remover of the credential tempfile
782 my $qtmp = shell_quote
($tmp);
784 "((sleep 1; rm $qtmp) & ".
785 "mysql --defaults-extra-file=$qtmp $pass_through_options ".
786 "$no_headers $html $ssl $host $user $port $database)";
787 $interactive_command = $batch_command;
788 return($batch_command,$interactive_command);
791 sub postgresql_commands
{
792 my ($database_driver,%opt) = (@_);
793 find_command_in_path
("psql") || die ("psql not in path");
794 my $sep = ($::opt_s
) ?
"-A --field-separator '$::opt_s'" : "";
795 my $password = defined($opt{'password'}) ?
796 "PGPASSWORD=".$opt{'password'} : "";
797 my $host = defined($opt{'host'}) ?
"-h ".$opt{'host'} : "";
798 my $port = defined($opt{'port'}) ?
"-p ".$opt{'port'} : "";
799 my $user = defined($opt{'user'}) ?
"-U ".$opt{'user'} : "";
800 my $database = defined($opt{'database'}) ?
"-d ".$opt{'database'} : "";
801 my $html = defined($::opt_html
) ?
"--html" : "";
802 my $no_headers = defined($::opt_n
) ?
"--tuples-only" : "";
804 if ($database_driver eq "postgresqlssl") { $ssl="PGSSLMODE=require"; }
806 "$ssl $password psql $pass_through_options $sep $no_headers ".
807 "$html $host $user $port $database";
808 $interactive_command = $batch_command;
810 return($batch_command,$interactive_command);
813 sub oracle_commands
{
814 my ($database_driver,%opt) = (@_);
815 # oracle://user:pass@grum:1521/XE becomes:
816 # sqlplus 'user/pass@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = grum)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XE) ))'
817 my $sqlplus = find_command_in_path
("sqlplus") ||
818 find_command_in_path
("sqlplus64") or
819 die("sqlplus/sqlplus64 not in path");
821 # Readline support: if rlwrap installed run rlwrap sqlplus
822 my $rlwrap = find_command_in_path
("rlwrap");
824 # Set good defaults in the inifile
825 $Global::Initfile
= "/tmp/$$.sql.init";
826 open(INIT,">".$Global::Initfile
) || die;
827 print INIT "SET LINESIZE 32767\n";
828 $::opt_debug
and print "SET LINESIZE 32767\n";
829 print INIT "SET WRAP OFF\n";
830 $::opt_debug
and print "SET WRAP OFF\n";
831 if(defined($::opt_html
)) {
832 print INIT "SET MARK HTML ON\n";
833 $::opt_debug
and print "SET MARK HTML ON\n";
835 if(defined($::opt_n
)) {
836 print INIT "SET PAGES 0\n";
837 $::opt_debug
and print "SET PAGES 0\n";
839 print INIT "SET PAGES 50000\n";
840 $::opt_debug
and print "SET PAGES 50000\n";
842 if(defined($::opt_s
)) {
843 print INIT "SET COLSEP $::opt_s\n";
844 $::opt_debug
and print "SET COLSEP $::opt_s\n";
848 my $password = defined($opt{'password'}) ?
"/".$opt{'password'} : "";
849 my $host = defined($opt{'host'}) ?
$opt{'host'} : "localhost";
850 my $port = defined($opt{'port'}) ?
$opt{'port'} : "1521";
851 my $user = defined($opt{'user'}) ?
$opt{'user'} : "";
852 # Database is called service in Oracle lingo
853 my $service = defined($opt{'database'}) ?
"(SERVICE_NAME = ".$opt{'database'}.")" : "";
854 my $tns = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = $host)(PORT = $port)) ".
855 "(CONNECT_DATA =(SERVER = DEDICATED)$service ))";
857 # -L: Do not re-ask for password if it is wrong
858 my $common_options = "-L $pass_through_options ".
859 "'$user$password\@$tns' \@$Global::Initfile";
860 my $batch_command = "$sqlplus -S ".$common_options;
861 my $interactive_command = "$rlwrap $sqlplus ".$common_options;
862 return($batch_command,$interactive_command);
865 sub sqlite_commands
{
866 my ($database_driver,%opt) = (@_);
867 if(not find_command_in_path
($database_driver)) {
868 print STDERR
"Database driver '$database_driver' not supported\n";
871 my $sep = defined($::opt_s
) ?
"-separator '$::opt_s'" : "";
872 my $password = defined($opt{'password'}) ?
"--password=".$opt{'password'} : "";
873 my $host = defined($opt{'host'}) ?
"--host=".$opt{'host'} : "";
874 my $port = defined($opt{'port'}) ?
"--port=".$opt{'port'} : "";
875 my $user = defined($opt{'user'}) ?
"--user=".$opt{'user'} : "";
876 my $database = defined($opt{'database'}) ?
$opt{'database'} : "";
877 my $html = defined($::opt_html
) ?
"-html" : "";
878 my $no_headers = defined($::opt_n
) ?
"-noheader" : "-header";
881 "$database_driver $pass_through_options $sep ".
882 "$no_headers $html $database";
883 $interactive_command = $batch_command;
884 return($batch_command,$interactive_command);
887 sub influx_commands
{
888 my ($database_driver,%opt) = (@_);
889 my $influx = find_command_in_path
("influx") ||
890 die ("influx not in path");
891 if(defined($::opt_s
)) {
892 die "Field separator not implemented for influx";
895 defined($opt{'password'}) ?
"-password=".$opt{'password'} : "";
896 my $host = defined($opt{'host'}) ?
"-host=".$opt{'host'} : "";
897 my $port = defined($opt{'port'}) ?
"-port=".$opt{'port'} : "";
898 my $user = defined($opt{'user'}) ?
"-username=".$opt{'user'} : "";
899 my $database = defined($opt{'database'}) ?
900 "-database $opt{'database'}" : "-database $ENV{'USER'}";
902 my $format = defined($::opt_json
) ?
"-format json" :
903 defined($::opt_pretty
) ?
"-format json -pretty" :
904 defined($::opt_csv
) ?
"-format csv" : "";
905 my $precision = defined($::opt_precision
) ?
906 "-precision $::opt_precision" : "";
908 my $no_headers = defined($::opt_n
) ?
"--skip-column-names" : "";
910 if($database_driver eq "influxssl") { $ssl="--ssl"; }
913 "$influx $pass_through_options $no_headers $format ".
914 "$precision $ssl $host $user $password $port $database";
915 $interactive_command = $batch_command;
916 return($batch_command,$interactive_command);
919 # Return the code for 'show processlist' in the chosen database dialect
921 my $dbdriver = shift;
924 ("mysql" => "show processlist;",
925 "postgresql" => ("SELECT ".
926 " datname AS database,".
927 " usename AS username,".
928 " now()-xact_start AS runtime,".
930 "FROM pg_stat_activity ".
931 "ORDER BY runtime DESC;"),
932 "oracle" => ("SELECT ".
933 ' CPU_TIME/100000, SYS.V_$SQL.SQL_TEXT, USERNAME '.
935 ' SYS.V_$SQL, SYS.V_$SESSION '.
937 ' SYS.V_$SQL.SQL_ID = SYS.V_$SESSION.SQL_ID(+) '.
938 "AND username IS NOT NULL ".
939 "ORDER BY CPU_TIME DESC;"),
940 "influx" => "show queries;",
942 if($statement{$dbdriver}) {
943 return $statement{$dbdriver};
945 print STDERR
"processlist is not implemented for $dbdriver\n";
950 # Return the code for 'show tables' in the chosen database dialect
952 my $dbdriver = shift;
955 ("mysql" => "show tables;",
956 "postgresql" => '\dt',
957 "oracle" => ("SELECT object_name ".
958 "FROM user_objects ".
959 "WHERE object_type = 'TABLE';"),
960 "sqlite" => ".tables",
961 "sqlite3" => ".tables",
962 "influx" => "show measurements;",
964 if($statement{$dbdriver}) {
965 return $statement{$dbdriver};
967 print STDERR
"tablelist is not implemented for $dbdriver\n";
972 # Return the code for 'show databases' in the chosen database dialect
974 my $dbdriver = shift;
977 ("mysql" => "show databases;",
978 "postgresql" => ("SELECT datname FROM pg_database ".
979 "WHERE datname NOT IN ('template0','template1','postgres') ".
980 "ORDER BY datname ASC;"),
981 "oracle" => ("select * from user_tablespaces;"),
982 "influx" => "show databases;",
984 if($statement{$dbdriver}) {
985 return $statement{$dbdriver};
987 print STDERR
"dblist is not implemented for $dbdriver\n";
992 # Return the code for 'show database size' in the chosen database dialect
994 my $dbdriver = shift;
997 if(defined $dburl{'database'}) {
1001 ' table_schema "database", '.
1002 ' SUM(data_length+index_length) "bytes", '.
1003 ' SUM(data_length+index_length)/1024/1024 "megabytes" '.
1004 'FROM information_schema.TABLES '.
1005 "WHERE table_schema = '$dburl{'database'}'".
1006 'GROUP BY table_schema;'),
1008 "SELECT '$dburl{'database'}' AS database, ".
1009 "pg_database_size('$dburl{'database'}') AS bytes, ".
1010 "pg_size_pretty(pg_database_size('$dburl{'database'}')) AS human_readabable "),
1012 "SELECT ".(undef_as_zero
(-s
$dburl{'database'}))." AS bytes;"),
1014 "SELECT ".(undef_as_zero
(-s
$dburl{'database'}))." AS bytes;"),
1020 ' table_schema "database", '.
1021 ' SUM(data_length+index_length) "bytes", '.
1022 ' SUM(data_length+index_length)/1024/1024 "megabytes" '.
1023 'FROM information_schema.TABLES '.
1024 'GROUP BY table_schema;'),
1026 'SELECT datname AS database, pg_database_size(datname) AS bytes, '.
1027 'pg_size_pretty(pg_database_size(datname)) AS human_readabable '.
1028 'FROM (SELECT datname FROM pg_database) a;'),
1030 "SELECT 0 AS bytes;"),
1032 "SELECT 0 AS bytes;"),
1035 if($statement{$dbdriver}) {
1036 return $statement{$dbdriver};
1038 print STDERR
"dbsize is not implemented for $dbdriver\n";
1044 # Return the code for 'show table size' in the chosen database dialect
1046 my $dbdriver = shift;
1047 my $database = shift;
1050 "SELECT relname, relpages*8 AS kb, reltuples::int AS \"live+dead rows\" ".
1052 "ORDER BY relpages DESC;"),
1054 "select table_name, TABLE_ROWS, DATA_LENGTH,INDEX_LENGTH from INFORMATION_SCHEMA.tables;"),
1056 if($statement{$dbdriver}) {
1057 return $statement{$dbdriver};
1059 print STDERR
"table size is not implemented for $dbdriver\n";
1064 sub is_stdin_terminal
{
1068 sub find_command_in_path
{
1069 # Find the command if it exists in the current path
1070 my $command = shift;
1071 my $path = `which $command`;
1079 print map{ "$_\n" } @_;
1082 print "sql [-hnr] [--table-size] [--db-size] [-p pass-through] [-s string] dburl [command]\n";
1087 $alias =~ s/^(sql:)*//; # Accept aliases prepended with sql:
1088 if ($alias !~ /^:/) {
1095 ($path) = readlink($0) =~ m
|^(.*)/|;
1097 ($path) = $0 =~ m
|^(.*)/|;
1100 my @deprecated = ("$ENV{HOME}/.dburl.aliases",
1101 "$path/dburl.aliases", "$path/dburl.aliases.dist");
1104 print STDERR
"$_ is deprecated. Use .sql/aliases instead (read man sql)\n";
1108 check_permissions
("$ENV{HOME}/.sql/aliases");
1109 check_permissions
("$ENV{HOME}/.dburl.aliases");
1110 my @search = ("$ENV{HOME}/.sql/aliases",
1111 "$ENV{HOME}/.dburl.aliases", "/etc/sql/aliases",
1112 "$path/dburl.aliases", "$path/dburl.aliases.dist");
1113 for my $alias_file (@search) {
1114 if(-r
$alias_file) {
1115 open(my $fh, "<", $alias_file) || die;
1116 push @urlalias, <$fh>;
1120 my ($alias_part,$rest) = $alias=~/(:\w*)(.*)/;
1121 # If we saw this before: we have an alias loop
1122 if(grep {$_ eq $alias_part } @Private::seen_aliases
) {
1123 print STDERR
"$alias_part is a cyclic alias\n";
1126 push @Private::seen_aliases
, $alias_part;
1131 /^$alias_part\s+(\S+.*)/ and do { $dburl = $1; last; }
1135 return get_alias
($dburl.$rest);
1137 Usage
("$alias is not defined in @search");
1142 sub check_permissions
{
1147 my $username = (getpwuid($<))[0];
1148 print STDERR
"$file should be owned by $username: chown $username $file\n";
1150 my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,
1151 $atime,$mtime,$ctime,$blksize,$blocks) = stat($file);
1153 my $username = (getpwuid($<))[0];
1154 print STDERR
"$file should be only be readable by $username: chmod 600 $file\n";
1162 # sql:mysql://[[user][:password]@][host][:port]/[database[?sql query]]
1164 if($url=~m
!(?
:sql
:)?
# You can prefix with 'sql:'
1165 ((?
:oracle
|ora
|mysql
|pg
|postgres
|postgresql
|influx
|influxdb
)(?
:s
|ssl
|)|
1166 (?
:sqlite
|sqlite2
|sqlite3
)):// # Databasedriver ($1)
1168 ([^:@
/][^:@
]*|) # Username ($2)
1170 :([^@
]*) # Password ($3)
1173 ([^:/]*)?
# Hostname ($4)
1176 ([^/]*)?
# Port ($5)
1180 ([^?
/]*)?
# Database ($6)
1188 $options{databasedriver
} = undef_if_empty
(uri_unescape
($1));
1189 $options{user
} = undef_if_empty
(uri_unescape
($2));
1190 $options{password
} = undef_if_empty
(uri_unescape
($3));
1191 $options{host
} = undef_if_empty
(uri_unescape
($4));
1192 $options{port
} = undef_if_empty
(uri_unescape
($5));
1193 $options{database
} = undef_if_empty
(uri_unescape
($6))
1195 $options{query
} = undef_if_empty
(uri_unescape
($7));
1196 debug
("dburl $url\n");
1197 debug
("databasedriver ",$options{databasedriver
}, " user ", $options{user
},
1198 " password ", $options{password
}, " host ", $options{host
},
1199 " port ", $options{port
}, " database ", $options{database
},
1200 " query ",$options{query
}, "\n");
1202 Usage
("$url is not a valid DBURL");
1209 # Copied from http://cpansearch.perl.org/src/GAAS/URI-1.55/URI/Escape.pm
1210 # to avoid depending on URI::Escape
1211 # This section is (C) Gisle Aas.
1212 # Note from RFC1630: "Sequences which start with a percent sign
1213 # but are not followed by two hexadecimal characters are reserved
1214 # for future extension"
1216 if (@_ && wantarray) {
1217 # not executed for the common case of a single argument
1218 my @str = ($str, @_); # need to copy
1220 s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg;
1224 $str =~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg if defined $str;
1228 sub undef_if_empty
{
1229 if(defined($_[0]) and $_[0] eq "") {
1243 "GNU $Global::progname $Global::version",
1244 "Copyright (C) 2009,2010,2011,2012,2013,2014,2015,2016,2017",
1245 "Ole Tange and Free Software Foundation, Inc.",
1246 "License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>",
1247 "This is free software: you are free to change and redistribute it.",
1248 "GNU $Global::progname comes with no warranty.",
1250 "Web site: http://www.gnu.org/software/${Global::progname}\n",
1251 "When using GNU $Global::progname for a publication please cite:\n",
1252 "O. Tange (2011): GNU SQL - A Command Line Tool for Accessing Different",
1253 "Databases Using DBURLs, ;login: The USENIX Magazine, April 2011:29-32.\n"
1266 print "$Global::progname [options] dburl [sqlcommand]\n";
1267 print "$Global::progname [options] dburl < sql_command_file\n";
1269 print "See 'man $Global::progname' for the options\n";
1274 $Global::debug
or return;
1275 @_ = grep { defined $_ ?
$_ : "" } @_;
1279 $::opt_skip_first_line
= $::opt_shebang
= 0;