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 ,
407 WillResolve TIMESTAMP NULL ,
409 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
410 LastUpdated TIMESTAMP NULL ,
411 Creator integer NOT NULL DEFAULT 0 ,
412 Created TIMESTAMP NULL ,
413 Disabled integer NOT NULL DEFAULT 0 ,
418 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
419 CREATE INDEX Tickets2 ON Tickets (Owner) ;
420 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
421 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
422 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
431 -- Sequences for table SCRIPACTIONS
434 CREATE SEQUENCE scripactions_id_seq;
436 CREATE TABLE ScripActions (
437 id INTEGER DEFAULT nextval('scripactions_id_seq'),
438 Name varchar(200) NULL ,
439 Description varchar(255) NULL ,
440 ExecModule varchar(60) NULL ,
441 Argument varchar(255) NULL ,
442 Creator integer NOT NULL DEFAULT 0 ,
443 Created TIMESTAMP NULL ,
444 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
445 LastUpdated TIMESTAMP NULL ,
457 -- Sequences for table TEMPLATES
460 CREATE SEQUENCE templates_id_seq;
462 CREATE TABLE Templates (
463 id INTEGER DEFAULT nextval('templates_id_seq'),
464 Queue integer NOT NULL DEFAULT 0 ,
465 Name varchar(200) NOT NULL ,
466 Description varchar(255) NULL ,
467 Type varchar(16) NULL ,
468 Language varchar(16) NULL ,
469 TranslationOf integer NOT NULL DEFAULT 0 ,
471 LastUpdated TIMESTAMP NULL ,
472 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
473 Creator integer NOT NULL DEFAULT 0 ,
474 Created TIMESTAMP NULL ,
481 -- {{{ ObjectCustomFieldValues
486 -- Sequences for table TICKETCUSTOMFIELDVALUES
489 CREATE SEQUENCE objectcustomfieldvalues_id_s;
491 CREATE TABLE ObjectCustomFieldValues (
492 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
493 CustomField int NOT NULL ,
494 ObjectType varchar(255) NULL ,
495 ObjectId int NOT NULL ,
496 SortOrder integer NOT NULL DEFAULT 0 ,
498 Content varchar(255) NULL ,
499 LargeContent text NULL,
500 ContentType varchar(80) NULL,
501 ContentEncoding varchar(80) NULL ,
503 Creator integer NOT NULL DEFAULT 0 ,
504 Created TIMESTAMP NULL ,
505 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
506 LastUpdated TIMESTAMP NULL ,
507 Disabled integer NOT NULL DEFAULT 0 ,
512 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
513 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
522 -- Sequences for table CUSTOMFIELDS
525 CREATE SEQUENCE customfields_id_seq;
527 CREATE TABLE CustomFields (
528 id INTEGER DEFAULT nextval('customfields_id_seq'),
529 Name varchar(200) NULL ,
530 Type varchar(200) NULL ,
531 MaxValues integer NOT NULL DEFAULT 0 ,
532 Repeated integer NOT NULL DEFAULT 0 ,
533 Pattern varchar(65536) NULL ,
534 LookupType varchar(255) NOT NULL ,
535 Description varchar(255) NULL ,
536 SortOrder integer NOT NULL DEFAULT 0 ,
538 Creator integer NOT NULL DEFAULT 0 ,
539 Created TIMESTAMP NULL ,
540 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
541 LastUpdated TIMESTAMP NULL ,
542 Disabled integer NOT NULL DEFAULT 0 ,
543 Required integer NOT NULL DEFAULT 0 ,
550 -- {{{ ObjectCustomFields
552 CREATE SEQUENCE objectcustomfields_id_s;
554 CREATE TABLE ObjectCustomFields (
555 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
556 CustomField integer NOT NULL,
557 ObjectId integer NOT 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 ,
570 -- {{{ CustomFieldValues
575 -- Sequences for table CUSTOMFIELDVALUES
578 CREATE SEQUENCE customfieldvalues_id_seq;
580 CREATE TABLE CustomFieldValues (
581 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
582 CustomField int NOT NULL ,
583 Name varchar(200) NULL ,
584 Description varchar(255) NULL ,
585 SortOrder integer NOT NULL DEFAULT 0 ,
587 Creator integer NOT NULL DEFAULT 0 ,
588 Created TIMESTAMP NULL ,
589 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
590 LastUpdated TIMESTAMP NULL ,
595 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
602 CREATE SEQUENCE attributes_id_seq;
604 CREATE TABLE Attributes (
605 id INTEGER DEFAULT nextval('attributes_id_seq'),
606 Name varchar(255) NOT NULL ,
607 Description varchar(255) NULL ,
609 ContentType varchar(16),
610 ObjectType varchar(64),
612 Creator integer NOT NULL DEFAULT 0 ,
613 Created TIMESTAMP NULL ,
614 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
615 LastUpdated TIMESTAMP NULL ,
620 CREATE INDEX Attributes1 on Attributes(Name);
621 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
627 -- sessions is used by Apache::Session to keep sessions in the database.
628 -- We should have a reaper script somewhere.
630 CREATE TABLE sessions (
631 id char(32) NOT NULL,
633 LastUpdated TIMESTAMP not null default current_timestamp,