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 ,
421 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
422 LastUpdated TIMESTAMP NULL ,
423 Creator integer NOT NULL DEFAULT 0 ,
424 Created TIMESTAMP NULL ,
425 Disabled integer NOT NULL DEFAULT 0 ,
430 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
431 CREATE INDEX Tickets2 ON Tickets (Owner) ;
432 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
433 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
434 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
443 -- Sequences for table SCRIPACTIONS
446 CREATE SEQUENCE scripactions_id_seq;
448 CREATE TABLE ScripActions (
449 id INTEGER DEFAULT nextval('scripactions_id_seq'),
450 Name varchar(200) NULL ,
451 Description varchar(255) NULL ,
452 ExecModule varchar(60) NULL ,
453 Argument varchar(255) NULL ,
454 Creator integer NOT NULL DEFAULT 0 ,
455 Created TIMESTAMP NULL ,
456 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
457 LastUpdated TIMESTAMP NULL ,
469 -- Sequences for table TEMPLATES
472 CREATE SEQUENCE templates_id_seq;
474 CREATE TABLE Templates (
475 id INTEGER DEFAULT nextval('templates_id_seq'),
476 Queue integer NOT NULL DEFAULT 0 ,
477 Name varchar(200) NOT NULL ,
478 Description varchar(255) NULL ,
479 Type varchar(16) NULL ,
480 Language varchar(16) NULL ,
481 TranslationOf integer NOT NULL DEFAULT 0 ,
483 LastUpdated TIMESTAMP NULL ,
484 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
485 Creator integer NOT NULL DEFAULT 0 ,
486 Created TIMESTAMP NULL ,
498 -- Sequences for table TICKETCUSTOMFIELDVALUES
501 CREATE SEQUENCE objectcustomfieldvalues_id_s;
503 CREATE TABLE ObjectCustomFieldValues (
504 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
505 CustomField int NOT NULL ,
506 ObjectType varchar(255) NULL ,
507 ObjectId int NOT NULL ,
508 SortOrder integer NOT NULL DEFAULT 0 ,
510 Content varchar(255) NULL ,
511 LargeContent text NULL,
512 ContentType varchar(80) NULL,
513 ContentEncoding varchar(80) NULL ,
515 Creator integer NOT NULL DEFAULT 0 ,
516 Created TIMESTAMP NULL ,
517 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
518 LastUpdated TIMESTAMP NULL ,
519 Disabled integer NOT NULL DEFAULT 0 ,
524 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
525 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
534 -- Sequences for table CUSTOMFIELDS
537 CREATE SEQUENCE customfields_id_seq;
539 CREATE TABLE CustomFields (
540 id INTEGER DEFAULT nextval('customfields_id_seq'),
541 Name varchar(200) NULL ,
542 Type varchar(200) NULL ,
543 RenderType varchar(64) NULL ,
544 MaxValues integer NOT NULL DEFAULT 0 ,
545 Repeated integer NOT NULL DEFAULT 0 ,
546 ValuesClass varchar(64) NULL ,
547 BasedOn integer NULL,
548 Pattern varchar(65536) NULL ,
549 LookupType varchar(255) NOT NULL ,
550 Description varchar(255) NULL ,
551 SortOrder integer NOT NULL DEFAULT 0 ,
553 Creator integer NOT NULL DEFAULT 0 ,
554 Created TIMESTAMP NULL ,
555 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
556 LastUpdated TIMESTAMP NULL ,
557 Disabled integer NOT NULL DEFAULT 0 ,
566 CREATE SEQUENCE objectcustomfields_id_s;
568 CREATE TABLE ObjectCustomFields (
569 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
570 CustomField integer NOT NULL,
571 ObjectId integer NOT NULL,
572 SortOrder integer NOT NULL DEFAULT 0 ,
574 Creator integer NOT NULL DEFAULT 0 ,
575 Created TIMESTAMP NULL ,
576 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
577 LastUpdated TIMESTAMP NULL ,
589 -- Sequences for table CUSTOMFIELDVALUES
592 CREATE SEQUENCE customfieldvalues_id_seq;
594 CREATE TABLE CustomFieldValues (
595 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
596 CustomField int NOT NULL ,
597 Name varchar(200) NULL ,
598 Description varchar(255) NULL ,
599 SortOrder integer NOT NULL DEFAULT 0 ,
600 Category varchar(255) NULL ,
602 Creator integer NOT NULL DEFAULT 0 ,
603 Created TIMESTAMP NULL ,
604 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
605 LastUpdated TIMESTAMP NULL ,
610 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
617 CREATE SEQUENCE attributes_id_seq;
619 CREATE TABLE Attributes (
620 id INTEGER DEFAULT nextval('attributes_id_seq'),
621 Name varchar(255) NOT NULL ,
622 Description varchar(255) NULL ,
624 ContentType varchar(16),
625 ObjectType varchar(64),
627 Creator integer NOT NULL DEFAULT 0 ,
628 Created TIMESTAMP NULL ,
629 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
630 LastUpdated TIMESTAMP NULL ,
635 CREATE INDEX Attributes1 on Attributes(Name);
636 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
642 -- sessions is used by Apache::Session to keep sessions in the database.
643 -- We should have a reaper script somewhere.
645 CREATE TABLE sessions (
646 id char(32) NOT NULL,
648 LastUpdated TIMESTAMP not null default current_timestamp,
654 CREATE TABLE Classes (
656 Name varchar(255) NOT NULL DEFAULT '',
657 Description varchar(255) NOT NULL DEFAULT '',
658 SortOrder integer NOT NULL DEFAULT 0,
659 Disabled smallint NOT NULL DEFAULT 0,
660 Creator integer NOT NULL DEFAULT 0,
661 Created TIMESTAMP NULL,
662 LastUpdatedBy integer NOT NULL DEFAULT 0,
663 LastUpdated TIMESTAMP NULL,
664 HotList smallint NOT NULL DEFAULT 0,
668 CREATE TABLE Articles (
670 Name varchar(255) NOT NULL DEFAULT '',
671 Summary varchar(255) NOT NULL DEFAULT '',
672 SortOrder integer NOT NULL DEFAULT 0,
673 Class integer NOT NULL DEFAULT 0,
674 Parent integer NOT NULL DEFAULT 0,
676 Creator integer NOT NULL DEFAULT 0,
677 Created TIMESTAMP NULL,
678 LastUpdatedBy integer NOT NULL DEFAULT 0,
679 LastUpdated TIMESTAMP NULL,
684 CREATE TABLE Topics (
686 Parent integer NOT NULL DEFAULT 0,
687 Name varchar(255) NOT NULL DEFAULT '',
688 Description varchar(255) NOT NULL DEFAULT '',
689 ObjectType varchar(64) NOT NULL DEFAULT '',
690 ObjectId integer NOT NULL,
695 CREATE TABLE ObjectTopics (
697 Topic integer NOT NULL,
698 ObjectType varchar(64) NOT NULL DEFAULT '',
699 ObjectId integer NOT NULL,
704 CREATE TABLE ObjectClasses (
706 Class integer NOT NULL,
707 ObjectType varchar(255) NOT NULL DEFAULT '',
708 ObjectId integer NOT NULL,
709 Creator integer NOT NULL DEFAULT 0,
710 Created TIMESTAMP NULL,
711 LastUpdatedBy integer NOT NULL DEFAULT 0,
712 LastUpdated TIMESTAMP NULL,