b9bf0953f67bab8746364d76b1862e29cd29a4ce
[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / Storage / DBI / Oracle / Generic.pm
1 package DBIx::Class::Storage::DBI::Oracle::Generic;
2
3 use strict;
4 use warnings;
5 use Scope::Guard ();
6 use Context::Preserve 'preserve_context';
7 use Try::Tiny;
8 use List::Util 'first';
9 use namespace::clean;
10
11 __PACKAGE__->sql_limit_dialect ('RowNum');
12 __PACKAGE__->sql_quote_char ('"');
13
14 =head1 NAME
15
16 DBIx::Class::Storage::DBI::Oracle::Generic - Oracle Support for DBIx::Class
17
18 =head1 SYNOPSIS
19
20   # In your result (table) classes
21   use base 'DBIx::Class::Core';
22   __PACKAGE__->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } });
23   __PACKAGE__->set_primary_key('id');
24
25   # Somewhere in your Code
26   # add some data to a table with a hierarchical relationship
27   $schema->resultset('Person')->create ({
28         firstname => 'foo',
29         lastname => 'bar',
30         children => [
31             {
32                 firstname => 'child1',
33                 lastname => 'bar',
34                 children => [
35                     {
36                         firstname => 'grandchild',
37                         lastname => 'bar',
38                     }
39                 ],
40             },
41             {
42                 firstname => 'child2',
43                 lastname => 'bar',
44             },
45         ],
46     });
47
48   # select from the hierarchical relationship
49   my $rs = $schema->resultset('Person')->search({},
50     {
51       'start_with' => { 'firstname' => 'foo', 'lastname' => 'bar' },
52       'connect_by' => { 'parentid' => { '-prior' => { -ident => 'personid' } },
53       'order_siblings_by' => { -asc => 'name' },
54     };
55   );
56
57   # this will select the whole tree starting from person "foo bar", creating
58   # following query:
59   # SELECT
60   #     me.persionid me.firstname, me.lastname, me.parentid
61   # FROM
62   #     person me
63   # START WITH
64   #     firstname = 'foo' and lastname = 'bar'
65   # CONNECT BY
66   #     parentid = prior personid
67   # ORDER SIBLINGS BY
68   #     firstname ASC
69
70 =head1 DESCRIPTION
71
72 This class implements base Oracle support. The subclass
73 L<DBIx::Class::Storage::DBI::Oracle::WhereJoins> is for C<(+)> joins in Oracle
74 versions before 9.0.
75
76 =head1 METHODS
77
78 =cut
79
80 use base qw/DBIx::Class::Storage::DBI/;
81 use mro 'c3';
82
83 __PACKAGE__->sql_maker_class('DBIx::Class::SQLMaker::Oracle');
84 __PACKAGE__->datetime_parser_type('DateTime::Format::Oracle');
85
86 sub __cache_queries_with_max_lob_parts { 2 }
87
88 sub _determine_supports_insert_returning {
89   my $self = shift;
90
91 # TODO find out which version supports the RETURNING syntax
92 # 8i has it and earlier docs are a 404 on oracle.com
93
94   return 1
95     if $self->_server_info->{normalized_dbms_version} >= 8.001;
96
97   return 0;
98 }
99
100 __PACKAGE__->_use_insert_returning_bound (1);
101
102 sub deployment_statements {
103   my $self = shift;;
104   my ($schema, $type, $version, $dir, $sqltargs, @rest) = @_;
105
106   $sqltargs ||= {};
107   my $quote_char = $self->schema->storage->sql_maker->quote_char;
108   $sqltargs->{quote_table_names} = $quote_char ? 1 : 0;
109   $sqltargs->{quote_field_names} = $quote_char ? 1 : 0;
110
111   if (
112     ! exists $sqltargs->{producer_args}{oracle_version}
113       and
114     my $dver = $self->_server_info->{dbms_version}
115   ) {
116     $sqltargs->{producer_args}{oracle_version} = $dver;
117   }
118
119   $self->next::method($schema, $type, $version, $dir, $sqltargs, @rest);
120 }
121
122 sub _dbh_last_insert_id {
123   my ($self, $dbh, $source, @columns) = @_;
124   my @ids = ();
125   foreach my $col (@columns) {
126     my $seq = ($source->column_info($col)->{sequence} ||= $self->get_autoinc_seq($source,$col));
127     my $id = $self->_sequence_fetch( 'CURRVAL', $seq );
128     push @ids, $id;
129   }
130   return @ids;
131 }
132
133 sub _dbh_get_autoinc_seq {
134   my ($self, $dbh, $source, $col) = @_;
135
136   my $sql_maker = $self->sql_maker;
137   my ($ql, $qr) = map { $_ ? (quotemeta $_) : '' } $sql_maker->_quote_chars;
138
139   my $source_name;
140   if ( ref $source->name eq 'SCALAR' ) {
141     $source_name = ${$source->name};
142
143     # the ALL_TRIGGERS match further on is case sensitive - thus uppercase
144     # stuff unless it is already quoted
145     $source_name = uc ($source_name) if $source_name !~ /\"/;
146   }
147   else {
148     $source_name = $source->name;
149     $source_name = uc($source_name) unless $ql;
150   }
151
152   # trigger_body is a LONG
153   local $dbh->{LongReadLen} = 64 * 1024 if ($dbh->{LongReadLen} < 64 * 1024);
154
155   # disable default bindtype
156   local $sql_maker->{bindtype} = 'normal';
157
158   # look up the correct sequence automatically
159   my ( $schema, $table ) = $source_name =~ /( (?:${ql})? \w+ (?:${qr})? ) \. ( (?:${ql})? \w+ (?:${qr})? )/x;
160
161   # if no explicit schema was requested - use the default schema (which in the case of Oracle is the db user)
162   $schema ||= uc( ($self->_dbi_connect_info||[])->[1] || '');
163
164   my ($sql, @bind) = $sql_maker->select (
165     'ALL_TRIGGERS',
166     [qw/TRIGGER_BODY TABLE_OWNER TRIGGER_NAME/],
167     {
168       $schema ? (OWNER => $schema) : (),
169       TABLE_NAME => $table || $source_name,
170       TRIGGERING_EVENT => { -like => '%INSERT%' },  # this will also catch insert_or_update
171       TRIGGER_TYPE => { -like => '%BEFORE%' },      # we care only about 'before' triggers
172       STATUS => 'ENABLED',
173      },
174   );
175
176   # to find all the triggers that mention the column in question a simple
177   # regex grep since the trigger_body above is a LONG and hence not searchable
178   my @triggers = ( map
179     { my %inf; @inf{qw/body schema name/} = @$_; \%inf }
180     ( grep
181       { $_->[0] =~ /\:new\.${ql}${col}${qr} | \:new\.$col/xi }
182       @{ $dbh->selectall_arrayref( $sql, {}, @bind ) }
183     )
184   );
185
186   # extract all sequence names mentioned in each trigger
187   for (@triggers) {
188     $_->{sequences} = [ $_->{body} =~ / ( "? [\.\w\"\-]+ "? ) \. nextval /xig ];
189   }
190
191   my $chosen_trigger;
192
193   # if only one trigger matched things are easy
194   if (@triggers == 1) {
195
196     if ( @{$triggers[0]{sequences}} == 1 ) {
197       $chosen_trigger = $triggers[0];
198     }
199     else {
200       $self->throw_exception( sprintf (
201         "Unable to introspect trigger '%s' for column %s.%s (references multiple sequences). "
202       . "You need to specify the correct 'sequence' explicitly in '%s's column_info.",
203         $triggers[0]{name},
204         $source_name,
205         $col,
206         $col,
207       ) );
208     }
209   }
210   # got more than one matching trigger - see if we can narrow it down
211   elsif (@triggers > 1) {
212
213     my @candidates = grep
214       { $_->{body} =~ / into \s+ \:new\.$col /xi }
215       @triggers
216     ;
217
218     if (@candidates == 1 && @{$candidates[0]{sequences}} == 1) {
219       $chosen_trigger = $candidates[0];
220     }
221     else {
222       $self->throw_exception( sprintf (
223         "Unable to reliably select a BEFORE INSERT trigger for column %s.%s (possibilities: %s). "
224       . "You need to specify the correct 'sequence' explicitly in '%s's column_info.",
225         $source_name,
226         $col,
227         ( join ', ', map { "'$_->{name}'" } @triggers ),
228         $col,
229       ) );
230     }
231   }
232
233   if ($chosen_trigger) {
234     my $seq_name = $chosen_trigger->{sequences}[0];
235
236     $seq_name = "$chosen_trigger->{schema}.$seq_name"
237       unless $seq_name =~ /\./;
238
239     return \$seq_name if $seq_name =~ /\"/; # may already be quoted in-trigger
240     return $seq_name;
241   }
242
243   $self->throw_exception( sprintf (
244     "No suitable BEFORE INSERT triggers found for column %s.%s. "
245   . "You need to specify the correct 'sequence' explicitly in '%s's column_info.",
246     $source_name,
247     $col,
248     $col,
249   ));
250 }
251
252 sub _sequence_fetch {
253   my ( $self, $type, $seq ) = @_;
254
255   # use the maker to leverage quoting settings
256   my $sql_maker = $self->sql_maker;
257   my ($id) = $self->_get_dbh->selectrow_array ($sql_maker->select('DUAL', [ ref $seq ? \"$$seq.$type" : "$seq.$type" ] ) );
258   return $id;
259 }
260
261 sub _ping {
262   my $self = shift;
263
264   my $dbh = $self->_dbh or return 0;
265
266   local $dbh->{RaiseError} = 1;
267   local $dbh->{PrintError} = 0;
268
269   return try {
270     $dbh->do('select 1 from dual');
271     1;
272   } catch {
273     0;
274   };
275 }
276
277 sub _dbh_execute {
278   my ($self, $dbh, $sql, @args) = @_;
279
280   my (@res, $tried);
281   my $want = wantarray;
282   my $next = $self->next::can;
283   do {
284     try {
285       my $exec = sub {
286         # Turn off sth caching for multi-part LOBs. See _prep_for_execute above.
287         local $self->{disable_sth_caching} = 1
288           if first {
289             ($_->[0]{_ora_lob_autosplit_part}||0)
290               > (__cache_queries_with_max_lob_parts-1)
291           } @{ $args[0] };
292
293         $self->$next($dbh, $sql, @args)
294       };
295
296       if (!defined $want) {
297         $exec->();
298       }
299       elsif (! $want) {
300         $res[0] = $exec->();
301       }
302       else {
303         @res = $exec->();
304       }
305
306       $tried++;
307     }
308     catch {
309       if (! $tried and $_ =~ /ORA-01003/) {
310         # ORA-01003: no statement parsed (someone changed the table somehow,
311         # invalidating your cursor.)
312         delete $dbh->{CachedKids}{$sql};
313       }
314       else {
315         $self->throw_exception($_);
316       }
317     };
318   } while (! $tried++);
319
320   return wantarray ? @res : $res[0];
321 }
322
323 sub _dbh_execute_array {
324   #my ($self, $sth, $tuple_status, @extra) = @_;
325
326   # DBD::Oracle warns loudly on partial execute_array failures
327   local $_[1]->{PrintWarn} = 0;
328
329   shift->next::method(@_);
330 }
331
332 =head2 get_autoinc_seq
333
334 Returns the sequence name for an autoincrement column
335
336 =cut
337
338 sub get_autoinc_seq {
339   my ($self, $source, $col) = @_;
340
341   $self->dbh_do('_dbh_get_autoinc_seq', $source, $col);
342 }
343
344 =head2 datetime_parser_type
345
346 This sets the proper DateTime::Format module for use with
347 L<DBIx::Class::InflateColumn::DateTime>.
348
349 =head2 connect_call_datetime_setup
350
351 Used as:
352
353     on_connect_call => 'datetime_setup'
354
355 In L<connect_info|DBIx::Class::Storage::DBI/connect_info> to set the session nls
356 date, and timestamp values for use with L<DBIx::Class::InflateColumn::DateTime>
357 and the necessary environment variables for L<DateTime::Format::Oracle>, which
358 is used by it.
359
360 Maximum allowable precision is used, unless the environment variables have
361 already been set.
362
363 These are the defaults used:
364
365   $ENV{NLS_DATE_FORMAT}         ||= 'YYYY-MM-DD HH24:MI:SS';
366   $ENV{NLS_TIMESTAMP_FORMAT}    ||= 'YYYY-MM-DD HH24:MI:SS.FF';
367   $ENV{NLS_TIMESTAMP_TZ_FORMAT} ||= 'YYYY-MM-DD HH24:MI:SS.FF TZHTZM';
368
369 To get more than second precision with L<DBIx::Class::InflateColumn::DateTime>
370 for your timestamps, use something like this:
371
372   use Time::HiRes 'time';
373   my $ts = DateTime->from_epoch(epoch => time);
374
375 =cut
376
377 sub connect_call_datetime_setup {
378   my $self = shift;
379
380   my $date_format = $ENV{NLS_DATE_FORMAT} ||= 'YYYY-MM-DD HH24:MI:SS';
381   my $timestamp_format = $ENV{NLS_TIMESTAMP_FORMAT} ||=
382     'YYYY-MM-DD HH24:MI:SS.FF';
383   my $timestamp_tz_format = $ENV{NLS_TIMESTAMP_TZ_FORMAT} ||=
384     'YYYY-MM-DD HH24:MI:SS.FF TZHTZM';
385
386   $self->_do_query(
387     "alter session set nls_date_format = '$date_format'"
388   );
389   $self->_do_query(
390     "alter session set nls_timestamp_format = '$timestamp_format'"
391   );
392   $self->_do_query(
393     "alter session set nls_timestamp_tz_format='$timestamp_tz_format'"
394   );
395 }
396
397 ### Note originally by Ron "Quinn" Straight <quinnfazigu@gmail.org>
398 ### http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commitdiff;h=5db2758de644d53e07cd3e05f0e9037bf40116fc
399 #
400 # Handle LOB types in Oracle.  Under a certain size (4k?), you can get away
401 # with the driver assuming your input is the deprecated LONG type if you
402 # encode it as a hex string.  That ain't gonna fly at larger values, where
403 # you'll discover you have to do what this does.
404 #
405 # This method had to be overridden because we need to set ora_field to the
406 # actual column, and that isn't passed to the call (provided by Storage) to
407 # bind_attribute_by_data_type.
408 #
409 # According to L<DBD::Oracle>, the ora_field isn't always necessary, but
410 # adding it doesn't hurt, and will save your bacon if you're modifying a
411 # table with more than one LOB column.
412 #
413 sub _dbi_attrs_for_bind {
414   my ($self, $ident, $bind) = @_;
415
416   my $attrs = $self->next::method($ident, $bind);
417
418   for my $i (0 .. $#$attrs) {
419     if (keys %{$attrs->[$i]||{}} and my $col = $bind->[$i][0]{dbic_colname}) {
420       $attrs->[$i]{ora_field} = $col;
421     }
422   }
423
424   $attrs;
425 }
426
427 my $dbd_loaded;
428 sub bind_attribute_by_data_type {
429   my ($self, $dt) = @_;
430
431   $dbd_loaded ||= do {
432     require DBD::Oracle;
433     if ($DBD::Oracle::VERSION eq '1.23') {
434       $self->throw_exception(
435         "BLOB/CLOB support in DBD::Oracle == 1.23 is broken, use an earlier or later ".
436         "version.\n\nSee: https://rt.cpan.org/Public/Bug/Display.html?id=46016\n"
437       );
438     }
439     1;
440   };
441
442   if ($self->_is_lob_type($dt)) {
443     return {
444       ora_type => $self->_is_text_lob_type($dt)
445         ? DBD::Oracle::ORA_CLOB()
446         : DBD::Oracle::ORA_BLOB()
447     };
448   }
449 }
450
451 # Handle blob columns in WHERE.
452 #
453 # For equality comparisons:
454 #
455 # We split data intended for comparing to a LOB into 2000 character chunks and
456 # compare them using dbms_lob.substr on the LOB column.
457 #
458 # We turn off DBD::Oracle LOB binds for these partial LOB comparisons by passing
459 # dbd_attrs => undef, because these are regular varchar2 comparisons and
460 # otherwise the query will fail.
461 #
462 # Since the most common comparison size is likely to be under 4000 characters
463 # (TEXT comparisons previously deployed to other RDBMSes) we disable
464 # prepare_cached for queries with more than two part comparisons to a LOB
465 # column. This is done in _dbh_execute (above) which was previously overridden
466 # to gracefully recover from an Oracle error. This is to be careful to not
467 # exhaust your application's open cursor limit.
468 #
469 # See:
470 # http://itcareershift.com/blog1/2011/02/21/oracle-max-number-of-open-cursors-complete-reference-for-the-new-oracle-dba/
471 # on the open_cursor limit.
472 #
473 # For everything else:
474 #
475 # We assume that everything that is not a LOB comparison, will most likely be a
476 # LIKE query or some sort of function invocation. This may prove to be a naive
477 # assumption in the future, but for now it should cover the two most likely
478 # things users would want to do with a BLOB or CLOB, an equality test or a LIKE
479 # query (on a CLOB.)
480 #
481 # For these expressions, the bind must NOT have the attributes of a LOB bind for
482 # DBD::Oracle, otherwise the query will fail. This is done by passing
483 # dbd_attrs => undef.
484
485 sub _prep_for_execute {
486   my $self = shift;
487   my ($op) = @_;
488
489   my ($sql, $bind) = $self->next::method(@_);
490
491   return ($sql, $bind) if $op ne 'select';
492
493   my @sql_part = split /\?/, $sql;
494   my ($new_sql, @new_binds);
495
496   foreach my $bound (@$bind) {
497     my $data_type = $bound->[0]{sqlt_datatype}||'';
498
499     if ($self->_is_lob_type($data_type)) {
500       if (my ($col, $eq) = $sql_part[0] =~ /(?<=\s)([\w."]+)(\s*=\s*)$/) {
501         my $data = $bound->[1];
502
503         $data = "$data" if ref $data;
504
505         my @parts = unpack '(a2000)*', $data;
506
507         my @sql_frag;
508
509         for my $idx (0..$#parts) {
510           push @sql_frag,
511 "UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(DBMS_LOB.SUBSTR($col, 2000, ".($idx*2000+1)."))) = ?";
512         }
513
514         my $sql_frag = '( ' . (join ' AND ', @sql_frag) . ' )';
515
516         $sql_part[0] =~ s/(?<=\s)([\w."]+)(\s*=\s*)$/$sql_frag/;
517
518         $new_sql .= shift @sql_part;
519
520         for my $idx (0..$#parts) {
521           push @new_binds, [
522             {
523               %{ $bound->[0] },
524               _ora_lob_autosplit_part => $idx,
525               dbd_attrs => undef,
526             },
527             $parts[$idx]
528           ];
529         }
530       }
531       else {
532         $new_sql .= shift(@sql_part) . '?';
533
534         push @new_binds, [
535           {
536             %{ $bound->[0] },
537             dbd_attrs => undef,
538           },
539           $bound->[1],
540         ];
541       }
542     }
543     else {
544       $new_sql .= shift(@sql_part) . '?';
545       push @new_binds, $bound;
546     }
547   }
548   $new_sql .= join '', @sql_part;
549
550   return ($new_sql, \@new_binds);
551 }
552
553 # Savepoints stuff.
554
555 sub _exec_svp_begin {
556   my ($self, $name) = @_;
557   $self->_dbh->do("SAVEPOINT $name");
558 }
559
560 # Oracle automatically releases a savepoint when you start another one with the
561 # same name.
562 sub _exec_svp_release { 1 }
563
564 sub _exec_svp_rollback {
565   my ($self, $name) = @_;
566   $self->_dbh->do("ROLLBACK TO SAVEPOINT $name")
567 }
568
569 =head2 relname_to_table_alias
570
571 L<DBIx::Class> uses L<DBIx::Class::Relationship> names as table aliases in
572 queries.
573
574 Unfortunately, Oracle doesn't support identifiers over 30 chars in length, so
575 the L<DBIx::Class::Relationship> name is shortened and appended with half of an
576 MD5 hash.
577
578 See L<DBIx::Class::Storage/"relname_to_table_alias">.
579
580 =cut
581
582 sub relname_to_table_alias {
583   my $self = shift;
584   my ($relname, $join_count) = @_;
585
586   my $alias = $self->next::method(@_);
587
588   # we need to shorten here in addition to the shortening in SQLA itself,
589   # since the final relnames are a crucial for the join optimizer
590   return $self->sql_maker->_shorten_identifier($alias);
591 }
592
593 =head2 with_deferred_fk_checks
594
595 Runs a coderef between:
596
597   alter session set constraints = deferred
598   ...
599   alter session set constraints = immediate
600
601 to defer foreign key checks.
602
603 Constraints must be declared C<DEFERRABLE> for this to work.
604
605 =cut
606
607 sub with_deferred_fk_checks {
608   my ($self, $sub) = @_;
609
610   my $txn_scope_guard = $self->txn_scope_guard;
611
612   $self->_do_query('alter session set constraints = deferred');
613
614   my $sg = Scope::Guard->new(sub {
615     $self->_do_query('alter session set constraints = immediate');
616   });
617
618   return
619     preserve_context { $sub->() } after => sub { $txn_scope_guard->commit };
620 }
621
622 =head1 ATTRIBUTES
623
624 Following additional attributes can be used in resultsets.
625
626 =head2 connect_by or connect_by_nocycle
627
628 =over 4
629
630 =item Value: \%connect_by
631
632 =back
633
634 A hashref of conditions used to specify the relationship between parent rows
635 and child rows of the hierarchy.
636
637
638   connect_by => { parentid => 'prior personid' }
639
640   # adds a connect by statement to the query:
641   # SELECT
642   #     me.persionid me.firstname, me.lastname, me.parentid
643   # FROM
644   #     person me
645   # CONNECT BY
646   #     parentid = prior persionid
647   
648
649   connect_by_nocycle => { parentid => 'prior personid' }
650
651   # adds a connect by statement to the query:
652   # SELECT
653   #     me.persionid me.firstname, me.lastname, me.parentid
654   # FROM
655   #     person me
656   # CONNECT BY NOCYCLE
657   #     parentid = prior persionid
658
659
660 =head2 start_with
661
662 =over 4
663
664 =item Value: \%condition
665
666 =back
667
668 A hashref of conditions which specify the root row(s) of the hierarchy.
669
670 It uses the same syntax as L<DBIx::Class::ResultSet/search>
671
672   start_with => { firstname => 'Foo', lastname => 'Bar' }
673
674   # SELECT
675   #     me.persionid me.firstname, me.lastname, me.parentid
676   # FROM
677   #     person me
678   # START WITH
679   #     firstname = 'foo' and lastname = 'bar'
680   # CONNECT BY
681   #     parentid = prior persionid
682
683 =head2 order_siblings_by
684
685 =over 4
686
687 =item Value: ($order_siblings_by | \@order_siblings_by)
688
689 =back
690
691 Which column(s) to order the siblings by.
692
693 It uses the same syntax as L<DBIx::Class::ResultSet/order_by>
694
695   'order_siblings_by' => 'firstname ASC'
696
697   # SELECT
698   #     me.persionid me.firstname, me.lastname, me.parentid
699   # FROM
700   #     person me
701   # CONNECT BY
702   #     parentid = prior persionid
703   # ORDER SIBLINGS BY
704   #     firstname ASC
705
706 =head1 AUTHOR
707
708 See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
709
710 =head1 LICENSE
711
712 You may distribute this code under the same terms as Perl itself.
713
714 =cut
715
716 1;
717 # vim:sts=2 sw=2: