SearchBuilder/Handle/MariaDB.pm

filter: Statementstmt 9.1% 13 / 142 Branchbran 0.0% 0 / 58 Conditioncond 0.0% 0 / 16 MC/DCmcdc 0.0% 0 / 8 Subroutinesub 16.6% 4 / 24 Podpod 81.2% 13 / 16 totaltotal 11.3% 30 / 264 CC 41 SCAR 35.2
CC
41
Cov
6%
DistinctQuery
90.0
SimpleMassChangeFromSelect
72.0
Fields
42.0
CRAP 33.9
« SearchBuilder/Handle/mysql.pm ↑ Summary SearchBuilder/Handle.pm »
1 package DBIx::SearchBuilder::Handle::MariaDB;
2  
3 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 1 ▶use version;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 1 ▶use base qw(DBIx::SearchBuilder::Handle);
Subroutine BEGIN: called CC=1 SCAR=0.0
8  
9 =head1 NAME
10  
11 DBIx::SearchBuilder::Handle::MariaDB - A MariaDB 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 # Follow the advice in the docs and use last_insert_id rather than
41 # {'mariadb_insertid'}.
42 #
43 # https://metacpan.org/dist/DBD-MariaDB/view/lib/DBD/MariaDB.pod#mariadb_insertid
44  
45 0 $self->{'id'} = $self->dbh->last_insert_id();
46  
47 # Docs say last_insert_id could still return undef, so keeping this code
48 0 ▶ unless ( $self->{'id'} ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($self->{"id"}) 0 0 0
49 0 $self->{'id'} = $self->FetchResult('SELECT LAST_INSERT_ID()');
50 }
51 0 ▶ warn "$self no row id returned on row creation" unless ($self->{'id'});
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"id"} 0 0 0
52  
53 0 return( $self->{'id'}); #Add Succeded. return the id
54 }
55  
56 =head2 KnowsBLOBs
57  
58 Returns 1 if the current database supports inserts of BLOBs automatically.
59 Returns undef if the current database must be informed of BLOBs for inserts.
60  
61 =cut
62  
63 0 ▶sub KnowsBLOBs {
Subroutine KnowsBLOBs: not called CC=1 SCAR=6.9
Pod documented
64 0 my $self = shift;
65 0 return(undef);
66 }
67  
68 =head2 BLOBParams FIELD_NAME FIELD_TYPE
69  
70 Returns a hash ref for the bind_param call to identify BLOB types used by
71 the current database for a particular column type.
72  
73 =cut
74  
75 0 ▶sub BLOBParams {
Subroutine BLOBParams: not called CC=2 SCAR=17.9
Pod documented
76 0 my $self = shift;
77 0 my $field = shift;
78 0 my $type = shift;
79  
80 0 ▶ if ( $type =~ /^(blob|longblob)$/i ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($type =~ /^(blob|longblob)$/i) { } 0 0 0
81 # Don't assign to key 'value' as it is defined later.
82 0 return ( { TYPE => 'SQL_BLOB', } );
83 }
84 else {
85 # Normal handling for these, so no hashref
86 0 return;
87 }
88 }
89  
90  
91 =head2 SimpleUpdateFromSelect
92  
93 Customization of L<DBIx::SearchBuilder::Handle/SimpleUpdateFromSelect>.
94 Mysql doesn't support update with subqueries when those fetch data from
95 the table that is updated.
96  
97 =cut
98  
99 0 ▶sub SimpleUpdateFromSelect {
Subroutine SimpleUpdateFromSelect: not called CC=5 SCAR=34.0
Pod documented
100 0 my ($self, $table, $values, $query, @query_binds) = @_;
101  
102 0 ▶ return $self->SUPER::SimpleUpdateFromSelect(
Branch: 1 branch
BranchTrueFalseTotal
unless $query =~ /\b\Q$table\E\b/i 0 0 0
103 $table, $values, $query, @query_binds
104 ) unless $query =~ /\b\Q$table\E\b/i;
105  
106 0 my $sth = $self->SimpleQuery( $query, @query_binds );
107 0 ▶ return $sth unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
108  
109 0 my (@binds, @columns);
110 0 for my $k (sort keys %$values) {
111 0 push @columns, $k;
112 0 push @binds, $values->{$k};
113 }
114  
115 0 ▶ $table = $self->QuoteName($table) if $self->{'QuoteTableNames'};
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"QuoteTableNames"} 0 0 0
116 0 my $update_query = "UPDATE $table SET "
117 . join( ', ', map "$_ = ?", @columns )
118 .' WHERE ID IN ';
119  
120 0 return $self->SimpleMassChangeFromSelect(
121 $update_query, \@binds,
122 $query, @query_binds
123 );
124 }
125  
126  
127 0 ▶sub DeleteFromSelect {
Subroutine DeleteFromSelect: not called CC=3 SCAR=24.8
Pod undocumented
128 0 my ($self, $table, $query, @query_binds) = @_;
129  
130 0 ▶ return $self->SUPER::DeleteFromSelect(
Branch: 1 branch
BranchTrueFalseTotal
unless $query =~ /\b\Q$table\E\b/i 0 0 0
131 $table, $query, @query_binds
132 ) unless $query =~ /\b\Q$table\E\b/i;
133  
134 0 ▶ $table = $self->QuoteName($table) if $self->{'QuoteTableNames'};
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"QuoteTableNames"} 0 0 0
135 0 return $self->SimpleMassChangeFromSelect(
136 "DELETE FROM $table WHERE id IN ", [],
137 $query, @query_binds
138 );
139 }
140  
141 0 ▶sub SimpleMassChangeFromSelect {
Subroutine SimpleMassChangeFromSelect: not called CC=8 SCAR=42.8
Pod undocumented
142 0 my ($self, $update_query, $update_binds, $search, @search_binds) = @_;
143  
144 0 my $sth = $self->SimpleQuery( $search, @search_binds );
145 0 ▶ return $sth unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
146  
147  
148 # tried TEMPORARY tables, much slower than fetching and delete
149 # also size of ENGINE=MEMORY is limitted by option, on disk
150 # tables more slower than in memory
151 0 my $res = 0;
152  
153 0 my @ids;
154 0 ▶ while ( my $id = ($sth->fetchrow_array)[0] ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $id = ($sth->fetchrow_array)[0]) 0 0 0
155 0 push @ids, $id;
156 0 ▶ next if @ids < 1000;
Branch: 1 branch
BranchTrueFalseTotal
if @ids < 1000 0 0 0
157  
158 0 my $q = $update_query .'('. join( ',', ('?')x@ids ) .')';
159 0 my $sth = $self->SimpleQuery( $q, @$update_binds, splice @ids );
160 0 ▶ return $sth unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
161  
162 0 $res += $sth->rows;
163 }
164 0 ▶ if ( @ids ) {
Branch: 1 branch
BranchTrueFalseTotal
if (@ids) 0 0 0
165 0 my $q = $update_query .'('. join( ',', ('?')x@ids ) .')';
166 0 my $sth = $self->SimpleQuery( $q, @$update_binds, splice @ids );
167 0 ▶ return $sth unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
168  
169 0 $res += $sth->rows;
170 }
171 0 ▶ return $res == 0? '0E0': $res;
Branch: 1 branch
BranchTrueFalseTotal
$res == 0 ? : 0 0 0
172 }
173  
174 =head2 DatabaseVersion
175  
176 Returns the MariaDB version, trimming off any -foo identifier
177  
178 =cut
179  
180 0 ▶sub DatabaseVersion {
Subroutine DatabaseVersion: not called CC=1 SCAR=6.9
Pod documented
181 0 my $self = shift;
182 0 my $v = $self->SUPER::DatabaseVersion();
183  
184 0 $v =~ s/\-.*$//;
185 0 return ($v);
186 }
187  
188 =head2 CaseSensitive
189  
190 Returns undef, since MariaDB's searches are not case sensitive by default
191  
192 =cut
193  
194 0 ▶sub CaseSensitive {
Subroutine CaseSensitive: not called CC=1 SCAR=6.9
Pod documented
195 0 my $self = shift;
196 0 return(undef);
197 }
198  
199 0 ▶sub DistinctQuery {
Subroutine DistinctQuery: not called CC=9 SCAR=45.0
Pod documented
200 0 my $self = shift;
201 0 my $statementref = shift;
202 0 my $sb = shift;
203  
204 0 ▶ return $self->SUPER::DistinctQuery( $statementref, $sb, @_ )
Branch: 1 branch
BranchTrueFalseTotal
unless $self->_DistinctNeedsGroupBy($sb) 0 0 0
205 unless $self->_DistinctNeedsGroupBy($sb);
206  
207 0 ▶ if ( substr($self->DatabaseVersion, 0, 1) == 4 ) {
Branch: 1 branch
BranchTrueFalseTotal
if (substr($self->DatabaseVersion, 0, 1) == 4) { } 0 0 0
208 0 local $sb->{'group_by'} = [{FIELD => 'id'}];
209  
210 0 my ($idx, @tmp, @specials) = (0, ());
211 0 foreach ( @{$sb->{'order_by'}} ) {
212 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
213 0 push @tmp, $_; next;
214 }
215  
216 push @specials,
217 ((($_->{'ORDER'}||'') =~ /^des/i)?'MAX':'MIN')
218 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
219 ." __special_sort_$idx";
220 0 push @tmp, { ALIAS => '', FIELD => "__special_sort_$idx", ORDER => $_->{'ORDER'} };
221 0 $idx++;
222 }
223  
224 0 local $sb->{'order_by'} = \@tmp;
225 0 $$statementref = "SELECT ". join( ", ", 'main.*', @specials ) ." FROM $$statementref";
226 0 $$statementref .= $sb->_GroupClause;
227 0 $$statementref .= $sb->_OrderClause;
228 } else {
229 0 local $sb->{'group_by'} = [{FIELD => 'id'}];
230 0 local $sb->{'order_by'} = $self->_DistinctGroupByOrder($sb);
231 0 $$statementref = "SELECT main.* FROM $$statementref";
232 0 $$statementref .= $sb->_GroupClause;
233 0 $$statementref .= $sb->_OrderClause;
234 }
235 }
236  
237 0 ▶sub Fields {
Subroutine Fields: not called CC=6 SCAR=37.4
Pod undocumented
238 0 my $self = shift;
239 0 my $table = shift;
240  
241 0 my $cache = \%DBIx::SearchBuilder::Handle::FIELDS_IN_TABLE;
242 0 ▶ unless ( $cache->{ lc $table } ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($$cache{lc $table}) 0 0 0
243 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
244 or return ();
245 0 my $info = $sth->fetchall_arrayref({});
246 0 foreach my $e ( sort {$a->{'ORDINAL_POSITION'} <=> $b->{'ORDINAL_POSITION'}} @$info ) {
247 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
248 }
249 }
250 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
251 }
252  
253 =head2 SimpleDateTimeFunctions
254  
255 Returns hash reference with specific date time functions of this
256 database for L<DBIx::SearchBuilder::Handle/DateTimeFunction>.
257  
258 =cut
259  
260 0 ▶sub SimpleDateTimeFunctions {
Subroutine SimpleDateTimeFunctions: not called CC=2 SCAR=17.9
Pod documented
261 0 my $self = shift;
262 return $self->{'_simple_date_time_functions'} ||= {
263 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
264 datetime => '?',
265 time => 'TIME(?)',
266  
267 hourly => "DATE_FORMAT(?, '%Y-%m-%d %H')",
268 hour => 'HOUR(?)',
269  
270 date => 'DATE(?)',
271 daily => 'DATE(?)',
272  
273 day => 'DAYOFMONTH(?)',
274 dayofmonth => 'DAYOFMONTH(?)',
275  
276 monthly => "DATE_FORMAT(?, '%Y-%m')",
277 month => 'MONTH(?)',
278  
279 annually => 'YEAR(?)',
280 year => 'YEAR(?)',
281  
282 dayofweek => "DAYOFWEEK(?) - 1", # 1-7, 1 - Sunday
283 dayofyear => "DAYOFYEAR(?)", # 1-366
284 weekofyear => "WEEK(?)", # skip mode argument, so it can be controlled in MariaDB config
285 };
286 }
287  
288  
289 =head2 ConvertTimezoneFunction
290  
291 Custom implementation of L<DBIx::SearchBuilder::Handle/ConvertTimezoneFunction>.
292  
293 Use the following query to get list of timezones:
294  
295 SELECT Name FROM mysql.time_zone_name;
296  
297 See also details on how MariaDB works with mysql timezone tables
298  
299 https://mariadb.com/kb/en/time-zones/
300  
301 =cut
302  
303 0 ▶sub ConvertTimezoneFunction {
Subroutine ConvertTimezoneFunction: not called CC=5 SCAR=34.0
Pod documented
304 0 my $self = shift;
305 0 my %args = (
306 From => 'UTC',
307 To => undef,
308 Field => '',
309 @_
310 );
311 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
312 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
313 0 my $dbh = $self->dbh;
314 0 $_ = $dbh->quote( $_ ) foreach @args{'From', 'To'};
315 0 return "CONVERT_TZ( $args{'Field'}, $args{'From'}, $args{'To'} )";
316 }
317  
318 0 ▶sub _DateTimeIntervalFunction {
Subroutine _DateTimeIntervalFunction: not called CC=1 SCAR=6.9
319 0 my $self = shift;
320 0 my %args = ( From => undef, To => undef, @_ );
321  
322 0 return "TIMESTAMPDIFF(SECOND, $args{'From'}, $args{'To'})";
323 }
324  
325  
326 =head2 QuoteName
327  
328 Quote table or column name to avoid reserved word errors.
329  
330 =cut
331  
332 # over-rides inherited method
333 0 ▶sub QuoteName {
Subroutine QuoteName: not called CC=2 SCAR=17.9
Pod documented
334 0 my ($self, $name) = @_;
335 # use dbi built in quoting if we have a connection,
336 0 ▶ if ($self->dbh) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->dbh) 0 0 0
337 0 return $self->SUPER::QuoteName($name);
338 }
339  
340 0 return sprintf('`%s`', $name);
341 }
342  
343 0 ▶sub DequoteName {
Subroutine DequoteName: not called CC=3 SCAR=24.8
Pod documented
344 0 my ($self, $name) = @_;
345  
346 # If we have a handle, the base class can do it for us
347 0 ▶ if ($self->dbh) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->dbh) 0 0 0
348 0 return $self->SUPER::DequoteName($name);
349 }
350  
351 0 ▶ if ($name =~ /^`(.*)`$/) {
Branch: 1 branch
BranchTrueFalseTotal
if ($name =~ /^`(.*)`$/) 0 0 0
352 0 return $1;
353 }
354 0 return $name;
355 }
356  
357 0 ▶sub _ExtractBindValues {
Subroutine _ExtractBindValues: not called CC=1 SCAR=6.9
358 0 my $self = shift;
359 0 my $value = shift;
360 0 return $self->SUPER::_ExtractBindValues( $value, '\\' );
361 }
362  
363 0 ▶sub _IsMariaDB {
Subroutine _IsMariaDB: not called CC=1 SCAR=6.9
364 0 my $self = shift;
365  
366 # We override DatabaseVersion to chop off "-MariaDB-whatever", so
367 # call super here to get the original version
368 0 my $v = $self->SUPER::DatabaseVersion();
369  
370 0 return ($v =~ /mariadb/i);
371 }
372  
373 0 ▶sub _RequireQuotedTables {
Subroutine _RequireQuotedTables: not called CC=1 SCAR=6.9
374 0 my $self = shift;
375  
376 # MariaDB version does not match mysql, and hasn't added new reserved words
377 # like "groups".
378 0 return 0;
379 }
380  
381 =head2 HasSupportForCombineSearchAndCount
382  
383 MariaDB 10.2+ supports this.
384  
385 =cut
386  
387 0 ▶sub HasSupportForCombineSearchAndCount {
Subroutine HasSupportForCombineSearchAndCount: not called CC=2 SCAR=17.9
Pod documented
388 0 my $self = shift;
389 0 my ($version) = $self->DatabaseVersion =~ /^(\d+\.\d+)/;
390  
391 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
392 }
393  
394 0 ▶sub CastAsDecimal {
Subroutine CastAsDecimal: not called CC=2 SCAR=17.9
Pod documented
395 0 my $self = shift;
396 0 ▶ my $field = shift or return;
Branch: 1 branch
BranchTrueFalseTotal
my $field = shift() or return 0 0 0
397  
398 # CAST($field AS DECIMAL) rounds values to integers by default. It supports
399 # specific precisions like CAST($field AS DECIMAL(5,2)), but we don't know
400 # the precisions in advance. +0 works like other dbs.
401 0 return "($field+0)";
402 }
403  
404 1 1;
« SearchBuilder/Handle/mysql.pm ↑ Summary SearchBuilder/Handle.pm »