lib/CallBackery/Database.pm

filter: Statementstmt 67.5% 102 / 151 Branchbran 72.2% 13 / 18 Conditioncond 30.0% 3 / 10 MC/DCmcdc 16.6% 1 / 6 Subroutinesub 62.5% 15 / 24 Podpod 93.3% 14 / 15 totaltotal 66.0% 148 / 224 CC 14 SCAR 10.7
CC
14
Cov
66%
getRowHash
6.0
insertIfNew
6.0
getConfigValue
6.0
CRAP 2.9
« lib/CallBackery.pm ↑ Summary blib/lib/CallBackery/GuiPlugin/AbstractHtml.pm »
1 package CallBackery::Database;
2  
3 # $Id: Database.pm 542 2013-12-12 16:36:34Z oetiker $
4  
5 11 ▶use Mojo::Base -base,-signatures;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 11 ▶use Data::Dumper;
Subroutine BEGIN: called CC=1 SCAR=0.0
8 11 ▶use Carp qw(croak);
Subroutine BEGIN: called CC=1 SCAR=0.0
9 11 ▶use CallBackery::Exception qw(mkerror);
Subroutine BEGIN: called CC=1 SCAR=0.0
10  
11 =head1 NAME
12  
13 CallBackery::Database - database access helpers
14  
15 =head1 SYNOPSIS
16  
17 use CallBackery::Database;
18 my $db = CallBackery::Database->new(app=>$self->config);
19 my ($fields,$values) = $self->map2sql(table,data);
20 my $selWhere = $self->map2where(table,data);
21 my $rowHash = $self->fetchRow(table,{field=>val,field=>val},selExpression?);
22 my $value = $self->fetchValue(table,{field=>val,field=>val},column);
23 my $id = $self->matchData(table,{field=>val,field=>val});
24 my $id = $self->lookUp(table,field,value);
25 my $id = $self->updateOrInsertData(table,{dataField=>val,...},{matchField=>val,...}?);
26 my $id = $self->insertIfNew(table,{field=>val,field=>val});
27  
28 =head1 DESCRIPTION
29  
30 Database access helpers.
31  
32 =head2 config
33  
34 object needs access to the system config to get access to the database
35  
36 =cut
37  
38 0 ▶has app => sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
39 0 croak "app property is required";
40 11 }, weak => 1;
41  
42 0 ▶has userName => sub {
Subroutine __ANON__: not called CC=1 SCAR=6.9
43 0 return "* no user *";
44 11 };
45  
46 7 ▶has config => sub {
Subroutine __ANON__: called CC=1 SCAR=0.0
47 7 shift->app->config;
48 11 }, weak => 1;
49  
50  
51 =head2 dhb
52  
53 a dbi database handle
54  
55 =cut
56  
57 11 my $lastFlush = time;
58  
59 7 ▶has sql => sub {
Subroutine __ANON__: called CC=1 SCAR=0.0
60 7 my $self = shift;
61 7 require Mojo::SQLite;
62 7 my $sql = Mojo::SQLite->new($self->config->cfgHash->{BACKEND}{cfg_db});
63  
64 7 $sql->options({
65 RaiseError => 1,
66 PrintError => 0,
67 AutoCommit => 1,
68 ShowErrorStatement => 1,
69 sqlite_unicode => 1,
70 FetchHashKeyName=>'NAME_lc',
71 });
72  
73 83 ▶ $sql->on(connection => sub ($sql, $dbh) {
Subroutine __ANON__: called CC=1 SCAR=0.0
74 83 $dbh->do('PRAGMA foreign_keys = ON;');
75 7 });
76  
77 7 $sql->migrations
78 ->name('cbmig')
79 ->from_data(__PACKAGE__,'dbsetup.sql')
80 ->migrate;
81  
82 7 return $sql;
83 11 };
84  
85 # this must be fresh ... always!
86 159 ▶sub mojoSqlDb {
Subroutine mojoSqlDb: called CC=1 SCAR=0.0
Pod undocumented
87 159 my $self = shift;
88 159 return $self->sql->db;
89 };
90  
91 =over 4
92  
93 =item my($fields,$values) = $self->C<map2sql(table,data)>;
94  
95 Provide two hash pointers and quote the field names for inclusion into an
96 SQL expression. Build field names according to the table_field rule.
97  
98 =cut
99  
100 12 ▶sub map2sql {
Subroutine map2sql: called CC=2 SCAR=6.9
Pod documented
101 12 my $self = shift;
102 12 my $table = shift;
103 12 my $data = shift;
104 12 my @values;
105 my @fields;
106 12 ▶ while (my($field, $value) = each %$data) {
Branch: 1 branch
BranchTrueFalseTotal
if (() = each %$data) 33 12 45
107 33 push @fields,$self->mojoSqlDb->dbh->quote_identifier($table."_".$field);
108 33 push @values,$value;
109 }
110 12 return (\@fields,\@values);
111 }
112  
113 =item my $sqlWhere = $self->C<map2where(table,data)>;
114  
115 build a where statement Find a record matching the given data in a table the
116 data is a map. Quote field names and values. Build field names according to
117 the table_field rule.
118  
119 =cut
120  
121 47 ▶sub map2where {
Subroutine map2where: called CC=3 SCAR=11.0
Pod documented
122 47 my $self = shift;
123 47 my $table = shift;
124 47 my $data = shift;
125 47 my $db = $self->mojoSqlDb;
126 47 my @expression;
127 47 ▶ while (my($field, $value) = each %$data) {
Branch: 1 branch
BranchTrueFalseTotal
if (() = each %$data) 53 47 100
128 53 my $field = $db->dbh->quote_identifier($table."_".$field);
129 53 my $expr;
130 53 ▶ if (defined $value){
Branch: 1 branch
BranchTrueFalseTotal
if (defined $value) { } 51 2 53
131 51 $expr = $field.' = '.$db->dbh->quote($value);
132 }
133 else {
134 2 $expr = $field.' is null';
135 }
136 53 push @expression, $expr;
137 }
138 47 return (join ' AND ',@expression);
139 }
140  
141 =item $hashRef = $self->C<getMap(table,column)>;
142  
143 Get an array of hashes with model and label tags:
144  
145 [{model: x, label: y},{id ...},...]
146  
147 =cut
148  
149 0 ▶sub getMap {
Subroutine getMap: not called CC=1 SCAR=6.9
Pod documented
150 0 my $self = shift;
151 0 my $table = shift;
152 0 my $column = shift;
153 0 my $db = $self->mojoSqlDb;
154 0 my $sqlId = $db->dbh->quote_identifier($table."_id");
155 0 my $sqlColumn = $db->dbh->quote_identifier($table."_".$column);
156 0 my $sqlTable = $db->dbh->quote_identifier($table);
157 0 my $SQL = <<"SQL";
158 SELECT $sqlId as model, $sqlColumn as label
159 FROM $sqlTable
160 ORDER by $sqlColumn
161 SQL
162 0 return $db->dbh->selectall_arrayref($SQL,{Slice=>{}});
163 }
164  
165 =item $hashRef = $self->C<getRowHash(table,{key=>value,....},$selectExpr?)>;
166  
167 Get a hash with record index as key. Optionally with a list of columns to return.
168  
169 {
170 2 => { a=>x, b=>y },
171 3 => { a=>k, b=>r }
172 }
173  
174 =cut
175  
176 0 ▶sub getRowHash {
Subroutine getRowHash: not called CC=2 SCAR=17.9
Pod documented
177 0 my $self = shift;
178 0 my $table = shift;
179 0 my $data = shift;
180 0 ▶ my $selectCols = shift // '*';
Condition0%0/2
shift() // "*"
l!l
00
MC/DC0%0/1
shift() // "*"
shift()
Truth table0%0/2
A
A= shift()
Aresult
11
00
181 0 my $db = $self->mojoSqlDb;
182 0 my $sqlTable = $db->dbh->quote_identifier($table);
183 0 my $sqlWhere = $self->map2where($table,$data);
184 0 my $SQL = <<"SQL";
185 SELECT $selectCols
186 FROM $sqlTable
187 WHERE $sqlWhere
188 SQL
189 0 return $db->dbh->selectall_hashref($SQL,$table."_id",{Slice=>{}});
190 }
191  
192  
193 =item $id = $self->C<fetchRow(table,{key=>value,key=>value},$selectExp ?)>;
194  
195 Find a record matching the given data in a table and return a hash of the matching record.
196  
197 =cut
198  
199 42 ▶sub fetchRow {
Subroutine fetchRow: called CC=2 SCAR=6.9
Pod documented
200 42 my $self = shift;
201 42 my $table = shift;
202 42 my $data = shift;
203 42 ▶ my $selectCols = shift // '*';
Condition100%2/2
shift() // "*"
l!l
1428
MC/DC100%1/1
shift() // "*"
shift()
Truth table100%2/2
A
A= shift()
Aresult
11
00
204 42 my $db = $self->mojoSqlDb;
205 42 my $sqlWhere = $self->map2where($table,$data);
206 42 my $sqlTable = $db->dbh->quote_identifier($table);
207 42 my $SQL = <<"SQL";
208 SELECT $selectCols
209 FROM $sqlTable
210 WHERE $sqlWhere
211 SQL
212 42 return $db->dbh->selectrow_hashref($SQL);
213 }
214  
215 =item $id = $self->C<fetchValue(table,{key=>value,key=>value},column)>;
216  
217 Find a record matching the given data in a table and returns the value in column.
218  
219 =cut
220  
221 14 ▶sub fetchValue {
Subroutine fetchValue: called CC=2 SCAR=6.9
Pod documented
222 14 my $self = shift;
223 14 my $table = shift;
224 14 my $where = shift;
225 14 my $column = shift;
226 14 my $db = $self->mojoSqlDb;
227 14 my $row = $self->fetchRow($table,$where,$db->dbh->quote_identifier($table.'_'.$column));
228 14 ▶ if ($row){
Branch: 1 branch
BranchTrueFalseTotal
if ($row) { } 9 5 14
229 9 return $row->{$table.'_'.$column};
230 }
231 else {
232 5 return undef;
233 }
234 }
235  
236  
237 =item $id = $self->C<matchData(table,data)>;
238  
239 Find a record matching the given data in a table
240 the data is a map.
241  
242 =cut
243  
244 13 ▶sub matchData {
Subroutine matchData: called CC=1 SCAR=0.0
Pod documented
245 13 my $self = shift;
246 13 my $table = shift;
247 13 my $data = shift;
248 13 my $found = $self->fetchValue($table,$data,"id");
249 13 return $found;
250  
251 }
252  
253 =item $id = $self->C<lookUp(table,column,value)>
254  
255 Lookup the value in table in table_column and return table_id.
256 Throw an exception if this fails. Use matchData if you are just looking.
257  
258 =cut
259  
260 7 ▶sub lookUp {
Subroutine lookUp: called CC=2 SCAR=7.0
Pod documented
261 7 my $self = shift;
262 7 my $table = shift;
263 7 my $column = shift;
264 7 my $value = shift;
265 7 ▶ my $id = $self->matchData($table,{$column => $value})
Branch: 1 branch
BranchTrueFalseTotal
my $id = $self->matchData($table, {$column, $value}) or die mkerror(1349, "Lookup for $column = $value in $table faild") 7 0 7
266 or die mkerror(1349,"Lookup for $column = $value in $table faild");
267 7 return $id;
268 }
269  
270 =item $id = $self->C<updateOrInsertData(table,data,match?)>
271  
272 Insert the given data into the table. If a match map is given, try an update first
273 with the given match only insert when update has 0 hits.
274  
275 =cut
276  
277 12 ▶sub updateOrInsertData {
Subroutine updateOrInsertData: called CC=5 SCAR=16.3
Pod documented
278 12 my $self = shift;
279 12 my $table = shift;
280 12 my $data = shift;
281 12 my $match = shift;
282 12 my $db = $self->mojoSqlDb;
283 12 my ($colNames,$colValues) = $self->map2sql($table,$data);
284 12 my $sqlTable = $db->dbh->quote_identifier($table);
285 12 my $sqlIdCol = $db->dbh->quote_identifier($table."_id");
286 12 my $sqlColumns = join ', ', @$colNames;
287 12 my $sqlSet = join ', ', map { "$_ = ?" } @$colNames;
288 12 my $sqlData = join ', ', map { '?' } @$colValues;
289 12 ▶ if ($match){ # try update first if we have an id
Branch: 1 branch
BranchTrueFalseTotal
if ($match) 5 7 12
290 5 my $matchWhere = $self->map2where($table,$match);
291 5 my $SQL = <<"SQL";
292 UPDATE $sqlTable SET $sqlSet
293 WHERE $matchWhere
294 SQL
295 5 my $count = $db->dbh->do($SQL,{},@$colValues);
296 5 ▶ if ($count > 0){
Branch: 1 branch
BranchTrueFalseTotal
if ($count > 0) 5 0 5
297 5 ▶ return ( $data->{id} // $match->{id} );
Condition33%1/3
$data->{"id"} // $match->{"id"}
l!l&&r!l&&!r
050
MC/DC0%0/2
$data->{"id"} // $match->{"id"}
$data->{"id"} $match->{"id"}
Truth table33%1/3
A // B
A= $data->{"id"}B= $match->{"id"}
ABresult
1X1
011
000
298 }
299 }
300 7 my $SQL = <<"SQL";
301 INSERT INTO $sqlTable ( $sqlColumns )
302 VALUES ( $sqlData )
303 SQL
304 7 $db->dbh->do($SQL,{},@$colValues);
305  
306 # non serial primary key, id defined by user
307 7 ▶ if (exists $data->{'id'}){
Branch: 1 branch
BranchTrueFalseTotal
if (exists $data->{'id'}) { } 0 7 7
308 0 return $data->{'id'};
309 }
310 # serial primary key
311 else{
312 7 return $db->dbh->last_insert_id(undef,undef,$table,$table."_id");
313 }
314 }
315  
316 =item $id = $self->C<insertIfNew(table,data)>
317  
318 Lookup the given data. If it is new, insert a record. Returns the matching Id.
319  
320 =cut
321  
322 0 ▶sub insertIfNew {
Subroutine insertIfNew: not called CC=2 SCAR=17.9
Pod documented
323 0 my $self = shift;
324 0 my $table = shift;
325 0 my $data = shift;
326 0 ▶ return ( $self->matchData($table,$data)
Condition0%0/3
$self->matchData($table, $data) // $self->updateOrInsertData($table, $data)
l!l&&r!l&&!r
000
MC/DC0%0/2
$self->matchData($table, $data) // $self->updateOrInsertData($table, $data)
$self->matchData($table, $data) $self->updateOrInsertData($table, $data)
Truth table0%0/3
A // B
A= $self->matchData($table, $data)B= $self->updateOrInsertData($table, $data)
ABresult
1X1
011
000
327 // $self->updateOrInsertData($table,$data));
328 }
329  
330 =item $id = $self->C<deleteData(table,id)>
331  
332 Delete data from table. Given the record id.
333 Returns true if the record was deleted.
334  
335 =cut
336  
337 0 ▶sub deleteData {
Subroutine deleteData: not called CC=1 SCAR=6.9
Pod documented
338 0 my $self = shift;
339 0 my $table = shift;
340 0 my $id = shift;
341 0 return $self->deleteDataWhere($table,{id=>$id});
342 }
343  
344 =item $id = $self->C<deleteDataWhere(table,{key=>val,key=>val})>
345  
346 Delete data from table. Given the column title and the matching value.
347 Returns true if the record was deleted.
348  
349 =cut
350  
351 0 ▶sub deleteDataWhere {
Subroutine deleteDataWhere: not called CC=1 SCAR=6.9
Pod documented
352 0 my $self = shift;
353 0 my $table = shift;
354 0 my $match = shift;
355 0 my $val = shift;
356 0 my $db = $self->mojoSqlDb;
357 0 my $sqlTable = $db->dbh->quote_identifier($table);
358 0 my $matchWhere = $self->map2where($table,$match);
359 0 my $SQL = 'DELETE FROM '.$sqlTable.' WHERE '.$matchWhere;
360 # say $SQL;
361 0 return $db->dbh->do($SQL);
362 }
363  
364 =item getConfigValue($key)
365  
366 return a raw data value from the config table
367  
368 =cut
369  
370 0 ▶sub getConfigValue {
Subroutine getConfigValue: not called CC=2 SCAR=17.9
Pod documented
371 0 my $self = shift;
372 0 my $key = shift;
373 0 my $value = eval {
374 0 local $SIG{__DIE__};
375 0 $self->fetchValue('cbconfig',{id => $key},'value');
376 };
377 0 ▶ return ($@ ? undef : $value);
Branch: 1 branch
BranchTrueFalseTotal
$@ ? : 0 0 0
378 }
379  
380 =item setConfigValue($key,$value)
381  
382 write a config value
383  
384 =cut
385  
386 0 ▶sub setConfigValue {
Subroutine setConfigValue: not called CC=1 SCAR=6.9
Pod documented
387 0 my $self = shift;
388 0 my $key = shift;
389 0 my $value = shift;
390 # warn "SET $key -> ".Dumper([$value]);
391 0 $self->updateOrInsertData('cbconfig',{
392 id=> $key,
393 value => $value
394 }, { id => $key });
395 0 return $value;
396 }
397  
398 11 1;
399  
400 =back
401  
402 =head1 COPYRIGHT
403  
404 Copyright (c) 2015 by OETIKER+PARTNER AG. All rights reserved.
405  
406 =head1 AUTHOR
407  
408 S<Tobi Oetiker E<lt>tobi@oetiker.chE<gt>>
409  
410 =head1 HISTORY
411  
412 2010-06-12 to 1.0 initial
413 2013-11-19 to 1.1 converted to mojo
414  
415 =cut
416  
417 __DATA__
« lib/CallBackery.pm ↑ Summary blib/lib/CallBackery/GuiPlugin/AbstractHtml.pm »