| 1 | # Copyright 2007, 2008, 2009, 2010, 2011, 2016, 2017 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::Database; | ||
| 18 | 1 | ▶ | use 5.010; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 19 | 1 | ▶ | use strict; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 20 | 1 | ▶ | use warnings; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 21 | 1 | ▶ | use Carp; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 22 | |||
| 23 | 0 | ▶ | use App::Chart; |
|
Subroutine
BEGIN: called CC=1 SCAR=2.6
| |||
| 24 | use App::Chart::DBI; | ||
| 25 | |||
| 26 | # uncomment this to run the ### lines | ||
| 27 | #use Devel::Comments; | ||
| 28 | |||
| 29 | |||
| 30 | #------------------------------------------------------------------------------ | ||
| 31 | |||
| 32 | # return true if $dbh contains a table called $table | ||
| 33 | sub dbh_table_exists { | ||
| 34 | my ($dbh, $table) = @_; | ||
| 35 | my $sth = $dbh->table_info (undef, undef, $table, undef); | ||
| 36 | my $exists = $sth->fetchrow_arrayref ? 1 : 0; | ||
| 37 | $sth->finish; | ||
| 38 | return $exists; | ||
| 39 | } | ||
| 40 | |||
| 41 | |||
| 42 | sub read_single { | ||
| 43 | return App::Chart::DBI->read_single (@_); | ||
| 44 | # my ($sql, @args) = @_; | ||
| 45 | # my $dbh = App::Chart::DBI->instance; | ||
| 46 | # my $sth = $dbh->prepare_cached ($sql); | ||
| 47 | # my $row = $dbh->selectrow_arrayref($sth, undef, @args); | ||
| 48 | # $sth->finish; | ||
| 49 | # if (! defined $row) { return undef; } | ||
| 50 | # return $row->[0]; | ||
| 51 | } | ||
| 52 | |||
| 53 | sub read_notes_single { | ||
| 54 | # my ($sql, @args) = @_; | ||
| 55 | # if (DEBUG) { print "read_notes_single(): $sql\n"; } | ||
| 56 | return App::Chart::DBI->read_single (@_); | ||
| 57 | # my $nbh = App::Chart::DBI->instance; | ||
| 58 | # my $sth = $nbh->prepare_cached ($sql); | ||
| 59 | # my $row = $nbh->selectrow_arrayref($sth, undef, @args); | ||
| 60 | # $sth->finish; | ||
| 61 | # if (! defined $row) { return undef; } | ||
| 62 | # return $row->[0]; | ||
| 63 | } | ||
| 64 | |||
| 65 | # might prefer some sort of "INSERT WHERE NOT EXISTS", but sqlite doesn't | ||
| 66 | # seem to take that (only it's own extension "INSERT OR IGNORE") | ||
| 67 | # | ||
| 68 | sub add_symbol { | ||
| 69 | my ($class, @symbol_list) = @_; | ||
| 70 | ### Database add_symbol(): @symbol_list | ||
| 71 | require App::Chart::Gtk2::Symlist::All; | ||
| 72 | require App::Chart::Gtk2::Symlist::Historical; | ||
| 73 | require App::Chart::Annotation; | ||
| 74 | my $all_symlist = App::Chart::Gtk2::Symlist::All->instance; | ||
| 75 | my $historical_symlist = App::Chart::Gtk2::Symlist::Historical->instance; | ||
| 76 | |||
| 77 | my $dbh = App::Chart::DBI->instance; | ||
| 78 | call_with_transaction | ||
| 79 | ($dbh, sub { | ||
| 80 | my $sth = $dbh->prepare_cached | ||
| 81 | ('UPDATE info SET historical=0 WHERE symbol=?'); | ||
| 82 | foreach my $symbol (@symbol_list) { | ||
| 83 | if ($class->symbol_exists ($symbol)) { | ||
| 84 | $sth->execute ($symbol); | ||
| 85 | $sth->finish; | ||
| 86 | } else { | ||
| 87 | $dbh->do ('INSERT INTO info (symbol) VALUES (?)', {}, $symbol); | ||
| 88 | } | ||
| 89 | $all_symlist->insert_symbol ($symbol); | ||
| 90 | $historical_symlist->delete_symbol ($symbol); | ||
| 91 | # possible existing alert levels | ||
| 92 | App::Chart::Annotation::Alert::update_alert($symbol); | ||
| 93 | } | ||
| 94 | }); | ||
| 95 | } | ||
| 96 | |||
| 97 | |||
| 98 | sub delete_symbol { | ||
| 99 | my ($class, $symbol, $notes_too) = @_; | ||
| 100 | ### Database delete_symbol(): $symbol | ||
| 101 | ### $notes_too | ||
| 102 | |||
| 103 | # sqlite allows multiple statements in one handle, but that's apparently | ||
| 104 | # not always so in DBI | ||
| 105 | |||
| 106 | require App::Chart::Gtk2::Symlist::All; | ||
| 107 | require App::Chart::Gtk2::Symlist::Historical; | ||
| 108 | require App::Chart::Annotation; | ||
| 109 | my $all_symlist = App::Chart::Gtk2::Symlist::All->instance; | ||
| 110 | my $historical_symlist = App::Chart::Gtk2::Symlist::Historical->instance; | ||
| 111 | |||
| 112 | my $dbh = App::Chart::DBI->instance; | ||
| 113 | call_with_transaction | ||
| 114 | ($dbh, sub { | ||
| 115 | foreach my $statement | ||
| 116 | ('DELETE FROM daily WHERE symbol=?', | ||
| 117 | 'DELETE FROM info WHERE symbol=?', | ||
| 118 | 'DELETE FROM dividend WHERE symbol=?', | ||
| 119 | 'DELETE FROM split WHERE symbol=?', | ||
| 120 | 'DELETE FROM extra WHERE symbol=?') { | ||
| 121 | $dbh->do($statement, undef, $symbol); | ||
| 122 | } | ||
| 123 | if ($notes_too) { | ||
| 124 | foreach my $statement | ||
| 125 | ('DELETE FROM annotation WHERE symbol=?', | ||
| 126 | 'DELETE FROM line WHERE symbol=?', | ||
| 127 | 'DELETE FROM alert WHERE symbol=?') { | ||
| 128 | $dbh->do($statement, undef, $symbol); | ||
| 129 | } | ||
| 130 | } | ||
| 131 | $all_symlist ->delete_symbol ($symbol); | ||
| 132 | $historical_symlist->delete_symbol ($symbol); | ||
| 133 | # delete from alerts list | ||
| 134 | App::Chart::Annotation::Alert::update_alert($symbol); | ||
| 135 | }); | ||
| 136 | |||
| 137 | App::Chart::chart_dirbroadcast()->send ('delete-symbol', $symbol); | ||
| 138 | App::Chart::chart_dirbroadcast()->send ('data-changed', { $symbol => 1 }); | ||
| 139 | App::Chart::chart_dirbroadcast()->send ('delete-notes', $symbol); | ||
| 140 | } | ||
| 141 | |||
| 142 | sub symbol_exists { | ||
| 143 | my ($class, $symbol) = @_; | ||
| 144 | return read_single ('SELECT symbol FROM info WHERE symbol=?', $symbol); | ||
| 145 | } | ||
| 146 | |||
| 147 | # return a hashref which has for its keys all the symbols in the database | ||
| 148 | # (the daily data, not quotes or intraday) | ||
| 149 | sub database_symbols_hash { | ||
| 150 | my $dbh = App::Chart::DBI->instance; | ||
| 151 | my $sth = $dbh->prepare_cached('SELECT symbol FROM info'); | ||
| 152 | my $aref = $dbh->selectcol_arrayref ($sth, { }); | ||
| 153 | $sth->finish(); | ||
| 154 | my %hash = (); | ||
| 155 | @hash{@$aref} = 1; | ||
| 156 | return \%hash; | ||
| 157 | } | ||
| 158 | |||
| 159 | sub symbols_list { | ||
| 160 | # my ($class) = @_; | ||
| 161 | my $dbh = App::Chart::DBI->instance; | ||
| 162 | my $sth = $dbh->prepare_cached('SELECT symbol FROM info'); | ||
| 163 | my $aref = $dbh->selectcol_arrayref ($sth); | ||
| 164 | $sth->finish(); | ||
| 165 | return @$aref; | ||
| 166 | } | ||
| 167 | |||
| 168 | sub symbol_is_historical { | ||
| 169 | my ($class, $symbol) = @_; | ||
| 170 | my $dbh = App::Chart::DBI->instance; | ||
| 171 | my $sth = $dbh->prepare_cached('SELECT historical FROM info WHERE symbol=?'); | ||
| 172 | my $aref = $dbh->selectrow_arrayref ($sth, undef, $symbol); | ||
| 173 | return ($aref && $aref->[0]); | ||
| 174 | } | ||
| 175 | |||
| 176 | sub symbol_name { | ||
| 177 | my ($class, $symbol) = @_; | ||
| 178 | return read_single ('SELECT name FROM info WHERE symbol=?', $symbol); | ||
| 179 | } | ||
| 180 | |||
| 181 | sub symbol_decimals { | ||
| 182 | my ($class, $symbol) = @_; | ||
| 183 | return (read_single ('SELECT decimals FROM info WHERE symbol=?', $symbol) | ||
| 184 | || 0); | ||
| 185 | } | ||
| 186 | |||
| 187 | sub write_extra { | ||
| 188 | my ($class, $symbol, $key, $value) = @_; | ||
| 189 | if (! defined $key) { croak 'write_extra() key cannot be undef'; } | ||
| 190 | |||
| 191 | my $dbh = App::Chart::DBI->instance; | ||
| 192 | if (defined $value) { | ||
| 193 | my $sth = $dbh->prepare_cached | ||
| 194 | ('INSERT OR REPLACE INTO extra (symbol, key, value) VALUES (?,?,?)'); | ||
| 195 | $sth->execute ($symbol, $key, $value); | ||
| 196 | $sth->finish; | ||
| 197 | } else { | ||
| 198 | $dbh->do ('DELETE FROM extra WHERE symbol=? AND key=?', | ||
| 199 | undef, | ||
| 200 | $symbol, $key); | ||
| 201 | } | ||
| 202 | } | ||
| 203 | |||
| 204 | sub read_extra { | ||
| 205 | my ($class, $symbol, $key) = @_; | ||
| 206 | return read_single ('SELECT value FROM extra WHERE symbol=? AND key=?', | ||
| 207 | $symbol, $key); | ||
| 208 | } | ||
| 209 | |||
| 210 | # An eval isn't backtrace friendly, but a __DIE__ handler would be reached | ||
| 211 | # by possible normal errors caught by a handler in $subr. | ||
| 212 | # | ||
| 213 | # rollback() can get errors too, like database gone away. They end up | ||
| 214 | # thrown in preference to the original error. | ||
| 215 | # | ||
| 216 | sub call_with_transaction { | ||
| 217 | my ($dbh, $subr) = @_; | ||
| 218 | my $hold = App::Chart::chart_dirbroadcast()->hold; | ||
| 219 | |||
| 220 | if ($dbh->{AutoCommit}) { | ||
| 221 | my $ret; | ||
| 222 | $dbh->begin_work; | ||
| 223 | if (eval { $ret = $subr->(); 1 }) { | ||
| 224 | $dbh->commit; | ||
| 225 | return $ret; | ||
| 226 | } else { | ||
| 227 | my $err = $@; | ||
| 228 | $dbh->rollback; | ||
| 229 | die $err; | ||
| 230 | } | ||
| 231 | |||
| 232 | } else { | ||
| 233 | $subr->(); | ||
| 234 | } | ||
| 235 | } | ||
| 236 | |||
| 237 | sub preference_get { | ||
| 238 | my ($class, $key, $default) = @_; | ||
| 239 | my $value = read_notes_single | ||
| 240 | ('SELECT value FROM preference WHERE key=?', $key); | ||
| 241 | if (defined $value) { | ||
| 242 | return $value; | ||
| 243 | } else { | ||
| 244 | return $default; | ||
| 245 | } | ||
| 246 | } | ||
| 247 | |||
| 248 | |||
| 249 | 1; | ||
| 250 | __END__ | ||