App/Chart/Database.pm

filter: Statementstmt 85.7% 12 / 14 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 5 / 5 Podpod n/a totaltotal 89.4% 17 / 19 CC 1 SCAR 0.6
CC
1
Cov
86%
BEGIN
1.3
BEGIN
1.0
BEGIN
1.0
CRAP 1.1
« App/Chart/Timebase/Days.pm ↑ Summary App/Chart/Gtk2/Ex/ListStoreDBISeq.pm »
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__
« App/Chart/Timebase/Days.pm ↑ Summary App/Chart/Gtk2/Ex/ListStoreDBISeq.pm »