RT 4.2.11, ticket#13852

[freeside.git] / rt / etc / schema.mysql

diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql
index e0ccecb..da14e72 100644 (file)
--- a/rt/etc/schema.mysql
+++ b/rt/etc/schema.mysql
@@ -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  ,