Progress on RT2 Oracle port (from: dmorgan@bartertrust.com)


#1

Great news: Dave has got a mostly working port of RT2 to oracle!
I’ll be working to roll his updates into the core codebase “soon.”

    jesse

----- Forwarded message from Dave Morgan dmorgan@bartertrust.com -----Date: Fri, 25 Aug 2000 14:02:51 -0700
From: Dave Morgan dmorgan@bartertrust.com
X-Mailer: Mozilla 4.73 [en] (X11; U; Linux 2.2.12-20 i686)
To: Jesse jesse@fsck.com
Subject: Progress

Hi Jesse,
I rebuilt from the new release and all my changes are below.
Very few considering the differences.

I have also changed schema.Oracle and attached it. Changes
also listed below

I am able to:
	Create a queue
	List all queues
	Create a ticket !!!! (Including CLOB fields)
	List tickets
I have done the above with rt, rtq and rtadmin.

CHANGES
In DBIx/Handle.pm

In Connect subroutine
Change

$dsn = “dbi:$args{‘Driver’}:$args{‘Database’}:$args{‘Host’}”;

$dsn = “dbi:$args{‘Driver’}:$args{‘Database’}”;

Add For BLOBS
$Handle->{LongTruncOk}=1;
$Handle->{LongReadLen}=8000;

Add for Date handling (I know, no error handling, and it should
probably use SimpleQuery, but …)

my $sth = $Handle->prepare(“ALTER SESSION set NLS_DATE_FORMAT = ‘YYYY-MM-DD HH24:MI:SS’”);
$sth->execute;

In DBIx/EasySearch
In subroutine _TableAliases

Change

my $compiled_aliases = $self->{‘table’}." AS main";

my $compiled_aliases = $self->{'table'}." main";

Change

$self->{‘aliases’}[$count]{‘table’}. " as ".

  $self->{'aliases'}[$count]{'table'}. " ".

In Record.pm Replaced subroutine Create with

sub Create {
my $self = shift;
my @keyvalpairs = (@_);
my ($cols, $vals);

while (my $key = shift @keyvalpairs) {
  my $value = shift @keyvalpairs;
  $cols .= $key . ", ";
  if (defined ($value)) {
  $value = $self->_Handle->safe_quote($value)
      unless ($key=~/^(Created|LastUpdated)$/ && $value=~/^now\(\)$/i);
  $vals .= "$value, ";
  }
  else {
$vals .= "NULL, ";
  }
}	

$cols =~ s/, $//;
$vals =~ s/, $//;
#TODO Check to make sure the key's not already listed.
#TODO update internal data structure
my $QueryString = "INSERT INTO ".$self->{'table'}." ($cols) VALUES ($vals)";

my $sth = $self->_Handle->SimpleQuery($QueryString);
if (!$sth) {
   if ($main::debug) {
die "Error with $QueryString";
  }
   else {
 return (0);
   }
 }

Oracle Hack to replace non-supported mysql_rowid call

$QueryString = "SELECT ".$self->{'table'}."_NUM.currval FROM DUAL";

$sth = $self->_Handle->SimpleQuery($QueryString);
if (!$sth) {
   if ($main::debug) {
die "Error with $QueryString";
  }
   else {
 return (0);
   }
 }

#probably better/more efficient way to do following
#needs error checking
my @row = $sth->fetchrow_array;
$self->{‘id’}=$row[0];
return( $self->{‘id’}); #Add Succeded. return the id
}

IN schema.Oracle

Added tables GROUPS and GROUPMEMBERS
Modified table ACL
Added
INSERT INTO ACL VALUES (NULL, 0,‘Everyone’,‘SuperUser’,‘System’,0);

Changed all sequences to naming convention _NUM
- many were missing an ‘S’
- ie: TICKET_NUM instead of TICKETS_NUM
- adjusted triggers

Changed all BLOB’s to CLOB’s for easy character handling.

In schema.mysql
Typo in insert statement Superuser changed to SuperUser
I assume mysql is case sensitive, even if not should be done properly

INSERT INTO ACL VALUES (1, 0,‘Everyone’,‘SuperUser’,‘System’,0);

HTH
Dave
Dave Morgan
Senior Database Administrator
Internet Barter Inc.
www.bartertrust.com
408-982-8774
REM $Header: /raid/cvsroot/rt/etc/Attic/schema.mysql,v 1.1.2.96 2000/06/15 13:02:02 tobiasb Exp $
REM