Upgrade to 4.2.8
[usit-rt.git] / etc / schema.Pg
CommitLineData
84fb5b46
MKG
1------------------------------------------------------------------
2-- My2Pg 1.23 translated dump
3--
4------------------------------------------------------------------
5
6
7
8--
9-- Sequences for table ATTACHMENTS
10--
11
12CREATE SEQUENCE attachments_id_seq;
13
14
15
16CREATE 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 ,
25 Content text NULL ,
26 Headers text NULL ,
27 Creator integer NOT NULL DEFAULT 0 ,
28 Created TIMESTAMP NULL ,
29 PRIMARY KEY (id)
30
31);
32
33CREATE INDEX Attachments1 ON Attachments (Parent) ;
34CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
35CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
36
37
38
39
40
41--
42-- Sequences for table QUEUES
43--
44
45CREATE SEQUENCE queues_id_seq;
46
47CREATE TABLE Queues (
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 Lifecycle varchar(32) NULL,
54 SubjectTag varchar(120) NULL ,
55 InitialPriority integer NOT NULL DEFAULT 0 ,
56 FinalPriority integer NOT NULL DEFAULT 0 ,
57 DefaultDueIn integer NOT NULL DEFAULT 0 ,
58 Creator integer NOT NULL DEFAULT 0 ,
59 Created TIMESTAMP NULL ,
60 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
61 LastUpdated TIMESTAMP NULL ,
62 Disabled integer NOT NULL DEFAULT 0 ,
63 PRIMARY KEY (id)
64
65);
3ffc5f4f 66CREATE UNIQUE INDEX Queues1 ON Queues (LOWER(Name)) ;
84fb5b46
MKG
67
68
69
70
71
72
73
74--
75-- Sequences for table LINKS
76--
77
78CREATE SEQUENCE links_id_seq;
79
80CREATE TABLE Links (
81 id INTEGER DEFAULT nextval('links_id_seq'),
82 Base varchar(240) NULL ,
83 Target varchar(240) NULL ,
84 Type varchar(20) NOT NULL ,
85 LocalTarget integer NOT NULL DEFAULT 0 ,
86 LocalBase integer NOT NULL DEFAULT 0 ,
87 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
88 LastUpdated TIMESTAMP NULL ,
89 Creator integer NOT NULL DEFAULT 0 ,
90 Created TIMESTAMP NULL ,
91 PRIMARY KEY (id)
92
93);
94CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
95CREATE INDEX Links4 ON Links(Type,LocalBase);
96
97
98
99
100
101
102
103--
104-- Sequences for table PRINCIPALS
105--
106
107CREATE SEQUENCE principals_id_seq;
108
109CREATE TABLE Principals (
110 id INTEGER DEFAULT nextval('principals_id_seq') not null,
111 PrincipalType VARCHAR(16) not null,
112 ObjectId integer,
113 Disabled integer NOT NULL DEFAULT 0 ,
114 PRIMARY KEY (id)
115
116);
117
118CREATE INDEX Principals2 ON Principals (ObjectId);
119
120
121
122
123
124
125
126
127--
128-- Sequences for table GROUPS
129--
130
131CREATE SEQUENCE groups_id_seq;
132
133CREATE TABLE Groups (
134 id INTEGER DEFAULT nextval('groups_id_seq'),
135 Name varchar(200) NULL ,
136 Description varchar(255) NULL ,
137 Domain varchar(64),
138 Type varchar(64),
139 Instance integer,
140 Creator integer NOT NULL DEFAULT 0 ,
141 Created TIMESTAMP NULL ,
142 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
143 LastUpdated TIMESTAMP NULL ,
144 PRIMARY KEY (id)
145
146);
3ffc5f4f
MKG
147CREATE INDEX Groups1 ON Groups (LOWER(Domain), LOWER(Type), Instance);
148CREATE INDEX Groups2 ON Groups (LOWER(Domain), LOWER(Name), Instance);
149CREATE INDEX Groups3 On Groups (Instance);
84fb5b46
MKG
150
151
152
153
154
155
156
157
158--
159-- Sequences for table SCRIPCONDITIONS
160--
161
162CREATE SEQUENCE scripconditions_id_seq;
163
164CREATE TABLE ScripConditions (
165 id INTEGER DEFAULT nextval('scripconditions_id_seq'),
166 Name varchar(200) NULL ,
167 Description varchar(255) NULL ,
168 ExecModule varchar(60) NULL ,
169 Argument varchar(255) NULL ,
170 ApplicableTransTypes varchar(60) NULL ,
171
172 Creator integer NOT NULL DEFAULT 0 ,
173 Created TIMESTAMP NULL ,
174 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
175 LastUpdated TIMESTAMP NULL ,
176 PRIMARY KEY (id)
177
178);
179
180
181
182
183
184
185--
186-- Sequences for table TRANSACTIONS
187--
188
189CREATE SEQUENCE transactions_id_seq;
190
191CREATE TABLE Transactions (
192 id INTEGER DEFAULT nextval('transactions_id_seq'),
193 ObjectType varchar(255) NOT NULL ,
194 ObjectId integer NOT NULL DEFAULT 0 ,
195 TimeTaken integer NOT NULL DEFAULT 0 ,
196 Type varchar(20) NULL ,
197 Field varchar(40) NULL ,
198 OldValue varchar(255) NULL ,
199 NewValue varchar(255) NULL ,
200 ReferenceType varchar(255) NULL,
201 OldReference integer NULL ,
202 NewReference integer NULL ,
203 Data varchar(255) NULL ,
204
205 Creator integer NOT NULL DEFAULT 0 ,
206 Created TIMESTAMP NULL ,
207 PRIMARY KEY (id)
208
209);
210CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
211
212
213
214
215
216
217
218--
219-- Sequences for table SCRIPS
220--
221
222CREATE SEQUENCE scrips_id_seq;
223
224CREATE TABLE Scrips (
225 id INTEGER DEFAULT nextval('scrips_id_seq'),
226 Description varchar(255),
227 ScripCondition integer NOT NULL DEFAULT 0 ,
228 ScripAction integer NOT NULL DEFAULT 0 ,
84fb5b46
MKG
229 CustomIsApplicableCode text NULL ,
230 CustomPrepareCode text NULL ,
231 CustomCommitCode text NULL ,
3ffc5f4f
MKG
232 Disabled integer NOT NULL DEFAULT 0 ,
233 Template varchar(200) NOT NULL,
84fb5b46
MKG
234 Creator integer NOT NULL DEFAULT 0 ,
235 Created TIMESTAMP NULL ,
236 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
237 LastUpdated TIMESTAMP NULL ,
238 PRIMARY KEY (id)
239
240);
241
242
3ffc5f4f
MKG
243CREATE SEQUENCE objectscrips_id_seq;
244
245CREATE TABLE ObjectScrips (
246 id INTEGER DEFAULT nextval('objectscrips_id_seq'),
247 Scrip integer NOT NULL,
248 Stage varchar(32) NOT NULL DEFAULT 'TransactionCreate' ,
249 ObjectId integer NOT NULL,
250 SortOrder integer NOT NULL DEFAULT 0 ,
251
252 Creator integer NOT NULL DEFAULT 0 ,
253 Created TIMESTAMP NULL ,
254 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
255 LastUpdated TIMESTAMP NULL ,
256 PRIMARY KEY (id)
257
258);
84fb5b46 259
3ffc5f4f 260CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);
84fb5b46
MKG
261
262
263
264--
265-- Sequences for table ACL
266--
267
268CREATE SEQUENCE acl_id_seq;
269
270CREATE TABLE ACL (
271 id INTEGER DEFAULT nextval('acl_id_seq'),
272 PrincipalType varchar(25) NOT NULL,
273
274 PrincipalId integer NOT NULL ,
275 RightName varchar(25) NOT NULL ,
276 ObjectType varchar(25) NOT NULL ,
277 ObjectId integer NOT NULL DEFAULT 0,
278 Creator integer NOT NULL DEFAULT 0 ,
279 Created TIMESTAMP NULL ,
280 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
281 LastUpdated TIMESTAMP NULL ,
282 PRIMARY KEY (id)
283
284);
285
286CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
287
288
289
290
291
292
293
294
295--
296-- Sequences for table GROUPMEMBERS
297--
298
299CREATE SEQUENCE groupmembers_id_seq;
300
301CREATE TABLE GroupMembers (
302 id INTEGER DEFAULT nextval('groupmembers_id_seq'),
303 GroupId integer NOT NULL DEFAULT 0,
304 MemberId integer NOT NULL DEFAULT 0,
305 Creator integer NOT NULL DEFAULT 0 ,
306 Created TIMESTAMP NULL ,
307 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
308 LastUpdated TIMESTAMP NULL ,
309 PRIMARY KEY (id)
310
311);
312
313CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId);
314
315
316
317
318
319
320
321--
322-- Sequences for table CACHEDGROUPMEMBERS
323--
324
325CREATE SEQUENCE cachedgroupmembers_id_seq;
326
327CREATE TABLE CachedGroupMembers (
328 id int DEFAULT nextval('cachedgroupmembers_id_seq'),
329 GroupId int,
330 MemberId int,
331 Via int,
332 ImmediateParentId int,
333 Disabled integer NOT NULL DEFAULT 0 ,
334 PRIMARY KEY (id)
335
336);
337
3ffc5f4f
MKG
338CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled);
339CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
340CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId,ImmediateParentId);
84fb5b46
MKG
341
342
343
344
345
346
347
348--
349-- Sequences for table USERS
350--
351
352CREATE SEQUENCE users_id_seq;
353
354CREATE TABLE Users (
355 id INTEGER DEFAULT nextval('users_id_seq'),
356 Name varchar(200) NOT NULL ,
357 Password varchar(256) NULL ,
358 AuthToken varchar(16) NULL ,
359 Comments text NULL ,
360 Signature text NULL ,
361 EmailAddress varchar(120) NULL ,
362 FreeformContactInfo text NULL ,
363 Organization varchar(200) NULL ,
364 RealName varchar(120) NULL ,
365 NickName varchar(16) NULL ,
366 Lang varchar(16) NULL ,
367 EmailEncoding varchar(16) NULL ,
368 WebEncoding varchar(16) NULL ,
369 ExternalContactInfoId varchar(100) NULL ,
370 ContactInfoSystem varchar(30) NULL ,
371 ExternalAuthId varchar(100) NULL ,
372 AuthSystem varchar(30) NULL ,
373 Gecos varchar(16) NULL ,
374 HomePhone varchar(30) NULL ,
375 WorkPhone varchar(30) NULL ,
376 MobilePhone varchar(30) NULL ,
377 PagerPhone varchar(30) NULL ,
378 Address1 varchar(200) NULL ,
379 Address2 varchar(200) NULL ,
380 City varchar(100) NULL ,
381 State varchar(100) NULL ,
382 Zip varchar(16) NULL ,
383 Country varchar(50) NULL ,
384 Timezone varchar(50) NULL ,
385 PGPKey text NULL,
3ffc5f4f 386 SMIMECertificate text NULL,
84fb5b46
MKG
387
388 Creator integer NOT NULL DEFAULT 0 ,
389 Created TIMESTAMP NULL ,
390 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
391 LastUpdated TIMESTAMP NULL ,
392 PRIMARY KEY (id)
393
394);
395
396
3ffc5f4f 397CREATE UNIQUE INDEX Users1 ON Users (LOWER(Name)) ;
84fb5b46
MKG
398CREATE INDEX Users4 ON Users (EmailAddress);
399
400
401
402
403
404
405
406
407--
408-- Sequences for table TICKETS
409--
410
411CREATE SEQUENCE tickets_id_seq;
412
413CREATE TABLE Tickets (
414 id INTEGER DEFAULT nextval('tickets_id_seq'),
415 EffectiveId integer NOT NULL DEFAULT 0 ,
3ffc5f4f 416 IsMerged smallint NULL DEFAULT NULL ,
84fb5b46
MKG
417 Queue integer NOT NULL DEFAULT 0 ,
418 Type varchar(16) NULL ,
419 IssueStatement integer NOT NULL DEFAULT 0 ,
420 Resolution integer NOT NULL DEFAULT 0 ,
421 Owner integer NOT NULL DEFAULT 0 ,
422 Subject varchar(200) NULL DEFAULT '[no subject]' ,
423 InitialPriority integer NOT NULL DEFAULT 0 ,
424 FinalPriority integer NOT NULL DEFAULT 0 ,
425 Priority integer NOT NULL DEFAULT 0 ,
426 TimeEstimated integer NOT NULL DEFAULT 0 ,
427 TimeWorked integer NOT NULL DEFAULT 0 ,
428 Status varchar(64) NULL ,
429 TimeLeft integer NOT NULL DEFAULT 0 ,
430 Told TIMESTAMP NULL ,
431 Starts TIMESTAMP NULL ,
432 Started TIMESTAMP NULL ,
433 Due TIMESTAMP NULL ,
434 Resolved TIMESTAMP NULL ,
435
436
437 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
438 LastUpdated TIMESTAMP NULL ,
439 Creator integer NOT NULL DEFAULT 0 ,
440 Created TIMESTAMP NULL ,
441 Disabled integer NOT NULL DEFAULT 0 ,
442 PRIMARY KEY (id)
443
444);
445
446CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
447CREATE INDEX Tickets2 ON Tickets (Owner) ;
448CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
84fb5b46
MKG
449
450
451--
452-- Sequences for table SCRIPACTIONS
453--
454
455CREATE SEQUENCE scripactions_id_seq;
456
457CREATE TABLE ScripActions (
458 id INTEGER DEFAULT nextval('scripactions_id_seq'),
459 Name varchar(200) NULL ,
460 Description varchar(255) NULL ,
461 ExecModule varchar(60) NULL ,
462 Argument varchar(255) NULL ,
463 Creator integer NOT NULL DEFAULT 0 ,
464 Created TIMESTAMP NULL ,
465 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
466 LastUpdated TIMESTAMP NULL ,
467 PRIMARY KEY (id)
468
469);
470
471
472
473
474
475
476
477--
478-- Sequences for table TEMPLATES
479--
480
481CREATE SEQUENCE templates_id_seq;
482
483CREATE TABLE Templates (
484 id INTEGER DEFAULT nextval('templates_id_seq'),
485 Queue integer NOT NULL DEFAULT 0 ,
486 Name varchar(200) NOT NULL ,
487 Description varchar(255) NULL ,
488 Type varchar(16) NULL ,
84fb5b46
MKG
489 Content text NULL ,
490 LastUpdated TIMESTAMP NULL ,
491 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
492 Creator integer NOT NULL DEFAULT 0 ,
493 Created TIMESTAMP NULL ,
494 PRIMARY KEY (id)
495
496);
497
498
499
500
501
502
503
504--
505-- Sequences for table TICKETCUSTOMFIELDVALUES
506--
507
508CREATE SEQUENCE objectcustomfieldvalues_id_s;
509
510CREATE TABLE ObjectCustomFieldValues (
511 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
512 CustomField int NOT NULL ,
513 ObjectType varchar(255) NULL ,
514 ObjectId int NOT NULL ,
515 SortOrder integer NOT NULL DEFAULT 0 ,
516
517 Content varchar(255) NULL ,
518 LargeContent text NULL,
519 ContentType varchar(80) NULL,
520 ContentEncoding varchar(80) NULL ,
521
522 Creator integer NOT NULL DEFAULT 0 ,
523 Created TIMESTAMP NULL ,
524 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
525 LastUpdated TIMESTAMP NULL ,
526 Disabled integer NOT NULL DEFAULT 0 ,
527 PRIMARY KEY (id)
528
529);
530
531CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
532CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
533
534
535
536
537
538
539
540--
541-- Sequences for table CUSTOMFIELDS
542--
543
544CREATE SEQUENCE customfields_id_seq;
545
546CREATE TABLE CustomFields (
547 id INTEGER DEFAULT nextval('customfields_id_seq'),
548 Name varchar(200) NULL ,
549 Type varchar(200) NULL ,
550 RenderType varchar(64) NULL ,
551 MaxValues integer NOT NULL DEFAULT 0 ,
84fb5b46
MKG
552 ValuesClass varchar(64) NULL ,
553 BasedOn integer NULL,
554 Pattern varchar(65536) NULL ,
555 LookupType varchar(255) NOT NULL ,
556 Description varchar(255) NULL ,
557 SortOrder integer NOT NULL DEFAULT 0 ,
558
559 Creator integer NOT NULL DEFAULT 0 ,
560 Created TIMESTAMP NULL ,
561 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
562 LastUpdated TIMESTAMP NULL ,
563 Disabled integer NOT NULL DEFAULT 0 ,
564 PRIMARY KEY (id)
565
566);
567
568
569
570
571
572CREATE SEQUENCE objectcustomfields_id_s;
573
574CREATE TABLE ObjectCustomFields (
575 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
576 CustomField integer NOT NULL,
577 ObjectId integer NOT NULL,
578 SortOrder integer NOT NULL DEFAULT 0 ,
579
580 Creator integer NOT NULL DEFAULT 0 ,
581 Created TIMESTAMP NULL ,
582 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
583 LastUpdated TIMESTAMP NULL ,
584 PRIMARY KEY (id)
585
586);
587
588
589
590
591
592
593
594--
595-- Sequences for table CUSTOMFIELDVALUES
596--
597
598CREATE SEQUENCE customfieldvalues_id_seq;
599
600CREATE TABLE CustomFieldValues (
601 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
602 CustomField int NOT NULL ,
603 Name varchar(200) NULL ,
604 Description varchar(255) NULL ,
605 SortOrder integer NOT NULL DEFAULT 0 ,
606 Category varchar(255) NULL ,
607
608 Creator integer NOT NULL DEFAULT 0 ,
609 Created TIMESTAMP NULL ,
610 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
611 LastUpdated TIMESTAMP NULL ,
612 PRIMARY KEY (id)
613
614);
615
616CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
617
618
619
620
621
622
623CREATE SEQUENCE attributes_id_seq;
624
625CREATE TABLE Attributes (
626 id INTEGER DEFAULT nextval('attributes_id_seq'),
627 Name varchar(255) NOT NULL ,
628 Description varchar(255) NULL ,
629 Content text,
630 ContentType varchar(16),
631 ObjectType varchar(64),
632 ObjectId integer,
633 Creator integer NOT NULL DEFAULT 0 ,
634 Created TIMESTAMP NULL ,
635 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
636 LastUpdated TIMESTAMP NULL ,
637 PRIMARY KEY (id)
638
639);
640
641CREATE INDEX Attributes1 on Attributes(Name);
642CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
643
644
645
646
647
648-- sessions is used by Apache::Session to keep sessions in the database.
649-- We should have a reaper script somewhere.
650
651CREATE TABLE sessions (
652 id char(32) NOT NULL,
653 a_session bytea,
654 LastUpdated TIMESTAMP not null default current_timestamp,
655 PRIMARY KEY (id)
656
657);
658
659
660CREATE TABLE Classes (
661id SERIAL,
662Name varchar(255) NOT NULL DEFAULT '',
663Description varchar(255) NOT NULL DEFAULT '',
664SortOrder integer NOT NULL DEFAULT 0,
665Disabled smallint NOT NULL DEFAULT 0,
666Creator integer NOT NULL DEFAULT 0,
667Created TIMESTAMP NULL,
668LastUpdatedBy integer NOT NULL DEFAULT 0,
669LastUpdated TIMESTAMP NULL,
670HotList smallint NOT NULL DEFAULT 0,
671PRIMARY KEY (id)
672);
673
674CREATE TABLE Articles (
675id SERIAL,
676Name varchar(255) NOT NULL DEFAULT '',
677Summary varchar(255) NOT NULL DEFAULT '',
678SortOrder integer NOT NULL DEFAULT 0,
679Class integer NOT NULL DEFAULT 0,
680Parent integer NOT NULL DEFAULT 0,
681URI varchar(255),
682Creator integer NOT NULL DEFAULT 0,
683Created TIMESTAMP NULL,
684LastUpdatedBy integer NOT NULL DEFAULT 0,
685LastUpdated TIMESTAMP NULL,
686PRIMARY KEY (id)
687);
688
689
690CREATE TABLE Topics (
691id SERIAL,
692Parent integer NOT NULL DEFAULT 0,
693Name varchar(255) NOT NULL DEFAULT '',
694Description varchar(255) NOT NULL DEFAULT '',
695ObjectType varchar(64) NOT NULL DEFAULT '',
696ObjectId integer NOT NULL,
697PRIMARY KEY (id)
698);
699
700
701CREATE TABLE ObjectTopics (
702id SERIAL,
703Topic integer NOT NULL,
704ObjectType varchar(64) NOT NULL DEFAULT '',
705ObjectId integer NOT NULL,
706PRIMARY KEY (id)
707);
708
709
710CREATE TABLE ObjectClasses (
711id SERIAL,
712Class integer NOT NULL,
713ObjectType varchar(255) NOT NULL DEFAULT '',
714ObjectId integer NOT NULL,
715Creator integer NOT NULL DEFAULT 0,
716Created TIMESTAMP NULL,
717LastUpdatedBy integer NOT NULL DEFAULT 0,
718LastUpdated TIMESTAMP NULL,
719PRIMARY KEY (id)
720);
721