summaryrefslogtreecommitdiff
path: root/rt/etc/schema.Informix
diff options
context:
space:
mode:
Diffstat (limited to 'rt/etc/schema.Informix')
-rw-r--r--rt/etc/schema.Informix342
1 files changed, 342 insertions, 0 deletions
diff --git a/rt/etc/schema.Informix b/rt/etc/schema.Informix
new file mode 100644
index 0000000..ca6173f
--- /dev/null
+++ b/rt/etc/schema.Informix
@@ -0,0 +1,342 @@
+-- This schema was adopted from the oracle schema by
+-- Andre Koppel.
+-- Version 0.2 Date 2003.10.21
+-- The work is still in progress
+
+CREATE TABLE Attachments (
+ id SERIAL,
+ TransactionId INTEGER NOT NULL,
+ Parent INTEGER DEFAULT 0 NOT NULL,
+ MessageId VARCHAR(160),
+ Subject VARCHAR(255),
+ Filename VARCHAR(255),
+ ContentType VARCHAR(80),
+ ContentEncoding VARCHAR(80),
+ Content BYTE,
+ Headers BYTE,
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ 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 SERIAL,
+ Name VARCHAR(200) DEFAULT '' NOT NULL,
+ Description VARCHAR(255) DEFAULT NULL,
+ CorrespondAddress VARCHAR(120) DEFAULT NULL,
+ CommentAddress VARCHAR(120) DEFAULT NULL,
+ InitialPriority INTEGER DEFAULT 0 NOT NULL,
+ FinalPriority INTEGER DEFAULT 0 NOT NULL,
+ DefaultDueIn INTEGER DEFAULT 0 NOT NULL,
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ Disabled INTEGER DEFAULT 0 NOT NULL,
+ PRIMARY KEY (id)
+);
+CREATE UNIQUE INDEX Queues1 ON Queues (Name);
+CREATE INDEX Queues2 ON Queues (Disabled);
+
+
+CREATE TABLE Links (
+ id SERIAL,
+ Base VARCHAR(240) DEFAULT NULL,
+ Target VARCHAR(240) DEFAULT NULL,
+ Type VARCHAR(20) DEFAULT '' NOT NULL,
+ LocalTarget INTEGER DEFAULT 0 NOT NULL,
+ LocalBase INTEGER DEFAULT 0 NOT NULL,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ 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);
+
+
+CREATE TABLE Principals (
+ id SERIAL,
+ PrincipalType VARCHAR(16) DEFAULT '' NOT NULL,
+ ObjectId INTEGER DEFAULT 0,
+ Disabled INTEGER DEFAULT 0 NOT NULL,
+ PRIMARY KEY (id)
+);
+CREATE INDEX Principals2 ON Principals (ObjectId);
+
+
+CREATE TABLE Groups (
+ id SERIAL,
+ Name VARCHAR(200) DEFAULT NULL,
+ Description VARCHAR(255) DEFAULT NULL,
+ Domain VARCHAR(64) DEFAULT '',
+ Type VARCHAR(64) DEFAULT '',
+ Instance INTEGER DEFAULT 0 NOT NULL,
+-- Instance VARCHAR(64) DEFAULT '' NOT NULL,
+ PRIMARY KEY (id)
+);
+CREATE INDEX Groups1 ON Groups (Domain, Instance, Type, id);
+CREATE INDEX Groups2 ON Groups (Type, Instance, Domain);
+
+
+CREATE TABLE ScripConditions (
+ id SERIAL,
+ Name VARCHAR(200),
+ Description VARCHAR(255),
+ ExecModule VARCHAR(60),
+ Argument VARCHAR(255),
+ ApplicableTransTypes VARCHAR(60),
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ PRIMARY KEY (id)
+);
+
+
+CREATE TABLE Transactions (
+ id SERIAL,
+ EffectiveTicket INTEGER DEFAULT 0 NOT NULL,
+ Ticket INTEGER DEFAULT 0 NOT NULL,
+ TimeTaken INTEGER DEFAULT 0 NOT NULL,
+ Type VARCHAR(20),
+ Field VARCHAR(40),
+ OldValue VARCHAR(255),
+ NewValue VARCHAR(255),
+ Data VARCHAR(255),
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ PRIMARY KEY (id)
+);
+CREATE INDEX Transactions1 ON Transactions (Ticket);
+CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
+
+
+CREATE TABLE Scrips (
+ id SERIAL,
+ Description VARCHAR(255) DEFAULT '',
+ ScripCondition INTEGER DEFAULT 0 NOT NULL,
+ ScripAction INTEGER DEFAULT 0 NOT NULL,
+ ConditionRules BYTE,
+ ActionRules BYTE,
+ CustomIsApplicableCode BYTE,
+ CustomPrepareCode BYTE,
+ CustomCommitCode BYTE,
+ Stage VARCHAR(32),
+ Queue INTEGER DEFAULT 0 NOT NULL,
+ Template INTEGER DEFAULT 0 NOT NULL,
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ PRIMARY KEY (id)
+);
+
+
+CREATE TABLE ACL (
+ id SERIAL,
+ PrincipalType VARCHAR(25) NOT NULL,
+ PrincipalId INTEGER NOT NULL,
+ RightName VARCHAR(25) NOT NULL,
+ ObjectType VARCHAR(25) NOT NULL,
+ ObjectId INTEGER DEFAULT 0 NOT NULL,
+ DelegatedBy INTEGER DEFAULT 0 NOT NULL,
+ DelegatedFrom INTEGER DEFAULT 0 NOT NULL,
+ PRIMARY KEY (id)
+);
+CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);
+
+
+CREATE TABLE GroupMembers (
+ id SERIAL,
+ GroupId INTEGER DEFAULT 0 NOT NULL,
+ MemberId INTEGER DEFAULT 0 NOT NULL,
+ PRIMARY KEY (id)
+);
+CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
+
+
+CREATE TABLE CachedGroupMembers (
+ id SERIAL,
+ GroupId INTEGER DEFAULT 0,
+ MemberId INTEGER DEFAULT 0,
+ Via INTEGER DEFAULT 0,
+ ImmediateParentId INTEGER DEFAULT 0,
+ Disabled INTEGER DEFAULT 0 NOT NULL,
+ PRIMARY KEY (id)
+);
+CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
+CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId);
+
+
+CREATE TABLE Users (
+ id SERIAL,
+ Name VARCHAR(200) NOT NULL,
+ Password VARCHAR(40),
+ Comments BYTE,
+ Signature BYTE,
+ EmailAddress VARCHAR(120),
+ FreeFormContactInfo BYTE,
+ Organization VARCHAR(200),
+ RealName VARCHAR(120),
+ NickName VARCHAR(16),
+ Lang VARCHAR(16),
+ EmailEncoding VARCHAR(16),
+ WebEncoding VARCHAR(16),
+ ExternalContactInfoId VARCHAR(100),
+ ContactInfoSystem VARCHAR(30),
+ ExternalAuthId VARCHAR(100),
+ AuthSystem VARCHAR(30),
+ Gecos VARCHAR(16),
+ HomePhone VARCHAR(30),
+ WorkPhone VARCHAR(30),
+ MobilePhone VARCHAR(30),
+ PagerPhone VARCHAR(30),
+ Address1 VARCHAR(200),
+ Address2 VARCHAR(200),
+ City VARCHAR(100),
+ State VARCHAR(100),
+ Zip VARCHAR(16),
+ Country VARCHAR(50),
+ Timezone VARCHAR(50),
+ PGPKey BYTE,
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ 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);
+
+
+CREATE TABLE Tickets (
+ id SERIAL,
+ EffectiveId INTEGER DEFAULT 0 NOT NULL,
+ Queue INTEGER DEFAULT 0 NOT NULL,
+ Type VARCHAR(16),
+ IssueStatement INTEGER DEFAULT 0 NOT NULL,
+ Resolution INTEGER DEFAULT 0 NOT NULL,
+ Owner INTEGER DEFAULT 0 NOT NULL,
+ Subject VARCHAR(200) DEFAULT '[no subject]',
+ InitialPriority INTEGER DEFAULT 0 NOT NULL,
+ FinalPriority INTEGER DEFAULT 0 NOT NULL,
+ Priority INTEGER DEFAULT 0 NOT NULL,
+ TimeEstimated INTEGER DEFAULT 0 NOT NULL,
+ TimeWorked INTEGER DEFAULT 0 NOT NULL,
+ Status VARCHAR(10),
+ TimeLeft INTEGER DEFAULT 0 NOT NULL,
+ Told DATETIME YEAR TO SECOND,
+ Starts DATETIME YEAR TO SECOND,
+ Started DATETIME YEAR TO SECOND,
+ Due DATETIME YEAR TO SECOND,
+ Resolved DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ Disabled INTEGER DEFAULT 0 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);
+
+
+CREATE TABLE ScripActions (
+ id SERIAL,
+ Name VARCHAR(200),
+ Description VARCHAR(255),
+ ExecModule VARCHAR(60),
+ Argument VARCHAR(255),
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ PRIMARY KEY (id)
+);
+
+
+CREATE TABLE Templates (
+ id SERIAL,
+ Queue INTEGER DEFAULT 0 NOT NULL,
+ Name VARCHAR(200) NOT NULL,
+ Description VARCHAR(255),
+ Type VARCHAR(16),
+ Language VARCHAR(16),
+ TranslationOf INTEGER DEFAULT 0 NOT NULL,
+ Content BYTE,
+ LastUpdated DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ PRIMARY KEY (id)
+);
+
+
+CREATE TABLE TicketCustomFieldValues (
+ id SERIAL,
+ Ticket INTEGER NOT NULL,
+ CustomField INTEGER NOT NULL,
+ Content VARCHAR(255),
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ PRIMARY KEY (id)
+);
+
+CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
+CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
+
+CREATE TABLE CustomFields (
+ id SERIAL,
+ Name VARCHAR(200),
+ Type VARCHAR(200),
+ Queue INTEGER DEFAULT 0 NOT NULL,
+ Description VARCHAR(255),
+ SortOrder INTEGER DEFAULT 0 NOT NULL,
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ Disabled SMALLINT DEFAULT 0 NOT NULL,
+ PRIMARY KEY (id)
+);
+CREATE INDEX CustomFields1 ON CustomFields (Disabled, Queue);
+
+
+CREATE TABLE CustomFieldValues (
+ id SERIAL,
+ CustomField INTEGER NOT NULL,
+ Name VARCHAR(200),
+ Description VARCHAR(255),
+ SortOrder INTEGER DEFAULT 0 NOT NULL,
+ Creator INTEGER DEFAULT 0 NOT NULL,
+ Created DATETIME YEAR TO SECOND,
+ LastUpdatedBy INTEGER DEFAULT 0 NOT NULL,
+ LastUpdated DATETIME YEAR TO SECOND,
+ PRIMARY KEY (id)
+);
+
+CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
+
+CREATE TABLE sessions (
+ id VARCHAR(32) NOT NULL,
+ a_session BYTE,
+ LastUpdated DATETIME YEAR TO SECOND,
+ PRIMARY KEY (id)
+);
+
generated by cgit v1.1 at 2024-06-09 02:32:18 +0000