| 1 | # Copyright 2008, 2009, 2010, 2011, 2013, 2014, 2016 Kevin Ryde | ||
| 2 | |||
| 3 | # This file is part of Chart. | ||
| 4 | # | ||
| 5 | # Chart is free software; you can redistribute it and/or modify it under the | ||
| 6 | # terms of the GNU General Public License as published by the Free Software | ||
| 7 | # Foundation; either version 3, or (at your option) any later version. | ||
| 8 | # | ||
| 9 | # Chart is distributed in the hope that it will be useful, but WITHOUT ANY | ||
| 10 | # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS | ||
| 11 | # FOR A PARTICULAR PURPOSE. See the GNU General Public License for more | ||
| 12 | # details. | ||
| 13 | # | ||
| 14 | # You should have received a copy of the GNU General Public License along | ||
| 15 | # with Chart. If not, see <http://www.gnu.org/licenses/>. | ||
| 16 | |||
| 17 | package App::Chart::Vacuum; | ||
| 18 | use 5.010; | ||
| 19 | use strict; | ||
| 20 | use warnings; | ||
| 21 | use Carp; | ||
| 22 | use File::stat; | ||
| 23 | use Locale::TextDomain ('App-Chart'); | ||
| 24 | |||
| 25 | use PerlIO::via::EscStatus; | ||
| 26 | |||
| 27 | use App::Chart::Annotation; | ||
| 28 | use App::Chart::Database; | ||
| 29 | use App::Chart::DBI; | ||
| 30 | use App::Chart::Download; | ||
| 31 | use App::Chart::Gtk2::Symlist::Alerts; | ||
| 32 | use App::Chart::Gtk2::Symlist::All; | ||
| 33 | use App::Chart::Gtk2::Symlist::Historical; | ||
| 34 | |||
| 35 | use constant VACUUM_AGE_DAYS => 14; | ||
| 36 | |||
| 37 | my $verbose = 0; | ||
| 38 | |||
| 39 | sub command_line_vacuum { | ||
| 40 | my ($class, $output, $args) = @_; | ||
| 41 | |||
| 42 | if ($output eq 'tty') { | ||
| 43 | if (-t STDOUT) { | ||
| 44 | binmode (STDOUT, ':via(EscStatus)') | ||
| 45 | or die 'Cannot push EscStatus'; | ||
| 46 | } else { | ||
| 47 | require PerlIO::via::EscStatus::ShowNone; | ||
| 48 | binmode (STDOUT, ':via(EscStatus::ShowNone)') | ||
| 49 | or die 'Cannot push EscStatus::ShowNone'; | ||
| 50 | } | ||
| 51 | } elsif ($output eq 'all-status') { | ||
| 52 | require PerlIO::via::EscStatus::ShowAll; | ||
| 53 | binmode (STDOUT, ':via(EscStatus::ShowAll)') | ||
| 54 | or die 'Cannot push EscStatus::ShowAll'; | ||
| 55 | } | ||
| 56 | |||
| 57 | my %option; | ||
| 58 | foreach my $arg (@$args) { | ||
| 59 | if ($arg =~ /^no-?/ip) { | ||
| 60 | $option{${^POSTMATCH}} = 0; | ||
| 61 | } else { | ||
| 62 | $option{$arg} = 1; | ||
| 63 | } | ||
| 64 | } | ||
| 65 | vacuum(%option); | ||
| 66 | } | ||
| 67 | |||
| 68 | sub vacuum { | ||
| 69 | my %option = @_; | ||
| 70 | if (! exists $option{'compact'}) { $option{'compact'} = 1; } | ||
| 71 | if (! exists $option{'consistency'}) { $option{'consistency'} = 1; } | ||
| 72 | |||
| 73 | $verbose = $App::Chart::option{'verbose'}; | ||
| 74 | if (exists $option{'verbose'}) { $verbose = $option{'verbose'}; } | ||
| 75 | |||
| 76 | App::Chart::Download::status (__('Vacuuming database')); | ||
| 77 | |||
| 78 | expire_latest(); | ||
| 79 | expire_intraday(); | ||
| 80 | |||
| 81 | if ($option{'consistency'}) { | ||
| 82 | check_listseq(); | ||
| 83 | check_alerts(); | ||
| 84 | check_historical(); | ||
| 85 | check_alphabetical(); | ||
| 86 | } | ||
| 87 | if ($option{'compact'}) { | ||
| 88 | vacuum_database(); | ||
| 89 | vacuum_notes(); | ||
| 90 | } | ||
| 91 | } | ||
| 92 | |||
| 93 | sub vacuum_notes { | ||
| 94 | my $notes_filename = App::Chart::DBI::notes_filename(); | ||
| 95 | my $notes_oldsize = -s $notes_filename; | ||
| 96 | App::Chart::Download::status (__x('VACUUM notes.sqdb ({oldsize} bytes)', | ||
| 97 | oldsize => $notes_oldsize)); | ||
| 98 | |||
| 99 | require DBI; | ||
| 100 | my $nbh = DBI->connect ("dbi:SQLite:dbname=$notes_filename", | ||
| 101 | '', '', {RaiseError=>1}); | ||
| 102 | $nbh->func(90_000, 'busy_timeout'); # 90 seconds | ||
| 103 | $nbh->{sqlite_unicode} = 1; | ||
| 104 | $nbh->do ('VACUUM'); | ||
| 105 | my $notes_newsize = -s $notes_filename; | ||
| 106 | print __x("Notes was {oldsize} now {newsize} bytes\n", | ||
| 107 | oldsize => $notes_oldsize, | ||
| 108 | newsize => $notes_newsize); | ||
| 109 | } | ||
| 110 | |||
| 111 | sub vacuum_database { | ||
| 112 | my $dbh = App::Chart::DBI->instance; | ||
| 113 | my $database_filename = App::Chart::DBI::database_filename(); | ||
| 114 | my $database_oldsize = -s $database_filename; | ||
| 115 | App::Chart::Download::status (__x('VACUUM database.sqdb ({oldsize} bytes)', | ||
| 116 | oldsize => $database_oldsize)); | ||
| 117 | $dbh->do ('VACUUM'); | ||
| 118 | my $database_newsize = -s $database_filename; | ||
| 119 | print __x("Database was {oldsize} now {newsize} bytes\n", | ||
| 120 | oldsize => $database_oldsize, | ||
| 121 | newsize => $database_newsize); | ||
| 122 | } | ||
| 123 | |||
| 124 | # old latest records discarded, except symbols in the database kept | ||
| 125 | # indefinitely, which in particular is for when the latest in fact comes | ||
| 126 | # from the daily data | ||
| 127 | # | ||
| 128 | sub expire_latest { | ||
| 129 | App::Chart::Download::status (__('Delete old "latest" quotes')); | ||
| 130 | |||
| 131 | my $dbh = App::Chart::DBI->instance; | ||
| 132 | my $age_seconds = 86400 * VACUUM_AGE_DAYS; | ||
| 133 | my @timestamp_range = App::Chart::Download::timestamp_range ($age_seconds); | ||
| 134 | |||
| 135 | # "0+" avoids 0E0 when no records deleted | ||
| 136 | my $n = 0 + $dbh->do | ||
| 137 | ('DELETE FROM latest | ||
| 138 | WHERE fetch_timestamp < ? OR fetch_timestamp > ? | ||
| 139 | AND NOT EXISTS (SELECT * FROM info WHERE info.symbol=latest.symbol)', | ||
| 140 | undef, | ||
| 141 | @timestamp_range); | ||
| 142 | my ($kept) = $dbh->selectrow_array ('SELECT COUNT(*) FROM latest'); | ||
| 143 | |||
| 144 | print __nx("deleted {n} old latest record (leaving {kept})\n", | ||
| 145 | "deleted {n} old latest records (leaving {kept})\n", | ||
| 146 | $n, | ||
| 147 | n => $n, | ||
| 148 | kept => $kept); | ||
| 149 | } | ||
| 150 | |||
| 151 | sub expire_intraday { | ||
| 152 | App::Chart::Download::status (__('Delete old intraday images')); | ||
| 153 | |||
| 154 | my $dbh = App::Chart::DBI->instance; | ||
| 155 | my $age_seconds = 86400 * VACUUM_AGE_DAYS; | ||
| 156 | my @timestamp_range = App::Chart::Download::timestamp_range ($age_seconds); | ||
| 157 | |||
| 158 | # "0+" avoids 0E0 when no records deleted | ||
| 159 | my $n = 0 + $dbh->do | ||
| 160 | ('DELETE FROM intraday_image | ||
| 161 | WHERE fetch_timestamp < ? OR fetch_timestamp > ?', | ||
| 162 | undef, | ||
| 163 | @timestamp_range); | ||
| 164 | my ($kept) = $dbh->selectrow_array ('SELECT COUNT(*) FROM intraday_image'); | ||
| 165 | |||
| 166 | print __nx("deleted {n} old intraday image (leaving {kept})\n", | ||
| 167 | "deleted {n} old intraday images (leaving {kept})\n", | ||
| 168 | $n, | ||
| 169 | n => $n, | ||
| 170 | kept => $kept); | ||
| 171 | } | ||
| 172 | |||
| 173 | sub check_listseq { | ||
| 174 | foreach my $symlist (App::Chart::Gtk2::Symlist->all_lists) { | ||
| 175 | $symlist->fixup (verbose => $verbose, | ||
| 176 | message => sub { | ||
| 177 | my ($message) = @_; | ||
| 178 | print $symlist->name, ": $message\n"; | ||
| 179 | }); | ||
| 180 | } | ||
| 181 | } | ||
| 182 | |||
| 183 | sub check_alerts { | ||
| 184 | App::Chart::Download::status (__('Check Alerts list contents')); | ||
| 185 | |||
| 186 | my $symlist = App::Chart::Gtk2::Symlist::Alerts->instance; | ||
| 187 | |||
| 188 | my @symbol_list = $symlist->interested_symbols; | ||
| 189 | if ($verbose) { | ||
| 190 | print "Alerts interested:", join(' ',@symbol_list),"\n"; | ||
| 191 | } | ||
| 192 | |||
| 193 | foreach my $symbol (@symbol_list) { | ||
| 194 | my $want = App::Chart::Annotation::Alert::want_alert ($symbol); | ||
| 195 | my $got = $symlist->contains_symbol ($symbol); | ||
| 196 | if ($want && ! $got) { | ||
| 197 | if ($verbose) { | ||
| 198 | print " $symbol: should be in Alerts, fixing\n"; | ||
| 199 | } | ||
| 200 | } elsif (! $want && $got) { | ||
| 201 | if ($verbose) { | ||
| 202 | print " $symbol: should not be in Alerts, fixing\n"; | ||
| 203 | } | ||
| 204 | } | ||
| 205 | App::Chart::Annotation::Alert::update_alert ($symbol); | ||
| 206 | } | ||
| 207 | |||
| 208 | @symbol_list = $symlist->symbols; | ||
| 209 | if ($verbose) { | ||
| 210 | print "Alerts now: ", join(' ',@symbol_list),"\n"; | ||
| 211 | } | ||
| 212 | } | ||
| 213 | |||
| 214 | sub check_historical { | ||
| 215 | App::Chart::Download::status (__('Check Historical list contents')); | ||
| 216 | |||
| 217 | my $symlist = App::Chart::Gtk2::Symlist::Historical->instance; | ||
| 218 | |||
| 219 | my @symbol_list = App::Chart::Database->symbols_list(); | ||
| 220 | if ($verbose) { | ||
| 221 | print "Historical check:", join(' ',@symbol_list),"\n"; | ||
| 222 | } | ||
| 223 | |||
| 224 | foreach my $symbol (@symbol_list) { | ||
| 225 | my $want = App::Chart::Download::want_historical ($symbol); | ||
| 226 | my $got = $symlist->contains_symbol ($symbol); | ||
| 227 | if (defined $want && ! $got) { | ||
| 228 | if ($verbose) { | ||
| 229 | print " $symbol: should be historical: $want\n"; | ||
| 230 | } | ||
| 231 | App::Chart::Download::consider_historical ([$symbol]); | ||
| 232 | |||
| 233 | } elsif (! defined $want && $got) { | ||
| 234 | if ($verbose) { | ||
| 235 | print " $symbol: should not be historical, fixing\n"; | ||
| 236 | } | ||
| 237 | # Symbol not historical exists, so let add_symbol() do its raising out | ||
| 238 | # of historical (like in App::Chart::Download::download()). | ||
| 239 | App::Chart::Database->add_symbol ($symbol); | ||
| 240 | } | ||
| 241 | } | ||
| 242 | |||
| 243 | @symbol_list = $symlist->symbols; | ||
| 244 | if ($verbose) { | ||
| 245 | print "Historical now: ", join(' ',@symbol_list),"\n"; | ||
| 246 | } | ||
| 247 | } | ||
| 248 | |||
| 249 | sub check_alphabetical { | ||
| 250 | App::Chart::Download::status (__('Check alphabetical symlists order')); | ||
| 251 | |||
| 252 | foreach my $symlist (App::Chart::Gtk2::Symlist::all_lists()) { | ||
| 253 | next unless $symlist->isa('App::Chart::Gtk2::Symlist::Alphabetical'); | ||
| 254 | |||
| 255 | if ($verbose) { | ||
| 256 | print $symlist->key, " ", $symlist->name, "\n"; | ||
| 257 | my @symbol_list = $symlist->symbols; | ||
| 258 | print " ",join(' ', @symbol_list), "\n"; | ||
| 259 | } | ||
| 260 | |||
| 261 | if (! symlist_is_alphabetical_ok ($symlist)) { | ||
| 262 | symlist_re_alphabetize ($symlist); | ||
| 263 | } | ||
| 264 | } | ||
| 265 | } | ||
| 266 | sub symlist_is_alphabetical_ok { | ||
| 267 | my ($symlist) = @_; | ||
| 268 | my @symbol_list = $symlist->symbols; | ||
| 269 | |||
| 270 | foreach my $i (1 .. $#symbol_list) { | ||
| 271 | my $prev = $symbol_list[$i-1]; | ||
| 272 | my $this = $symbol_list[$i]; | ||
| 273 | my $order = App::Chart::symbol_cmp ($this, $prev); | ||
| 274 | if ($order < 0) { | ||
| 275 | print $symlist->name," '$this' should be before '$prev', fixing whole list\n"; | ||
| 276 | return 0; | ||
| 277 | } | ||
| 278 | if ($order == 0) { | ||
| 279 | print $symlist->name," '$this' duplicated, fixing whole list\n"; | ||
| 280 | return 0; | ||
| 281 | } | ||
| 282 | } | ||
| 283 | if ($verbose) { | ||
| 284 | print " good\n"; | ||
| 285 | } | ||
| 286 | return 1; | ||
| 287 | } | ||
| 288 | sub symlist_re_alphabetize { | ||
| 289 | my ($symlist) = @_; | ||
| 290 | my @symbol_list = $symlist->symbols; | ||
| 291 | if ($verbose) { | ||
| 292 | print " ",join(' ', @symbol_list), "\n"; | ||
| 293 | } | ||
| 294 | my $dbh = App::Chart::DBI->instance; | ||
| 295 | App::Chart::Database::call_with_transaction | ||
| 296 | ($dbh, sub { | ||
| 297 | foreach my $symbol (reverse @symbol_list) { | ||
| 298 | $symlist->delete_symbol ($symbol); | ||
| 299 | } | ||
| 300 | foreach my $symbol (@symbol_list) { | ||
| 301 | $symlist->insert_symbol ($symbol); | ||
| 302 | } | ||
| 303 | }); | ||
| 304 | |||
| 305 | @symbol_list = $symlist->symbols; | ||
| 306 | if ($verbose) { | ||
| 307 | print " Now: ",join(' ', @symbol_list), "\n"; | ||
| 308 | } | ||
| 309 | } | ||
| 310 | |||
| 311 | 1; | ||
| 312 | __END__ |