X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=rt%2Fetc%2Fschema.mysql;fp=rt%2Fetc%2Fschema.mysql;h=0000000000000000000000000000000000000000;hp=46f8ec562768aca0133eea5767c5f2612bc13c2f;hb=5c96d46d56f2066bb40d9a34c4db56f53f43c6f2;hpb=63a268637b2d51a8766412617724b9436439deb6 diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql deleted file mode 100755 index 46f8ec562..000000000 --- a/rt/etc/schema.mysql +++ /dev/null @@ -1,416 +0,0 @@ -# {{{ 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 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(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 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(200) NULL , - Description varchar(255) NULL , - Domain varchar(64), - Type varchar(64), - Instance varchar(64), - PRIMARY KEY (id) -) 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(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, - 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 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, - 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 GrouMem on CachedGroupMembers (GroupId,MemberId); - -# }}} - -# {{{ 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 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 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 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(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; - -# }}} - -# {{{ 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) -); - -# }}}