diff options
Diffstat (limited to 'rt/etc/schema.mysql-4.0')
-rwxr-xr-x | rt/etc/schema.mysql-4.0 | 464 |
1 files changed, 464 insertions, 0 deletions
diff --git a/rt/etc/schema.mysql-4.0 b/rt/etc/schema.mysql-4.0 new file mode 100755 index 000000000..c4299d472 --- /dev/null +++ b/rt/etc/schema.mysql-4.0 @@ -0,0 +1,464 @@ +# {{{ Attachments + +CREATE TABLE Attachments ( + id INTEGER NOT NULL AUTO_INCREMENT, + TransactionId integer NOT NULL , + Parent integer NOT NULL DEFAULT 0 , + 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 NOT NULL DEFAULT 0 , + Created DATETIME NULL , + PRIMARY KEY (id) +) TYPE=InnoDB; + +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(200) NOT NULL , + Description varchar(255) 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 , + Created DATETIME NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + 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 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 , + LastUpdated DATETIME NULL , + Creator integer NOT NULL DEFAULT 0 , + Created DATETIME NULL , + PRIMARY KEY (id) +) TYPE=InnoDB; + +CREATE INDEX Links2 ON Links (Base, Type) ; +CREATE INDEX Links3 ON Links (Target, Type) ; +CREATE INDEX Links4 ON Links (Type,LocalBase); + +# }}} + +# {{{ 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(200) NULL , + Description varchar(255) NULL , + Domain varchar(64), + Type varchar(64), + Instance integer, + PRIMARY KEY (id) +) TYPE=InnoDB; + +CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id); +CREATE INDEX Groups2 On Groups (Type, Instance); + +# }}} + +# {{{ ScripConditions + +CREATE TABLE ScripConditions ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(200) NULL , + Description varchar(255) NULL , + ExecModule varchar(60) NULL , + Argument varchar(255) NULL , + ApplicableTransTypes varchar(60) NULL , + + Creator integer NOT NULL DEFAULT 0 , + Created DATETIME 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, + ObjectType varchar(64) NOT NULL, + ObjectId 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 , + + Creator integer NOT NULL DEFAULT 0 , + Created DATETIME NULL , + PRIMARY KEY (id) +) TYPE=InnoDB; +CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId); + +# }}} + +# {{{ 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 , + Stage varchar(32) NULL , + Queue integer NOT NULL DEFAULT 0 , + Template 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; + +# }}} + +# {{{ 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 + PRIMARY KEY (id) +) TYPE=InnoDB; + +CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId); + +# }}} + +# {{{ GroupMembers + +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 + 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 CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId); + +# }}} + +# {{{ Users + +CREATE TABLE Users ( + id INTEGER NOT NULL AUTO_INCREMENT, + 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 , + 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 , + Timezone varchar(50) NULL , + PGPKey text NULL, + + Creator integer NOT NULL DEFAULT 0 , + Created DATETIME NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +) TYPE=InnoDB; + + +CREATE UNIQUE INDEX Users1 ON Users (Name) ; +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 , + Type varchar(16) 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 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 , + TimeLeft integer NOT NULL DEFAULT 0 , + Told DATETIME NULL , + Starts DATETIME NULL , + Started DATETIME NULL , + Due DATETIME NULL , + Resolved DATETIME NULL , + + + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated DATETIME NULL , + Creator integer NOT NULL DEFAULT 0 , + 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 Tickets6 ON Tickets (EffectiveId, Type) ; + +# }}} + +# {{{ ScripActions + +CREATE TABLE ScripActions ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(200) NULL , + Description varchar(255) NULL , + ExecModule varchar(60) NULL , + Argument 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; + +# }}} + +# {{{ 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 , + Type varchar(16) NULL , + Language varchar(16) NULL , + TranslationOf integer NOT NULL DEFAULT 0 , + 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; + +# }}} + +# {{{ ObjectCustomFieldValues + +CREATE TABLE ObjectCustomFieldValues ( + id INTEGER NOT NULL AUTO_INCREMENT, + 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 + +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 TEXT NULL , # New -- Must validate against this + Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry + Description varchar(255) NULL , + SortOrder integer NOT NULL DEFAULT 0 , + LookupType varchar(255) NOT NULL, + + 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; + +# }}} + +# {{{ ObjectCustomFields + +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; + +# }}} + +# {{{ 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; + +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 + +# 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) +); + +# }}} |