| # This Source Code Form is subject to the terms of the Mozilla Public |
| # License, v. 2.0. If a copy of the MPL was not distributed with this |
| # file, You can obtain one at http://mozilla.org/MPL/2.0/. |
| # |
| # This Source Code Form is "Incompatible With Secondary Licenses", as |
| # defined by the Mozilla Public License, v. 2.0. |
| |
| =head1 NAME |
| |
| Bugzilla::DB::Oracle - Bugzilla database compatibility layer for Oracle |
| |
| =head1 DESCRIPTION |
| |
| This module overrides methods of the Bugzilla::DB module with Oracle |
| specific implementation. It is instantiated by the Bugzilla::DB module |
| and should never be used directly. |
| |
| For interface details see L<Bugzilla::DB> and L<DBI>. |
| |
| =cut |
| |
| package Bugzilla::DB::Oracle; |
| |
| use 5.10.1; |
| use strict; |
| use warnings; |
| |
| use parent qw(Bugzilla::DB); |
| |
| use DBD::Oracle; |
| use DBD::Oracle qw(:ora_types); |
| use List::Util qw(max); |
| |
| use Bugzilla::Constants; |
| use Bugzilla::Error; |
| use Bugzilla::Util; |
| |
| ##################################################################### |
| # Constants |
| ##################################################################### |
| use constant EMPTY_STRING => '__BZ_EMPTY_STR__'; |
| use constant ISOLATION_LEVEL => 'READ COMMITTED'; |
| 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 '; |
| |
| 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; |
| } |
| |
| sub bz_last_key { |
| 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; |
| } |
| |
| sub bz_check_regexp { |
| my ($self, $pattern) = @_; |
| |
| eval { $self->do("SELECT 1 FROM DUAL WHERE " |
| . $self->sql_regexp($self->quote("a"), $pattern, 1)) }; |
| |
| $@ && 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 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))"; |
| } |
| |
| sub sql_regexp { |
| my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; |
| $real_pattern ||= $pattern; |
| |
| $self->bz_check_regexp($real_pattern) if !$nocheck; |
| |
| return "REGEXP_LIKE($expr, $pattern)"; |
| } |
| |
| sub sql_not_regexp { |
| my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; |
| $real_pattern ||= $pattern; |
| |
| $self->bz_check_regexp($real_pattern) if !$nocheck; |
| |
| return "NOT REGEXP_LIKE($expr, $pattern)" |
| } |
| |
| sub sql_limit { |
| my ($self, $limit, $offset) = @_; |
| |
| if(defined $offset) { |
| return "/* LIMIT $limit $offset */"; |
| } |
| return "/* LIMIT $limit */"; |
| } |
| |
| sub sql_string_concat { |
| my ($self, @params) = @_; |
| |
| return 'CONCAT(' . join(', ', @params) . ')'; |
| } |
| |
| sub sql_to_days { |
| my ($self, $date) = @_; |
| |
| return " TO_CHAR(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)"; |
| } |
| |
| sub sql_date_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; |
| |
| 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"; |
| } |
| |
| sub sql_position { |
| 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) . " )"; |
| } |
| |
| 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)"); |
| } |
| } |
| |
| 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); |
| } |
| } |
| |
| # 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); |
| } |
| } |
| |
| sub _fix_empty { |
| 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; |
| } |
| |
| sub _fix_hashref { |
| 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; |
| } |
| |
| # 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; |
| } |
| |
| # 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. |
| $nonstring =~ s/\bCURRENT_DATE\b/TRUNC(CURRENT_DATE)/io; |
| |
| # Oracle use SUBSTR instead of SUBSTRING |
| $nonstring =~ s/\bSUBSTRING\b/SUBSTR/io; |
| |
| # Oracle need no 'AS' |
| $nonstring =~ s/\bAS\b//ig; |
| |
| # Look for a LIMIT clause |
| ($limit) = ($nonstring =~ m(/\* LIMIT (\d*) \*/)o); |
| |
| if(!length($string)){ |
| push @result, EMPTY_STRING; |
| push @result, $nonstring; |
| } else { |
| push @result, $string; |
| push @result, $nonstring; |
| } |
| } |
| |
| 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; |
| } |
| return $new_sql; |
| } |
| |
| sub 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; |
| } |
| } |
| |
| 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; |
| |
| _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; |
| |
| _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); |
| } |
| } |
| |
| 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; |
| } |
| |
| 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; |
| } |
| |
| 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'; |
| } |
| |
| 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'; |
| } |
| |
| sub quote_identifier { |
| my ($self,$id) = @_; |
| return $id; |
| } |
| |
| ##################################################################### |
| # Protected "Real Database" Schema Information Methods |
| ##################################################################### |
| |
| 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; |
| } |
| |
| 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; |
| } |
| |
| ##################################################################### |
| # Custom Database Setup |
| ##################################################################### |
| |
| 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 |
| MAP MEMBER FUNCTION T_CLOB_DELIM_ToVarchar return VARCHAR2 is |
| BEGIN |
| RETURN p_CONTENT; |
| END; |
| END;"); |
| |
| $self->do("CREATE OR REPLACE TYPE T_GROUP_CONCAT AS OBJECT |
| ( CLOB_CONTENT CLOB, |
| DELIMITER VARCHAR2(256), |
| STATIC FUNCTION ODCIAGGREGATEINITIALIZE( |
| SCTX IN OUT NOCOPY T_GROUP_CONCAT) |
| RETURN NUMBER, |
| MEMBER FUNCTION ODCIAGGREGATEITERATE( |
| SELF IN OUT NOCOPY T_GROUP_CONCAT, |
| VALUE IN T_CLOB_DELIM) |
| RETURN NUMBER, |
| MEMBER FUNCTION ODCIAGGREGATETERMINATE( |
| SELF IN T_GROUP_CONCAT, |
| RETURNVALUE OUT NOCOPY CLOB, |
| FLAGS IN NUMBER) |
| RETURN NUMBER, |
| MEMBER FUNCTION ODCIAGGREGATEMERGE( |
| SELF IN OUT NOCOPY T_GROUP_CONCAT, |
| CTX2 IN T_GROUP_CONCAT) |
| RETURN NUMBER);"); |
| |
| $self->do("CREATE OR REPLACE TYPE BODY T_GROUP_CONCAT IS |
| STATIC FUNCTION ODCIAGGREGATEINITIALIZE( |
| SCTX IN OUT NOCOPY T_GROUP_CONCAT) |
| RETURN NUMBER IS |
| BEGIN |
| SCTX := T_GROUP_CONCAT(EMPTY_CLOB(), NULL); |
| DBMS_LOB.CREATETEMPORARY(SCTX.CLOB_CONTENT, TRUE); |
| RETURN ODCICONST.SUCCESS; |
| END; |
| MEMBER FUNCTION ODCIAGGREGATEITERATE( |
| SELF IN OUT NOCOPY T_GROUP_CONCAT, |
| VALUE IN T_CLOB_DELIM) |
| RETURN NUMBER IS |
| BEGIN |
| SELF.DELIMITER := VALUE.P_DELIMITER; |
| DBMS_LOB.WRITEAPPEND(SELF.CLOB_CONTENT, |
| LENGTH(SELF.DELIMITER), |
| SELF.DELIMITER); |
| DBMS_LOB.APPEND(SELF.CLOB_CONTENT, VALUE.P_CONTENT); |
| |
| RETURN ODCICONST.SUCCESS; |
| END; |
| MEMBER FUNCTION ODCIAGGREGATETERMINATE( |
| SELF IN T_GROUP_CONCAT, |
| RETURNVALUE OUT NOCOPY CLOB, |
| FLAGS IN NUMBER) |
| RETURN NUMBER IS |
| BEGIN |
| RETURNVALUE := RTRIM(LTRIM(SELF.CLOB_CONTENT, |
| SELF.DELIMITER), |
| SELF.DELIMITER); |
| RETURN ODCICONST.SUCCESS; |
| END; |
| MEMBER FUNCTION ODCIAGGREGATEMERGE( |
| SELF IN OUT NOCOPY T_GROUP_CONCAT, |
| CTX2 IN T_GROUP_CONCAT) |
| RETURN NUMBER IS |
| BEGIN |
| DBMS_LOB.WRITEAPPEND(SELF.CLOB_CONTENT, |
| LENGTH(SELF.DELIMITER), |
| SELF.DELIMITER); |
| DBMS_LOB.APPEND(SELF.CLOB_CONTENT, CTX2.CLOB_CONTENT); |
| RETURN ODCICONST.SUCCESS; |
| END; |
| END;"); |
| |
| # 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); |
| } |
| } |
| } |
| } |
| |
| # 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 $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 $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; |
| } |
| |
| ############################################################################ |
| |
| package Bugzilla::DB::Oracle::st; |
| |
| use 5.10.1; |
| 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; |
| } |
| |
| 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; |
| } |
| } |
| |
| 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; |
| } |
| |
| 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); |
| } |
| } |
| 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; |
| } |
| |
| sub fetch { |
| my $self = shift; |
| my $row = $self->SUPER::fetch(@_); |
| if ($row) { |
| Bugzilla::DB::Oracle::_fix_arrayref($row); |
| } |
| return $row; |
| } |
| 1; |
| |
| =head1 B<Methods in need of POD> |
| |
| =over |
| |
| =item adjust_statement |
| |
| =item bz_check_regexp |
| |
| =item bz_drop_table |
| |
| =item bz_explain |
| |
| =item bz_last_key |
| |
| =item bz_setup_database |
| |
| =item bz_table_columns_real |
| |
| =item bz_table_list_real |
| |
| =item do |
| |
| =item prepare |
| |
| =item prepare_cached |
| |
| =item quote_identifier |
| |
| =item selectall_arrayref |
| |
| =item selectall_hashref |
| |
| =item selectcol_arrayref |
| |
| =item selectrow_array |
| |
| =item selectrow_arrayref |
| |
| =item selectrow_hashref |
| |
| =item sql_date_format |
| |
| =item sql_date_math |
| |
| =item sql_from_days |
| |
| =item sql_fulltext_search |
| |
| =item sql_group_concat |
| |
| =item sql_in |
| |
| =item sql_limit |
| |
| =item sql_not_regexp |
| |
| =item sql_position |
| |
| =item sql_regexp |
| |
| =item sql_string_concat |
| |
| =item sql_to_days |
| |
| =back |
