diff options
Diffstat (limited to 'rt/etc/schema.mysql')
| -rwxr-xr-x | rt/etc/schema.mysql | 429 |
1 files changed, 140 insertions, 289 deletions
diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql index 46f8ec562..7e715c201 100755 --- a/rt/etc/schema.mysql +++ b/rt/etc/schema.mysql @@ -1,9 +1,21 @@ -# {{{ Attachments - +CREATE TABLE KeywordSelects ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(255) NULL , + Keyword integer NULL , + Single integer NULL , + Depth integer NOT NULL DEFAULT 0 , + ObjectType varchar(32) NOT NULL , + ObjectField varchar(32) NULL , + ObjectValue varchar(255) NULL , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword); +CREATE INDEX KeywordSelects2 ON KeywordSelects (ObjectType, ObjectField, ObjectValue); CREATE TABLE Attachments ( id INTEGER NOT NULL AUTO_INCREMENT, TransactionId integer NOT NULL , - Parent integer NOT NULL DEFAULT 0 , + Parent integer NULL , MessageId varchar(160) NULL , Subject varchar(255) NULL , Filename varchar(255) NULL , @@ -11,222 +23,161 @@ CREATE TABLE Attachments ( ContentEncoding varchar(80) NULL , Content LONGTEXT NULL , Headers LONGTEXT NULL , - Creator integer NOT NULL DEFAULT 0 , + Creator integer NULL , Created DATETIME NULL , PRIMARY KEY (id) -) TYPE=InnoDB; - -CREATE INDEX Attachments1 ON Attachments (Parent) ; -CREATE INDEX Attachments2 ON Attachments (TransactionId) ; -CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ; -# }}} - -# {{{ Queues +); +CREATE INDEX Attachments1 ON Attachments (Parent); +CREATE INDEX Attachments2 ON Attachments (TransactionId); +CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId); CREATE TABLE Queues ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(200) NOT NULL , - Description varchar(255) NULL , + Name varchar(120) NOT NULL , + Description varchar(120) NULL , CorrespondAddress varchar(120) NULL , CommentAddress varchar(120) NULL , - InitialPriority integer NOT NULL DEFAULT 0 , - FinalPriority integer NOT NULL DEFAULT 0 , - DefaultDueIn integer NOT NULL DEFAULT 0 , - Creator integer NOT NULL DEFAULT 0 , + InitialPriority integer NULL , + FinalPriority integer NULL , + DefaultDueIn integer NULL , + Creator integer NULL , Created DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 , PRIMARY KEY (id) -) TYPE=InnoDB; -CREATE UNIQUE INDEX Queues1 ON Queues (Name) ; -CREATE INDEX Queues2 ON Queues (Disabled) ; - -# }}} - -# {{{ Links - +); +CREATE UNIQUE INDEX Queues1 ON Queues (Name); CREATE TABLE Links ( id INTEGER NOT NULL AUTO_INCREMENT, Base varchar(240) NULL , Target varchar(240) NULL , Type varchar(20) NOT NULL , - LocalTarget integer NOT NULL DEFAULT 0 , - LocalBase integer NOT NULL DEFAULT 0 , - LastUpdatedBy integer NOT NULL DEFAULT 0 , + LocalTarget integer NULL , + LocalBase integer NULL , + LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , - Creator integer NOT NULL DEFAULT 0 , + Creator integer NULL , Created DATETIME NULL , PRIMARY KEY (id) -) TYPE=InnoDB; - -CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ; -CREATE INDEX Links2 ON Links (Base, Type) ; -CREATE INDEX Links3 ON Links (Target, Type) ; - -# }}} - -# {{{ Principals - -CREATE TABLE Principals ( - id INTEGER AUTO_INCREMENT not null, - PrincipalType VARCHAR(16) not null, - ObjectId integer, # foreign key to Users or Groups, depending - Disabled int2 NOT NULL DEFAULT 0 , - PRIMARY KEY (id) -) TYPE=InnoDB; - -CREATE INDEX Principals2 ON Principals (ObjectId); - -# }}} - -# {{{ Groups - +); +CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type); CREATE TABLE Groups ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(200) NULL , - Description varchar(255) NULL , - Domain varchar(64), - Type varchar(64), - Instance varchar(64), + Name varchar(16) NULL , + Description varchar(64) NULL , + Pseudo integer NOT NULL DEFAULT 0 , PRIMARY KEY (id) -) TYPE=InnoDB; - -CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id); -CREATE INDEX Groups2 On Groups (Type, Instance, Domain); - -# }}} - -# {{{ ScripConditions - +); +CREATE UNIQUE INDEX Groups1 ON Groups (Name); +CREATE TABLE Watchers ( + id INTEGER NOT NULL AUTO_INCREMENT, + Type varchar(16) NULL , + Scope varchar(16) NULL , + Value integer NULL , + Email varchar(255) NULL , + Quiet integer NULL , + Owner integer NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +); +CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner); CREATE TABLE ScripConditions ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(200) NULL , + Name varchar(255) NULL , Description varchar(255) NULL , ExecModule varchar(60) NULL , Argument varchar(255) NULL , ApplicableTransTypes varchar(60) NULL , - - Creator integer NOT NULL DEFAULT 0 , + Creator integer NULL , Created DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , PRIMARY KEY (id) -) TYPE=InnoDB; - -# }}} - -# {{{ Transactions +); CREATE TABLE Transactions ( id INTEGER NOT NULL AUTO_INCREMENT, - EffectiveTicket integer NOT NULL DEFAULT 0 , - Ticket integer NOT NULL DEFAULT 0 , - TimeTaken integer NOT NULL DEFAULT 0 , + EffectiveTicket integer NULL , + Ticket integer NULL , + TimeTaken integer NULL , Type varchar(20) NULL , Field varchar(40) NULL , OldValue varchar(255) NULL , NewValue varchar(255) NULL , Data varchar(100) NULL , - - Creator integer NOT NULL DEFAULT 0 , + Creator integer NULL , Created DATETIME NULL , PRIMARY KEY (id) -) TYPE=InnoDB; +); CREATE INDEX Transactions1 ON Transactions (Ticket); CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); - -# }}} - -# {{{ Scrips - CREATE TABLE Scrips ( id INTEGER NOT NULL AUTO_INCREMENT, - Description varchar(255), - ScripCondition integer NOT NULL DEFAULT 0 , - ScripAction integer NOT NULL DEFAULT 0 , - ConditionRules text NULL , - ActionRules text NULL , - CustomIsApplicableCode text NULL , - CustomPrepareCode text NULL , - CustomCommitCode text NULL , + ScripCondition integer NULL , + ScripAction integer NULL , Stage varchar(32) NULL , - Queue integer NOT NULL DEFAULT 0 , - Template integer NOT NULL DEFAULT 0 , - Creator integer NOT NULL DEFAULT 0 , + Queue integer NULL , + Template integer NULL , + Creator integer NULL , Created DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , PRIMARY KEY (id) -) TYPE=InnoDB; - -# }}} - -# {{{ ACL +); CREATE TABLE ACL ( id INTEGER NOT NULL AUTO_INCREMENT, - PrincipalType varchar(25) NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor", "Requestor" - - PrincipalId integer NOT NULL , #Foreign key to principals - RightName varchar(25) NOT NULL , - ObjectType varchar(25) NOT NULL , - ObjectId integer NOT NULL default 0, - DelegatedBy integer NOT NULL default 0, #foreign key to principals with a userid - DelegatedFrom integer NOT NULL default 0, #foreign key to ACL + PrincipalId integer NULL , + PrincipalType varchar(25) NULL , + RightName varchar(25) NULL , + RightScope varchar(25) NULL , + RightAppliesTo integer NULL , PRIMARY KEY (id) -) TYPE=InnoDB; - -CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId); - -# }}} - -# {{{ GroupMembers - +); +CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId); +CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId); CREATE TABLE GroupMembers ( id INTEGER NOT NULL AUTO_INCREMENT, - GroupId integer NOT NULL DEFAULT 0, - MemberId integer NOT NULL DEFAULT 0, #Foreign key to Principals + GroupId integer NULL , + UserId integer NULL , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId); +CREATE TABLE ObjectKeywords ( + id INTEGER NOT NULL AUTO_INCREMENT, + Keyword integer NOT NULL , + KeywordSelect integer NOT NULL , + ObjectType varchar(32) NOT NULL , + ObjectId integer NOT NULL , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords (ObjectId, ObjectType, KeywordSelect, Keyword); +CREATE INDEX ObjectKeywords2 ON ObjectKeywords (ObjectId, ObjectType); +CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword); +CREATE TABLE Keywords ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(255) NOT NULL , + Description varchar(255) NULL , + Parent integer NULL , + Disabled int2 NOT NULL DEFAULT 0 , PRIMARY KEY (id) -) TYPE=InnoDB; -CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId); - - -# }}} - -# {{{ GroupMembersCache - -create table CachedGroupMembers ( - id int auto_increment, - GroupId int, # foreign key to Principals - MemberId int, # foreign key to Principals - Via int, #foreign key to CachedGroupMembers. (may point to $self->id) - ImmediateParentId int, #foreign key to prinicpals. - # this points to the group that the member is - # a member of, for ease of deletes. - Disabled int2 NOT NULL DEFAULT 0 , # if this cached group member is a member of this group by way of a disabled - # group or this group is disabled, this will be set to 1 - # this allows us to not find members of disabled subgroups when listing off - # group members recursively. - # Also, this allows us to have the ACL system elide members of disabled groups - PRIMARY KEY (id) -) TYPE=InnoDB; - -CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled); -CREATE INDEX GrouMem on CachedGroupMembers (GroupId,MemberId); - -# }}} - -# {{{ Users - +); +CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent); +CREATE INDEX Keywords2 ON Keywords (Name); +CREATE INDEX Keywords3 ON Keywords (Parent); CREATE TABLE Users ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(200) NOT NULL , + Name varchar(120) NOT NULL , Password varchar(40) NULL , Comments blob NULL , Signature blob NULL , EmailAddress varchar(120) NULL , FreeformContactInfo blob NULL , Organization varchar(200) NULL , + Privileged integer NULL , RealName varchar(120) NULL , - NickName varchar(16) NULL , + Nickname varchar(16) NULL , Lang varchar(16) NULL , EmailEncoding varchar(16) NULL , WebEncoding varchar(16) NULL , @@ -245,172 +196,72 @@ CREATE TABLE Users ( State varchar(100) NULL , Zip varchar(16) NULL , Country varchar(50) NULL , - Timezone varchar(50) NULL , - PGPKey text NULL, - - Creator integer NOT NULL DEFAULT 0 , + Creator integer NULL , Created DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , + Disabled int2 NOT NULL DEFAULT 0 , PRIMARY KEY (id) -) TYPE=InnoDB; - - -CREATE UNIQUE INDEX Users1 ON Users (Name) ; -CREATE INDEX Users2 ON Users (Name); +); +CREATE UNIQUE INDEX Users1 ON Users (Name); CREATE INDEX Users3 ON Users (id, EmailAddress); CREATE INDEX Users4 ON Users (EmailAddress); - - -# }}} - -# {{{ Tickets - CREATE TABLE Tickets ( id INTEGER NOT NULL AUTO_INCREMENT, - EffectiveId integer NOT NULL DEFAULT 0 , - Queue integer NOT NULL DEFAULT 0 , + EffectiveId integer NULL , + Queue integer NULL , Type varchar(16) NULL , - IssueStatement integer NOT NULL DEFAULT 0 , - Resolution integer NOT NULL DEFAULT 0 , - Owner integer NOT NULL DEFAULT 0 , + IssueStatement integer NULL , + Resolution integer NULL , + Owner integer NULL , Subject varchar(200) NULL DEFAULT '[no subject]' , - InitialPriority integer NOT NULL DEFAULT 0 , - FinalPriority integer NOT NULL DEFAULT 0 , - Priority integer NOT NULL DEFAULT 0 , - TimeEstimated integer NOT NULL DEFAULT 0 , - TimeWorked integer NOT NULL DEFAULT 0 , + InitialPriority integer NULL , + FinalPriority integer NULL , + Priority integer NULL , Status varchar(10) NULL , - TimeLeft integer NOT NULL DEFAULT 0 , + TimeWorked integer NULL , + TimeLeft integer NULL , Told DATETIME NULL , Starts DATETIME NULL , Started DATETIME NULL , Due DATETIME NULL , Resolved DATETIME NULL , - - - LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , - Creator integer NOT NULL DEFAULT 0 , + Creator integer NULL , Created DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 , PRIMARY KEY (id) -) TYPE=InnoDB; - -CREATE INDEX Tickets1 ON Tickets (Queue, Status) ; -CREATE INDEX Tickets2 ON Tickets (Owner) ; -CREATE INDEX Tickets3 ON Tickets (EffectiveId) ; -CREATE INDEX Tickets4 ON Tickets (id, Status) ; -CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ; -CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ; - -# }}} - -# {{{ ScripActions - +); +CREATE INDEX Tickets1 ON Tickets (Queue, Status); +CREATE INDEX Tickets2 ON Tickets (Owner); +CREATE INDEX Tickets3 ON Tickets (EffectiveId); +CREATE INDEX Tickets4 ON Tickets (id, Status); +CREATE INDEX Tickets5 ON Tickets (id, EffectiveId); CREATE TABLE ScripActions ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(200) NULL , + Name varchar(255) NULL , Description varchar(255) NULL , ExecModule varchar(60) NULL , Argument varchar(255) NULL , - Creator integer NOT NULL DEFAULT 0 , + Creator integer NULL , Created DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdatedBy integer NULL , LastUpdated DATETIME NULL , PRIMARY KEY (id) -) TYPE=InnoDB; - -# }}} - -# {{{ Templates - +); CREATE TABLE Templates ( id INTEGER NOT NULL AUTO_INCREMENT, Queue integer NOT NULL DEFAULT 0 , - Name varchar(200) NOT NULL , - Description varchar(255) NULL , + Name varchar(40) NOT NULL , + Description varchar(120) NULL , Type varchar(16) NULL , Language varchar(16) NULL , - TranslationOf integer NOT NULL DEFAULT 0 , + TranslationOf integer NULL , Content blob NULL , LastUpdated DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , - Creator integer NOT NULL DEFAULT 0 , - Created DATETIME NULL , - PRIMARY KEY (id) -) TYPE=InnoDB; - -# }}} - -# {{{ TicketCustomFieldValues - -CREATE TABLE TicketCustomFieldValues ( - id INTEGER NOT NULL AUTO_INCREMENT, - Ticket int NOT NULL , - CustomField int NOT NULL , - Content varchar(255) NULL , - - Creator integer NOT NULL DEFAULT 0 , + LastUpdatedBy integer NULL , + Creator integer NULL , Created DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , - LastUpdated DATETIME NULL , - PRIMARY KEY (id) -) TYPE=InnoDB; - -# }}} - -# {{{ CustomFields - -CREATE TABLE CustomFields ( - id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(200) NULL , - Type varchar(200) NULL , - Queue integer NOT NULL DEFAULT 0 , - Description varchar(255) NULL , - SortOrder integer NOT NULL DEFAULT 0 , - - Creator integer NOT NULL DEFAULT 0 , - Created DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , - LastUpdated DATETIME NULL , - Disabled int2 NOT NULL DEFAULT 0 , - PRIMARY KEY (id) -) TYPE=InnoDB; - -CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue); - - -# }}} - -# {{{ CustomFieldValues - -CREATE TABLE CustomFieldValues ( - id INTEGER NOT NULL AUTO_INCREMENT, - CustomField int NOT NULL , - Name varchar(200) NULL , - Description varchar(255) NULL , - SortOrder integer NOT NULL DEFAULT 0 , - - Creator integer NOT NULL DEFAULT 0 , - Created DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , - LastUpdated DATETIME NULL , PRIMARY KEY (id) -) TYPE=InnoDB; - -# }}} - -# {{{ Sessions - -# sessions is used by Apache::Session to keep sessions in the database. -# We should have a reaper script somewhere. - -CREATE TABLE sessions ( - id char(32) NOT NULL, - a_session LONGTEXT, - LastUpdated TIMESTAMP, - PRIMARY KEY (id) ); - -# }}} |