| 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__ | ||