+++ /dev/null
--- 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,
- ObjectType VARCHAR(255),
- ObjectId INTEGER DEFAULT 0 NOT NULL,
- TimeTaken INTEGER DEFAULT 0 NOT NULL,
- Type VARCHAR(20),
- Field VARCHAR(40),
- OldValue VARCHAR(255),
- NewValue VARCHAR(255),
- ReferenceType VARCHAR(255),
- OldReference INTEGER DEFAULT 0,
- NewReference INTEGER DEFAULT 0,
- Data VARCHAR(255),
- Creator INTEGER DEFAULT 0 NOT NULL,
- Created DATETIME YEAR TO SECOND,
- PRIMARY KEY (id)
-);
-CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
-
-
-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),
- MaxValues INTEGER DEFAULT 0 NOT NULL,
- Pattern VARCHAR(255),
- LookupType VARCHAR(255),
- 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 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 Attributes (
- id SERIAL,
- Name VARCHAR(255) DEFAULT '' NOT NULL,
- Description VARCHAR(255) DEFAULT NULL,
- Content BYTE,
- ContentType VARCHAR(16),
- ObjectType VARCHAR(25) NOT NULL,
- ObjectId 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 Attributes1 on Attributes(Name);
-CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
-
-
-CREATE TABLE sessions (
- id VARCHAR(32) NOT NULL,
- a_session BYTE,
- LastUpdated DATETIME YEAR TO SECOND,
- PRIMARY KEY (id)
-);
-
projects / freeside.git / blobdiff