sort-sqlite

   1 #!/usr/bin/env perl
   2
   3 #=======================================================================
   4 # sort-sqlite
   5 # File ID: 0f3555b6-70fd-11e5-89be-fefdb24f8e10
   6 #
   7 # Sort the rows in an SQLite database.
   8 #
   9 # Character set: UTF-8
  10 # ©opyleft 2015– Øyvind A. Holm <sunny@sunbase.org>
  11 # License: GNU General Public License version 2 or later, see end of
  12 # file for legal stuff.
  13 #=======================================================================
  14
  15 use strict;
  16 use warnings;
  17 use Getopt::Long;
  18 use File::Copy;
  19 use IPC::Open3;
  20
  21 local $| = 1;
  22
  23 our %Opt = (
  24
  25     'help' => 0,
  26     'quiet' => 0,
  27     'verbose' => 0,
  28     'version' => 0,
  29
  30 );
  31
  32 our $progname = $0;
  33 $progname =~ s/^.*\/(.*?)$/$1/;
  34 our $VERSION = '0.2.1';
  35
  36 my $ERROR = 0;
  37 my $OK = 1;
  38
  39 my $SQLITE = "sqlite3";
  40
  41 my @Columns = ();
  42
  43 Getopt::Long::Configure('bundling');
  44 GetOptions(
  45
  46     'column|c=s' => \@Columns,
  47     'help|h' => \$Opt{'help'},
  48     'quiet|q+' => \$Opt{'quiet'},
  49     'verbose|v+' => \$Opt{'verbose'},
  50     'version' => \$Opt{'version'},
  51
  52 ) || die("$progname: Option error. Use -h for help.\n");
  53
  54 $Opt{'verbose'} -= $Opt{'quiet'};
  55 $Opt{'help'} && usage(0);
  56 if ($Opt{'version'}) {
  57     print_version();
  58     exit(0);
  59 }
  60
  61 my $sql_error = 0; # Is set to !0 if some sqlite3 error happened
  62
  63 exit(main());
  64
  65 sub main {
  66     if (!scalar(@Columns)) {
  67         warn("$progname: Missing -c/--column option\n");
  68         return 1;
  69     }
  70     $ENV{'LC_COLLATE'} = 'C';
  71     for my $file (@ARGV) {
  72         sort_file($file, @Columns) || return 1;
  73     }
  74     return 0;
  75 } # main()
  76
  77 sub sort_file {
  78     # {{{
  79     my ($file, @tabcol) = @_;
  80     my $tmptable = "sortsqltmp";
  81     my $retval = $OK;
  82     my $c;
  83
  84     for $c (@tabcol) {
  85         if ($c !~ /^(\S+?)\.(\S+)$/) {
  86             warn("$progname: \"$c\" is not a valid identifier\n");
  87             return $ERROR;
  88         }
  89     }
  90
  91     if (!-r $file || !-f $file) {
  92         warn("$progname: $file: File is not readable by you or " .
  93              "is not a regular file\n");
  94         return $ERROR;
  95     }
  96
  97     my $backup = sprintf("%s.%s.bck", $file, sec_to_string(mod_date($file)));
  98     if (!copy($file, $backup)) {
  99         warn("$progname: $backup: Cannot create backup file: $!\n");
 100         return $ERROR;
 101     }
 102
 103     msg(1, "Sorting $file");
 104     for $c (@tabcol) {
 105         my ($table, $column);
 106
 107         if ($c =~ /^(\S+?)\.(\S+)$/) {
 108             ($table, $column) = ($1, $2);
 109         } else {
 110             warn("$progname: This should not happen, invalid identifier " .
 111                  "\"$c\" even though it has been validated\n");
 112             return $ERROR;
 113         }
 114         sql($file, "
 115 BEGIN EXCLUSIVE TRANSACTION;
 116
 117 CREATE TEMPORARY TABLE \"$tmptable\" AS
 118   SELECT * FROM \"$table\";
 119 DELETE FROM \"$table\";
 120 INSERT INTO \"$table\"
 121   SELECT * FROM \"$tmptable\" ORDER BY \"$column\";
 122 DROP TABLE \"$tmptable\";
 123
 124 COMMIT TRANSACTION;
 125 ");
 126     }
 127     if ($sql_error) {
 128         warn("$progname: $file: $SQLITE error, aborting\n");
 129         return $ERROR;
 130     }
 131
 132     return $OK;
 133     # }}}
 134 } # sort_file()
 135
 136 sub mod_date {
 137     # Return file modification timestamp {{{
 138     my $File = shift;
 139     my $Retval = "";
 140     my @stat_array = stat($File);
 141     if (scalar(@stat_array)) {
 142         $Retval = $stat_array[9];
 143     } else {
 144         warn("$progname: $File: Cannot stat file: $!\n");
 145     }
 146     return $Retval;
 147     # }}}
 148 } # mod_date()
 149
 150 sub sec_to_string {
 151     # Convert seconds since 1970 to "yyyymmddThhmmss[.frac]Z" {{{
 152     my ($Seconds, $Sep) = @_;
 153     length($Seconds) || return '';
 154     ($Seconds =~ /^-?(\d*)(\.\d+)?$/) || return undef;
 155     my $Secfrac = ($Seconds =~ /^([\-\d]*)(\.\d+)$/) ? 1.0*$2 : "";
 156     $Secfrac =~ s/^0//;
 157
 158     defined($Sep) || ($Sep = " ");
 159     my @TA = gmtime($Seconds);
 160     my($DateString) = sprintf("%04u%02u%02uT%02u%02u%02u%sZ",
 161                               $TA[5]+1900, $TA[4]+1, $TA[3],
 162                               $TA[2], $TA[1], $TA[0], $Secfrac);
 163     return $DateString;
 164     # }}}
 165 } # sec_to_string()
 166
 167 sub sql {
 168     # {{{
 169     my ($db, $sql) = @_;
 170     my @retval = ();
 171
 172     msg(3, "sql(): db = '$db'");
 173     local(*CHLD_IN, *CHLD_OUT, *CHLD_ERR);
 174
 175     $sql_error = 0;
 176     my $pid = open3(*CHLD_IN, *CHLD_OUT, *CHLD_ERR, $SQLITE, $db) or (
 177         $sql_error = 1,
 178         msg(0, "sql(): open3() error: $!"),
 179         return "sql() error",
 180     );
 181     msg(3, "sql(): sql = '$sql'");
 182     print(CHLD_IN "$sql\n") or msg(0, "sql(): print CHLD_IN error: $!");
 183     close(CHLD_IN);
 184     @retval = <CHLD_OUT>;
 185     msg(3, "sql(): retval = '" . join('|', @retval) . "'");
 186     my @child_stderr = <CHLD_ERR>;
 187     if (scalar(@child_stderr)) {
 188         msg(0, "$SQLITE error: " . join('', @child_stderr));
 189         $sql_error = 1;
 190     }
 191     return join('', @retval);
 192     # }}}
 193 } # sql()
 194
 195 sub print_version {
 196     # Print program version {{{
 197     print("$progname $VERSION\n");
 198     return;
 199     # }}}
 200 } # print_version()
 201
 202 sub usage {
 203     # Send the help message to stdout {{{
 204     my $Retval = shift;
 205
 206     if ($Opt{'verbose'}) {
 207         print("\n");
 208         print_version();
 209     }
 210     print(<<"END");
 211
 212 Sort the rows in an SQLite database. A backup of the previous version is
 213 copied to a *.bck file containing the date of the file modification time
 214 in the file name.
 215
 216 Usage: $progname [options] SQLite_database_file [...]
 217
 218 Options:
 219
 220   -c TABLE.COLUMN, --column TABLE.COLUMN
 221     Sort COLUMN in TABLE. This option can be repeated to sort several
 222     columns.
 223   -h, --help
 224     Show this help.
 225   -q, --quiet
 226     Be more quiet. Can be repeated to increase silence.
 227   -v, --verbose
 228     Increase level of verbosity. Can be repeated.
 229   --version
 230     Print version information.
 231
 232 END
 233     exit($Retval);
 234     # }}}
 235 } # usage()
 236
 237 sub msg {
 238     # Print a status message to stderr based on verbosity level {{{
 239     my ($verbose_level, $Txt) = @_;
 240
 241     if ($Opt{'verbose'} >= $verbose_level) {
 242         print(STDERR "$progname: $Txt\n");
 243     }
 244     return;
 245     # }}}
 246 } # msg()
 247
 248 __END__
 249
 250 # This program is free software; you can redistribute it and/or modify
 251 # it under the terms of the GNU General Public License as published by
 252 # the Free Software Foundation; either version 2 of the License, or (at
 253 # your option) any later version.
 254 #
 255 # This program is distributed in the hope that it will be useful, but
 256 # WITHOUT ANY WARRANTY; without even the implied warranty of
 257 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 258 # See the GNU General Public License for more details.
 259 #
 260 # You should have received a copy of the GNU General Public License
 261 # along with this program.
 262 # If not, see L<http://www.gnu.org/licenses/>.
 263
 264 # vim: set fenc=UTF-8 ft=perl fdm=marker ts=4 sw=4 sts=4 et fo+=w :