X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=rt%2Fetc%2Fschema.mysql;h=da14e72eda9764a3966e3d9243ed53b9059719c7;hp=c313aaf540d76c7ec5c65397b03c6790598666e6;hb=681a340f6be4184b1472a8e1fa9cd5d074f6f325;hpb=6587f6ba7d047ddc1686c080090afe7d53365bd4 diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql index c313aaf54..da14e72ed 100644 --- a/rt/etc/schema.mysql +++ b/rt/etc/schema.mysql @@ -22,9 +22,9 @@ CREATE TABLE Queues ( id INTEGER NOT NULL AUTO_INCREMENT, Name varchar(200) NOT NULL , Description varchar(255) NULL , - CorrespondAddress varchar(120) CHARACTER SET ascii NULL, - CommentAddress varchar(120) CHARACTER SET ascii NULL, - Lifecycle varchar(32) CHARACTER SET ascii NULL, + CorrespondAddress varchar(120) NULL, + CommentAddress varchar(120) NULL, + Lifecycle varchar(32) NULL, SubjectTag varchar(120) NULL, InitialPriority integer NOT NULL DEFAULT 0 , FinalPriority integer NOT NULL DEFAULT 0 , @@ -46,7 +46,7 @@ CREATE TABLE Links ( id INTEGER NOT NULL AUTO_INCREMENT, Base varchar(240) NULL, Target varchar(240) NULL, - Type varchar(20) NOT NULL, + Type varchar(20) CHARACTER SET ascii NOT NULL , LocalTarget integer NOT NULL DEFAULT 0 , LocalBase integer NOT NULL DEFAULT 0 , LastUpdatedBy integer NOT NULL DEFAULT 0 , @@ -54,7 +54,7 @@ CREATE TABLE Links ( Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , PRIMARY KEY (id) -) ENGINE=InnoDB CHARACTER SET ascii; +) ENGINE=InnoDB CHARACTER SET utf8; CREATE INDEX Links2 ON Links (Base, Type) ; CREATE INDEX Links3 ON Links (Target, Type) ; @@ -88,10 +88,9 @@ CREATE TABLE Groups ( PRIMARY KEY (id) ) ENGINE=InnoDB CHARACTER SET utf8; -CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id); -CREATE INDEX Groups2 On Groups (Type, Instance); - - +CREATE INDEX Groups1 ON Groups (Domain, Type, Instance); +CREATE INDEX Groups2 ON Groups (Domain, Name, Instance); +CREATE INDEX Groups3 On Groups (Instance); CREATE TABLE ScripConditions ( id INTEGER NOT NULL AUTO_INCREMENT, @@ -137,14 +136,25 @@ CREATE TABLE Scrips ( 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 , - Stage varchar(32) CHARACTER SET ascii NULL , - Queue integer NOT NULL DEFAULT 0 , - Template integer NOT NULL DEFAULT 0 , + Disabled int2 NOT NULL DEFAULT 0 , + Template varchar(200) NOT NULL , + Creator integer NOT NULL DEFAULT 0 , + Created DATETIME NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +) ENGINE=InnoDB CHARACTER SET utf8; + +CREATE TABLE ObjectScrips ( + id INTEGER NOT NULL AUTO_INCREMENT, + Scrip integer NOT NULL , + Stage varchar(32) CHARACTER SET ascii NOT NULL DEFAULT 'TransactionCreate', + 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 , @@ -152,6 +162,7 @@ CREATE TABLE Scrips ( PRIMARY KEY (id) ) ENGINE=InnoDB CHARACTER SET utf8; +CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip); CREATE TABLE ACL ( id INTEGER NOT NULL AUTO_INCREMENT, @@ -204,6 +215,7 @@ create table CachedGroupMembers ( ) ENGINE=InnoDB CHARACTER SET utf8; CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled); +CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled); CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId); @@ -240,6 +252,7 @@ CREATE TABLE Users ( Country varchar(50) NULL , Timezone varchar(50) NULL , PGPKey text NULL, + SMIMECertificate text NULL, Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , @@ -258,6 +271,7 @@ CREATE INDEX Users4 ON Users (EmailAddress); CREATE TABLE Tickets ( id INTEGER NOT NULL AUTO_INCREMENT, EffectiveId integer NOT NULL DEFAULT 0 , + IsMerged int2 NULL DEFAULT NULL, Queue integer NOT NULL DEFAULT 0 , Type varchar(16) CHARACTER SET ascii NULL , IssueStatement integer NOT NULL DEFAULT 0 , @@ -313,8 +327,6 @@ CREATE TABLE Templates ( Name varchar(200) NOT NULL , Description varchar(255) NULL , Type varchar(16) CHARACTER SET ascii NULL , - Language varchar(16) CHARACTER SET ascii NULL , - TranslationOf integer NOT NULL DEFAULT 0 , Content TEXT NULL , LastUpdated DATETIME NULL , LastUpdatedBy integer NOT NULL DEFAULT 0 , @@ -328,14 +340,14 @@ CREATE TABLE Templates ( CREATE TABLE ObjectCustomFieldValues ( id INTEGER NOT NULL AUTO_INCREMENT, CustomField int NOT NULL , - ObjectType varchar(255) CHARACTER SET ascii NOT NULL, # Final target of the Object - ObjectId int NOT NULL , # New -- Replaces Ticket + ObjectType varchar(255) CHARACTER SET ascii 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 LONGBLOB NULL, # New -- to hold 255+ strings - ContentType varchar(80) CHARACTER SET ascii NULL, # New -- only text/* gets searched - ContentEncoding varchar(80) CHARACTER SET ascii NULL , # New -- for binary Content + LargeContent LONGBLOB NULL, # New -- to hold 255+ strings + ContentType varchar(80) CHARACTER SET ascii NULL, # New -- only text/* gets searched + ContentEncoding varchar(80) CHARACTER SET ascii NULL , # New -- for binary Content Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , @@ -353,11 +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) CHARACTER SET ascii NULL , # Changed -- 'Single' and 'Multiple' is moved out + Type varchar(200) CHARACTER SET ascii NULL , # Changed -- 'Single' and 'Multiple' is moved out RenderType varchar(64) CHARACTER SET ascii NULL , - MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0) - Pattern TEXT NULL , # New -- Must validate against this - Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry + MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0) + Pattern TEXT NULL , # New -- Must validate against this BasedOn INTEGER NULL, ValuesClass varchar(64) CHARACTER SET ascii NULL , Description varchar(255) NULL , @@ -413,7 +424,7 @@ CREATE TABLE Attributes ( id INTEGER NOT NULL AUTO_INCREMENT, Name varchar(255) NULL , Description varchar(255) NULL , - Content BLOB, + Content LONGBLOB, ContentType varchar(16) CHARACTER SET ascii, ObjectType varchar(64) CHARACTER SET ascii, ObjectId integer, # foreign key to anything