[usit-rt.git] / etc / schema.Oracle


CREATE SEQUENCE ATTACHMENTS_seq;
CREATE TABLE Attachments (
        id              NUMBER(11,0) 
                        CONSTRAINT Attachments_Key PRIMARY KEY,
        TransactionId   NUMBER(11,0) NOT NULL,
        Parent          NUMBER(11,0) DEFAULT 0 NOT NULL, 
        MessageId       VARCHAR2(160),
        Subject         VARCHAR2(255),
        Filename        VARCHAR2(255),
        ContentType     VARCHAR2(80),
        ContentEncoding VARCHAR2(80),
        Content         CLOB,
        Headers         CLOB,
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE
);
CREATE INDEX Attachments2 ON Attachments (TransactionId);
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);


CREATE SEQUENCE QUEUES_seq;
CREATE TABLE Queues (
        id                      NUMBER(11,0) 
                CONSTRAINT Queues_Key PRIMARY KEY,
        Name                    VARCHAR2(200) CONSTRAINT Queues_Name_Unique UNIQUE NOT NULL,
        Description             VARCHAR2(255),
        CorrespondAddress       VARCHAR2(120),
        CommentAddress          VARCHAR2(120),
        Lifecycle               VARCHAR2(32),
        SubjectTag              VARCHAR2(120),
        InitialPriority         NUMBER(11,0) DEFAULT 0 NOT NULL,
        FinalPriority           NUMBER(11,0) DEFAULT 0 NOT NULL,
        DefaultDueIn            NUMBER(11,0) DEFAULT 0 NOT NULL,
        Creator                 NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created                 DATE,
        LastUpdatedBy           NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated             DATE,
        Disabled                NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE UNIQUE INDEX Queues1 ON Queues (LOWER(Name));
CREATE INDEX Queues2 ON Queues (Disabled);


CREATE SEQUENCE LINKS_seq;
CREATE TABLE Links (
        id              NUMBER(11,0) 
                CONSTRAINT Links_Key PRIMARY KEY,
        Base            VARCHAR2(240),
        Target          VARCHAR2(240),
        Type            VARCHAR2(20) NOT NULL,
        LocalTarget     NUMBER(11,0) DEFAULT 0 NOT NULL,
        LocalBase       NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE,
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE
);
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 SEQUENCE PRINCIPALS_seq;
CREATE TABLE Principals (
        id              NUMBER(11,0) 
                CONSTRAINT Principals_Key PRIMARY KEY,
        PrincipalType   VARCHAR2(16),
        ObjectId        NUMBER(11,0),
        Disabled        NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE UNIQUE  INDEX Principals2 ON Principals (ObjectId);


CREATE SEQUENCE GROUPS_seq;
CREATE TABLE Groups (
        id              NUMBER(11,0) 
                CONSTRAINT Groups_Key PRIMARY KEY,
        Name            VARCHAR2(200),
        Description     VARCHAR2(255),
        Domain          VARCHAR2(64),
        Type            VARCHAR2(64),
        Instance        NUMBER(11,0) DEFAULT 0, -- NOT NULL
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE
--      Instance        VARCHAR2(64)
);
CREATE INDEX Groups1 ON Groups (LOWER(Domain), LOWER(Type), Instance);
CREATE INDEX Groups2 ON Groups (LOWER(Domain), LOWER(Name), Instance);
CREATE INDEX Groups3 ON Groups (Instance);


CREATE SEQUENCE SCRIPCONDITIONS_seq;
CREATE TABLE ScripConditions (
        id                      NUMBER(11, 0) 
                CONSTRAINT ScripConditions_Key PRIMARY KEY,
        Name                    VARCHAR2(200),
        Description             VARCHAR2(255),
        ExecModule              VARCHAR2(60),
        Argument                VARCHAR2(255),
        ApplicableTransTypes    VARCHAR2(60),
        Creator                 NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created                 DATE,
        LastUpdatedBy           NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated             DATE
);


CREATE SEQUENCE TRANSACTIONS_seq;
CREATE TABLE Transactions (
        id                      NUMBER(11,0) 
                CONSTRAINT Transactions_Key PRIMARY KEY,
        ObjectType              VARCHAR2(255),
        ObjectId                NUMBER(11,0) DEFAULT 0 NOT NULL,
        TimeTaken               NUMBER(11,0) DEFAULT 0 NOT NULL,
        Type                    VARCHAR2(20),
        Field                   VARCHAR2(40),
        OldValue                VARCHAR2(255),
        NewValue                VARCHAR2(255),
        ReferenceType           VARCHAR2(255),
        OldReference            NUMBER(11,0),
        NewReference            NUMBER(11,0),
        Data                    VARCHAR2(255),
        Creator                 NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created                 DATE
);
CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);


CREATE SEQUENCE SCRIPS_seq;
CREATE TABLE Scrips (
        id              NUMBER(11,0) 
                CONSTRAINT Scrips_Key PRIMARY KEY,      
        Description     VARCHAR2(255),
        ScripCondition  NUMBER(11,0) DEFAULT 0 NOT NULL,
        ScripAction     NUMBER(11,0) DEFAULT 0 NOT NULL,
        CustomIsApplicableCode  CLOB,
        CustomPrepareCode       CLOB,
        CustomCommitCode        CLOB,
        Disabled        NUMBER(11,0) DEFAULT 0 NOT NULL,
        Template        VARCHAR2(200) NOT NULL,
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE  
);

CREATE SEQUENCE OBJECTSCRIPS_seq;
CREATE TABLE ObjectScrips (
        id              NUMBER(11,0)
                 CONSTRAINT ObjectScrips_Key PRIMARY KEY,
        Scrip       NUMBER(11,0)  NOT NULL,
        Stage           VARCHAR2(32) DEFAULT 'TransactionCreate' NOT NULL,
        ObjectId              NUMBER(11,0)  NOT NULL,
        SortOrder       NUMBER(11,0) DEFAULT 0 NOT NULL,
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE
);
CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);

