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 ,
436 WillResolve TIMESTAMP NULL ,
438 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
439 LastUpdated TIMESTAMP NULL ,
440 Creator integer NOT NULL DEFAULT 0 ,
441 Created TIMESTAMP NULL ,
442 Disabled integer NOT NULL DEFAULT 0 ,
447 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
448 CREATE INDEX Tickets2 ON Tickets (Owner) ;
449 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
453 -- Sequences for table SCRIPACTIONS
456 CREATE SEQUENCE scripactions_id_seq;
458 CREATE TABLE ScripActions (
459 id INTEGER DEFAULT nextval('scripactions_id_seq'),
460 Name varchar(200) NULL ,
461 Description varchar(255) NULL ,
462 ExecModule varchar(60) NULL ,
463 Argument varchar(255) NULL ,
464 Creator integer NOT NULL DEFAULT 0 ,
465 Created TIMESTAMP NULL ,
466 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
467 LastUpdated TIMESTAMP NULL ,
479 -- Sequences for table TEMPLATES
482 CREATE SEQUENCE templates_id_seq;
484 CREATE TABLE Templates (
485 id INTEGER DEFAULT nextval('templates_id_seq'),
486 Queue integer NOT NULL DEFAULT 0 ,
487 Name varchar(200) NOT NULL ,
488 Description varchar(255) NULL ,
489 Type varchar(16) NULL ,
491 LastUpdated TIMESTAMP NULL ,
492 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
493 Creator integer NOT NULL DEFAULT 0 ,
494 Created TIMESTAMP NULL ,
506 -- Sequences for table TICKETCUSTOMFIELDVALUES
509 CREATE SEQUENCE objectcustomfieldvalues_id_s;
511 CREATE TABLE ObjectCustomFieldValues (
512 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
513 CustomField int NOT NULL ,
514 ObjectType varchar(255) NULL ,
515 ObjectId int NOT NULL ,
516 SortOrder integer NOT NULL DEFAULT 0 ,
518 Content varchar(255) NULL ,
519 LargeContent text NULL,
520 ContentType varchar(80) NULL,
521 ContentEncoding varchar(80) NULL ,
523 Creator integer NOT NULL DEFAULT 0 ,
524 Created TIMESTAMP NULL ,
525 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
526 LastUpdated TIMESTAMP NULL ,
527 Disabled integer NOT NULL DEFAULT 0 ,
532 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
533 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
542 -- Sequences for table CUSTOMFIELDS
545 CREATE SEQUENCE customfields_id_seq;
547 CREATE TABLE CustomFields (
548 id INTEGER DEFAULT nextval('customfields_id_seq'),
549 Name varchar(200) NULL ,
550 Type varchar(200) NULL ,
551 RenderType varchar(64) NULL ,
552 MaxValues integer NOT NULL DEFAULT 0 ,
553 ValuesClass varchar(64) NULL ,
554 BasedOn integer NULL,
555 Pattern varchar(65536) NULL ,
556 LookupType varchar(255) NOT NULL ,
557 Description varchar(255) NULL ,
558 SortOrder integer NOT NULL DEFAULT 0 ,
560 Creator integer NOT NULL DEFAULT 0 ,
561 Created TIMESTAMP NULL ,
562 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
563 LastUpdated TIMESTAMP NULL ,
564 Disabled integer NOT NULL DEFAULT 0 ,
565 Required integer NOT NULL DEFAULT 0 ,
574 CREATE SEQUENCE objectcustomfields_id_s;
576 CREATE TABLE ObjectCustomFields (
577 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
578 CustomField integer NOT NULL,
579 ObjectId integer NOT NULL,
580 SortOrder integer NOT NULL DEFAULT 0 ,
582 Creator integer NOT NULL DEFAULT 0 ,
583 Created TIMESTAMP NULL ,
584 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
585 LastUpdated TIMESTAMP NULL ,
597 -- Sequences for table CUSTOMFIELDVALUES
600 CREATE SEQUENCE customfieldvalues_id_seq;
602 CREATE TABLE CustomFieldValues (
603 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
604 CustomField int NOT NULL ,
605 Name varchar(200) NULL ,
606 Description varchar(255) NULL ,
607 SortOrder integer NOT NULL DEFAULT 0 ,
608 Category varchar(255) NULL ,
610 Creator integer NOT NULL DEFAULT 0 ,
611 Created TIMESTAMP NULL ,
612 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
613 LastUpdated TIMESTAMP NULL ,
618 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
625 CREATE SEQUENCE attributes_id_seq;
627 CREATE TABLE Attributes (
628 id INTEGER DEFAULT nextval('attributes_id_seq'),
629 Name varchar(255) NOT NULL ,
630 Description varchar(255) NULL ,
632 ContentType varchar(16),
633 ObjectType varchar(64),
635 Creator integer NOT NULL DEFAULT 0 ,
636 Created TIMESTAMP NULL ,
637 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
638 LastUpdated TIMESTAMP NULL ,
643 CREATE INDEX Attributes1 on Attributes(Name);
644 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
650 -- sessions is used by Apache::Session to keep sessions in the database.
651 -- We should have a reaper script somewhere.
653 CREATE TABLE sessions (
654 id char(32) NOT NULL,
656 LastUpdated TIMESTAMP not null default current_timestamp,
662 CREATE TABLE Classes (
664 Name varchar(255) NOT NULL DEFAULT '',
665 Description varchar(255) NOT NULL DEFAULT '',
666 SortOrder integer NOT NULL DEFAULT 0,
667 Disabled smallint NOT NULL DEFAULT 0,
668 Creator integer NOT NULL DEFAULT 0,
669 Created TIMESTAMP NULL,
670 LastUpdatedBy integer NOT NULL DEFAULT 0,
671 LastUpdated TIMESTAMP NULL,
672 HotList smallint NOT NULL DEFAULT 0,
676 CREATE TABLE Articles (
678 Name varchar(255) NOT NULL DEFAULT '',
679 Summary varchar(255) NOT NULL DEFAULT '',
680 SortOrder integer NOT NULL DEFAULT 0,
681 Class integer NOT NULL DEFAULT 0,
682 Parent integer NOT NULL DEFAULT 0,
684 Creator integer NOT NULL DEFAULT 0,
685 Created TIMESTAMP NULL,
686 LastUpdatedBy integer NOT NULL DEFAULT 0,
687 LastUpdated TIMESTAMP NULL,
692 CREATE TABLE Topics (
694 Parent integer NOT NULL DEFAULT 0,
695 Name varchar(255) NOT NULL DEFAULT '',
696 Description varchar(255) NOT NULL DEFAULT '',
697 ObjectType varchar(64) NOT NULL DEFAULT '',
698 ObjectId integer NOT NULL,
703 CREATE TABLE ObjectTopics (
705 Topic integer NOT NULL,
706 ObjectType varchar(64) NOT NULL DEFAULT '',
707 ObjectId integer NOT NULL,
712 CREATE TABLE ObjectClasses (
714 Class integer NOT NULL,
715 ObjectType varchar(255) NOT NULL DEFAULT '',
716 ObjectId integer NOT NULL,
717 Creator integer NOT NULL DEFAULT 0,
718 Created TIMESTAMP NULL,
719 LastUpdatedBy integer NOT NULL DEFAULT 0,
720 LastUpdated TIMESTAMP NULL,