SearchBuilder/Handle/mysql.pm

filter: Statementstmt 14.8% 21 / 141 Branchbran 6.2% 4 / 64 Conditioncond 0.0% 0 / 16 MC/DCmcdc 0.0% 0 / 8 Subroutinesub 27.2% 6 / 22 Podpod 78.5% 11 / 14 totaltotal 15.8% 42 / 265 CC 44 SCAR 35.3
CC
44
Cov
11%
DistinctQuery
90.0
SimpleMassChangeFromSelect
72.0
Fields
42.0
CRAP 34.2
« SearchBuilder/Handle/Pg.pm ↑ Summary SearchBuilder/Handle/MariaDB.pm »
1 package DBIx::SearchBuilder::Handle::mysql;
2  
3 2 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 2 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 2 ▶use version;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 2 ▶use base qw(DBIx::SearchBuilder::Handle);
Subroutine BEGIN: called CC=1 SCAR=0.0
8  
9 =head1 NAME
10  
11 DBIx::SearchBuilder::Handle::mysql - A mysql specific Handle object
12  
13 =head1 SYNOPSIS
14  
15  
16 =head1 DESCRIPTION
17  
18 This module provides a subclass of DBIx::SearchBuilder::Handle that
19 compensates for some of the idiosyncrasies of MySQL.
20  
21 =head1 METHODS
22  
23 =head2 Insert
24  
25 Takes a table name as the first argument and assumes that the rest of the arguments are an array of key-value pairs to be inserted.
26  
27 If the insert succeeds, returns the id of the insert, otherwise, returns
28 a Class::ReturnValue object with the error reported.
29  
30 =cut
31  
32 0 ▶sub Insert {
Subroutine Insert: not called CC=4 SCAR=30.0
Pod documented
33 0 my $self = shift;
34  
35 0 my $sth = $self->SUPER::Insert(@_);
36 0 ▶ if (!$sth) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($sth) 0 0 0
37 0 return ($sth);
38 }
39  
40 0 $self->{'id'}=$self->dbh->{'mysql_insertid'};
41  
42 # Yay. we get to work around mysql_insertid being null some of the time :/
43 0 ▶ unless ($self->{'id'}) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($self->{"id"}) 0 0 0
44 0 $self->{'id'} = $self->FetchResult('SELECT LAST_INSERT_ID()');
45 }
46 0 ▶ warn "$self no row id returned on row creation" unless ($self->{'id'});
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"id"} 0 0 0
47  
48 0 return( $self->{'id'}); #Add Succeded. return the id
49 }
50  
51  
52 =head2 SimpleUpdateFromSelect
53  
54 Customization of L<DBIx::SearchBuilder::Handle/SimpleUpdateFromSelect>.
55 Mysql doesn't support update with subqueries when those fetch data from
56 the table that is updated.
57  
58 =cut
59  
60 0 ▶sub SimpleUpdateFromSelect {
Subroutine SimpleUpdateFromSelect: not called CC=5 SCAR=34.0
Pod documented
61 0 my ($self, $table, $values, $query, @query_binds) = @_;
62  
63 0 ▶ return $self->SUPER::SimpleUpdateFromSelect(
Branch: 1 branch
BranchTrueFalseTotal
unless $query =~ /\b\Q$table\E\b/i 0 0 0
64 $table, $values, $query, @query_binds
65 ) unless $query =~ /\b\Q$table\E\b/i;
66  
67 0 my $sth = $self->SimpleQuery( $query, @query_binds );
68 0 ▶ return $sth unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
69  
70 0 my (@binds, @columns);
71 0 for my $k (sort keys %$values) {
72 0 push @columns, $k;
73 0 push @binds, $values->{$k};
74 }
75  
76 0 ▶ $table = $self->QuoteName($table) if $self->{'QuoteTableNames'};
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"QuoteTableNames"} 0 0 0
77 0 my $update_query = "UPDATE $table SET "
78 . join( ', ', map "$_ = ?", @columns )
79 .' WHERE ID IN ';
80  
81 0 return $self->SimpleMassChangeFromSelect(
82 $update_query, \@binds,
83 $query, @query_binds
84 );
85 }
86  
87  
88 0 ▶sub DeleteFromSelect {
Subroutine DeleteFromSelect: not called CC=3 SCAR=24.8
Pod undocumented
89 0 my ($self, $table, $query, @query_binds) = @_;
90  
91 0 ▶ return $self->SUPER::DeleteFromSelect(
Branch: 1 branch
BranchTrueFalseTotal
unless $query =~ /\b\Q$table\E\b/i 0 0 0
92 $table, $query, @query_binds
93 ) unless $query =~ /\b\Q$table\E\b/i;
94  
95 0 ▶ $table = $self->QuoteName($table) if $self->{'QuoteTableNames'};
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"QuoteTableNames"} 0 0 0
96 0 return $self->SimpleMassChangeFromSelect(
97 "DELETE FROM $table WHERE id IN ", [],
98 $query, @query_binds
99 );
100 }
101  
102 0 ▶sub SimpleMassChangeFromSelect {
Subroutine SimpleMassChangeFromSelect: not called CC=8 SCAR=42.8
Pod undocumented
103 0 my ($self, $update_query, $update_binds, $search, @search_binds) = @_;
104  
105 0 my $sth = $self->SimpleQuery( $search, @search_binds );
106 0 ▶ return $sth unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
107  
108  
109 # tried TEMPORARY tables, much slower than fetching and delete
110 # also size of ENGINE=MEMORY is limitted by option, on disk
111 # tables more slower than in memory
112 0 my $res = 0;
113  
114 0 my @ids;
115 0 ▶ while ( my $id = ($sth->fetchrow_array)[0] ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $id = ($sth->fetchrow_array)[0]) 0 0 0
116 0 push @ids, $id;
117 0 ▶ next if @ids < 1000;
Branch: 1 branch
BranchTrueFalseTotal
if @ids < 1000 0 0 0
118  
119 0 my $q = $update_query .'('. join( ',', ('?')x@ids ) .')';
120 0 my $sth = $self->SimpleQuery( $q, @$update_binds, splice @ids );
121 0 ▶ return $sth unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
122  
123 0 $res += $sth->rows;
124 }
125 0 ▶ if ( @ids ) {
Branch: 1 branch
BranchTrueFalseTotal
if (@ids) 0 0 0
126 0 my $q = $update_query .'('. join( ',', ('?')x@ids ) .')';
127 0 my $sth = $self->SimpleQuery( $q, @$update_binds, splice @ids );
128 0 ▶ return $sth unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
129  
130 0 $res += $sth->rows;
131 }
132 0 ▶ return $res == 0? '0E0': $res;
Branch: 1 branch
BranchTrueFalseTotal
$res == 0 ? : 0 0 0
133 }
134  
135 =head2 DatabaseVersion
136  
137 Returns the mysql version, trimming off any -foo identifier
138  
139 =cut
140  
141 0 ▶sub DatabaseVersion {
Subroutine DatabaseVersion: not called CC=1 SCAR=6.9
Pod documented
142 0 my $self = shift;
143 0 my $v = $self->SUPER::DatabaseVersion();
144  
145 0 $v =~ s/\-.*$//;
146 0 return ($v);
147 }
148  
149 =head2 CaseSensitive
150  
151 Returns undef, since mysql's searches are not case sensitive by default
152  
153 =cut
154  
155 0 ▶sub CaseSensitive {
Subroutine CaseSensitive: not called CC=1 SCAR=6.9
Pod documented
156 0 my $self = shift;
157 0 return(undef);
158 }
159  
160 0 ▶sub DistinctQuery {
Subroutine DistinctQuery: not called CC=9 SCAR=45.0
Pod documented
161 0 my $self = shift;
162 0 my $statementref = shift;
163 0 my $sb = shift;
164  
165 0 ▶ return $self->SUPER::DistinctQuery( $statementref, $sb, @_ )
Branch: 1 branch
BranchTrueFalseTotal
unless $self->_DistinctNeedsGroupBy($sb) 0 0 0
166 unless $self->_DistinctNeedsGroupBy($sb);
167  
168 0 ▶ if ( substr($self->DatabaseVersion, 0, 1) == 4 ) {
Branch: 1 branch
BranchTrueFalseTotal
if (substr($self->DatabaseVersion, 0, 1) == 4) { } 0 0 0
169 0 local $sb->{'group_by'} = [{FIELD => 'id'}];
170  
171 0 my ($idx, @tmp, @specials) = (0, ());
172 0 foreach ( @{$sb->{'order_by'}} ) {
173 0 ▶ if ( !exists $_->{'ALIAS'} || ($_->{'ALIAS'}||'') eq "main" ) {
Branch: 1 branch
BranchTrueFalseTotal
if (not exists $_->{"ALIAS"} or ($_->{"ALIAS"} || "") eq "main") 0 0 0
Condition0%0/5
not exists $_->{"ALIAS"} or ($_->{"ALIAS"} || "") eq "main"
l!l&&r!l&&!r
000
$_->{'ALIAS'} || ''
l!l
00
MC/DC0%0/2
not exists $_->{"ALIAS"} or ($_->{"ALIAS"} || "") eq "main"
not exists $_->{"ALIAS"} $_->{'ALIAS'}
Truth table0%0/3
not(A) or B
A= not exists $_->{"ALIAS"}B= $_->{'ALIAS'}
ABresult
1X1
011
000
174 0 push @tmp, $_; next;
175 }
176  
177 push @specials,
178 ((($_->{'ORDER'}||'') =~ /^des/i)?'MAX':'MIN')
179 0 ▶ ."(". $_->{'ALIAS'} .".". $_->{'FIELD'} .")"
Branch: 1 branch
BranchTrueFalseTotal
($_->{'ORDER'} || '') =~ /^des/i ? : 0 0 0
Condition0%0/2
$_->{'ORDER'} || ''
l!l
00
MC/DC0%0/1
$_->{'ORDER'} || ''
$_->{'ORDER'}
Truth table0%0/2
A
A= $_->{'ORDER'}
Aresult
11
00
180 ." __special_sort_$idx";
181 0 push @tmp, { ALIAS => '', FIELD => "__special_sort_$idx", ORDER => $_->{'ORDER'} };
182 0 $idx++;
183 }
184  
185 0 local $sb->{'order_by'} = \@tmp;
186 0 $$statementref = "SELECT ". join( ", ", 'main.*', @specials ) ." FROM $$statementref";
187 0 $$statementref .= $sb->_GroupClause;
188 0 $$statementref .= $sb->_OrderClause;
189 } else {
190 0 local $sb->{'group_by'} = [{FIELD => 'id'}];
191 0 local $sb->{'order_by'} = $self->_DistinctGroupByOrder($sb);
192 0 $$statementref = "SELECT main.* FROM $$statementref";
193 0 $$statementref .= $sb->_GroupClause;
194 0 $$statementref .= $sb->_OrderClause;
195 }
196 }
197  
198 0 ▶sub Fields {
Subroutine Fields: not called CC=6 SCAR=37.4
Pod undocumented
199 0 my $self = shift;
200 0 my $table = shift;
201  
202 0 my $cache = \%DBIx::SearchBuilder::Handle::FIELDS_IN_TABLE;
203 0 ▶ unless ( $cache->{ lc $table } ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($$cache{lc $table}) 0 0 0
204 0 ▶ my $sth = $self->dbh->column_info( undef, undef, $table, '%' )
Branch: 1 branch
BranchTrueFalseTotal
my $sth = $self->dbh->column_info(undef, undef, $table, "%") or return () 0 0 0
205 or return ();
206 0 my $info = $sth->fetchall_arrayref({});
207 0 foreach my $e ( sort {$a->{'ORDINAL_POSITION'} <=> $b->{'ORDINAL_POSITION'}} @$info ) {
208 0 ▶ push @{ $cache->{ lc $e->{'TABLE_NAME'} } ||= [] }, lc $e->{'COLUMN_NAME'};
Condition0%0/2
$$cache{lc $e->{"TABLE_NAME"}} ||= []
l!l
00
MC/DC0%0/1
$$cache{lc $e->{"TABLE_NAME"}} ||= []
$$cache{lc $e->{"TABLE_NAME"}}
Truth table0%0/2
A
A= $$cache{lc $e->{"TABLE_NAME"}}
Aresult
11
00
209 }
210 }
211 0 ▶ return @{ $cache->{ lc $table } || [] };
Condition0%0/2
$$cache{lc $table} || []
l!l
00
MC/DC0%0/1
$$cache{lc $table} || []
$$cache{lc $table}
Truth table0%0/2
A
A= $$cache{lc $table}
Aresult
11
00
212 }
213  
214 =head2 SimpleDateTimeFunctions
215  
216 Returns hash reference with specific date time functions of this
217 database for L<DBIx::SearchBuilder::Handle/DateTimeFunction>.
218  
219 =cut
220  
221 0 ▶sub SimpleDateTimeFunctions {
Subroutine SimpleDateTimeFunctions: not called CC=2 SCAR=17.9
Pod documented
222 0 my $self = shift;
223 return $self->{'_simple_date_time_functions'} ||= {
224 0 ▶ %{ $self->SUPER::SimpleDateTimeFunctions(@_) },
Condition0%0/2
$self->{"_simple_date_time_functions"} ||= {%{$self->SUPER::SimpleDateTimeFunctions(@_);}, "datetime", "?", "time", "TIME(?)", "hourly", "DATE_FORMAT(?, '%Y-%m-%d %H')", "hour", "HOUR(?)", "date", "DATE(?)", "daily", "DATE(?)", "day", "DAYOFMONTH(?)", "dayofmonth", "DAYOFMONTH(?)", "monthly", "DATE_FORMAT(?, '%Y-%m')", "month", "MONTH(?)", "annually", "YEAR(?)", "year", "YEAR(?)", "dayofweek", "DAYOFWEEK(?) - 1", "dayofyear", "DAYOFYEAR(?)", "weekofyear", "WEEK(?)"}
l!l
00
MC/DC0%0/1
$self->{"_simple_date_time_functions"} ||= {%{$self->SUPER::SimpleDateTimeFunctions(@_);}, "datetime", "?", "time", "TIME(?)", "hourly", "DATE_FORMAT(?, '%Y-%m-%d %H')", "hour", "HOUR(?)", "date", "DATE(?)", "daily", "DATE(?)", "day", "DAYOFMONTH(?)", "dayofmonth", "DAYOFMONTH(?)", "monthly", "DATE_FORMAT(?, '%Y-%m')", "month", "MONTH(?)", "annually", "YEAR(?)", "year", "YEAR(?)", "dayofweek", "DAYOFWEEK(?) - 1", "dayofyear", "DAYOFYEAR(?)", "weekofyear", "WEEK(?)"}
$self->{"_simple_date_time_functions"}
Truth table0%0/2
A
A= $self->{"_simple_date_time_functions"}
Aresult
11
00
225 datetime => '?',
226 time => 'TIME(?)',
227  
228 hourly => "DATE_FORMAT(?, '%Y-%m-%d %H')",
229 hour => 'HOUR(?)',
230  
231 date => 'DATE(?)',
232 daily => 'DATE(?)',
233  
234 day => 'DAYOFMONTH(?)',
235 dayofmonth => 'DAYOFMONTH(?)',
236  
237 monthly => "DATE_FORMAT(?, '%Y-%m')",
238 month => 'MONTH(?)',
239  
240 annually => 'YEAR(?)',
241 year => 'YEAR(?)',
242  
243 dayofweek => "DAYOFWEEK(?) - 1", # 1-7, 1 - Sunday
244 dayofyear => "DAYOFYEAR(?)", # 1-366
245 weekofyear => "WEEK(?)", # skip mode argument, so it can be controlled in mysql config
246 };
247 }
248  
249  
250 =head2 ConvertTimezoneFunction
251  
252 Custom implementation of L<DBIx::SearchBuilder::Handle/ConvertTimezoneFunction>.
253  
254 Use the following query to get list of timezones:
255  
256 SELECT Name FROM mysql.time_zone_name;
257  
258 Read docs about keeping timezone data up to date:
259  
260 http://dev.mysql.com/doc/refman/5.5/en/time-zone-upgrades.html
261  
262 =cut
263  
264 0 ▶sub ConvertTimezoneFunction {
Subroutine ConvertTimezoneFunction: not called CC=5 SCAR=34.0
Pod documented
265 0 my $self = shift;
266 0 my %args = (
267 From => 'UTC',
268 To => undef,
269 Field => '',
270 @_
271 );
272 0 ▶ return $args{'Field'} unless $args{From} && $args{'To'};
Branch: 1 branch
BranchTrueFalseTotal
unless $args{"From"} and $args{"To"} 0 0 0
Condition0%0/3
$args{"From"} and $args{"To"}
!ll&&!rl&&r
000
MC/DC0%0/2
$args{"From"} and $args{"To"}
$args{"From"} $args{"To"}
Truth table0%0/3
A and B
A= $args{"From"}B= $args{"To"}
ABresult
0X0
100
111
273 0 ▶ return $args{'Field'} if lc $args{From} eq lc $args{'To'};
Branch: 1 branch
BranchTrueFalseTotal
if lc $args{"From"} eq lc $args{"To"} 0 0 0
274 0 my $dbh = $self->dbh;
275 0 $_ = $dbh->quote( $_ ) foreach @args{'From', 'To'};
276 0 return "CONVERT_TZ( $args{'Field'}, $args{'From'}, $args{'To'} )";
277 }
278  
279 0 ▶sub _DateTimeIntervalFunction {
Subroutine _DateTimeIntervalFunction: not called CC=1 SCAR=6.9
280 0 my $self = shift;
281 0 my %args = ( From => undef, To => undef, @_ );
282  
283 0 return "TIMESTAMPDIFF(SECOND, $args{'From'}, $args{'To'})";
284 }
285  
286  
287 =head2 QuoteName
288  
289 Quote table or column name to avoid reserved word errors.
290  
291 =cut
292  
293 # over-rides inherited method
294 1 ▶sub QuoteName {
Subroutine QuoteName: called CC=2 SCAR=7.6
Pod documented
295 1 my ($self, $name) = @_;
296 # use dbi built in quoting if we have a connection,
297 1 ▶ if ($self->dbh) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->dbh) 0 1 1
298 0 return $self->SUPER::QuoteName($name);
299 }
300  
301 1 return sprintf('`%s`', $name);
302 }
303  
304 4 ▶sub DequoteName {
Subroutine DequoteName: called CC=3 SCAR=11.2
Pod documented
305 4 my ($self, $name) = @_;
306  
307 # If we have a handle, the base class can do it for us
308 4 ▶ if ($self->dbh) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->dbh) 0 4 4
309 0 return $self->SUPER::DequoteName($name);
310 }
311  
312 4 ▶ if ($name =~ /^`(.*)`$/) {
Branch: 1 branch
BranchTrueFalseTotal
if ($name =~ /^`(.*)`$/) 1 3 4
313 1 return $1;
314 }
315 3 return $name;
316 }
317  
318 0 ▶sub _ExtractBindValues {
Subroutine _ExtractBindValues: not called CC=1 SCAR=6.9
319 0 my $self = shift;
320 0 my $value = shift;
321 0 return $self->SUPER::_ExtractBindValues( $value, '\\' );
322 }
323  
324 0 ▶sub _IsMariaDB {
Subroutine _IsMariaDB: not called CC=1 SCAR=6.9
325 0 my $self = shift;
326  
327 # We override DatabaseVersion to chop off "-MariaDB-whatever", so
328 # call super here to get the original version
329 0 my $v = $self->SUPER::DatabaseVersion();
330  
331 0 return ($v =~ /mariadb/i);
332 }
333  
334 0 ▶sub _RequireQuotedTables {
Subroutine _RequireQuotedTables: not called CC=3 SCAR=24.8
335 0 my $self = shift;
336  
337 # MariaDB version does not match mysql, and hasn't added new reserved words
338 0 ▶ return 0 if $self->_IsMariaDB;
Branch: 1 branch
BranchTrueFalseTotal
if $self->_IsMariaDB 0 0 0
339  
340 0 my $version = $self->DatabaseVersion;
341  
342 # Get major version number by chopping off everything after the first "."
343 0 $version =~ s/\..*//;
344 0 ▶ if ( $version >= 8 ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($version >= 8) 0 0 0
345 0 return 1;
346 }
347 0 return 0;
348 }
349  
350 =head2 HasSupportForCombineSearchAndCount
351  
352 MariaDB 10.2+ and MySQL 8+ support this.
353  
354 =cut
355  
356 0 ▶sub HasSupportForCombineSearchAndCount {
Subroutine HasSupportForCombineSearchAndCount: not called CC=4 SCAR=30.0
Pod documented
357 0 my $self = shift;
358 0 my ($version) = $self->DatabaseVersion =~ /^(\d+\.\d+)/;
359  
360 0 ▶ if ( $self->_IsMariaDB ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->_IsMariaDB) { } 0 0 0
361 0 ▶ return (version->parse('v'.$version) >= version->parse('v10.2')) ? 1 : 0;
Branch: 1 branch
BranchTrueFalseTotal
'version'->parse('v' . $version) >= 'version'->parse('v10.2') ? : 0 0 0
362 }
363 else {
364 0 ▶ return (version->parse('v'.$version) >= version->parse('v8')) ? 1 : 0;
Branch: 1 branch
BranchTrueFalseTotal
'version'->parse('v' . $version) >= 'version'->parse('v8') ? : 0 0 0
365 }
366 }
367  
368 0 ▶sub CastAsDecimal {
Subroutine CastAsDecimal: not called CC=2 SCAR=17.9
Pod documented
369 0 my $self = shift;
370 0 ▶ my $field = shift or return;
Branch: 1 branch
BranchTrueFalseTotal
my $field = shift() or return 0 0 0
371  
372 # CAST($field AS DECIMAL) rounds values to integers by default. It supports
373 # specific precisions like CAST($field AS DECIMAL(5,2)), but we don't know
374 # the precisions in advance. +0 works like other dbs.
375 0 return "($field+0)";
376 }
377  
378 2 1;
« SearchBuilder/Handle/Pg.pm ↑ Summary SearchBuilder/Handle/MariaDB.pm »