| 1 | # Copyright 2007, 2008, 2009, 2010 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::ListStore::DBI; | ||
| 18 | use 5.008; | ||
| 19 | use strict; | ||
| 20 | use warnings; | ||
| 21 | use Carp; | ||
| 22 | use Gtk2; | ||
| 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 | |||
| 66 | sub INIT_INSTANCE { | ||
| 67 | my ($self) = @_; | ||
| 68 | _establish_where ($self); # initial empty | ||
| 69 | |||
| 70 | # # class closure no good as of Perl-Gtk2 1.221, must connect to self | ||
| 71 | # $self->signal_connect (rows_reordered => \&_do_rows_reordered); | ||
| 72 | } | ||
| 73 | |||
| 74 | sub SET_PROPERTY { | ||
| 75 | my ($self, $pspec, $newval) = @_; | ||
| 76 | my $pname = $pspec->get_name; | ||
| 77 | ### ListStore-DBI SET_PROPERTY(): $pname | ||
| 78 | $self->{$pname} = $newval; | ||
| 79 | |||
| 80 | if ($pname eq 'columns') { | ||
| 81 | my $columns = $newval; | ||
| 82 | $self->set_column_types (('Glib::String') x @$columns); | ||
| 83 | |||
| 84 | } elsif ($pname eq 'where') { | ||
| 85 | _establish_where ($self); | ||
| 86 | } | ||
| 87 | delete $self->{'sth'}; | ||
| 88 | $self->reread; | ||
| 89 | } | ||
| 90 | |||
| 91 | sub _establish_where { | ||
| 92 | my ($self) = @_; | ||
| 93 | |||
| 94 | my @columns; | ||
| 95 | my @values; | ||
| 96 | $self->{'where_clause'} = ''; | ||
| 97 | $self->{'where_and'} = ' WHERE '; | ||
| 98 | $self->{'where_columns'} = \@columns; | ||
| 99 | $self->{'where_values'} = \@values; | ||
| 100 | |||
| 101 | if (my $where = $self->{'where'}) { | ||
| 102 | my @conds; | ||
| 103 | while (my ($column, $value) = each %$where) { | ||
| 104 | push @columns, $column; | ||
| 105 | push @conds, "$column=?"; | ||
| 106 | push @values, $value; | ||
| 107 | } | ||
| 108 | if (@conds) { | ||
| 109 | my $cond = $self->{'where_clause'} = ' WHERE ' . join(' AND ', @conds); | ||
| 110 | $self->{'where_and'} = $cond . ' AND '; | ||
| 111 | } | ||
| 112 | } | ||
| 113 | ### ListStore-DBI where_clause: $self->{'where_clause'} | ||
| 114 | ### where_and: $self->{'where_and'} | ||
| 115 | ### where_values: $self->{'where_values'} | ||
| 116 | } | ||
| 117 | |||
| 118 | sub reread { | ||
| 119 | my ($self) = @_; | ||
| 120 | ### ListStore-DBI reread() | ||
| 121 | |||
| 122 | my $dbh = $self->{'dbh'}; | ||
| 123 | my $table = $self->{'table'}; | ||
| 124 | my $columns = $self->{'columns'}; | ||
| 125 | my $where_values = $self->{'where_values'}; | ||
| 126 | |||
| 127 | local $self->{'reading_database'} = 1; | ||
| 128 | |||
| 129 | unless ($dbh && $table && $columns) { | ||
| 130 | $self->clear; | ||
| 131 | return; | ||
| 132 | } | ||
| 133 | |||
| 134 | my $sth = ($self->{'sth'}->{'read'} ||= do { | ||
| 135 | $dbh->prepare ('SELECT ' . join(',', @$columns) | ||
| 136 | . " FROM ".$dbh->quote_identifier($table) | ||
| 137 | . " $self->{'where_clause'}" | ||
| 138 | . ($self->{'order_by'} | ||
| 139 | ? "ORDER BY $self->{'order_by'}" | ||
| 140 | : '')); | ||
| 141 | }); | ||
| 142 | $sth->execute (@$where_values); | ||
| 143 | |||
| 144 | local $self->{'reading_database'} = 1; | ||
| 145 | my $iter = $self->get_iter_first; | ||
| 146 | |||
| 147 | while (my @row = $sth->fetchrow_array) { | ||
| 148 | if ($iter) { | ||
| 149 | my @set; | ||
| 150 | foreach my $col (0 .. $#row) { | ||
| 151 | if (! _equal ($self->get_value ($iter, $col), $row[$col])) { | ||
| 152 | push @set, $col, $row[$col]; | ||
| 153 | } | ||
| 154 | } | ||
| 155 | if (@set) { | ||
| 156 | $self->set ($iter, @set); | ||
| 157 | } | ||
| 158 | $iter = $self->iter_next ($iter); | ||
| 159 | } else { | ||
| 160 | ### reread() append row | ||
| 161 | $self->insert_with_values (POSIX::INT_MAX(), | ||
| 162 | map {; ($_, $row[$_]) } (0 .. $#row)); | ||
| 163 | } | ||
| 164 | } | ||
| 165 | $sth->finish; | ||
| 166 | |||
| 167 | if ($iter) { | ||
| 168 | ### reread() remove excess | ||
| 169 | ### from: $self->get_path($iter)->to_string | ||
| 170 | ### to: $self->iter_n_children(undef) | ||
| 171 | while ($self->remove ($iter)) { | ||
| 172 | } | ||
| 173 | } | ||
| 174 | ### reread() done | ||
| 175 | } | ||
| 176 | |||
| 177 | sub _equal { | ||
| 178 | my ($x, $y) = @_; | ||
| 179 | if (defined $x) { | ||
| 180 | if (defined $y) { | ||
| 181 | return $x eq $y; | ||
| 182 | } | ||
| 183 | return 0; | ||
| 184 | } else { | ||
| 185 | return ! defined $y; | ||
| 186 | } | ||
| 187 | } | ||
| 188 | |||
| 189 | |||
| 190 | #------------------------------------------------------------------------------ | ||
| 191 | # local changes propagate to database | ||
| 192 | |||
| 193 | # 'row-changed' class closure | ||
| 194 | sub _do_row_changed { | ||
| 195 | my ($self, $path, $iter) = @_; | ||
| 196 | |||
| 197 | unless ($self->{'reading_database'}) { | ||
| 198 | ### ListStore-DBI _do_row_changed(): $path->to_string | ||
| 199 | |||
| 200 | my $dbh = $self->{'dbh'} || croak 'No DBI handle to store change'; | ||
| 201 | my $columns = $self->{'columns'}; | ||
| 202 | |||
| 203 | my $sth = ($self->{'sth'}->{'change'} ||= do { | ||
| 204 | $dbh->prepare ("UPDATE ".$dbh->quote_identifier($self->{'table'}) | ||
| 205 | . "SET " . join (',', map {; "$_=?" } @$columns) | ||
| 206 | . "$self->{'where_clause'}") | ||
| 207 | }); | ||
| 208 | |||
| 209 | my $affected = $sth->execute ($self->get_value ($iter, 0 .. $#$columns), | ||
| 210 | @{$self->{'where_values'}}); | ||
| 211 | $sth->finish; | ||
| 212 | |||
| 213 | if ($affected != 1) { | ||
| 214 | # $self->reread; | ||
| 215 | croak "ListStore-DBI: oops, expected to change 1, got $affected"; | ||
| 216 | } | ||
| 217 | } | ||
| 218 | return shift->signal_chain_from_overridden(@_); | ||
| 219 | } | ||
| 220 | |||
| 221 | # 'row-deleted' class closure | ||
| 222 | sub _do_row_deleted { | ||
| 223 | my ($self, $path) = @_; | ||
| 224 | |||
| 225 | unless ($self->{'reading_database'}) { | ||
| 226 | ### ListStore-DBI _do_row_deleted(): $path->to_string | ||
| 227 | |||
| 228 | my $dbh = $self->{'dbh'} || croak 'No DBI handle to apply delete'; | ||
| 229 | my $where_values = $self->{'where_values'}; | ||
| 230 | my $affected; | ||
| 231 | |||
| 232 | my $sth = ($self->{'sth'}->{'delete'} ||= do { | ||
| 233 | $dbh->prepare ("DELETE FROM ".$dbh->quote_identifier($self->{'table'}) | ||
| 234 | . " $self->{'where_clause'}") | ||
| 235 | }); | ||
| 236 | $affected = $sth->execute (@$where_values); | ||
| 237 | $sth->finish; | ||
| 238 | |||
| 239 | if ($affected != 1) { | ||
| 240 | # $self->reread; | ||
| 241 | croak "ListStore-DBI: oops, expected to delete 1, got $affected"; | ||
| 242 | } | ||
| 243 | } | ||
| 244 | return shift->signal_chain_from_overridden(@_); | ||
| 245 | } | ||
| 246 | |||
| 247 | # 'row-inserted' class closure | ||
| 248 | sub _do_row_inserted { | ||
| 249 | my ($self, $path, $iter) = @_; | ||
| 250 | ### ListStore-DBI _do_row_inserted(): $path->to_string | ||
| 251 | |||
| 252 | unless ($self->{'reading_database'}) { | ||
| 253 | my $dbh = $self->{'dbh'} || croak 'No DBI handle to apply insert'; | ||
| 254 | my $columns = $self->{'columns'}; | ||
| 255 | |||
| 256 | my $sth = ($self->{'sth'}->{'insert'} ||= do { | ||
| 257 | my @columns = (@{$self->{'where_columns'}}, @$columns); | ||
| 258 | $dbh->prepare | ||
| 259 | ("INSERT INTO " . $dbh->quote_identifier($self->{'table'}) | ||
| 260 | . " (" . join(',',@columns) | ||
| 261 | . ') VALUES (' . join(',', ('?')x(@columns)) . ')'); | ||
| 262 | }); | ||
| 263 | $sth->execute (@{$self->{'where_values'}}, | ||
| 264 | $self->get_value($iter,0 .. $#$columns)); | ||
| 265 | $sth->finish; | ||
| 266 | } | ||
| 267 | return shift->signal_chain_from_overridden(@_); | ||
| 268 | } | ||
| 269 | |||
| 270 | # # 'rows-reordered' connected on self | ||
| 271 | # sub _do_rows_reordered { | ||
| 272 | # my ($self, $path, $iter, $aref) = @_; | ||
| 273 | # | ||
| 274 | # unless ($self->{'reading_database'}) { | ||
| 275 | # ### ListStore-DBI _do_rows_reordered(): $aref | ||
| 276 | # | ||
| 277 | # my $dbh = $self->{'dbh'} || croak 'No DBI handle to reorder'; | ||
| 278 | # my $where_values = $self->{'where_values'}; | ||
| 279 | # | ||
| 280 | # my $sth_reorder = ($self->{'sth'}->{'reorder'} ||= do { | ||
| 281 | # $dbh->prepare ("UPDATE $self->{'table'} SET seq=?" | ||
| 282 | # . " $self->{'where_and'} seq=?") | ||
| 283 | # }); | ||
| 284 | # | ||
| 285 | # App::Chart::Database::call_with_transaction | ||
| 286 | # ($dbh, sub { | ||
| 287 | # foreach my $newpos (0 .. $#$aref) { | ||
| 288 | # my $oldpos = $aref->[$newpos]; | ||
| 289 | # if ($oldpos != $newpos) { | ||
| 290 | # ### renumber: "from $oldpos to ".(-1-$newpos) | ||
| 291 | # $sth_reorder->execute (-1-$newpos, @$where_values, $oldpos); | ||
| 292 | # $sth_reorder->finish; | ||
| 293 | # } | ||
| 294 | # } | ||
| 295 | # _negate ($self); | ||
| 296 | # }); | ||
| 297 | # # local $self->{'reading_database'} = 1; | ||
| 298 | # # App::Chart::Glib::Ex::DirBroadcast->send ('dbi-reordered', $key); | ||
| 299 | # } | ||
| 300 | # } | ||
| 301 | |||
| 302 | |||
| 303 | #------------------------------------------------------------------------------ | ||
| 304 | # compatibility | ||
| 305 | |||
| 306 | BEGIN { | ||
| 307 | unless (Gtk2::ListStore->can('insert_with_values')) { | ||
| 308 | eval <<'HERE'; | ||
| 309 | sub insert_with_values { | ||
| 310 | my $self = shift; | ||
| 311 | my $position = shift; | ||
| 312 | $self->set ($self->insert($position), @_); | ||
| 313 | } | ||
| 314 | HERE | ||
| 315 | } | ||
| 316 | } | ||
| 317 | |||
| 318 | 1; | ||
| 319 | __END__ |