src/sql

   1 #!/usr/bin/perl -w
   2
   3 # Copyright (C) 2008-2010 Ole Tange, http://ole.tange.dk
   4 #
   5 # Copyright (C) 2010-2022 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-2022 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> (beta testing)
  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> (beta testing)
 106
 107 =item B<--pretty> (beta testing)
 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-2022 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 parse_options();
 527
 528 my $pass_through_options = (defined $::opt_p) ? join(" ",@{$::opt_p}) : "";
 529 my $dburl_or_alias = shift;
 530 if (not defined $dburl_or_alias) { Usage("No DBURL given"); exit -1; }
 531 my %dburl = parse_dburl(get_alias($dburl_or_alias));
 532
 533 my $interactive_command;
 534 my $batch_command;
 535
 536 my $database_driver = database_driver_alias($dburl{'databasedriver'});
 537 if($database_driver eq "mysql" or
 538    $database_driver eq "mysqlssl") {
 539     ($batch_command,$interactive_command) =
 540         mysql_commands($database_driver,%dburl);
 541 } elsif($database_driver eq "postgresql" or
 542         $database_driver eq "postgresqlssl") {
 543     ($batch_command,$interactive_command) =
 544         postgresql_commands($database_driver,%dburl);
 545 } elsif($database_driver eq "oracle") {
 546     ($batch_command,$interactive_command) =
 547         oracle_commands($database_driver,%dburl);
 548 } elsif($database_driver eq "sqlite" or
 549         $database_driver eq "sqlite3") {
 550     ($batch_command,$interactive_command) =
 551         sqlite_commands($database_driver,%dburl);
 552 } elsif($database_driver eq "influx" or
 553         $database_driver eq "influxssl") {
 554     ($batch_command,$interactive_command) =
 555         influx_commands($database_driver,%dburl);
 556 }
 557
 558 my $err;
 559 my $retries;
 560 $retries ||= defined $::opt_retries ? $::opt_retries : undef;
 561 $retries ||= defined $::opt_retry ? $::opt_retry * 3 : undef;
 562 $retries ||= 1;
 563
 564 if(defined $::opt_processlist) {
 565     unshift @ARGV, processlist($database_driver,%dburl);
 566 }
 567
 568 if(defined $::opt_tablelist) {
 569     unshift @ARGV, tablelist($database_driver,%dburl);
 570 }
 571
 572 if(defined $::opt_dblist) {
 573     unshift @ARGV, dblist($database_driver,%dburl);
 574 }
 575
 576 if(defined $::opt_dbsize) {
 577     unshift @ARGV, dbsize($database_driver,%dburl);
 578 }
 579
 580 if(defined $::opt_tablesize) {
 581     unshift @ARGV, tablesize($database_driver,%dburl);
 582 }
 583
 584 my $queryfile = "";
 585 if($dburl{'query'}) {
 586     my $fh;
 587     ($fh,$queryfile) = tempfile(SUFFIX => ".sql");
 588     print $fh $dburl{'query'},"\n";
 589     close $fh;
 590     $batch_command = "(cat $queryfile;rm $queryfile; cat) | $batch_command";
 591 }
 592
 593 sub shell_quote($) {
 594     # Quote for other shells (Bourne compatibles)
 595     # Inputs:
 596     #   $string = string to be quoted
 597     # Returns:
 598     #   $shell_quoted = string quoted as needed by the shell
 599     my $s = $_[0];
 600     if($s =~ /[^-_.+a-z0-9\/]/i) {
 601         $s =~ s/'/'"'"'/g; # "-quote single quotes
 602         $s = "'$s'";       # '-quote entire string
 603         $s =~ s/^''//;     # Remove unneeded '' at ends
 604         $s =~ s/''$//;     # (faster than s/^''|''$//g)
 605         return $s;
 606     } elsif ($s eq "") {
 607         return "''";
 608     } else {
 609         # No quoting needed
 610         return $s;
 611     }
 612 }
 613
 614 do {
 615     if(@ARGV) {
 616         # SQL Commands given as arguments:
 617         # Run those commands
 618         $::opt_debug and print "[ | $batch_command]\n";
 619         $::opt_verbose and print "[ | $batch_command]\n";
 620         if($database_driver eq "influx" or $database_driver eq "influxssl") {
 621             # Influx currently cannot read commands from stdin
 622             for(@ARGV) {
 623                 s/\\n/\n/g;
 624                 s/\\x0a/\n/gi;
 625                 $::opt_debug and print "[$batch_command -execute $_]\n";
 626                 system("$batch_command -execute ".shell_quote($_));
 627             }
 628         } else {
 629             open(M,"| $batch_command") ||
 630                 die("mysql/psql/sqlplus/influx not in path");
 631             for(@ARGV) {
 632                 s/\\n/\n/g;
 633                 s/\\x0a/\n/gi;
 634                 print M "$_\n";
 635             }
 636             close M;
 637         }
 638     } else {
 639         if (is_stdin_terminal()) {
 640             # Run interactively
 641             $::opt_debug and print "[$interactive_command]\n";
 642             $::opt_verbose and print "[$interactive_command]\n";
 643             system("$interactive_command");
 644         } else {
 645             # Let the command read from stdin
 646             $::opt_debug and print "[$batch_command]\n";
 647             $::opt_verbose and print "[$batch_command]\n";
 648             if($database_driver eq "influx" or $database_driver eq "influxssl") {
 649                 # Influx currently cannot read commands from stdin
 650                 while(<STDIN>) {
 651                     s/\\n/\n/g;
 652                     s/\\x0a/\n/gi;
 653                     $::opt_debug and print "[$batch_command -execute $_]\n";
 654                     system("$batch_command -execute ".shell_quote($_));
 655                 }
 656             } else{
 657                 system("$batch_command");
 658             }
 659         }
 660     }
 661     $err = $?>>8;
 662 } while (--$retries and $err);
 663
 664 $queryfile and unlink $queryfile;
 665
 666 $Global::Initfile && unlink $Global::Initfile;
 667 exit ($err);
 668
 669 sub parse_options {
 670     $Global::version = 20221022;
 671     $Global::progname = 'sql';
 672
 673     # This must be done first as this may exec myself
 674     if(defined $ARGV[0] and ($ARGV[0]=~/^-Y/ or $ARGV[0]=~/^--shebang /)) {
 675         # Program is called from #! line in script
 676         $ARGV[0]=~s/^-Y //; # remove -Y if on its own
 677         $ARGV[0]=~s/^-Y/-/; # remove -Y if bundled with other options
 678         $ARGV[0]=~s/^--shebang //; # remove --shebang if it is set
 679         my $argfile = pop @ARGV;
 680         # exec myself to split @ARGV into separate fields
 681         exec "$0 --skip-first-line < $argfile @ARGV";
 682     }
 683     Getopt::Long::Configure ("bundling","require_order");
 684     GetOptions("passthrough|p=s@" => \$::opt_p,
 685                "sep|s=s" => \$::opt_s,
 686                "html" => \$::opt_html,
 687                "show-processlist|proclist|listproc|showqueries|show-queries"
 688                => \$::opt_processlist,
 689                "show-tables|showtables|listtables|list-tables|".
 690                "tablelist|table-list|show-measurements|showmeasurements|".
 691                "list-measurements|listmeasurements" => \$::opt_tablelist,
 692                "dblist|".
 693                "listdb|listdbs|list-db|list-dbs|list-database|".
 694                "list-databases|listdatabases|listdatabase|showdb|".
 695                "showdbs|show-db|show-dbs|show-database|show-databases|".
 696                "showdatabases|showdatabase" => \$::opt_dblist,
 697                "db-size|dbsize" => \$::opt_dbsize,
 698                "table-size|tablesize" => \$::opt_tablesize,
 699                "json|j" => \$::opt_json,
 700                "pretty" => \$::opt_pretty,
 701                "csv" => \$::opt_csv,
 702                "precision=s" => \$::opt_precision,
 703                "noheaders|no-headers|n" => \$::opt_n,
 704                "r" => \$::opt_retry,
 705                "retries=s" => \$::opt_retries,
 706                "debug|D" => \$::opt_debug,
 707                # Shebang #!/usr/bin/parallel -Yotheroptions
 708                "Y|shebang" => \$::opt_shebang,
 709                "skip-first-line" => \$::opt_skip_first_line,
 710                # GNU requirements
 711                "help|h" => \$::opt_help,
 712                "version|V" => \$::opt_version,
 713                "verbose|v" => \$::opt_verbose,
 714         ) || die_usage();
 715
 716     if(defined $::opt_help) { die_usage(); }
 717     if(defined $::opt_version) { version(); exit(0); }
 718     $Global::debug = $::opt_debug;
 719 }
 720
 721 sub database_driver_alias {
 722     my $driver = shift;
 723     my %database_driver_alias = ("mysql" => "mysql",
 724                                  "mysqls" => "mysqlssl",
 725                                  "mysqlssl" => "mysqlssl",
 726                                  "oracle" => "oracle",
 727                                  "ora" => "oracle",
 728                                  "oracles" => "oraclessl",
 729                                  "oras" => "oraclessl",
 730                                  "oraclessl" => "oraclessl",
 731                                  "orassl" => "oraclessl",
 732                                  "postgresql" => "postgresql",
 733                                  "pgsql" => "postgresql",
 734                                  "postgres" => "postgresql",
 735                                  "pg" => "postgresql",
 736                                  "postgresqlssl" => "postgresqlssl",
 737                                  "pgsqlssl" => "postgresqlssl",
 738                                  "postgresssl" => "postgresqlssl",
 739                                  "pgssl" => "postgresqlssl",
 740                                  "postgresqls" => "postgresqlssl",
 741                                  "pgsqls" => "postgresqlssl",
 742                                  "postgress" => "postgresqlssl",
 743                                  "pgs" => "postgresqlssl",
 744                                  "sqlite" => "sqlite",
 745                                  "sqlite2" => "sqlite",
 746                                  "sqlite3" => "sqlite3",
 747                                  "influx" => "influx",
 748                                  "influxdb" => "influx",
 749                                  "influxssl" => "influxssl",
 750                                  "influxdbssl" => "influxssl",
 751                                  "influxs" => "influxssl",
 752                                  "influxdbs" => "influxssl",
 753         );
 754     return $database_driver_alias{$driver};
 755 }
 756
 757 sub mysql_commands {
 758     my ($database_driver,%opt) = (@_);
 759     find_command_in_path("mysql") || die ("mysql not in path");
 760     if(defined($::opt_s)) { die "Field separator not implemented for mysql" }
 761     my $password = defined($opt{'password'}) ? "--password=".$opt{'password'} : "";
 762     my $host = defined($opt{'host'}) ? "--host=".$opt{'host'} : "";
 763     my $port = defined($opt{'port'}) ? "--port=".$opt{'port'} : "";
 764     my $user = defined($opt{'user'}) ? "--user=".$opt{'user'} : "";
 765     my $database = defined($opt{'database'}) ? $opt{'database'} : $ENV{'USER'};
 766     my $html = defined($::opt_html) ? "--html" : "";
 767     my $no_headers = defined($::opt_n) ? "--skip-column-names" : "";
 768     my $ssl = "";
 769     if ($database_driver eq "mysqlssl") { $ssl="--ssl"; }
 770     my($credential_fh,$tmp) = tempfile(SUFFIX => ".sql");
 771     chmod (0600,$tmp);
 772     print $credential_fh ("[client]\n",
 773                           $user && "user=$opt{'user'}\n",
 774                           $password && "password=$opt{'password'}\n",
 775                           $host && "host=$opt{'host'}\n");
 776     close $credential_fh;
 777
 778     # Prepend with a remover of the credential tempfile
 779     # -C: Compression if both ends support it
 780     $batch_command =
 781         "((sleep 1; rm $tmp) & ".
 782         "mysql --defaults-extra-file=$tmp -C $pass_through_options ".
 783         "$no_headers $html $ssl $host $user $port  $database)";
 784     $interactive_command = $batch_command;
 785     return($batch_command,$interactive_command);
 786 }
 787
 788 sub postgresql_commands {
 789     my ($database_driver,%opt) = (@_);
 790     find_command_in_path("psql") || die ("psql not in path");
 791     my $sep = ($::opt_s) ? "-A --field-separator '$::opt_s'" : "";
 792     my $password = defined($opt{'password'}) ?
 793         "PGPASSWORD=".$opt{'password'} : "";
 794     my $host = defined($opt{'host'}) ? "-h ".$opt{'host'} : "";
 795     my $port = defined($opt{'port'}) ? "-p ".$opt{'port'} : "";
 796     my $user = defined($opt{'user'}) ? "-U ".$opt{'user'} : "";
 797     my $database = defined($opt{'database'}) ? "-d ".$opt{'database'} : "";
 798     my $html = defined($::opt_html) ? "--html" : "";
 799     my $no_headers = defined($::opt_n) ? "--tuples-only" : "";
 800     my $ssl = "";
 801     if ($database_driver eq "postgresqlssl") { $ssl="PGSSLMODE=require"; }
 802     $batch_command =
 803         "$ssl $password psql $pass_through_options $sep $no_headers ".
 804         "$html $host $user $port $database";
 805     $interactive_command = $batch_command;
 806
 807     return($batch_command,$interactive_command);
 808 }
 809
 810 sub oracle_commands {
 811     my ($database_driver,%opt) = (@_);
 812     # oracle://user:pass@grum:1521/XE becomes:
 813     # sqlplus 'user/pass@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = grum)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XE) ))'
 814     my $sqlplus = find_command_in_path("sqlplus") ||
 815         find_command_in_path("sqlplus64") or
 816         die("sqlplus/sqlplus64 not in path");
 817
 818     # Readline support: if rlwrap installed run rlwrap sqlplus
 819     my $rlwrap = find_command_in_path("rlwrap");
 820
 821     # Set good defaults in the inifile
 822     $Global::Initfile = "/tmp/$$.sql.init";
 823     open(INIT,">".$Global::Initfile) || die;
 824     print INIT "SET LINESIZE 32767\n";
 825     $::opt_debug and print "SET LINESIZE 32767\n";
 826     print INIT "SET WRAP OFF\n";
 827     $::opt_debug and print "SET WRAP OFF\n";
 828     if(defined($::opt_html)) {
 829         print INIT "SET MARK HTML ON\n";
 830         $::opt_debug and print "SET MARK HTML ON\n";
 831     }
 832     if(defined($::opt_n)) {
 833         print INIT "SET PAGES 0\n";
 834         $::opt_debug and print "SET PAGES 0\n";
 835     } else {
 836         print INIT "SET PAGES 50000\n";
 837         $::opt_debug and print "SET PAGES 50000\n";
 838     }
 839     if(defined($::opt_s)) {
 840         print INIT "SET COLSEP $::opt_s\n";
 841         $::opt_debug and print "SET COLSEP $::opt_s\n";
 842     }
 843     close INIT;
 844
 845     my $password = defined($opt{'password'}) ? "/".$opt{'password'} : "";
 846     my $host = defined($opt{'host'}) ? $opt{'host'} : "localhost";
 847     my $port = defined($opt{'port'}) ? $opt{'port'} : "1521";
 848     my $user = defined($opt{'user'}) ? $opt{'user'} : "";
 849     # Database is called service in Oracle lingo
 850     my $service = defined($opt{'database'}) ? "(SERVICE_NAME = ".$opt{'database'}.")" : "";
 851     my $tns = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = $host)(PORT = $port)) ".
 852         "(CONNECT_DATA =(SERVER = DEDICATED)$service ))";
 853     my $ssl = "";
 854     # -L: Do not re-ask for password if it is wrong
 855     my $common_options = "-L $pass_through_options ".
 856         "'$user$password\@$tns' \@$Global::Initfile";
 857     my $batch_command = "$sqlplus -S ".$common_options;
 858     my $interactive_command = "$rlwrap $sqlplus ".$common_options;
 859     return($batch_command,$interactive_command);
 860 }
 861
 862 sub sqlite_commands {
 863     my ($database_driver,%opt) = (@_);
 864     if(not find_command_in_path($database_driver)) {
 865         print STDERR "Database driver '$database_driver' not supported\n";
 866         exit -1;
 867     }
 868     my $sep = defined($::opt_s) ? "-separator '$::opt_s'" : "";
 869     my $password = defined($opt{'password'}) ? "--password=".$opt{'password'} : "";
 870     my $host = defined($opt{'host'}) ? "--host=".$opt{'host'} : "";
 871     my $port = defined($opt{'port'}) ? "--port=".$opt{'port'} : "";
 872     my $user = defined($opt{'user'}) ? "--user=".$opt{'user'} : "";
 873     my $database = defined($opt{'database'}) ? $opt{'database'} : "";
 874     my $html = defined($::opt_html) ? "-html" : "";
 875     my $no_headers = defined($::opt_n) ? "-noheader" : "-header";
 876     my $ssl = "";
 877     $batch_command =
 878         "$database_driver $pass_through_options $sep ".
 879         "$no_headers $html $database";
 880     $interactive_command = $batch_command;
 881     return($batch_command,$interactive_command);
 882 }
 883
 884 sub influx_commands {
 885     my ($database_driver,%opt) = (@_);
 886     my $influx = find_command_in_path("influx") ||
 887         die ("influx not in path");
 888     if(defined($::opt_s)) {
 889         die "Field separator not implemented for influx";
 890     }
 891     my $password =
 892         defined($opt{'password'}) ? "-password=".$opt{'password'} : "";
 893     my $host = defined($opt{'host'}) ? "-host=".$opt{'host'} : "";
 894     my $port = defined($opt{'port'}) ? "-port=".$opt{'port'} : "";
 895     my $user = defined($opt{'user'}) ? "-username=".$opt{'user'} : "";
 896     my $database = defined($opt{'database'}) ?
 897         "-database $opt{'database'}" : "-database $ENV{'USER'}";
 898
 899     my $format = defined($::opt_json) ? "-format json" :
 900                  defined($::opt_pretty) ? "-format json -pretty" :
 901                  defined($::opt_csv) ? "-format csv" : "";
 902     my $precision = defined($::opt_precision) ?
 903         "-precision $::opt_precision" : "";
 904
 905     my $no_headers = defined($::opt_n) ? "--skip-column-names" : "";
 906     my $ssl = "";
 907     if($database_driver eq "influxssl") { $ssl="--ssl"; }
 908
 909     $batch_command =
 910         "$influx $pass_through_options $no_headers $format ".
 911         "$precision $ssl $host $user $password $port  $database";
 912     $interactive_command = $batch_command;
 913     return($batch_command,$interactive_command);
 914 }
 915
 916 # Return the code for 'show processlist' in the chosen database dialect
 917 sub processlist {
 918     my $dbdriver = shift;
 919     my %dburl = @_;
 920     my %statement =
 921         ("mysql" => "show processlist;",
 922          "postgresql" => ("SELECT ".
 923                           "    datname AS database,".
 924                           "    usename AS username,".
 925                           "    now()-xact_start AS runtime,".
 926                           "    current_query ".
 927                           "FROM pg_stat_activity ".
 928                           "ORDER BY runtime DESC;"),
 929          "oracle" => ("SELECT ".
 930                       '    CPU_TIME/100000, SYS.V_$SQL.SQL_TEXT, USERNAME '.
 931                       "FROM ".
 932                       '    SYS.V_$SQL, SYS.V_$SESSION '.
 933                       "WHERE ".
 934                       '    SYS.V_$SQL.SQL_ID = SYS.V_$SESSION.SQL_ID(+) '.
 935                       "AND username IS NOT NULL ".
 936                       "ORDER BY CPU_TIME DESC;"),
 937          "influx" => "show queries;",
 938         );
 939     if($statement{$dbdriver}) {
 940         return $statement{$dbdriver};
 941     } else {
 942         print STDERR "processlist is not implemented for $dbdriver\n";
 943         exit 1;
 944     }
 945 }
 946
 947 # Return the code for 'show tables' in the chosen database dialect
 948 sub tablelist {
 949     my $dbdriver = shift;
 950     my %dburl = @_;
 951     my %statement =
 952         ("mysql" => "show tables;",
 953          "postgresql" => '\dt',
 954          "oracle" => ("SELECT object_name ".
 955                       "FROM user_objects ".
 956                       "WHERE object_type = 'TABLE';"),
 957          "sqlite" => ".tables",
 958          "sqlite3" => ".tables",
 959          "influx" => "show measurements;",
 960         );
 961     if($statement{$dbdriver}) {
 962         return $statement{$dbdriver};
 963     } else {
 964         print STDERR "tablelist is not implemented for $dbdriver\n";
 965         exit 1;
 966     }
 967 }
 968
 969 # Return the code for 'show databases' in the chosen database dialect
 970 sub dblist {
 971     my $dbdriver = shift;
 972     my %dburl = @_;
 973     my %statement =
 974         ("mysql" => "show databases;",
 975          "postgresql" => ("SELECT datname FROM pg_database ".
 976                           "WHERE datname NOT IN ('template0','template1','postgres') ".
 977                           "ORDER BY datname ASC;"),
 978          "oracle" => ("select * from user_tablespaces;"),
 979          "influx" => "show databases;",
 980         );
 981     if($statement{$dbdriver}) {
 982         return $statement{$dbdriver};
 983     } else {
 984         print STDERR "dblist is not implemented for $dbdriver\n";
 985         exit 1;
 986     }
 987 }
 988
 989 # Return the code for 'show database size' in the chosen database dialect
 990 sub dbsize {
 991     my $dbdriver = shift;
 992     my %dburl = @_;
 993     my %statement;
 994     if(defined $dburl{'database'}) {
 995         %statement =
 996             ("mysql" => (
 997                  'SELECT '.
 998                  '    table_schema "database", '.
 999                  '    SUM(data_length+index_length) "bytes", '.
1000                  '    SUM(data_length+index_length)/1024/1024 "megabytes" '.
1001                  'FROM information_schema.TABLES '.
1002                  "WHERE table_schema = '$dburl{'database'}'".
1003                  'GROUP BY table_schema;'),
1004              "postgresql" => (
1005                  "SELECT '$dburl{'database'}' AS database, ".
1006                  "pg_database_size('$dburl{'database'}') AS bytes, ".
1007                  "pg_size_pretty(pg_database_size('$dburl{'database'}')) AS human_readabable "),
1008              "sqlite" => (
1009                  "SELECT ".(undef_as_zero(-s $dburl{'database'}))." AS bytes;"),
1010              "sqlite3" => (
1011                  "SELECT ".(undef_as_zero(-s $dburl{'database'}))." AS bytes;"),
1012             );
1013     } else {
1014         %statement =
1015             ("mysql" => (
1016                  'SELECT '.
1017                  '    table_schema "database", '.
1018                  '    SUM(data_length+index_length) "bytes", '.
1019                  '    SUM(data_length+index_length)/1024/1024 "megabytes" '.
1020                  'FROM information_schema.TABLES '.
1021                  'GROUP BY table_schema;'),
1022              "postgresql" => (
1023                  'SELECT datname AS database, pg_database_size(datname) AS bytes, '.
1024                  'pg_size_pretty(pg_database_size(datname)) AS human_readabable '.
1025                  'FROM (SELECT datname FROM pg_database) a;'),
1026              "sqlite" => (
1027                  "SELECT 0 AS bytes;"),
1028              "sqlite3" => (
1029                  "SELECT 0 AS bytes;"),
1030             );
1031     }
1032     if($statement{$dbdriver}) {
1033         return $statement{$dbdriver};
1034     } else {
1035         print STDERR "dbsize is not implemented for $dbdriver\n";
1036         exit 1;
1037     }
1038 }
1039
1040
1041 # Return the code for 'show table size' in the chosen database dialect
1042 sub tablesize {
1043     my $dbdriver = shift;
1044     my $database = shift;
1045     my %statement =
1046         ("postgresql" => (
1047              "SELECT relname, relpages*8 AS kb, reltuples::int AS \"live+dead rows\" ".
1048              "FROM pg_class c ".
1049              "ORDER BY relpages DESC;"),
1050          "mysql" => (
1051              "select table_name, TABLE_ROWS, DATA_LENGTH,INDEX_LENGTH from INFORMATION_SCHEMA.tables;"),
1052         );
1053     if($statement{$dbdriver}) {
1054         return $statement{$dbdriver};
1055     } else {
1056         print STDERR "table size is not implemented for $dbdriver\n";
1057         exit 1;
1058     }
1059 }
1060
1061 sub is_stdin_terminal {
1062     return (-t STDIN);
1063 }
1064
1065 sub find_command_in_path {
1066     # Find the command if it exists in the current path
1067     my $command = shift;
1068     my $path = `which $command`;
1069     chomp $path;
1070     return $path;
1071 }
1072
1073 sub Usage {
1074     if(@_) {
1075         print "Error:\n";
1076         print map{ "$_\n" } @_;
1077         print "\n";
1078     }
1079     print "sql [-hnr] [--table-size] [--db-size] [-p pass-through] [-s string] dburl [command]\n";
1080 }
1081
1082 sub get_alias {
1083     my $alias = shift;
1084     $alias =~ s/^(sql:)*//; # Accept aliases prepended with sql:
1085     if ($alias !~ /^:/) {
1086         return $alias;
1087     }
1088
1089     # Find the alias
1090     my $path;
1091     if (-l $0) {
1092         ($path) = readlink($0) =~ m|^(.*)/|;
1093     } else {
1094         ($path) = $0 =~ m|^(.*)/|;
1095     }
1096
1097     my @deprecated = ("$ENV{HOME}/.dburl.aliases",
1098                       "$path/dburl.aliases", "$path/dburl.aliases.dist");
1099     for (@deprecated) {
1100         if(-r $_) {
1101             print STDERR "$_ is deprecated. Use .sql/aliases instead (read man sql)\n";
1102         }
1103     }
1104     my @urlalias=();
1105     check_permissions("$ENV{HOME}/.sql/aliases");
1106     check_permissions("$ENV{HOME}/.dburl.aliases");
1107     my @search = ("$ENV{HOME}/.sql/aliases",
1108                   "$ENV{HOME}/.dburl.aliases", "/etc/sql/aliases",
1109                   "$path/dburl.aliases", "$path/dburl.aliases.dist");
1110     for my $alias_file (@search) {
1111         if(-r $alias_file) {
1112             push @urlalias, `cat "$alias_file"`;
1113         }
1114     }
1115     my ($alias_part,$rest) = $alias=~/(:\w*)(.*)/;
1116     # If we saw this before: we have an alias loop
1117     if(grep {$_ eq $alias_part } @Private::seen_aliases) {
1118         print STDERR "$alias_part is a cyclic alias\n";
1119         exit -1;
1120     } else {
1121         push @Private::seen_aliases, $alias_part;
1122     }
1123
1124     my $dburl;
1125     for (@urlalias) {
1126         /^$alias_part\s+(\S+.*)/ and do { $dburl = $1; last; }
1127     }
1128
1129     if($dburl) {
1130         return get_alias($dburl.$rest);
1131     } else {
1132         Usage("$alias is not defined in @search");
1133         exit(-1);
1134     }
1135 }
1136
1137 sub check_permissions {
1138     my $file = shift;
1139
1140     if(-e $file) {
1141         if(not -o $file) {
1142             my $username = (getpwuid($<))[0];
1143             print STDERR "$file should be owned by $username: chown $username $file\n";
1144         }
1145         my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,
1146             $atime,$mtime,$ctime,$blksize,$blocks) = stat($file);
1147         if($mode & 077) {
1148             my $username = (getpwuid($<))[0];
1149             print STDERR "$file should be only be readable by $username: chmod 600 $file\n";
1150         }
1151     }
1152 }
1153
1154 sub parse_dburl {
1155     my $url = shift;
1156     my %options = ();
1157     # sql:mysql://[[user][:password]@][host][:port]/[database[?sql query]]
1158
1159     if($url=~m!(?:sql:)? # You can prefix with 'sql:'
1160                ((?:oracle|ora|mysql|pg|postgres|postgresql|influx|influxdb)(?:s|ssl|)|
1161                  (?:sqlite|sqlite2|sqlite3)):// # Databasedriver ($1)
1162                (?:
1163                 ([^:@/][^:@]*|) # Username ($2)
1164                 (?:
1165                  :([^@]*) # Password ($3)
1166                 )?
1167                @)?
1168                ([^:/]*)? # Hostname ($4)
1169                (?:
1170                 :
1171                 ([^/]*)? # Port ($5)
1172                )?
1173                (?:
1174                 /
1175                 ([^?/]*)? # Database ($6)
1176                )?
1177                (?:
1178                 /?
1179                 \?
1180                 (.*)? # Query ($7)
1181                )?
1182               !x) {
1183         $options{databasedriver} = undef_if_empty(uri_unescape($1));
1184         $options{user} = undef_if_empty(uri_unescape($2));
1185         $options{password} = undef_if_empty(uri_unescape($3));
1186         $options{host} = undef_if_empty(uri_unescape($4));
1187         $options{port} = undef_if_empty(uri_unescape($5));
1188         $options{database} = undef_if_empty(uri_unescape($6))
1189             || $options{user};
1190         $options{query} = undef_if_empty(uri_unescape($7));
1191         debug("dburl $url\n");
1192         debug("databasedriver ",$options{databasedriver}, " user ", $options{user},
1193               " password ", $options{password}, " host ", $options{host},
1194               " port ", $options{port}, " database ", $options{database},
1195               " query ",$options{query}, "\n");
1196     } else {
1197         Usage("$url is not a valid DBURL");
1198         exit -1;
1199     }
1200     return %options;
1201 }
1202
1203 sub uri_unescape {
1204     # Copied from http://cpansearch.perl.org/src/GAAS/URI-1.55/URI/Escape.pm
1205     # to avoid depending on URI::Escape
1206     # This section is (C) Gisle Aas.
1207     # Note from RFC1630:  "Sequences which start with a percent sign
1208     # but are not followed by two hexadecimal characters are reserved
1209     # for future extension"
1210     my $str = shift;
1211     if (@_ && wantarray) {
1212         # not executed for the common case of a single argument
1213         my @str = ($str, @_);  # need to copy
1214         foreach (@str) {
1215             s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg;
1216         }
1217         return @str;
1218     }
1219     $str =~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg if defined $str;
1220     $str;
1221 }
1222
1223 sub undef_if_empty {
1224     if(defined($_[0]) and $_[0] eq "") {
1225         return undef;
1226     }
1227     return $_[0];
1228 }
1229
1230 sub undef_as_zero {
1231     my $a = shift;
1232     return $a ? $a : 0;
1233 }
1234
1235 sub version {
1236     # Returns: N/A
1237     print join("\n",
1238                "GNU $Global::progname $Global::version",
1239                "Copyright (C) 2009,2010,2011,2012,2013,2014,2015,2016,2017",
1240                "Ole Tange and Free Software Foundation, Inc.",
1241                "License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>",
1242                "This is free software: you are free to change and redistribute it.",
1243                "GNU $Global::progname comes with no warranty.",
1244                "",
1245                "Web site: http://www.gnu.org/software/${Global::progname}\n",
1246                "When using GNU $Global::progname for a publication please cite:\n",
1247                "O. Tange (2011): GNU SQL - A Command Line Tool for Accessing Different",
1248                "Databases Using DBURLs, ;login: The USENIX Magazine, April 2011:29-32.\n"
1249         );
1250 }
1251
1252 sub die_usage {
1253     # Returns: N/A
1254     usage();
1255     exit(255);
1256 }
1257
1258 sub usage {
1259     # Returns: N/A
1260     print "Usage:\n";
1261     print "$Global::progname [options] dburl [sqlcommand]\n";
1262     print "$Global::progname [options] dburl < sql_command_file\n";
1263     print "\n";
1264     print "See 'man $Global::progname' for the options\n";
1265 }
1266
1267 sub debug {
1268     # Returns: N/A
1269     $Global::debug or return;
1270     @_ = grep { defined $_ ? $_ : "" } @_;
1271     print @_;
1272 }
1273
1274 $::opt_skip_first_line = $::opt_shebang = 0;