Merge branch 'dev'
[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);
66CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
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);
147CREATE UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name);
148CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
149
150
151
152
153
154
155
156
157--
158-- Sequences for table SCRIPCONDITIONS
159--
160
161CREATE SEQUENCE scripconditions_id_seq;
162
163CREATE 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 ,
170
171 Creator integer NOT NULL DEFAULT 0 ,
172 Created TIMESTAMP NULL ,
173 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
174 LastUpdated TIMESTAMP NULL ,
175 PRIMARY KEY (id)
176
177);
178
179
180
181
182
183
184--
185-- Sequences for table TRANSACTIONS
186--
187
188CREATE SEQUENCE transactions_id_seq;
189
190CREATE 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 ,
203
204 Creator integer NOT NULL DEFAULT 0 ,
205 Created TIMESTAMP NULL ,
206 PRIMARY KEY (id)
207
208);
209CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
210
211
212
213
214
215
216
217--
218-- Sequences for table SCRIPS
219--
220
221CREATE SEQUENCE scrips_id_seq;
222
223CREATE 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 ,
240 PRIMARY KEY (id)
241
242);
243
244
245
246
247
248
249--
250-- Sequences for table ACL
251--
252
253CREATE SEQUENCE acl_id_seq;
254
255CREATE TABLE ACL (
256 id INTEGER DEFAULT nextval('acl_id_seq'),
257 PrincipalType varchar(25) NOT NULL,
258
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 Creator integer NOT NULL DEFAULT 0 ,
264 Created TIMESTAMP NULL ,
265 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
266 LastUpdated TIMESTAMP NULL ,
267 PRIMARY KEY (id)
268
269);
270
271CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
272
273
274
275
276
277
278
279
280--
281-- Sequences for table GROUPMEMBERS
282--
283
284CREATE SEQUENCE groupmembers_id_seq;
285
286CREATE TABLE GroupMembers (
287 id INTEGER DEFAULT nextval('groupmembers_id_seq'),
288 GroupId integer NOT NULL DEFAULT 0,
289 MemberId integer NOT NULL DEFAULT 0,
290 Creator integer NOT NULL DEFAULT 0 ,
291 Created TIMESTAMP NULL ,
292 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
293 LastUpdated TIMESTAMP NULL ,
294 PRIMARY KEY (id)
295
296);
297
298CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId);
299
300
301
302
303
304
305
306--
307-- Sequences for table CACHEDGROUPMEMBERS
308--
309
310CREATE SEQUENCE cachedgroupmembers_id_seq;
311
312CREATE TABLE CachedGroupMembers (
313 id int DEFAULT nextval('cachedgroupmembers_id_seq'),
314 GroupId int,
315 MemberId int,
316 Via int,
317 ImmediateParentId int,
318 Disabled integer NOT NULL DEFAULT 0 ,
319 PRIMARY KEY (id)
320
321);
322
323CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId);
324CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);
325CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
326
327
328
329
330
331
332
333--
334-- Sequences for table USERS
335--
336
337CREATE SEQUENCE users_id_seq;
338
339CREATE TABLE Users (
340 id INTEGER DEFAULT nextval('users_id_seq'),
341 Name varchar(200) NOT NULL ,
342 Password varchar(256) NULL ,
343 AuthToken varchar(16) NULL ,
344 Comments text NULL ,
345 Signature text NULL ,
346 EmailAddress varchar(120) NULL ,
347 FreeformContactInfo text NULL ,
348 Organization varchar(200) NULL ,
349 RealName varchar(120) NULL ,
350 NickName varchar(16) NULL ,
351 Lang varchar(16) NULL ,
352 EmailEncoding varchar(16) NULL ,
353 WebEncoding varchar(16) NULL ,
354 ExternalContactInfoId varchar(100) NULL ,
355 ContactInfoSystem varchar(30) NULL ,
356 ExternalAuthId varchar(100) NULL ,
357 AuthSystem varchar(30) NULL ,
358 Gecos varchar(16) NULL ,
359 HomePhone varchar(30) NULL ,
360 WorkPhone varchar(30) NULL ,
361 MobilePhone varchar(30) NULL ,
362 PagerPhone varchar(30) NULL ,
363 Address1 varchar(200) NULL ,
364 Address2 varchar(200) NULL ,
365 City varchar(100) NULL ,
366 State varchar(100) NULL ,
367 Zip varchar(16) NULL ,
368 Country varchar(50) NULL ,
369 Timezone varchar(50) NULL ,
370 PGPKey text NULL,
371
372 Creator integer NOT NULL DEFAULT 0 ,
373 Created TIMESTAMP NULL ,
374 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
375 LastUpdated TIMESTAMP NULL ,
376 PRIMARY KEY (id)
377
378);
379
380
381CREATE UNIQUE INDEX Users1 ON Users (Name) ;
382CREATE INDEX Users3 ON Users (id, EmailAddress);
383CREATE INDEX Users4 ON Users (EmailAddress);
384
385
386
387
388
389
390
391
392--
393-- Sequences for table TICKETS
394--
395
396CREATE SEQUENCE tickets_id_seq;
397
398CREATE TABLE Tickets (
399 id INTEGER DEFAULT nextval('tickets_id_seq'),
400 EffectiveId integer NOT NULL DEFAULT 0 ,
401 Queue integer NOT NULL DEFAULT 0 ,
402 Type varchar(16) NULL ,
403 IssueStatement integer NOT NULL DEFAULT 0 ,
404 Resolution integer NOT NULL DEFAULT 0 ,
405 Owner integer NOT NULL DEFAULT 0 ,
406 Subject varchar(200) NULL DEFAULT '[no subject]' ,
407 InitialPriority integer NOT NULL DEFAULT 0 ,
408 FinalPriority integer NOT NULL DEFAULT 0 ,
409 Priority integer NOT NULL DEFAULT 0 ,
410 TimeEstimated integer NOT NULL DEFAULT 0 ,
411 TimeWorked integer NOT NULL DEFAULT 0 ,
412 Status varchar(64) NULL ,
413 TimeLeft integer NOT NULL DEFAULT 0 ,
414 Told TIMESTAMP NULL ,
415 Starts TIMESTAMP NULL ,
416 Started TIMESTAMP NULL ,
417 Due TIMESTAMP NULL ,
418 Resolved TIMESTAMP NULL ,
419
420
421 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
422 LastUpdated TIMESTAMP NULL ,
423 Creator integer NOT NULL DEFAULT 0 ,
424 Created TIMESTAMP NULL ,
425 Disabled integer NOT NULL DEFAULT 0 ,
426 PRIMARY KEY (id)
427
428);
429
430CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
431CREATE INDEX Tickets2 ON Tickets (Owner) ;
432CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
433CREATE INDEX Tickets4 ON Tickets (id, Status) ;
434CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
435
436
437
438
439
440
441
442--
443-- Sequences for table SCRIPACTIONS
444--
445
446CREATE SEQUENCE scripactions_id_seq;
447
448CREATE TABLE ScripActions (
449 id INTEGER DEFAULT nextval('scripactions_id_seq'),
450 Name varchar(200) NULL ,
451 Description varchar(255) NULL ,
452 ExecModule varchar(60) NULL ,
453 Argument varchar(255) NULL ,
454 Creator integer NOT NULL DEFAULT 0 ,
455 Created TIMESTAMP NULL ,
456 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
457 LastUpdated TIMESTAMP NULL ,
458 PRIMARY KEY (id)
459
460);
461
462
463
464
465
466
467
468--
469-- Sequences for table TEMPLATES
470--
471
472CREATE SEQUENCE templates_id_seq;
473
474CREATE TABLE Templates (
475 id INTEGER DEFAULT nextval('templates_id_seq'),
476 Queue integer NOT NULL DEFAULT 0 ,
477 Name varchar(200) NOT NULL ,
478 Description varchar(255) NULL ,
479 Type varchar(16) NULL ,
480 Language varchar(16) NULL ,
481 TranslationOf integer NOT NULL DEFAULT 0 ,
482 Content text NULL ,
483 LastUpdated TIMESTAMP NULL ,
484 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
485 Creator integer NOT NULL DEFAULT 0 ,
486 Created TIMESTAMP NULL ,
487 PRIMARY KEY (id)
488
489);
490
491
492
493
494
495
496
497--
498-- Sequences for table TICKETCUSTOMFIELDVALUES
499--
500
501CREATE SEQUENCE objectcustomfieldvalues_id_s;
502
503CREATE TABLE ObjectCustomFieldValues (
504 id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_s'),
505 CustomField int NOT NULL ,
506 ObjectType varchar(255) NULL ,
507 ObjectId int NOT NULL ,
508 SortOrder integer NOT NULL DEFAULT 0 ,
509
510 Content varchar(255) NULL ,
511 LargeContent text NULL,
512 ContentType varchar(80) NULL,
513 ContentEncoding varchar(80) NULL ,
514
515 Creator integer NOT NULL DEFAULT 0 ,
516 Created TIMESTAMP NULL ,
517 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
518 LastUpdated TIMESTAMP NULL ,
519 Disabled integer NOT NULL DEFAULT 0 ,
520 PRIMARY KEY (id)
521
522);
523
524CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
525CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
526
527
528
529
530
531
532
533--
534-- Sequences for table CUSTOMFIELDS
535--
536
537CREATE SEQUENCE customfields_id_seq;
538
539CREATE TABLE CustomFields (
540 id INTEGER DEFAULT nextval('customfields_id_seq'),
541 Name varchar(200) NULL ,
542 Type varchar(200) NULL ,
543 RenderType varchar(64) NULL ,
544 MaxValues integer NOT NULL DEFAULT 0 ,
545 Repeated integer NOT NULL DEFAULT 0 ,
546 ValuesClass varchar(64) NULL ,
547 BasedOn integer NULL,
548 Pattern varchar(65536) NULL ,
549 LookupType varchar(255) NOT NULL ,
550 Description varchar(255) NULL ,
551 SortOrder integer NOT NULL DEFAULT 0 ,
552
553 Creator integer NOT NULL DEFAULT 0 ,
554 Created TIMESTAMP NULL ,
555 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
556 LastUpdated TIMESTAMP NULL ,
557 Disabled integer NOT NULL DEFAULT 0 ,
558 PRIMARY KEY (id)
559
560);
561
562
563
564
565
566CREATE SEQUENCE objectcustomfields_id_s;
567
568CREATE TABLE ObjectCustomFields (
569 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
570 CustomField integer NOT NULL,
571 ObjectId integer NOT NULL,
572 SortOrder integer NOT NULL DEFAULT 0 ,
573
574 Creator integer NOT NULL DEFAULT 0 ,
575 Created TIMESTAMP NULL ,
576 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
577 LastUpdated TIMESTAMP NULL ,
578 PRIMARY KEY (id)
579
580);
581
582
583
584
585
586
587
588--
589-- Sequences for table CUSTOMFIELDVALUES
590--
591
592CREATE SEQUENCE customfieldvalues_id_seq;
593
594CREATE TABLE CustomFieldValues (
595 id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
596 CustomField int NOT NULL ,
597 Name varchar(200) NULL ,
598 Description varchar(255) NULL ,
599 SortOrder integer NOT NULL DEFAULT 0 ,
600 Category varchar(255) NULL ,
601
602 Creator integer NOT NULL DEFAULT 0 ,
603 Created TIMESTAMP NULL ,
604 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
605 LastUpdated TIMESTAMP NULL ,
606 PRIMARY KEY (id)
607
608);
609
610CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
611
612
613
614
615
616
617CREATE SEQUENCE attributes_id_seq;
618
619CREATE TABLE Attributes (
620 id INTEGER DEFAULT nextval('attributes_id_seq'),
621 Name varchar(255) NOT NULL ,
622 Description varchar(255) NULL ,
623 Content text,
624 ContentType varchar(16),
625 ObjectType varchar(64),
626 ObjectId integer,
627 Creator integer NOT NULL DEFAULT 0 ,
628 Created TIMESTAMP NULL ,
629 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
630 LastUpdated TIMESTAMP NULL ,
631 PRIMARY KEY (id)
632
633);
634
635CREATE INDEX Attributes1 on Attributes(Name);
636CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
637
638
639
640
641
642-- sessions is used by Apache::Session to keep sessions in the database.
643-- We should have a reaper script somewhere.
644
645CREATE TABLE sessions (
646 id char(32) NOT NULL,
647 a_session bytea,
648 LastUpdated TIMESTAMP not null default current_timestamp,
649 PRIMARY KEY (id)
650
651);
652
653
654CREATE TABLE Classes (
655id SERIAL,
656Name varchar(255) NOT NULL DEFAULT '',
657Description varchar(255) NOT NULL DEFAULT '',
658SortOrder integer NOT NULL DEFAULT 0,
659Disabled smallint NOT NULL DEFAULT 0,
660Creator integer NOT NULL DEFAULT 0,
661Created TIMESTAMP NULL,
662LastUpdatedBy integer NOT NULL DEFAULT 0,
663LastUpdated TIMESTAMP NULL,
664HotList smallint NOT NULL DEFAULT 0,
665PRIMARY KEY (id)
666);
667
668CREATE TABLE Articles (
669id SERIAL,
670Name varchar(255) NOT NULL DEFAULT '',
671Summary varchar(255) NOT NULL DEFAULT '',
672SortOrder integer NOT NULL DEFAULT 0,
673Class integer NOT NULL DEFAULT 0,
674Parent integer NOT NULL DEFAULT 0,
675URI varchar(255),
676Creator integer NOT NULL DEFAULT 0,
677Created TIMESTAMP NULL,
678LastUpdatedBy integer NOT NULL DEFAULT 0,
679LastUpdated TIMESTAMP NULL,
680PRIMARY KEY (id)
681);
682
683
684CREATE TABLE Topics (
685id SERIAL,
686Parent integer NOT NULL DEFAULT 0,
687Name varchar(255) NOT NULL DEFAULT '',
688Description varchar(255) NOT NULL DEFAULT '',
689ObjectType varchar(64) NOT NULL DEFAULT '',
690ObjectId integer NOT NULL,
691PRIMARY KEY (id)
692);
693
694
695CREATE TABLE ObjectTopics (
696id SERIAL,
697Topic integer NOT NULL,
698ObjectType varchar(64) NOT NULL DEFAULT '',
699ObjectId integer NOT NULL,
700PRIMARY KEY (id)
701);
702
703
704CREATE TABLE ObjectClasses (
705id SERIAL,
706Class integer NOT NULL,
707ObjectType varchar(255) NOT NULL DEFAULT '',
708ObjectId integer NOT NULL,
709Creator integer NOT NULL DEFAULT 0,
710Created TIMESTAMP NULL,
711LastUpdatedBy integer NOT NULL DEFAULT 0,
712LastUpdated TIMESTAMP NULL,
713PRIMARY KEY (id)
714);
715