rt/etc/schema.Oracle

   1 CREATE SEQUENCE KEYWORDSELECTS_seq;
   2 CREATE TABLE KeywordSelects (
   3         id              NUMBER(11, 0) PRIMARY KEY,
   4         Name            VARCHAR2(255),
   5         Keyword         NUMBER(11, 0),
   6         Single          NUMBER(11, 0),
   7         Depth           NUMBER(11, 0) DEFAULT 0,
   8         ObjectType      VARCHAR2(32) NOT NULL,
   9         ObjectField     VARCHAR2(32),
  10         ObjectValue     VARCHAR2(255),
  11         Disabled                NUMBER(11, 0) DEFAULT 0
  12 );
  13
  14 CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword);
  15 CREATE INDEX KeywordSelects2 ON
  16         KeywordSelects(ObjectType, ObjectField, ObjectValue);
  17
  18
  19 CREATE SEQUENCE ATTACHMENTS_seq;
  20 CREATE TABLE Attachments (
  21         id              NUMBER(11,0) PRIMARY KEY,
  22         TransactionId   NUMBER(11,0) NOT NULL,
  23         Parent          NUMBER(11,0),
  24         MessageId       VARCHAR2(160),
  25         Subject         VARCHAR2(255),
  26         Filename        VARCHAR2(255),
  27         ContentType     VARCHAR2(80),
  28         ContentEncoding         VARCHAR2(80),
  29         Content         CLOB,
  30         Headers         CLOB,
  31         Creator         NUMBER(11,0),
  32         Created         DATE,
  33         Disabled        NUMBER(11,0) DEFAULT 0
  34 );
  35
  36 CREATE SEQUENCE QUEUES_seq;
  37 CREATE TABLE Queues (
  38         id                      NUMBER(11, 0) PRIMARY KEY,
  39         Name                    VARCHAR2(40) NOT NULL UNIQUE,
  40         Description             VARCHAR2(120),
  41         CorrespondAddress       VARCHAR2(40),
  42         CommentAddress          VARCHAR2(40),
  43         InitialPriority         NUMBER(11, 0),
  44         FinalPriority           NUMBER(11, 0),
  45         DefaultDueIn            NUMBER(11, 0),
  46         Creator                 NUMBER(11, 0),
  47         Created                 DATE,
  48         LastUpdatedBy           NUMBER(11, 0),
  49         LastUpdated             DATE,
  50         Disabled                NUMBER(11,0) DEFAULT 0
  51 );
  52
  53 CREATE SEQUENCE LINKS_seq;
  54 CREATE TABLE Links (
  55         id              NUMBER(11,0) PRIMARY KEY,
  56         Base            VARCHAR2(255),
  57         Target          VARCHAR2(255),
  58         Type            VARCHAR2(20) NOT NULL,
  59         LocalTarget     NUMBER(11,0),
  60         LocalBase       NUMBER(11,0),
  61         LastUpdatedBy   NUMBER(11,0),
  62         LastUpdated     DATE,
  63         Creator         NUMBER(11,0),
  64         Created         DATE
  65 );
  66
  67 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
  68
  69
  70
  71 CREATE SEQUENCE GROUPS_seq;
  72 CREATE TABLE Groups (
  73         id              NUMBER(11,0) PRIMARY KEY,
  74         Name            VARCHAR2(16) UNIQUE,
  75         Description     VARCHAR(64),
  76         Pseudo          NUMBER(11,0) DEFAULT 0
  77 );
  78
  79 CREATE SEQUENCE WATCHERS_seq;
  80 CREATE TABLE Watchers (
  81         id              NUMBER(11,0) PRIMARY KEY,
  82         Type            VARCHAR2(16),
  83         Scope           VARCHAR2(16),
  84         Value           NUMBER(11,0),
  85         Email           VARCHAR2(255),
  86         Quiet           NUMBER(11,0),
  87         Owner           NUMBER(11,0),
  88         Creator         NUMBER(11,0),
  89         Created         DATE,
  90         LastUpdatedBy   NUMBER(11,0),
  91         LastUpdated     DATE
  92 );
  93
  94
  95
  96 CREATE SEQUENCE SCRIPCONDITIONS_seq;
  97 CREATE TABLE ScripConditions (
  98         id                      NUMBER(11, 0) PRIMARY KEY,
  99         Name                    VARCHAR2(255),
 100         Description             VARCHAR2(255),
 101         ExecModule              VARCHAR2(60),
 102         Argument                VARCHAR2(255),
 103         ApplicableTransTypes    VARCHAR2(60),
 104         Creator                 NUMBER(11, 0),
 105         Created                 DATE,
 106         LastUpdatedBy           NUMBER(11, 0),
 107         LastUpdated             DATE
 108 );
 109
 110
 111 CREATE SEQUENCE TRANSACTIONS_seq;
 112 CREATE TABLE Transactions (
 113         id                      NUMBER(11,0) PRIMARY KEY,
 114         EffectiveTicket         NUMBER(11,0),
 115         Ticket                  NUMBER(11,0),
 116         TimeTaken               NUMBER(11,0),
 117         Type                    VARCHAR2(20),
 118         Field                   VARCHAR2(40),
 119         OldValue                VARCHAR2(255),
 120         NewValue                VARCHAR2(255),
 121         Data                    VARCHAR2(100),
 122         Creator                 NUMBER(11,0),
 123         Created                 DATE,
 124         Disabled                NUMBER(11,0) DEFAULT 0
 125 );
 126
 127 CREATE SEQUENCE SCRIPS_seq;
 128 CREATE TABLE Scrips (
 129         id              NUMBER(11,0) PRIMARY KEY,
 130         ScripCondition  NUMBER(11,0),
 131         ScripAction     NUMBER(11,0),
 132         Stage           VARCHAR2(32),
 133         Queue           NUMBER(11,0),
 134         Template        NUMBER(11,0),
 135         Creator         NUMBER(11,0),
 136         Created         DATE,
 137         LastUpdatedBy   NUMBER(11,0),
 138         LastUpdated     DATE
 139 );
 140
 141
 142
 143
 144 CREATE SEQUENCE ACL_seq;
 145 CREATE TABLE ACL (
 146         id              NUMBER(11,0) PRIMARY KEY,
 147         PrincipalId     NUMBER(11,0),
 148         PrincipalType   VARCHAR2(25),
 149         RightName       VARCHAR2(25),
 150         RightScope      VARCHAR2(25),
 151         RightAppliesTo  NUMBER(11,0)
 152 );
 153
 154 CREATE SEQUENCE GROUPMEMBERS_seq;
 155 CREATE TABLE GroupMembers (
 156         id              NUMBER(11,0) PRIMARY KEY,
 157         GroupId         NUMBER(11,0),
 158         UserId          NUMBER(11,0)
 159 );
 160
 161 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId);
 162
 163
 164 CREATE SEQUENCE OBJECTKEYWORDS_seq;
 165 CREATE TABLE ObjectKeywords (
 166   id            NUMBER(11,0)  PRIMARY KEY,
 167   Keyword       NUMBER(11,0) NOT NULL,
 168   KeywordSelect NUMBER(11,0)  NOT NULL,
 169   ObjectType    VARCHAR2(32) NOT NULL,
 170   ObjectId      NUMBER(11,0) NOT NULL
 171 );
 172
 173 CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords
 174         (ObjectId, ObjectType, KeywordSelect, Keyword);
 175 CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword);
 176
 177 CREATE SEQUENCE KEYWORDS_seq;
 178 CREATE TABLE Keywords (
 179         id              NUMBER(11, 0) PRIMARY KEY,
 180         Name            VARCHAR2(255) NOT NULL,
 181         Description     VARCHAR2(255),
 182         Parent          NUMBER(11, 0),
 183         Disabled                NUMBER(11, 0) DEFAULT 0
 184 );
 185
 186 CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
 187 CREATE INDEX Keywords3 ON Keywords (Parent);
 188
 189 CREATE SEQUENCE USERS_seq;
 190 CREATE TABLE Users (
 191         id                      NUMBER(11,0) PRIMARY KEY,
 192         Name                    VARCHAR2(120) NOT NULL UNIQUE,
 193         Password                VARCHAR2(40),
 194         Comments                CLOB,
 195         Signature               CLOB,
 196         EmailAddress            VARCHAR2(120),
 197         FreeFormContactInfo     CLOB,
 198         Organization            VARCHAR2(200),
 199         Privileged              NUMBER(11,0),
 200         RealName                VARCHAR2(120),
 201         NickName                VARCHAR2(16),
 202         Lang                    VARCHAR2(16),
 203         EmailEncoding           VARCHAR2(16),
 204         WebEncoding             VARCHAR2(16),
 205         ExternalContactInfoId   VARCHAR2(100),
 206         ContactInfoSystem       VARCHAR2(30),
 207         ExternalAuthId          VARCHAR2(100),
 208         AuthSystem              VARCHAR2(30),
 209         Gecos                   VARCHAR2(16),
 210         HomePhone               VARCHAR2(30),
 211         WorkPhone               VARCHAR2(30),
 212         MobilePhone             VARCHAR2(30),
 213         PagerPhone              VARCHAR2(30),
 214         Address1                VARCHAR2(200),
 215         Address2                VARCHAR2(200),
 216         City                    VARCHAR2(100),
 217         State                   VARCHAR2(100),
 218         Zip                     VARCHAR2(16),
 219         Country                 VARCHAR2(50),
 220         Creator                 NUMBER(11,0),
 221         Created                 DATE,
 222         LastUpdatedBy           NUMBER(11,0),
 223         LastUpdated             DATE,
 224         Disabled                        NUMBER(11,0) DEFAULT 0
 225 );
 226
 227
 228
 229
 230 CREATE SEQUENCE TICKETS_seq;
 231 CREATE TABLE Tickets (
 232         id                      NUMBER(11, 0) PRIMARY KEY,
 233         EffectiveId             NUMBER(11, 0),
 234         Queue                   NUMBER(11,0),
 235         Type                    VARCHAR2(16),
 236         IssueStatement          NUMBER(11,0),
 237         Resolution              NUMBER(11,0),
 238         Owner                   NUMBER(11,0),
 239         Subject                 VARCHAR2(200) DEFAULT '',
 240         InitialPriority         NUMBER(11,0) DEFAULT 0,
 241         FinalPriority           NUMBER(11,0) DEFAULT 0,
 242         Priority                NUMBER(11,0) DEFAULT 0,
 243         Status                  VARCHAR2(10),
 244         TimeWorked              NUMBER(11,0) DEFAULT 0,
 245         TimeLeft                NUMBER(11,0) DEFAULT 0,
 246         Told                    DATE,
 247         Starts                  DATE,
 248         Started                 DATE,
 249         Due                     DATE,
 250         Resolved                DATE,
 251         LastUpdatedBy           NUMBER(11,0),
 252         LastUpdated             DATE,
 253         Creator                 NUMBER(11,0),
 254         Created                 DATE,
 255         Disabled                NUMBER(11,0) DEFAULT 0
 256 );
 257
 258 CREATE SEQUENCE SCRIPACTIONS_seq;
 259 CREATE TABLE ScripActions (
 260   id            NUMBER(11,0) PRIMARY KEY,
 261   Name          VARCHAR2(255),
 262   Description   VARCHAR2(255),
 263   ExecModule    VARCHAR2(60),
 264   Argument      VARCHAR2(255),
 265   Creator       NUMBER(11,0),
 266   Created       DATE,
 267   LastUpdatedBy NUMBER(11,0),
 268   LastUpdated   DATE
 269 );
 270
 271
 272 CREATE SEQUENCE TEMPLATES_seq;
 273 CREATE TABLE Templates (
 274         id              NUMBER(11,0) PRIMARY KEY,
 275         Queue           NUMBER(11,0) DEFAULT 0 NOT NULL,
 276         Name            VARCHAR2(40) NOT NULL UNIQUE,
 277         Description     VARCHAR2(120),
 278         Type            VARCHAR2(16),
 279         Language        VARCHAR2(16),
 280         TranslationOf   NUMBER(11,0),
 281         Content         CLOB,
 282         LastUpdated     DATE,
 283         LastUpdatedBy   NUMBER(11,0),
 284         Creator         NUMBER(11,0),
 285         Created         DATE
 286 );
 287