--- /dev/null
+# -*- 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 Bugzilla::Util;
+
+use constant ADD_COLUMN => 'ADD';
+# Whether this is true or not, this is what it needs to be in order for
+# hash_identifier to maintain backwards compatibility with versions before
+# 3.2rc2.
+use constant MAX_IDENTIFIER_LEN => 27;
+
+#------------------------------------------------------------------------------
+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, $self->_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_" . $self->_hash_identifier($index_name);
+ 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));
+
+}
+
+sub get_drop_index_ddl {
+ my $self = shift;
+ my ($table, $name) = @_;
+
+ $name = 'idx_' . $self->_hash_identifier($name);
+ return $self->SUPER::get_drop_index_ddl($table, $name);
+}
+
+# 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 ${fk_name}_UC"
+ . " AFTER UPDATE ON ". $to_table
+ . " REFERENCING "
+ . " NEW AS NEW "
+ . " OLD AS OLD "
+ . " FOR EACH ROW "
+ . " BEGIN "
+ . " UPDATE $table"
+ . " SET $column = :NEW.$to_column"
+ . " WHERE $column = :OLD.$to_column;"
+ . " END ${fk_name}_UC;";
+ my $dbh = Bugzilla->dbh;
+ $dbh->do($tr_str);
+ }
+
+ return $fk_string;
+}
+
+sub get_drop_fk_sql {
+ my $self = shift;
+ my ($table, $column, $references) = @_;
+ my $fk_name = $self->_get_fk_name(@_);
+ my @sql;
+ if (!$references->{UPDATE} || $references->{UPDATE} =~ /CASCADE/i) {
+ push(@sql, "DROP TRIGGER ${fk_name}_uc");
+ }
+ push(@sql, $self->SUPER::get_drop_fk_sql(@_));
+ return @sql;
+}
+
+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_" . $self->_hash_identifier($fk_name);
+
+ return $fk_name;
+}
+
+sub get_alter_column_ddl {
+ my ($self, $table, $column, $new_def, $set_nulls_to) = @_;
+
+ my @statements;
+ my $old_def = $self->get_column_abstract($table, $column);
+ my $specific = $self->{db_specific};
+
+ # If the types have changed, we have to deal with that.
+ if (uc(trim($old_def->{TYPE})) ne uc(trim($new_def->{TYPE}))) {
+ push(@statements, $self->_get_alter_type_sql($table, $column,
+ $new_def, $old_def));
+ }
+
+ my $default = $new_def->{DEFAULT};
+ my $default_old = $old_def->{DEFAULT};
+ # This first condition prevents "uninitialized value" errors.
+ if (!defined $default && !defined $default_old) {
+ # Do Nothing
+ }
+ # If we went from having a default to not having one
+ elsif (!defined $default && defined $default_old) {
+ push(@statements, "ALTER TABLE $table MODIFY $column"
+ . " DEFAULT NULL");
+ }
+ # If we went from no default to a default, or we changed the default.
+ elsif ( (defined $default && !defined $default_old) ||
+ ($default ne $default_old) )
+ {
+ $default = $specific->{$default} if exists $specific->{$default};
+ push(@statements, "ALTER TABLE $table MODIFY $column "
+ . " DEFAULT $default");
+ }
+
+ # If we went from NULL to NOT NULL.
+ if (!$old_def->{NOTNULL} && $new_def->{NOTNULL}) {
+ my $setdefault;
+ # Handle any fields that were NULL before, if we have a default,
+ $setdefault = $new_def->{DEFAULT} if exists $new_def->{DEFAULT};
+ # But if we have a set_nulls_to, that overrides the DEFAULT
+ # (although nobody would usually specify both a default and
+ # a set_nulls_to.)
+ $setdefault = $set_nulls_to if defined $set_nulls_to;
+ if (defined $setdefault) {
+ push(@statements, "UPDATE $table SET $column = $setdefault"
+ . " WHERE $column IS NULL");
+ }
+ push(@statements, "ALTER TABLE $table MODIFY $column"
+ . " NOT NULL");
+ push (@statements, _get_notnull_trigger_ddl($table, $column))
+ if $old_def->{TYPE} =~ /varchar|text/i
+ && $new_def->{TYPE} =~ /varchar|text/i;
+ }
+ # If we went from NOT NULL to NULL
+ elsif ($old_def->{NOTNULL} && !$new_def->{NOTNULL}) {
+ push(@statements, "ALTER TABLE $table MODIFY $column"
+ . " NULL");
+ push(@statements, "DROP TRIGGER ${table}_${column}")
+ if $new_def->{TYPE} =~ /varchar|text/i
+ && $old_def->{TYPE} =~ /varchar|text/i;
+ }
+
+ # If we went from not being a PRIMARY KEY to being a PRIMARY KEY.
+ if (!$old_def->{PRIMARYKEY} && $new_def->{PRIMARYKEY}) {
+ push(@statements, "ALTER TABLE $table ADD PRIMARY KEY ($column)");
+ }
+ # If we went from being a PK to not being a PK
+ elsif ( $old_def->{PRIMARYKEY} && !$new_def->{PRIMARYKEY} ) {
+ push(@statements, "ALTER TABLE $table DROP PRIMARY KEY");
+ }
+
+ return @statements;
+}
+
+sub _get_alter_type_sql {
+ my ($self, $table, $column, $new_def, $old_def) = @_;
+ my @statements;
+
+ my $type = $new_def->{TYPE};
+ $type = $self->{db_specific}->{$type}
+ if exists $self->{db_specific}->{$type};
+
+ if ($type =~ /serial/i && $old_def->{TYPE} !~ /serial/i) {
+ die("You cannot specify a DEFAULT on a SERIAL-type column.")
+ if $new_def->{DEFAULT};
+ }
+
+ if ( ($old_def->{TYPE} =~ /LONGTEXT/i && $new_def->{TYPE} !~ /LONGTEXT/i)
+ || ($old_def->{TYPE} !~ /LONGTEXT/i && $new_def->{TYPE} =~ /LONGTEXT/i)
+ ) {
+ # LONG to VARCHAR or VARCHAR to LONG is not allowed in Oracle,
+ # just a way to work around.
+ # Determine whether column_temp is already exist.
+ my $dbh=Bugzilla->dbh;
+ my $column_exist = $dbh->selectcol_arrayref(
+ "SELECT CNAME FROM COL WHERE TNAME = UPPER(?) AND
+ CNAME = UPPER(?)", undef,$table,$column . "_temp");
+ if(!@$column_exist) {
+ push(@statements,
+ "ALTER TABLE $table ADD ${column}_temp $type");
+ }
+ push(@statements, "UPDATE $table SET ${column}_temp = $column");
+ push(@statements, "COMMIT");
+ push(@statements, "ALTER TABLE $table DROP COLUMN $column");
+ push(@statements,
+ "ALTER TABLE $table RENAME COLUMN ${column}_temp TO $column");
+ } else {
+ push(@statements, "ALTER TABLE $table MODIFY $column $type");
+ }
+
+ if ($new_def->{TYPE} =~ /serial/i && $old_def->{TYPE} !~ /serial/i) {
+ push(@statements, _get_create_seq_ddl($table, $column));
+ }
+
+ # If this column is no longer SERIAL, we need to drop the sequence
+ # that went along with it.
+ if ($old_def->{TYPE} =~ /serial/i && $new_def->{TYPE} !~ /serial/i) {
+ push(@statements, "DROP SEQUENCE ${table}_${column}_SEQ");
+ push(@statements, "DROP TRIGGER ${table}_${column}_TR");
+ }
+
+ # If this column is changed to type TEXT/VARCHAR, we need to deal with
+ # empty string.
+ if ( $old_def->{TYPE} !~ /varchar|text/i
+ && $new_def->{TYPE} =~ /varchar|text/i
+ && $new_def->{NOTNULL} )
+ {
+ push (@statements, _get_notnull_trigger_ddl($table, $column));
+ }
+ # If this column is no longer TEXT/VARCHAR, we need to drop the trigger
+ # that went along with it.
+ if ( $old_def->{TYPE} =~ /varchar|text/i
+ && $old_def->{NOTNULL}
+ && $new_def->{TYPE} !~ /varchar|text/i )
+ {
+ push(@statements, "DROP TRIGGER ${table}_${column}");
+ }
+ return @statements;
+}
+
+sub get_rename_column_ddl {
+ my ($self, $table, $old_name, $new_name) = @_;
+ if (lc($old_name) eq lc($new_name)) {
+ # if the only change is a case change, return an empty list.
+ return ();
+ }
+ my @sql = ("ALTER TABLE $table RENAME COLUMN $old_name TO $new_name");
+ my $def = $self->get_column_abstract($table, $old_name);
+ if ($def->{TYPE} =~ /SERIAL/i) {
+ # We have to rename the series also, and fix the default of the series.
+ push(@sql, "RENAME ${table}_${old_name}_SEQ TO
+ ${table}_${new_name}_seq");
+ my $serial_sql =
+ "CREATE OR REPLACE TRIGGER ${table}_${new_name}_TR "
+ . " BEFORE INSERT ON ${table} "
+ . " FOR EACH ROW "
+ . " BEGIN "
+ . " SELECT ${table}_${new_name}_SEQ.NEXTVAL "
+ . " INTO :NEW.${new_name} FROM DUAL; "
+ . " END;";
+ push(@sql, $serial_sql);
+ push(@sql, "DROP TRIGGER ${table}_${old_name}_TR");
+ }
+ if ($def->{TYPE} =~ /varchar|text/i && $def->{NOTNULL} ) {
+ push(@sql, _get_notnull_trigger_ddl($table,$new_name));
+ push(@sql, "DROP TRIGGER ${table}_${old_name}");
+ }
+ return @sql;
+}
+
+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 ($self, $table, $column, $start_with) = @_;
+ $start_with ||= 1;
+ my @ddl;
+ my $seq_name = "${table}_${column}_SEQ";
+ my $seq_sql = "CREATE SEQUENCE $seq_name "
+ . " INCREMENT BY 1 "
+ . " START WITH $start_with "
+ . " 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;
projects / vuplus_webkit / blobdiff