| 1 | # Copyright 2007, 2008, 2009, 2010, 2011, 2012, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2023, 2024 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::Series::Database; | ||
| 18 | use 5.010; | ||
| 19 | use strict; | ||
| 20 | use warnings; | ||
| 21 | use Carp; | ||
| 22 | use Scalar::Util; | ||
| 23 | |||
| 24 | use App::Chart::Database; | ||
| 25 | use App::Chart::DBI; | ||
| 26 | use base 'App::Chart::Series::OHLCVI'; | ||
| 27 | |||
| 28 | our $VERSION = 275; | ||
| 29 | |||
| 30 | use constant DEBUG => 0; | ||
| 31 | |||
| 32 | # %cache is keyed by symbol string, with value a App::Chart::Series::Database | ||
| 33 | # object. The value reference is weakened so it becomes undef when | ||
| 34 | # otherwise unused. | ||
| 35 | # | ||
| 36 | our %cache = (); | ||
| 37 | |||
| 38 | sub _purge_cache_on_data_changed { | ||
| 39 | my ($symbol_hash) = @_; | ||
| 40 | if (DEBUG) { | ||
| 41 | print "data-changed, purge series: ", | ||
| 42 | join (', ', grep {exists $cache{$_}} keys %$symbol_hash),"\n"; | ||
| 43 | } | ||
| 44 | delete @cache{keys %$symbol_hash}; # hash slice | ||
| 45 | } | ||
| 46 | use constant::defer _init_cache => sub { | ||
| 47 | App::Chart::chart_dirbroadcast()->connect_first | ||
| 48 | ('data-changed', \&_purge_cache_on_data_changed); | ||
| 49 | return; | ||
| 50 | }; | ||
| 51 | |||
| 52 | sub new { | ||
| 53 | my ($class, $symbol) = @_; | ||
| 54 | if (DEBUG) { print "Series new $symbol\n"; } | ||
| 55 | |||
| 56 | my $self = $cache{$symbol}; | ||
| 57 | if ($self) { return $self; } | ||
| 58 | |||
| 59 | my $base = App::Chart::DBI->read_single | ||
| 60 | ('SELECT date FROM daily WHERE symbol=? ORDER BY date ASC LIMIT 1', | ||
| 61 | $symbol); | ||
| 62 | if (! $base) { | ||
| 63 | require App::Chart::TZ; | ||
| 64 | my $timezone = App::Chart::TZ->for_symbol ($symbol); | ||
| 65 | $base = $timezone->iso_date; | ||
| 66 | } | ||
| 67 | if (DEBUG) { print " base $base\n"; } | ||
| 68 | require App::Chart::Timebase::Days; | ||
| 69 | my $timebase = App::Chart::Timebase::Days->new_from_iso ($base); | ||
| 70 | |||
| 71 | $self = $class->SUPER::new (symbol => $symbol, | ||
| 72 | timebase => $timebase); | ||
| 73 | |||
| 74 | # lose any cache entries which have gone undef through weaks destroyed | ||
| 75 | delete @cache{grep {! $cache{$_}} keys %cache}; | ||
| 76 | |||
| 77 | # add new entry | ||
| 78 | _init_cache(); | ||
| 79 | $cache{$symbol} = $self; | ||
| 80 | Scalar::Util::weaken ($cache{$symbol}); | ||
| 81 | |||
| 82 | return $self; | ||
| 83 | } | ||
| 84 | |||
| 85 | sub hi { | ||
| 86 | my ($self) = @_; | ||
| 87 | if (! exists $self->{'hi'}) { | ||
| 88 | if (DEBUG) { print "Series hi for $self->{'symbol'}\n"; } | ||
| 89 | my $date = App::Chart::DBI->read_single | ||
| 90 | ('SELECT date FROM daily WHERE symbol=? ORDER BY date DESC LIMIT 1', | ||
| 91 | $self->{'symbol'}); | ||
| 92 | if (DEBUG) { print " iso ",$date//'undef',"\n"; } | ||
| 93 | my $timebase = $self->{'timebase'}; | ||
| 94 | $self->{'hi'} = ($date | ||
| 95 | ? $timebase->from_iso_floor ($date) | ||
| 96 | : 0); | ||
| 97 | if (DEBUG) { print " hi=$self->{'hi'}\n"; } | ||
| 98 | } | ||
| 99 | return $self->{'hi'}; | ||
| 100 | } | ||
| 101 | |||
| 102 | sub fill_part { | ||
| 103 | my ($self, $lo, $hi) = @_; | ||
| 104 | if (DEBUG) { print "Database $self->{'symbol'} fill_part $lo $hi\n"; } | ||
| 105 | |||
| 106 | my $dbh = App::Chart::DBI->instance; | ||
| 107 | my $timebase = $self->{'timebase'}; | ||
| 108 | |||
| 109 | # date descending so first store pre-extends the respective arrays | ||
| 110 | my $sth = $dbh->prepare_cached | ||
| 111 | ('SELECT date, open, high, low, close, volume, openint | ||
| 112 | FROM daily WHERE (symbol=? AND (date BETWEEN ? AND ?)) | ||
| 113 | ORDER BY date DESC'); | ||
| 114 | |||
| 115 | my $aref = $dbh->selectall_arrayref ($sth, undef, | ||
| 116 | $self->{'symbol'}, | ||
| 117 | $timebase->to_iso ($lo), | ||
| 118 | $timebase->to_iso ($hi)); | ||
| 119 | $sth->finish; | ||
| 120 | |||
| 121 | my $opens = $self->array('opens'); | ||
| 122 | my $highs = $self->array('highs'); | ||
| 123 | my $lows = $self->array('lows'); | ||
| 124 | my $closes = $self->array('closes'); | ||
| 125 | my $volumes = $self->array('volumes'); | ||
| 126 | my $openints = $self->array('openints'); | ||
| 127 | |||
| 128 | foreach my $row (@$aref) { | ||
| 129 | my $i = $timebase->from_iso_floor ($row->[0]); | ||
| 130 | next if ($i < 0); | ||
| 131 | |||
| 132 | if (defined $row->[1]) { $opens->[$i] = $row->[1]; } | ||
| 133 | if (defined $row->[2]) { $highs->[$i] = $row->[2]; } | ||
| 134 | if (defined $row->[3]) { $lows->[$i] = $row->[3]; } | ||
| 135 | if (defined $row->[4]) { $closes->[$i] = $row->[4]; } | ||
| 136 | if (defined $row->[5]) { $volumes->[$i] = $row->[5]; } | ||
| 137 | if (defined $row->[6]) { $openints->[$i] = $row->[6]; } | ||
| 138 | } | ||
| 139 | } | ||
| 140 | |||
| 141 | sub name { | ||
| 142 | my ($self) = @_; | ||
| 143 | return $self->symbol; # in Series.pm | ||
| 144 | } | ||
| 145 | # { | ||
| 146 | # $self->{'symbol'} | ||
| 147 | # | ||
| 148 | # if (! exists $self->{'name'}) { | ||
| 149 | # $self->{'name'} = App::Chart::Database->symbol_name ($self->{'symbol'}); | ||
| 150 | # } | ||
| 151 | # return $self->{'name'} | ||
| 152 | # } | ||
| 153 | sub symbol_name { | ||
| 154 | my ($self) = @_; | ||
| 155 | return App::Chart::Database->symbol_name ($self->{'symbol'}); | ||
| 156 | } | ||
| 157 | |||
| 158 | sub decimals { | ||
| 159 | my ($self) = @_; | ||
| 160 | if (! exists $self->{'decimals'}) { | ||
| 161 | $self->{'decimals'} | ||
| 162 | = App::Chart::Database->symbol_decimals ($self->{'symbol'}); | ||
| 163 | } | ||
| 164 | return $self->{'decimals'}; | ||
| 165 | } | ||
| 166 | |||
| 167 | sub dividends { | ||
| 168 | my ($self) = @_; | ||
| 169 | return ($self->{'dividends'} ||= do { | ||
| 170 | my $dbh = App::Chart::DBI->instance; | ||
| 171 | my $sth = $dbh->prepare_cached | ||
| 172 | ('SELECT ex_date, type, amount, imputation, qualifier, note | ||
| 173 | FROM dividend WHERE symbol=? ORDER BY ex_date ASC'); | ||
| 174 | my $aref = $dbh->selectall_arrayref ($sth, {Slice=>{}}, $self->{'symbol'}); | ||
| 175 | |||
| 176 | my $timebase = $self->{'timebase'}; | ||
| 177 | foreach my $div (@$aref) { | ||
| 178 | foreach my $date (qw(ex_date record_date pay_date)) { | ||
| 179 | my $iso = $div->{$date} or next; | ||
| 180 | my $t = $timebase->from_iso_floor ($iso); | ||
| 181 | $div->{$date.'_t'} = $t; | ||
| 182 | } | ||
| 183 | } | ||
| 184 | $aref | ||
| 185 | }); | ||
| 186 | } | ||
| 187 | |||
| 188 | sub splits { | ||
| 189 | my ($self) = @_; | ||
| 190 | return ($self->{'splits'} ||= do { | ||
| 191 | my $dbh = App::Chart::DBI->instance; | ||
| 192 | my $sth = $dbh->prepare_cached | ||
| 193 | ('SELECT date, new, old, note | ||
| 194 | FROM split WHERE symbol=? ORDER BY date ASC'); | ||
| 195 | my $aref = $dbh->selectall_arrayref ($sth, {Slice=>{}}, $self->{'symbol'}); | ||
| 196 | |||
| 197 | my $timebase = $self->{'timebase'}; | ||
| 198 | foreach my $div (@$aref) { | ||
| 199 | my $iso = $div->{'date'} or next; | ||
| 200 | my $t = $timebase->from_iso_floor ($iso); | ||
| 201 | $div->{'date_t'} = $t; | ||
| 202 | } | ||
| 203 | $aref | ||
| 204 | }); | ||
| 205 | } | ||
| 206 | |||
| 207 | sub annotations { | ||
| 208 | my ($self) = @_; | ||
| 209 | return ($self->{'annotations'} ||= do { | ||
| 210 | if (DEBUG) { print "Series::Database read annotations ", | ||
| 211 | $self->{'symbol'},"\n"; } | ||
| 212 | my $dbh = App::Chart::DBI->instance; | ||
| 213 | my $sth = $dbh->prepare_cached | ||
| 214 | ('SELECT id, date, note FROM annotation | ||
| 215 | WHERE symbol=? ORDER BY date ASC'); | ||
| 216 | my $aref = $dbh->selectall_arrayref ($sth, {Slice=>{}}, $self->{'symbol'}); | ||
| 217 | |||
| 218 | my $timebase = $self->{'timebase'}; | ||
| 219 | foreach my $ann (@$aref) { | ||
| 220 | my $iso = $ann->{'date'} or next; | ||
| 221 | my $t = $timebase->from_iso_floor ($iso); | ||
| 222 | $ann->{'date_t'} = $t; | ||
| 223 | } | ||
| 224 | $aref | ||
| 225 | }); | ||
| 226 | } | ||
| 227 | |||
| 228 | sub Alerts_arrayref { | ||
| 229 | my ($series) = @_; | ||
| 230 | return ($series->{__PACKAGE__.'.array'} ||= do { | ||
| 231 | my $symbol = $series->symbol || ''; | ||
| 232 | require App::Chart::DBI; | ||
| 233 | my $dbh = App::Chart::DBI->instance; | ||
| 234 | |||
| 235 | my $sth = $dbh->prepare_cached ('SELECT * FROM alert WHERE symbol=?'); | ||
| 236 | my $aref = $dbh->selectall_arrayref ($sth, {Slice=>{}}, $symbol); | ||
| 237 | $sth->finish; | ||
| 238 | if (@$aref) { | ||
| 239 | require App::Chart::Annotation; | ||
| 240 | foreach my $elem (@$aref) { | ||
| 241 | bless $elem, 'App::Chart::Annotation::Alert'; | ||
| 242 | } | ||
| 243 | } | ||
| 244 | $aref; | ||
| 245 | }); | ||
| 246 | } | ||
| 247 | |||
| 248 | |||
| 249 | 1; | ||
| 250 | __END__ |