aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat (limited to 'Bugzilla/DB/Oracle.pm')
-rw-r--r--Bugzilla/DB/Oracle.pm1019
1 files changed, 524 insertions, 495 deletions
diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm
index 7424019ac..930270ccc 100644
--- a/Bugzilla/DB/Oracle.pm
+++ b/Bugzilla/DB/Oracle.pm
@@ -38,461 +38,473 @@ use Bugzilla::Util;
#####################################################################
# Constants
#####################################################################
-use constant EMPTY_STRING => '__BZ_EMPTY_STR__';
+use constant EMPTY_STRING => '__BZ_EMPTY_STR__';
use constant ISOLATION_LEVEL => 'READ COMMITTED';
-use constant BLOB_TYPE => { ora_type => ORA_BLOB };
+use constant BLOB_TYPE => {ora_type => ORA_BLOB};
+
# The max size allowed for LOB fields, in kilobytes.
use constant MIN_LONG_READ_LEN => 32 * 1024;
-use constant FULLTEXT_OR => ' OR ';
+use constant FULLTEXT_OR => ' OR ';
sub new {
- my ($class, $params) = @_;
- my ($user, $pass, $host, $dbname, $port) =
- @$params{qw(db_user db_pass db_host db_name db_port)};
-
- # You can never connect to Oracle without a DB name,
- # and there is no default DB.
- $dbname ||= Bugzilla->localconfig->{db_name};
-
- # Set the language enviroment
- $ENV{'NLS_LANG'} = '.AL32UTF8' if Bugzilla->params->{'utf8'};
-
- # construct the DSN from the parameters we got
- my $dsn = "dbi:Oracle:host=$host;sid=$dbname";
- $dsn .= ";port=$port" if $port;
- my $attrs = { FetchHashKeyName => 'NAME_lc',
- LongReadLen => max(Bugzilla->params->{'maxattachmentsize'} || 0,
- MIN_LONG_READ_LEN) * 1024,
- };
- my $self = $class->db_new({ dsn => $dsn, user => $user,
- pass => $pass, attrs => $attrs });
- # Needed by TheSchwartz
- $self->{private_bz_dsn} = $dsn;
-
- bless ($self, $class);
-
- # Set the session's default date format to match MySQL
- $self->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
- $self->do("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'");
- $self->do("ALTER SESSION SET NLS_LENGTH_SEMANTICS='CHAR'")
- if Bugzilla->params->{'utf8'};
- # To allow case insensitive query.
- $self->do("ALTER SESSION SET NLS_COMP='ANSI'");
- $self->do("ALTER SESSION SET NLS_SORT='BINARY_AI'");
- return $self;
+ my ($class, $params) = @_;
+ my ($user, $pass, $host, $dbname, $port)
+ = @$params{qw(db_user db_pass db_host db_name db_port)};
+
+ # You can never connect to Oracle without a DB name,
+ # and there is no default DB.
+ $dbname ||= Bugzilla->localconfig->{db_name};
+
+ # Set the language enviroment
+ $ENV{'NLS_LANG'} = '.AL32UTF8' if Bugzilla->params->{'utf8'};
+
+ # construct the DSN from the parameters we got
+ my $dsn = "dbi:Oracle:host=$host;sid=$dbname";
+ $dsn .= ";port=$port" if $port;
+ my $attrs = {
+ FetchHashKeyName => 'NAME_lc',
+ LongReadLen =>
+ max(Bugzilla->params->{'maxattachmentsize'} || 0, MIN_LONG_READ_LEN) * 1024,
+ };
+ my $self = $class->db_new(
+ {dsn => $dsn, user => $user, pass => $pass, attrs => $attrs});
+
+ # Needed by TheSchwartz
+ $self->{private_bz_dsn} = $dsn;
+
+ bless($self, $class);
+
+ # Set the session's default date format to match MySQL
+ $self->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
+ $self->do("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'");
+ $self->do("ALTER SESSION SET NLS_LENGTH_SEMANTICS='CHAR'")
+ if Bugzilla->params->{'utf8'};
+
+ # To allow case insensitive query.
+ $self->do("ALTER SESSION SET NLS_COMP='ANSI'");
+ $self->do("ALTER SESSION SET NLS_SORT='BINARY_AI'");
+ return $self;
}
sub bz_last_key {
- my ($self, $table, $column) = @_;
+ my ($self, $table, $column) = @_;
- my $seq = $table . "_" . $column . "_SEQ";
- my ($last_insert_id) = $self->selectrow_array("SELECT $seq.CURRVAL "
- . " FROM DUAL");
- return $last_insert_id;
+ my $seq = $table . "_" . $column . "_SEQ";
+ my ($last_insert_id)
+ = $self->selectrow_array("SELECT $seq.CURRVAL " . " FROM DUAL");
+ return $last_insert_id;
}
sub bz_check_regexp {
- my ($self, $pattern) = @_;
+ my ($self, $pattern) = @_;
- eval { $self->do("SELECT 1 FROM DUAL WHERE "
- . $self->sql_regexp($self->quote("a"), $pattern, 1)) };
+ eval {
+ $self->do("SELECT 1 FROM DUAL WHERE "
+ . $self->sql_regexp($self->quote("a"), $pattern, 1));
+ };
- $@ && ThrowUserError('illegal_regexp',
- { value => $pattern, dberror => $self->errstr });
+ $@
+ && ThrowUserError('illegal_regexp',
+ {value => $pattern, dberror => $self->errstr});
}
-sub bz_explain {
- my ($self, $sql) = @_;
- my $sth = $self->prepare("EXPLAIN PLAN FOR $sql");
- $sth->execute();
- my $explain = $self->selectcol_arrayref(
- "SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY)");
- return join("\n", @$explain);
-}
+sub bz_explain {
+ my ($self, $sql) = @_;
+ my $sth = $self->prepare("EXPLAIN PLAN FOR $sql");
+ $sth->execute();
+ my $explain = $self->selectcol_arrayref(
+ "SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY)");
+ return join("\n", @$explain);
+}
sub sql_group_concat {
- my ($self, $text, $separator) = @_;
- $separator = $self->quote(', ') if !defined $separator;
- my ($distinct, $rest) = $text =~/^(\s*DISTINCT\s|)(.+)$/i;
- return "group_concat($distinct T_CLOB_DELIM(NVL($rest, ' '), $separator))";
+ my ($self, $text, $separator) = @_;
+ $separator = $self->quote(', ') if !defined $separator;
+ my ($distinct, $rest) = $text =~ /^(\s*DISTINCT\s|)(.+)$/i;
+ return "group_concat($distinct T_CLOB_DELIM(NVL($rest, ' '), $separator))";
}
sub sql_regexp {
- my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_;
- $real_pattern ||= $pattern;
+ my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_;
+ $real_pattern ||= $pattern;
- $self->bz_check_regexp($real_pattern) if !$nocheck;
+ $self->bz_check_regexp($real_pattern) if !$nocheck;
- return "REGEXP_LIKE($expr, $pattern)";
+ return "REGEXP_LIKE($expr, $pattern)";
}
sub sql_not_regexp {
- my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_;
- $real_pattern ||= $pattern;
+ my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_;
+ $real_pattern ||= $pattern;
- $self->bz_check_regexp($real_pattern) if !$nocheck;
+ $self->bz_check_regexp($real_pattern) if !$nocheck;
- return "NOT REGEXP_LIKE($expr, $pattern)"
+ return "NOT REGEXP_LIKE($expr, $pattern)";
}
sub sql_limit {
- my ($self, $limit, $offset) = @_;
+ my ($self, $limit, $offset) = @_;
- if(defined $offset) {
- return "/* LIMIT $limit $offset */";
- }
- return "/* LIMIT $limit */";
+ if (defined $offset) {
+ return "/* LIMIT $limit $offset */";
+ }
+ return "/* LIMIT $limit */";
}
sub sql_string_concat {
- my ($self, @params) = @_;
+ my ($self, @params) = @_;
- return 'CONCAT(' . join(', ', @params) . ')';
+ return 'CONCAT(' . join(', ', @params) . ')';
}
sub sql_to_days {
- my ($self, $date) = @_;
+ my ($self, $date) = @_;
- return " TO_CHAR(TO_DATE($date),'J') ";
+ return " TO_CHAR(TO_DATE($date),'J') ";
}
-sub sql_from_days{
- my ($self, $date) = @_;
- return " TO_DATE($date,'J') ";
+sub sql_from_days {
+ my ($self, $date) = @_;
+
+ return " TO_DATE($date,'J') ";
}
sub sql_fulltext_search {
- my ($self, $column, $text) = @_;
- state $label = 0;
- $text = $self->quote($text);
- trick_taint($text);
- $label++;
- return "CONTAINS($column,$text,$label) > 0", "SCORE($label)";
+ my ($self, $column, $text) = @_;
+ state $label = 0;
+ $text = $self->quote($text);
+ trick_taint($text);
+ $label++;
+ return "CONTAINS($column,$text,$label) > 0", "SCORE($label)";
}
sub sql_date_format {
- my ($self, $date, $format) = @_;
-
- $format = "%Y.%m.%d %H:%i:%s" if !$format;
+ my ($self, $date, $format) = @_;
+
+ $format = "%Y.%m.%d %H:%i:%s" if !$format;
- $format =~ s/\%Y/YYYY/g;
- $format =~ s/\%y/YY/g;
- $format =~ s/\%m/MM/g;
- $format =~ s/\%d/DD/g;
- $format =~ s/\%a/Dy/g;
- $format =~ s/\%H/HH24/g;
- $format =~ s/\%i/MI/g;
- $format =~ s/\%s/SS/g;
+ $format =~ s/\%Y/YYYY/g;
+ $format =~ s/\%y/YY/g;
+ $format =~ s/\%m/MM/g;
+ $format =~ s/\%d/DD/g;
+ $format =~ s/\%a/Dy/g;
+ $format =~ s/\%H/HH24/g;
+ $format =~ s/\%i/MI/g;
+ $format =~ s/\%s/SS/g;
- return "TO_CHAR($date, " . $self->quote($format) . ")";
+ return "TO_CHAR($date, " . $self->quote($format) . ")";
}
sub sql_date_math {
- my ($self, $date, $operator, $interval, $units) = @_;
- my $time_sql;
- if ($units =~ /YEAR|MONTH/i) {
- $time_sql = "NUMTOYMINTERVAL($interval,'$units')";
- } else{
- $time_sql = "NUMTODSINTERVAL($interval,'$units')";
- }
- return "$date $operator $time_sql";
+ my ($self, $date, $operator, $interval, $units) = @_;
+ my $time_sql;
+ if ($units =~ /YEAR|MONTH/i) {
+ $time_sql = "NUMTOYMINTERVAL($interval,'$units')";
+ }
+ else {
+ $time_sql = "NUMTODSINTERVAL($interval,'$units')";
+ }
+ return "$date $operator $time_sql";
}
sub sql_position {
- my ($self, $fragment, $text) = @_;
- return "INSTR($text, $fragment)";
+ my ($self, $fragment, $text) = @_;
+ return "INSTR($text, $fragment)";
}
sub sql_in {
- my ($self, $column_name, $in_list_ref, $negate) = @_;
- my @in_list = @$in_list_ref;
- return $self->SUPER::sql_in($column_name, $in_list_ref, $negate) if $#in_list < 1000;
- my @in_str;
- while (@in_list) {
- my $length = $#in_list + 1;
- my $splice = $length > 1000 ? 1000 : $length;
- my @sub_in_list = splice(@in_list, 0, $splice);
- push(@in_str,
- $self->SUPER::sql_in($column_name, \@sub_in_list, $negate));
- }
- return "( " . join(" OR ", @in_str) . " )";
+ my ($self, $column_name, $in_list_ref, $negate) = @_;
+ my @in_list = @$in_list_ref;
+ return $self->SUPER::sql_in($column_name, $in_list_ref, $negate)
+ if $#in_list < 1000;
+ my @in_str;
+ while (@in_list) {
+ my $length = $#in_list + 1;
+ my $splice = $length > 1000 ? 1000 : $length;
+ my @sub_in_list = splice(@in_list, 0, $splice);
+ push(@in_str, $self->SUPER::sql_in($column_name, \@sub_in_list, $negate));
+ }
+ return "( " . join(" OR ", @in_str) . " )";
}
sub _bz_add_field_table {
- my ($self, $name, $schema_ref, $type) = @_;
- $self->SUPER::_bz_add_field_table($name, $schema_ref);
- if (defined($type) && $type == FIELD_TYPE_MULTI_SELECT) {
- my $uk_name = "UK_" . $self->_bz_schema->_hash_identifier($name . '_value');
- $self->do("ALTER TABLE $name ADD CONSTRAINT $uk_name UNIQUE(value)");
- }
+ my ($self, $name, $schema_ref, $type) = @_;
+ $self->SUPER::_bz_add_field_table($name, $schema_ref);
+ if (defined($type) && $type == FIELD_TYPE_MULTI_SELECT) {
+ my $uk_name = "UK_" . $self->_bz_schema->_hash_identifier($name . '_value');
+ $self->do("ALTER TABLE $name ADD CONSTRAINT $uk_name UNIQUE(value)");
+ }
}
sub bz_drop_table {
- my ($self, $name) = @_;
- my $table_exists = $self->bz_table_info($name);
- if ($table_exists) {
- $self->_bz_drop_fks($name);
- $self->SUPER::bz_drop_table($name);
- }
+ my ($self, $name) = @_;
+ my $table_exists = $self->bz_table_info($name);
+ if ($table_exists) {
+ $self->_bz_drop_fks($name);
+ $self->SUPER::bz_drop_table($name);
+ }
}
-# Dropping all FKs for a specified table.
+# Dropping all FKs for a specified table.
sub _bz_drop_fks {
- my ($self, $table) = @_;
- my @columns = $self->bz_table_columns($table);
- foreach my $column (@columns) {
- $self->bz_drop_fk($table, $column);
- }
+ my ($self, $table) = @_;
+ my @columns = $self->bz_table_columns($table);
+ foreach my $column (@columns) {
+ $self->bz_drop_fk($table, $column);
+ }
}
sub _fix_empty {
- my ($string) = @_;
- $string = '' if $string eq EMPTY_STRING;
- return $string;
+ my ($string) = @_;
+ $string = '' if $string eq EMPTY_STRING;
+ return $string;
}
sub _fix_arrayref {
- my ($row) = @_;
- return undef if !defined $row;
- foreach my $field (@$row) {
- $field = _fix_empty($field) if defined $field;
- }
- return $row;
+ my ($row) = @_;
+ return undef if !defined $row;
+ foreach my $field (@$row) {
+ $field = _fix_empty($field) if defined $field;
+ }
+ return $row;
}
sub _fix_hashref {
- my ($row) = @_;
- return undef if !defined $row;
- foreach my $value (values %$row) {
- $value = _fix_empty($value) if defined $value;
- }
- return $row;
+ my ($row) = @_;
+ return undef if !defined $row;
+ foreach my $value (values %$row) {
+ $value = _fix_empty($value) if defined $value;
+ }
+ return $row;
}
sub adjust_statement {
- my ($sql) = @_;
-
- if ($sql =~ /^CREATE OR REPLACE.*/i){
- return $sql;
- }
-
- # We can't just assume any occurrence of "''" in $sql is an empty
- # string, since "''" can occur inside a string literal as a way of
- # escaping a single "'" in the literal. Therefore we must be trickier...
-
- # split the statement into parts by single-quotes. The negative value
- # at the end to the split operator from dropping trailing empty strings
- # (e.g., when $sql ends in "''")
- my @parts = split /'/, $sql, -1;
-
- if( !(@parts % 2) ) {
- # Either the string is empty or the quotes are mismatched
- # Returning input unmodified.
- return $sql;
+ my ($sql) = @_;
+
+ if ($sql =~ /^CREATE OR REPLACE.*/i) {
+ return $sql;
+ }
+
+ # We can't just assume any occurrence of "''" in $sql is an empty
+ # string, since "''" can occur inside a string literal as a way of
+ # escaping a single "'" in the literal. Therefore we must be trickier...
+
+ # split the statement into parts by single-quotes. The negative value
+ # at the end to the split operator from dropping trailing empty strings
+ # (e.g., when $sql ends in "''")
+ my @parts = split /'/, $sql, -1;
+
+ if (!(@parts % 2)) {
+
+ # Either the string is empty or the quotes are mismatched
+ # Returning input unmodified.
+ return $sql;
+ }
+
+ # We already verified that we have an odd number of parts. If we take
+ # the first part off now, we know we're entering the loop with an even
+ # number of parts
+ my @result;
+ my $part = shift @parts;
+
+ # Oracle requires a FROM clause in all SELECT statements, so append
+ # "FROM dual" to queries without one (e.g., "SELECT NOW()")
+ my $is_select = ($part =~ m/^\s*SELECT\b/io);
+ my $has_from = ($part =~ m/\bFROM\b/io) if $is_select;
+
+ # Oracle includes the time in CURRENT_DATE.
+ $part =~ s/\bCURRENT_DATE\b/TRUNC(CURRENT_DATE)/io;
+
+ # Oracle use SUBSTR instead of SUBSTRING
+ $part =~ s/\bSUBSTRING\b/SUBSTR/io;
+
+ # Oracle need no 'AS'
+ $part =~ s/\bAS\b//ig;
+
+ # Oracle doesn't have LIMIT, so if we find the LIMIT comment, wrap the
+ # query with "SELECT * FROM (...) WHERE rownum < $limit"
+ my ($limit, $offset) = ($part =~ m{/\* LIMIT (\d*) (\d*) \*/}o);
+
+ push @result, $part;
+ while (@parts) {
+ my $string = shift @parts;
+ my $nonstring = shift @parts;
+
+ # if the non-string part is zero-length and there are more parts left,
+ # then this is an escaped quote inside a string literal
+ while (!(length $nonstring) && @parts) {
+
+ # we know it's safe to remove two parts at a time, since we
+ # entered the loop with an even number of parts
+ $string .= "''" . shift @parts;
+ $nonstring = shift @parts;
}
- # We already verified that we have an odd number of parts. If we take
- # the first part off now, we know we're entering the loop with an even
- # number of parts
- my @result;
- my $part = shift @parts;
-
- # Oracle requires a FROM clause in all SELECT statements, so append
- # "FROM dual" to queries without one (e.g., "SELECT NOW()")
- my $is_select = ($part =~ m/^\s*SELECT\b/io);
- my $has_from = ($part =~ m/\bFROM\b/io) if $is_select;
+ # Look for a FROM if this is a SELECT and we haven't found one yet
+ $has_from = ($nonstring =~ m/\bFROM\b/io) if ($is_select and !$has_from);
# Oracle includes the time in CURRENT_DATE.
- $part =~ s/\bCURRENT_DATE\b/TRUNC(CURRENT_DATE)/io;
+ $nonstring =~ s/\bCURRENT_DATE\b/TRUNC(CURRENT_DATE)/io;
# Oracle use SUBSTR instead of SUBSTRING
- $part =~ s/\bSUBSTRING\b/SUBSTR/io;
-
+ $nonstring =~ s/\bSUBSTRING\b/SUBSTR/io;
+
# Oracle need no 'AS'
- $part =~ s/\bAS\b//ig;
-
- # Oracle doesn't have LIMIT, so if we find the LIMIT comment, wrap the
- # query with "SELECT * FROM (...) WHERE rownum < $limit"
- my ($limit,$offset) = ($part =~ m{/\* LIMIT (\d*) (\d*) \*/}o);
-
- push @result, $part;
- while( @parts ) {
- my $string = shift @parts;
- my $nonstring = shift @parts;
-
- # if the non-string part is zero-length and there are more parts left,
- # then this is an escaped quote inside a string literal
- while( !(length $nonstring) && @parts ) {
- # we know it's safe to remove two parts at a time, since we
- # entered the loop with an even number of parts
- $string .= "''" . shift @parts;
- $nonstring = shift @parts;
- }
+ $nonstring =~ s/\bAS\b//ig;
- # Look for a FROM if this is a SELECT and we haven't found one yet
- $has_from = ($nonstring =~ m/\bFROM\b/io)
- if ($is_select and !$has_from);
+ # Look for a LIMIT clause
+ ($limit) = ($nonstring =~ m(/\* LIMIT (\d*) \*/)o);
- # Oracle includes the time in CURRENT_DATE.
- $nonstring =~ s/\bCURRENT_DATE\b/TRUNC(CURRENT_DATE)/io;
+ if (!length($string)) {
+ push @result, EMPTY_STRING;
+ push @result, $nonstring;
+ }
+ else {
+ push @result, $string;
+ push @result, $nonstring;
+ }
+ }
- # Oracle use SUBSTR instead of SUBSTRING
- $nonstring =~ s/\bSUBSTRING\b/SUBSTR/io;
+ my $new_sql = join "'", @result;
- # Oracle need no 'AS'
- $nonstring =~ s/\bAS\b//ig;
+ # Append "FROM dual" if this is a SELECT without a FROM clause
+ $new_sql .= " FROM DUAL" if ($is_select and !$has_from);
- # Look for a LIMIT clause
- ($limit) = ($nonstring =~ m(/\* LIMIT (\d*) \*/)o);
+ # Wrap the query with a "WHERE rownum <= ..." if we found LIMIT
- if(!length($string)){
- push @result, EMPTY_STRING;
- push @result, $nonstring;
- } else {
- push @result, $string;
- push @result, $nonstring;
- }
+ if (defined($limit)) {
+ if ($new_sql !~ /\bWHERE\b/) {
+ $new_sql = $new_sql . " WHERE 1=1";
}
-
- my $new_sql = join "'", @result;
-
- # Append "FROM dual" if this is a SELECT without a FROM clause
- $new_sql .= " FROM DUAL" if ($is_select and !$has_from);
-
- # Wrap the query with a "WHERE rownum <= ..." if we found LIMIT
-
- if (defined($limit)) {
- if ($new_sql !~ /\bWHERE\b/) {
- $new_sql = $new_sql." WHERE 1=1";
- }
- my ($before_where, $after_where) = split(/\bWHERE\b/i, $new_sql, 2);
- if (defined($offset)) {
- my ($before_from, $after_from) = split(/\bFROM\b/i, $new_sql, 2);
- $before_where = "$before_from FROM ($before_from,"
- . " ROW_NUMBER() OVER (ORDER BY 1) R "
- . " FROM $after_from ) ";
- $after_where = " R BETWEEN $offset+1 AND $limit+$offset";
- } else {
- $after_where = " rownum <=$limit AND ".$after_where;
- }
- $new_sql = $before_where." WHERE ".$after_where;
+ my ($before_where, $after_where) = split(/\bWHERE\b/i, $new_sql, 2);
+ if (defined($offset)) {
+ my ($before_from, $after_from) = split(/\bFROM\b/i, $new_sql, 2);
+ $before_where
+ = "$before_from FROM ($before_from,"
+ . " ROW_NUMBER() OVER (ORDER BY 1) R "
+ . " FROM $after_from ) ";
+ $after_where = " R BETWEEN $offset+1 AND $limit+$offset";
+ }
+ else {
+ $after_where = " rownum <=$limit AND " . $after_where;
}
- return $new_sql;
+ $new_sql = $before_where . " WHERE " . $after_where;
+ }
+ return $new_sql;
}
sub do {
- my $self = shift;
- my $sql = shift;
- $sql = adjust_statement($sql);
- unshift @_, $sql;
- return $self->SUPER::do(@_);
+ my $self = shift;
+ my $sql = shift;
+ $sql = adjust_statement($sql);
+ unshift @_, $sql;
+ return $self->SUPER::do(@_);
}
sub selectrow_array {
- my $self = shift;
- my $stmt = shift;
- my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
- unshift @_, $new_stmt;
- if ( wantarray ) {
- my @row = $self->SUPER::selectrow_array(@_);
- _fix_arrayref(\@row);
- return @row;
- } else {
- my $row = $self->SUPER::selectrow_array(@_);
- $row = _fix_empty($row) if defined $row;
- return $row;
- }
+ my $self = shift;
+ my $stmt = shift;
+ my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
+ unshift @_, $new_stmt;
+ if (wantarray) {
+ my @row = $self->SUPER::selectrow_array(@_);
+ _fix_arrayref(\@row);
+ return @row;
+ }
+ else {
+ my $row = $self->SUPER::selectrow_array(@_);
+ $row = _fix_empty($row) if defined $row;
+ return $row;
+ }
}
sub selectrow_arrayref {
- my $self = shift;
- my $stmt = shift;
- my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
- unshift @_, $new_stmt;
- my $ref = $self->SUPER::selectrow_arrayref(@_);
- return undef if !defined $ref;
+ my $self = shift;
+ my $stmt = shift;
+ my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
+ unshift @_, $new_stmt;
+ my $ref = $self->SUPER::selectrow_arrayref(@_);
+ return undef if !defined $ref;
- _fix_arrayref($ref);
- return $ref;
+ _fix_arrayref($ref);
+ return $ref;
}
sub selectrow_hashref {
- my $self = shift;
- my $stmt = shift;
- my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
- unshift @_, $new_stmt;
- my $ref = $self->SUPER::selectrow_hashref(@_);
- return undef if !defined $ref;
+ my $self = shift;
+ my $stmt = shift;
+ my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
+ unshift @_, $new_stmt;
+ my $ref = $self->SUPER::selectrow_hashref(@_);
+ return undef if !defined $ref;
- _fix_hashref($ref);
- return $ref;
+ _fix_hashref($ref);
+ return $ref;
}
sub selectall_arrayref {
- my $self = shift;
- my $stmt = shift;
- my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
- unshift @_, $new_stmt;
- my $ref = $self->SUPER::selectall_arrayref(@_);
- return undef if !defined $ref;
-
- foreach my $row (@$ref) {
- if (ref($row) eq 'ARRAY') {
- _fix_arrayref($row);
- }
- elsif (ref($row) eq 'HASH') {
- _fix_hashref($row);
- }
+ my $self = shift;
+ my $stmt = shift;
+ my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
+ unshift @_, $new_stmt;
+ my $ref = $self->SUPER::selectall_arrayref(@_);
+ return undef if !defined $ref;
+
+ foreach my $row (@$ref) {
+ if (ref($row) eq 'ARRAY') {
+ _fix_arrayref($row);
}
+ elsif (ref($row) eq 'HASH') {
+ _fix_hashref($row);
+ }
+ }
- return $ref;
+ return $ref;
}
sub selectall_hashref {
- my $self = shift;
- my $stmt = shift;
- my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
- unshift @_, $new_stmt;
- my $rows = $self->SUPER::selectall_hashref(@_);
- return undef if !defined $rows;
- foreach my $row (values %$rows) {
- _fix_hashref($row);
- }
- return $rows;
+ my $self = shift;
+ my $stmt = shift;
+ my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
+ unshift @_, $new_stmt;
+ my $rows = $self->SUPER::selectall_hashref(@_);
+ return undef if !defined $rows;
+ foreach my $row (values %$rows) {
+ _fix_hashref($row);
+ }
+ return $rows;
}
sub selectcol_arrayref {
- my $self = shift;
- my $stmt = shift;
- my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
- unshift @_, $new_stmt;
- my $ref = $self->SUPER::selectcol_arrayref(@_);
- return undef if !defined $ref;
- _fix_arrayref($ref);
- return $ref;
+ my $self = shift;
+ my $stmt = shift;
+ my $new_stmt = (ref $stmt) ? $stmt : adjust_statement($stmt);
+ unshift @_, $new_stmt;
+ my $ref = $self->SUPER::selectcol_arrayref(@_);
+ return undef if !defined $ref;
+ _fix_arrayref($ref);
+ return $ref;
}
sub prepare {
- my $self = shift;
- my $sql = shift;
- my $new_sql = adjust_statement($sql);
- unshift @_, $new_sql;
- return bless $self->SUPER::prepare(@_),
- 'Bugzilla::DB::Oracle::st';
+ my $self = shift;
+ my $sql = shift;
+ my $new_sql = adjust_statement($sql);
+ unshift @_, $new_sql;
+ return bless $self->SUPER::prepare(@_), 'Bugzilla::DB::Oracle::st';
}
sub prepare_cached {
- my $self = shift;
- my $sql = shift;
- my $new_sql = adjust_statement($sql);
- unshift @_, $new_sql;
- return bless $self->SUPER::prepare_cached(@_),
- 'Bugzilla::DB::Oracle::st';
+ my $self = shift;
+ my $sql = shift;
+ my $new_sql = adjust_statement($sql);
+ unshift @_, $new_sql;
+ return bless $self->SUPER::prepare_cached(@_), 'Bugzilla::DB::Oracle::st';
}
sub quote_identifier {
- my ($self,$id) = @_;
- return $id;
+ my ($self, $id) = @_;
+ return $id;
}
#####################################################################
@@ -500,20 +512,22 @@ sub quote_identifier {
#####################################################################
sub bz_table_columns_real {
- my ($self, $table) = @_;
- $table = uc($table);
- my $cols = $self->selectcol_arrayref(
- "SELECT LOWER(COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE
- TABLE_NAME = ? ORDER BY COLUMN_NAME", undef, $table);
- return @$cols;
+ my ($self, $table) = @_;
+ $table = uc($table);
+ 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) = @_;
- 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;
+ my ($self) = @_;
+ 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;
}
#####################################################################
@@ -521,32 +535,37 @@ sub bz_table_list_real {
#####################################################################
sub bz_setup_database {
- my $self = shift;
-
- # Create a function that returns SYSDATE to emulate MySQL's "NOW()".
- # Function NOW() is used widely in Bugzilla SQLs, but Oracle does not
- # have that function, So we have to create one ourself.
- $self->do("CREATE OR REPLACE FUNCTION NOW "
- . " RETURN DATE IS BEGIN RETURN SYSDATE; END;");
- $self->do("CREATE OR REPLACE FUNCTION CHAR_LENGTH(COLUMN_NAME VARCHAR2)"
- . " RETURN NUMBER IS BEGIN RETURN LENGTH(COLUMN_NAME); END;");
-
- # Create types for group_concat
- my $type_exists = $self->selectrow_array("SELECT 1 FROM user_types
- WHERE type_name = 'T_GROUP_CONCAT'");
- $self->do("DROP TYPE T_GROUP_CONCAT") if $type_exists;
- $self->do("CREATE OR REPLACE TYPE T_CLOB_DELIM AS OBJECT "
- . "( p_CONTENT CLOB, p_DELIMITER VARCHAR2(256)"
- . ", MAP MEMBER FUNCTION T_CLOB_DELIM_ToVarchar return VARCHAR2"
- . ");");
- $self->do("CREATE OR REPLACE TYPE BODY T_CLOB_DELIM IS
+ my $self = shift;
+
+ # Create a function that returns SYSDATE to emulate MySQL's "NOW()".
+ # Function NOW() is used widely in Bugzilla SQLs, but Oracle does not
+ # have that function, So we have to create one ourself.
+ $self->do("CREATE OR REPLACE FUNCTION NOW "
+ . " RETURN DATE IS BEGIN RETURN SYSDATE; END;");
+ $self->do("CREATE OR REPLACE FUNCTION CHAR_LENGTH(COLUMN_NAME VARCHAR2)"
+ . " RETURN NUMBER IS BEGIN RETURN LENGTH(COLUMN_NAME); END;");
+
+ # Create types for group_concat
+ my $type_exists = $self->selectrow_array(
+ "SELECT 1 FROM user_types
+ WHERE type_name = 'T_GROUP_CONCAT'"
+ );
+ $self->do("DROP TYPE T_GROUP_CONCAT") if $type_exists;
+ $self->do("CREATE OR REPLACE TYPE T_CLOB_DELIM AS OBJECT "
+ . "( p_CONTENT CLOB, p_DELIMITER VARCHAR2(256)"
+ . ", MAP MEMBER FUNCTION T_CLOB_DELIM_ToVarchar return VARCHAR2"
+ . ");");
+ $self->do(
+ "CREATE OR REPLACE TYPE BODY T_CLOB_DELIM IS
MAP MEMBER FUNCTION T_CLOB_DELIM_ToVarchar return VARCHAR2 is
BEGIN
RETURN p_CONTENT;
END;
- END;");
+ END;"
+ );
- $self->do("CREATE OR REPLACE TYPE T_GROUP_CONCAT AS OBJECT
+ $self->do(
+ "CREATE OR REPLACE TYPE T_GROUP_CONCAT AS OBJECT
( CLOB_CONTENT CLOB,
DELIMITER VARCHAR2(256),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(
@@ -564,9 +583,11 @@ sub bz_setup_database {
MEMBER FUNCTION ODCIAGGREGATEMERGE(
SELF IN OUT NOCOPY T_GROUP_CONCAT,
CTX2 IN T_GROUP_CONCAT)
- RETURN NUMBER);");
+ RETURN NUMBER);"
+ );
- $self->do("CREATE OR REPLACE TYPE BODY T_GROUP_CONCAT IS
+ $self->do(
+ "CREATE OR REPLACE TYPE BODY T_GROUP_CONCAT IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(
SCTX IN OUT NOCOPY T_GROUP_CONCAT)
RETURN NUMBER IS
@@ -610,110 +631,117 @@ sub bz_setup_database {
DBMS_LOB.APPEND(SELF.CLOB_CONTENT, CTX2.CLOB_CONTENT);
RETURN ODCICONST.SUCCESS;
END;
- END;");
+ END;"
+ );
- # Create user-defined aggregate function group_concat
- $self->do("CREATE OR REPLACE FUNCTION GROUP_CONCAT(P_INPUT T_CLOB_DELIM)
+ # Create user-defined aggregate function group_concat
+ $self->do(
+ "CREATE OR REPLACE FUNCTION GROUP_CONCAT(P_INPUT T_CLOB_DELIM)
RETURN CLOB
- DETERMINISTIC PARALLEL_ENABLE AGGREGATE USING T_GROUP_CONCAT;");
-
- # Create a WORLD_LEXER named BZ_LEX for multilingual fulltext search
- my $lexer = $self->selectcol_arrayref(
- "SELECT pre_name FROM CTXSYS.CTX_PREFERENCES WHERE pre_name = ? AND
- pre_owner = ?",
- undef,'BZ_LEX',uc(Bugzilla->localconfig->{db_user}));
- if(!@$lexer) {
- $self->do("BEGIN CTX_DDL.CREATE_PREFERENCE
- ('BZ_LEX', 'WORLD_LEXER'); END;");
- }
-
- $self->SUPER::bz_setup_database(@_);
-
- my $sth = $self->prepare("SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_NAME = ?");
- 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);
- # bz_add_column() before Bugzilla 4.2.3 didn't handle primary keys
- # correctly (bug 731156). We have to add missing sequences and
- # triggers ourselves.
- if ($def->{TYPE} =~ /SERIAL/i) {
- my $sequence = "${table}_${column}_SEQ";
- my $exists = $self->selectrow_array($sth, undef, $sequence);
- if (!$exists) {
- my @sql = $self->_get_create_seq_ddl($table, $column);
- $self->do($_) foreach @sql;
- }
- }
-
- 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);
- # bz_rename_table didn't rename the trigger correctly.
- if ($table eq 'bug_tag' && $to_table eq 'tags') {
- $to_table = 'tag';
- }
- if ( $update =~ /CASCADE/i ){
- my $trigger_name = uc($fk_name . "_UC");
- my $exist_trigger = $self->selectcol_arrayref($sth, undef, $trigger_name);
- if(@$exist_trigger) {
- $self->do("DROP TRIGGER $trigger_name");
- }
-
- my $tr_str = "CREATE OR REPLACE TRIGGER $trigger_name"
- . " AFTER UPDATE OF $to_column 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);
- }
- }
+ DETERMINISTIC PARALLEL_ENABLE AGGREGATE USING T_GROUP_CONCAT;"
+ );
+
+ # Create a WORLD_LEXER named BZ_LEX for multilingual fulltext search
+ my $lexer = $self->selectcol_arrayref(
+ "SELECT pre_name FROM CTXSYS.CTX_PREFERENCES WHERE pre_name = ? AND
+ pre_owner = ?", undef, 'BZ_LEX', uc(Bugzilla->localconfig->{db_user})
+ );
+ if (!@$lexer) {
+ $self->do(
+ "BEGIN CTX_DDL.CREATE_PREFERENCE
+ ('BZ_LEX', 'WORLD_LEXER'); END;"
+ );
+ }
+
+ $self->SUPER::bz_setup_database(@_);
+
+ my $sth = $self->prepare(
+ "SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_NAME = ?");
+ 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);
+
+ # bz_add_column() before Bugzilla 4.2.3 didn't handle primary keys
+ # correctly (bug 731156). We have to add missing sequences and
+ # triggers ourselves.
+ if ($def->{TYPE} =~ /SERIAL/i) {
+ my $sequence = "${table}_${column}_SEQ";
+ my $exists = $self->selectrow_array($sth, undef, $sequence);
+ if (!$exists) {
+ my @sql = $self->_get_create_seq_ddl($table, $column);
+ $self->do($_) foreach @sql;
}
+ }
+
+ 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);
+
+ # bz_rename_table didn't rename the trigger correctly.
+ if ($table eq 'bug_tag' && $to_table eq 'tags') {
+ $to_table = 'tag';
+ }
+ if ($update =~ /CASCADE/i) {
+ my $trigger_name = uc($fk_name . "_UC");
+ my $exist_trigger = $self->selectcol_arrayref($sth, undef, $trigger_name);
+ if (@$exist_trigger) {
+ $self->do("DROP TRIGGER $trigger_name");
+ }
+
+ my $tr_str
+ = "CREATE OR REPLACE TRIGGER $trigger_name"
+ . " AFTER UPDATE OF $to_column 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);
+ }
+ }
}
+ }
- # Drop the trigger which causes bug 541553
- my $trigger_name = "PRODUCTS_MILESTONEURL";
- my $exist_trigger = $self->selectcol_arrayref($sth, undef, $trigger_name);
- if(@$exist_trigger) {
- $self->do("DROP TRIGGER $trigger_name");
- }
+ # Drop the trigger which causes bug 541553
+ my $trigger_name = "PRODUCTS_MILESTONEURL";
+ my $exist_trigger = $self->selectcol_arrayref($sth, undef, $trigger_name);
+ if (@$exist_trigger) {
+ $self->do("DROP TRIGGER $trigger_name");
+ }
}
# These two methods have been copied from Bugzilla::DB::Schema::Oracle.
sub _get_create_seq_ddl {
- my ($self, $table, $column) = @_;
+ my ($self, $table, $column) = @_;
- my $seq_name = "${table}_${column}_SEQ";
- my $seq_sql = "CREATE SEQUENCE $seq_name INCREMENT BY 1 START WITH 1 " .
- "NOMAXVALUE NOCYCLE NOCACHE";
- my $trigger_sql = $self->_get_create_trigger_ddl($table, $column, $seq_name);
- return ($seq_sql, $trigger_sql);
+ my $seq_name = "${table}_${column}_SEQ";
+ my $seq_sql = "CREATE SEQUENCE $seq_name INCREMENT BY 1 START WITH 1 "
+ . "NOMAXVALUE NOCYCLE NOCACHE";
+ my $trigger_sql = $self->_get_create_trigger_ddl($table, $column, $seq_name);
+ return ($seq_sql, $trigger_sql);
}
sub _get_create_trigger_ddl {
- my ($self, $table, $column, $seq_name) = @_;
+ my ($self, $table, $column, $seq_name) = @_;
- my $trigger_sql = "CREATE OR REPLACE TRIGGER ${table}_${column}_TR "
- . " BEFORE INSERT ON $table "
- . " FOR EACH ROW "
- . " BEGIN "
- . " SELECT ${seq_name}.NEXTVAL "
- . " INTO :NEW.$column FROM DUAL; "
- . " END;";
- return $trigger_sql;
+ my $trigger_sql
+ = "CREATE OR REPLACE TRIGGER ${table}_${column}_TR "
+ . " BEFORE INSERT ON $table "
+ . " FOR EACH ROW "
+ . " BEGIN "
+ . " SELECT ${seq_name}.NEXTVAL "
+ . " INTO :NEW.$column FROM DUAL; " . " END;";
+ return $trigger_sql;
}
############################################################################
@@ -725,68 +753,69 @@ use strict;
use warnings;
use parent -norequire, qw(DBI::st);
-
+
sub fetchrow_arrayref {
- my $self = shift;
- my $ref = $self->SUPER::fetchrow_arrayref(@_);
- return undef if !defined $ref;
- Bugzilla::DB::Oracle::_fix_arrayref($ref);
- return $ref;
+ my $self = shift;
+ my $ref = $self->SUPER::fetchrow_arrayref(@_);
+ return undef if !defined $ref;
+ Bugzilla::DB::Oracle::_fix_arrayref($ref);
+ return $ref;
}
sub fetchrow_array {
- my $self = shift;
- if ( wantarray ) {
- my @row = $self->SUPER::fetchrow_array(@_);
- Bugzilla::DB::Oracle::_fix_arrayref(\@row);
- return @row;
- } else {
- my $row = $self->SUPER::fetchrow_array(@_);
- $row = Bugzilla::DB::Oracle::_fix_empty($row) if defined $row;
- return $row;
- }
+ my $self = shift;
+ if (wantarray) {
+ my @row = $self->SUPER::fetchrow_array(@_);
+ Bugzilla::DB::Oracle::_fix_arrayref(\@row);
+ return @row;
+ }
+ else {
+ my $row = $self->SUPER::fetchrow_array(@_);
+ $row = Bugzilla::DB::Oracle::_fix_empty($row) if defined $row;
+ return $row;
+ }
}
sub fetchrow_hashref {
- my $self = shift;
- my $ref = $self->SUPER::fetchrow_hashref(@_);
- return undef if !defined $ref;
- Bugzilla::DB::Oracle::_fix_hashref($ref);
- return $ref;
+ my $self = shift;
+ my $ref = $self->SUPER::fetchrow_hashref(@_);
+ return undef if !defined $ref;
+ Bugzilla::DB::Oracle::_fix_hashref($ref);
+ return $ref;
}
sub fetchall_arrayref {
- my $self = shift;
- my $ref = $self->SUPER::fetchall_arrayref(@_);
- return undef if !defined $ref;
- foreach my $row (@$ref) {
- if (ref($row) eq 'ARRAY') {
- Bugzilla::DB::Oracle::_fix_arrayref($row);
- }
- elsif (ref($row) eq 'HASH') {
- Bugzilla::DB::Oracle::_fix_hashref($row);
- }
+ my $self = shift;
+ my $ref = $self->SUPER::fetchall_arrayref(@_);
+ return undef if !defined $ref;
+ foreach my $row (@$ref) {
+ if (ref($row) eq 'ARRAY') {
+ Bugzilla::DB::Oracle::_fix_arrayref($row);
}
- return $ref;
+ elsif (ref($row) eq 'HASH') {
+ Bugzilla::DB::Oracle::_fix_hashref($row);
+ }
+ }
+ return $ref;
}
sub fetchall_hashref {
- my $self = shift;
- my $ref = $self->SUPER::fetchall_hashref(@_);
- return undef if !defined $ref;
- foreach my $row (values %$ref) {
- Bugzilla::DB::Oracle::_fix_hashref($row);
- }
- return $ref;
+ my $self = shift;
+ my $ref = $self->SUPER::fetchall_hashref(@_);
+ return undef if !defined $ref;
+ foreach my $row (values %$ref) {
+ Bugzilla::DB::Oracle::_fix_hashref($row);
+ }
+ return $ref;
}
sub fetch {
- my $self = shift;
- my $row = $self->SUPER::fetch(@_);
- if ($row) {
- Bugzilla::DB::Oracle::_fix_arrayref($row);
- }
- return $row;
+ my $self = shift;
+ my $row = $self->SUPER::fetch(@_);
+ if ($row) {
+ Bugzilla::DB::Oracle::_fix_arrayref($row);
+ }
+ return $row;
}
1;