diff options
| -rw-r--r-- | Bugzilla/Constants.pm | 7 | ||||
| -rw-r--r-- | Bugzilla/DB.pm | 56 | ||||
| -rw-r--r-- | Bugzilla/DB/Oracle.pm | 487 | ||||
| -rw-r--r-- | Bugzilla/DB/Pg.pm | 56 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema.pm | 3 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Oracle.pm | 210 |
6 files changed, 762 insertions, 57 deletions
diff --git a/Bugzilla/Constants.pm b/Bugzilla/Constants.pm index e34fc0bb7..3c02c3902 100644 --- a/Bugzilla/Constants.pm +++ b/Bugzilla/Constants.pm @@ -395,6 +395,13 @@ use constant DB_MODULE => { version => '1.45', }, name => 'PostgreSQL'}, + 'oracle'=> {db => 'Bugzilla::DB::Oracle', db_version => '10.01.0', + dbd => { + package => 'DBD-Oracle', + module => 'DBD::Oracle', + version => '1.19', + }, + name => 'Oracle'}, }; # The user who should be considered "root" when we're giving diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 4aad803c6..49692eec0 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -301,6 +301,62 @@ sub import { $Exporter::ExportLevel-- if $is_exporter; } +sub bz_lock_tables { + my ($self, @tables) = @_; + + my $list = join(', ', @tables); + # Check first if there was no lock before + if ($self->{private_bz_tables_locked}) { + ThrowCodeError("already_locked", { current => $self->{private_bz_tables_locked}, + new => $list }); + } else { + my %read_tables; + my %write_tables; + foreach my $table (@tables) { + $table =~ /^([\d\w]+)([\s]+AS[\s]+[\d\w]+)?[\s]+(WRITE|READ)$/i; + my $table_name = $1; + if ($3 =~ /READ/i) { + if (!exists $read_tables{$table_name}) { + $read_tables{$table_name} = undef; + } + } + else { + if (!exists $write_tables{$table_name}) { + $write_tables{$table_name} = undef; + } + } + } + + # Begin Transaction + $self->bz_start_transaction(); + + Bugzilla->dbh->do('LOCK TABLE ' . join(', ', keys %read_tables) . + ' IN ROW SHARE MODE') if keys %read_tables; + Bugzilla->dbh->do('LOCK TABLE ' . join(', ', keys %write_tables) . + ' IN ROW EXCLUSIVE MODE') if keys %write_tables; + $self->{private_bz_tables_locked} = $list; + } +} + +sub bz_unlock_tables { + my ($self, $abort) = @_; + + # Check first if there was previous matching lock + if (!$self->{private_bz_tables_locked}) { + # Abort is allowed even without previous lock for error handling + return if $abort; + ThrowCodeError("no_matching_lock"); + } else { + $self->{private_bz_tables_locked} = ""; + # End transaction, tables will be unlocked automatically + if ($abort) { + $self->bz_rollback_transaction(); + } else { + $self->bz_commit_transaction(); + } + } +} + sub sql_istrcmp { my ($self, $left, $right, $op) = @_; $op ||= "="; diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm new file mode 100644 index 000000000..008f4be70 --- /dev/null +++ b/Bugzilla/DB/Oracle.pm @@ -0,0 +1,487 @@ +# -*- Mode: perl; indent-tabs-mode: nil -*- +# +# The contents of this file are subject to the Mozilla Public +# License Version 1.1 (the "License"); you may not use this file +# except in compliance with the License. You may obtain a copy of +# the License at http://www.mozilla.org/MPL/ +# +# Software distributed under the License is distributed on an "AS +# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or +# implied. See the License for the specific language governing +# rights and limitations under the License. +# +# The Original Code is the Bugzilla Bug Tracking System. +# +# The Initial Developer of the Original Code is Oracle Corporation. +# Portions created by Oracle are Copyright (C) 2007 Oracle Corporation. +# All Rights Reserved. +# +# Contributor(s): Lance Larsh <lance.larsh@oracle.com> +# Xiaoou Wu <xiaoou.wu@oracle.com> +# Max Kanat-Alexander <mkanat@bugzilla.org> + +=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 strict; + +use DBD::Oracle; +use DBD::Oracle qw(:ora_types); +use Bugzilla::Constants; +use Bugzilla::Error; +use Bugzilla::Util; +# This module extends the DB interface via inheritance +use base qw(Bugzilla::DB); + +##################################################################### +# Constants +##################################################################### +use constant EMPTY_STRING => '__BZ_EMPTY_STR__'; +use constant ISOLATION_LEVEL => 'SERIALIZABLE'; +use constant BLOB_TYPE => { ora_type => ORA_BLOB }; + +sub new { + my ($class, $user, $pass, $host, $dbname, $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 => ( Bugzilla->params->{'maxattachmentsize'} + || 1000 ) * 1024, + }; + my $self = $class->db_new($dsn, $user, $pass, $attrs); + + 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'}; + 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 sql_regexp { + my ($self, $expr, $pattern) = @_; + + return "REGEXP_LIKE($expr, $pattern)"; +} + +sub sql_not_regexp { + my ($self, $expr, $pattern) = @_; + + 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) = @_; + $text = $self->quote($text); + trick_taint($text); + return "CONTAINS($column,$text)"; +} + +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_interval { + my ($self, $interval, $units) = @_; + + return "INTERVAL " . $self->quote($interval) . " $units"; +} + +sub sql_position { + my ($self, $fragment, $text) = @_; + return "INSTR($text, $fragment)"; +} +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) = @_; + + # 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 recognizes CURRENT_DATE, but not CURRENT_DATE() + $part =~ s/\bCURRENT_DATE\b\(\)/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 recognizes CURRENT_DATE, but not CURRENT_DATE() + $nonstring =~ s/\bCURRENT_DATE\b\(\)/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); + + 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; + if (defined($offset)) { + if ($new_sql =~ /(.*\s+)FROM(\s+.*)/i) { + my ($before_from,$after_from) = ($1,$2); + $before_where = "$before_from FROM ($before_from," + . " ROW_NUMBER() OVER (ORDER BY quipid) 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; +} + + + +##################################################################### +# 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;"); + # Create a WORLD_LEXER named BZ_LEX for multilingual fulltext search + $self->do("BEGIN CTX_DDL.CREATE_PREFERENCE + ('BZ_LEX', 'WORLD_LEXER'); END;"); + + $self->SUPER::bz_setup_database(@_); +} + +package Bugzilla::DB::Oracle::st; +use base qw(DBD::Oracle::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; +} + +1; diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm index bd16b654c..9675e1f26 100644 --- a/Bugzilla/DB/Pg.pm +++ b/Bugzilla/DB/Pg.pm @@ -158,62 +158,6 @@ sub sql_string_concat { return '(CAST(' . join(' AS text) || CAST(', @params) . ' AS text))'; } -sub bz_lock_tables { - my ($self, @tables) = @_; - - my $list = join(', ', @tables); - # Check first if there was no lock before - if ($self->{private_bz_tables_locked}) { - ThrowCodeError("already_locked", { current => $self->{private_bz_tables_locked}, - new => $list }); - } else { - my %read_tables; - my %write_tables; - foreach my $table (@tables) { - $table =~ /^([\d\w]+)([\s]+AS[\s]+[\d\w]+)?[\s]+(WRITE|READ)$/i; - my $table_name = $1; - if ($3 =~ /READ/i) { - if (!exists $read_tables{$table_name}) { - $read_tables{$table_name} = undef; - } - } - else { - if (!exists $write_tables{$table_name}) { - $write_tables{$table_name} = undef; - } - } - } - - # Begin Transaction - $self->bz_start_transaction(); - - Bugzilla->dbh->do('LOCK TABLE ' . join(', ', keys %read_tables) . - ' IN ROW SHARE MODE') if keys %read_tables; - Bugzilla->dbh->do('LOCK TABLE ' . join(', ', keys %write_tables) . - ' IN ROW EXCLUSIVE MODE') if keys %write_tables; - $self->{private_bz_tables_locked} = $list; - } -} - -sub bz_unlock_tables { - my ($self, $abort) = @_; - - # Check first if there was previous matching lock - if (!$self->{private_bz_tables_locked}) { - # Abort is allowed even without previous lock for error handling - return if $abort; - ThrowCodeError("no_matching_lock"); - } else { - $self->{private_bz_tables_locked} = ""; - # End transaction, tables will be unlocked automatically - if ($abort) { - $self->bz_rollback_transaction(); - } else { - $self->bz_commit_transaction(); - } - } -} - # Tell us whether or not a particular sequence exists in the DB. sub bz_sequence_exists { my ($self, $seq_name) = @_; diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm index a4c1019b8..89ae99a62 100644 --- a/Bugzilla/DB/Schema.pm +++ b/Bugzilla/DB/Schema.pm @@ -206,6 +206,7 @@ update this column in this table." =cut use constant SCHEMA_VERSION => '2.00'; +use constant ADD_COLUMN => 'ADD COLUMN'; use constant ABSTRACT_SCHEMA => { # BUG-RELATED TABLES @@ -1750,7 +1751,7 @@ sub get_add_column_ddl { my ($self, $table, $column, $definition, $init_value) = @_; my @statements; - push(@statements, "ALTER TABLE $table ADD COLUMN $column " . + push(@statements, "ALTER TABLE $table". ADD_COLUMN ." $column " . $self->get_type_ddl($definition)); # XXX - Note that although this works for MySQL, most databases will fail diff --git a/Bugzilla/DB/Schema/Oracle.pm b/Bugzilla/DB/Schema/Oracle.pm new file mode 100644 index 000000000..fef970c8e --- /dev/null +++ b/Bugzilla/DB/Schema/Oracle.pm @@ -0,0 +1,210 @@ +# -*- Mode: perl; indent-tabs-mode: nil -*- +# +# The contents of this file are subject to the Mozilla Public +# License Version 1.1 (the "License"); you may not use this file +# except in compliance with the License. You may obtain a copy of +# the License at http://www.mozilla.org/MPL/ +# +# Software distributed under the License is distributed on an "AS +# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or +# implied. See the License for the specific language governing +# rights and limitations under the License. +# +# The Original Code is the Bugzilla Bug Tracking System. +# +# The Initial Developer of the Original Code is Oracle Corporation. +# Portions created by Oracle are Copyright (C) 2007 Oracle Corporation. +# All Rights Reserved. +# +# Contributor(s): Lance Larsh <lance.larsh@oracle.com> +# Xiaoou Wu <xiaoou.wu@oracle.com> +# Max Kanat-Alexander <mkanat@bugzilla.org> + +package Bugzilla::DB::Schema::Oracle; + +############################################################################### +# +# DB::Schema implementation for Oracle +# +############################################################################### + +use strict; + +use base qw(Bugzilla::DB::Schema); +use Carp qw(confess); +use Digest::MD5 qw(md5_hex); +use Bugzilla::Util; + +use constant ADD_COLUMN => 'ADD'; + +#------------------------------------------------------------------------------ +sub _initialize { + + my $self = shift; + + $self = $self->SUPER::_initialize(@_); + + $self->{db_specific} = { + + BOOLEAN => 'integer', + FALSE => '0', + TRUE => '1', + + INT1 => 'integer', + INT2 => 'integer', + INT3 => 'integer', + INT4 => 'integer', + + SMALLSERIAL => 'integer', + MEDIUMSERIAL => 'integer', + INTSERIAL => 'integer', + + TINYTEXT => 'varchar(255)', + MEDIUMTEXT => 'varchar(4000)', + LONGTEXT => 'clob', + + LONGBLOB => 'blob', + + DATETIME => 'date', + + }; + + $self->_adjust_schema; + + return $self; + +} #eosub--_initialize +#-------------------------------------------------------------------- + +sub get_table_ddl { + my $self = shift; + my $table = shift; + unshift @_, $table; + my @ddl = $self->SUPER::get_table_ddl(@_); + + my @fields = @{ $self->{abstract_schema}{$table}{FIELDS} || [] }; + while (@fields) { + my $field_name = shift @fields; + my $field_info = shift @fields; + # Create triggers to deal with empty string. + if ( $field_info->{TYPE} =~ /varchar|TEXT/i + && $field_info->{NOTNULL} ) { + push (@ddl, _get_notnull_trigger_ddl($table, $field_name)); + } + # Create sequences and triggers to emulate SERIAL datatypes. + if ( $field_info->{TYPE} =~ /SERIAL/i ) { + push (@ddl, _get_create_seq_ddl($table, $field_name)); + } + } + return @ddl; + +} #eosub--get_table_ddl + +# Extend superclass method to create Oracle Text indexes if index type +# is FULLTEXT from schema. Returns a "create index" SQL statement. +sub _get_create_index_ddl { + + my ($self, $table_name, $index_name, $index_fields, $index_type) = @_; + $index_name = "idx_" . substr(md5_hex($index_name),0,20); + if ($index_type eq 'FULLTEXT') { + my $sql = "CREATE INDEX $index_name ON $table_name (" + . join(',',@$index_fields) + . ") INDEXTYPE IS CTXSYS.CONTEXT " + . " PARAMETERS('LEXER BZ_LEX SYNC(ON COMMIT)')" ; + return $sql; + } + + return($self->SUPER::_get_create_index_ddl($table_name, $index_name, + $index_fields, $index_type)); + +} #eosub--_get_create_index_ddl + +# Oracle supports the use of FOREIGN KEY integrity constraints +# to define the referential integrity actions, including: +# - Update and delete No Action (default) +# - Delete CASCADE +# - Delete SET NULL +sub get_fk_ddl { + my ($self, $table, $column, $references) = @_; + return "" if !$references; + + my $update = $references->{UPDATE} || 'CASCADE'; + my $delete = $references->{DELETE}; + my $to_table = $references->{TABLE} || confess "No table in reference"; + my $to_column = $references->{COLUMN} || confess "No column in reference"; + my $fk_name = $self->_get_fk_name($table, $column, $references); + + my $fk_string = "\n CONSTRAINT $fk_name FOREIGN KEY ($column)\n" + . " REFERENCES $to_table($to_column)\n"; + + $fk_string = $fk_string . " ON DELETE $delete" if $delete; + + if ( $update =~ /CASCADE/i ){ + my $tr_str = "CREATE OR REPLACE TRIGGER ". $table . "_uc" + . " AFTER UPDATE ON ". $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 . ";" + . " END ". $table . "_uc;"; + my $dbh = Bugzilla->dbh; + $dbh->do($tr_str); + } + + return $fk_string; +} + +sub _get_fk_name { + my ($self, $table, $column, $references) = @_; + my $to_table = $references->{TABLE}; + my $to_column = $references->{COLUMN}; + my $fk_name = "${table}_${column}_${to_table}_${to_column}"; + $fk_name = "fk_" . substr(md5_hex($fk_name),0,20); + + return $fk_name; +} + +sub _get_notnull_trigger_ddl { + my ($table, $column) = @_; + + my $notnull_sql = "CREATE OR REPLACE TRIGGER " + . " ${table}_${column}" + . " BEFORE INSERT OR UPDATE ON ". $table + . " FOR EACH ROW" + . " BEGIN " + . " IF :NEW.". $column ." IS NULL THEN " + . " SELECT '" . Bugzilla::DB::Oracle::EMPTY_STRING + . "' INTO :NEW.". $column ." FROM DUAL; " + . " END IF; " + . " END ".$table.";"; + return $notnull_sql; +} + +sub _get_create_seq_ddl { + my ($table, $column) = @_; + my @ddl; + my $seq_name = "${table}_${column}_SEQ"; + my $seq_sql = "CREATE SEQUENCE $seq_name " + . " INCREMENT BY 1 " + . " START WITH 1 " + . " NOMAXVALUE " + . " NOCYCLE " + . " NOCACHE"; + my $serial_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;"; + push (@ddl, $seq_sql); + push (@ddl, $serial_sql); + + return @ddl; +} + +1; |