From 3ef62a0570055da710328937e7f65dbb2c027c62 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 12 Aug 2002 06:17:09 +0000 Subject: import rt 2.0.14 --- rt/etc/schema.mysql | 267 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 267 insertions(+) create mode 100755 rt/etc/schema.mysql (limited to 'rt/etc/schema.mysql') diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql new file mode 100755 index 000000000..7e715c201 --- /dev/null +++ b/rt/etc/schema.mysql @@ -0,0 +1,267 @@ +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 NULL , + MessageId varchar(160) NULL , + Subject varchar(255) NULL , + Filename varchar(255) NULL , + ContentType varchar(80) NULL , + ContentEncoding varchar(80) NULL , + Content LONGTEXT NULL , + Headers LONGTEXT NULL , + Creator integer NULL , + Created DATETIME NULL , + PRIMARY KEY (id) +); +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(120) NOT NULL , + Description varchar(120) NULL , + CorrespondAddress varchar(120) NULL , + CommentAddress varchar(120) NULL , + InitialPriority integer NULL , + FinalPriority integer NULL , + DefaultDueIn integer NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +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 NULL , + LocalBase integer NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + Creator integer NULL , + Created DATETIME NULL , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type); +CREATE TABLE Groups ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(16) NULL , + Description varchar(64) NULL , + Pseudo integer NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +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(255) NULL , + Description varchar(255) NULL , + ExecModule varchar(60) NULL , + Argument varchar(255) NULL , + ApplicableTransTypes varchar(60) NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +); +CREATE TABLE Transactions ( + id INTEGER NOT NULL AUTO_INCREMENT, + 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 NULL , + Created DATETIME NULL , + PRIMARY KEY (id) +); +CREATE INDEX Transactions1 ON Transactions (Ticket); +CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); +CREATE TABLE Scrips ( + id INTEGER NOT NULL AUTO_INCREMENT, + ScripCondition integer NULL , + ScripAction integer NULL , + Stage varchar(32) NULL , + Queue integer NULL , + Template integer NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +); +CREATE TABLE ACL ( + id INTEGER NOT NULL AUTO_INCREMENT, + PrincipalId integer NULL , + PrincipalType varchar(25) NULL , + RightName varchar(25) NULL , + RightScope varchar(25) NULL , + RightAppliesTo integer NULL , + PRIMARY KEY (id) +); +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 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) +); +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(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 , + Lang varchar(16) NULL , + EmailEncoding varchar(16) NULL , + WebEncoding varchar(16) NULL , + ExternalContactInfoId varchar(100) NULL , + ContactInfoSystem varchar(30) NULL , + ExternalAuthId varchar(100) NULL , + AuthSystem varchar(30) NULL , + Gecos varchar(16) NULL , + HomePhone varchar(30) NULL , + WorkPhone varchar(30) NULL , + MobilePhone varchar(30) NULL , + PagerPhone varchar(30) NULL , + Address1 varchar(200) NULL , + Address2 varchar(200) NULL , + City varchar(100) NULL , + State varchar(100) NULL , + Zip varchar(16) NULL , + Country varchar(50) NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Users1 ON Users (Name); +CREATE INDEX Users3 ON Users (id, EmailAddress); +CREATE INDEX Users4 ON Users (EmailAddress); +CREATE TABLE Tickets ( + id INTEGER NOT NULL AUTO_INCREMENT, + EffectiveId integer NULL , + Queue integer NULL , + Type varchar(16) NULL , + IssueStatement integer NULL , + Resolution integer NULL , + Owner integer NULL , + Subject varchar(200) NULL DEFAULT '[no subject]' , + InitialPriority integer NULL , + FinalPriority integer NULL , + Priority integer NULL , + Status varchar(10) NULL , + TimeWorked integer NULL , + TimeLeft integer NULL , + Told DATETIME NULL , + Starts DATETIME NULL , + Started DATETIME NULL , + Due DATETIME NULL , + Resolved DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + Creator integer NULL , + Created DATETIME NULL , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +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(255) NULL , + Description varchar(255) NULL , + ExecModule varchar(60) NULL , + Argument varchar(255) NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +); +CREATE TABLE Templates ( + id INTEGER NOT NULL AUTO_INCREMENT, + Queue integer NOT NULL DEFAULT 0 , + Name varchar(40) NOT NULL , + Description varchar(120) NULL , + Type varchar(16) NULL , + Language varchar(16) NULL , + TranslationOf integer NULL , + Content blob NULL , + LastUpdated DATETIME NULL , + LastUpdatedBy integer NULL , + Creator integer NULL , + Created DATETIME NULL , + PRIMARY KEY (id) +); -- cgit v1.2.1 From 945721f48f74d5cfffef7c7cf3a3d6bc2521f5dd Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 15 Jul 2003 13:16:32 +0000 Subject: import of rt 3.0.4 --- rt/etc/schema.mysql | 429 +++++++++++++++++++++++++++++++++++----------------- 1 file changed, 289 insertions(+), 140 deletions(-) (limited to 'rt/etc/schema.mysql') diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql index 7e715c201..46f8ec562 100755 --- a/rt/etc/schema.mysql +++ b/rt/etc/schema.mysql @@ -1,21 +1,9 @@ -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); +# {{{ Attachments + CREATE TABLE Attachments ( id INTEGER NOT NULL AUTO_INCREMENT, TransactionId integer NOT NULL , - Parent integer NULL , + Parent integer NOT NULL DEFAULT 0 , MessageId varchar(160) NULL , Subject varchar(255) NULL , Filename varchar(255) NULL , @@ -23,161 +11,222 @@ CREATE TABLE Attachments ( ContentEncoding varchar(80) NULL , Content LONGTEXT NULL , Headers LONGTEXT NULL , - Creator integer NULL , + Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , PRIMARY KEY (id) -); -CREATE INDEX Attachments1 ON Attachments (Parent); -CREATE INDEX Attachments2 ON Attachments (TransactionId); -CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId); +) TYPE=InnoDB; + +CREATE INDEX Attachments1 ON Attachments (Parent) ; +CREATE INDEX Attachments2 ON Attachments (TransactionId) ; +CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ; +# }}} + +# {{{ Queues CREATE TABLE Queues ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(120) NOT NULL , - Description varchar(120) NULL , + Name varchar(200) NOT NULL , + Description varchar(255) NULL , CorrespondAddress varchar(120) NULL , CommentAddress varchar(120) NULL , - InitialPriority integer NULL , - FinalPriority integer NULL , - DefaultDueIn integer NULL , - Creator integer 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 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 , PRIMARY KEY (id) -); -CREATE UNIQUE INDEX Queues1 ON Queues (Name); +) TYPE=InnoDB; +CREATE UNIQUE INDEX Queues1 ON Queues (Name) ; +CREATE INDEX Queues2 ON Queues (Disabled) ; + +# }}} + +# {{{ Links + CREATE TABLE Links ( id INTEGER NOT NULL AUTO_INCREMENT, Base varchar(240) NULL , Target varchar(240) NULL , Type varchar(20) NOT NULL , - LocalTarget integer NULL , - LocalBase integer NULL , - LastUpdatedBy integer NULL , + LocalTarget integer NOT NULL DEFAULT 0 , + LocalBase integer NOT NULL DEFAULT 0 , + LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , - Creator integer NULL , + Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , PRIMARY KEY (id) -); -CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type); +) 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 TABLE Groups ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(16) NULL , - Description varchar(64) NULL , - Pseudo integer NOT NULL DEFAULT 0 , - PRIMARY KEY (id) -); -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 , + Name varchar(200) NULL , + Description varchar(255) NULL , + Domain varchar(64), + Type varchar(64), + Instance varchar(64), PRIMARY KEY (id) -); -CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner); +) TYPE=InnoDB; + +CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id); +CREATE INDEX Groups2 On Groups (Type, Instance, Domain); + +# }}} + +# {{{ ScripConditions + CREATE TABLE ScripConditions ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(255) NULL , + Name varchar(200) NULL , Description varchar(255) NULL , ExecModule varchar(60) NULL , Argument varchar(255) NULL , ApplicableTransTypes varchar(60) NULL , - Creator integer NULL , + + Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , PRIMARY KEY (id) -); +) TYPE=InnoDB; + +# }}} + +# {{{ Transactions CREATE TABLE Transactions ( id INTEGER NOT NULL AUTO_INCREMENT, - EffectiveTicket integer NULL , - Ticket integer NULL , - TimeTaken integer NULL , + 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 , Data varchar(100) NULL , - Creator integer NULL , + + Creator integer NOT NULL DEFAULT 0 , 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, - ScripCondition integer NULL , - ScripAction integer NULL , + 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) NULL , - Queue integer NULL , - Template integer NULL , - Creator integer NULL , + Queue integer NOT NULL DEFAULT 0 , + Template integer NOT NULL DEFAULT 0 , + Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , PRIMARY KEY (id) -); +) TYPE=InnoDB; + +# }}} + +# {{{ ACL CREATE TABLE ACL ( id INTEGER NOT NULL AUTO_INCREMENT, - PrincipalId integer NULL , - PrincipalType varchar(25) NULL , - RightName varchar(25) NULL , - RightScope varchar(25) NULL , - RightAppliesTo integer NULL , + 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 PRIMARY KEY (id) -); -CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId); -CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId); +) TYPE=InnoDB; + +CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId); + +# }}} + +# {{{ GroupMembers + CREATE TABLE GroupMembers ( id INTEGER NOT NULL AUTO_INCREMENT, - 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 , + GroupId integer NOT NULL DEFAULT 0, + MemberId integer NOT NULL DEFAULT 0, #Foreign key to Principals PRIMARY KEY (id) -); -CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent); -CREATE INDEX Keywords2 ON Keywords (Name); -CREATE INDEX Keywords3 ON Keywords (Parent); +) 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 TABLE Users ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(120) NOT NULL , + Name varchar(200) 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 , @@ -196,72 +245,172 @@ CREATE TABLE Users ( State varchar(100) NULL , Zip varchar(16) NULL , Country varchar(50) NULL , - Creator integer NULL , + Timezone varchar(50) NULL , + PGPKey text NULL, + + Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , - Disabled int2 NOT NULL DEFAULT 0 , PRIMARY KEY (id) -); -CREATE UNIQUE INDEX Users1 ON Users (Name); +) TYPE=InnoDB; + + +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); + + +# }}} + +# {{{ Tickets + CREATE TABLE Tickets ( id INTEGER NOT NULL AUTO_INCREMENT, - EffectiveId integer NULL , - Queue integer NULL , + EffectiveId integer NOT NULL DEFAULT 0 , + Queue integer NOT NULL DEFAULT 0 , Type varchar(16) NULL , - IssueStatement integer NULL , - Resolution integer NULL , - Owner integer NULL , + IssueStatement integer NOT NULL DEFAULT 0 , + Resolution integer NOT NULL DEFAULT 0 , + Owner integer NOT NULL DEFAULT 0 , Subject varchar(200) NULL DEFAULT '[no subject]' , - InitialPriority integer NULL , - FinalPriority integer NULL , - Priority integer NULL , + 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 , Status varchar(10) NULL , - TimeWorked integer NULL , - TimeLeft integer NULL , + TimeLeft integer NOT NULL DEFAULT 0 , Told DATETIME NULL , Starts DATETIME NULL , Started DATETIME NULL , Due DATETIME NULL , Resolved DATETIME NULL , - LastUpdatedBy integer NULL , + + + LastUpdatedBy integer NOT NULL DEFAULT 0 , LastUpdated DATETIME NULL , - Creator integer NULL , + Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 , PRIMARY KEY (id) -); -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); +) 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 TABLE ScripActions ( id INTEGER NOT NULL AUTO_INCREMENT, - Name varchar(255) NULL , + Name varchar(200) NULL , Description varchar(255) NULL , ExecModule varchar(60) NULL , Argument varchar(255) NULL , - Creator integer NULL , + Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , 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(40) NOT NULL , - Description varchar(120) NULL , + Name varchar(200) NOT NULL , + Description varchar(255) NULL , Type varchar(16) NULL , Language varchar(16) NULL , - TranslationOf integer NULL , + TranslationOf integer NOT NULL DEFAULT 0 , Content blob NULL , LastUpdated DATETIME NULL , - LastUpdatedBy integer NULL , - Creator integer 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 , 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) ); + +# }}} -- cgit v1.2.1 From 289340780927b5bac2c7604d7317c3063c6dd8cc Mon Sep 17 00:00:00 2001 From: ivan Date: Thu, 11 Mar 2004 02:05:38 +0000 Subject: import of rt 3.0.9 --- rt/etc/schema.mysql | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-) (limited to 'rt/etc/schema.mysql') diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql index 46f8ec562..14e92238f 100755 --- a/rt/etc/schema.mysql +++ b/rt/etc/schema.mysql @@ -62,6 +62,7 @@ CREATE TABLE Links ( 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); # }}} @@ -87,7 +88,7 @@ CREATE TABLE Groups ( Description varchar(255) NULL , Domain varchar(64), Type varchar(64), - Instance varchar(64), + Instance integer, PRIMARY KEY (id) ) TYPE=InnoDB; @@ -125,7 +126,7 @@ CREATE TABLE Transactions ( Field varchar(40) NULL , OldValue varchar(255) NULL , NewValue varchar(255) NULL , - Data varchar(100) NULL , + Data varchar(255) NULL , Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , @@ -358,6 +359,9 @@ CREATE TABLE TicketCustomFieldValues ( PRIMARY KEY (id) ) TYPE=InnoDB; +CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content); +CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket); + # }}} # {{{ CustomFields @@ -399,6 +403,8 @@ CREATE TABLE CustomFieldValues ( PRIMARY KEY (id) ) TYPE=InnoDB; +CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); + # }}} # {{{ Sessions -- cgit v1.2.1