diff options
Diffstat (limited to 'rt/etc')
| -rw-r--r-- | rt/etc/acl.Oracle | 12 | ||||
| -rw-r--r-- | rt/etc/schema.Oracle | 354 | ||||
| -rwxr-xr-x | rt/etc/schema.Pg | 16 | ||||
| -rwxr-xr-x | rt/etc/schema.mysql | 10 |
4 files changed, 16 insertions, 376 deletions
diff --git a/rt/etc/acl.Oracle b/rt/etc/acl.Oracle index ac29215c2..c8667c031 100644 --- a/rt/etc/acl.Oracle +++ b/rt/etc/acl.Oracle @@ -1,10 +1,10 @@ sub acl { return ( -"CREATE USER ${RT::DatabaseUser} identified by ${RT::DatabasePassword} ". -"default tablespace USERS " . -"temporary tablespace TEMP " . -"quota unlimited on USERS" , -"grant connect, resource to ${RT::DatabaseUser}" -); +"CREATE USER ${RT::DatabaseUser} identified by ${RT::DatabasePassword}". +"temporary tablespace TEMP" . +"default tablespace USERS" . +"quota unlimited on USERS;" , +"grant connect, resource to ${RT::DatabaseUser};", +"exit;"); } 1; diff --git a/rt/etc/schema.Oracle b/rt/etc/schema.Oracle deleted file mode 100644 index 95cfda2fd..000000000 --- a/rt/etc/schema.Oracle +++ /dev/null @@ -1,354 +0,0 @@ -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), - 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 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 --- Instance VARCHAR2(64) -); -CREATE INDEX Groups1 ON Groups (lower( Domain), Instance, lower(Type), id); -CREATE INDEX Groups2 ON Groups (lower(Type), Instance, lower(Domain)); - - -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, - EffectiveTicket NUMBER(11,0) DEFAULT 0 NOT NULL, - Ticket 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), - Data VARCHAR2(255), - Creator NUMBER(11,0) DEFAULT 0 NOT NULL, - Created DATE -); -CREATE INDEX Transactions1 ON Transactions (Ticket); -CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); - - -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, - ConditionRules CLOB, - ActionRules CLOB, - CustomIsApplicableCode CLOB, - CustomPrepareCode CLOB, - CustomCommitCode CLOB, - Stage VARCHAR2(32), - Queue NUMBER(11,0) DEFAULT 0 NOT NULL, - Template 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 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, - DelegatedBy NUMBER(11,0) DEFAULT 0 NOT NULL, - DelegatedFrom NUMBER(11,0) DEFAULT 0 NOT NULL -); -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 -); -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 GrouMem ON CachedGroupMembers (GroupId, MemberId); - - -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(40), - 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, - 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 (Name); - -CREATE INDEX Users2 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, - 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(10), - 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 Tickets4 ON Tickets (id, Status); -CREATE INDEX Tickets5 ON Tickets (id, EffectiveId); -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), - Language VARCHAR2(16), - TranslationOf NUMBER(11,0) DEFAULT 0 NOT NULL, - Content CLOB, - LastUpdated DATE, - LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL, - Creator NUMBER(11,0) DEFAULT 0 NOT NULL, - Created DATE -); - - -CREATE SEQUENCE TICKETCUSTOMFIELDVALUES_seq; -CREATE TABLE TicketCustomFieldValues ( - id NUMBER(11,0) - CONSTRAINT TicketCustomFieldValues_Key PRIMARY KEY, - Ticket NUMBER(11,0), - CustomField NUMBER(11,0) NOT NULL, - Content VARCHAR2(255), - Creator NUMBER(11,0) DEFAULT 0 NOT NULL, - Created DATE, - LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL, - LastUpdated DATE -); - -CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content); -CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket); - -CREATE SEQUENCE CUSTOMFIELDS_seq; -CREATE TABLE CustomFields ( - id NUMBER(11,0) - CONSTRAINT CustomFields_Key PRIMARY KEY, - Name VARCHAR2(200), - Type VARCHAR2(200), - Queue NUMBER(11,0) DEFAULT 0 NOT NULL, - Description VARCHAR2(255), - 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, - Disabled NUMBER(11,0) DEFAULT 0 NOT NULL -); -CREATE INDEX CustomFields1 ON CustomFields (Disabled, Queue); - - -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, - 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 TABLE sessions ( - id VARCHAR2(32) - CONSTRAINT Sessions_Key PRIMARY KEY, - a_session CLOB, - LastUpdated DATE -); - diff --git a/rt/etc/schema.Pg b/rt/etc/schema.Pg index 085c61595..ba0d6fc6c 100755 --- a/rt/etc/schema.Pg +++ b/rt/etc/schema.Pg @@ -3,6 +3,9 @@ -- ------------------------------------------------------------------ +BEGIN; + + -- @@ -90,7 +93,6 @@ CREATE TABLE Links ( ); CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ; -CREATE INDEX Links4 ON Links(Type,LocalBase); -- }}} @@ -134,7 +136,7 @@ CREATE TABLE Groups ( Description varchar(255) NULL , Domain varchar(64), Type varchar(64), - Instance integer, + Instance varchar(64), PRIMARY KEY (id) ); @@ -190,7 +192,7 @@ CREATE TABLE Transactions ( Field varchar(40) NULL , OldValue varchar(255) NULL , NewValue varchar(255) NULL , - Data varchar(255) NULL , + Data varchar(100) NULL , Creator integer NOT NULL DEFAULT 0 , Created TIMESTAMP NULL , @@ -498,9 +500,6 @@ CREATE TABLE TicketCustomFieldValues ( ); -CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content); -CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket); - -- }}} -- {{{ CustomFields @@ -557,8 +556,6 @@ CREATE TABLE CustomFieldValues ( ); -CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); - -- }}} -- {{{ Sessions @@ -576,3 +573,6 @@ CREATE TABLE sessions ( -- }}} + + +COMMIT; diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql index 14e92238f..46f8ec562 100755 --- a/rt/etc/schema.mysql +++ b/rt/etc/schema.mysql @@ -62,7 +62,6 @@ CREATE TABLE Links ( 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); # }}} @@ -88,7 +87,7 @@ CREATE TABLE Groups ( Description varchar(255) NULL , Domain varchar(64), Type varchar(64), - Instance integer, + Instance varchar(64), PRIMARY KEY (id) ) TYPE=InnoDB; @@ -126,7 +125,7 @@ CREATE TABLE Transactions ( Field varchar(40) NULL , OldValue varchar(255) NULL , NewValue varchar(255) NULL , - Data varchar(255) NULL , + Data varchar(100) NULL , Creator integer NOT NULL DEFAULT 0 , Created DATETIME NULL , @@ -359,9 +358,6 @@ CREATE TABLE TicketCustomFieldValues ( PRIMARY KEY (id) ) TYPE=InnoDB; -CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content); -CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket); - # }}} # {{{ CustomFields @@ -403,8 +399,6 @@ CREATE TABLE CustomFieldValues ( PRIMARY KEY (id) ) TYPE=InnoDB; -CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); - # }}} # {{{ Sessions |