summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authormkanat%bugzilla.org <>2008-08-01 07:14:43 +0200
committermkanat%bugzilla.org <>2008-08-01 07:14:43 +0200
commitd165b7785df760352ce17d3677691c5be0b3b6c4 (patch)
treeffcb0a48a48d77648668e5088e83e0d59076cb2c
parent8cf4ef03913d7866655f40eab3dff80f1559ac5b (diff)
downloadbugzilla-d165b7785df760352ce17d3677691c5be0b3b6c4.tar.gz
bugzilla-d165b7785df760352ce17d3677691c5be0b3b6c4.tar.xz
Bug 448241: [Oracle] Foreign keys: UPDATE CASCADE trigger updates wrong tables and fields
Patch By Xiaoou Wu <xiaoou.wu@oracle.com> r=mkanat, a=mkanat
-rw-r--r--Bugzilla/DB/Oracle.pm60
-rw-r--r--Bugzilla/DB/Schema/Oracle.pm8
2 files changed, 52 insertions, 16 deletions
diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm
index 1814dccdb..56d9d3fbf 100644
--- a/Bugzilla/DB/Oracle.pm
+++ b/Bugzilla/DB/Oracle.pm
@@ -454,22 +454,18 @@ sub quote_identifier {
sub bz_table_columns_real {
my ($self, $table) = @_;
$table = uc($table);
- my @cols = $self->SUPER::bz_table_columns_real($table);
- return map { lc($_) } @cols;
+ my $cols = $self->selectcol_arrayref(
+ "SELECT LOWER(COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE
+ TABLE_NAME = ? ORDER BY COLUMN_NAME", undef, $table);
+ return @$cols;
}
sub bz_table_list_real {
my ($self) = @_;
- # Oracle only accepts the username in uppercase.
- my $db_user = uc(Bugzilla->localconfig->{db_user});
- my $table_sth = $self->table_info(undef, $db_user, undef, "TABLE");
- my @tables = @{$self->selectcol_arrayref($table_sth, { Columns => [3] })};
- # Oracle returns uppercase table names, but Bugzilla expects lowercase
- # names.
- @tables = map { lc($_) } @tables;
- # Oracle has certain tables that start with DR$_IDX.
- @tables = grep { $_ !~ /^dr\$/ } @tables;
- return @tables;
+ my $tables = $self->selectcol_arrayref(
+ "SELECT LOWER(TABLE_NAME) FROM USER_TABLES WHERE
+ TABLE_NAME NOT LIKE ? ORDER BY TABLE_NAME", undef, 'DR$%');
+ return @$tables;
}
#####################################################################
@@ -495,6 +491,46 @@ sub bz_setup_database {
}
$self->SUPER::bz_setup_database(@_);
+
+ my @tables = $self->bz_table_list_real();
+ foreach my $table (@tables) {
+ my @columns = $self->bz_table_columns_real($table);
+ foreach my $column (@columns) {
+ my $def = $self->bz_column_info($table, $column);
+ if ($def->{REFERENCES}) {
+ my $references = $def->{REFERENCES};
+ my $update = $references->{UPDATE} || 'CASCADE';
+ my $to_table = $references->{TABLE};
+ my $to_column = $references->{COLUMN};
+ my $fk_name = $self->_bz_schema->_get_fk_name($table,
+ $column,
+ $references);
+ if ( $update =~ /CASCADE/i ){
+ my $trigger_name = uc($fk_name . "_UC");
+ my $exist_trigger = $self->selectcol_arrayref(
+ "SELECT OBJECT_NAME FROM USER_OBJECTS
+ WHERE OBJECT_NAME = ?", undef, $trigger_name);
+ if(@$exist_trigger) {
+ $self->do("DROP TRIGGER $trigger_name");
+ }
+
+ my $tr_str = "CREATE OR REPLACE TRIGGER $trigger_name"
+ . " AFTER UPDATE ON ". $to_table
+ . " REFERENCING "
+ . " NEW AS NEW "
+ . " OLD AS OLD "
+ . " FOR EACH ROW "
+ . " BEGIN "
+ . " UPDATE $table"
+ . " SET $column = :NEW.$to_column"
+ . " WHERE $column = :OLD.$to_column;"
+ . " END $trigger_name;";
+ $self->do($tr_str);
+ }
+ }
+ }
+ }
+
}
package Bugzilla::DB::Oracle::st;
diff --git a/Bugzilla/DB/Schema/Oracle.pm b/Bugzilla/DB/Schema/Oracle.pm
index 05c2bbeb3..ba3870a76 100644
--- a/Bugzilla/DB/Schema/Oracle.pm
+++ b/Bugzilla/DB/Schema/Oracle.pm
@@ -153,15 +153,15 @@ sub get_fk_ddl {
if ( $update =~ /CASCADE/i ){
my $tr_str = "CREATE OR REPLACE TRIGGER ${fk_name}_UC"
- . " AFTER UPDATE ON ". $table
+ . " AFTER UPDATE ON ". $to_table
. " REFERENCING "
. " NEW AS NEW "
. " OLD AS OLD "
. " FOR EACH ROW "
. " BEGIN "
- . " UPDATE $to_table"
- . " SET $to_column = :NEW.$column"
- . " WHERE $to_column = :OLD.$column;"
+ . " UPDATE $table"
+ . " SET $column = :NEW.$to_column"
+ . " WHERE $column = :OLD.$to_column;"
. " END ${fk_name}_UC;";
my $dbh = Bugzilla->dbh;
$dbh->do($tr_str);