CREATE SEQUENCE ACL_seq;
CREATE TABLE ACL (
        id              NUMBER(11,0) 
                CONSTRAINT ACL_Key PRIMARY KEY,
        PrincipalType   VARCHAR2(25) NOT NULL,
        PrincipalId     NUMBER(11,0) NOT NULL,
        RightName       VARCHAR2(25) NOT NULL,
        ObjectType      VARCHAR2(25) NOT NULL,
        ObjectId        NUMBER(11,0) DEFAULT 0 NOT NULL,
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE
);
CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);


CREATE SEQUENCE GROUPMEMBERS_seq;
CREATE TABLE GroupMembers (
        id              NUMBER(11,0) 
                CONSTRAINT GroupMembers_Key PRIMARY KEY,
        GroupId         NUMBER(11,0) DEFAULT 0 NOT NULL,
        MemberId        NUMBER(11,0) DEFAULT 0 NOT NULL,
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE
);
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);


CREATE SEQUENCE CachedGroupMembers_seq;
CREATE TABLE CachedGroupMembers (
        id              NUMBER(11,0) 
                CONSTRAINT CachedGroupMembers_Key PRIMARY KEY,
        GroupId         NUMBER(11,0),
        MemberId        NUMBER(11,0),
        Via             NUMBER(11,0),
        ImmediateParentId       NUMBER(11,0),
        Disabled        NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
CREATE INDEX CachedGroupMembers2 ON CachedGroupMembers (MemberId, GroupId, Disabled);
CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);


CREATE SEQUENCE USERS_seq;
CREATE TABLE Users (
        id                      NUMBER(11,0) 
                CONSTRAINT Users_Key PRIMARY KEY,
        Name                    VARCHAR2(200) CONSTRAINT Users_Name_Unique 
                unique  NOT NULL,
        Password                VARCHAR2(256),
        AuthToken               VARCHAR2(16),
        Comments                CLOB,
        Signature               CLOB,
        EmailAddress            VARCHAR2(120),
        FreeFormContactInfo     CLOB,
        Organization            VARCHAR2(200),
        RealName                VARCHAR2(120),
        NickName                VARCHAR2(16),
        Lang                    VARCHAR2(16),
        EmailEncoding           VARCHAR2(16),
        WebEncoding             VARCHAR2(16),
        ExternalContactInfoId   VARCHAR2(100),
        ContactInfoSystem       VARCHAR2(30),
        ExternalAuthId          VARCHAR2(100),
        AuthSystem              VARCHAR2(30),
        Gecos                   VARCHAR2(16),
        HomePhone               VARCHAR2(30),
        WorkPhone               VARCHAR2(30),
        MobilePhone             VARCHAR2(30),
        PagerPhone              VARCHAR2(30),
        Address1                VARCHAR2(200),
        Address2                VARCHAR2(200),
        City                    VARCHAR2(100),
        State                   VARCHAR2(100),
        Zip                     VARCHAR2(16),
        Country                 VARCHAR2(50),
        Timezone                VARCHAR2(50),
        PGPKey                  CLOB,
        SMIMECertificate        CLOB,
        Creator                 NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created                 DATE,
        LastUpdatedBy           NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated             DATE
);

CREATE UNIQUE INDEX Users1 ON Users (LOWER(Name));
CREATE INDEX Users4 ON Users (LOWER(EmailAddress));


