1 ------------------------------------------------------------------
2 -- My2Pg 1.23 translated dump
4 ------------------------------------------------------------------
12 -- Sequences for table ATTACHMENTS
15 CREATE SEQUENCE attachments_id_seq;
19 CREATE TABLE Attachments (
20 id INTEGER DEFAULT nextval('attachments_id_seq'),
21 TransactionId integer NOT NULL ,
22 Parent integer NOT NULL DEFAULT 0 ,
23 MessageId varchar(160) NULL ,
24 Subject varchar(255) NULL ,
25 Filename varchar(255) NULL ,
26 ContentType varchar(80) NULL ,
27 ContentEncoding varchar(80) NULL ,
30 Creator integer NOT NULL DEFAULT 0 ,
31 Created TIMESTAMP NULL ,
36 CREATE INDEX Attachments1 ON Attachments (Parent) ;
37 CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
38 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
45 -- Sequences for table QUEUES
48 CREATE SEQUENCE queues_id_seq;
51 id INTEGER DEFAULT nextval('queues_id_seq'),
52 Name varchar(200) NOT NULL ,
53 Description varchar(255) NULL ,
54 CorrespondAddress varchar(120) NULL ,
55 CommentAddress varchar(120) NULL ,
56 InitialPriority integer NOT NULL DEFAULT 0 ,
57 FinalPriority integer NOT NULL DEFAULT 0 ,
58 DefaultDueIn integer NOT NULL DEFAULT 0 ,
59 Creator integer NOT NULL DEFAULT 0 ,
60 Created TIMESTAMP NULL ,
61 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
62 LastUpdated TIMESTAMP NULL ,
63 Disabled int2 NOT NULL DEFAULT 0 ,
67 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
76 -- Sequences for table LINKS
79 CREATE SEQUENCE links_id_seq;
82 id INTEGER DEFAULT nextval('links_id_seq'),
83 Base varchar(240) NULL ,
84 Target varchar(240) NULL ,
85 Type varchar(20) NOT NULL ,
86 LocalTarget integer NOT NULL DEFAULT 0 ,
87 LocalBase integer NOT NULL DEFAULT 0 ,
88 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
89 LastUpdated TIMESTAMP NULL ,
90 Creator integer NOT NULL DEFAULT 0 ,
91 Created TIMESTAMP NULL ,
95 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
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 int2 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 ,
139 Instance varchar(64),
143 CREATE UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name);
144 CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
149 -- {{{ ScripConditions
154 -- Sequences for table SCRIPCONDITIONS
157 CREATE SEQUENCE scripconditions_id_seq;
159 CREATE TABLE ScripConditions (
160 id INTEGER DEFAULT nextval('scripconditions_id_seq'),
161 Name varchar(200) NULL ,
162 Description varchar(255) NULL ,
163 ExecModule varchar(60) NULL ,
164 Argument varchar(255) NULL ,
165 ApplicableTransTypes varchar(60) NULL ,
167 Creator integer NOT NULL DEFAULT 0 ,
168 Created TIMESTAMP NULL ,
169 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
170 LastUpdated TIMESTAMP NULL ,
181 -- Sequences for table TRANSACTIONS
184 CREATE SEQUENCE transactions_id_seq;
186 CREATE TABLE Transactions (
187 id INTEGER DEFAULT nextval('transactions_id_seq'),
188 EffectiveTicket integer NOT NULL DEFAULT 0 ,
189 Ticket integer NOT NULL DEFAULT 0 ,
190 TimeTaken integer NOT NULL DEFAULT 0 ,
191 Type varchar(20) NULL ,
192 Field varchar(40) NULL ,
193 OldValue varchar(255) NULL ,
194 NewValue varchar(255) NULL ,
195 Data varchar(100) NULL ,
197 Creator integer NOT NULL DEFAULT 0 ,
198 Created TIMESTAMP NULL ,
202 CREATE INDEX Transactions1 ON Transactions (Ticket);
203 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
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 int2 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 int2 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 -- {{{ TicketCustomFieldValues
484 -- Sequences for table TICKETCUSTOMFIELDVALUES
487 CREATE SEQUENCE ticketcustomfieldvalues_id_s;
489 CREATE TABLE TicketCustomFieldValues (
490 id INTEGER DEFAULT nextval('ticketcustomfieldvalues_id_s'),
491 Ticket int NOT NULL ,
492 CustomField int NOT NULL ,
493 Content varchar(255) NULL ,
495 Creator integer NOT NULL DEFAULT 0 ,
496 Created TIMESTAMP NULL ,
497 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
498 LastUpdated TIMESTAMP NULL ,
510 -- Sequences for table CUSTOMFIELDS
513 CREATE SEQUENCE customfields_id_seq;
515 CREATE TABLE CustomFields (
516 id INTEGER DEFAULT nextval('customfields_id_seq'),
517 Name varchar(200) NULL ,
518 Type varchar(200) NULL ,
519 Queue integer NOT NULL DEFAULT 0 ,
520 Description varchar(255) NULL ,
521 SortOrder integer NOT NULL DEFAULT 0 ,
523 Creator integer NOT NULL DEFAULT 0 ,
524 Created TIMESTAMP NULL ,
525 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
526 LastUpdated TIMESTAMP NULL ,
527 Disabled int2 NOT NULL DEFAULT 0 ,
534 -- {{{ CustomFieldValues
539 -- Sequences for table CUSTOMFIELDVALUES
542 CREATE SEQUENCE customfieldvalues_id_seq;
544 CREATE TABLE CustomFieldValues (
545 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
546 CustomField int NOT NULL ,
547 Name varchar(200) NULL ,
548 Description varchar(255) NULL ,
549 SortOrder integer NOT NULL DEFAULT 0 ,
551 Creator integer NOT NULL DEFAULT 0 ,
552 Created TIMESTAMP NULL ,
553 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
554 LastUpdated TIMESTAMP NULL ,
563 -- sessions is used by Apache::Session to keep sessions in the database.
564 -- We should have a reaper script somewhere.
566 CREATE TABLE sessions (
567 id char(32) NOT NULL,
569 LastUpdated TIMESTAMP not null default current_timestamp,