| Subject: | DBIx::Class::Storage::DBI::Oracle::Generic does not handle fully-qualified table names |
The method _dbh_get_autoinc_seq in
lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm does not handle
fully-qualified table names (eg. SCHEMA.TABLENAME). It tries to lookup
the sequence using "WHERE t.table_name = ?".
As a result, ->create calls are failing with:
DBIx::Class::ResultSet::create(): Unable to find a sequence INSERT
trigger on table 'foo.bar'...
The attached patch does the right thing, splitting the table name if
it contains a dot, and using "WHERE t.owner = ? AND t.table_name = ?'
for the lookup.
A test case for this has been added to t/73oracle.t.
cheers,
Aldo
| Subject: | Oracle-FQN.patch |
diff -Nurb DBIx-Class-0.08099_04/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm DBIx-Class-0.08099_04_PATCHED/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm
--- DBIx-Class-0.08099_04/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm 2008-07-24 00:22:28.000000000 +0200
+++ DBIx-Class-0.08099_04_PATCHED/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm 2008-08-06 16:36:52.000000000 +0200
@@ -55,8 +55,22 @@
# trigger_body is a LONG
$dbh->{LongReadLen} = 64 * 1024 if ($dbh->{LongReadLen} < 64 * 1024);
- my $sth = $dbh->prepare($sql);
+ my $sth;
+
+ # check for fully-qualified name (eg. SCHEMA.TABLENAME)
+ if(my($schema, $table) = $source->name =~ /(\w+)\.(\w+)/) {
+ $sql = q{
+ SELECT trigger_body FROM ALL_TRIGGERS t
+ WHERE t.owner = ? AND t.table_name = ?
+ AND t.triggering_event = 'INSERT'
+ AND t.status = 'ENABLED'
+ };
+ $sth = $dbh->prepare($sql);
+ $sth->execute( uc($schema), uc($table) );
+ } else {
+ $sth = $dbh->prepare($sql);
$sth->execute( uc($source->name) );
+ }
while (my ($insert_trigger) = $sth->fetchrow_array) {
return uc($1) if $insert_trigger =~ m!(\w+)\.nextval!i; # col name goes here???
}
diff -Nurb DBIx-Class-0.08099_04/MANIFEST DBIx-Class-0.08099_04_PATCHED/MANIFEST
--- DBIx-Class-0.08099_04/MANIFEST 2008-07-24 12:00:42.000000000 +0200
+++ DBIx-Class-0.08099_04_PATCHED/MANIFEST 2008-08-06 16:40:31.000000000 +0200
@@ -301,6 +301,7 @@
t/lib/DBICTest/ResultSetManager/Foo.pm
t/lib/DBICTest/Schema.pm
t/lib/DBICTest/Schema/Artist.pm
+t/lib/DBICTest/Schema/ArtistFQN.pm
t/lib/DBICTest/Schema/ArtistSourceName.pm
t/lib/DBICTest/Schema/ArtistSubclass.pm
t/lib/DBICTest/Schema/ArtistUndirectedMap.pm
diff -Nurb DBIx-Class-0.08099_04/t/73oracle.t DBIx-Class-0.08099_04_PATCHED/t/73oracle.t
--- DBIx-Class-0.08099_04/t/73oracle.t 2008-07-24 00:22:30.000000000 +0200
+++ DBIx-Class-0.08099_04_PATCHED/t/73oracle.t 2008-08-06 16:35:21.000000000 +0200
@@ -12,7 +12,7 @@
' as well as following sequences: \'pkid1_seq\', \'pkid2_seq\' and \'nonpkid_seq\''
unless ($dsn && $user && $pass);
-plan tests => 23;
+plan tests => 24;
my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
@@ -62,6 +62,10 @@
my $new = $schema->resultset('Artist')->create({ name => 'foo' });
is($new->artistid, 1, "Oracle Auto-PK worked");
+# test again with fully-qualified table name
+$new = $schema->resultset('ArtistFQN')->create({ name => 'bar' });
+is($new->artistid, 2, "Oracle Auto-PK worked with fully-qualified tablename");
+
# test join with row count ambiguity
my $cd = $schema->resultset('CD')->create({ cdid => 1, artist => 1, title => 'EP C', year => '2003' });
my $track = $schema->resultset('Track')->create({ trackid => 1, cd => 1, position => 1, title => 'Track1' });
@@ -90,7 +94,7 @@
}
my $it = $schema->resultset('Artist')->search( {},
{ rows => 3,
- offset => 2,
+ offset => 3,
order_by => 'artistid' }
);
is( $it->count, 3, "LIMIT count ok" );
diff -Nurb DBIx-Class-0.08099_04/t/lib/DBICTest/Schema/ArtistFQN.pm DBIx-Class-0.08099_04_PATCHED/t/lib/DBICTest/Schema/ArtistFQN.pm
--- DBIx-Class-0.08099_04/t/lib/DBICTest/Schema/ArtistFQN.pm 1970-01-01 01:00:00.000000000 +0100
+++ DBIx-Class-0.08099_04_PATCHED/t/lib/DBICTest/Schema/ArtistFQN.pm 2008-08-06 16:42:16.000000000 +0200
@@ -0,0 +1,24 @@
+package # hide from PAUSE
+ DBICTest::Schema::ArtistFQN;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table(
+ defined $ENV{DBICTEST_ORA_USER}
+ ? $ENV{DBICTEST_ORA_USER} .'.artist'
+ : 'artist'
+);
+__PACKAGE__->add_columns(
+ 'artistid' => {
+ data_type => 'integer',
+ is_auto_increment => 1,
+ },
+ 'name' => {
+ data_type => 'varchar',
+ size => 100,
+ is_nullable => 1,
+ },
+);
+__PACKAGE__->set_primary_key('artistid');
+
+1;
diff -Nurb DBIx-Class-0.08099_04/t/lib/DBICTest/Schema.pm DBIx-Class-0.08099_04_PATCHED/t/lib/DBICTest/Schema.pm
--- DBIx-Class-0.08099_04/t/lib/DBICTest/Schema.pm 2008-07-24 00:22:34.000000000 +0200
+++ DBIx-Class-0.08099_04_PATCHED/t/lib/DBICTest/Schema.pm 2008-08-06 16:23:31.000000000 +0200
@@ -7,6 +7,7 @@
__PACKAGE__->load_classes(qw/
Artist
+ ArtistFQN
SequenceTest
Employee
CD