]>
Commit | Line | Data |
---|---|---|
84fb5b46 MKG |
1 | ------------------------------------------------------------------ |
2 | -- My2Pg 1.23 translated dump | |
3 | -- | |
4 | ------------------------------------------------------------------ | |
5 | ||
6 | ||
7 | ||
8 | -- | |
9 | -- Sequences for table ATTACHMENTS | |
10 | -- | |
11 | ||
12 | CREATE SEQUENCE attachments_id_seq; | |
13 | ||
14 | ||
15 | ||
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 , | |
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 | ||
33 | CREATE INDEX Attachments1 ON Attachments (Parent) ; | |
34 | CREATE INDEX Attachments2 ON Attachments (TransactionId) ; | |
35 | CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ; | |
36 | ||
37 | ||
38 | ||
39 | ||
40 | ||
41 | -- | |
42 | -- Sequences for table QUEUES | |
43 | -- | |
44 | ||
45 | CREATE SEQUENCE queues_id_seq; | |
46 | ||
47 | CREATE 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 | 66 | CREATE UNIQUE INDEX Queues1 ON Queues (LOWER(Name)) ; |
84fb5b46 MKG |
67 | |
68 | ||
69 | ||
70 | ||
71 | ||
72 | ||
73 | ||
74 | -- | |
75 | -- Sequences for table LINKS | |
76 | -- | |
77 | ||
78 | CREATE SEQUENCE links_id_seq; | |
79 | ||
80 | CREATE 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 | ); | |
94 | CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ; | |
95 | CREATE INDEX Links4 ON Links(Type,LocalBase); | |
96 | ||
97 | ||
98 | ||
99 | ||
100 | ||
101 | ||
102 | ||
103 | -- | |
104 | -- Sequences for table PRINCIPALS | |
105 | -- | |
106 | ||
107 | CREATE SEQUENCE principals_id_seq; | |
108 | ||
109 | CREATE 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 | ||
118 | CREATE INDEX Principals2 ON Principals (ObjectId); | |
119 | ||
120 | ||
121 | ||
122 | ||
123 | ||
124 | ||
125 | ||
126 | ||
127 | -- | |
128 | -- Sequences for table GROUPS | |
129 | -- | |
130 | ||
131 | CREATE SEQUENCE groups_id_seq; | |
132 | ||
133 | CREATE 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 |
147 | CREATE INDEX Groups1 ON Groups (LOWER(Domain), LOWER(Type), Instance); |
148 | CREATE INDEX Groups2 ON Groups (LOWER(Domain), LOWER(Name), Instance); | |
149 | CREATE INDEX Groups3 On Groups (Instance); | |
84fb5b46 MKG |
150 | |
151 | ||
152 | ||
153 | ||
154 | ||
155 | ||
156 | ||
157 | ||
158 | -- | |
159 | -- Sequences for table SCRIPCONDITIONS | |
160 | -- | |
161 | ||
162 | CREATE SEQUENCE scripconditions_id_seq; | |
163 | ||
164 | CREATE 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 | ||
189 | CREATE SEQUENCE transactions_id_seq; | |
190 | ||
191 | CREATE 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 | ); | |
210 | CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId); | |
211 | ||
212 | ||
213 | ||
214 | ||
215 | ||
216 | ||
217 | ||
218 | -- | |
219 | -- Sequences for table SCRIPS | |
220 | -- | |
221 | ||
222 | CREATE SEQUENCE scrips_id_seq; | |
223 | ||
224 | CREATE 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 |
243 | CREATE SEQUENCE objectscrips_id_seq; |
244 | ||
245 | CREATE 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 | 260 | CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip); |
84fb5b46 MKG |
261 | |
262 | ||
263 | ||
264 | -- | |
265 | -- Sequences for table ACL | |
266 | -- | |
267 | ||
268 | CREATE SEQUENCE acl_id_seq; | |
269 | ||
270 | CREATE 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 | ||
286 | CREATE 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 | ||
299 | CREATE SEQUENCE groupmembers_id_seq; | |
300 | ||
301 | CREATE 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 | ||
313 | CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId); | |
314 | ||
315 | ||
316 | ||
317 | ||
318 | ||
319 | ||
320 | ||
321 | -- | |
322 | -- Sequences for table CACHEDGROUPMEMBERS | |
323 | -- | |
324 | ||
325 | CREATE SEQUENCE cachedgroupmembers_id_seq; | |
326 | ||
327 | CREATE 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 |
338 | CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled); |
339 | CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled); | |
340 | CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId,ImmediateParentId); | |
84fb5b46 MKG |
341 | |
342 | ||
343 | ||
344 | ||
345 | ||
346 | ||
347 | ||
348 | -- | |
349 | -- Sequences for table USERS | |
350 | -- | |
351 | ||
352 | CREATE SEQUENCE users_id_seq; | |
353 | ||
354 | CREATE 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 | 397 | CREATE UNIQUE INDEX Users1 ON Users (LOWER(Name)) ; |
84fb5b46 MKG |
398 | CREATE INDEX Users4 ON Users (EmailAddress); |
399 | ||
400 | ||
401 | ||
402 | ||
403 | ||
404 | ||
405 | ||
406 | ||
407 | -- | |
408 | -- Sequences for table TICKETS | |
409 | -- | |
410 | ||
411 | CREATE SEQUENCE tickets_id_seq; | |
412 | ||
413 | CREATE 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 | ||
446 | CREATE INDEX Tickets1 ON Tickets (Queue, Status) ; | |
447 | CREATE INDEX Tickets2 ON Tickets (Owner) ; | |
448 | CREATE INDEX Tickets3 ON Tickets (EffectiveId) ; | |
84fb5b46 MKG |
449 | |
450 | ||
451 | -- | |
452 | -- Sequences for table SCRIPACTIONS | |
453 | -- | |
454 | ||
455 | CREATE SEQUENCE scripactions_id_seq; | |
456 | ||
457 | CREATE 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 | ||
481 | CREATE SEQUENCE templates_id_seq; | |
482 | ||
483 | CREATE 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 | ||
508 | CREATE SEQUENCE objectcustomfieldvalues_id_s; | |
509 | ||
510 | CREATE 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 | ||
531 | CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content); | |
532 | CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); | |
533 | ||
534 | ||
535 | ||
536 | ||
537 | ||
538 | ||
539 | ||
540 | -- | |
541 | -- Sequences for table CUSTOMFIELDS | |
542 | -- | |
543 | ||
544 | CREATE SEQUENCE customfields_id_seq; | |
545 | ||
546 | CREATE 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 | ||
572 | CREATE SEQUENCE objectcustomfields_id_s; | |
573 | ||
574 | CREATE 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 | ||
598 | CREATE SEQUENCE customfieldvalues_id_seq; | |
599 | ||
600 | CREATE 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 | ||
616 | CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField); | |
617 | ||
618 | ||
619 | ||
620 | ||
621 | ||
622 | ||
623 | CREATE SEQUENCE attributes_id_seq; | |
624 | ||
625 | CREATE 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 | ||
641 | CREATE INDEX Attributes1 on Attributes(Name); | |
642 | CREATE 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 | ||
651 | CREATE 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 | ||
660 | CREATE TABLE Classes ( | |
661 | id SERIAL, | |
662 | Name varchar(255) NOT NULL DEFAULT '', | |
663 | Description varchar(255) NOT NULL DEFAULT '', | |
664 | SortOrder integer NOT NULL DEFAULT 0, | |
665 | Disabled smallint NOT NULL DEFAULT 0, | |
666 | Creator integer NOT NULL DEFAULT 0, | |
667 | Created TIMESTAMP NULL, | |
668 | LastUpdatedBy integer NOT NULL DEFAULT 0, | |
669 | LastUpdated TIMESTAMP NULL, | |
670 | HotList smallint NOT NULL DEFAULT 0, | |
671 | PRIMARY KEY (id) | |
672 | ); | |
673 | ||
674 | CREATE TABLE Articles ( | |
675 | id SERIAL, | |
676 | Name varchar(255) NOT NULL DEFAULT '', | |
677 | Summary varchar(255) NOT NULL DEFAULT '', | |
678 | SortOrder integer NOT NULL DEFAULT 0, | |
679 | Class integer NOT NULL DEFAULT 0, | |
680 | Parent integer NOT NULL DEFAULT 0, | |
681 | URI varchar(255), | |
682 | Creator integer NOT NULL DEFAULT 0, | |
683 | Created TIMESTAMP NULL, | |
684 | LastUpdatedBy integer NOT NULL DEFAULT 0, | |
685 | LastUpdated TIMESTAMP NULL, | |
686 | PRIMARY KEY (id) | |
687 | ); | |
688 | ||
689 | ||
690 | CREATE TABLE Topics ( | |
691 | id SERIAL, | |
692 | Parent integer NOT NULL DEFAULT 0, | |
693 | Name varchar(255) NOT NULL DEFAULT '', | |
694 | Description varchar(255) NOT NULL DEFAULT '', | |
695 | ObjectType varchar(64) NOT NULL DEFAULT '', | |
696 | ObjectId integer NOT NULL, | |
697 | PRIMARY KEY (id) | |
698 | ); | |
699 | ||
700 | ||
701 | CREATE TABLE ObjectTopics ( | |
702 | id SERIAL, | |
703 | Topic integer NOT NULL, | |
704 | ObjectType varchar(64) NOT NULL DEFAULT '', | |
705 | ObjectId integer NOT NULL, | |
706 | PRIMARY KEY (id) | |
707 | ); | |
708 | ||
709 | ||
710 | CREATE TABLE ObjectClasses ( | |
711 | id SERIAL, | |
712 | Class integer NOT NULL, | |
713 | ObjectType varchar(255) NOT NULL DEFAULT '', | |
714 | ObjectId integer NOT NULL, | |
715 | Creator integer NOT NULL DEFAULT 0, | |
716 | Created TIMESTAMP NULL, | |
717 | LastUpdatedBy integer NOT NULL DEFAULT 0, | |
718 | LastUpdated TIMESTAMP NULL, | |
719 | PRIMARY KEY (id) | |
720 | ); | |
721 |