App/Chart/Gtk2/Ex/ListStoreDBISeq.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/Database.pm ↑ Summary App/Chart/Gtk2/IntradayImage.pm »
1 # Copyright 2007, 2008, 2009, 2010, 2015 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::Gtk2::Ex::ListStoreDBISeq;
18 1 ▶use 5.008;
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 'carp','croak';
Subroutine BEGIN: called CC=1 SCAR=0.0
22 0 ▶use Gtk2;
Subroutine BEGIN: called CC=1 SCAR=2.6
23 use List::Util qw(min max);
24 use POSIX ();
25  
26 use App::Chart;
27 use App::Chart::Database;
28  
29 # uncomment this to run the ### lines
30 #use Smart::Comments;
31  
32  
33 use Glib::Object::Subclass
34 'Gtk2::ListStore',
35 signals => { row_changed => \&_do_row_changed,
36 row_inserted => \&_do_row_inserted,
37 row_deleted => \&_do_row_deleted },
38 properties => [
39 # a perl DBI handle
40 Glib::ParamSpec->scalar
41 ('dbh',
42 'dbh',
43 'Blurb.',
44 Glib::G_PARAM_READWRITE),
45  
46 Glib::ParamSpec->scalar
47 ('table',
48 'table',
49 'Blurb.',
50 Glib::G_PARAM_READWRITE),
51  
52 Glib::ParamSpec->scalar
53 ('where',
54 'where',
55 'Blurb.',
56 Glib::G_PARAM_READWRITE),
57  
58 Glib::ParamSpec->scalar
59 ('columns',
60 'columns',
61 'Blurb.',
62 Glib::G_PARAM_READWRITE),
63 ];
64  
65 sub INIT_INSTANCE {
66 my ($self) = @_;
67 _establish_where ($self); # initial empty
68  
69 # class closure no good as of Perl-Gtk2 1.221, must connect to self
70 $self->signal_connect (rows_reordered => \&_do_rows_reordered);
71 }
72  
73 sub SET_PROPERTY {
74 my ($self, $pspec, $newval) = @_;
75 my $pname = $pspec->get_name;
76 ### ListSeq SET_PROPERTY(): $pname
77 $self->{$pname} = $newval;
78  
79 if ($pname eq 'columns') {
80 my $columns = $newval;
81 $self->set_column_types (('Glib::String') x @$columns);
82  
83 } elsif ($pname eq 'where') {
84 _establish_where ($self);
85 }
86 delete $self->{'sth'};
87 $self->reread;
88 }
89  
90 sub _establish_where {
91 my ($self) = @_;
92  
93 my @columns;
94 my @values;
95 $self->{'where_clause'} = '';
96 $self->{'where_and'} = ' WHERE ';
97 $self->{'where_columns'} = \@columns;
98 $self->{'where_values'} = \@values;
99  
100 if (my $where = $self->{'where'}) {
101 my @conds;
102 while (my ($column, $value) = each %$where) {
103 push @columns, $column;
104 push @conds, "$column=?";
105 push @values, $value;
106 }
107 if (@conds) {
108 my $cond = $self->{'where_clause'} = ' WHERE ' . join(' AND ', @conds);
109 $self->{'where_and'} = $cond . ' AND ';
110 }
111 }
112 ### ListSeq where_clause: $self->{'where_clause'}
113 ### where_and: $self->{'where_and'}
114 ### where_values: $self->{'where_values'}
115 }
116  
117 sub reread {
118 my ($self) = @_;
119 ### ListSeq reread()
120  
121 my $dbh = $self->{'dbh'};
122 my $table = $self->{'table'};
123 my $columns = $self->{'columns'};
124 my $where_values = $self->{'where_values'};
125  
126 if (! ($dbh && $table && $columns)) {
127 local $self->{'reading_database'} = 1;
128 $self->clear;
129 return;
130 }
131  
132 my $sth_read = ($self->{'sth'}->{'read'} ||= do {
133 $dbh->prepare ('SELECT ' . join(',', 'seq', @$columns)
134 . " FROM $table $self->{'where_clause'} ORDER BY seq ASC")
135 });
136 $sth_read->execute (@$where_values);
137  
138 local $self->{'reading_database'} = 1;
139 my $iter = $self->get_iter_first;
140  
141 my $want_seq = 0;
142 while (my @row = $sth_read->fetchrow_array) {
143 my $got_seq = shift @row;
144 if ($got_seq != $want_seq) {
145 carp "ListSeq: bad seq in database, got $got_seq want $want_seq, fixing";
146 $dbh->do ("UPDATE $table SET seq=? $self->{'where_and'} seq=?",
147 undef,
148 $want_seq, @$where_values, $got_seq)
149 }
150 $want_seq++;
151  
152 if ($iter) {
153 my @set;
154 foreach my $col (0 .. $#row) {
155 if (! _equal ($self->get_value ($iter, $col), $row[$col])) {
156 push @set, $col, $row[$col];
157 }
158 }
159 if (@set) {
160 ### reread set row: $want_seq-1
161 $self->set ($iter, @set);
162 } else {
163 ### reread unchanged row: $want_seq-1
164 }
165 $iter = $self->iter_next ($iter);
166 } else {
167 ### reread append row: $want_seq-1
168 @row = map {; ($_ => $row[$_]) } (0 .. $#row);
169 $self->insert_with_values (POSIX::INT_MAX(), @row);
170 }
171 }
172 $sth_read->finish;
173  
174 if ($iter) {
175 ### reread remove excess
176 ### from: $self->get_path($iter)->to_string
177 ### to: $self->iter_n_children(undef)
178 while ($self->remove ($iter)) {
179 }
180 }
181 ### reread done
182 }
183  
184 sub _equal {
185 my ($x, $y) = @_;
186 if (defined $x) {
187 if (defined $y) {
188 return $x eq $y;
189 }
190 return 0;
191 } else {
192 return ! defined $y;
193 }
194 }
195  
196 # .... untested ....
197 sub fixup {
198 my ($self, %options) = @_;
199 ### ListSeq fixup()
200  
201 my $dbh = $self->{'dbh'};
202 my $where_values = $self->{'where_values'};
203  
204 my $message = $options{'message'};
205 if (! ref $message) {
206 $message = sub { print $_[0],"\n"; };
207 }
208 my $verbose = $options{'verbose'};
209  
210 App::Chart::Database::call_with_transaction
211 ($dbh, sub {
212 my $table = $self->{'table'};
213 my $where_clause = $self->{'where_clause'};
214 my $aref = $dbh->selectcol_arrayref
215 ("SELECT seq FROM $table $where_clause ORDER BY seq ASC",
216 undef, @$where_values);
217 ### $aref
218  
219 if (_is_0_to_N ($aref)) {
220 if ($verbose) {
221 $message->('Sequence numbers ok');
222 }
223 } else {
224 $message->('Bad sequence numbers, fixing');
225  
226 # seq numbers moved up to $tempseq then back down to 0. Must
227 # force $tempseq not to be negative so the move down works.
228 # Since seq+$where_clause should be unique it's the fixup here is
229 # to collapse gaps and move up negatives.
230  
231 my $sth = $dbh->prepare
232 ("UPDATE $table SET seq=? $self->{'where_and'} seq=?");
233 my $tempseq = max (0, $aref->[-1] + 1);
234  
235 my $newseq = $tempseq;
236 foreach my $oldseq (@$aref) {
237 $sth->execute ($newseq, @$where_values, $oldseq);
238 print "$newseq <- $oldseq\n";
239 $sth->finish;
240 $newseq++;
241 }
242  
243 $dbh->do ("UPDATE $table SET seq=seq-$tempseq $where_clause",
244 undef, @$where_values);
245 $self->reread;
246 }
247 });
248 }
249  
250 sub _is_0_to_N {
251 my ($aref) = @_;
252 for (my $i = 0; $i < @$aref; $i++) {
253 if ($aref->[$i] != $i) {
254 return 0;
255 }
256 }
257 return 1;
258 }
259  
260  
261  
262  
263 #------------------------------------------------------------------------------
264 # local changes propagated to database
265  
266 # 'row-changed' class closure
267 sub _do_row_changed {
268 my ($self, $path, $iter) = @_;
269  
270 if (! $self->{'reading_database'}) {
271 ### ListSeq _do_row_changed(): $path->to_string
272  
273 my $dbh = $self->{'dbh'} || croak 'No DBI handle to store change';
274 my $columns = $self->{'columns'};
275  
276 my $sth_change = ($self->{'sth'}->{'change'} ||= do {
277 $dbh->prepare ("UPDATE $self->{'table'} SET "
278 . join (',', map {; "$_=?" } @$columns)
279 . "$self->{'where_and'} seq=?")
280 });
281  
282 my @values = map { $self->get_value($iter,$_) } (0 .. $#$columns);
283 my ($seq) = $path->get_indices;
284  
285 my $affected = $sth_change->execute (@values,
286 @{$self->{'where_values'}},
287 $seq);
288 $sth_change->finish;
289  
290 if ($affected != 1) {
291 # $self->reread;
292 croak "ListSeq: oops, expected to change 1, got $affected";
293 }
294  
295 # local $self->{'reading_database'} = 1;
296 # App::Chart::Glib::Ex::DirBroadcast->send ('dbi-changed', $where, $seq);
297 }
298 return shift->signal_chain_from_overridden(@_);
299 }
300  
301 # 'row-deleted' class closure
302 sub _do_row_deleted {
303 my ($self, $path) = @_;
304 delete $self->{'hash'};
305 if (! $self->{'reading_database'}) {
306 ### ListSeq _do_row_deleted(): $path->to_string
307  
308 my $dbh = $self->{'dbh'} || croak 'No DBI handle to apply delete';
309 my $where_values = $self->{'where_values'};
310 my ($seq) = $path->get_indices;
311 my $affected;
312  
313 my $sth_delete = ($self->{'sth'}->{'delete'} ||= do {
314 $dbh->prepare ("DELETE FROM $self->{'table'} $self->{'where_and'} seq=?")
315 });
316  
317 my $sth_shift_down = ($self->{'sth'}->{'shift_down'} ||= do {
318 # -1-(seq-1) == -seq
319 $dbh->prepare ("UPDATE $self->{'table'} SET seq=-seq"
320 . " $self->{'where_and'} seq>?")
321 });
322  
323 App::Chart::Database::call_with_transaction
324 ($dbh, sub {
325 $affected = $sth_delete->execute (@$where_values, $seq);
326 $sth_delete->finish;
327  
328 if ($affected != 1) {
329 # $self->reread;
330 croak "ListSeq: oops, expected to delete 1, got $affected";
331 }
332  
333 $sth_shift_down->execute (@$where_values, $seq);
334 $sth_shift_down->finish;
335 _negate ($self);
336 });
337 # local $self->{'reading_database'} = 1;
338 # App::Chart::Glib::Ex::DirBroadcast->send ('dbi-delete', $where, $seq);
339 }
340 return shift->signal_chain_from_overridden(@_);
341 }
342  
343 # 'row-inserted' class closure
344 sub _do_row_inserted {
345 my ($self, $path, $iter) = @_;
346 ### ListSeq _do_row_inserted(): $path->to_string
347 ### reading_database: $self->{'reading_database'}
348  
349 if (! $self->{'reading_database'}) {
350  
351 my ($seq) = $path->get_indices;
352 my $dbh = $self->{'dbh'} || croak 'No DBI handle to apply insert';
353 my $columns = $self->{'columns'};
354 my $where_values = $self->{'where_values'};
355  
356 my $sth_lastseq = ($self->{'sth'}->{'lastseq'} ||= do {
357 $dbh->prepare ("SELECT seq FROM $self->{'table'}"
358 . " $self->{'where_clause'} ORDER BY seq DESC LIMIT 1")
359 });
360 my $sth_shift_up = ($self->{'sth'}->{'shift_up'} ||= do {
361 # -1-(seq+1) == -2-seq
362 $dbh->prepare ("UPDATE $self->{'table'} SET seq=-2-seq"
363 . " $self->{'where_and'} seq>=?")
364 });
365 my $sth_insert = ($self->{'sth'}->{'insert'} ||= do {
366 my $where_columns = $self->{'where_columns'};
367 my @columns = ('seq', @$where_columns, @$columns);
368 $dbh->prepare
369 ("INSERT INTO $self->{'table'} (" . join(',',@columns)
370 . ') VALUES (' . join(',', ('?')x(@columns)) . ')');
371 });
372  
373 my @values = map { $self->get_value($iter,$_) } (0 .. $#$columns);
374  
375 App::Chart::Database::call_with_transaction
376 ($dbh, sub {
377 $sth_lastseq->execute (@$where_values);
378 my ($lastseq) = $sth_lastseq->fetchrow_array;
379 $sth_lastseq->finish;
380 if (! defined $lastseq) { $lastseq = -1; }
381 ### lastseq: $lastseq
382  
383 if ($seq > $lastseq+1) {
384 croak "ListSeq: oops, insert seq $seq but last is $lastseq";
385 }
386  
387 $sth_shift_up->execute (@$where_values, $seq);
388 $sth_shift_up->finish;
389  
390 _negate ($self);
391  
392 $sth_insert->execute ($seq, @$where_values, @values);
393 $sth_insert->finish;
394 });
395 # local $self->{'reading_database'} = 1;
396 # App::Chart::Glib::Ex::DirBroadcast->send ('dbi-inserted', $where,$seq);
397 }
398 return shift->signal_chain_from_overridden(@_);
399 }
400  
401 # 'rows-reordered' connected on self
402 sub _do_rows_reordered {
403 my ($self, $path, $iter, $aref) = @_;
404  
405 delete $self->{'hash'};
406 if (! $self->{'reading_database'}) {
407 ### ListSeq _do_rows_reordered(): $aref
408  
409 my $dbh = $self->{'dbh'} || croak 'No DBI handle to reorder';
410 my $where_values = $self->{'where_values'};
411  
412 my $sth_reorder = ($self->{'sth'}->{'reorder'} ||= do {
413 $dbh->prepare ("UPDATE $self->{'table'} SET seq=?"
414 . " $self->{'where_and'} seq=?")
415 });
416  
417 App::Chart::Database::call_with_transaction
418 ($dbh, sub {
419 foreach my $newpos (0 .. $#$aref) {
420 my $oldpos = $aref->[$newpos];
421 if ($oldpos != $newpos) {
422 ### renumber: "from $oldpos to ".(-1-$newpos)
423 $sth_reorder->execute (-1-$newpos, @$where_values, $oldpos);
424 $sth_reorder->finish;
425 }
426 }
427 _negate ($self);
428 });
429 # local $self->{'reading_database'} = 1;
430 # App::Chart::Glib::Ex::DirBroadcast->send ('dbi-reordered', $key);
431 }
432 }
433  
434 sub _negate {
435 my ($self) = @_;
436 my $dbh = $self->{'dbh'};
437 my $sth_negate = ($self->{'sth'}->{'negate'} ||= do {
438 my $table = $self->{'table'};
439 my $where_and = $self->{'where_and'};
440 $dbh->prepare ("UPDATE $table SET seq=-1-seq $where_and seq<0")
441 });
442 my $where_values = $self->{'where_values'};
443 $sth_negate->execute (@$where_values);
444 $sth_negate->finish;
445 }
446  
447  
448 1;
449 __END__
« App/Chart/Database.pm ↑ Summary App/Chart/Gtk2/IntradayImage.pm »