SearchBuilder/Handle/Oracle.pm untested

filter: Statementstmt 0.0% 0 / 141 Branchbran 0.0% 0 / 44 Conditioncond 0.0% 0 / 30 MC/DCmcdc 0.0% 0 / 17 Subroutinesub 0.0% 0 / 21 Podpod 0.0% 0 / 17 totaltotal 0.0% 0 / 270 CC 23 SCAR 30.7
CC
23
Cov
0%
Insert
56.0
BuildDSN
42.0
DistinctQuery
30.0
CRAP 21.6
1 package DBIx::SearchBuilder::Handle::Oracle;
2  
3 use strict;
4 use warnings;
5  
6 use base qw/DBIx::SearchBuilder::Handle/;
7  
8 use DBD::Oracle qw(:ora_types ORA_OCI);
9  
10 =head1 NAME
11  
12 DBIx::SearchBuilder::Handle::Oracle - An oracle 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 Oracle.
21  
22 =head1 METHODS
23  
24 =cut
25  
26  
27 =head2 Connect PARAMHASH: Driver, Database, Host, User, Password
28  
29 Takes a paramhash and connects to your DBI datasource.
30  
31 =cut
32  
33 sub Connect {
34 my $self = shift;
35  
36 my %args = (
37 Driver => undef,
38 Database => undef,
39 User => undef,
40 Password => undef,
41 SID => undef,
42 Host => undef,
43 @_
44 );
45  
46 my $rv = $self->SUPER::Connect(%args);
47
48 $self->dbh->{LongTruncOk}=1;
49 $self->dbh->{LongReadLen}=8000;
50  
51 foreach my $setting (qw(DATE TIMESTAMP TIMESTAMP_TZ)) {
52 $self->SimpleQuery(
53 "ALTER SESSION set NLS_${setting}_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"
54 );
55 }
56  
57 return ($rv);
58 }
59  
60 =head2 BuildDSN
61  
62 Customized version of L<DBIx::SearchBuilder::Handle/BuildDSN> method.
63  
64 Takes additional argument SID. Database argument used unless SID provided.
65 Two forms of DSN are generated depending on whether Host defined or not:
66  
67 dbi:Oracle:sid=<SID>;host=...[;port=...]
68 dbi:Oracle:<SID>
69  
70 Read details in documentation for L<DBD::Oracle> module.
71  
72 =cut
73  
74 sub BuildDSN {
75 my $self = shift;
76 my %args = (
77 Driver => undef,
78 Database => undef,
79 Host => undef,
80 Port => undef,
81 SID => undef,
82 @_
83 );
84 $args{'Driver'} ||= 'Oracle';
85  
86 # read DBD::Oracle for details, but basicly it supports
87 # either 'dbi:Oracle:SID' or 'dbi:Oracle:sid=SID;host=...;[port=...;]'
88 # and tests shows that 'dbi:Oracle:SID' != 'dbi:Oracle:sid=SID'
89  
90 $args{'SID'} ||= $args{'Database'};
91 my $dsn = "dbi:$args{'Driver'}:";
92 if ( $args{'Host'} ) {
93 $dsn .= "sid=$args{'SID'}" if $args{'SID'};
94 $dsn .= ";host=$args{'Host'}";
95 $dsn .= ";port=$args{'Port'}" if $args{'Port'};
96 }
97 else {
98 $dsn .= $args{'SID'} if $args{'SID'};
99 $dsn .= ";port=$args{'Port'}" if $args{'Port'};
100 }
101  
102 return $self->{'dsn'} = $dsn;
103 }
104  
105  
106 =head2 Insert
107  
108 Takes a table name as the first argument and assumes that the rest of the arguments
109 are an array of key-value pairs to be inserted.
110  
111 =cut
112  
113 sub Insert {
114 my $self = shift;
115 my $table = shift;
116 my ($sth);
117  
118 # Oracle Hack to replace non-supported mysql_rowid call
119  
120 my %attribs = @_;
121 my ($unique_id, $QueryString);
122  
123 if ($attribs{'Id'} || $attribs{'id'}) {
124 $unique_id = ($attribs{'Id'} ? $attribs{'Id'} : $attribs{'id'} );
125 } else {
126 $QueryString = "SELECT ".$table."_seq.nextval FROM DUAL";
127 $sth = $self->SimpleQuery($QueryString);
128 if (!$sth) {
129 if ($main::debug) {
130 die "Error with $QueryString";
131 } else {
132 return (undef);
133 }
134 }
135  
136 #needs error checking
137 my @row = $sth->fetchrow_array;
138  
139 $unique_id = $row[0];
140 }
141  
142 #TODO: don't hardcode this to id pull it from somewhere else
143 #call super::Insert with the new column id.
144  
145 $attribs{'id'} = $unique_id;
146 delete $attribs{'Id'};
147 $sth = $self->SUPER::Insert( $table, %attribs);
148  
149 unless ($sth) {
150 if ($main::debug) {
151 die "Error with $QueryString: ". $self->dbh->errstr;
152 } else {
153 return (undef);
154 }
155 }
156  
157 $self->{'id'} = $unique_id;
158 return( $self->{'id'}); #Add Succeded. return the id
159 }
160  
161 =head2 InsertFromSelect
162  
163 Customization of L<DBIx::SearchBuilder::Handle/InsertFromSelect>.
164  
165 Unlike other DBs Oracle needs:
166  
167 =over 4
168  
169 =item * id generated from sequences for every new record.
170  
171 =item * query wrapping in parens.
172  
173 =back
174  
175 B<NOTE> that on Oracle there is a limitation on the query. Every
176 column in the result should have unique name or alias, for example the
177 following query would generate "ORA-00918: column ambiguously defined"
178 error:
179  
180 SELECT g.id, u.id FROM ...
181  
182 Solve with aliases:
183  
184 SELECT g.id AS group_id, u.id AS user_id FROM ...
185  
186 =cut
187  
188 sub InsertFromSelect {
189 my ($self, $table, $columns, $query, @binds) = @_;
190 if ( $columns && !grep lc($_) eq 'id', @$columns ) {
191 unshift @$columns, 'id';
192 $query = "SELECT ${table}_seq.nextval, insert_from.* FROM ($query) insert_from";
193 }
194 return $self->SUPER::InsertFromSelect( $table, $columns, "($query)", @binds);
195 }
196  
197 =head2 KnowsBLOBs
198  
199 Returns 1 if the current database supports inserts of BLOBs automatically.
200 Returns undef if the current database must be informed of BLOBs for inserts.
201  
202 =cut
203  
204 sub KnowsBLOBs {
205 my $self = shift;
206 return(undef);
207 }
208  
209  
210  
211 =head2 BLOBParams FIELD_NAME FIELD_TYPE
212  
213 Returns a hash ref for the bind_param call to identify BLOB types used by
214 the current database for a particular column type.
215 The current Oracle implementation only supports ORA_CLOB types (112).
216  
217 =cut
218  
219 sub BLOBParams {
220 my $self = shift;
221 my $field = shift;
222 #my $type = shift;
223 # Don't assign to key 'value' as it is defined later.
224 return (
225 { ora_field => $field, ora_type => ORA_CLOB, }
226 );
227 }
228  
229  
230  
231 =head2 ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW
232  
233 takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE starting with FIRST_ROW;
234  
235 =cut
236  
237 sub ApplyLimits {
238 my $self = shift;
239 my $statementref = shift;
240 my $per_page = shift;
241 my $first = shift;
242 my $sb = shift;
243  
244 # Transform an SQL query from:
245 #
246 # SELECT main.*
247 # FROM Tickets main
248 # WHERE ((main.EffectiveId = main.id))
249 # AND ((main.Type = 'ticket'))
250 # AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
251 # AND ( (main.Queue = '1') ) )
252 #
253 # to:
254 #
255 # SELECT * FROM (
256 # SELECT limitquery.*,rownum limitrownum FROM (
257 # SELECT main.*
258 # FROM Tickets main
259 # WHERE ((main.EffectiveId = main.id))
260 # AND ((main.Type = 'ticket'))
261 # AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
262 # AND ( (main.Queue = '1') ) )
263 # ) limitquery WHERE rownum <= 50
264 # ) WHERE limitrownum >= 1
265 #
266  
267 if ($per_page) {
268 # Oracle orders from 1 not zero
269 $first++;
270 # Make current query a sub select
271 my $last = $first + $per_page - 1;
272 if ( $sb->{_bind_values} ) {
273 push @{ $sb->{_bind_values} }, $last, $first;
274 $first = $last = '?';
275 }
276 $$statementref = "SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( $$statementref ) limitquery WHERE rownum <= " . $last . " ) WHERE limitrownum >= " . $first;
277 }
278 }
279  
280  
281  
282 =head2 DistinctQuery STATEMENTREF
283  
284 takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set.
285  
286 =cut
287  
288 sub DistinctQuery {
289 my $self = shift;
290 my $statementref = shift;
291 my $sb = shift;
292  
293 my $table = $sb->Table;
294 my $hint = $sb->QueryHint;
295 $hint = $hint ? " /* $hint */ " : " ";
296  
297 if ($sb->_OrderClause =~ /(?<!main)\./) {
298 # If we are ordering by something not in 'main', we need to GROUP
299 # BY and adjust the ORDER_BY accordingly
300 local $sb->{group_by} = [@{$sb->{group_by} || []}, {FIELD => 'id'}];
301 local $sb->{'order_by'} = [
302 map {
303 ($_->{'ALIAS'}||'') ne "main"
304 ? { %{$_}, FIELD => ((($_->{'ORDER'}||'') =~ /^des/i)?'MAX':'MIN') ."(".$_->{FIELD}.")" }
305 : $_
306 }
307 @{$sb->{'order_by'}}
308 ];
309 my $group = $sb->_GroupClause;
310 my $order = $sb->_OrderClause;
311 $$statementref = "SELECT" . $hint . "main.* FROM ( SELECT main.id, row_number() over( $order ) sortorder FROM $$statementref $group ) distinctquery, $table main WHERE (main.id = distinctquery.id) ORDER BY distinctquery.sortorder";
312 } else {
313 # Wrapp select query in a subselect as Oracle doesn't allow
314 # DISTINCT against CLOB/BLOB column types.
315 $$statementref = "SELECT" . $hint . " main.* FROM ( SELECT DISTINCT main.id FROM $$statementref ) distinctquery, $table main WHERE (main.id = distinctquery.id) ";
316 $$statementref .= $sb->_GroupClause;
317 $$statementref .= $sb->_OrderClause;
318 }
319 }
320  
321  
322  
323  
324 =head2 BinarySafeBLOBs
325  
326 Return undef, as Oracle doesn't support binary-safe CLOBS
327  
328  
329 =cut
330  
331 sub BinarySafeBLOBs {
332 my $self = shift;
333 return(undef);
334 }
335  
336 =head2 DatabaseVersion
337  
338 Returns value of ORA_OCI constant, see L<DBI/Constants>.
339  
340 =cut
341  
342 sub DatabaseVersion {
343 return ''. ORA_OCI;
344 }
345  
346 sub Fields {
347 my $self = shift;
348 my $table = shift;
349  
350 my $cache = \%DBIx::SearchBuilder::Handle::FIELDS_IN_TABLE;
351 unless ( $cache->{ lc $table } ) {
352 # uc(table) required as oracle stores UC names in information tables
353 # and lookup clauses are case sensetive
354 my $sth = $self->dbh->column_info( undef, undef, uc($table), '%' )
355 or return ();
356 my $info = $sth->fetchall_arrayref({});
357 # TODO: not sure why results are lower case, probably NAME_ls affects it
358 # we should check it out at some point
359 foreach my $e ( sort {$a->{'ordinal_position'} <=> $b->{'ordinal_position'}} @$info ) {
360 push @{ $cache->{ lc $e->{'table_name'} } ||= [] }, lc $e->{'column_name'};
361 }
362 }
363 return @{ $cache->{ lc $table } || [] };
364 }
365  
366 =head2 SimpleDateTimeFunctions
367  
368 Returns hash reference with specific date time functions of this
369 database for L<DBIx::SearchBuilder::Handle/DateTimeFunction>.
370  
371 =cut
372  
373 # http://download.oracle.com/docs/cd/B14117_01/server.101/b10749/ch4datetime.htm
374 sub SimpleDateTimeFunctions {
375 my $self = shift;
376 return $self->{'_simple_date_time_functions'}
377 if $self->{'_simple_date_time_functions'};
378  
379 my %res = %{ $self->SUPER::SimpleDateTimeFunctions(@_) };
380  
381 return $self->{'_simple_date_time_functions'} ||= {
382 %res,
383 datetime => "?",
384 time => "TO_CHAR(?, 'HH24:MI:SS')",
385  
386 hourly => "TO_CHAR(?, 'YYYY-MM-DD HH24')",
387 hour => "TO_CHAR(?, 'HH24')",
388  
389 date => "TO_CHAR(?, 'YYYY-MM-DD')",
390 daily => "TO_CHAR(?, 'YYYY-MM-DD')",
391  
392 day => "TO_CHAR(?, 'DD')",
393 dayofmonth => "TO_CHAR(?, 'DD')",
394  
395 monthly => "TO_CHAR(?, 'YYYY-MM')",
396 month => "TO_CHAR(?, 'MM')",
397  
398 annually => "TO_CHAR(?, 'YYYY')",
399 year => "TO_CHAR(?, 'YYYY')",
400  
401 dayofweek => "TO_CHAR(?, 'D') - 1", # 1-7, 1 - Sunday
402 dayofyear => "TO_CHAR(?, 'DDD')", # 1-366
403 # no idea about props
404 weekofyear => "TO_CHAR(?, 'WW')",
405 };
406 }
407  
408 =head2 ConvertTimezoneFunction
409  
410 Custom implementation of L<DBIx::SearchBuilder::Handle/ConvertTimezoneFunction>.
411  
412 Use the following query to get list of timezones:
413  
414 SELECT tzname FROM v$timezone_names;
415  
416 Read Oracle's docs about timezone files:
417  
418 http://download.oracle.com/docs/cd/B14117_01/server.101/b10749/ch4datetime.htm#i1006667
419  
420 =cut
421  
422 sub ConvertTimezoneFunction {
423 my $self = shift;
424 my %args = (
425 From => 'UTC',
426 To => undef,
427 Field => '',
428 @_
429 );
430 return $args{'Field'} unless $args{From} && $args{'To'};
431 return $args{'Field'} if lc $args{From} eq lc $args{'To'};
432  
433 my $dbh = $self->dbh;
434 $_ = $dbh->quote( $_ ) foreach @args{'From', 'To'};
435 return "FROM_TZ( CAST ($args{'Field'} AS TIMESTAMP), $args{'From'}) AT TIME ZONE $args{'To'}";
436 }
437  
438 sub _DateTimeIntervalFunction {
439 my $self = shift;
440 my %args = ( From => undef, To => undef, @_ );
441  
442 return "ROUND(( CAST( $args{'To'} AS DATE ) - CAST( $args{'From'} AS DATE ) ) * 86400)";
443 }
444  
445 sub HasSupportForNullsOrder {
446 return 1;
447 }
448  
449 sub HasSupportForEmptyString {
450 return 0;
451 }
452  
453 sub CastAsDecimal {
454 my $self = shift;
455 my $field = shift or return;
456 return "TO_NUMBER($field)";
457 }
458  
459 1;