src/sql

   1 #!/usr/bin/perl -w
   2
   3 # Copyright (C) 2008-2010 Ole Tange, http://ole.tange.dk
   4 #
   5 # Copyright (C) 2010-2024 Ole Tange, http://ole.tange.dk and
   6 # Free Software Foundation, Inc.
   7 #
   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.
  12 #
  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.
  17 #
  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
  22 #
  23 # SPDX-FileCopyrightText: 2008-2024 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
  26
  27 =head1 NAME
  28
  29 sql - execute a command on a database determined by a dburl
  30
  31 =head1 SYNOPSIS
  32
  33 B<sql> [options] I<dburl> [I<commands>]
  34
  35 B<sql> [options] I<dburl> < commandfile
  36
  37 B<#!/usr/bin/sql> B<--shebang> [options] I<dburl>
  38
  39 =head1 DESCRIPTION
  40
  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.
  46
  47 The database is addressed using a DBURL. If I<commands> are left out
  48 you will get that database's interactive shell.
  49
  50 GNU B<sql> is often used in combination with GNU B<parallel>.
  51
  52 =over 9
  53
  54 =item I<dburl>
  55
  56 A DBURL has the following syntax:
  57 [sql:]vendor://
  58 [[user][:password]@][host][:port]/[database][?sqlquery]
  59
  60 See the section DBURL below.
  61
  62 =item I<commands>
  63
  64 The SQL commands to run. Each argument will have a newline
  65 appended.
  66
  67 Example: "SELECT * FROM foo;" "SELECT * FROM bar;"
  68
  69 If the arguments contain '\n' or '\x0a' this will be replaced with a
  70 newline:
  71
  72 Example: "SELECT * FROM foo;\n SELECT * FROM bar;"
  73
  74 If no commands are given SQL is read from the keyboard or STDIN.
  75
  76 Example: echo 'SELECT * FROM foo;' | sql mysql:///
  77
  78
  79 =item B<--csv>
  80
  81 CSV output.
  82
  83
  84 =item B<--db-size>
  85
  86 =item B<--dbsize>
  87
  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
  90 I<system> has that.
  91
  92
  93 =item B<--help>
  94
  95 =item B<-h>
  96
  97 Print a summary of the options to GNU B<sql> and exit.
  98
  99
 100 =item B<--html>
 101
 102 HTML output. Turn on HTML tabular output.
 103
 104
 105 =item B<--json>
 106
 107 =item B<--pretty>
 108
 109 Pretty JSON output.
 110
 111
 112 =item B<--list-databases>
 113
 114 =item B<--listdbs>
 115
 116 =item B<--show-databases>
 117
 118 =item B<--showdbs>
 119
 120 List the databases (table spaces) in the database.
 121
 122
 123 =item B<--listproc>
 124
 125 =item B<--proclist>
 126
 127 =item B<--show-processlist>
 128
 129 Show the list of running queries.
 130
 131
 132 =item B<--list-tables>
 133
 134 =item B<--show-tables>
 135
 136 =item B<--table-list>
 137
 138 List the tables in the database.
 139
 140
 141 =item B<--noheaders>
 142
 143 =item B<--no-headers>
 144
 145 =item B<-n>
 146
 147 Remove headers and footers and print only tuples. Bug in Oracle: it
 148 still prints number of rows found.
 149
 150
 151 =item B<-p> I<pass-through>
 152
 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:
 156
 157 I<-p "-U scott"> can also be written I<-p -U -p scott>.
 158
 159
 160 =item B<--precision> <I<rfc3339|h|m|s|ms|u|ns>>
 161
 162 Precision of timestamps.
 163
 164 Specifiy the format of the output timestamps: rfc3339, h, m, s, ms, u
 165 or ns.
 166
 167
 168 =item B<-r>
 169
 170 Try 3 times. Short version of I<--retries 3>.
 171
 172
 173 =item B<--retries> I<ntimes>
 174
 175 Try I<ntimes> times. If the client program returns with an error,
 176 retry the command. Default is I<--retries 1>.
 177
 178
 179 =item B<--sep> I<string>
 180
 181 =item B<-s> I<string>
 182
 183 Field separator. Use I<string> as separator between columns.
 184
 185
 186 =item B<--skip-first-line>
 187
 188 Do not use the first line of input (used by GNU B<sql> itself
 189 when called with B<--shebang>).
 190
 191
 192 =item B<--table-size>
 193
 194 =item B<--tablesize>
 195
 196 Size of tables. Show the size of the tables in the database.
 197
 198
 199 =item B<--verbose>
 200
 201 =item B<-v>
 202
 203 Print which command is sent.
 204
 205
 206 =item B<--version>
 207
 208 =item B<-V>
 209
 210 Print the version GNU B<sql> and exit.
 211
 212
 213 =item B<--shebang>
 214
 215 =item B<-Y>
 216
 217 GNU B<sql> can be called as a shebang (#!) command as the first line of a script. Like this:
 218
 219   #!/usr/bin/sql -Y mysql:///
 220
 221   SELECT * FROM foo;
 222
 223 For this to work B<--shebang> or B<-Y> must be set as the first option.
 224
 225 =back
 226
 227 =head1 DBURL
 228
 229 A DBURL has the following syntax:
 230 [sql:]vendor://
 231 [[user][:password]@][host][:port]/[database][?sqlquery]
 232
 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').
 236
 237 Examples:
 238
 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
 243  pg:///
 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;
 247
 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)
 254
 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.
 258
 259 Example of aliases:
 260
 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`
 264  :myalias3 mysql:///
 265  # Short form of mysql://`whoami`:nopassword@localhost:33333/mydb
 266  :myalias4 mysql://:33333/mydb
 267  # Alias for an alias
 268  :m      :myalias4
 269  # the sortest alias possible
 270  :       sqlite2:////tmp/db.sqlite
 271  # Including an SQL query
 272  :query  sqlite:////tmp/db.sqlite?SELECT * FROM foo;
 273
 274 =head1 EXAMPLES
 275
 276 =head2 Get an interactive prompt
 277
 278 The most basic use of GNU B<sql> is to get an interactive prompt:
 279
 280 B<sql sql:oracle://scott:tiger@ora.example.com/xe>
 281
 282 If you have setup an alias you can do:
 283
 284 B<sql :myora>
 285
 286
 287 =head2 Run a query
 288
 289 To run a query directly from the command line:
 290
 291 B<sql :myalias "SELECT * FROM foo;">
 292
 293 Oracle requires newlines after each statement. This can be done like
 294 this:
 295
 296 B<sql :myora "SELECT * FROM foo;" "SELECT * FROM bar;">
 297
 298 Or this:
 299
 300 B<sql :myora "SELECT * FROM foo;\nSELECT * FROM bar;">
 301
 302
 303 =head2 Copy a PostgreSQL database
 304
 305 To copy a PostgreSQL database use pg_dump to generate the dump and GNU
 306 B<sql> to import it:
 307
 308 B<pg_dump pg_database | sql pg://scott:tiger@pg.example.com/pgdb>
 309
 310
 311 =head2 Empty all tables in a MySQL database
 312
 313 Using GNU B<parallel> it is easy to empty all tables without dropping them:
 314
 315 B<sql -n mysql:/// 'show tables' | parallel sql mysql:/// DELETE FROM {};>
 316
 317
 318 =head2 Drop all tables in a PostgreSQL database
 319
 320 To drop all tables in a PostgreSQL database do:
 321
 322 B<sql -n pg:/// '\dt' | parallel --colsep '\|' -r sql pg:/// DROP TABLE {2};>
 323
 324
 325 =head2 Run as a script
 326
 327 Instead of doing:
 328
 329 B<sql mysql:/// < sqlfile>
 330
 331 you can combine the sqlfile with the DBURL to make a
 332 UNIX-script. Create a script called I<demosql>:
 333
 334 B<#!/usr/bin/sql -Y mysql:///>
 335
 336 B<SELECT * FROM foo;>
 337
 338 Then do:
 339
 340 B<chmod +x demosql; ./demosql>
 341
 342
 343 =head2 Use --colsep to process multiple columns
 344
 345 Use GNU B<parallel>'s B<--colsep> to separate columns:
 346
 347 B<sql -s '\t' :myalias 'SELECT * FROM foo;' | parallel --colsep '\t' do_stuff {4} {1}>
 348
 349
 350 =head2 Retry if the connection fails
 351
 352 If the access to the database fails occasionally B<--retries> can help
 353 make sure the query succeeds:
 354
 355 B<sql --retries 5 :myalias 'SELECT * FROM really_big_foo;'>
 356
 357
 358 =head2 Get info about the running database system
 359
 360 Show how big the database is:
 361
 362 B<sql --db-size :myalias>
 363
 364 List the tables:
 365
 366 B<sql --list-tables :myalias>
 367
 368 List the size of the tables:
 369
 370 B<sql --table-size :myalias>
 371
 372 List the running processes:
 373
 374 B<sql --show-processlist :myalias>
 375
 376
 377 =head1 REPORTING BUGS
 378
 379 GNU B<sql> is part of GNU B<parallel>. Report bugs to <bug-parallel@gnu.org>.
 380
 381
 382 =head1 AUTHOR
 383
 384 When using GNU B<sql> for a publication please cite:
 385
 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.
 388
 389 Copyright (C) 2008-2010 Ole Tange http://ole.tange.dk
 390
 391 Copyright (C) 2010-2024 Ole Tange, http://ole.tange.dk and Free
 392 Software Foundation, Inc.
 393
 394 =head1 LICENSE
 395
 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.
 400
 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.
 405
 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/>.
 408
 409 =head2 Documentation license I
 410
 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.
 417
 418
 419 =head2 Documentation license II
 420
 421 You are free:
 422
 423 =over 9
 424
 425 =item B<to Share>
 426
 427 to copy, distribute and transmit the work
 428
 429 =item B<to Remix>
 430
 431 to adapt the work
 432
 433 =back
 434
 435 Under the following conditions:
 436
 437 =over 9
 438
 439 =item B<Attribution>
 440
 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).
 444
 445 =item B<Share Alike>
 446
 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
 449 license.
 450
 451 =back
 452
 453 With the understanding that:
 454
 455 =over 9
 456
 457 =item B<Waiver>
 458
 459 Any of the above conditions can be waived if you get permission from
 460 the copyright holder.
 461
 462 =item B<Public Domain>
 463
 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.
 466
 467 =item B<Other Rights>
 468
 469 In no way are any of the following rights affected by the license:
 470
 471 =over 9
 472
 473 =item *
 474
 475 Your fair dealing or fair use rights, or other applicable
 476 copyright exceptions and limitations;
 477
 478 =item *
 479
 480 The author's moral rights;
 481
 482 =item *
 483
 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.
 486
 487 =back
 488
 489 =item B<Notice>
 490
 491 For any reuse or distribution, you must make clear to others the
 492 license terms of this work.
 493
 494 =back
 495
 496 A copy of the full license is included in the file as cc-by-sa.txt.
 497
 498 =head1 DEPENDENCIES
 499
 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.
 506
 507
 508 =head1 FILES
 509
 510 ~/.sql/aliases - user's own aliases with DBURLs
 511
 512 /etc/sql/aliases - common aliases with DBURLs
 513
 514
 515 =head1 SEE ALSO
 516
 517 B<mysql>(1), B<psql>(1), B<rlwrap>(1), B<sqlite>(1), B<sqlite3>(1),
 518 B<sqlplus>(1), B<influx>(1)
 519
 520 =cut
 521
 522 use Getopt::Long;
 523 use strict;
 524 use File::Temp qw/tempfile tempdir/;
 525
 526 sub shell_quote($) {
 527     # Quote for other shells (Bourne compatibles)
 528     # Inputs:
 529     #   $string = string to be quoted
 530     # Returns:
 531     #   $shell_quoted = string quoted as needed by the shell
 532     my $s = $_[0];
 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)
 538         return $s;
 539     } elsif ($s eq "") {
 540         return "''";
 541     } else {
 542         # No quoting needed
 543         return $s;
 544     }
 545 }
 546
 547 parse_options();
 548
 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));
 553
 554 my $interactive_command;
 555 my $batch_command;
 556
 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);
 577 }
 578
 579 my $err;
 580 my $retries;
 581 $retries ||= defined $::opt_retries ? $::opt_retries : undef;
 582 $retries ||= defined $::opt_retry ? $::opt_retry * 3 : undef;
 583 $retries ||= 1;
 584
 585 if(defined $::opt_processlist) {
 586     unshift @ARGV, processlist($database_driver,%dburl);
 587 }
 588
 589 if(defined $::opt_tablelist) {
 590     unshift @ARGV, tablelist($database_driver,%dburl);
 591 }
 592
 593 if(defined $::opt_dblist) {
 594     unshift @ARGV, dblist($database_driver,%dburl);
 595 }
 596
 597 if(defined $::opt_dbsize) {
 598     unshift @ARGV, dbsize($database_driver,%dburl);
 599 }
 600
 601 if(defined $::opt_tablesize) {
 602     unshift @ARGV, tablesize($database_driver,%dburl);
 603 }
 604
 605 my $queryfile = "";
 606 if($dburl{'query'}) {
 607     my $fh;
 608     ($fh,$queryfile) = tempfile(SUFFIX => ".sql");
 609     print $fh $dburl{'query'},"\n";
 610     close $fh;
 611     my $qfile = shell_quote($queryfile);
 612     $batch_command = "(cat $qfile;rm $qfile; cat) | $batch_command";
 613 }
 614
 615
 616
 617 do {
 618     if(@ARGV) {
 619         # SQL Commands given as arguments:
 620         # Run those commands
 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
 625             for(@ARGV) {
 626                 s/\\n/\n/g;
 627                 s/\\x0a/\n/gi;
 628                 $::opt_debug and print "[$batch_command -execute $_]\n";
 629                 system("$batch_command -execute ".shell_quote($_));
 630             }
 631         } else {
 632             open(M,"| $batch_command") ||
 633                 die("mysql/psql/sqlplus/influx not in path");
 634             for(@ARGV) {
 635                 s/\\n/\n/g;
 636                 s/\\x0a/\n/gi;
 637                 print M "$_\n";
 638             }
 639             close M;
 640         }
 641     } else {
 642         if (is_stdin_terminal()) {
 643             # Run interactively
 644             $::opt_debug and print "[$interactive_command]\n";
 645             $::opt_verbose and print "[$interactive_command]\n";
 646             system("$interactive_command");
 647         } else {
 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
 653                 while(<STDIN>) {
 654                     s/\\n/\n/g;
 655                     s/\\x0a/\n/gi;
 656                     $::opt_debug and print "[$batch_command -execute $_]\n";
 657                     system("$batch_command -execute ".shell_quote($_));
 658                 }
 659             } else{
 660                 system("$batch_command");
 661             }
 662         }
 663     }
 664     $err = $?>>8;
 665 } while (--$retries and $err);
 666
 667 $queryfile and unlink $queryfile;
 668
 669 $Global::Initfile && unlink $Global::Initfile;
 670 exit ($err);
 671
 672 sub parse_options {
 673     $Global::version = 20240522;
 674     $Global::progname = 'sql';
 675
 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";
 685     }
 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,
 695                "dblist|".
 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,
 713                # GNU requirements
 714                "help|h" => \$::opt_help,
 715                "version|V" => \$::opt_version,
 716                "verbose|v" => \$::opt_verbose,
 717         ) || die_usage();
 718
 719     if(defined $::opt_help) { die_usage(); }
 720     if(defined $::opt_version) { version(); exit(0); }
 721     $Global::debug = $::opt_debug;
 722 }
 723
 724 sub database_driver_alias {
 725     my $driver = shift;
 726     my %database_driver_alias = ("mysql" => "mysql",
 727                                  "mysqls" => "mysqlssl",
 728                                  "mysqlssl" => "mysqlssl",
 729                                  "oracle" => "oracle",
 730                                  "ora" => "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",
 756         );
 757     return $database_driver_alias{$driver};
 758 }
 759
 760 sub mysql_commands {
 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" : "";
 771     my $ssl = "";
 772     if ($database_driver eq "mysqlssl") { $ssl="--ssl"; }
 773     my($credential_fh,$tmp) = tempfile(SUFFIX => ".sql");
 774     chmod (0600,$tmp);
 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;
 780
 781     # Prepend with a remover of the credential tempfile
 782     my $qtmp = shell_quote($tmp);
 783     $batch_command =
 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);
 789 }
 790
 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" : "";
 803     my $ssl = "";
 804     if ($database_driver eq "postgresqlssl") { $ssl="PGSSLMODE=require"; }
 805     $batch_command =
 806         "$ssl $password psql $pass_through_options $sep $no_headers ".
 807         "$html $host $user $port $database";
 808     $interactive_command = $batch_command;
 809
 810     return($batch_command,$interactive_command);
 811 }
 812
 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");
 820
 821     # Readline support: if rlwrap installed run rlwrap sqlplus
 822     my $rlwrap = find_command_in_path("rlwrap");
 823
 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";
 834     }
 835     if(defined($::opt_n)) {
 836         print INIT "SET PAGES 0\n";
 837         $::opt_debug and print "SET PAGES 0\n";
 838     } else {
 839         print INIT "SET PAGES 50000\n";
 840         $::opt_debug and print "SET PAGES 50000\n";
 841     }
 842     if(defined($::opt_s)) {
 843         print INIT "SET COLSEP $::opt_s\n";
 844         $::opt_debug and print "SET COLSEP $::opt_s\n";
 845     }
 846     close INIT;
 847
 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 ))";
 856     my $ssl = "";
 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);
 863 }
 864
 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";
 869         exit -1;
 870     }
 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";
 879     my $ssl = "";
 880     $batch_command =
 881         "$database_driver $pass_through_options $sep ".
 882         "$no_headers $html $database";
 883     $interactive_command = $batch_command;
 884     return($batch_command,$interactive_command);
 885 }
 886
 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";
 893     }
 894     my $password =
 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'}";
 901
 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" : "";
 907
 908     my $no_headers = defined($::opt_n) ? "--skip-column-names" : "";
 909     my $ssl = "";
 910     if($database_driver eq "influxssl") { $ssl="--ssl"; }
 911
 912     $batch_command =
 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);
 917 }
 918
 919 # Return the code for 'show processlist' in the chosen database dialect
 920 sub processlist {
 921     my $dbdriver = shift;
 922     my %dburl = @_;
 923     my %statement =
 924         ("mysql" => "show processlist;",
 925          "postgresql" => ("SELECT ".
 926                           "    datname AS database,".
 927                           "    usename AS username,".
 928                           "    now()-xact_start AS runtime,".
 929                           "    current_query ".
 930                           "FROM pg_stat_activity ".
 931                           "ORDER BY runtime DESC;"),
 932          "oracle" => ("SELECT ".
 933                       '    CPU_TIME/100000, SYS.V_$SQL.SQL_TEXT, USERNAME '.
 934                       "FROM ".
 935                       '    SYS.V_$SQL, SYS.V_$SESSION '.
 936                       "WHERE ".
 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;",
 941         );
 942     if($statement{$dbdriver}) {
 943         return $statement{$dbdriver};
 944     } else {
 945         print STDERR "processlist is not implemented for $dbdriver\n";
 946         exit 1;
 947     }
 948 }
 949
 950 # Return the code for 'show tables' in the chosen database dialect
 951 sub tablelist {
 952     my $dbdriver = shift;
 953     my %dburl = @_;
 954     my %statement =
 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;",
 963         );
 964     if($statement{$dbdriver}) {
 965         return $statement{$dbdriver};
 966     } else {
 967         print STDERR "tablelist is not implemented for $dbdriver\n";
 968         exit 1;
 969     }
 970 }
 971
 972 # Return the code for 'show databases' in the chosen database dialect
 973 sub dblist {
 974     my $dbdriver = shift;
 975     my %dburl = @_;
 976     my %statement =
 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;",
 983         );
 984     if($statement{$dbdriver}) {
 985         return $statement{$dbdriver};
 986     } else {
 987         print STDERR "dblist is not implemented for $dbdriver\n";
 988         exit 1;
 989     }
 990 }
 991
 992 # Return the code for 'show database size' in the chosen database dialect
 993 sub dbsize {
 994     my $dbdriver = shift;
 995     my %dburl = @_;
 996     my %statement;
 997     if(defined $dburl{'database'}) {
 998         %statement =
 999             ("mysql" => (
1000                  'SELECT '.
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;'),
1007              "postgresql" => (
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 "),
1011              "sqlite" => (
1012                  "SELECT ".(undef_as_zero(-s $dburl{'database'}))." AS bytes;"),
1013              "sqlite3" => (
1014                  "SELECT ".(undef_as_zero(-s $dburl{'database'}))." AS bytes;"),
1015             );
1016     } else {
1017         %statement =
1018             ("mysql" => (
1019                  'SELECT '.
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;'),
1025              "postgresql" => (
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;'),
1029              "sqlite" => (
1030                  "SELECT 0 AS bytes;"),
1031              "sqlite3" => (
1032                  "SELECT 0 AS bytes;"),
1033             );
1034     }
1035     if($statement{$dbdriver}) {
1036         return $statement{$dbdriver};
1037     } else {
1038         print STDERR "dbsize is not implemented for $dbdriver\n";
1039         exit 1;
1040     }
1041 }
1042
1043
1044 # Return the code for 'show table size' in the chosen database dialect
1045 sub tablesize {
1046     my $dbdriver = shift;
1047     my $database = shift;
1048     my %statement =
1049         ("postgresql" => (
1050              "SELECT relname, relpages*8 AS kb, reltuples::int AS \"live+dead rows\" ".
1051              "FROM pg_class c ".
1052              "ORDER BY relpages DESC;"),
1053          "mysql" => (
1054              "select table_name, TABLE_ROWS, DATA_LENGTH,INDEX_LENGTH from INFORMATION_SCHEMA.tables;"),
1055         );
1056     if($statement{$dbdriver}) {
1057         return $statement{$dbdriver};
1058     } else {
1059         print STDERR "table size is not implemented for $dbdriver\n";
1060         exit 1;
1061     }
1062 }
1063
1064 sub is_stdin_terminal {
1065     return (-t STDIN);
1066 }
1067
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`;
1072     chomp $path;
1073     return $path;
1074 }
1075
1076 sub Usage {
1077     if(@_) {
1078         print "Error:\n";
1079         print map{ "$_\n" } @_;
1080         print "\n";
1081     }
1082     print "sql [-hnr] [--table-size] [--db-size] [-p pass-through] [-s string] dburl [command]\n";
1083 }
1084
1085 sub get_alias {
1086     my $alias = shift;
1087     $alias =~ s/^(sql:)*//; # Accept aliases prepended with sql:
1088     if ($alias !~ /^:/) {
1089         return $alias;
1090     }
1091
1092     # Find the alias
1093     my $path;
1094     if (-l $0) {
1095         ($path) = readlink($0) =~ m|^(.*)/|;
1096     } else {
1097         ($path) = $0 =~ m|^(.*)/|;
1098     }
1099
1100     my @deprecated = ("$ENV{HOME}/.dburl.aliases",
1101                       "$path/dburl.aliases", "$path/dburl.aliases.dist");
1102     for (@deprecated) {
1103         if(-r $_) {
1104             print STDERR "$_ is deprecated. Use .sql/aliases instead (read man sql)\n";
1105         }
1106     }
1107     my @urlalias=();
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>;
1117             close $fh;
1118         }
1119     }
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";
1124         exit -1;
1125     } else {
1126         push @Private::seen_aliases, $alias_part;
1127     }
1128
1129     my $dburl;
1130     for (@urlalias) {
1131         /^$alias_part\s+(\S+.*)/ and do { $dburl = $1; last; }
1132     }
1133
1134     if($dburl) {
1135         return get_alias($dburl.$rest);
1136     } else {
1137         Usage("$alias is not defined in @search");
1138         exit(-1);
1139     }
1140 }
1141
1142 sub check_permissions {
1143     my $file = shift;
1144
1145     if(-e $file) {
1146         if(not -o $file) {
1147             my $username = (getpwuid($<))[0];
1148             print STDERR "$file should be owned by $username: chown $username $file\n";
1149         }
1150         my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,
1151             $atime,$mtime,$ctime,$blksize,$blocks) = stat($file);
1152         if($mode & 077) {
1153             my $username = (getpwuid($<))[0];
1154             print STDERR "$file should be only be readable by $username: chmod 600 $file\n";
1155         }
1156     }
1157 }
1158
1159 sub parse_dburl {
1160     my $url = shift;
1161     my %options = ();
1162     # sql:mysql://[[user][:password]@][host][:port]/[database[?sql query]]
1163
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)
1167                (?:
1168                 ([^:@/][^:@]*|) # Username ($2)
1169                 (?:
1170                  :([^@]*) # Password ($3)
1171                 )?
1172                @)?
1173                ([^:/]*)? # Hostname ($4)
1174                (?:
1175                 :
1176                 ([^/]*)? # Port ($5)
1177                )?
1178                (?:
1179                 /
1180                 ([^?/]*)? # Database ($6)
1181                )?
1182                (?:
1183                 /?
1184                 \?
1185                 (.*)? # Query ($7)
1186                )?
1187               !x) {
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))
1194             || $options{user};
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");
1201     } else {
1202         Usage("$url is not a valid DBURL");
1203         exit -1;
1204     }
1205     return %options;
1206 }
1207
1208 sub uri_unescape {
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"
1215     my $str = shift;
1216     if (@_ && wantarray) {
1217         # not executed for the common case of a single argument
1218         my @str = ($str, @_);  # need to copy
1219         foreach (@str) {
1220             s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg;
1221         }
1222         return @str;
1223     }
1224     $str =~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg if defined $str;
1225     $str;
1226 }
1227
1228 sub undef_if_empty {
1229     if(defined($_[0]) and $_[0] eq "") {
1230         return undef;
1231     }
1232     return $_[0];
1233 }
1234
1235 sub undef_as_zero {
1236     my $a = shift;
1237     return $a ? $a : 0;
1238 }
1239
1240 sub version {
1241     # Returns: N/A
1242     print join("\n",
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.",
1249                "",
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"
1254         );
1255 }
1256
1257 sub die_usage {
1258     # Returns: N/A
1259     usage();
1260     exit(255);
1261 }
1262
1263 sub usage {
1264     # Returns: N/A
1265     print "Usage:\n";
1266     print "$Global::progname [options] dburl [sqlcommand]\n";
1267     print "$Global::progname [options] dburl < sql_command_file\n";
1268     print "\n";
1269     print "See 'man $Global::progname' for the options\n";
1270 }
1271
1272 sub debug {
1273     # Returns: N/A
1274     $Global::debug or return;
1275     @_ = grep { defined $_ ? $_ : "" } @_;
1276     print @_;
1277 }
1278
1279 $::opt_skip_first_line = $::opt_shebang = 0;