App/Chart/Gtk2/Ex/ListStore/DBI.pm untested

filter: Statementstmt 0.0% 0 / 136 Branchbran 0.0% 0 / 36 Conditioncond 0.0% 0 / 24 MC/DCmcdc 0.0% 0 / 14 Subroutinesub 0.0% 0 / 19 Podpod 0.0% 0 / 9 totaltotal 0.0% 0 / 238 CC 19 SCAR 28.2
CC
19
Cov
0%
reread
56.0
SET_PROPERTY
12.0
_establish_where
12.0
CRAP 16.7
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__