1
CREATE GENERATOR CHANGE_NUM;
3
/* Domain definitions */
4
CREATE DOMAIN D_ACTION AS BLOB SUB_TYPE 0 SEGMENT SIZE 0;
5
CREATE DOMAIN CHANGE_DATE AS TIMESTAMP;
6
CREATE DOMAIN CHANGE_NUMBER AS INTEGER;
7
CREATE DOMAIN CHANGE_WHO AS VARCHAR(32);
8
CREATE DOMAIN CLASS AS CHAR(10);
9
CREATE DOMAIN DESCRIPTION AS BLOB SUB_TYPE 0 SEGMENT SIZE 80;
10
CREATE DOMAIN EXPLANATION AS BLOB SUB_TYPE 0 SEGMENT SIZE 0;
11
CREATE DOMAIN FACILITY AS CHAR(10);
12
CREATE DOMAIN FAC_CODE AS SMALLINT;
13
CREATE DOMAIN D_FILE AS VARCHAR(30);
14
CREATE DOMAIN FLAGS AS SMALLINT;
15
CREATE DOMAIN LANGUAGE AS VARCHAR(10);
16
CREATE DOMAIN LAST_CHANGE AS TIMESTAMP;
17
CREATE DOMAIN LOCALE AS VARCHAR(20);
18
CREATE DOMAIN MAX_NUMBER AS SMALLINT;
19
CREATE DOMAIN MODULE AS VARCHAR(32);
20
CREATE DOMAIN NUMBER AS SMALLINT;
21
CREATE DOMAIN OLD_SEQUENCE AS SMALLINT;
22
CREATE DOMAIN ROUTINE AS VARCHAR(32);
23
CREATE DOMAIN SEQUENCE AS SMALLINT;
24
CREATE DOMAIN SEVERITY AS SMALLINT;
25
CREATE DOMAIN SEVERITY_TEXT AS CHAR(7);
26
CREATE DOMAIN SQL_CLASS AS CHAR(2);
27
CREATE DOMAIN SQL_CODE AS SMALLINT;
28
CREATE DOMAIN SQL_SUBCLASS AS CHAR(3);
29
CREATE DOMAIN SYMBOL AS VARCHAR(32);
30
CREATE DOMAIN TEMPLATE AS BLOB SUB_TYPE 0 SEGMENT SIZE 256;
31
CREATE DOMAIN TEXT AS VARCHAR(118);
32
CREATE DOMAIN TRANSLATOR AS VARCHAR(32);
33
CREATE DOMAIN TRANS_DATE AS TIMESTAMP;
34
CREATE DOMAIN TRANS_NOTES AS BLOB SUB_TYPE 0 SEGMENT SIZE 0;
35
CREATE DOMAIN D_TYPE AS CHAR(12);
36
CREATE DOMAIN D_VALUE AS INTEGER;
37
CREATE DOMAIN VMS_CODE AS INTEGER;
43
CREATE TABLE FACILITIES (
44
LAST_CHANGE LAST_CHANGE,
45
FACILITY FACILITY NOT NULL CONSTRAINT FAC_2 UNIQUE,
46
FAC_CODE FAC_CODE NOT NULL CONSTRAINT FAC_1 PRIMARY KEY,
50
CREATE TABLE HISTORY (
51
CHANGE_NUMBER CHANGE_NUMBER NOT NULL CONSTRAINT HIS_1 PRIMARY KEY,
52
CHANGE_WHO CHANGE_WHO NOT NULL,
53
CHANGE_DATE CHANGE_DATE NOT NULL,
54
FAC_CODE FAC_CODE NOT NULL /*REFERENCES FACILITIES*/,
55
NUMBER NUMBER NOT NULL,
58
OLD_EXPLANATION EXPLANATION,
60
CODE COMPUTED BY (fac_code * 10000 + number)
63
CREATE TABLE LOCALES (
64
LOCALE LOCALE NOT NULL CONSTRAINT PRIMARY_LOCALES PRIMARY KEY,
65
DESCRIPTION DESCRIPTION
68
CREATE TABLE MESSAGES (
72
TRANS_NOTES TRANS_NOTES,
73
FAC_CODE FAC_CODE NOT NULL REFERENCES FACILITIES,
74
NUMBER NUMBER NOT NULL,
78
EXPLANATION EXPLANATION,
79
CODE COMPUTED BY (fac_code * 10000 + number),
80
CONSTRAINT MSG_1 UNIQUE(FAC_CODE, NUMBER)
83
CREATE TABLE SYMBOLS (
84
SYMBOL SYMBOL NOT NULL CONSTRAINT SYM2 PRIMARY KEY,
91
CREATE TABLE SYSTEM_ERRORS (
94
SQL_SUBCLASS SQL_SUBCLASS,
95
FAC_CODE FAC_CODE NOT NULL REFERENCES FACILITIES,
96
NUMBER NUMBER NOT NULL,
98
GDS_SYMBOL SYMBOL NOT NULL CONSTRAINT SYSERR2 UNIQUE,
100
SEVERITY_TEXT SEVERITY_TEXT,
101
CODE COMPUTED BY (FAC_CODE * 10000 + NUMBER),
102
SQL_STATE COMPUTED BY (SQL_CLASS || SQL_SUBCLASS),
103
CONSTRAINT SYSERR1 UNIQUE(FAC_CODE, NUMBER)
106
CREATE TABLE TEMPLATES (
114
CREATE TABLE TRANSMSGS (
117
ENG_EXPLANATION EXPLANATION,
118
FAC_CODE FAC_CODE NOT NULL REFERENCES FACILITIES,
119
NUMBER NUMBER NOT NULL,
120
LOCALE LOCALE NOT NULL CONSTRAINT UNKNOWN_LOCALE REFERENCES LOCALES,
123
EXPLANATION EXPLANATION,
124
TRANSLATOR TRANSLATOR,
125
TRANS_DATE TRANS_DATE,
126
CODE COMPUTED BY (fac_code * 10000 + number),
127
CONSTRAINT TMSG_1 UNIQUE(LOCALE, FAC_CODE, NUMBER)
132
/* Index definitions for all user tables */
133
CREATE INDEX SYM1 ON SYMBOLS(CLASS, "TYPE");
140
CREATE TRIGGER FACILITIES$STORE FOR FACILITIES
141
ACTIVE BEFORE INSERT POSITION 0 AS
143
new.last_change = CURRENT_TIMESTAMP;
146
CREATE TRIGGER FACILITIES$MODIFY FOR FACILITIES
147
ACTIVE BEFORE UPDATE POSITION 0 AS
149
new.last_change = CURRENT_TIMESTAMP;
152
CREATE TRIGGER MESSAGES$MODIFY FOR MESSAGES
153
ACTIVE AFTER UPDATE POSITION 0 AS
156
set last_change = current_timestamp
157
where fac_code = new.fac_code;
160
CREATE TRIGGER MSGS$MODIFY FOR MESSAGES
161
ACTIVE AFTER UPDATE POSITION 1 AS
163
if (new.text <> old.text) then
165
insert into history (change_number, change_date, change_who, fac_code,
166
number, old_text, old_action, old_explanation, locale)
167
values (gen_id(change_num, 1), current_timestamp, current_user,
168
old.fac_code, old.number, old.text, old."ACTION",
169
old.explanation, 'c_pg');
173
CREATE TRIGGER TRANSMSGS$STORE FOR TRANSMSGS
174
ACTIVE BEFORE INSERT POSITION 0 AS
176
if (new.translator is NULL) then
177
new.translator = current_user;
178
if (new.trans_date is NULL) then
179
new.trans_date = current_timestamp;
182
CREATE TRIGGER TRANSMSGS$MODIFY FOR TRANSMSGS
183
ACTIVE AFTER UPDATE POSITION 0 AS
185
if (new.text <> old.text) then
187
insert into history (change_number, change_date, change_who, fac_code,
188
number, old_text, old_action, old_explanation, locale)
189
values (gen_id(change_num, 1), current_timestamp, current_user,
190
old.fac_code, old.number, old.text, old."ACTION",
191
old.explanation, old.locale);
198
-- Grant permission to the general audience to read these tables.
199
GRANT SELECT ON FACILITIES TO PUBLIC;
200
GRANT SELECT ON HISTORY TO PUBLIC;
201
GRANT SELECT ON LOCALES TO PUBLIC;
202
GRANT SELECT ON MESSAGES TO PUBLIC;
203
GRANT SELECT ON SYMBOLS TO PUBLIC;
204
GRANT SELECT ON SYSTEM_ERRORS TO PUBLIC;
205
GRANT SELECT ON TEMPLATES TO PUBLIC;
206
GRANT SELECT ON TRANSMSGS TO PUBLIC;