SearchBuilder/Handle/Pg.pm

filter: Statementstmt 10.1% 13 / 128 Branchbran 0.0% 0 / 48 Conditioncond 0.0% 0 / 30 MC/DCmcdc 0.0% 0 / 17 Subroutinesub 22.2% 4 / 18 Podpod 100% 11 / 11 totaltotal 11.1% 28 / 252 CC 39 SCAR 36.3
CC
39
Cov
6%
_DistinctNeedsGroupBy
90.0
Insert
42.0
ApplyLimits
42.0
CRAP 37.7
↑ Summary SearchBuilder/Handle/mysql.pm »
1 package DBIx::SearchBuilder::Handle::Pg;
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  
6 1 ▶use base qw(DBIx::SearchBuilder::Handle);
Subroutine BEGIN: called CC=1 SCAR=0.0
7  
8 1 ▶use Want qw(howmany);
Subroutine BEGIN: called CC=1 SCAR=0.0
9  
10 =head1 NAME
11  
12 DBIx::SearchBuilder::Handle::Pg - A Postgres specific Handle object
13  
14 =head1 SYNOPSIS
15  
16  
17 =head1 DESCRIPTION
18  
19 This module provides a subclass of DBIx::SearchBuilder::Handle that
20 compensates for some of the idiosyncrasies of Postgres.
21  
22 =head1 METHODS
23  
24 =cut
25  
26  
27 =head2 Connect
28  
29 Connect takes a hashref and passes it off to SUPER::Connect;
30 Forces the timezone to GMT
31 it returns a database handle.
32  
33 =cut
34  
35 0 ▶sub Connect {
Subroutine Connect: not called CC=1 SCAR=6.9
Pod documented
36 0 my $self = shift;
37  
38 0 my $rv = $self->SUPER::Connect(@_);
39 0 $self->SimpleQuery("SET TIME ZONE 'GMT'");
40 0 $self->SimpleQuery("SET DATESTYLE TO 'ISO'");
41 0 $self->AutoCommit(1);
42 0 return ($rv);
43 }
44  
45 =head2 BuildDSN
46  
47 Extend L<DBIx::SearchBuilder::Handle/BuildDNS> to force
48 C<client_encoding> to be UTF-8, so that character strings can be
49 safely passed to, and retrieved from, the database. See
50 L<DBD::Pg/Encoding>.
51  
52 =cut
53  
54 0 ▶sub BuildDSN {
Subroutine BuildDSN: not called CC=1 SCAR=6.9
Pod documented
55 0 my $self = shift;
56 0 $self->SUPER::BuildDSN(@_);
57 0 $self->{'dsn'} .= ';client_encoding=UTF8';
58 0 return $self->{'dsn'};
59 }
60  
61 =head2 Insert
62  
63 Takes a table name as the first argument and assumes
64 that the rest of the arguments are an array of key-value
65 pairs to be inserted.
66  
67 In case of insert failure, returns a L<Class::ReturnValue>
68 object preloaded with error info.
69  
70 =cut
71  
72  
73 0 ▶sub Insert {
Subroutine Insert: not called CC=6 SCAR=37.4
Pod documented
74 0 my $self = shift;
75 0 my $table = shift;
76 0 my %args = (@_);
77  
78 0 my $sth = $self->SUPER::Insert( $table, %args );
79 0 ▶ return $sth unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
80  
81 0 ▶ if ( $args{'id'} || $args{'Id'} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($args{"id"} or $args{"Id"}) 0 0 0
Condition0%0/3
$args{"id"} or $args{"Id"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$args{"id"} or $args{"Id"}
$args{"id"} $args{"Id"}
Truth table0%0/3
A or B
A= $args{"id"}B= $args{"Id"}
ABresult
1X1
011
000
82 0 ▶ $self->{'id'} = $args{'id'} || $args{'Id'};
Condition0%0/3
$args{'id'} || $args{'Id'}
l!l&&r!l&&!r
000
MC/DC0%0/2
$args{'id'} || $args{'Id'}
$args{'id'} $args{'Id'}
Truth table0%0/3
A || B
A= $args{'id'}B= $args{'Id'}
ABresult
1X1
011
000
83 0 return ( $self->{'id'} );
84 }
85  
86 0 my $sequence_name = $self->IdSequenceName($table);
87 0 ▶ unless ($sequence_name) { return ($sequence_name) } # Class::ReturnValue
Branch: 1 branch
BranchTrueFalseTotal
unless ($sequence_name) 0 0 0
88 0 my $seqsth = $self->dbh->prepare(
89 qq{SELECT CURRVAL('} . $sequence_name . qq{')} );
90 0 $seqsth->execute;
91 0 $self->{'id'} = $seqsth->fetchrow_array();
92  
93 0 return ( $self->{'id'} );
94 }
95  
96 =head2 InsertQueryString
97  
98 Postgres sepcific overriding method for
99 L<DBIx::SearchBuilder::Handle/InsertQueryString>.
100  
101 =cut
102  
103 0 ▶sub InsertQueryString {
Subroutine InsertQueryString: not called CC=1 SCAR=6.9
Pod documented
104 0 my $self = shift;
105 0 my ($query_string, @bind) = $self->SUPER::InsertQueryString( @_ );
106 0 $query_string =~ s/\(\s*\)\s+VALUES\s+\(\s*\)\s*$/DEFAULT VALUES/;
107 0 return ($query_string, @bind);
108 }
109  
110 =head2 IdSequenceName TABLE
111  
112 Takes a TABLE name and returns the name of the sequence of the primary key for that table.
113  
114 =cut
115  
116 0 ▶sub IdSequenceName {
Subroutine IdSequenceName: not called CC=5 SCAR=34.0
Pod documented
117 0 my $self = shift;
118 0 my $table = shift;
119  
120 0 ▶ return $self->{'_sequences'}{$table} if (exists $self->{'_sequences'}{$table});
Branch: 1 branch
BranchTrueFalseTotal
if exists $self->{"_sequences"}{$table} 0 0 0
121 # Let's get the id of that row we just inserted
122 0 my $seq;
123 0 my $colinfosth = $self->dbh->column_info( undef, undef, lc($table), '%' );
124 0 ▶ while ( my $foo = $colinfosth->fetchrow_hashref ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $foo = $colinfosth->fetchrow_hashref) 0 0 0
125  
126 # Regexp from DBIx::Class's Pg handle. Thanks to Marcus Ramberg
127 0 ▶ if ( defined $foo->{'COLUMN_DEF'}
Branch: 1 branch
BranchTrueFalseTotal
if (defined $foo->{"COLUMN_DEF"} and $foo->{"COLUMN_DEF"} =~ /^nextval\(+'"?([^"']+)"?'(::(?:text|regclass)\))+/i) 0 0 0
Condition0%0/3
defined $foo->{"COLUMN_DEF"} and $foo->{"COLUMN_DEF"} =~ /^nextval\(+'"?([^"']+)"?'(::(?:text|regclass)\))+/i
!ll&&!rl&&r
000
MC/DC0%0/2
defined $foo->{"COLUMN_DEF"} and $foo->{"COLUMN_DEF"} =~ /^nextval\(+'"?([^"']+)"?'(::(?:text|regclass)\))+/i
defined $foo->{"COLUMN_DEF"} $foo->{"COLUMN_DEF"} =~ /^nextval\(+'"?([^"']+)"?'(::(?:text|regclass)\))+/i
Truth table0%0/3
A and B
A= defined $foo->{"COLUMN_DEF"}B= $foo->{"COLUMN_DEF"} =~ /^nextval\(+'"?([^"']+)"?'(::(?:text|regclass)\))+/i
ABresult
0X0
100
111
128 && $foo->{'COLUMN_DEF'}
129 =~ m!^nextval\(+'"?([^"']+)"?'(::(?:text|regclass)\))+!i )
130  
131 {
132 0 return $self->{'_sequences'}{$table} = $1;
133 }
134  
135 }
136  
137 0 my $ret = Class::ReturnValue->new();
138 0 $ret->as_error(
139 errno => '-1',
140 message => "Found no sequence for $table",
141 do_backtrace => undef
142 );
143 0 return ( $ret->return_value );
144 }
145  
146  
147  
148 =head2 BinarySafeBLOBs
149  
150 Return undef, as no current version of postgres supports binary-safe blobs
151  
152 =cut
153  
154 0 ▶sub BinarySafeBLOBs {
Subroutine BinarySafeBLOBs: not called CC=1 SCAR=6.9
Pod documented
155 0 my $self = shift;
156 0 return(undef);
157 }
158  
159  
160 =head2 ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW
161  
162 takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE starting with FIRST_ROW;
163  
164  
165 =cut
166  
167 0 ▶sub ApplyLimits {
Subroutine ApplyLimits: not called CC=6 SCAR=37.4
Pod documented
168 0 my $self = shift;
169 0 my $statementref = shift;
170 0 my $per_page = shift;
171 0 my $first = shift;
172 0 my $sb = shift;
173  
174 0 my $limit_clause = '';
175  
176 0 ▶ if ( $per_page) {
Branch: 1 branch
BranchTrueFalseTotal
if ($per_page) 0 0 0
177 0 $limit_clause = " LIMIT ";
178  
179 0 ▶ if ( $sb->{_bind_values} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($sb->{"_bind_values"}) 0 0 0
180 0 ▶ push @{ $sb->{_bind_values} }, $per_page, $first || ();
Condition0%0/3
$first || ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$first || ()
$first ()
Truth table0%0/3
A || B
A= $firstB= ()
ABresult
1X1
011
000
181 0 ▶ $first = '?' if $first;
Branch: 1 branch
BranchTrueFalseTotal
if $first 0 0 0
182 0 $per_page = '?';
183 }
184  
185 0 $limit_clause .= $per_page;
186 0 ▶ if ( $first ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($first) 0 0 0
187 0 $limit_clause .= " OFFSET $first";
188 }
189 }
190  
191 0 $$statementref .= $limit_clause;
192 }
193  
194  
195 =head2 _MakeClauseCaseInsensitive FIELD OPERATOR VALUE
196  
197 Takes a field, operator and value. performs the magic necessary to make
198 your database treat this clause as case insensitive.
199  
200 Returns a FIELD OPERATOR VALUE triple.
201  
202 =cut
203  
204 0 ▶sub _MakeClauseCaseInsensitive {
Subroutine _MakeClauseCaseInsensitive: not called CC=5 SCAR=34.0
205 0 my $self = shift;
206 0 my $field = shift;
207 0 my $operator = shift;
208 0 my $value = shift;
209  
210 # we don't need to downcase numeric values and dates
211 0 ▶ if ($value =~ /^$DBIx::SearchBuilder::Handle::RE_CASE_INSENSITIVE_CHARS+$/o) {
Branch: 1 branch
BranchTrueFalseTotal
if ($value =~ /^$DBIx::SearchBuilder::Handle::RE_CASE_INSENSITIVE_CHARS+$/o) 0 0 0
212 0 return ( $field, $operator, $value);
213 }
214  
215 0 ▶ if ( $operator =~ /LIKE/i ) {
Branch: 2 branches
BranchTrueFalseTotal
if ($operator =~ /LIKE/i) { } 0 0 0
elsif ($operator =~ /=/) { } 0 0 0
216 0 $operator =~ s/LIKE/ILIKE/ig;
217 0 return ( $field, $operator, $value );
218 }
219 elsif ( $operator =~ /=/ ) {
220 0 ▶ if (howmany() >= 4) {
Branch: 1 branch
BranchTrueFalseTotal
if (&howmany() >= 4) { } 0 0 0
221 0 return ( "LOWER($field)", $operator, $value, "LOWER(?)");
222 }
223 # RT 3.0.x and earlier don't know how to cope with a "LOWER" function
224 # on the value. they only expect field, operator, value.
225 #
226 else {
227 0 return ( "LOWER($field)", $operator, lc($value));
228 }
229 }
230 else {
231 0 $self->SUPER::_MakeClauseCaseInsensitive( $field, $operator, $value );
232 }
233 }
234  
235  
236 # Pg also needs GROUP BY for ORDER BY expressions like LOWER(main.Name),
237 # even with "SELECT DISTINCT main.*".
238 0 ▶sub _DistinctNeedsGroupBy {
Subroutine _DistinctNeedsGroupBy: not called CC=9 SCAR=45.0
239 0 my $self = shift;
240 0 my $sb = shift;
241  
242 # GROUP BY main.id doesn't work with columns of other aliases
243 0 ▶ return $self->SUPER::_DistinctNeedsGroupBy($sb) if $sb->_ColumnsForQuery =~ /(?<!main)\./;
Branch: 1 branch
BranchTrueFalseTotal
if $sb->_ColumnsForQuery =~ /(?<!main)\./ 0 0 0
244  
245 0 ▶ for my $row ( @{ $sb->{'order_by'} || [] } ) {
Condition0%0/2
$sb->{'order_by'} || []
l!l
00
MC/DC0%0/1
$sb->{'order_by'} || []
$sb->{'order_by'}
Truth table0%0/2
A
A= $sb->{'order_by'}
Aresult
11
00
246 0 ▶ next unless $row->{'FIELD'};
Branch: 1 branch
BranchTrueFalseTotal
unless $row->{"FIELD"} 0 0 0
247 0 ▶ next if $self->_DistinctOrderIsBareName($row);
Branch: 1 branch
BranchTrueFalseTotal
if $self->_DistinctOrderIsBareName($row) 0 0 0
248 0 ▶ return 1 if ( $row->{'ALIAS'} // 'main' ) ne 'main';
Branch: 1 branch
BranchTrueFalseTotal
if ($row->{"ALIAS"} // "main") ne "main" 0 0 0
Condition0%0/2
$row->{"ALIAS"} // "main"
l!l
00
MC/DC0%0/1
$row->{"ALIAS"} // "main"
$row->{"ALIAS"}
Truth table0%0/2
A
A= $row->{"ALIAS"}
Aresult
11
00
249 0 ▶ return 1 if $row->{'FIELD'} !~ /^\w+$/;
Branch: 1 branch
BranchTrueFalseTotal
unless $row->{"FIELD"} =~ /^\w+$/ 0 0 0
250 }
251 0 return $self->SUPER::_DistinctNeedsGroupBy($sb);
252 }
253  
254 =head2 DistinctQuery STATEMENTREF
255  
256 takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set.
257  
258 =cut
259  
260 0 ▶sub DistinctQuery {
Subroutine DistinctQuery: not called CC=6 SCAR=37.4
Pod documented
261 0 my $self = shift;
262 0 my $statementref = shift;
263 0 my $sb = shift;
264 0 my $table = $sb->Table;
265  
266 0 ▶ return $self->SUPER::DistinctQuery( $statementref, $sb, @_ )
Branch: 1 branch
BranchTrueFalseTotal
unless $self->_DistinctNeedsGroupBy($sb) 0 0 0
267 unless $self->_DistinctNeedsGroupBy($sb);
268  
269 # "SELECT main.* FROM ( SELECT id FROM ... ORDER BY ... ) as dist,
270 # X main WHERE (main.id = dist.id);" doesn't work in some cases.
271 # It's hard to show with tests. Pg's optimizer can choose execution
272 # plan not guaranting order
273  
274 0 my $groups;
275 0 ▶ if ($self->DatabaseVersion =~ /^(\d+)\.(\d+)/ and ($1 > 9 or ($1 == 9 and $2 >= 1))) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->DatabaseVersion =~ /^(\d+)\.(\d+)/ and $1 > 9 || $1 == 9 && $2 >= 1) { } 0 0 0
Condition0%0/9
$self->DatabaseVersion =~ /^(\d+)\.(\d+)/ and $1 > 9 || $1 == 9 && $2 >= 1
!ll&&!rl&&r
000
$1 > 9 or $1 == 9 and $2 >= 1
l!l&&r!l&&!r
000
$1 == 9 and $2 >= 1
!ll&&!rl&&r
000
MC/DC0%0/4
$self->DatabaseVersion =~ /^(\d+)\.(\d+)/ and $1 > 9 || $1 == 9 && $2 >= 1
$self->DatabaseVersion =~ /^(\d+)\.(\d+)/ $1 > 9 $1 == 9 $2 >= 1
Truth table0%0/5
A and B or C and D
A= $self->DatabaseVersion =~ /^(\d+)\.(\d+)/B= $1 > 9C= $1 == 9D= $2 >= 1
ABCDresult
0XXX0
100X0
10100
11XX1
10111
276 # Pg 9.1 supports "SELECT main.foo ... GROUP BY main.id" if id is the primary key
277 0 $groups = [ {FIELD => "id"} ];
278 } else {
279 # For earlier versions, we have to list out all of the columns
280 0 $groups = [ map {+{FIELD => $_}} $self->Fields($table) ];
281 }
282 0 local $sb->{group_by} = $groups;
283 0 local $sb->{'order_by'} = $self->_DistinctGroupByOrder($sb);
284 0 my $group = $sb->_GroupClause;
285 0 my $order = $sb->_OrderClause;
286 0 $$statementref = "SELECT main.* FROM $$statementref $group $order";
287 }
288  
289 =head2 SimpleDateTimeFunctions
290  
291 Returns hash reference with specific date time functions of this
292 database for L<DBIx::SearchBuilder::Handle/DateTimeFunction>.
293  
294 =cut
295  
296 0 ▶sub SimpleDateTimeFunctions {
Subroutine SimpleDateTimeFunctions: not called CC=4 SCAR=30.0
Pod documented
297 0 my $self = shift;
298 return $self->{'_simple_date_time_functions'}
299 0 ▶ if $self->{'_simple_date_time_functions'};
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"_simple_date_time_functions"} 0 0 0
300  
301 0 my %res = %{ $self->SUPER::SimpleDateTimeFunctions(@_) };
302 0 s/SUBSTR\s*\(\s*\?/SUBSTR( CAST(? AS text)/ig for values %res;
303  
304 # everything else we should implement through date_trunc that
305 # does SUBSTR(?, 1, X) on a date, but leaves trailing values
306 # when we don't need them
307  
308 0 ▶ return $self->{'_simple_date_time_functions'} ||= {
Condition0%0/2
$self->{"_simple_date_time_functions"} ||= {%res, "datetime", "?", "time", "CAST(? AS time)", "hour", "EXTRACT(HOUR FROM ?)", "date", "CAST(? AS date)", "daily", "CAST(? AS date)", "day", "EXTRACT(DAY FROM ?)", "month", "EXTRACT(MONTH FROM ?)", "annually", "EXTRACT(YEAR FROM ?)", "year", "EXTRACT(YEAR FROM ?)", "dayofweek", "EXTRACT(DOW FROM ?)", "dayofyear", "EXTRACT(DOY FROM ?)", "weekofyear", "EXTRACT(WEEK FROM ?)"}
l!l
00
MC/DC0%0/1
$self->{"_simple_date_time_functions"} ||= {%res, "datetime", "?", "time", "CAST(? AS time)", "hour", "EXTRACT(HOUR FROM ?)", "date", "CAST(? AS date)", "daily", "CAST(? AS date)", "day", "EXTRACT(DAY FROM ?)", "month", "EXTRACT(MONTH FROM ?)", "annually", "EXTRACT(YEAR FROM ?)", "year", "EXTRACT(YEAR FROM ?)", "dayofweek", "EXTRACT(DOW FROM ?)", "dayofyear", "EXTRACT(DOY FROM ?)", "weekofyear", "EXTRACT(WEEK FROM ?)"}
$self->{"_simple_date_time_functions"}
Truth table0%0/2
A
A= $self->{"_simple_date_time_functions"}
Aresult
11
00
309 %res,
310 datetime => '?',
311 time => 'CAST(? AS time)',
312  
313 hour => 'EXTRACT(HOUR FROM ?)',
314  
315 date => 'CAST(? AS date)',
316 daily => 'CAST(? AS date)',
317  
318 day => 'EXTRACT(DAY FROM ?)',
319  
320 month => 'EXTRACT(MONTH FROM ?)',
321  
322 annually => 'EXTRACT(YEAR FROM ?)',
323 year => 'EXTRACT(YEAR FROM ?)',
324  
325 dayofweek => "EXTRACT(DOW FROM ?)", # 0-6, 0 - Sunday
326 dayofyear => "EXTRACT(DOY FROM ?)", # 1-366
327 # 1-53, 1st week January 4, week starts on Monay
328 weekofyear => "EXTRACT(WEEK FROM ?)",
329 };
330 }
331  
332 =head2 ConvertTimezoneFunction
333  
334 Custom implementation of L<DBIx::SearchBuilder::Handle/ConvertTimezoneFunction>.
335  
336 In Pg time and timestamp data types may be "with time zone" or "without time zone".
337 So if Field argument is timestamp "with time zone" then From argument is not
338 required and is useless. Otherwise From argument identifies time zone of the Field
339 argument that is "without time zone".
340  
341 For consistency with other DBs use timestamp columns without time zones and provide
342 From argument.
343  
344 =cut
345  
346 0 ▶sub ConvertTimezoneFunction {
Subroutine ConvertTimezoneFunction: not called CC=5 SCAR=34.0
Pod documented
347 0 my $self = shift;
348 0 my %args = (
349 From => 'UTC',
350 To => undef,
351 Field => '',
352 @_
353 );
354 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
355 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
356  
357 0 my $dbh = $self->dbh;
358 0 my $res = $args{'Field'};
359 0 $res = "TIMEZONE($_, $res)" foreach map $dbh->quote( $_ ), grep $_, @args{'From', 'To'};
360 0 return $res;
361 }
362  
363 0 ▶sub _DateTimeIntervalFunction {
Subroutine _DateTimeIntervalFunction: not called CC=1 SCAR=6.9
364 0 my $self = shift;
365 0 my %args = ( From => undef, To => undef, @_ );
366  
367 0 return "(EXTRACT(EPOCH FROM $args{'To'}) - EXTRACT(EPOCH FROM $args{'From'}))";
368 }
369  
370 0 ▶sub HasSupportForNullsOrder {
Subroutine HasSupportForNullsOrder: not called CC=1 SCAR=6.9
Pod documented
371 0 return 1;
372 }
373  
374 1 1;
↑ Summary SearchBuilder/Handle/mysql.pm »