use Try::Tiny;
use namespace::clean;
+__PACKAGE__->sql_limit_dialect ('RowNum');
+
=head1 NAME
DBIx::Class::Storage::DBI::Oracle::Generic - Oracle Support for DBIx::Class
my $rs = $schema->resultset('Person')->search({},
{
'start_with' => { 'firstname' => 'foo', 'lastname' => 'bar' },
- 'connect_by' => { 'parentid' => { '-prior' => \'persionid' },
+ 'connect_by' => { 'parentid' => { '-prior' => { -ident => 'personid' } },
'order_siblings_by' => { -asc => 'name' },
};
);
# START WITH
# firstname = 'foo' and lastname = 'bar'
# CONNECT BY
- # parentid = prior persionid
+ # parentid = prior personid
# ORDER SIBLINGS BY
# firstname ASC
use base qw/DBIx::Class::Storage::DBI/;
use mro 'c3';
-__PACKAGE__->sql_maker_class('DBIx::Class::SQLAHacks::Oracle');
+__PACKAGE__->sql_maker_class('DBIx::Class::SQLMaker::Oracle');
sub deployment_statements {
my $self = shift;;
$sqltargs->{quote_table_names} = $quote_char ? 1 : 0;
$sqltargs->{quote_field_names} = $quote_char ? 1 : 0;
- my $oracle_version = try { $self->_get_dbh->get_info(18) };
-
- $sqltargs->{producer_args}{oracle_version} = $oracle_version;
+ if (
+ ! exists $sqltargs->{producer_args}{oracle_version}
+ and
+ my $dver = $self->_server_info->{dbms_version}
+ ) {
+ $sqltargs->{producer_args}{oracle_version} = $dver;
+ }
$self->next::method($schema, $type, $version, $dir, $sqltargs, @rest);
}
my ( $schema, $table ) = $source_name =~ /(\w+)\.(\w+)/;
my ($sql, @bind) = $sql_maker->select (
'ALL_TRIGGERS',
- ['trigger_body'],
+ [qw/ trigger_body table_owner trigger_name /],
{
$schema ? (owner => $schema) : (),
table_name => $table || $source_name,
- triggering_event => { -like => '%INSERT%' },
+ triggering_event => { -like => '%INSERT%' }, # this will also catch insert_or_update
+ trigger_type => { -like => '%BEFORE%' }, # we care only about 'before' triggers
status => 'ENABLED',
},
);
- my $sth = $dbh->prepare($sql);
- $sth->execute (@bind);
- while (my ($insert_trigger) = $sth->fetchrow_array) {
- return $1 if $insert_trigger =~ m!("?\w+"?)\.nextval!i; # col name goes here???
+ # to find all the triggers that mention the column in question a simple
+ # regex grep since the trigger_body above is a LONG and hence not searchable
+ my @triggers = ( map
+ { my %inf; @inf{qw/body schema name/} = @$_; \%inf }
+ ( grep
+ { $_->[0] =~ /\:new\.$col/i }
+ @{ $dbh->selectall_arrayref( $sql, {}, @bind ) }
+ )
+ );
+
+ # extract all sequence names mentioned in each trigger
+ for (@triggers) {
+ $_->{sequences} = [ $_->{body} =~ / ( "? [\.\w\"\-]+ "? ) \. nextval /xig ];
+ }
+
+ my $chosen_trigger;
+
+ # if only one trigger matched things are easy
+ if (@triggers == 1) {
+
+ if ( @{$triggers[0]{sequences}} == 1 ) {
+ $chosen_trigger = $triggers[0];
+ }
+ else {
+ $self->throw_exception( sprintf (
+ "Unable to introspect trigger '%s' for column %s.%s (references multiple sequences). "
+ . "You need to specify the correct 'sequence' explicitly in '%s's column_info.",
+ $triggers[0]{name},
+ $source_name,
+ $col,
+ $col,
+ ) );
+ }
}
- $self->throw_exception("Unable to find a sequence INSERT trigger on table '$source_name'.");
+ # got more than one matching trigger - see if we can narrow it down
+ elsif (@triggers > 1) {
+
+ my @candidates = grep
+ { $_->{body} =~ / into \s+ \:new\.$col /xi }
+ @triggers
+ ;
+
+ if (@candidates == 1 && @{$candidates[0]{sequences}} == 1) {
+ $chosen_trigger = $candidates[0];
+ }
+ else {
+ $self->throw_exception( sprintf (
+ "Unable to reliably select a BEFORE INSERT trigger for column %s.%s (possibilities: %s). "
+ . "You need to specify the correct 'sequence' explicitly in '%s's column_info.",
+ $source_name,
+ $col,
+ ( join ', ', map { "'$_->{name}'" } @triggers ),
+ $col,
+ ) );
+ }
+ }
+
+ if ($chosen_trigger) {
+ my $seq_name = $chosen_trigger->{sequences}[0];
+
+ $seq_name = "$chosen_trigger->{schema}.$seq_name"
+ unless $seq_name =~ /\./;
+
+ return $seq_name;
+ }
+
+ $self->throw_exception( sprintf (
+ "No suitable BEFORE INSERT triggers found for column %s.%s. "
+ . "You need to specify the correct 'sequence' explicitly in '%s's column_info.",
+ $source_name,
+ $col,
+ $col,
+ ));
}
sub _sequence_fetch {
my $alias = $self->next::method(@_);
- return $alias if length($alias) <= 30;
-
- # get a base64 md5 of the alias with join_count
- require Digest::MD5;
- my $ctx = Digest::MD5->new;
- $ctx->add($alias);
- my $md5 = $ctx->b64digest;
-
- # remove alignment mark just in case
- $md5 =~ s/=*\z//;
-
- # truncate and prepend to truncated relname without vowels
- (my $devoweled = $relname) =~ s/[aeiou]//g;
- my $shortened = substr($devoweled, 0, 18);
-
- my $new_alias =
- $shortened . '_' . substr($md5, 0, 30 - length($shortened) - 1);
-
- return $new_alias;
+ return $self->sql_maker->_shorten_identifier($alias, [$relname]);
}
=head2 with_deferred_fk_checks