1 ------------------------------------------------------------------
2 -- My2Pg 1.23 translated dump
4 ------------------------------------------------------------------
7 -- Deal with changed defaults of Pg 8.x
9 SELECT CASE WHEN version() like '%PostgreSQL 8%" THEN
10 set_config('default_with_oids','on','f')
15 -- Sequences for table ATTACHMENTS
18 CREATE SEQUENCE attachments_id_seq;
22 CREATE TABLE Attachments (
23 id INTEGER DEFAULT nextval('attachments_id_seq'),
24 TransactionId integer NOT NULL ,
25 Parent integer NOT NULL DEFAULT 0 ,
26 MessageId varchar(160) NULL ,
27 Subject varchar(255) NULL ,
28 Filename varchar(255) NULL ,
29 ContentType varchar(80) NULL ,
30 ContentEncoding varchar(80) NULL ,
33 Creator integer NOT NULL DEFAULT 0 ,
34 Created TIMESTAMP NULL ,
39 CREATE INDEX Attachments1 ON Attachments (Parent) ;
40 CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
41 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
48 -- Sequences for table QUEUES
51 CREATE SEQUENCE queues_id_seq;
54 id INTEGER DEFAULT nextval('queues_id_seq'),
55 Name varchar(200) NOT NULL ,
56 Description varchar(255) NULL ,
57 CorrespondAddress varchar(120) NULL ,
58 CommentAddress varchar(120) NULL ,
59 InitialPriority integer NOT NULL DEFAULT 0 ,
60 FinalPriority integer NOT NULL DEFAULT 0 ,
61 DefaultDueIn integer NOT NULL DEFAULT 0 ,
62 Creator integer NOT NULL DEFAULT 0 ,
63 Created TIMESTAMP NULL ,
64 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
65 LastUpdated TIMESTAMP NULL ,
66 Disabled integer NOT NULL DEFAULT 0 ,
70 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
79 -- Sequences for table LINKS
82 CREATE SEQUENCE links_id_seq;
85 id INTEGER DEFAULT nextval('links_id_seq'),
86 Base varchar(240) NULL ,
87 Target varchar(240) NULL ,
88 Type varchar(20) NOT NULL ,
89 LocalTarget integer NOT NULL DEFAULT 0 ,
90 LocalBase integer NOT NULL DEFAULT 0 ,
91 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
92 LastUpdated TIMESTAMP NULL ,
93 Creator integer NOT NULL DEFAULT 0 ,
94 Created TIMESTAMP NULL ,
98 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
99 CREATE INDEX Links4 ON Links(Type,LocalBase);
108 -- Sequences for table PRINCIPALS
111 CREATE SEQUENCE principals_id_seq;
113 CREATE TABLE Principals (
114 id INTEGER DEFAULT nextval('principals_id_seq') not null,
115 PrincipalType VARCHAR(16) not null,
117 Disabled integer NOT NULL DEFAULT 0 ,
122 CREATE INDEX Principals2 ON Principals (ObjectId);
132 -- Sequences for table GROUPS
135 CREATE SEQUENCE groups_id_seq;
137 CREATE TABLE Groups (
138 id INTEGER DEFAULT nextval('groups_id_seq'),
139 Name varchar(200) NULL ,
140 Description varchar(255) NULL ,
147 CREATE UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name);
148 CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
153 -- {{{ ScripConditions
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 DelegatedBy integer NOT NULL DEFAULT 0,
264 DelegatedFrom integer NOT NULL DEFAULT 0,
269 CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
279 -- Sequences for table GROUPMEMBERS
282 CREATE SEQUENCE groupmembers_id_seq;
284 CREATE TABLE GroupMembers (
285 id INTEGER DEFAULT nextval('groupmembers_id_seq'),
286 GroupId integer NOT NULL DEFAULT 0,
287 MemberId integer NOT NULL DEFAULT 0,
294 -- {{{ GroupMembersCache
299 -- Sequences for table CACHEDGROUPMEMBERS
302 CREATE SEQUENCE cachedgroupmembers_id_seq;
304 CREATE TABLE CachedGroupMembers (
305 id int DEFAULT nextval('cachedgroupmembers_id_seq'),
309 ImmediateParentId int,
310 Disabled integer NOT NULL DEFAULT 0 ,
315 CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId);
316 CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);
317 CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
326 -- Sequences for table USERS
329 CREATE SEQUENCE users_id_seq;
332 id INTEGER DEFAULT nextval('users_id_seq'),
333 Name varchar(200) NOT NULL ,
334 Password varchar(40) NULL ,
336 Signature text NULL ,
337 EmailAddress varchar(120) NULL ,
338 FreeformContactInfo text NULL ,
339 Organization varchar(200) NULL ,
340 RealName varchar(120) NULL ,
341 NickName varchar(16) NULL ,
342 Lang varchar(16) NULL ,
343 EmailEncoding varchar(16) NULL ,
344 WebEncoding varchar(16) NULL ,
345 ExternalContactInfoId varchar(100) NULL ,
346 ContactInfoSystem varchar(30) NULL ,
347 ExternalAuthId varchar(100) NULL ,
348 AuthSystem varchar(30) NULL ,
349 Gecos varchar(16) NULL ,
350 HomePhone varchar(30) NULL ,
351 WorkPhone varchar(30) NULL ,
352 MobilePhone varchar(30) NULL ,
353 PagerPhone varchar(30) NULL ,
354 Address1 varchar(200) NULL ,
355 Address2 varchar(200) NULL ,
356 City varchar(100) NULL ,
357 State varchar(100) NULL ,
358 Zip varchar(16) NULL ,
359 Country varchar(50) NULL ,
360 Timezone varchar(50) NULL ,
363 Creator integer NOT NULL DEFAULT 0 ,
364 Created TIMESTAMP NULL ,
365 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
366 LastUpdated TIMESTAMP NULL ,
372 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
373 CREATE INDEX Users2 ON Users (Name);
374 CREATE INDEX Users3 ON Users (id, EmailAddress);
375 CREATE INDEX Users4 ON Users (EmailAddress);
385 -- Sequences for table TICKETS
388 CREATE SEQUENCE tickets_id_seq;
390 CREATE TABLE Tickets (
391 id INTEGER DEFAULT nextval('tickets_id_seq'),
392 EffectiveId integer NOT NULL DEFAULT 0 ,
393 Queue integer NOT NULL DEFAULT 0 ,
394 Type varchar(16) NULL ,
395 IssueStatement integer NOT NULL DEFAULT 0 ,
396 Resolution integer NOT NULL DEFAULT 0 ,
397 Owner integer NOT NULL DEFAULT 0 ,
398 Subject varchar(200) NULL DEFAULT '[no subject]' ,
399 InitialPriority integer NOT NULL DEFAULT 0 ,
400 FinalPriority integer NOT NULL DEFAULT 0 ,
401 Priority integer NOT NULL DEFAULT 0 ,
402 TimeEstimated integer NOT NULL DEFAULT 0 ,
403 TimeWorked integer NOT NULL DEFAULT 0 ,
404 Status varchar(10) NULL ,
405 TimeLeft integer NOT NULL DEFAULT 0 ,
406 Told TIMESTAMP NULL ,
407 Starts TIMESTAMP NULL ,
408 Started TIMESTAMP NULL ,
410 Resolved TIMESTAMP NULL ,
413 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
414 LastUpdated TIMESTAMP NULL ,
415 Creator integer NOT NULL DEFAULT 0 ,
416 Created TIMESTAMP NULL ,
417 Disabled integer NOT NULL DEFAULT 0 ,
422 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
423 CREATE INDEX Tickets2 ON Tickets (Owner) ;
424 CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
425 CREATE INDEX Tickets4 ON Tickets (id, Status) ;
426 CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
435 -- Sequences for table SCRIPACTIONS
438 CREATE SEQUENCE scripactions_id_seq;
440 CREATE TABLE ScripActions (
441 id INTEGER DEFAULT nextval('scripactions_id_seq'),
442 Name varchar(200) NULL ,
443 Description varchar(255) NULL ,
444 ExecModule varchar(60) NULL ,
445 Argument varchar(255) NULL ,
446 Creator integer NOT NULL DEFAULT 0 ,
447 Created TIMESTAMP NULL ,
448 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
449 LastUpdated TIMESTAMP NULL ,
461 -- Sequences for table TEMPLATES
464 CREATE SEQUENCE templates_id_seq;
466 CREATE TABLE Templates (
467 id INTEGER DEFAULT nextval('templates_id_seq'),
468 Queue integer NOT NULL DEFAULT 0 ,
469 Name varchar(200) NOT NULL ,
470 Description varchar(255) NULL ,
471 Type varchar(16) NULL ,
472 Language varchar(16) NULL ,
473 TranslationOf integer NOT NULL DEFAULT 0 ,
475 LastUpdated TIMESTAMP NULL ,
476 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
477 Creator integer NOT NULL DEFAULT 0 ,
478 Created TIMESTAMP NULL ,
485 -- {{{ ObjectCustomFieldValues
490 -- Sequences for table TICKETCUSTOMFIELDVALUES
493 CREATE SEQUENCE objectcustomfieldvalues_id_s;
495 CREATE TABLE ObjectCustomFieldValues (
496 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
497 CustomField int NOT NULL ,
498 ObjectType varchar(255) NULL ,
499 ObjectId int NOT NULL ,
500 SortOrder integer NOT NULL DEFAULT 0 ,
502 Content varchar(255) NULL ,
503 LargeContent text NULL,
504 ContentType varchar(80) NULL,
505 ContentEncoding varchar(80) NULL ,
507 Creator integer NOT NULL DEFAULT 0 ,
508 Created TIMESTAMP NULL ,
509 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
510 LastUpdated TIMESTAMP NULL ,
511 Disabled integer NOT NULL DEFAULT 0 ,
516 CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
517 CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
526 -- Sequences for table CUSTOMFIELDS
529 CREATE SEQUENCE customfields_id_seq;
531 CREATE TABLE CustomFields (
532 id INTEGER DEFAULT nextval('customfields_id_seq'),
533 Name varchar(200) NULL ,
534 Type varchar(200) NULL ,
535 MaxValues integer NOT NULL DEFAULT 0 ,
536 Repeated integer NOT NULL DEFAULT 0 ,
537 Pattern varchar(255) NULL ,
538 LookupType varchar(255) NOT NULL ,
539 Description varchar(255) NULL ,
540 SortOrder integer NOT NULL DEFAULT 0 ,
542 Creator integer NOT NULL DEFAULT 0 ,
543 Created TIMESTAMP NULL ,
544 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
545 LastUpdated TIMESTAMP NULL ,
546 Disabled integer NOT NULL DEFAULT 0 ,
553 -- {{{ ObjectCustomFields
555 CREATE SEQUENCE objectcustomfields_id_s;
557 CREATE TABLE ObjectCustomFields (
558 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
559 CustomField integer NOT NULL,
560 ObjectId integer NOT NULL,
561 SortOrder integer NOT NULL DEFAULT 0 ,
563 Creator integer NOT NULL DEFAULT 0 ,
564 Created TIMESTAMP NULL ,
565 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
566 LastUpdated TIMESTAMP NULL ,
573 -- {{{ CustomFieldValues
578 -- Sequences for table CUSTOMFIELDVALUES
581 CREATE SEQUENCE customfieldvalues_id_seq;
583 CREATE TABLE CustomFieldValues (
584 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
585 CustomField int NOT NULL ,
586 Name varchar(200) NULL ,
587 Description varchar(255) NULL ,
588 SortOrder integer NOT NULL DEFAULT 0 ,
590 Creator integer NOT NULL DEFAULT 0 ,
591 Created TIMESTAMP NULL ,
592 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
593 LastUpdated TIMESTAMP NULL ,
598 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
605 CREATE SEQUENCE attributes_id_seq;
607 CREATE TABLE Attributes (
608 id INTEGER DEFAULT nextval('attributes_id_seq'),
609 Name varchar(255) NOT NULL ,
610 Description varchar(255) NULL ,
612 ContentType varchar(16),
613 ObjectType varchar(64),
615 Creator integer NOT NULL DEFAULT 0 ,
616 Created TIMESTAMP NULL ,
617 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
618 LastUpdated TIMESTAMP NULL ,
623 CREATE INDEX Attributes1 on Attributes(Name);
624 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
630 -- sessions is used by Apache::Session to keep sessions in the database.
631 -- We should have a reaper script somewhere.
633 CREATE TABLE sessions (
634 id char(32) NOT NULL,
636 LastUpdated TIMESTAMP not null default current_timestamp,