| 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; |