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)
);