diff options
Diffstat (limited to 'Bugzilla/DB/Oracle.pm')
| -rw-r--r-- | Bugzilla/DB/Oracle.pm | 104 |
1 files changed, 52 insertions, 52 deletions
diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm index 42f3b5efd..5c9ea68a3 100644 --- a/Bugzilla/DB/Oracle.pm +++ b/Bugzilla/DB/Oracle.pm @@ -49,7 +49,7 @@ our $fulltext_label = 0; sub new { my ($class, $params) = @_; - my ($user, $pass, $host, $dbname, $port) = + 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, @@ -62,11 +62,11 @@ sub new { # 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', + 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, + my $self = $class->db_new({ dsn => $dsn, user => $user, pass => $pass, attrs => $attrs }); # Needed by TheSchwartz $self->{private_bz_dsn} = $dsn; @@ -76,7 +76,7 @@ sub new { # 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'") + $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'"); @@ -103,14 +103,14 @@ sub bz_check_regexp { { value => $pattern, dberror => $self->errstr }); } -sub bz_explain { - my ($self, $sql) = @_; - my $sth = $self->prepare("EXPLAIN PLAN FOR $sql"); +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); -} + return join("\n", @$explain); +} sub sql_group_concat { my ($self, $text, $separator) = @_; @@ -134,7 +134,7 @@ sub sql_not_regexp { $self->bz_check_regexp($real_pattern) if !$nocheck; - return "NOT REGEXP_LIKE($expr, $pattern)" + return "NOT REGEXP_LIKE($expr, $pattern)" } sub sql_limit { @@ -173,7 +173,7 @@ sub sql_fulltext_search { sub sql_date_format { my ($self, $date, $format) = @_; - + $format = "%Y.%m.%d %H:%i:%s" if !$format; $format =~ s/\%Y/YYYY/g; @@ -213,7 +213,7 @@ sub sql_in { my $length = $#in_list + 1; my $splice = $length > 1000 ? 1000 : $length; my @sub_in_list = splice(@in_list, 0, $splice); - push(@in_str, + push(@in_str, $self->SUPER::sql_in($column_name, \@sub_in_list, $negate)); } return "( " . join(" OR ", @in_str) . " )"; @@ -237,7 +237,7 @@ sub bz_drop_table { } } -# 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); @@ -272,10 +272,10 @@ sub _fix_hashref { 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 @@ -297,7 +297,7 @@ sub adjust_statement { # 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); @@ -309,21 +309,21 @@ sub adjust_statement { # 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 + # 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 @@ -332,7 +332,7 @@ sub adjust_statement { } # Look for a FROM if this is a SELECT and we haven't found one yet - $has_from = ($nonstring =~ m/\bFROM\b/io) + $has_from = ($nonstring =~ m/\bFROM\b/io) if ($is_select and !$has_from); # Oracle recognizes CURRENT_DATE, but not CURRENT_DATE() @@ -341,11 +341,11 @@ sub adjust_statement { # Oracle use SUBSTR instead of SUBSTRING $nonstring =~ s/\bSUBSTRING\b/SUBSTR/io; - + # Oracle need no 'AS' $nonstring =~ s/\bAS\b//ig; - - # Take the first 4000 chars for comparison + + # Take the first 4000 chars for comparison $nonstring =~ s/\(\s*(longdescs_\d+\.thetext|attachdata_\d+\.thedata)/ \(DBMS_LOB.SUBSTR\($1, 4000, 1\)/ig; @@ -367,7 +367,7 @@ sub adjust_statement { $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"; @@ -377,7 +377,7 @@ sub adjust_statement { 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 ) "; + . " FROM $after_from ) "; $after_where = " R BETWEEN $offset+1 AND $limit+$offset"; } else { $after_where = " rownum <=$limit AND ".$after_where; @@ -442,7 +442,7 @@ sub selectall_arrayref { 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); @@ -462,7 +462,7 @@ sub selectall_hashref { unshift @_, $new_stmt; my $rows = $self->SUPER::selectall_hashref(@_); return undef if !defined $rows; - foreach my $row (values %$rows) { + foreach my $row (values %$rows) { _fix_hashref($row); } return $rows; @@ -510,7 +510,7 @@ 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 + "SELECT LOWER(COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ? ORDER BY COLUMN_NAME", undef, $table); return @$cols; } @@ -518,7 +518,7 @@ sub bz_table_columns_real { sub bz_table_list_real { my ($self) = @_; my $tables = $self->selectcol_arrayref( - "SELECT LOWER(TABLE_NAME) FROM USER_TABLES WHERE + "SELECT LOWER(TABLE_NAME) FROM USER_TABLES WHERE TABLE_NAME NOT LIKE ? ORDER BY TABLE_NAME", undef, 'DR$%'); return @$tables; } @@ -529,15 +529,15 @@ 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. + # 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)" + $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'"); @@ -553,7 +553,7 @@ sub bz_setup_database { 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( @@ -561,7 +561,7 @@ sub bz_setup_database { RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE( SELF IN OUT NOCOPY T_GROUP_CONCAT, - VALUE IN T_CLOB_DELIM) + VALUE IN T_CLOB_DELIM) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE( SELF IN T_GROUP_CONCAT, @@ -570,7 +570,7 @@ sub bz_setup_database { RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE( SELF IN OUT NOCOPY T_GROUP_CONCAT, - CTX2 IN T_GROUP_CONCAT) + CTX2 IN T_GROUP_CONCAT) RETURN NUMBER);"); $self->do("CREATE OR REPLACE TYPE BODY T_GROUP_CONCAT IS @@ -584,35 +584,35 @@ sub bz_setup_database { END; MEMBER FUNCTION ODCIAGGREGATEITERATE( SELF IN OUT NOCOPY T_GROUP_CONCAT, - VALUE IN T_CLOB_DELIM) + VALUE IN T_CLOB_DELIM) RETURN NUMBER IS BEGIN SELF.DELIMITER := VALUE.P_DELIMITER; - DBMS_LOB.WRITEAPPEND(SELF.CLOB_CONTENT, + 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) + FLAGS IN NUMBER) RETURN NUMBER IS BEGIN - RETURNVALUE := RTRIM(LTRIM(SELF.CLOB_CONTENT, - SELF.DELIMITER), + 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) + CTX2 IN T_GROUP_CONCAT) RETURN NUMBER IS BEGIN - DBMS_LOB.WRITEAPPEND(SELF.CLOB_CONTENT, - LENGTH(SELF.DELIMITER), + DBMS_LOB.WRITEAPPEND(SELF.CLOB_CONTENT, + LENGTH(SELF.DELIMITER), SELF.DELIMITER); DBMS_LOB.APPEND(SELF.CLOB_CONTENT, CTX2.CLOB_CONTENT); RETURN ODCICONST.SUCCESS; @@ -620,8 +620,8 @@ sub bz_setup_database { END;"); # Create user-defined aggregate function group_concat - $self->do("CREATE OR REPLACE FUNCTION GROUP_CONCAT(P_INPUT T_CLOB_DELIM) - RETURN CLOB + $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 @@ -673,7 +673,7 @@ sub bz_setup_database { 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 " @@ -727,7 +727,7 @@ sub _get_create_trigger_ddl { package Bugzilla::DB::Oracle::st; use base qw(DBI::st); - + sub fetchrow_arrayref { my $self = shift; my $ref = $self->SUPER::fetchrow_arrayref(@_); |