CREATE SEQUENCE TICKETS_seq;
CREATE TABLE Tickets (
       id                      NUMBER(11, 0) 
               CONSTRAINT Tickets_Key PRIMARY KEY,
        EffectiveId             NUMBER(11,0) DEFAULT 0 NOT NULL,
        IsMerged                NUMBER(11,0) DEFAULT NULL NULL,
        Queue                   NUMBER(11,0) DEFAULT 0 NOT NULL,
        Type                    VARCHAR2(16),           
        IssueStatement          NUMBER(11,0) DEFAULT 0 NOT NULL,
        Resolution              NUMBER(11,0) DEFAULT 0 NOT NULL,
        Owner                   NUMBER(11,0) DEFAULT 0 NOT NULL,
        Subject                 VARCHAR2(200) DEFAULT '[no subject]', 
        InitialPriority         NUMBER(11,0) DEFAULT 0 NOT NULL,
        FinalPriority           NUMBER(11,0) DEFAULT 0 NOT NULL,
        Priority                NUMBER(11,0) DEFAULT 0 NOT NULL,
        TimeEstimated           NUMBER(11,0) DEFAULT 0 NOT NULL,
        TimeWorked              NUMBER(11,0) DEFAULT 0 NOT NULL,
        Status                  VARCHAR2(64),           
        TimeLeft                NUMBER(11,0) DEFAULT 0 NOT NULL,
        Told                    DATE,
        Starts                  DATE,
        Started                 DATE,
        Due                     DATE,
        Resolved                DATE,
        LastUpdatedBy           NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated             DATE,
        Creator                 NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created                 DATE,
        Disabled                NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE INDEX Tickets1 ON Tickets (Queue, Status);
CREATE INDEX Tickets2 ON Tickets (Owner);
CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);


CREATE SEQUENCE SCRIPACTIONS_seq;
CREATE TABLE ScripActions (
  id            NUMBER(11,0) 
                CONSTRAINT ScripActions_Key PRIMARY KEY,
  Name          VARCHAR2(200),
  Description   VARCHAR2(255),
  ExecModule    VARCHAR2(60),
  Argument      VARCHAR2(255),
  Creator       NUMBER(11,0) DEFAULT 0 NOT NULL,
  Created       DATE,
  LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
  LastUpdated   DATE
);


CREATE SEQUENCE TEMPLATES_seq;
CREATE TABLE Templates (
        id              NUMBER(11,0) 
                CONSTRAINT Templates_Key PRIMARY KEY,
        Queue           NUMBER(11,0) DEFAULT 0 NOT NULL,
        Name            VARCHAR2(200) NOT NULL,
        Description     VARCHAR2(255),
        Type            VARCHAR2(16),
        Content         CLOB,
        LastUpdated     DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE
);


CREATE SEQUENCE OBJECTCUSTOMFIELDS_seq;
CREATE TABLE ObjectCustomFields (
        id              NUMBER(11,0)
                 CONSTRAINT ObjectCustomFields_Key PRIMARY KEY,
        CustomField       NUMBER(11,0)  NOT NULL,
        ObjectId              NUMBER(11,0)  NOT NULL,
        SortOrder       NUMBER(11,0) DEFAULT 0 NOT NULL,
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE
);


CREATE SEQUENCE OBJECTCUSTOMFIELDVALUES_seq;
CREATE TABLE ObjectCustomFieldValues (
        id              NUMBER(11,0) 
                CONSTRAINT ObjectCustomFieldValues_Key PRIMARY KEY,
        CustomField     NUMBER(11,0) NOT NULL,
        ObjectType      VARCHAR2(25) NOT NULL,
        ObjectId        NUMBER(11,0) DEFAULT 0 NOT NULL,
        SortOrder       NUMBER(11,0) DEFAULT 0 NOT NULL,
        Content         VARCHAR2(255),
        LargeContent    CLOB,
        ContentType     VARCHAR2(80),
        ContentEncoding VARCHAR2(80),
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE,
        Disabled        NUMBER(11,0) DEFAULT 0 NOT NULL
);

CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content); 
CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); 

CREATE SEQUENCE CUSTOMFIELDS_seq;
CREATE TABLE CustomFields (
        id              NUMBER(11,0) 
                CONSTRAINT CustomFields_Key PRIMARY KEY,
        Name            VARCHAR2(200),
        Type            VARCHAR2(200),
        RenderType      VARCHAR2(64),
        MaxValues       NUMBER(11,0) DEFAULT 0 NOT NULL,
        Pattern         CLOB,
        ValuesClass     VARCHAR2(64),
        BasedOn         NUMBER(11,0) NULL,
        Description     VARCHAR2(255),
        SortOrder       NUMBER(11,0) DEFAULT 0 NOT NULL,
        LookupType      VARCHAR2(255),
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE,
        Disabled        NUMBER(11,0) DEFAULT 0 NOT NULL
);


CREATE SEQUENCE CUSTOMFIELDVALUES_seq;
CREATE TABLE CustomFieldValues (
        id              NUMBER(11,0) 
                CONSTRAINT CustomFieldValues_Key PRIMARY KEY,
        CustomField     NUMBER(11,0),
        Name            VARCHAR2(200),
        Description     VARCHAR2(255),
        SortOrder       NUMBER(11,0) DEFAULT 0 NOT NULL,
    Category    VARCHAR2(255),
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE,
        LastUpdatedBy   NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated     DATE
);

CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);

CREATE SEQUENCE ATTRIBUTES_seq;
CREATE TABLE Attributes (
        id                      NUMBER(11,0) PRIMARY KEY,
        Name                    VARCHAR2(255) NOT NULL,
        Description             VARCHAR2(255),
        Content         CLOB,
    ContentType VARCHAR(16),
        ObjectType      VARCHAR2(25) NOT NULL,
        ObjectId        NUMBER(11,0) DEFAULT 0 NOT NULL,
        Creator                 NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created                 DATE,
        LastUpdatedBy           NUMBER(11,0) DEFAULT 0 NOT NULL,
        LastUpdated             DATE
);

CREATE INDEX Attributes1 on Attributes(Name);
CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);


CREATE TABLE sessions (
        id              VARCHAR2(32) 
                CONSTRAINT Sessions_Key PRIMARY KEY,
        a_session       CLOB,
        LastUpdated     DATE
);

CREATE SEQUENCE Classes_seq;
CREATE TABLE Classes (
id NUMBER(11,0)
  CONSTRAINT Classes_key PRIMARY KEY,
Name varchar2(255) DEFAULT '',
Description varchar2(255) DEFAULT '',
SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
Disabled NUMBER(11,0) DEFAULT 0 NOT NULL,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE,
HotList NUMBER(11,0) DEFAULT 0 NOT NULL
);

CREATE SEQUENCE Articles_seq;
CREATE TABLE Articles (
id NUMBER(11,0)
  CONSTRAINT Articles_key PRIMARY KEY,
Name varchar2(255) DEFAULT '',
Summary varchar2(255) DEFAULT '',
SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
Class NUMBER(11,0) DEFAULT 0 NOT NULL,
Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
URI varchar2(255),
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
);


CREATE SEQUENCE Topics_seq;
CREATE TABLE Topics (
id NUMBER(11,0)
  CONSTRAINT Topics_key PRIMARY KEY,
Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
Name varchar2(255) DEFAULT '',
Description varchar2(255) DEFAULT '',
ObjectType varchar2(64) DEFAULT '' NOT NULL,
ObjectId NUMBER(11,0) NOT NULL
);


CREATE SEQUENCE ObjectTopics_seq;
CREATE TABLE ObjectTopics (
id NUMBER(11,0)
  CONSTRAINT ObjectTopics_key PRIMARY KEY,
Topic NUMBER(11,0) NOT NULL,
ObjectType varchar2(64) DEFAULT '' NOT NULL,
ObjectId NUMBER(11,0) NOT NULL
);

