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,
288 -- {{{ GroupMembersCache
293 -- Sequences for table CACHEDGROUPMEMBERS
296 CREATE SEQUENCE cachedgroupmembers_id_seq;
298 CREATE TABLE CachedGroupMembers (
299 id int DEFAULT nextval('cachedgroupmembers_id_seq'),
303 ImmediateParentId int,
304 Disabled integer NOT NULL DEFAULT 0 ,
309 CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId);
310 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);
311 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
320 -- Sequences for table USERS
323 CREATE SEQUENCE users_id_seq;
326 id INTEGER DEFAULT nextval('users_id_seq'),
327 Name varchar(200) NOT NULL ,
328 Password varchar(40) NULL ,
330 Signature text NULL ,
331 EmailAddress varchar(120) NULL ,
332 FreeformContactInfo text NULL ,
333 Organization varchar(200) NULL ,
334 RealName varchar(120) NULL ,
335 NickName varchar(16) NULL ,
336 Lang varchar(16) NULL ,
337 EmailEncoding varchar(16) NULL ,
338 WebEncoding varchar(16) NULL ,
339 ExternalContactInfoId varchar(100) NULL ,
340 ContactInfoSystem varchar(30) NULL ,
341 ExternalAuthId varchar(100) NULL ,
342 AuthSystem varchar(30) NULL ,
343 Gecos varchar(16) NULL ,
344 HomePhone varchar(30) NULL ,
345 WorkPhone varchar(30) NULL ,
346 MobilePhone varchar(30) NULL ,
347 PagerPhone varchar(30) NULL ,
348 Address1 varchar(200) NULL ,
349 Address2 varchar(200) NULL ,
350 City varchar(100) NULL ,
351 State varchar(100) NULL ,
352 Zip varchar(16) NULL ,
353 Country varchar(50) NULL ,
354 Timezone varchar(50) NULL ,
357 Creator integer NOT NULL DEFAULT 0 ,
358 Created TIMESTAMP NULL ,
359 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
360 LastUpdated TIMESTAMP NULL ,
366 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
367 CREATE INDEX Users2 ON Users (Name);
368 CREATE INDEX Users3 ON Users (id, EmailAddress);
369 CREATE INDEX Users4 ON Users (EmailAddress);
379 -- Sequences for table TICKETS
382 CREATE SEQUENCE tickets_id_seq;
384 CREATE TABLE Tickets (
385 id INTEGER DEFAULT nextval('tickets_id_seq'),
386 EffectiveId integer NOT NULL DEFAULT 0 ,
387 Queue integer NOT NULL DEFAULT 0 ,
388 Type varchar(16) NULL ,
389 IssueStatement integer NOT NULL DEFAULT 0 ,
390 Resolution integer NOT NULL DEFAULT 0 ,
391 Owner integer NOT NULL DEFAULT 0 ,
392 Subject varchar(200) NULL DEFAULT '[no subject]' ,
393 InitialPriority integer NOT NULL DEFAULT 0 ,
394 FinalPriority integer NOT NULL DEFAULT 0 ,
395 Priority integer NOT NULL DEFAULT 0 ,
396 TimeEstimated integer NOT NULL DEFAULT 0 ,
397 TimeWorked integer NOT NULL DEFAULT 0 ,
398 Status varchar(10) NULL ,
399 TimeLeft integer NOT NULL DEFAULT 0 ,
400 Told TIMESTAMP NULL ,
401 Starts TIMESTAMP NULL ,
402 Started TIMESTAMP NULL ,
404 Resolved TIMESTAMP NULL ,
407 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
408 LastUpdated TIMESTAMP NULL ,
409 Creator integer NOT NULL DEFAULT 0 ,
410 Created TIMESTAMP NULL ,
411 Disabled integer NOT NULL DEFAULT 0 ,
416 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
417 CREATE INDEX Tickets2 ON Tickets (Owner) ;
418 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
419 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
420 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
429 -- Sequences for table SCRIPACTIONS
432 CREATE SEQUENCE scripactions_id_seq;
434 CREATE TABLE ScripActions (
435 id INTEGER DEFAULT nextval('scripactions_id_seq'),
436 Name varchar(200) NULL ,
437 Description varchar(255) NULL ,
438 ExecModule varchar(60) NULL ,
439 Argument varchar(255) NULL ,
440 Creator integer NOT NULL DEFAULT 0 ,
441 Created TIMESTAMP NULL ,
442 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
443 LastUpdated TIMESTAMP NULL ,
455 -- Sequences for table TEMPLATES
458 CREATE SEQUENCE templates_id_seq;
460 CREATE TABLE Templates (
461 id INTEGER DEFAULT nextval('templates_id_seq'),
462 Queue integer NOT NULL DEFAULT 0 ,
463 Name varchar(200) NOT NULL ,
464 Description varchar(255) NULL ,
465 Type varchar(16) NULL ,
466 Language varchar(16) NULL ,
467 TranslationOf integer NOT NULL DEFAULT 0 ,
469 LastUpdated TIMESTAMP NULL ,
470 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
471 Creator integer NOT NULL DEFAULT 0 ,
472 Created TIMESTAMP NULL ,
479 -- {{{ ObjectCustomFieldValues
484 -- Sequences for table TICKETCUSTOMFIELDVALUES
487 CREATE SEQUENCE objectcustomfieldvalues_id_s;
489 CREATE TABLE ObjectCustomFieldValues (
490 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
491 CustomField int NOT NULL ,
492 ObjectType varchar(255) NULL ,
493 ObjectId int NOT NULL ,
494 SortOrder integer NOT NULL DEFAULT 0 ,
496 Content varchar(255) NULL ,
497 LargeContent text NULL,
498 ContentType varchar(80) NULL,
499 ContentEncoding varchar(80) NULL ,
501 Creator integer NOT NULL DEFAULT 0 ,
502 Created TIMESTAMP NULL ,
503 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
504 LastUpdated TIMESTAMP NULL ,
505 Disabled integer NOT NULL DEFAULT 0 ,
510 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
511 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
520 -- Sequences for table CUSTOMFIELDS
523 CREATE SEQUENCE customfields_id_seq;
525 CREATE TABLE CustomFields (
526 id INTEGER DEFAULT nextval('customfields_id_seq'),
527 Name varchar(200) NULL ,
528 Type varchar(200) NULL ,
529 MaxValues integer NOT NULL DEFAULT 0 ,
530 Repeated integer NOT NULL DEFAULT 0 ,
531 Pattern varchar(255) NULL ,
532 LookupType varchar(255) NOT NULL ,
533 Description varchar(255) NULL ,
534 SortOrder integer NOT NULL DEFAULT 0 ,
536 Creator integer NOT NULL DEFAULT 0 ,
537 Created TIMESTAMP NULL ,
538 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
539 LastUpdated TIMESTAMP NULL ,
540 Disabled integer NOT NULL DEFAULT 0 ,
547 -- {{{ ObjectCustomFields
549 CREATE SEQUENCE objectcustomfields_id_s;
551 CREATE TABLE ObjectCustomFields (
552 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
553 CustomField integer NOT NULL,
554 ObjectId integer NOT NULL,
555 SortOrder integer NOT NULL DEFAULT 0 ,
557 Creator integer NOT NULL DEFAULT 0 ,
558 Created TIMESTAMP NULL ,
559 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
560 LastUpdated TIMESTAMP NULL ,
567 -- {{{ CustomFieldValues
572 -- Sequences for table CUSTOMFIELDVALUES
575 CREATE SEQUENCE customfieldvalues_id_seq;
577 CREATE TABLE CustomFieldValues (
578 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
579 CustomField int NOT NULL ,
580 Name varchar(200) NULL ,
581 Description varchar(255) NULL ,
582 SortOrder integer NOT NULL DEFAULT 0 ,
584 Creator integer NOT NULL DEFAULT 0 ,
585 Created TIMESTAMP NULL ,
586 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
587 LastUpdated TIMESTAMP NULL ,
592 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
599 CREATE SEQUENCE attributes_id_seq;
601 CREATE TABLE Attributes (
602 id INTEGER DEFAULT nextval('attributes_id_seq'),
603 Name varchar(255) NOT NULL ,
604 Description varchar(255) NULL ,
606 ContentType varchar(16),
607 ObjectType varchar(64),
609 Creator integer NOT NULL DEFAULT 0 ,
610 Created TIMESTAMP NULL ,
611 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
612 LastUpdated TIMESTAMP NULL ,
617 CREATE INDEX Attributes1 on Attributes(Name);
618 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
624 -- sessions is used by Apache::Session to keep sessions in the database.
625 -- We should have a reaper script somewhere.
627 CREATE TABLE sessions (
628 id char(32) NOT NULL,
630 LastUpdated TIMESTAMP not null default current_timestamp,