diff options
Diffstat (limited to 'rt/etc/schema.mysql')
| -rwxr-xr-x | rt/etc/schema.mysql | 89 |
1 files changed, 21 insertions, 68 deletions
diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql index b7d53f884..46f8ec562 100755 --- a/rt/etc/schema.mysql +++ b/rt/etc/schema.mysql @@ -16,6 +16,7 @@ CREATE TABLE Attachments ( PRIMARY KEY (id) ) TYPE=InnoDB; +CREATE INDEX Attachments1 ON Attachments (Parent) ; CREATE INDEX Attachments2 ON Attachments (TransactionId) ; CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ; # }}} @@ -58,9 +59,9 @@ CREATE TABLE Links ( 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) ; -CREATE INDEX Links4 ON Links (Type,LocalBase); # }}} @@ -86,12 +87,12 @@ CREATE TABLE Groups ( Description varchar(255) NULL , Domain varchar(64), Type varchar(64), - Instance integer, + Instance varchar(64), PRIMARY KEY (id) ) TYPE=InnoDB; CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id); -CREATE INDEX Groups2 On Groups (Type, Instance); +CREATE INDEX Groups2 On Groups (Type, Instance, Domain); # }}} @@ -117,23 +118,21 @@ CREATE TABLE ScripConditions ( # {{{ Transactions CREATE TABLE Transactions ( id INTEGER NOT NULL AUTO_INCREMENT, - ObjectType varchar(64) NOT NULL, - ObjectId integer NOT NULL DEFAULT 0 , + EffectiveTicket integer NOT NULL DEFAULT 0 , + Ticket integer NOT NULL DEFAULT 0 , TimeTaken integer NOT NULL DEFAULT 0 , Type varchar(20) NULL , Field varchar(40) NULL , OldValue varchar(255) NULL , NewValue varchar(255) NULL , - ReferenceType varchar(255) NULL, - OldReference integer NULL , - NewReference integer NULL , - Data varchar(255) NULL , + Data varchar(100) NULL , Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , PRIMARY KEY (id) ) TYPE=InnoDB; -CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId); +CREATE INDEX Transactions1 ON Transactions (Ticket); +CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); # }}} @@ -211,6 +210,7 @@ create table CachedGroupMembers ( ) TYPE=InnoDB; CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled); +CREATE INDEX GrouMem on CachedGroupMembers (GroupId,MemberId); # }}} @@ -257,6 +257,8 @@ CREATE TABLE Users ( CREATE UNIQUE INDEX Users1 ON Users (Name) ; +CREATE INDEX Users2 ON Users (Name); +CREATE INDEX Users3 ON Users (id, EmailAddress); CREATE INDEX Users4 ON Users (EmailAddress); @@ -297,6 +299,9 @@ CREATE TABLE Tickets ( 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) ; # }}} @@ -338,31 +343,21 @@ CREATE TABLE Templates ( # }}} -# {{{ ObjectCustomFieldValues +# {{{ TicketCustomFieldValues -CREATE TABLE ObjectCustomFieldValues ( +CREATE TABLE TicketCustomFieldValues ( id INTEGER NOT NULL AUTO_INCREMENT, + Ticket int NOT NULL , CustomField int NOT NULL , - ObjectType varchar(255) NOT NULL, # Final target of the Object - ObjectId int NOT NULL , # New -- Replaces Ticket - SortOrder integer NOT NULL DEFAULT 0 , # New -- ordering for multiple values - Content varchar(255) NULL , - LargeContent LONGTEXT NULL, # New -- to hold 255+ strings - ContentType varchar(80) NULL, # New -- only text/* gets searched - ContentEncoding varchar(80) NULL , # New -- for binary Content Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , - Disabled int2 NOT NULL DEFAULT 0 , # New -- whether the value was current PRIMARY KEY (id) ) TYPE=InnoDB; -CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content); -CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); - # }}} # {{{ CustomFields @@ -370,13 +365,10 @@ CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,Ob CREATE TABLE CustomFields ( id INTEGER NOT NULL AUTO_INCREMENT, Name varchar(200) NULL , - Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out - MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0) - Pattern varchar(255) NULL , # New -- Must validate against this - Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry + Type varchar(200) NULL , + Queue integer NOT NULL DEFAULT 0 , Description varchar(255) NULL , SortOrder integer NOT NULL DEFAULT 0 , - LookupType varchar(255) NOT NULL, Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , @@ -386,22 +378,8 @@ CREATE TABLE CustomFields ( PRIMARY KEY (id) ) TYPE=InnoDB; -# }}} - -# {{{ ObjectCustomFields +CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue); -CREATE TABLE ObjectCustomFields ( - id INTEGER NOT NULL AUTO_INCREMENT, - CustomField int NOT NULL , - ObjectId integer NOT 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; # }}} @@ -421,31 +399,6 @@ CREATE TABLE CustomFieldValues ( PRIMARY KEY (id) ) TYPE=InnoDB; -CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); - -# }}} - - -# {{{ Attributes - -CREATE TABLE Attributes ( - id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(255) NULL , - Description varchar(255) NULL , - Content text, - ContentType varchar(16), - ObjectType varchar(64), - ObjectId integer, # foreign key to anything - Creator integer NOT NULL DEFAULT 0 , - Created DATETIME NULL , - LastUpdatedBy integer NOT NULL DEFAULT 0 , - LastUpdated DATETIME NULL , - PRIMARY KEY (id) -) TYPE=InnoDB; - -CREATE INDEX Attributes1 on Attributes(Name); -CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId); - # }}} # {{{ Sessions |