CREATE SEQUENCE ObjectClasses_seq;
CREATE TABLE ObjectClasses (
id NUMBER(11,0)
  CONSTRAINT ObjectClasses_key PRIMARY KEY,
Class NUMBER(11,0) NOT NULL,
ObjectType varchar2(255) DEFAULT '' NOT NULL,
ObjectId NUMBER(11,0) NOT NULL,
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE,
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
);
Commit	Line	Data
84fb5b46 MKG	1
	2	CREATE SEQUENCE ATTACHMENTS_seq;
	3	CREATE TABLE Attachments (
3ffc5f4f MKG	4	id NUMBER(11,0)
	5	CONSTRAINT Attachments_Key PRIMARY KEY,
	6	TransactionId NUMBER(11,0) NOT NULL,
	7	Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
	8	MessageId VARCHAR2(160),
	9	Subject VARCHAR2(255),
	10	Filename VARCHAR2(255),
	11	ContentType VARCHAR2(80),
	12	ContentEncoding VARCHAR2(80),
	13	Content CLOB,
	14	Headers CLOB,
	15	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	16	Created DATE
84fb5b46 MKG	17	);
	18	CREATE INDEX Attachments2 ON Attachments (TransactionId);
	19	CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
	20
	21
	22	CREATE SEQUENCE QUEUES_seq;
	23	CREATE TABLE Queues (
3ffc5f4f MKG	24	id NUMBER(11,0)
	25	CONSTRAINT Queues_Key PRIMARY KEY,
	26	Name VARCHAR2(200) CONSTRAINT Queues_Name_Unique UNIQUE NOT NULL,
	27	Description VARCHAR2(255),
	28	CorrespondAddress VARCHAR2(120),
	29	CommentAddress VARCHAR2(120),
	30	Lifecycle VARCHAR2(32),
	31	SubjectTag VARCHAR2(120),
	32	InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
	33	FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
	34	DefaultDueIn NUMBER(11,0) DEFAULT 0 NOT NULL,
	35	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	36	Created DATE,
	37	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	38	LastUpdated DATE,
	39	Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
84fb5b46	40	);
3ffc5f4f	41	CREATE UNIQUE INDEX Queues1 ON Queues (LOWER(Name));
84fb5b46 MKG	42	CREATE INDEX Queues2 ON Queues (Disabled);
	43
	44
	45	CREATE SEQUENCE LINKS_seq;
	46	CREATE TABLE Links (
3ffc5f4f MKG	47	id NUMBER(11,0)
	48	CONSTRAINT Links_Key PRIMARY KEY,
	49	Base VARCHAR2(240),
	50	Target VARCHAR2(240),
	51	Type VARCHAR2(20) NOT NULL,
	52	LocalTarget NUMBER(11,0) DEFAULT 0 NOT NULL,
	53	LocalBase NUMBER(11,0) DEFAULT 0 NOT NULL,
	54	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	55	LastUpdated DATE,
	56	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	57	Created DATE
84fb5b46 MKG	58	);
	59	CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
	60	CREATE INDEX Links2 ON Links (Base, Type);
	61	CREATE INDEX Links3 ON Links (Target, Type);
	62	CREATE INDEX Links4 ON Links(Type,LocalBase);
	63
	64
	65	CREATE SEQUENCE PRINCIPALS_seq;
	66	CREATE TABLE Principals (
3ffc5f4f MKG	67	id NUMBER(11,0)
	68	CONSTRAINT Principals_Key PRIMARY KEY,
	69	PrincipalType VARCHAR2(16),
	70	ObjectId NUMBER(11,0),
	71	Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
84fb5b46 MKG	72	);
	73	CREATE UNIQUE INDEX Principals2 ON Principals (ObjectId);
	74
	75
	76	CREATE SEQUENCE GROUPS_seq;
	77	CREATE TABLE Groups (
3ffc5f4f MKG	78	id NUMBER(11,0)
	79	CONSTRAINT Groups_Key PRIMARY KEY,
	80	Name VARCHAR2(200),
	81	Description VARCHAR2(255),
	82	Domain VARCHAR2(64),
	83	Type VARCHAR2(64),
	84	Instance NUMBER(11,0) DEFAULT 0, -- NOT NULL
	85	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	86	Created DATE,
	87	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	88	LastUpdated DATE
	89	-- Instance VARCHAR2(64)
84fb5b46	90	);
3ffc5f4f MKG	91	CREATE INDEX Groups1 ON Groups (LOWER(Domain), LOWER(Type), Instance);
	92	CREATE INDEX Groups2 ON Groups (LOWER(Domain), LOWER(Name), Instance);
	93	CREATE INDEX Groups3 ON Groups (Instance);
84fb5b46 MKG	94
	95
	96	CREATE SEQUENCE SCRIPCONDITIONS_seq;
	97	CREATE TABLE ScripConditions (
3ffc5f4f MKG	98	id NUMBER(11, 0)
	99	CONSTRAINT ScripConditions_Key PRIMARY KEY,
	100	Name VARCHAR2(200),
	101	Description VARCHAR2(255),
	102	ExecModule VARCHAR2(60),
	103	Argument VARCHAR2(255),
	104	ApplicableTransTypes VARCHAR2(60),
	105	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	106	Created DATE,
	107	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	108	LastUpdated DATE
84fb5b46 MKG	109	);
	110
	111
	112	CREATE SEQUENCE TRANSACTIONS_seq;
	113	CREATE TABLE Transactions (
3ffc5f4f MKG	114	id NUMBER(11,0)
	115	CONSTRAINT Transactions_Key PRIMARY KEY,
	116	ObjectType VARCHAR2(255),
	117	ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
	118	TimeTaken NUMBER(11,0) DEFAULT 0 NOT NULL,
	119	Type VARCHAR2(20),
	120	Field VARCHAR2(40),
	121	OldValue VARCHAR2(255),
	122	NewValue VARCHAR2(255),
	123	ReferenceType VARCHAR2(255),
	124	OldReference NUMBER(11,0),
	125	NewReference NUMBER(11,0),
	126	Data VARCHAR2(255),
	127	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	128	Created DATE
84fb5b46 MKG	129	);
	130	CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
	131
	132
	133	CREATE SEQUENCE SCRIPS_seq;
	134	CREATE TABLE Scrips (
3ffc5f4f MKG	135	id NUMBER(11,0)
	136	CONSTRAINT Scrips_Key PRIMARY KEY,
	137	Description VARCHAR2(255),
	138	ScripCondition NUMBER(11,0) DEFAULT 0 NOT NULL,
	139	ScripAction NUMBER(11,0) DEFAULT 0 NOT NULL,
	140	CustomIsApplicableCode CLOB,
	141	CustomPrepareCode CLOB,
	142	CustomCommitCode CLOB,
	143	Disabled NUMBER(11,0) DEFAULT 0 NOT NULL,
	144	Template VARCHAR2(200) NOT NULL,
	145	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	146	Created DATE,
	147	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	148	LastUpdated DATE
84fb5b46 MKG	149	);
84fb5b46 MKG	150
3ffc5f4f MKG	151	CREATE SEQUENCE OBJECTSCRIPS_seq;
	152	CREATE TABLE ObjectScrips (
	153	id NUMBER(11,0)
	154	CONSTRAINT ObjectScrips_Key PRIMARY KEY,
	155	Scrip NUMBER(11,0) NOT NULL,
	156	Stage VARCHAR2(32) DEFAULT 'TransactionCreate' NOT NULL,
	157	ObjectId NUMBER(11,0) NOT NULL,
	158	SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
	159	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	160	Created DATE,
	161	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	162	LastUpdated DATE
	163	);
	164	CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);
