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 Lifecycle varchar(32) NULL,
54 SubjectTag varchar(120) NULL ,
55 InitialPriority integer NOT NULL DEFAULT 0 ,
56 FinalPriority integer NOT NULL DEFAULT 0 ,
57 DefaultDueIn integer NOT NULL DEFAULT 0 ,
58 Creator integer NOT NULL DEFAULT 0 ,
59 Created TIMESTAMP NULL ,
60 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
61 LastUpdated TIMESTAMP NULL ,
62 Disabled integer NOT NULL DEFAULT 0 ,
66 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
75 -- Sequences for table LINKS
78 CREATE SEQUENCE links_id_seq;
81 id INTEGER DEFAULT nextval('links_id_seq'),
82 Base varchar(240) NULL ,
83 Target varchar(240) NULL ,
84 Type varchar(20) NOT NULL ,
85 LocalTarget integer NOT NULL DEFAULT 0 ,
86 LocalBase integer NOT NULL DEFAULT 0 ,
87 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
88 LastUpdated TIMESTAMP NULL ,
89 Creator integer NOT NULL DEFAULT 0 ,
90 Created TIMESTAMP NULL ,
94 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
95 CREATE INDEX Links4 ON Links(Type,LocalBase);
104 -- Sequences for table PRINCIPALS
107 CREATE SEQUENCE principals_id_seq;
109 CREATE TABLE Principals (
110 id INTEGER DEFAULT nextval('principals_id_seq') not null,
111 PrincipalType VARCHAR(16) not null,
113 Disabled integer NOT NULL DEFAULT 0 ,
118 CREATE INDEX Principals2 ON Principals (ObjectId);
128 -- Sequences for table GROUPS
131 CREATE SEQUENCE groups_id_seq;
133 CREATE TABLE Groups (
134 id INTEGER DEFAULT nextval('groups_id_seq'),
135 Name varchar(200) NULL ,
136 Description varchar(255) NULL ,
140 Creator integer NOT NULL DEFAULT 0 ,
141 Created TIMESTAMP NULL ,
142 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
143 LastUpdated TIMESTAMP NULL ,
147 CREATE UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name);
148 CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
158 -- Sequences for table SCRIPCONDITIONS
161 CREATE SEQUENCE scripconditions_id_seq;
163 CREATE TABLE ScripConditions (
164 id INTEGER DEFAULT nextval('scripconditions_id_seq'),
165 Name varchar(200) NULL ,
166 Description varchar(255) NULL ,
167 ExecModule varchar(60) NULL ,
168 Argument varchar(255) NULL ,
169 ApplicableTransTypes varchar(60) NULL ,
171 Creator integer NOT NULL DEFAULT 0 ,
172 Created TIMESTAMP NULL ,
173 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
174 LastUpdated TIMESTAMP NULL ,
185 -- Sequences for table TRANSACTIONS
188 CREATE SEQUENCE transactions_id_seq;
190 CREATE TABLE Transactions (
191 id INTEGER DEFAULT nextval('transactions_id_seq'),
192 ObjectType varchar(255) NOT NULL ,
193 ObjectId integer NOT NULL DEFAULT 0 ,
194 TimeTaken integer NOT NULL DEFAULT 0 ,
195 Type varchar(20) NULL ,
196 Field varchar(40) NULL ,
197 OldValue varchar(255) NULL ,
198 NewValue varchar(255) NULL ,
199 ReferenceType varchar(255) NULL,
200 OldReference integer NULL ,
201 NewReference integer NULL ,
202 Data varchar(255) NULL ,
204 Creator integer NOT NULL DEFAULT 0 ,
205 Created TIMESTAMP NULL ,
209 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
218 -- Sequences for table SCRIPS
221 CREATE SEQUENCE scrips_id_seq;
223 CREATE TABLE Scrips (
224 id INTEGER DEFAULT nextval('scrips_id_seq'),
225 Description varchar(255),
226 ScripCondition integer NOT NULL DEFAULT 0 ,
227 ScripAction integer NOT NULL DEFAULT 0 ,
228 ConditionRules text NULL ,
229 ActionRules text NULL ,
230 CustomIsApplicableCode text NULL ,
231 CustomPrepareCode text NULL ,
232 CustomCommitCode text NULL ,
233 Stage varchar(32) NULL ,
234 Queue integer NOT NULL DEFAULT 0 ,
235 Template integer NOT NULL DEFAULT 0 ,
236 Creator integer NOT NULL DEFAULT 0 ,
237 Created TIMESTAMP NULL ,
238 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
239 LastUpdated TIMESTAMP NULL ,
250 -- Sequences for table ACL
253 CREATE SEQUENCE acl_id_seq;
256 id INTEGER DEFAULT nextval('acl_id_seq'),
257 PrincipalType varchar(25) NOT NULL,
259 PrincipalId integer NOT NULL ,
260 RightName varchar(25) NOT NULL ,
261 ObjectType varchar(25) NOT NULL ,
262 ObjectId integer NOT NULL DEFAULT 0,
263 Creator integer NOT NULL DEFAULT 0 ,
264 Created TIMESTAMP NULL ,
265 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
266 LastUpdated TIMESTAMP NULL ,
271 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
281 -- Sequences for table GROUPMEMBERS
284 CREATE SEQUENCE groupmembers_id_seq;
286 CREATE TABLE GroupMembers (
287 id INTEGER DEFAULT nextval('groupmembers_id_seq'),
288 GroupId integer NOT NULL DEFAULT 0,
289 MemberId integer NOT NULL DEFAULT 0,
290 Creator integer NOT NULL DEFAULT 0 ,
291 Created TIMESTAMP NULL ,
292 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
293 LastUpdated TIMESTAMP NULL ,
298 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId);
307 -- Sequences for table CACHEDGROUPMEMBERS
310 CREATE SEQUENCE cachedgroupmembers_id_seq;
312 CREATE TABLE CachedGroupMembers (
313 id int DEFAULT nextval('cachedgroupmembers_id_seq'),
317 ImmediateParentId int,
318 Disabled integer NOT NULL DEFAULT 0 ,
323 CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId);
324 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);
325 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
334 -- Sequences for table USERS
337 CREATE SEQUENCE users_id_seq;
340 id INTEGER DEFAULT nextval('users_id_seq'),
341 Name varchar(200) NOT NULL ,
342 Password varchar(256) NULL ,
343 AuthToken varchar(16) NULL ,
345 Signature text NULL ,
346 EmailAddress varchar(120) NULL ,
347 FreeformContactInfo text NULL ,
348 Organization varchar(200) NULL ,
349 RealName varchar(120) NULL ,
350 NickName varchar(16) NULL ,
351 Lang varchar(16) NULL ,
352 EmailEncoding varchar(16) NULL ,
353 WebEncoding varchar(16) NULL ,
354 ExternalContactInfoId varchar(100) NULL ,
355 ContactInfoSystem varchar(30) NULL ,
356 ExternalAuthId varchar(100) NULL ,
357 AuthSystem varchar(30) NULL ,
358 Gecos varchar(16) NULL ,
359 HomePhone varchar(30) NULL ,
360 WorkPhone varchar(30) NULL ,
361 MobilePhone varchar(30) NULL ,
362 PagerPhone varchar(30) NULL ,
363 Address1 varchar(200) NULL ,
364 Address2 varchar(200) NULL ,
365 City varchar(100) NULL ,
366 State varchar(100) NULL ,
367 Zip varchar(16) NULL ,
368 Country varchar(50) NULL ,
369 Timezone varchar(50) NULL ,
372 Creator integer NOT NULL DEFAULT 0 ,
373 Created TIMESTAMP NULL ,
374 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
375 LastUpdated TIMESTAMP NULL ,
381 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
382 CREATE INDEX Users3 ON Users (id, EmailAddress);
383 CREATE INDEX Users4 ON Users (EmailAddress);
393 -- Sequences for table TICKETS
396 CREATE SEQUENCE tickets_id_seq;
398 CREATE TABLE Tickets (
399 id INTEGER DEFAULT nextval('tickets_id_seq'),
400 EffectiveId integer NOT NULL DEFAULT 0 ,
401 Queue integer NOT NULL DEFAULT 0 ,
402 Type varchar(16) NULL ,
403 IssueStatement integer NOT NULL DEFAULT 0 ,
404 Resolution integer NOT NULL DEFAULT 0 ,
405 Owner integer NOT NULL DEFAULT 0 ,
406 Subject varchar(200) NULL DEFAULT '[no subject]' ,
407 InitialPriority integer NOT NULL DEFAULT 0 ,
408 FinalPriority integer NOT NULL DEFAULT 0 ,
409 Priority integer NOT NULL DEFAULT 0 ,
410 TimeEstimated integer NOT NULL DEFAULT 0 ,
411 TimeWorked integer NOT NULL DEFAULT 0 ,
412 Status varchar(64) NULL ,
413 TimeLeft integer NOT NULL DEFAULT 0 ,
414 Told TIMESTAMP NULL ,
415 Starts TIMESTAMP NULL ,
416 Started TIMESTAMP NULL ,
418 Resolved TIMESTAMP NULL ,
420 WillResolve TIMESTAMP NULL ,
422 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
423 LastUpdated TIMESTAMP NULL ,
424 Creator integer NOT NULL DEFAULT 0 ,
425 Created TIMESTAMP NULL ,
426 Disabled integer NOT NULL DEFAULT 0 ,
431 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
432 CREATE INDEX Tickets2 ON Tickets (Owner) ;
433 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
434 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
435 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
444 -- Sequences for table SCRIPACTIONS
447 CREATE SEQUENCE scripactions_id_seq;
449 CREATE TABLE ScripActions (
450 id INTEGER DEFAULT nextval('scripactions_id_seq'),
451 Name varchar(200) NULL ,
452 Description varchar(255) NULL ,
453 ExecModule varchar(60) NULL ,
454 Argument varchar(255) NULL ,
455 Creator integer NOT NULL DEFAULT 0 ,
456 Created TIMESTAMP NULL ,
457 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
458 LastUpdated TIMESTAMP NULL ,
470 -- Sequences for table TEMPLATES
473 CREATE SEQUENCE templates_id_seq;
475 CREATE TABLE Templates (
476 id INTEGER DEFAULT nextval('templates_id_seq'),
477 Queue integer NOT NULL DEFAULT 0 ,
478 Name varchar(200) NOT NULL ,
479 Description varchar(255) NULL ,
480 Type varchar(16) NULL ,
481 Language varchar(16) NULL ,
482 TranslationOf integer NOT NULL DEFAULT 0 ,
484 LastUpdated TIMESTAMP NULL ,
485 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
486 Creator integer NOT NULL DEFAULT 0 ,
487 Created TIMESTAMP NULL ,
499 -- Sequences for table TICKETCUSTOMFIELDVALUES
502 CREATE SEQUENCE objectcustomfieldvalues_id_s;
504 CREATE TABLE ObjectCustomFieldValues (
505 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
506 CustomField int NOT NULL ,
507 ObjectType varchar(255) NULL ,
508 ObjectId int NOT NULL ,
509 SortOrder integer NOT NULL DEFAULT 0 ,
511 Content varchar(255) NULL ,
512 LargeContent text NULL,
513 ContentType varchar(80) NULL,
514 ContentEncoding varchar(80) NULL ,
516 Creator integer NOT NULL DEFAULT 0 ,
517 Created TIMESTAMP NULL ,
518 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
519 LastUpdated TIMESTAMP NULL ,
520 Disabled integer NOT NULL DEFAULT 0 ,
525 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
526 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
535 -- Sequences for table CUSTOMFIELDS
538 CREATE SEQUENCE customfields_id_seq;
540 CREATE TABLE CustomFields (
541 id INTEGER DEFAULT nextval('customfields_id_seq'),
542 Name varchar(200) NULL ,
543 Type varchar(200) NULL ,
544 RenderType varchar(64) NULL ,
545 MaxValues integer NOT NULL DEFAULT 0 ,
546 Repeated integer NOT NULL DEFAULT 0 ,
547 ValuesClass varchar(64) NULL ,
548 BasedOn integer NULL,
549 Pattern varchar(65536) NULL ,
550 LookupType varchar(255) NOT NULL ,
551 Description varchar(255) NULL ,
552 SortOrder integer NOT NULL DEFAULT 0 ,
554 Creator integer NOT NULL DEFAULT 0 ,
555 Created TIMESTAMP NULL ,
556 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
557 LastUpdated TIMESTAMP NULL ,
558 Disabled integer NOT NULL DEFAULT 0 ,
559 Required integer NOT NULL DEFAULT 0 ,
568 CREATE SEQUENCE objectcustomfields_id_s;
570 CREATE TABLE ObjectCustomFields (
571 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
572 CustomField integer NOT NULL,
573 ObjectId integer NOT NULL,
574 SortOrder integer NOT NULL DEFAULT 0 ,
576 Creator integer NOT NULL DEFAULT 0 ,
577 Created TIMESTAMP NULL ,
578 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
579 LastUpdated TIMESTAMP NULL ,
591 -- Sequences for table CUSTOMFIELDVALUES
594 CREATE SEQUENCE customfieldvalues_id_seq;
596 CREATE TABLE CustomFieldValues (
597 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
598 CustomField int NOT NULL ,
599 Name varchar(200) NULL ,
600 Description varchar(255) NULL ,
601 SortOrder integer NOT NULL DEFAULT 0 ,
602 Category varchar(255) NULL ,
604 Creator integer NOT NULL DEFAULT 0 ,
605 Created TIMESTAMP NULL ,
606 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
607 LastUpdated TIMESTAMP NULL ,
612 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
619 CREATE SEQUENCE attributes_id_seq;
621 CREATE TABLE Attributes (
622 id INTEGER DEFAULT nextval('attributes_id_seq'),
623 Name varchar(255) NOT NULL ,
624 Description varchar(255) NULL ,
626 ContentType varchar(16),
627 ObjectType varchar(64),
629 Creator integer NOT NULL DEFAULT 0 ,
630 Created TIMESTAMP NULL ,
631 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
632 LastUpdated TIMESTAMP NULL ,
637 CREATE INDEX Attributes1 on Attributes(Name);
638 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
644 -- sessions is used by Apache::Session to keep sessions in the database.
645 -- We should have a reaper script somewhere.
647 CREATE TABLE sessions (
648 id char(32) NOT NULL,
650 LastUpdated TIMESTAMP not null default current_timestamp,
656 CREATE TABLE Classes (
658 Name varchar(255) NOT NULL DEFAULT '',
659 Description varchar(255) NOT NULL DEFAULT '',
660 SortOrder integer NOT NULL DEFAULT 0,
661 Disabled smallint NOT NULL DEFAULT 0,
662 Creator integer NOT NULL DEFAULT 0,
663 Created TIMESTAMP NULL,
664 LastUpdatedBy integer NOT NULL DEFAULT 0,
665 LastUpdated TIMESTAMP NULL,
666 HotList smallint NOT NULL DEFAULT 0,
670 CREATE TABLE Articles (
672 Name varchar(255) NOT NULL DEFAULT '',
673 Summary varchar(255) NOT NULL DEFAULT '',
674 SortOrder integer NOT NULL DEFAULT 0,
675 Class integer NOT NULL DEFAULT 0,
676 Parent integer NOT NULL DEFAULT 0,
678 Creator integer NOT NULL DEFAULT 0,
679 Created TIMESTAMP NULL,
680 LastUpdatedBy integer NOT NULL DEFAULT 0,
681 LastUpdated TIMESTAMP NULL,
686 CREATE TABLE Topics (
688 Parent integer NOT NULL DEFAULT 0,
689 Name varchar(255) NOT NULL DEFAULT '',
690 Description varchar(255) NOT NULL DEFAULT '',
691 ObjectType varchar(64) NOT NULL DEFAULT '',
692 ObjectId integer NOT NULL,
697 CREATE TABLE ObjectTopics (
699 Topic integer NOT NULL,
700 ObjectType varchar(64) NOT NULL DEFAULT '',
701 ObjectId integer NOT NULL,
706 CREATE TABLE ObjectClasses (
708 Class integer NOT NULL,
709 ObjectType varchar(255) NOT NULL DEFAULT '',
710 ObjectId integer NOT NULL,
711 Creator integer NOT NULL DEFAULT 0,
712 Created TIMESTAMP NULL,
713 LastUpdatedBy integer NOT NULL DEFAULT 0,
714 LastUpdated TIMESTAMP NULL,