From e18d4374c16e3d9e8164eec8af3c4cd6c52c4406 Mon Sep 17 00:00:00 2001 From: "mkanat%bugzilla.org" <> Date: Tue, 11 Dec 2007 11:36:02 +0000 Subject: Bug 310717: [Oracle] Bugzilla::DB::Oracle module Bug 310718: [Oracle] Bugzilla::DB::Schema::Oracle module Patch By Xiaoou Wu r=mkanat, a=mkanat --- Bugzilla/DB/Schema/Oracle.pm | 210 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 210 insertions(+) create mode 100644 Bugzilla/DB/Schema/Oracle.pm (limited to 'Bugzilla/DB/Schema/Oracle.pm') 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 +# Xiaoou Wu +# Max Kanat-Alexander + +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; -- cgit v1.2.3-24-g4f1b