84fb5b46 MKG	165
	166	CREATE SEQUENCE ACL_seq;
	167	CREATE TABLE ACL (
3ffc5f4f MKG	168	id NUMBER(11,0)
	169	CONSTRAINT ACL_Key PRIMARY KEY,
	170	PrincipalType VARCHAR2(25) NOT NULL,
	171	PrincipalId NUMBER(11,0) NOT NULL,
	172	RightName VARCHAR2(25) NOT NULL,
	173	ObjectType VARCHAR2(25) NOT NULL,
	174	ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
	175	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	176	Created DATE,
	177	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	178	LastUpdated DATE
84fb5b46 MKG	179	);
	180	CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);
	181
	182
	183	CREATE SEQUENCE GROUPMEMBERS_seq;
	184	CREATE TABLE GroupMembers (
3ffc5f4f MKG	185	id NUMBER(11,0)
	186	CONSTRAINT GroupMembers_Key PRIMARY KEY,
	187	GroupId NUMBER(11,0) DEFAULT 0 NOT NULL,
	188	MemberId NUMBER(11,0) DEFAULT 0 NOT NULL,
	189	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	190	Created DATE,
	191	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	192	LastUpdated DATE
84fb5b46 MKG	193	);
	194	CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);
	195
	196
	197	CREATE SEQUENCE CachedGroupMembers_seq;
	198	CREATE TABLE CachedGroupMembers (
3ffc5f4f MKG	199	id NUMBER(11,0)
	200	CONSTRAINT CachedGroupMembers_Key PRIMARY KEY,
	201	GroupId NUMBER(11,0),
	202	MemberId NUMBER(11,0),
	203	Via NUMBER(11,0),
	204	ImmediateParentId NUMBER(11,0),
	205	Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
84fb5b46 MKG	206	);
84fb5b46 MKG	207	CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
3ffc5f4f	208	CREATE INDEX CachedGroupMembers2 ON CachedGroupMembers (MemberId, GroupId, Disabled);
84fb5b46 MKG	209	CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
	210
	211
	212	CREATE SEQUENCE USERS_seq;
	213	CREATE TABLE Users (
3ffc5f4f MKG	214	id NUMBER(11,0)
	215	CONSTRAINT Users_Key PRIMARY KEY,
	216	Name VARCHAR2(200) CONSTRAINT Users_Name_Unique
	217	unique NOT NULL,
	218	Password VARCHAR2(256),
	219	AuthToken VARCHAR2(16),
	220	Comments CLOB,
	221	Signature CLOB,
	222	EmailAddress VARCHAR2(120),
	223	FreeFormContactInfo CLOB,
	224	Organization VARCHAR2(200),
	225	RealName VARCHAR2(120),
	226	NickName VARCHAR2(16),
	227	Lang VARCHAR2(16),
	228	EmailEncoding VARCHAR2(16),
	229	WebEncoding VARCHAR2(16),
	230	ExternalContactInfoId VARCHAR2(100),
	231	ContactInfoSystem VARCHAR2(30),
	232	ExternalAuthId VARCHAR2(100),
	233	AuthSystem VARCHAR2(30),
	234	Gecos VARCHAR2(16),
	235	HomePhone VARCHAR2(30),
	236	WorkPhone VARCHAR2(30),
	237	MobilePhone VARCHAR2(30),
	238	PagerPhone VARCHAR2(30),
	239	Address1 VARCHAR2(200),
	240	Address2 VARCHAR2(200),
	241	City VARCHAR2(100),
	242	State VARCHAR2(100),
	243	Zip VARCHAR2(16),
	244	Country VARCHAR2(50),
	245	Timezone VARCHAR2(50),
	246	PGPKey CLOB,
	247	SMIMECertificate CLOB,
	248	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	249	Created DATE,
	250	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	251	LastUpdated DATE
84fb5b46	252	);
84fb5b46	253
3ffc5f4f	254	CREATE UNIQUE INDEX Users1 ON Users (LOWER(Name));
84fb5b46 MKG	255	CREATE INDEX Users4 ON Users (LOWER(EmailAddress));
	256
	257
	258	CREATE SEQUENCE TICKETS_seq;
	259	CREATE TABLE Tickets (
	260	id NUMBER(11, 0)
	261	CONSTRAINT Tickets_Key PRIMARY KEY,
3ffc5f4f MKG	262	EffectiveId NUMBER(11,0) DEFAULT 0 NOT NULL,
	263	IsMerged NUMBER(11,0) DEFAULT NULL NULL,
	264	Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
	265	Type VARCHAR2(16),
	266	IssueStatement NUMBER(11,0) DEFAULT 0 NOT NULL,
	267	Resolution NUMBER(11,0) DEFAULT 0 NOT NULL,
	268	Owner NUMBER(11,0) DEFAULT 0 NOT NULL,
	269	Subject VARCHAR2(200) DEFAULT '[no subject]',
	270	InitialPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
	271	FinalPriority NUMBER(11,0) DEFAULT 0 NOT NULL,
	272	Priority NUMBER(11,0) DEFAULT 0 NOT NULL,
	273	TimeEstimated NUMBER(11,0) DEFAULT 0 NOT NULL,
	274	TimeWorked NUMBER(11,0) DEFAULT 0 NOT NULL,
	275	Status VARCHAR2(64),
	276	TimeLeft NUMBER(11,0) DEFAULT 0 NOT NULL,
	277	Told DATE,
	278	Starts DATE,
	279	Started DATE,
	280	Due DATE,
	281	Resolved DATE,
	282	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	283	LastUpdated DATE,
	284	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	285	Created DATE,
	286	Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
84fb5b46 MKG	287	);
	288	CREATE INDEX Tickets1 ON Tickets (Queue, Status);
	289	CREATE INDEX Tickets2 ON Tickets (Owner);
