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 integer 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 integer 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 ObjectType varchar(255) NOT NULL ,
187 ObjectId 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 ReferenceType varchar(255) NULL,
194 OldReference integer NULL ,
195 NewReference integer NULL ,
196 Data varchar(255) NULL ,
198 Creator integer NOT NULL DEFAULT 0 ,
199 Created TIMESTAMP NULL ,
203 CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
212 -- Sequences for table SCRIPS
215 CREATE SEQUENCE scrips_id_seq;
217 CREATE TABLE Scrips (
218 id INTEGER DEFAULT nextval('scrips_id_seq'),
219 Description varchar(255),
220 ScripCondition integer NOT NULL DEFAULT 0 ,
221 ScripAction integer NOT NULL DEFAULT 0 ,
222 ConditionRules text NULL ,
223 ActionRules text NULL ,
224 CustomIsApplicableCode text NULL ,
225 CustomPrepareCode text NULL ,
226 CustomCommitCode text NULL ,
227 Stage varchar(32) NULL ,
228 Queue integer NOT NULL DEFAULT 0 ,
229 Template integer NOT NULL DEFAULT 0 ,
230 Creator integer NOT NULL DEFAULT 0 ,
231 Created TIMESTAMP NULL ,
232 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
233 LastUpdated TIMESTAMP NULL ,
244 -- Sequences for table ACL
247 CREATE SEQUENCE acl_id_seq;
250 id INTEGER DEFAULT nextval('acl_id_seq'),
251 PrincipalType varchar(25) NOT NULL,
253 PrincipalId integer NOT NULL ,
254 RightName varchar(25) NOT NULL ,
255 ObjectType varchar(25) NOT NULL ,
256 ObjectId integer NOT NULL DEFAULT 0,
257 DelegatedBy integer NOT NULL DEFAULT 0,
258 DelegatedFrom integer NOT NULL DEFAULT 0,
263 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
273 -- Sequences for table GROUPMEMBERS
276 CREATE SEQUENCE groupmembers_id_seq;
278 CREATE TABLE GroupMembers (
279 id INTEGER DEFAULT nextval('groupmembers_id_seq'),
280 GroupId integer NOT NULL DEFAULT 0,
281 MemberId integer NOT NULL DEFAULT 0,
286 CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId);
290 -- {{{ GroupMembersCache
295 -- Sequences for table CACHEDGROUPMEMBERS
298 CREATE SEQUENCE cachedgroupmembers_id_seq;
300 CREATE TABLE CachedGroupMembers (
301 id int DEFAULT nextval('cachedgroupmembers_id_seq'),
305 ImmediateParentId int,
306 Disabled integer NOT NULL DEFAULT 0 ,
311 CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId);
312 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);
313 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
322 -- Sequences for table USERS
325 CREATE SEQUENCE users_id_seq;
328 id INTEGER DEFAULT nextval('users_id_seq'),
329 Name varchar(200) NOT NULL ,
330 Password varchar(40) NULL ,
332 Signature text NULL ,
333 EmailAddress varchar(120) NULL ,
334 FreeformContactInfo text NULL ,
335 Organization varchar(200) NULL ,
336 RealName varchar(120) NULL ,
337 NickName varchar(16) NULL ,
338 Lang varchar(16) NULL ,
339 EmailEncoding varchar(16) NULL ,
340 WebEncoding varchar(16) NULL ,
341 ExternalContactInfoId varchar(100) NULL ,
342 ContactInfoSystem varchar(30) NULL ,
343 ExternalAuthId varchar(100) NULL ,
344 AuthSystem varchar(30) NULL ,
345 Gecos varchar(16) NULL ,
346 HomePhone varchar(30) NULL ,
347 WorkPhone varchar(30) NULL ,
348 MobilePhone varchar(30) NULL ,
349 PagerPhone varchar(30) NULL ,
350 Address1 varchar(200) NULL ,
351 Address2 varchar(200) NULL ,
352 City varchar(100) NULL ,
353 State varchar(100) NULL ,
354 Zip varchar(16) NULL ,
355 Country varchar(50) NULL ,
356 Timezone varchar(50) NULL ,
359 Creator integer NOT NULL DEFAULT 0 ,
360 Created TIMESTAMP NULL ,
361 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
362 LastUpdated TIMESTAMP NULL ,
368 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
369 CREATE INDEX Users3 ON Users (id, EmailAddress);
370 CREATE INDEX Users4 ON Users (EmailAddress);
380 -- Sequences for table TICKETS
383 CREATE SEQUENCE tickets_id_seq;
385 CREATE TABLE Tickets (
386 id INTEGER DEFAULT nextval('tickets_id_seq'),
387 EffectiveId integer NOT NULL DEFAULT 0 ,
388 Queue integer NOT NULL DEFAULT 0 ,
389 Type varchar(16) NULL ,
390 IssueStatement integer NOT NULL DEFAULT 0 ,
391 Resolution integer NOT NULL DEFAULT 0 ,
392 Owner integer NOT NULL DEFAULT 0 ,
393 Subject varchar(200) NULL DEFAULT '[no subject]' ,
394 InitialPriority integer NOT NULL DEFAULT 0 ,
395 FinalPriority integer NOT NULL DEFAULT 0 ,
396 Priority integer NOT NULL DEFAULT 0 ,
397 TimeEstimated integer NOT NULL DEFAULT 0 ,
398 TimeWorked integer NOT NULL DEFAULT 0 ,
399 Status varchar(10) NULL ,
400 TimeLeft integer NOT NULL DEFAULT 0 ,
401 Told TIMESTAMP NULL ,
402 Starts TIMESTAMP NULL ,
403 Started TIMESTAMP NULL ,
405 Resolved TIMESTAMP NULL ,
408 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
409 LastUpdated TIMESTAMP NULL ,
410 Creator integer NOT NULL DEFAULT 0 ,
411 Created TIMESTAMP NULL ,
412 Disabled integer NOT NULL DEFAULT 0 ,
417 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
418 CREATE INDEX Tickets2 ON Tickets (Owner) ;
419 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
420 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
421 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
430 -- Sequences for table SCRIPACTIONS
433 CREATE SEQUENCE scripactions_id_seq;
435 CREATE TABLE ScripActions (
436 id INTEGER DEFAULT nextval('scripactions_id_seq'),
437 Name varchar(200) NULL ,
438 Description varchar(255) NULL ,
439 ExecModule varchar(60) NULL ,
440 Argument varchar(255) NULL ,
441 Creator integer NOT NULL DEFAULT 0 ,
442 Created TIMESTAMP NULL ,
443 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
444 LastUpdated TIMESTAMP NULL ,
456 -- Sequences for table TEMPLATES
459 CREATE SEQUENCE templates_id_seq;
461 CREATE TABLE Templates (
462 id INTEGER DEFAULT nextval('templates_id_seq'),
463 Queue integer NOT NULL DEFAULT 0 ,
464 Name varchar(200) NOT NULL ,
465 Description varchar(255) NULL ,
466 Type varchar(16) NULL ,
467 Language varchar(16) NULL ,
468 TranslationOf integer NOT NULL DEFAULT 0 ,
470 LastUpdated TIMESTAMP NULL ,
471 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
472 Creator integer NOT NULL DEFAULT 0 ,
473 Created TIMESTAMP NULL ,
480 -- {{{ ObjectCustomFieldValues
485 -- Sequences for table TICKETCUSTOMFIELDVALUES
488 CREATE SEQUENCE objectcustomfieldvalues_id_s;
490 CREATE TABLE ObjectCustomFieldValues (
491 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
492 CustomField int NOT NULL ,
493 ObjectType varchar(255) NULL ,
494 ObjectId int NOT NULL ,
495 SortOrder integer NOT NULL DEFAULT 0 ,
497 Content varchar(255) NULL ,
498 LargeContent text NULL,
499 ContentType varchar(80) NULL,
500 ContentEncoding varchar(80) NULL ,
502 Creator integer NOT NULL DEFAULT 0 ,
503 Created TIMESTAMP NULL ,
504 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
505 LastUpdated TIMESTAMP NULL ,
506 Disabled integer NOT NULL DEFAULT 0 ,
511 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
512 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
521 -- Sequences for table CUSTOMFIELDS
524 CREATE SEQUENCE customfields_id_seq;
526 CREATE TABLE CustomFields (
527 id INTEGER DEFAULT nextval('customfields_id_seq'),
528 Name varchar(200) NULL ,
529 Type varchar(200) NULL ,
530 MaxValues integer NOT NULL DEFAULT 0 ,
531 Repeated integer NOT NULL DEFAULT 0 ,
532 Pattern varchar(65536) NULL ,
533 LookupType varchar(255) NOT NULL ,
534 Description varchar(255) NULL ,
535 SortOrder integer NOT NULL DEFAULT 0 ,
537 Creator integer NOT NULL DEFAULT 0 ,
538 Created TIMESTAMP NULL ,
539 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
540 LastUpdated TIMESTAMP NULL ,
541 Disabled integer NOT NULL DEFAULT 0 ,
542 Required integer NOT NULL DEFAULT 0 ,
549 -- {{{ ObjectCustomFields
551 CREATE SEQUENCE objectcustomfields_id_s;
553 CREATE TABLE ObjectCustomFields (
554 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
555 CustomField integer NOT NULL,
556 ObjectId integer NOT 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 ,
569 -- {{{ CustomFieldValues
574 -- Sequences for table CUSTOMFIELDVALUES
577 CREATE SEQUENCE customfieldvalues_id_seq;
579 CREATE TABLE CustomFieldValues (
580 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
581 CustomField int NOT NULL ,
582 Name varchar(200) NULL ,
583 Description varchar(255) NULL ,
584 SortOrder integer NOT NULL DEFAULT 0 ,
586 Creator integer NOT NULL DEFAULT 0 ,
587 Created TIMESTAMP NULL ,
588 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
589 LastUpdated TIMESTAMP NULL ,
594 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
601 CREATE SEQUENCE attributes_id_seq;
603 CREATE TABLE Attributes (
604 id INTEGER DEFAULT nextval('attributes_id_seq'),
605 Name varchar(255) NOT NULL ,
606 Description varchar(255) NULL ,
608 ContentType varchar(16),
609 ObjectType varchar(64),
611 Creator integer NOT NULL DEFAULT 0 ,
612 Created TIMESTAMP NULL ,
613 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
614 LastUpdated TIMESTAMP NULL ,
619 CREATE INDEX Attributes1 on Attributes(Name);
620 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
626 -- sessions is used by Apache::Session to keep sessions in the database.
627 -- We should have a reaper script somewhere.
629 CREATE TABLE sessions (
630 id char(32) NOT NULL,
632 LastUpdated TIMESTAMP not null default current_timestamp,