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 (LOWER(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 INDEX Groups1 ON Groups (LOWER(Domain), LOWER(Type), Instance);
148 CREATE INDEX Groups2 ON Groups (LOWER(Domain), LOWER(Name), Instance);
149 CREATE INDEX Groups3 On Groups (Instance);
159 -- Sequences for table SCRIPCONDITIONS
162 CREATE SEQUENCE scripconditions_id_seq;
164 CREATE TABLE ScripConditions (
165 id INTEGER DEFAULT nextval('scripconditions_id_seq'),
166 Name varchar(200) NULL ,
167 Description varchar(255) NULL ,
168 ExecModule varchar(60) NULL ,
169 Argument varchar(255) NULL ,
170 ApplicableTransTypes varchar(60) NULL ,
172 Creator integer NOT NULL DEFAULT 0 ,
173 Created TIMESTAMP NULL ,
174 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
175 LastUpdated TIMESTAMP NULL ,
186 -- Sequences for table TRANSACTIONS
189 CREATE SEQUENCE transactions_id_seq;
191 CREATE TABLE Transactions (
192 id INTEGER DEFAULT nextval('transactions_id_seq'),
193 ObjectType varchar(255) NOT NULL ,
194 ObjectId integer NOT NULL DEFAULT 0 ,
195 TimeTaken integer NOT NULL DEFAULT 0 ,
196 Type varchar(20) NULL ,
197 Field varchar(40) NULL ,
198 OldValue varchar(255) NULL ,
199 NewValue varchar(255) NULL ,
200 ReferenceType varchar(255) NULL,
201 OldReference integer NULL ,
202 NewReference integer NULL ,
203 Data varchar(255) NULL ,
205 Creator integer NOT NULL DEFAULT 0 ,
206 Created TIMESTAMP NULL ,
210 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
219 -- Sequences for table SCRIPS
222 CREATE SEQUENCE scrips_id_seq;
224 CREATE TABLE Scrips (
225 id INTEGER DEFAULT nextval('scrips_id_seq'),
226 Description varchar(255),
227 ScripCondition integer NOT NULL DEFAULT 0 ,
228 ScripAction integer NOT NULL DEFAULT 0 ,
229 CustomIsApplicableCode text NULL ,
230 CustomPrepareCode text NULL ,
231 CustomCommitCode text NULL ,
232 Disabled integer NOT NULL DEFAULT 0 ,
233 Template varchar(200) NOT NULL,
234 Creator integer NOT NULL DEFAULT 0 ,
235 Created TIMESTAMP NULL ,
236 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
237 LastUpdated TIMESTAMP NULL ,
243 CREATE SEQUENCE objectscrips_id_seq;
245 CREATE TABLE ObjectScrips (
246 id INTEGER DEFAULT nextval('objectscrips_id_seq'),
247 Scrip integer NOT NULL,
248 Stage varchar(32) NOT NULL DEFAULT 'TransactionCreate' ,
249 ObjectId integer NOT NULL,
250 SortOrder integer NOT NULL DEFAULT 0 ,
252 Creator integer NOT NULL DEFAULT 0 ,
253 Created TIMESTAMP NULL ,
254 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
255 LastUpdated TIMESTAMP NULL ,
260 CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);
265 -- Sequences for table ACL
268 CREATE SEQUENCE acl_id_seq;
271 id INTEGER DEFAULT nextval('acl_id_seq'),
272 PrincipalType varchar(25) NOT NULL,
274 PrincipalId integer NOT NULL ,
275 RightName varchar(25) NOT NULL ,
276 ObjectType varchar(25) NOT NULL ,
277 ObjectId integer NOT NULL DEFAULT 0,
278 Creator integer NOT NULL DEFAULT 0 ,
279 Created TIMESTAMP NULL ,
280 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
281 LastUpdated TIMESTAMP NULL ,
286 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
296 -- Sequences for table GROUPMEMBERS
299 CREATE SEQUENCE groupmembers_id_seq;
301 CREATE TABLE GroupMembers (
302 id INTEGER DEFAULT nextval('groupmembers_id_seq'),
303 GroupId integer NOT NULL DEFAULT 0,
304 MemberId integer NOT NULL DEFAULT 0,
305 Creator integer NOT NULL DEFAULT 0 ,
306 Created TIMESTAMP NULL ,
307 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
308 LastUpdated TIMESTAMP NULL ,
313 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId);
322 -- Sequences for table CACHEDGROUPMEMBERS
325 CREATE SEQUENCE cachedgroupmembers_id_seq;
327 CREATE TABLE CachedGroupMembers (
328 id int DEFAULT nextval('cachedgroupmembers_id_seq'),
332 ImmediateParentId int,
333 Disabled integer NOT NULL DEFAULT 0 ,
338 CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled);
339 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
340 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId,ImmediateParentId);
349 -- Sequences for table USERS
352 CREATE SEQUENCE users_id_seq;
355 id INTEGER DEFAULT nextval('users_id_seq'),
356 Name varchar(200) NOT NULL ,
357 Password varchar(256) NULL ,
358 AuthToken varchar(16) NULL ,
360 Signature text NULL ,
361 EmailAddress varchar(120) NULL ,
362 FreeformContactInfo text NULL ,
363 Organization varchar(200) NULL ,
364 RealName varchar(120) NULL ,
365 NickName varchar(16) NULL ,
366 Lang varchar(16) NULL ,
367 EmailEncoding varchar(16) NULL ,
368 WebEncoding varchar(16) NULL ,
369 ExternalContactInfoId varchar(100) NULL ,
370 ContactInfoSystem varchar(30) NULL ,
371 ExternalAuthId varchar(100) NULL ,
372 AuthSystem varchar(30) NULL ,
373 Gecos varchar(16) NULL ,
374 HomePhone varchar(30) NULL ,
375 WorkPhone varchar(30) NULL ,
376 MobilePhone varchar(30) NULL ,
377 PagerPhone varchar(30) NULL ,
378 Address1 varchar(200) NULL ,
379 Address2 varchar(200) NULL ,
380 City varchar(100) NULL ,
381 State varchar(100) NULL ,
382 Zip varchar(16) NULL ,
383 Country varchar(50) NULL ,
384 Timezone varchar(50) NULL ,
386 SMIMECertificate text NULL,
388 Creator integer NOT NULL DEFAULT 0 ,
389 Created TIMESTAMP NULL ,
390 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
391 LastUpdated TIMESTAMP NULL ,
397 CREATE UNIQUE INDEX Users1 ON Users (LOWER(Name)) ;
398 CREATE INDEX Users4 ON Users (EmailAddress);
408 -- Sequences for table TICKETS
411 CREATE SEQUENCE tickets_id_seq;
413 CREATE TABLE Tickets (
414 id INTEGER DEFAULT nextval('tickets_id_seq'),
415 EffectiveId integer NOT NULL DEFAULT 0 ,
416 IsMerged smallint NULL DEFAULT NULL ,
417 Queue integer NOT NULL DEFAULT 0 ,
418 Type varchar(16) NULL ,
419 IssueStatement integer NOT NULL DEFAULT 0 ,
420 Resolution integer NOT NULL DEFAULT 0 ,
421 Owner integer NOT NULL DEFAULT 0 ,
422 Subject varchar(200) NULL DEFAULT '[no subject]' ,
423 InitialPriority integer NOT NULL DEFAULT 0 ,
424 FinalPriority integer NOT NULL DEFAULT 0 ,
425 Priority integer NOT NULL DEFAULT 0 ,
426 TimeEstimated integer NOT NULL DEFAULT 0 ,
427 TimeWorked integer NOT NULL DEFAULT 0 ,
428 Status varchar(64) NULL ,
429 TimeLeft integer NOT NULL DEFAULT 0 ,
430 Told TIMESTAMP NULL ,
431 Starts TIMESTAMP NULL ,
432 Started TIMESTAMP NULL ,
434 Resolved TIMESTAMP NULL ,
437 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
438 LastUpdated TIMESTAMP NULL ,
439 Creator integer NOT NULL DEFAULT 0 ,
440 Created TIMESTAMP NULL ,
441 Disabled integer NOT NULL DEFAULT 0 ,
446 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
447 CREATE INDEX Tickets2 ON Tickets (Owner) ;
448 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
452 -- Sequences for table SCRIPACTIONS
455 CREATE SEQUENCE scripactions_id_seq;
457 CREATE TABLE ScripActions (
458 id INTEGER DEFAULT nextval('scripactions_id_seq'),
459 Name varchar(200) NULL ,
460 Description varchar(255) NULL ,
461 ExecModule varchar(60) NULL ,
462 Argument varchar(255) NULL ,
463 Creator integer NOT NULL DEFAULT 0 ,
464 Created TIMESTAMP NULL ,
465 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
466 LastUpdated TIMESTAMP NULL ,
478 -- Sequences for table TEMPLATES
481 CREATE SEQUENCE templates_id_seq;
483 CREATE TABLE Templates (
484 id INTEGER DEFAULT nextval('templates_id_seq'),
485 Queue integer NOT NULL DEFAULT 0 ,
486 Name varchar(200) NOT NULL ,
487 Description varchar(255) NULL ,
488 Type varchar(16) NULL ,
490 LastUpdated TIMESTAMP NULL ,
491 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
492 Creator integer NOT NULL DEFAULT 0 ,
493 Created TIMESTAMP NULL ,
505 -- Sequences for table TICKETCUSTOMFIELDVALUES
508 CREATE SEQUENCE objectcustomfieldvalues_id_s;
510 CREATE TABLE ObjectCustomFieldValues (
511 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
512 CustomField int NOT NULL ,
513 ObjectType varchar(255) NULL ,
514 ObjectId int NOT NULL ,
515 SortOrder integer NOT NULL DEFAULT 0 ,
517 Content varchar(255) NULL ,
518 LargeContent text NULL,
519 ContentType varchar(80) NULL,
520 ContentEncoding varchar(80) NULL ,
522 Creator integer NOT NULL DEFAULT 0 ,
523 Created TIMESTAMP NULL ,
524 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
525 LastUpdated TIMESTAMP NULL ,
526 Disabled integer NOT NULL DEFAULT 0 ,
531 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
532 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
541 -- Sequences for table CUSTOMFIELDS
544 CREATE SEQUENCE customfields_id_seq;
546 CREATE TABLE CustomFields (
547 id INTEGER DEFAULT nextval('customfields_id_seq'),
548 Name varchar(200) NULL ,
549 Type varchar(200) NULL ,
550 RenderType varchar(64) NULL ,
551 MaxValues integer NOT NULL DEFAULT 0 ,
552 ValuesClass varchar(64) NULL ,
553 BasedOn integer NULL,
554 Pattern varchar(65536) NULL ,
555 LookupType varchar(255) NOT NULL ,
556 Description varchar(255) NULL ,
557 SortOrder integer NOT NULL DEFAULT 0 ,
559 Creator integer NOT NULL DEFAULT 0 ,
560 Created TIMESTAMP NULL ,
561 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
562 LastUpdated TIMESTAMP NULL ,
563 Disabled integer NOT NULL DEFAULT 0 ,
572 CREATE SEQUENCE objectcustomfields_id_s;
574 CREATE TABLE ObjectCustomFields (
575 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
576 CustomField integer NOT NULL,
577 ObjectId integer NOT NULL,
578 SortOrder integer NOT NULL DEFAULT 0 ,
580 Creator integer NOT NULL DEFAULT 0 ,
581 Created TIMESTAMP NULL ,
582 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
583 LastUpdated TIMESTAMP NULL ,
595 -- Sequences for table CUSTOMFIELDVALUES
598 CREATE SEQUENCE customfieldvalues_id_seq;
600 CREATE TABLE CustomFieldValues (
601 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
602 CustomField int NOT NULL ,
603 Name varchar(200) NULL ,
604 Description varchar(255) NULL ,
605 SortOrder integer NOT NULL DEFAULT 0 ,
606 Category varchar(255) NULL ,
608 Creator integer NOT NULL DEFAULT 0 ,
609 Created TIMESTAMP NULL ,
610 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
611 LastUpdated TIMESTAMP NULL ,
616 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
623 CREATE SEQUENCE attributes_id_seq;
625 CREATE TABLE Attributes (
626 id INTEGER DEFAULT nextval('attributes_id_seq'),
627 Name varchar(255) NOT NULL ,
628 Description varchar(255) NULL ,
630 ContentType varchar(16),
631 ObjectType varchar(64),
633 Creator integer NOT NULL DEFAULT 0 ,
634 Created TIMESTAMP NULL ,
635 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
636 LastUpdated TIMESTAMP NULL ,
641 CREATE INDEX Attributes1 on Attributes(Name);
642 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
648 -- sessions is used by Apache::Session to keep sessions in the database.
649 -- We should have a reaper script somewhere.
651 CREATE TABLE sessions (
652 id char(32) NOT NULL,
654 LastUpdated TIMESTAMP not null default current_timestamp,
660 CREATE TABLE Classes (
662 Name varchar(255) NOT NULL DEFAULT '',
663 Description varchar(255) NOT NULL DEFAULT '',
664 SortOrder integer NOT NULL DEFAULT 0,
665 Disabled smallint NOT NULL DEFAULT 0,
666 Creator integer NOT NULL DEFAULT 0,
667 Created TIMESTAMP NULL,
668 LastUpdatedBy integer NOT NULL DEFAULT 0,
669 LastUpdated TIMESTAMP NULL,
670 HotList smallint NOT NULL DEFAULT 0,
674 CREATE TABLE Articles (
676 Name varchar(255) NOT NULL DEFAULT '',
677 Summary varchar(255) NOT NULL DEFAULT '',
678 SortOrder integer NOT NULL DEFAULT 0,
679 Class integer NOT NULL DEFAULT 0,
680 Parent integer NOT NULL DEFAULT 0,
682 Creator integer NOT NULL DEFAULT 0,
683 Created TIMESTAMP NULL,
684 LastUpdatedBy integer NOT NULL DEFAULT 0,
685 LastUpdated TIMESTAMP NULL,
690 CREATE TABLE Topics (
692 Parent integer NOT NULL DEFAULT 0,
693 Name varchar(255) NOT NULL DEFAULT '',
694 Description varchar(255) NOT NULL DEFAULT '',
695 ObjectType varchar(64) NOT NULL DEFAULT '',
696 ObjectId integer NOT NULL,
701 CREATE TABLE ObjectTopics (
703 Topic integer NOT NULL,
704 ObjectType varchar(64) NOT NULL DEFAULT '',
705 ObjectId integer NOT NULL,
710 CREATE TABLE ObjectClasses (
712 Class integer NOT NULL,
713 ObjectType varchar(255) NOT NULL DEFAULT '',
714 ObjectId integer NOT NULL,
715 Creator integer NOT NULL DEFAULT 0,
716 Created TIMESTAMP NULL,
717 LastUpdatedBy integer NOT NULL DEFAULT 0,
718 LastUpdated TIMESTAMP NULL,