--
------------------------------------------------------------------
---
--- Deal with changed defaults of Pg 8.x
---
-SELECT CASE WHEN version() like '%PostgreSQL 8%" THEN
- set_config('default_with_oids','on','f')
-END;
--
CREATE SEQUENCE attachments_id_seq;
--- {{{ Attachments
+
CREATE TABLE Attachments (
id INTEGER DEFAULT nextval('attachments_id_seq'),
CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
--- }}}
--- {{{ Queues
+
+
--
Description varchar(255) NULL ,
CorrespondAddress varchar(120) NULL ,
CommentAddress varchar(120) NULL ,
+ Lifecycle varchar(32) NULL,
+ SubjectTag varchar(120) NULL ,
InitialPriority integer NOT NULL DEFAULT 0 ,
FinalPriority integer NOT NULL DEFAULT 0 ,
DefaultDueIn integer NOT NULL DEFAULT 0 ,
);
CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
--- }}}
--- {{{ Links
+
+
CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
CREATE INDEX Links4 ON Links(Type,LocalBase);
--- }}}
--- {{{ Principals
+
+
CREATE INDEX Principals2 ON Principals (ObjectId);
--- }}}
--- {{{ Groups
+
+
Domain varchar(64),
Type varchar(64),
Instance integer,
+ Creator integer NOT NULL DEFAULT 0 ,
+ Created TIMESTAMP NULL ,
+ LastUpdatedBy integer NOT NULL DEFAULT 0 ,
+ LastUpdated TIMESTAMP NULL ,
PRIMARY KEY (id)
);
CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
--- }}}
--- {{{ ScripConditions
+
+
);
--- }}}
--- {{{ Transactions
+
+
--
);
CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
--- }}}
--- {{{ Scrips
+
+
);
--- }}}
--- {{{ ACL
+
+
--
RightName varchar(25) NOT NULL ,
ObjectType varchar(25) NOT NULL ,
ObjectId integer NOT NULL DEFAULT 0,
- DelegatedBy integer NOT NULL DEFAULT 0,
- DelegatedFrom integer NOT NULL DEFAULT 0,
+ Creator integer NOT NULL DEFAULT 0 ,
+ Created TIMESTAMP NULL ,
+ LastUpdatedBy integer NOT NULL DEFAULT 0 ,
+ LastUpdated TIMESTAMP NULL ,
PRIMARY KEY (id)
);
CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
--- }}}
--- {{{ GroupMembers
+
+
id INTEGER DEFAULT nextval('groupmembers_id_seq'),
GroupId integer NOT NULL DEFAULT 0,
MemberId integer NOT NULL DEFAULT 0,
+ Creator integer NOT NULL DEFAULT 0 ,
+ Created TIMESTAMP NULL ,
+ LastUpdatedBy integer NOT NULL DEFAULT 0 ,
+ LastUpdated TIMESTAMP NULL ,
PRIMARY KEY (id)
);
--- }}}
+CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId);
+
+
+
--- {{{ GroupMembersCache
CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);
CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
--- }}}
--- {{{ Users
+
+
CREATE TABLE Users (
id INTEGER DEFAULT nextval('users_id_seq'),
Name varchar(200) NOT NULL ,
- Password varchar(40) NULL ,
+ Password varchar(256) NULL ,
+ AuthToken varchar(16) NULL ,
Comments text NULL ,
Signature text NULL ,
EmailAddress varchar(120) NULL ,
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
+
+
Priority integer NOT NULL DEFAULT 0 ,
TimeEstimated integer NOT NULL DEFAULT 0 ,
TimeWorked integer NOT NULL DEFAULT 0 ,
- Status varchar(10) NULL ,
+ Status varchar(64) NULL ,
TimeLeft integer NOT NULL DEFAULT 0 ,
Told TIMESTAMP NULL ,
Starts TIMESTAMP NULL ,
Due TIMESTAMP NULL ,
Resolved TIMESTAMP NULL ,
+ WillResolve TIMESTAMP NULL ,
LastUpdatedBy integer NOT NULL DEFAULT 0 ,
LastUpdated TIMESTAMP NULL ,
CREATE INDEX Tickets4 ON Tickets (id, Status) ;
CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
--- }}}
--- {{{ ScripActions
+
+
);
--- }}}
--- {{{ Templates
+
+
);
--- }}}
--- {{{ ObjectCustomFieldValues
+
+
CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
--- }}}
--- {{{ CustomFields
+
+
id INTEGER DEFAULT nextval('customfields_id_seq'),
Name varchar(200) NULL ,
Type varchar(200) NULL ,
+ RenderType varchar(64) NULL ,
MaxValues integer NOT NULL DEFAULT 0 ,
Repeated integer NOT NULL DEFAULT 0 ,
- Pattern varchar(255) NULL ,
+ ValuesClass varchar(64) NULL ,
+ BasedOn integer NULL,
+ Pattern varchar(65536) NULL ,
LookupType varchar(255) NOT NULL ,
Description varchar(255) NULL ,
SortOrder integer NOT NULL DEFAULT 0 ,
LastUpdatedBy integer NOT NULL DEFAULT 0 ,
LastUpdated TIMESTAMP NULL ,
Disabled integer NOT NULL DEFAULT 0 ,
+ Required integer NOT NULL DEFAULT 0 ,
PRIMARY KEY (id)
);
--- }}}
--- {{{ ObjectCustomFields
+
+
CREATE SEQUENCE objectcustomfields_id_s;
);
--- }}}
--- {{{ CustomFieldValues
+
+
Name varchar(200) NULL ,
Description varchar(255) NULL ,
SortOrder integer NOT NULL DEFAULT 0 ,
+ Category varchar(255) NULL ,
Creator integer NOT NULL DEFAULT 0 ,
Created TIMESTAMP NULL ,
CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
--- }}}
--- {{{ Attributes
+
+
CREATE SEQUENCE attributes_id_seq;
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 Classes (
+id SERIAL,
+Name varchar(255) NOT NULL DEFAULT '',
+Description varchar(255) NOT NULL DEFAULT '',
+SortOrder integer NOT NULL DEFAULT 0,
+Disabled smallint NOT NULL DEFAULT 0,
+Creator integer NOT NULL DEFAULT 0,
+Created TIMESTAMP NULL,
+LastUpdatedBy integer NOT NULL DEFAULT 0,
+LastUpdated TIMESTAMP NULL,
+HotList smallint NOT NULL DEFAULT 0,
+PRIMARY KEY (id)
+);
+
+CREATE TABLE Articles (
+id SERIAL,
+Name varchar(255) NOT NULL DEFAULT '',
+Summary varchar(255) NOT NULL DEFAULT '',
+SortOrder integer NOT NULL DEFAULT 0,
+Class integer NOT NULL DEFAULT 0,
+Parent integer NOT NULL DEFAULT 0,
+URI varchar(255),
+Creator integer NOT NULL DEFAULT 0,
+Created TIMESTAMP NULL,
+LastUpdatedBy integer NOT NULL DEFAULT 0,
+LastUpdated TIMESTAMP NULL,
+PRIMARY KEY (id)
+);
+
+
+CREATE TABLE Topics (
+id SERIAL,
+Parent integer NOT NULL DEFAULT 0,
+Name varchar(255) NOT NULL DEFAULT '',
+Description varchar(255) NOT NULL DEFAULT '',
+ObjectType varchar(64) NOT NULL DEFAULT '',
+ObjectId integer NOT NULL,
+PRIMARY KEY (id)
+);
+
+
+CREATE TABLE ObjectTopics (
+id SERIAL,
+Topic integer NOT NULL,
+ObjectType varchar(64) NOT NULL DEFAULT '',
+ObjectId integer NOT NULL,
+PRIMARY KEY (id)
+);
+
+
+CREATE TABLE ObjectClasses (
+id SERIAL,
+Class integer NOT NULL,
+ObjectType varchar(255) NOT NULL DEFAULT '',
+ObjectId integer NOT NULL,
+Creator integer NOT NULL DEFAULT 0,
+Created TIMESTAMP NULL,
+LastUpdatedBy integer NOT NULL DEFAULT 0,
+LastUpdated TIMESTAMP NULL,
+PRIMARY KEY (id)
+);