[rt-devel] Oracle Port progress

Dave Morgan dmorgan at bartertrust.com
Tue Aug 29 16:01:33 EDT 2000


Hi All,
	i have made fairly substantial progress in the Oracle port, in
	fact I am running up against limitations due to stubs and
	unimplemented features.

	I have attached a new schema file as I had not updated the Tickets table
	(my apologies). There is also one more change in the In DBIx/EasySearch.pm
	file:
		Routine _DoSearch
	Change
	#       $QueryString = "SELECT distinct main.* FROM " . $self->_TableAliases;
        	$QueryString = "SELECT main.* FROM " . $self->_TableAliases;

	Since all tables have primary keys I believe the DISTINCT clause is
	redundant. If I am wrong please let me know.

	Functionality that has been tested this week
		rt -response
		rt -comments
		rt -resolve
		rt -stall
		rt -open
		rt -due
		rt -show
		rt -subject
		rt -queue
		rt -prio
		rt -finalprio
		rt -notify
		rt -kill
HTH
Dave

-- 
Dave Morgan
Senior Database Administrator
Internet Barter Inc.
www.bartertrust.com
408-982-8774
-------------- next part --------------
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,
	TimeLeft 		NUMBER(11,0) DEFAULT 0,
  	Told 			DATE,
	Starts			DATE,
	Started			DATE,
  	Due 			DATE,
	Resolved		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 }}}


More information about the Rt-devel mailing list