[rt-devel] Blast from the Past

[Jesse Vincent]

Date: Sat, 27 Feb 1999 23:43:04 -0800
From: Jesse <jesse at fsck.com>
To: rt-devel at skylight.fsck.com
Subject: Planning for RT2


So I've started to plan for rt2, as I'm planning to release 1.0.0 in
the next several weeks.  The 1.0 series will continue as a
bugfix-track. rt2 will be a COMPLETE rewrite.  Frankly, I'm embarassed
by the current code...it must perish.

rt2 needs to be a good deal more flexible than what we've got now.
I'd like to see the following changes:

	DBI Backend,
	Object-based architecture,
	MIME message handling,
	Get rid of setuidness, if possible,
	Per-queue configurable schema,
	Seperate access to queues from notifications,
	Move transaction content into the database,
	Move mail templates to the database.
	User-mode ticket browsing tool.
	More configurable web ui.
	Cookies-based authentication.



To this end, I've started to put together a schema.  it's not
complete by any means, but I'd love input.


CREATE TABLE ticket (
	id int(11) AUTO_INCREMENT PRIMARY KEY,
	alias char(24),			#a textual name for this ticket.
	parent int(11),			#the id of this ticket's id
	queue char(24) NOT NULL,	#the id of the queue this ticket's in
	title char(127) NOT NULL,	#subject of this fact
	priority int(11),		#0 to 99 relative priority
	due date,			#date due
	created date,			#date created
	modified date,			#date last modified
	time_taken int(11), #in minutes
	requestor_notified date,	
	state ('submitted','reviewed',''stalled','deferred','resolved','historical'),
	primary_key (id)
);


CREATE TABLE transaction (
	id int(11) AUTO_INCREMENT PRIMARY KEY,
	content blob,
	content_type char(64),
	time_taken int(11),
	ticket int(11) NOT NULL
);

CREATE TABLE action (

	id int(11) AUTO_INCREMENT PRIMARY KEY,
	transaction int(11) NOT NULL,
	col char(24) NOT NULL,
	coltype enum('core','extension'),
	id newval char(256)
);


CREATE TABLE ticket_notificants (
	
	ticket int(11) NOT NULL,
	role enum('requestor','owner',3rdparty'),
	person char(80) NOT NULL,
	on enum('state', 'correspond','comment', 'transaction'),
	template char(24) NOT NULL
	
);


CREATE TABLE ticket_access (
	ticket int(11) NOT NULL,
	person char(80) NOT NULL,
	access enum('see','view','modify','admin')
);

CREATE TABLE queue_access (
	queue char(24) NOT NULL,
	person char(80) NOT NULL,
	access enum('see','view','modify','admin')
);

CREATE TABLE queue_notificants (
	
	ticket int(11) NOT NULL,
	person char(80) NOT NULL,
	on enum('state', 'correspond', 'comment','transaction'),
	template char(24) NOT NULL
);


CREATE TABLE queue (
	email char(127) not null,
	name char(64) not null,
 	id char(24),
	primary key (id),
	extended_fields char(24),	 #the name of a table which has the queue's extended schema
);


CREATE TABLE templates (
 	id int(11) AUTO_INCREMENT NOT NULL,
	name char(24) NOT NULL,
	content BLOB,
	embedded_perl BOOL
);

CREATE TABLE users (
	email char(127) not null,
	login char(32),
	password char(32),
	realname char(127),
	primary key (email)
);