84fb5b46 MKG	290	CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);
	291
	292
	293	CREATE SEQUENCE SCRIPACTIONS_seq;
	294	CREATE TABLE ScripActions (
3ffc5f4f MKG	295	id NUMBER(11,0)
	296	CONSTRAINT ScripActions_Key PRIMARY KEY,
	297	Name VARCHAR2(200),
	298	Description VARCHAR2(255),
	299	ExecModule VARCHAR2(60),
	300	Argument VARCHAR2(255),
	301	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	302	Created DATE,
	303	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	304	LastUpdated DATE
84fb5b46 MKG	305	);
	306
	307
	308	CREATE SEQUENCE TEMPLATES_seq;
	309	CREATE TABLE Templates (
3ffc5f4f MKG	310	id NUMBER(11,0)
	311	CONSTRAINT Templates_Key PRIMARY KEY,
	312	Queue NUMBER(11,0) DEFAULT 0 NOT NULL,
	313	Name VARCHAR2(200) NOT NULL,
	314	Description VARCHAR2(255),
	315	Type VARCHAR2(16),
	316	Content CLOB,
	317	LastUpdated DATE,
	318	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	319	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	320	Created DATE
84fb5b46 MKG	321	);
	322
	323
	324	CREATE SEQUENCE OBJECTCUSTOMFIELDS_seq;
	325	CREATE TABLE ObjectCustomFields (
3ffc5f4f	326	id NUMBER(11,0)
84fb5b46 MKG	327	CONSTRAINT ObjectCustomFields_Key PRIMARY KEY,
	328	CustomField NUMBER(11,0) NOT NULL,
	329	ObjectId NUMBER(11,0) NOT NULL,
3ffc5f4f MKG	330	SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
	331	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	332	Created DATE,
	333	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	334	LastUpdated DATE
84fb5b46 MKG	335	);
	336
	337
	338	CREATE SEQUENCE OBJECTCUSTOMFIELDVALUES_seq;
	339	CREATE TABLE ObjectCustomFieldValues (
3ffc5f4f MKG	340	id NUMBER(11,0)
	341	CONSTRAINT ObjectCustomFieldValues_Key PRIMARY KEY,
	342	CustomField NUMBER(11,0) NOT NULL,
	343	ObjectType VARCHAR2(25) NOT NULL,
	344	ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
	345	SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
	346	Content VARCHAR2(255),
	347	LargeContent CLOB,
	348	ContentType VARCHAR2(80),
	349	ContentEncoding VARCHAR2(80),
	350	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	351	Created DATE,
	352	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	353	LastUpdated DATE,
	354	Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
84fb5b46 MKG	355	);
	356
	357	CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
	358	CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
	359
	360	CREATE SEQUENCE CUSTOMFIELDS_seq;
	361	CREATE TABLE CustomFields (
3ffc5f4f MKG	362	id NUMBER(11,0)
	363	CONSTRAINT CustomFields_Key PRIMARY KEY,
	364	Name VARCHAR2(200),
	365	Type VARCHAR2(200),
	366	RenderType VARCHAR2(64),
	367	MaxValues NUMBER(11,0) DEFAULT 0 NOT NULL,
	368	Pattern CLOB,
	369	ValuesClass VARCHAR2(64),
84fb5b46	370	BasedOn NUMBER(11,0) NULL,
3ffc5f4f MKG	371	Description VARCHAR2(255),
	372	SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
	373	LookupType VARCHAR2(255),
	374	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	375	Created DATE,
	376	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	377	LastUpdated DATE,
	378	Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
84fb5b46 MKG	379	);
	380
	381
	382	CREATE SEQUENCE CUSTOMFIELDVALUES_seq;
	383	CREATE TABLE CustomFieldValues (
3ffc5f4f MKG	384	id NUMBER(11,0)
	385	CONSTRAINT CustomFieldValues_Key PRIMARY KEY,
	386	CustomField NUMBER(11,0),
	387	Name VARCHAR2(200),
	388	Description VARCHAR2(255),
	389	SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
84fb5b46	390	Category VARCHAR2(255),
3ffc5f4f MKG	391	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	392	Created DATE,
	393	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	394	LastUpdated DATE
84fb5b46 MKG	395	);
	396
	397	CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
	398
	399	CREATE SEQUENCE ATTRIBUTES_seq;
	400	CREATE TABLE Attributes (
3ffc5f4f MKG	401	id NUMBER(11,0) PRIMARY KEY,
	402	Name VARCHAR2(255) NOT NULL,
	403	Description VARCHAR2(255),
	404	Content CLOB,
84fb5b46	405	ContentType VARCHAR(16),
3ffc5f4f MKG	406	ObjectType VARCHAR2(25) NOT NULL,
	407	ObjectId NUMBER(11,0) DEFAULT 0 NOT NULL,
	408	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	409	Created DATE,
	410	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	411	LastUpdated DATE
84fb5b46 MKG	412	);
	413
	414	CREATE INDEX Attributes1 on Attributes(Name);
	415	CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
	416
	417
	418	CREATE TABLE sessions (
3ffc5f4f MKG	419	id VARCHAR2(32)
	420	CONSTRAINT Sessions_Key PRIMARY KEY,
	421	a_session CLOB,
	422	LastUpdated DATE
84fb5b46 MKG	423	);
	424
	425	CREATE SEQUENCE Classes_seq;
	426	CREATE TABLE Classes (
	427	id NUMBER(11,0)
	428	CONSTRAINT Classes_key PRIMARY KEY,
	429	Name varchar2(255) DEFAULT '',
	430	Description varchar2(255) DEFAULT '',
	431	SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
	432	Disabled NUMBER(11,0) DEFAULT 0 NOT NULL,
	433	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	434	Created DATE,
	435	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	436	LastUpdated DATE,
	437	HotList NUMBER(11,0) DEFAULT 0 NOT NULL
	438	);
	439
	440	CREATE SEQUENCE Articles_seq;
	441	CREATE TABLE Articles (
	442	id NUMBER(11,0)
	443	CONSTRAINT Articles_key PRIMARY KEY,
	444	Name varchar2(255) DEFAULT '',
	445	Summary varchar2(255) DEFAULT '',
	446	SortOrder NUMBER(11,0) DEFAULT 0 NOT NULL,
	447	Class NUMBER(11,0) DEFAULT 0 NOT NULL,
	448	Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
	449	URI varchar2(255),
	450	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	451	Created DATE,
	452	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
	453	LastUpdated DATE
	454	);
	455
	456
	457	CREATE SEQUENCE Topics_seq;
	458	CREATE TABLE Topics (
	459	id NUMBER(11,0)
	460	CONSTRAINT Topics_key PRIMARY KEY,
	461	Parent NUMBER(11,0) DEFAULT 0 NOT NULL,
	462	Name varchar2(255) DEFAULT '',
	463	Description varchar2(255) DEFAULT '',
	464	ObjectType varchar2(64) DEFAULT '' NOT NULL,
	465	ObjectId NUMBER(11,0) NOT NULL
	466	);
	467
	468
	469	CREATE SEQUENCE ObjectTopics_seq;
	470	CREATE TABLE ObjectTopics (
	471	id NUMBER(11,0)
	472	CONSTRAINT ObjectTopics_key PRIMARY KEY,
	473	Topic NUMBER(11,0) NOT NULL,
	474	ObjectType varchar2(64) DEFAULT '' NOT NULL,
	475	ObjectId NUMBER(11,0) NOT NULL
	476	);
	477
	478	CREATE SEQUENCE ObjectClasses_seq;
	479	CREATE TABLE ObjectClasses (
	480	id NUMBER(11,0)
	481	CONSTRAINT ObjectClasses_key PRIMARY KEY,
	482	Class NUMBER(11,0) NOT NULL,
	483	ObjectType varchar2(255) DEFAULT '' NOT NULL,
	484	ObjectId NUMBER(11,0) NOT NULL,
	485	Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
	486	Created DATE,
487	LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
488	LastUpdated DATE
489	);