1 ------------------------------------------------------------------
2 -- My2Pg 1.23 translated dump
4 ------------------------------------------------------------------
9 -- Sequences for table ATTACHMENTS
12 CREATE SEQUENCE attachments_id_seq;
16 CREATE TABLE Attachments (
17 id INTEGER DEFAULT nextval('attachments_id_seq'),
18 TransactionId integer NOT NULL ,
19 Parent integer NOT NULL DEFAULT 0 ,
20 MessageId varchar(160) NULL ,
21 Subject varchar(255) NULL ,
22 Filename varchar(255) NULL ,
23 ContentType varchar(80) NULL ,
24 ContentEncoding varchar(80) NULL ,
27 Creator integer NOT NULL DEFAULT 0 ,
28 Created TIMESTAMP NULL ,
33 CREATE INDEX Attachments1 ON Attachments (Parent) ;
34 CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
35 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
42 -- Sequences for table QUEUES
45 CREATE SEQUENCE queues_id_seq;
48 id INTEGER DEFAULT nextval('queues_id_seq'),
49 Name varchar(200) NOT NULL ,
50 Description varchar(255) NULL ,
51 CorrespondAddress varchar(120) NULL ,
52 CommentAddress varchar(120) NULL ,
53 InitialPriority integer NOT NULL DEFAULT 0 ,
54 FinalPriority integer NOT NULL DEFAULT 0 ,
55 DefaultDueIn integer NOT NULL DEFAULT 0 ,
56 Creator integer NOT NULL DEFAULT 0 ,
57 Created TIMESTAMP NULL ,
58 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
59 LastUpdated TIMESTAMP NULL ,
60 Disabled int2 NOT NULL DEFAULT 0 ,
64 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
73 -- Sequences for table LINKS
76 CREATE SEQUENCE links_id_seq;
79 id INTEGER DEFAULT nextval('links_id_seq'),
80 Base varchar(240) NULL ,
81 Target varchar(240) NULL ,
82 Type varchar(20) NOT NULL ,
83 LocalTarget integer NOT NULL DEFAULT 0 ,
84 LocalBase integer NOT NULL DEFAULT 0 ,
85 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
86 LastUpdated TIMESTAMP NULL ,
87 Creator integer NOT NULL DEFAULT 0 ,
88 Created TIMESTAMP NULL ,
92 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
93 CREATE INDEX Links4 ON Links(Type,LocalBase);
102 -- Sequences for table PRINCIPALS
105 CREATE SEQUENCE principals_id_seq;
107 CREATE TABLE Principals (
108 id INTEGER DEFAULT nextval('principals_id_seq') not null,
109 PrincipalType VARCHAR(16) not null,
111 Disabled int2 NOT NULL DEFAULT 0 ,
116 CREATE INDEX Principals2 ON Principals (ObjectId);
126 -- Sequences for table GROUPS
129 CREATE SEQUENCE groups_id_seq;
131 CREATE TABLE Groups (
132 id INTEGER DEFAULT nextval('groups_id_seq'),
133 Name varchar(200) NULL ,
134 Description varchar(255) NULL ,
141 CREATE UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name);
142 CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
147 -- {{{ ScripConditions
152 -- Sequences for table SCRIPCONDITIONS
155 CREATE SEQUENCE scripconditions_id_seq;
157 CREATE TABLE ScripConditions (
158 id INTEGER DEFAULT nextval('scripconditions_id_seq'),
159 Name varchar(200) NULL ,
160 Description varchar(255) NULL ,
161 ExecModule varchar(60) NULL ,
162 Argument varchar(255) NULL ,
163 ApplicableTransTypes varchar(60) NULL ,
165 Creator integer NOT NULL DEFAULT 0 ,
166 Created TIMESTAMP NULL ,
167 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
168 LastUpdated TIMESTAMP NULL ,
179 -- Sequences for table TRANSACTIONS
182 CREATE SEQUENCE transactions_id_seq;
184 CREATE TABLE Transactions (
185 id INTEGER DEFAULT nextval('transactions_id_seq'),
186 EffectiveTicket integer NOT NULL DEFAULT 0 ,
187 Ticket integer NOT NULL DEFAULT 0 ,
188 TimeTaken integer NOT NULL DEFAULT 0 ,
189 Type varchar(20) NULL ,
190 Field varchar(40) NULL ,
191 OldValue varchar(255) NULL ,
192 NewValue varchar(255) NULL ,
193 Data varchar(255) NULL ,
195 Creator integer NOT NULL DEFAULT 0 ,
196 Created TIMESTAMP NULL ,
200 CREATE INDEX Transactions1 ON Transactions (Ticket);
201 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
210 -- Sequences for table SCRIPS
213 CREATE SEQUENCE scrips_id_seq;
215 CREATE TABLE Scrips (
216 id INTEGER DEFAULT nextval('scrips_id_seq'),
217 Description varchar(255),
218 ScripCondition integer NOT NULL DEFAULT 0 ,
219 ScripAction integer NOT NULL DEFAULT 0 ,
220 ConditionRules text NULL ,
221 ActionRules text NULL ,
222 CustomIsApplicableCode text NULL ,
223 CustomPrepareCode text NULL ,
224 CustomCommitCode text NULL ,
225 Stage varchar(32) NULL ,
226 Queue integer NOT NULL DEFAULT 0 ,
227 Template integer NOT NULL DEFAULT 0 ,
228 Creator integer NOT NULL DEFAULT 0 ,
229 Created TIMESTAMP NULL ,
230 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
231 LastUpdated TIMESTAMP NULL ,
242 -- Sequences for table ACL
245 CREATE SEQUENCE acl_id_seq;
248 id INTEGER DEFAULT nextval('acl_id_seq'),
249 PrincipalType varchar(25) NOT NULL,
251 PrincipalId integer NOT NULL ,
252 RightName varchar(25) NOT NULL ,
253 ObjectType varchar(25) NOT NULL ,
254 ObjectId integer NOT NULL DEFAULT 0,
255 DelegatedBy integer NOT NULL DEFAULT 0,
256 DelegatedFrom integer NOT NULL DEFAULT 0,
261 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
271 -- Sequences for table GROUPMEMBERS
274 CREATE SEQUENCE groupmembers_id_seq;
276 CREATE TABLE GroupMembers (
277 id INTEGER DEFAULT nextval('groupmembers_id_seq'),
278 GroupId integer NOT NULL DEFAULT 0,
279 MemberId integer NOT NULL DEFAULT 0,
286 -- {{{ GroupMembersCache
291 -- Sequences for table CACHEDGROUPMEMBERS
294 CREATE SEQUENCE cachedgroupmembers_id_seq;
296 CREATE TABLE CachedGroupMembers (
297 id int DEFAULT nextval('cachedgroupmembers_id_seq'),
301 ImmediateParentId int,
302 Disabled int2 NOT NULL DEFAULT 0 ,
307 CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId);
308 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);
309 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
318 -- Sequences for table USERS
321 CREATE SEQUENCE users_id_seq;
324 id INTEGER DEFAULT nextval('users_id_seq'),
325 Name varchar(200) NOT NULL ,
326 Password varchar(40) NULL ,
328 Signature text NULL ,
329 EmailAddress varchar(120) NULL ,
330 FreeformContactInfo text NULL ,
331 Organization varchar(200) NULL ,
332 RealName varchar(120) NULL ,
333 NickName varchar(16) NULL ,
334 Lang varchar(16) NULL ,
335 EmailEncoding varchar(16) NULL ,
336 WebEncoding varchar(16) NULL ,
337 ExternalContactInfoId varchar(100) NULL ,
338 ContactInfoSystem varchar(30) NULL ,
339 ExternalAuthId varchar(100) NULL ,
340 AuthSystem varchar(30) NULL ,
341 Gecos varchar(16) NULL ,
342 HomePhone varchar(30) NULL ,
343 WorkPhone varchar(30) NULL ,
344 MobilePhone varchar(30) NULL ,
345 PagerPhone varchar(30) NULL ,
346 Address1 varchar(200) NULL ,
347 Address2 varchar(200) NULL ,
348 City varchar(100) NULL ,
349 State varchar(100) NULL ,
350 Zip varchar(16) NULL ,
351 Country varchar(50) NULL ,
352 Timezone varchar(50) NULL ,
355 Creator integer NOT NULL DEFAULT 0 ,
356 Created TIMESTAMP NULL ,
357 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
358 LastUpdated TIMESTAMP NULL ,
364 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
365 CREATE INDEX Users2 ON Users (Name);
366 CREATE INDEX Users3 ON Users (id, EmailAddress);
367 CREATE INDEX Users4 ON Users (EmailAddress);
377 -- Sequences for table TICKETS
380 CREATE SEQUENCE tickets_id_seq;
382 CREATE TABLE Tickets (
383 id INTEGER DEFAULT nextval('tickets_id_seq'),
384 EffectiveId integer NOT NULL DEFAULT 0 ,
385 Queue integer NOT NULL DEFAULT 0 ,
386 Type varchar(16) NULL ,
387 IssueStatement integer NOT NULL DEFAULT 0 ,
388 Resolution integer NOT NULL DEFAULT 0 ,
389 Owner integer NOT NULL DEFAULT 0 ,
390 Subject varchar(200) NULL DEFAULT '[no subject]' ,
391 InitialPriority integer NOT NULL DEFAULT 0 ,
392 FinalPriority integer NOT NULL DEFAULT 0 ,
393 Priority integer NOT NULL DEFAULT 0 ,
394 TimeEstimated integer NOT NULL DEFAULT 0 ,
395 TimeWorked integer NOT NULL DEFAULT 0 ,
396 Status varchar(10) NULL ,
397 TimeLeft integer NOT NULL DEFAULT 0 ,
398 Told TIMESTAMP NULL ,
399 Starts TIMESTAMP NULL ,
400 Started TIMESTAMP NULL ,
402 Resolved TIMESTAMP NULL ,
405 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
406 LastUpdated TIMESTAMP NULL ,
407 Creator integer NOT NULL DEFAULT 0 ,
408 Created TIMESTAMP NULL ,
409 Disabled int2 NOT NULL DEFAULT 0 ,
414 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
415 CREATE INDEX Tickets2 ON Tickets (Owner) ;
416 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
417 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
418 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
427 -- Sequences for table SCRIPACTIONS
430 CREATE SEQUENCE scripactions_id_seq;
432 CREATE TABLE ScripActions (
433 id INTEGER DEFAULT nextval('scripactions_id_seq'),
434 Name varchar(200) NULL ,
435 Description varchar(255) NULL ,
436 ExecModule varchar(60) NULL ,
437 Argument varchar(255) NULL ,
438 Creator integer NOT NULL DEFAULT 0 ,
439 Created TIMESTAMP NULL ,
440 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
441 LastUpdated TIMESTAMP NULL ,
453 -- Sequences for table TEMPLATES
456 CREATE SEQUENCE templates_id_seq;
458 CREATE TABLE Templates (
459 id INTEGER DEFAULT nextval('templates_id_seq'),
460 Queue integer NOT NULL DEFAULT 0 ,
461 Name varchar(200) NOT NULL ,
462 Description varchar(255) NULL ,
463 Type varchar(16) NULL ,
464 Language varchar(16) NULL ,
465 TranslationOf integer NOT NULL DEFAULT 0 ,
467 LastUpdated TIMESTAMP NULL ,
468 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
469 Creator integer NOT NULL DEFAULT 0 ,
470 Created TIMESTAMP NULL ,
477 -- {{{ TicketCustomFieldValues
482 -- Sequences for table TICKETCUSTOMFIELDVALUES
485 CREATE SEQUENCE ticketcustomfieldvalues_id_s;
487 CREATE TABLE TicketCustomFieldValues (
488 id INTEGER DEFAULT nextval('ticketcustomfieldvalues_id_s'),
489 Ticket int NOT NULL ,
490 CustomField int NOT NULL ,
491 Content varchar(255) NULL ,
493 Creator integer NOT NULL DEFAULT 0 ,
494 Created TIMESTAMP NULL ,
495 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
496 LastUpdated TIMESTAMP NULL ,
501 CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
502 CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
511 -- Sequences for table CUSTOMFIELDS
514 CREATE SEQUENCE customfields_id_seq;
516 CREATE TABLE CustomFields (
517 id INTEGER DEFAULT nextval('customfields_id_seq'),
518 Name varchar(200) NULL ,
519 Type varchar(200) NULL ,
520 Queue integer NOT NULL DEFAULT 0 ,
521 Description varchar(255) NULL ,
522 SortOrder integer NOT NULL DEFAULT 0 ,
524 Creator integer NOT NULL DEFAULT 0 ,
525 Created TIMESTAMP NULL ,
526 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
527 LastUpdated TIMESTAMP NULL ,
528 Disabled int2 NOT NULL DEFAULT 0 ,
535 -- {{{ CustomFieldValues
540 -- Sequences for table CUSTOMFIELDVALUES
543 CREATE SEQUENCE customfieldvalues_id_seq;
545 CREATE TABLE CustomFieldValues (
546 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
547 CustomField int NOT NULL ,
548 Name varchar(200) NULL ,
549 Description varchar(255) NULL ,
550 SortOrder integer NOT NULL DEFAULT 0 ,
552 Creator integer NOT NULL DEFAULT 0 ,
553 Created TIMESTAMP NULL ,
554 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
555 LastUpdated TIMESTAMP NULL ,
560 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
566 -- sessions is used by Apache::Session to keep sessions in the database.
567 -- We should have a reaper script somewhere.
569 CREATE TABLE sessions (
570 id char(32) NOT NULL,
572 LastUpdated TIMESTAMP not null default current_timestamp,