--- /dev/null
+# {{{ Attachments
+
+CREATE TABLE rt3.Attachments (
+ id numeric(38,0) identity,
+ TransactionId integer NOT NULL ,
+ Parent integer NOT NULL ,
+ MessageId varchar(160) NULL ,
+ Subject varchar(255) NULL ,
+ Filename varchar(255) NULL ,
+ ContentType varchar(80) NULL ,
+ ContentEncoding varchar(80) NULL ,
+ Content TEXT NULL ,
+ Headers TEXT NULL ,
+ Creator integer NOT 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) ;
+# }}}
+
+# {{{ Queues
+CREATE TABLE rt3.Queues (
+ id numeric(38,0) identity,
+ Name varchar(200) NOT NULL ,
+ Description varchar(255) NULL ,
+ CorrespondAddress varchar(120) NULL ,
+ CommentAddress varchar(120) NULL ,
+ InitialPriority integer NOT NULL ,
+ FinalPriority integer NOT NULL ,
+ DefaultDueIn integer NOT NULL ,
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ Disabled numeric(1) NOT NULL ,
+ PRIMARY KEY (id)
+) ;
+CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
+CREATE INDEX Queues2 ON Queues (Disabled) ;
+
+# }}}
+
+# {{{ Links
+
+CREATE TABLE rt3.Links (
+ id numeric(38,0) identity,
+ Base varchar(240) NULL ,
+ Target varchar(240) NULL ,
+ Type varchar(20) NOT NULL ,
+ LocalTarget integer NOT NULL ,
+ LocalBase integer NOT NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+
+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);
+
+# }}}
+
+# {{{ Principals
+
+CREATE TABLE rt3.Principals (
+ id numeric(38,0) identity,
+ PrincipalType VARCHAR(16) not null,
+ ObjectId integer, Disabled numeric(1) NOT NULL ,
+ PRIMARY KEY (id)
+) ;
+
+CREATE INDEX Principals2 ON Principals (ObjectId);
+
+# }}}
+
+# {{{ Groups
+
+CREATE TABLE rt3.Groups (
+ id numeric(38,0) identity,
+ Name varchar(200) NULL ,
+ Description varchar(255) NULL ,
+ Domain varchar(64),
+ Type varchar(64),
+ Instance integer,
+ PRIMARY KEY (id)
+) ;
+
+CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
+CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
+
+# }}}
+
+# {{{ ScripConditions
+
+CREATE TABLE rt3.ScripConditions (
+ id numeric(38,0) identity,
+ Name varchar(200) NULL ,
+ Description varchar(255) NULL ,
+ ExecModule varchar(60) NULL ,
+ Argument varchar(255) NULL ,
+ ApplicableTransTypes varchar(60) NULL ,
+
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+
+# }}}
+
+# {{{ Transactions
+CREATE TABLE rt3.Transactions (
+ id numeric(38,0) identity,
+ EffectiveTicket integer NOT NULL ,
+ Ticket integer NOT NULL ,
+ TimeTaken integer NOT NULL ,
+ Type varchar(20) NULL ,
+ Field varchar(40) NULL ,
+ OldValue varchar(255) NULL ,
+ NewValue varchar(255) NULL ,
+ Data varchar(255) NULL ,
+
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+CREATE INDEX Transactions1 ON Transactions (Ticket);
+CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
+
+# }}}
+
+# {{{ Scrips
+
+CREATE TABLE rt3.Scrips (
+ id numeric(38,0) identity,
+ Description varchar(255),
+ ScripCondition integer NOT NULL ,
+ ScripAction integer NOT NULL ,
+ ConditionRules text NULL ,
+ ActionRules text NULL ,
+ CustomIsApplicableCode text NULL ,
+ CustomPrepareCode text NULL ,
+ CustomCommitCode text NULL ,
+ Stage varchar(32) NULL ,
+ Queue integer NOT NULL ,
+ Template integer NOT NULL ,
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+
+# }}}
+
+# {{{ ACL
+CREATE TABLE rt3.ACL (
+ id numeric(38,0) identity,
+ 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 ,
+ DelegatedBy integer NOT NULL , #foreign key to principals with a userid
+ DelegatedFrom integer NOT NULL , #foreign key to ACL
+ PRIMARY KEY (id)
+) ;
+
+CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
+
+# }}}
+
+# {{{ GroupMembers
+
+CREATE TABLE rt3.GroupMembers (
+ id numeric(38,0) identity,
+ GroupId integer NOT NULL ,
+ MemberId integer NOT NULL , #Foreign key to Principals
+ PRIMARY KEY (id)
+) ;
+CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
+
+
+# }}}
+
+# {{{ GroupMembersCache
+
+CREATE TABLE rt3.CachedGroupMembers (
+ id numeric(38,0) identity,
+ 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 numeric(1) NOT NULL , # 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)
+) ;
+
+CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
+CREATE INDEX GrouMem on CachedGroupMembers (GroupId,MemberId);
+
+# }}}
+
+# {{{ Users
+
+CREATE TABLE rt3.Users (
+ id numeric(38,0) identity,
+ Name varchar(200) NOT NULL ,
+ Password varchar(40) NULL ,
+ Comments text NULL ,
+ Signature text NULL ,
+ EmailAddress varchar(120) NULL ,
+ FreeformContactInfo text 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 ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+
+
+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 rt3.Tickets (
+ id numeric(38,0) identity,
+ EffectiveId integer NOT NULL ,
+ Queue integer NOT NULL ,
+ Type varchar(16) NULL ,
+ IssueStatement integer NOT NULL ,
+ Resolution integer NOT NULL ,
+ Owner integer NOT NULL ,
+ Subject varchar(200) NULL,
+ InitialPriority integer NOT NULL ,
+ FinalPriority integer NOT NULL ,
+ Priority integer NOT NULL ,
+ TimeEstimated integer NOT NULL ,
+ TimeWorked integer NOT NULL ,
+ Status varchar(10) NULL ,
+ TimeLeft integer NOT NULL ,
+ Told DATETIME NULL ,
+ Starts DATETIME NULL ,
+ Started DATETIME NULL ,
+ Due DATETIME NULL ,
+ Resolved DATETIME NULL ,
+
+
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ Disabled numeric(1) NOT NULL ,
+ 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 INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
+
+# }}}
+
+# {{{ ScripActions
+
+CREATE TABLE rt3.ScripActions (
+ id numeric(38,0) identity,
+ Name varchar(200) NULL ,
+ Description varchar(255) NULL ,
+ ExecModule varchar(60) NULL ,
+ Argument varchar(255) NULL ,
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+
+# }}}
+
+# {{{ Templates
+
+CREATE TABLE rt3.Templates (
+ id numeric(38,0) identity,
+ Queue integer NOT NULL ,
+ Name varchar(200) NOT NULL ,
+ Description varchar(255) NULL ,
+ Type varchar(16) NULL ,
+ Language varchar(16) NULL ,
+ TranslationOf integer NOT NULL ,
+ Content text NULL ,
+ LastUpdated DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+
+# }}}
+
+# {{{ TicketCustomFieldValues
+
+CREATE TABLE rt3.TicketCustomFieldValues (
+ id numeric(38,0) identity,
+ Ticket int NOT NULL ,
+ CustomField int NOT NULL ,
+ Content varchar(255) NULL ,
+
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+
+CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
+CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
+
+# }}}
+
+# {{{ CustomFields
+
+CREATE TABLE rt3.CustomFields (
+ id numeric(38,0) identity,
+ Name varchar(200) NULL ,
+ Type varchar(200) NULL ,
+ Queue integer NOT NULL ,
+ Description varchar(255) NULL ,
+ SortOrder integer NOT NULL ,
+
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ Disabled numeric(1) NOT NULL ,
+ PRIMARY KEY (id)
+) ;
+
+CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue);
+
+
+# }}}
+
+# {{{ CustomFieldValues
+
+CREATE TABLE rt3.CustomFieldValues (
+ id numeric(38,0) identity,
+ CustomField int NOT NULL ,
+ Name varchar(200) NULL ,
+ Description varchar(255) NULL ,
+ SortOrder integer NOT NULL ,
+
+ Creator integer NOT NULL ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+
+CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
+
+# }}}
+
+
+# {{{ Attributes
+
+CREATE TABLE rt3.Attributes (
+ id numeric(38,0) identity,
+ 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 ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL ,
+ LastUpdated DATETIME NULL ,
+ PRIMARY KEY (id)
+) ;
+
+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 rt3.sessions (
+ id char(32) NOT NULL,
+ a_session TEXT,
+ LastUpdated DATETIME,
+ PRIMARY KEY (id)
+);
+
+# }}}
projects / freeside.git / blobdiff