[rt-devel] [fwd] Progress on RT2 Oracle port (from: dmorgan@bartertrust.com)
Jesse
jesse at fsck.com
Fri Aug 25 17:10:53 EDT 2000
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 at bartertrust.com> -----
Date: Fri, 25 Aug 2000 14:02:51 -0700
From: Dave Morgan <dmorgan at bartertrust.com>
X-Mailer: Mozilla 4.73 [en] (X11; U; Linux 2.2.12-20 i686)
To: Jesse <jesse at 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 <TABLENAME>_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
REM Request Tracker is Copyright 1996-1999 Jesse Reed Vincent <jesse at fsck.com>
REM RT is distributed under the terms of the GNU Public License
REM This contains both table definitions and some default data. The
REM data shouldn't be here, it's mostly for test purposes. Eventually,
REM the makefile should call up some init scrips that uses an RT api or the (admin)
REM cli to add the data.
REM I think it might make sense replacing "TIMESTAMP" with "DATE"
REM for mysql. Yes, indeed, I'll do that right away.
REM Dave Morgan BarterTrust 2000/08
REM - Oracle Port
REM - issue with AUTO INCREMENT
REM - have created before insert triggers to put
REM a sequence generated number in. This means
REM the value of the primary key column cannot be defined
REM in the insert statement. The trigger will override any
REM supplied value
REM
REM {{{ Core entities (Queues, Tickets, Transactions, Attachments, Links)
REM {{{ TABLE Queues
CREATE SEQUENCE QUEUES_NUM;
CREATE TABLE Queues (
id NUMBER(11, 0) PRIMARY KEY,
QueueId VARCHAR2(40) NOT NULL,
Description VARCHAR2(120),
CorrespondAddress VARCHAR2(40),
CommentAddress VARCHAR2(40),
PermitNonmemberCreate NUMBER(11, 0), /*let nonmembers create*/
InitialPriority NUMBER(11, 0),
FinalPriority NUMBER(11, 0),
DefaultDueIn NUMBER(11, 0), /*reqs in this queue are due in X days by default*/
Creator NUMBER(11, 0),
Created DATE,
LastUpdatedBy NUMBER(11, 0),
LastUpdated DATE
);
CREATE OR REPLACE TRIGGER QUEUES_AUTOINCREMENT
BEFORE INSERT ON QUEUES
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT QUEUES_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM {{{ Values for Queues
REM Default queue - should eventually be created by the admin-cli through some init script
INSERT INTO Queues VALUES (NULL, 'general','A General Catch-all queue' ,'rt at localhost', 'rt-comment at localhost', 1, 0, 0, NULL, NULL, NULL, NULL, NULL);
REM {{{ TABLE Tickets
CREATE SEQUENCE TICKETS_NUM;
CREATE TABLE Tickets (
id NUMBER(11, 0) PRIMARY KEY,
EffectiveId NUMBER(11, 0),
Queue NUMBER(11,0),
Alias VARCHAR2(40),
Type VARCHAR2(16), /*Ticket, Task, Bug*/
IssueStatement NUMBER(11,0), /*Reference to Attachments*/
Resolution NUMBER(11,0), /*Reference to Attachments*/
Owner NUMBER(11,0), /*Reference to Users*/
Subject VARCHAR2(200) DEFAULT '',
InitialPriority NUMBER(11,0) DEFAULT 0,
FinalPriority NUMBER(11,0) DEFAULT 0,
Priority NUMBER(11,0) DEFAULT 0,
Status VARCHAR2(10), /*For now, One of "Open, Stalled, Resolved, Dead"*/
TimeWorked NUMBER(11,0) DEFAULT 0,
Told DATE,
Due DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE,
Creator NUMBER(11,0),
Created DATE
);
CREATE OR REPLACE TRIGGER TICKETS_AUTOINCREMENT
BEFORE INSERT ON TICKETS
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT TICKETS_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM {{{ TABLE Transactions
CREATE SEQUENCE TRANSACTIONS_NUM;
CREATE TABLE Transactions (
id NUMBER(11,0) PRIMARY KEY,
EffectiveTicket NUMBER(11,0),
Ticket NUMBER(11,0),
TimeTaken NUMBER(11,0),
Type VARCHAR2(20),
Field VARCHAR2(40),
OldValue VARCHAR2(255),
NewValue VARCHAR2(255),
Data VARCHAR2(100),
Creator NUMBER(11,0),
Created DATE
);
CREATE OR REPLACE TRIGGER TRANSACTIONS_AUTOINCREMENT
BEFORE INSERT ON TRANSACTIONS
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT TRANSACTIONS_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM {{{ TABLE Attachments
CREATE SEQUENCE ATTACHMENTS_NUM;
CREATE TABLE Attachments (
id NUMBER(11,0) PRIMARY KEY,
TransactionId NUMBER(11,0) NOT NULL,
Parent NUMBER(11,0), /*Another attachment.*/
MessageId VARCHAR2(160),
Subject VARCHAR2(255),
Filename VARCHAR2(255),
ContentType VARCHAR2(80),
Content CLOB,
Headers CLOB,
Creator NUMBER(11,0),
Created DATE
);
CREATE OR REPLACE TRIGGER ATTACHMENTS_AUTOINCREMENT
BEFORE INSERT ON ATTACHMENTS
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT ATTACHMENTS_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM {{{ TABLE Links
CREATE SEQUENCE LINKS_NUM;
CREATE TABLE Links (
id NUMBER(11,0) PRIMARY KEY,
Base VARCHAR2(255),
Target VARCHAR2(255),
Type VARCHAR2(20) NOT NULL,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE,
Creator NUMBER(11,0),
Created DATE
);
REM Type can be one of:
REM <BASE> DependsOn <TARGET>
REM <BASE> MemberOf <TARGET>
REM <BASE> MergedInto <TARGET>
REM <BASE> RefersTo <TARGET>
REM One possible Idea: Turn base into URIs
REM fsck.com-rt://instancename/ticket/<id>
REM We need possibilities for letting links point to/from remote
REM instances of RT as well as to other remote and internal web-based
REM databases. I suggest another table for URL information about
REM foreign installations and different databases (URLs, description,
REM ++) ... and using the
REM mail gateway for communication between different RT instances.
CREATE OR REPLACE TRIGGER LINKS_AUTOINCREMENT
BEFORE INSERT ON LINKS
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT LINKS_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM {{{ Users, ACL and watchers
REM {{{ Table Users
REM Actually I don't think this is a good way to do it; we
REM need it to be site-configurable what information we want to store
REM about our users.
REM Actually I think the best way is to only store only what is
REM essential for RT, and let
REM all other data be stored externally in any system of choise. Hm.
REM ATOH, A Plugin authentication system which replaces this table
REM would clearly be the best and only right thing. We have to fix that for RT 2.1
CREATE SEQUENCE USERS_NUM;
CREATE TABLE Users (
id NUMBER(11,0) PRIMARY KEY,
UserId VARCHAR2(120) NOT NULL,
Gecos VARCHAR2(16), /*Gecos is the name of the fields in a unix passwd file.*/
/*In this case, it refers to "Unix Username"*/
RealName VARCHAR2(120),
Lang VARCHAR2(16), /*Preffered language - to be used by L10N (not there, yet)*/
Password VARCHAR2(40),
Comments CLOB,
Signature CLOB,
EmailAddress VARCHAR2(120),
CanManipulate NUMBER(11,0), /*Whether this user can be granted rights*/
/*If 0, the user can only be a requestor*/
IsAdministrator NUMBER(11,0),
/*We should rather have a "plug'n'play" authentication system where*/
/*this table can be exchanged for some external system.*/
ExternalId VARCHAR2(100),
/*I'd daresay that those fields really shouldn't be in RT:*/
/*-- TobiX*/
HomePhone VARCHAR2(30),
WorkPhone VARCHAR2(30),
Address1 VARCHAR2(200),
Address2 VARCHAR2(200),
City VARCHAR2(100),
State VARCHAR2(100),
Zip VARCHAR2(16),
Country VARCHAR2(50),
Creator NUMBER(11,0),
Created DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE
);
CREATE OR REPLACE TRIGGER USERS_AUTOINCREMENT
BEFORE INSERT ON USERS
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT USERS_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM }}}
REM {{{ Values for Users
INSERT INTO Users (id, UserId, RealName, Comments, IsAdministrator, Creator, Created, LastUpdatedBy, LastUpdated)
VALUES (1, 'RT_System', 'The RT System itself', UTL_RAW.CAST_TO_RAW('Do not delete or modify this user. It is integral to RT''s internal database structures'),1,1, NULL, 1, NULL );
INSERT INTO Users (id, UserId, RealName, Comments, IsAdministrator, Creator, Created, LastUpdatedBy, LastUpdated)
VALUES (2, 'Nobody', 'No-one in particular', UTL_RAW.CAST_TO_RAW('Do not delete or modify this user. It is integral to RT''s internal database structures'),1,1, NULL, 1, NULL );
INSERT INTO Users (id, UserId, Gecos, RealName, Password, EmailAddress, Comments, IsAdministrator, Creator, Created, LastUpdatedBy, LastUpdated)
VALUES (3, 'root', 'root', 'Enoch Root', 'password', 'root at localhost','', 0,1, NULL, 1, NULL );
REM {{{ AlternateEmails (stubbed!)
REM Create Table AlternateEmails (
REM id NUMBER(11,0),
REM Email VARCHAR2(120) PRIMARY KEY
REM );
REM }}}
CREATE SEQUENCE GROUPS_NUM;
CREATE TABLE Groups (
id NUMBER(11,0) PRIMARY KEY,
Name VARCHAR2(16),
Description VARCHAR(64)
);
CREATE OR REPLACE TRIGGER GROUPS_AUTOINCREMENT
BEFORE INSERT ON GROUPS
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT GROUPS_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM }}}
REM {{{ Table GroupMembers
CREATE SEQUENCE GROUPMEMBERS_NUM;
CREATE TABLE GroupMembers (
id NUMBER(11,0) PRIMARY KEY,
GroupId NUMBER(11,0) ,
UserId NUMBER(11,0)
);
REM }}}
CREATE OR REPLACE TRIGGER GROUPMEMBERS_AUTOINCREMENT
BEFORE INSERT ON GROUPMEMBERS
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT GROUPMEMBERS_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM {{{ Table ACL {
CREATE SEQUENCE ACL_NUM;
CREATE TABLE ACL (
id NUMBER(11,0) PRIMARY KEY,
PrincipalId NUMBER(11,0),
PrincipalType VARCHAR2(25),
Right VARCHAR2(25),
Scope VARCHAR2(25),
AppliesTo NUMBER(11,0)
);
CREATE OR REPLACE TRIGGER ACL_AUTOINCREMENT
BEFORE INSERT ON ACL
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT ACL_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM Grant everyone all rights until we get things settled
INSERT INTO ACL VALUES (NULL, 0,'Everyone','SuperUser','System',0);
REM {{{ TABLE Watchers
CREATE SEQUENCE WATCHERS_NUM;
CREATE TABLE Watchers (
id NUMBER(11,0) PRIMARY KEY,
Type VARCHAR2(16), /*One of: Requestor, Cc, AdminCc*/
Scope VARCHAR2(16), /*One of: "Queue", "Ticket" as for now*/
/*... might be extended to "Keywords", "Owners", etc.*/
Value NUMBER(11,0), /*Which Queue or Ticket this Watcher Watches.*/
Email VARCHAR2(255), /*Where this Watch should send mail. If null, use Owner->EmailAddress*/
Quiet NUMBER(11,0), /*Should we send mail to this one at all.*/
Owner NUMBER(11,0), /*A reference to the users table. If it's for a Cc or AdminCc who doesn't*/
/*have an RT account, Owner should be 0. OwnerObj->EmailAddress is */
/*usually equal to Email. Owner will be automaticly set by Watchers->Create.*/
Creator NUMBER(11,0),
Created DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE
);
CREATE OR REPLACE TRIGGER WATCHERS_AUTOINCREMENT
BEFORE INSERT ON WATCHERS
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT WATCHERS_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM {{{ Scrip system
REM TODO:
REM -steal & give (should be sent both to old and new)
REM mechanisms to avoid sending more than one mail to a party
REM dealing with CC and BCC.
REM {{{ TABLE Scrips
CREATE SEQUENCE SCRIPS_NUM;
CREATE TABLE Scrips (
id NUMBER(11,0) PRIMARY KEY, /*The ID. All IDs lower than 256 is reservated*/
/*default scrips.*/
Name VARCHAR2(255), /*Textual string*/
Description VARCHAR2(255), /*another longer textual string*/
Type VARCHAR2(60), /*Transaction type or 'any' */
Action VARCHAR2(60), /*Name of a perl module. inside RT::Action*/
DefaultTemplate NUMBER(11,0), /*Link to the templates table of template to pass in.*/
Argument VARCHAR2(255), /*an argument to pass in.*/
Creator NUMBER(11,0),
Created DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE
);
CREATE OR REPLACE TRIGGER SCRIPS_AUTOINCREMENT
BEFORE INSERT ON SCRIPS
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT SCRIPS_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM {{{ Values for Scrips
REM Short about the default available actions for subscriptions; (this should be in some docs)
REM Action - base class for any scrips.
REM SendEmail - standard thing, mails away a template
REM Notify - sends away a template to the recipient in the Scrips Argument
REM AutoReply - sends an autoreply
INSERT INTO Scrips VALUES (1,'Autoreply',
'Sends an automatic response to the requestor when a ticket was created',
'Create','AutoReply',1,'',0,NULL,0,NULL);
INSERT INTO Scrips VALUES (2,'NotifyOwnerOnTransaction',
'Sends mail to owner when anything happens',
'Any','Notify',2,'Owner',0,NULL,0,NULL);
INSERT INTO Scrips VALUES (3, 'NotifyAdminWatchersOnCorrespond',
'Sends mail to administrative watchers when email comes in',
'Correspond,Create','SendEmail',3,'',0,NULL,0,NULL);
INSERT INTO Scrips VALUES (4, 'NotifyRegularWatchersOnCorrespond',
'Send "official" mail to (external) Cc watchers and (external) requestors whenever correspondence comes in',
'Correspond','SendEmail',4,'',0,NULL,0,NULL);
INSERT INTO Scrips VALUES (5, 'NotifyAdminWatchersOnComment',
'Send mail to administrative watchers whenever comments come in',
'Comment','SendEmail',5,'AdminCc',1,NULL,1,NULL);
INSERT INTO Scrips VALUES (6, 'NotifyAllWatchersOnStatus',
'Send mail to watchers whenever a ticket''s status changes',
'Resolve/Stall/Open/Kill','SendEmail',6,'',1,NULL,1,NULL);
INSERT INTO Scrips VALUES (7, 'NotifyAllWatchersOnResolve',
'Send mail to watchers whenever a ticket''s status changes to resolved.',
'Resolve','SendEmailOnResolve',7,'',1,NULL,1,NULL);
INSERT INTO Scrips VALUES (8, 'NotifyAdminWatchersOnTransaction',
'Sends mail to owner when anything happens',
'Any','Notify',2,'AdminCc',1,NULL,1,NULL);
REM TODO: Get Notify.pm to support OldOwner + fix a template
REM INSERT INTO Scrips VALUES (20, 'NotifyOldOwnerOnSteal',
REM 'Sends mail to the old owner when the ticket is stolen',
REM 'Steal','Notify',10,'OldOwner',1,NULL,1,NULL);
REM Short about the default available link actions available;
REM Action
REM StallDependent - stalls the dependent upon a DependsOn linking action.
REM OpenDependent - when a DependsOn is resolved, the dependent should be opened.
REM Spam - upon correspondance to a group ticket, sends to all member requestors.
REM ResolveMembers - when a Group Ticket is resolved, all Members should be resolved.
REM ResolveGroupTicket - resolve the Group Ticket when all Members are resolved.
INSERT INTO Scrips VALUES (9, 'StallDependentOnLink',
'Stalls the dependent when a DependsOn link is made',
'Link', 'StallDependent',NULL,NULL,1,NULL,1,NULL);
INSERT INTO Scrips VALUES (10, 'OpenDependentOnResolve',
'Opens the dependent when the DependsOn is resolved',
'Resolve', 'OpenDependent',NULL,NULL,1,NULL,1,NULL);
INSERT INTO Scrips VALUES (11, 'ResolveMembers',
'Resolves all members when MemberOf is resolved',
'Resolve', 'ResolveMembers',NULL,NULL,1,NULL,1,NULL);
INSERT INTO Scrips VALUES (13, 'NotifyWatchersofMembers', 'Sends mail to the watchers of all tickets
which are members of this ticket',
'Correspond', 'Spam',4,NULL,1,NULL,1,NULL);
INSERT INTO Scrips VALUES (255, 'PlaceHolder', 'Custom Scrips should have higher ID', 'None',
NULL, NULL, NULL, NULL, NULL, NULL, NULL);
REM {{{ TABLE ScripScope
CREATE SEQUENCE SCRIPSCOPE_NUM;
CREATE TABLE ScripScope (
id NUMBER(11,0) PRIMARY KEY,
Scrip NUMBER(11,0), /*Scrip Id*/
Queue NUMBER(11,0), /*Queue Id 0 for global*/
/*(maybe there might be conditions where other*/
/*Scopes apply, i.e. a ticket, keyword, owner, etc?)*/
Template NUMBER(11,0), /*Template Id or 0 for default template*/
Creator NUMBER(11,0),
Created DATE,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE
);
CREATE OR REPLACE TRIGGER SCRIPSCOPE_AUTOINCREMENT
BEFORE INSERT ON SCRIPSCOPE
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT SCRIPSCOPE_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM Put all scrips at all queues:
insert INTO ScripScope select NULL, id, 0, NULL, NULL, NULL, NULL, NULL from Scrips
WHERE NAME !='NotifyAdminWatchersOnTransaction';
REM REMMED below cause cannot depend on id value, done in above where clause
REM delete from ScripScope where id=8;
REM {{{ TABLE Templates
CREATE SEQUENCE TEMPLATES_NUM;
CREATE TABLE Templates (
id NUMBER(11,0) PRIMARY KEY,
Title VARCHAR2(120),
Language VARCHAR2(16),
TranslationOf NUMBER(11,0),
/*The standard language codes should be used, things like en, en-GB*/
/*(or is it UK), en-US and fr-FR, fr-CA, etc. With 16 characters*/
/*available, it's also possible to add languages or variants not*/
/*covered by the standard. TranslationOf should poNUMBER(11,0) to the id of*/
/*the "original" template (or any other translation), and it should be*/
/*the same for all the different translations of the same content.*/
/* */
/*I'm not sure if those attributes will be used directly by the code*/
/*anywhere in the near future, but if we're going to do more content*/
/*managing (i.e. put a language attribute on every user, and*/
/*automaticly determinate what templates are appropriate to send to a*/
/*user)*/
Content CLOB,
LastUpdatedBy NUMBER(11,0),
LastUpdated DATE,
Creator NUMBER(11,0),
Created DATE
);
CREATE OR REPLACE TRIGGER TEMPLATES_AUTOINCREMENT
BEFORE INSERT ON TEMPLATES
FOR EACH ROW
DECLARE
id_number INTEGER;
BEGIN
SELECT TEMPLATES_NUM.nextval INTO ID_NUMBER FROM DUAL;
:new.id := ID_NUMBER;
END;
/
REM {{{ Values for Templates
REM Default templates
REM Should eventually be created through some init script calling on the CLI admin tools
INSERT INTO Templates VALUES (1,'Default Autoresponse Template', NULL, NULL,
UTL_RAW.CAST_TO_RAW('To: {$Ticket->RequestorsAsString}
Cc: {$Ticket->CcAsString}
Bcc: {$Ticket->AdminCcAsString}
Subject: AutoReply: {$Ticket->Subject}
Greetings,
This message has been automatically generated in response to your
message regarding \"{$Ticket->Subject()}\", the content of which appears below.
There is no need to reply to it now. The Support group has received
your message and it has been assigned a ticket ID of [{$rtname} REM{$Ticket->id()}].
Please include the string
[{$rtname} REM{$Ticket->id}]
in the subject line of all future correspondence about this
problem. To do so, you may reply to this message.
Thank you,
The Support Group
{$Ticket->Queue()->CorrespondAddress()}
-------------------------------------------------------------------------
{$Transaction->Message()->First()->Content()}'),
NULL,to_date('2000/02/27','YYYY/MM/DD'),NULL,to_date('2000/02/27','YYYY/MM/DD'));
INSERT INTO Templates VALUES (2,'Default transaction template',NULL, NULL,
UTL_RAW.CAST_TO_RAW('Reply-To: {$Ticket->Queue->CommentAddress} (Replies will be entered as comments)
{$Transaction->CreatedAsString}: Request {$Ticket->id} was acted upon.
Transaction: {$Transaction->Description}
Queue: {$Ticket->Queue->QueueId}
Subject: {my $z=$Transaction->Message()->First(); $z ? $z->Subject : "(No subject given)"}
Owner: {$Ticket->Owner ? $Ticket->Owner->UserId : "Nobody"}
Requestors: {join(",",@{$Ticket->Requestors->Emails()})}
Status: {$Ticket->Status}
Ticket <URL: {$RT::WebURL}/Ticket/Display.html?id={$Ticket->id} >
-------------------------------------------------------------------------
{$Transaction->Message()->First()->Content() if $Transaction->Message()->First()}'),
NULL,to_date('2000/02/27','YYYY/MM/DD'),NULL,to_date('2000/02/27','YYYY/MM/DD'));
INSERT INTO Templates VALUES (3,'Default admin correspondence template', NULL, NULL,
UTL_RAW.CAST_TO_RAW('Subject: {$Transaction->Message()->First()->Subject}
Content-Type: text/plain; charset=ISO-8859-1
To: {$Ticket->AdminCcAsString}
{$Transaction->IsInbound() ? "In" : "Out"}bound correspondence:
<URL: {$RT::WebURL}/Ticket/Display.html?id={$Ticket->id} >
{$Transaction->Message()->First()->Content()}'),
NULL,to_date('2000/02/27','YYYY/MM/DD'),NULL,to_date('2000/02/27','YYYY/MM/DD'));
INSERT INTO Templates VALUES (4,'Default Correspondence template for requestors', NULL, NULL,
UTL_RAW.CAST_TO_RAW('Subject: {my $z=$Transaction->Message()->First(); $z ? $z->Subject : "(No subject given)"}
To: {$Ticket->RequestorsAsString}
Cc: {$Ticket->CcAsString}
Content-Type: text/plain; charset=ISO-8859-1
{$Transaction->Message()->First()->Content()}'),
NULL,to_date('2000/02/27','YYYY/MM/DD'),NULL,to_date('2000/02/27','YYYY/MM/DD'));
INSERT INTO Templates VALUES (5,'Default Administrative Comment template', NULL, NULL,
UTL_RAW.CAST_TO_RAW('Subject: [Comment] {my $z=$Transaction->Message()->First(); return "(...)" unless $z; my $s=$z->Subject; $s =~ s/\[Comment\]//g; $s;}
Reply-To: {$Ticket->Queue->CommentAddress}
Content-Type: text/plain; charset=ISO-8859-1
To: {$Ticket->AdminCcAsString}
{$RT::WebURL}/Ticket/Display.html?id={$Ticket->id}
This is a comment. It is not sent to the Requestor(s):
{$Transaction->Message()->First()->Content()}'),
NULL,to_date('2000/02/27','YYYY/MM/DD'),NULL,to_date('2000/02/27','YYYY/MM/DD'));
INSERT INTO Templates VALUES (6,'Status Change', NULL, NULL,
UTL_RAW.CAST_TO_RAW('Subject: Status Changed to: {$Transaction->NewValue}
Reply-To: {$Ticket->Queue->CorrespondAddress}
Content-Type: text/plain; charset=ISO-8859-1
To: {$Ticket->WatchersAsString}
{$RT::WebURL}/Ticket/Display.html?id={$Ticket->id}
{$Transaction->Message()->First()->Content()}'),
NULL,to_date('2000/02/27','YYYY/MM/DD'),NULL,to_date('2000/02/27','YYYY/MM/DD'));
INSERT INTO Templates VALUES (7,'Ticket Resolved', NULL, NULL,
UTL_RAW.CAST_TO_RAW('Subject: Ticket Resolved
Reply-To: {$Ticket->Queue->CorrespondAddress}
Content-Type: text/plain; charset=ISO-8859-1
To: {$Ticket->RequestorsAsString}
Cc: {$Ticket->CcAsString}
Your request has been resolved. We hope you''re happy.'),
NULL,to_date('2000/02/27','YYYY/MM/DD'),NULL,to_date('2000/02/27','YYYY/MM/DD'));
REM }}}
REM }}}
exit;
REM {{{ Stubbed work
REM For storing who we have sent autoreplies to, so we don't overload
REM somebody with those autoreplies. Take a look at Action/AutoReply.pm
REM (postponed until post-2.0)
REM create table AutoReplies (
REM Email VARCHAR2(255),
REM Template NUMBER(11,0),
REM Created DATE
REM);
REM }}}
----- End forwarded message -----
--
jesse reed vincent --- root at eruditorum.org --- jesse at fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
-------------------------------------------------------------
This is scary. I'm imagining tracerouting you and seeing links like "Route
84" and "Route 9, Exit 14". Obviously, this is illness induced.
--Cana McCoy
More information about the Rt-devel
mailing list