| Subject: | Oracle autoincrement broken for Resultsets with scalar refs |
I'm not sure why it should be possible to pass a scalar reference as
table name. I couldn't find any documentation about this, but
DBICTest::Schema::CD uses a scalar ref, and the comment says it should
not be removed.
Using DBICTest::Schema::CD with the autoincrement feature of Oracle does
not work. It just dies with the message:
Unable to find a sequence INSERT trigger on table 'SCALAR(0x8f96218)'.
I've attached a patch which shows the problem in the test suite, and
fixes it by handing the scalar ref in the orcale specific storage
implementation.
| Subject: | dbic-oracle-autoinc.patch |
diff -Nurb DBIx-Class-0.08112-orig/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm DBIx-Class-0.08112-autoinc/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm
--- DBIx-Class-0.08112-orig/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm 2009-09-21 00:05:24.000000000 +0200
+++ DBIx-Class-0.08112-autoinc/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm 2009-10-27 15:57:31.000000000 +0100
@@ -53,8 +53,16 @@
my $sth;
+ my $source_name;
+ if ( ref $source->name ne 'SCALAR' ) {
+ $source_name = $source->name;
+ }
+ else {
+ $source_name = ${$source->name};
+ }
+
# check for fully-qualified name (eg. SCHEMA.TABLENAME)
- if ( my ( $schema, $table ) = $source->name =~ /(\w+)\.(\w+)/ ) {
+ if ( my ( $schema, $table ) = $source_name =~ /(\w+)\.(\w+)/ ) {
$sql = q{
SELECT trigger_body FROM ALL_TRIGGERS t
WHERE t.owner = ? AND t.table_name = ?
@@ -66,7 +74,7 @@
}
else {
$sth = $dbh->prepare($sql);
- $sth->execute( uc( $source->name ) );
+ $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.08112-orig/t/73oracle.t DBIx-Class-0.08112-autoinc/t/73oracle.t
--- DBIx-Class-0.08112-orig/t/73oracle.t 2009-08-28 00:03:32.000000000 +0200
+++ DBIx-Class-0.08112-autoinc/t/73oracle.t 2009-10-27 15:49:15.000000000 +0100
@@ -40,7 +40,7 @@
' as well as following sequences: \'pkid1_seq\', \'pkid2_seq\' and \'nonpkid_seq\''
unless ($dsn && $user && $pass);
-plan tests => 35;
+plan tests => 36;
DBICTest::Schema->load_classes('ArtistFQN');
my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
@@ -49,6 +49,7 @@
eval {
$dbh->do("DROP SEQUENCE artist_seq");
+ $dbh->do("DROP SEQUENCE cd_seq");
$dbh->do("DROP SEQUENCE pkid1_seq");
$dbh->do("DROP SEQUENCE pkid2_seq");
$dbh->do("DROP SEQUENCE nonpkid_seq");
@@ -58,6 +59,7 @@
$dbh->do("DROP TABLE track");
};
$dbh->do("CREATE SEQUENCE artist_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
+$dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
$dbh->do("CREATE SEQUENCE pkid1_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
$dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0");
$dbh->do("CREATE SEQUENCE nonpkid_seq START WITH 20 MAXVALUE 999999 MINVALUE 0");
@@ -67,6 +69,7 @@
$dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12), position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at DATE, small_dt DATE)");
$dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
+$dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");
$dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))");
$dbh->do(qq{
CREATE OR REPLACE TRIGGER artist_insert_trg
@@ -80,6 +83,18 @@
END IF;
END;
});
+$dbh->do(qq{
+ CREATE OR REPLACE TRIGGER cd_insert_trg
+ BEFORE INSERT ON cd
+ FOR EACH ROW
+ BEGIN
+ IF :new.cdid IS NULL THEN
+ SELECT cd_seq.nextval
+ INTO :new.cdid
+ FROM DUAL;
+ END IF;
+ END;
+});
{
# Swiped from t/bindtype_columns.t to avoid creating my own Resultset.
@@ -108,13 +123,15 @@
my $new = $schema->resultset('Artist')->create({ name => 'foo' });
is($new->artistid, 1, "Oracle Auto-PK worked");
+my $cd = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' });
+is($new->artistid, 1, "Oracle Auto-PK worked - using scalar ref as table name");
+
# 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' });
my $tjoin = $schema->resultset('Track')->search({ 'me.title' => 'Track1'},
@@ -212,6 +229,7 @@
END {
if($schema && ($dbh = $schema->storage->dbh)) {
$dbh->do("DROP SEQUENCE artist_seq");
+ $dbh->do("DROP SEQUENCE cd_seq");
$dbh->do("DROP SEQUENCE pkid1_seq");
$dbh->do("DROP SEQUENCE pkid2_seq");
$dbh->do("DROP SEQUENCE nonpkid_seq");