Skip to main content
summaryrefslogtreecommitdiffstats
blob: 19e652b11576e45ca2f5fd2f95558bcafbe37d27 (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
--
-- Tables for AppDB
--
CREATE TABLE OPENK_EISMAN_SEQ_TABLE
(
  SEQ_NAME  VARCHAR(32),
  SEQ_COUNT INTEGER
)
WITH (OIDS =FALSE
);
ALTER TABLE OPENK_EISMAN_SEQ_TABLE OWNER TO eisman;
-- Initialise Sequence Table
INSERT INTO OPENK_EISMAN_SEQ_TABLE (seq_name, seq_count) VALUES ('ACTIVITY_SEQ', 1);
INSERT INTO OPENK_EISMAN_SEQ_TABLE (seq_name, seq_count) VALUES ('PRESELECTION_SEQ', 1);
INSERT INTO OPENK_EISMAN_SEQ_TABLE (seq_name, seq_count) VALUES ('SYNCHRONOUS_MACHINE_SEQ', 1);
INSERT INTO OPENK_EISMAN_SEQ_TABLE (seq_name, seq_count) VALUES ('GENERATING_UNIT_SEQ', 1);
INSERT INTO OPENK_EISMAN_SEQ_TABLE (seq_name, seq_count) VALUES ('EQUIPMENT_CONTAINER_SEQ', 1);

CREATE TABLE PRESELECTION
(
  ID                             INTEGER NOT NULL,
  NAME                           VARCHAR(64),
  REDUCTION_SETTING_PER_CENT     INTEGER,
  DISCRIMINATION_COEFF_ENABLED   VARCHAR(5),
  CHARACTERISTIC_MISSING_FEEDIN  VARCHAR(32),
  SUBSTITUTE_VALUE_WIND_PER_CENT INTEGER,
  SUBSTITUTE_VALUE_PHOT_PER_CENT INTEGER,
  SUBSTITUTE_VALUE_BIOG_PER_CENT INTEGER,
  SECURITY_REDUCTION_PER_CENT    INTEGER,
  PROVIDE_SERVICE_DATE           VARCHAR(5),
  DATE_CREATED                   TIMESTAMP,
  CREATED_BY                     VARCHAR(64),
  DATE_UPDATED                   TIMESTAMP,
  UPDATED_BY                     VARCHAR(64),
  CONSTRAINT "PK_PRESELECTION" PRIMARY KEY (ID)
)
WITH (OIDS =FALSE
);
ALTER TABLE PRESELECTION OWNER TO eisman;

CREATE TABLE ACTIVITY
(
  ID                       INTEGER NOT NULL,
  REASON_REDUCTION         VARCHAR(64),
  DATE_STARTED             TIMESTAMP,
  DATE_FINISHED            TIMESTAMP,
  POWER_REDUCED_VALUE      NUMERIC,
  POWER_REDUCED_MULTIPLIER VARCHAR(12),
  POWER_REDUCED_UNIT       VARCHAR(12),
  PRACTICE                 VARCHAR(5),
  MOMENT_INFO_FEEDIN       VARCHAR(8),
  INFO_FEEDIN              VARCHAR(1024),
  MOMENT_INFO_DSO          VARCHAR(8),
  INFO_DSO                 VARCHAR(1024),
  DATE_CREATED             TIMESTAMP,
  CREATED_BY               VARCHAR(64),
  DATE_UPDATED             TIMESTAMP,
  UPDATED_BY               VARCHAR(64),
  ACTIVITY_ID              INTEGER,
  PRESELECTION_ID          INTEGER,
  CONSTRAINT "PK_ACTIVITY" PRIMARY KEY (ID),
  CONSTRAINT "FK_ACTIVITY_ACTIVITY" FOREIGN KEY (ACTIVITY_ID) REFERENCES ACTIVITY (ID) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK_ACTIVITY_PRESELECTION" FOREIGN KEY (PRESELECTION_ID) REFERENCES PRESELECTION (ID) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS =FALSE
);
ALTER TABLE ACTIVITY OWNER TO eisman;

CREATE TABLE EQUIPMENT_CONTAINER
(
  ID                                 INTEGER     NOT NULL,
  MRID                               VARCHAR(16) NOT NULL UNIQUE ,
  NAME                               VARCHAR(64),
  HIGH_VOLTAGE_LIMIT_VALUE           NUMERIC,
  HIGH_VOLTAGE_LIMIT_UNIT_MULTIPLIER VARCHAR(12),
  HIGH_VOLTAGE_LIMIT_UNIT_SYMBOL     VARCHAR(12),
  EQUIPMENT_CONTAINER_TYPE           VARCHAR(32),
  EQUIPMENT_CONTAINER_ID             INTEGER,
  ACTIVITY_ID                        INTEGER,
  --  SUBGEOGRAPHICAL_REGION_ID          INTEGER,
  CONSTRAINT "PK_EQUIPMENT_CONTAINER" PRIMARY KEY (ID),
  --  CONSTRAINT "FK_EQUIPMENT_CONTAINER_SUBGEOGRAPHICAL_REGION" FOREIGN KEY (SUBGEOGRAPHICAL_REGION_ID) REFERENCES SUBGEOGRAPHICAL_REGION (ID) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK_EQUIPMENT_CONTAINER_ACTIVITY" FOREIGN KEY (ACTIVITY_ID) REFERENCES ACTIVITY (ID) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS =FALSE
);
ALTER TABLE EQUIPMENT_CONTAINER OWNER TO eisman;

CREATE TABLE SUBGEOGRAPHICAL_REGION
(
  ID          INTEGER NOT NULL,
  MRID        VARCHAR(16) UNIQUE ,
  NAME        VARCHAR(64),
  ACTIVITY_ID INTEGER,
  --  GEOGRAPHICAL_REGION_ID INTEGER,
  CONSTRAINT "PK_SUBGEOGRAPHICAL_REGION" PRIMARY KEY (ID),
  CONSTRAINT "FK_SUBGEOGRAPHICAL_REGION_ACTIVITY" FOREIGN KEY (ACTIVITY_ID) REFERENCES ACTIVITY (ID) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS =FALSE
);
ALTER TABLE SUBGEOGRAPHICAL_REGION OWNER TO eisman;

CREATE TABLE LOCATION
(
  ID            INTEGER NOT NULL,
  NAME          VARCHAR(64),
  STREET_NAME   VARCHAR(64),
  STREET_NUMBER VARCHAR(12),
  POSTAL_CODE   VARCHAR(12),
  CITY          VARCHAR(64),
  COUNTRY       VARCHAR(64),
  EMAIL         VARCHAR(64),
  CONSTRAINT "PK_LOCATION" PRIMARY KEY (ID)
)
WITH (OIDS =FALSE
);
ALTER TABLE LOCATION OWNER TO eisman;

CREATE TABLE SYNCHRONOUS_MACHINE
(
  ID                                 INTEGER     NOT NULL,
  MRID                               VARCHAR(16) NOT NULL,
  NAME                               VARCHAR(64) UNIQUE ,
  NORMALLY_IN_SERVICE                VARCHAR(5),
  SYNCHRONOUS_MACHINE_KIND           VARCHAR(32),
  CURRENT_REDUCTION_SETTING_PER_CENT INTEGER,
  FEED_IN_RANKING                    INTEGER,
  FEED_IN_PRIORITY                   INTEGER,
  COMMUNICATION_TYPE                 VARCHAR(12),
  COMMUNICATION_GROUP                INTEGER,
  REFERENCE_SYNCHRONOUS_MACHINE      VARCHAR(5),
  SYNCHRONOUS_MACHINE_ID             INTEGER,
  --  GENERATING_UNIT_ID            INTEGER,
  LOCATION_ID                   INTEGER,
  EQUIPMENT_CONTAINER_ID             INTEGER,
  CONSTRAINT "PK_SYNCHRONOUS_MACHINE_ID" PRIMARY KEY (ID),
  CONSTRAINT "FK_SYNCHRONOUS_MACHINE_SYNCHRONOUS_MACHINE" FOREIGN KEY (SYNCHRONOUS_MACHINE_ID) REFERENCES SYNCHRONOUS_MACHINE (ID) ON UPDATE NO ACTION ON DELETE NO ACTION,
  --  CONSTRAINT "FK_SYNCHRONOUS_MACHINE_GENERATING_UNIT" FOREIGN KEY (GENERATING_UNIT_ID) REFERENCES GENERATING_UNIT (ID) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK_SYNCHRONOUS_MACHINE_LOCATION" FOREIGN KEY (LOCATION_ID) REFERENCES LOCATION (ID) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK_SYNCHRONOUS_MACHINE_EQUIPMENT_CONTAINER" FOREIGN KEY (EQUIPMENT_CONTAINER_ID) REFERENCES EQUIPMENT_CONTAINER (ID) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS =FALSE
);
ALTER TABLE SYNCHRONOUS_MACHINE OWNER TO eisman;

CREATE TABLE REDUCTION_SETTING
(
  ID                         INTEGER NOT NULL,
  REDUCTION_SETTING_PER_CENT INTEGER,
  SYNCHRONOUS_MACHINE_ID     INTEGER,
  CONSTRAINT "PK_REDUCTION_SETTING_ID" PRIMARY KEY (ID),
  CONSTRAINT "FK_REDUCTION_SETTING_SYNCHRONOUS_MACHINE" FOREIGN KEY (SYNCHRONOUS_MACHINE_ID) REFERENCES SYNCHRONOUS_MACHINE (ID) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS =FALSE
);
ALTER TABLE REDUCTION_SETTING OWNER TO eisman;

/*
CREATE TABLE GENERATING_UNIT
(
  ID                                  INTEGER NOT NULL,
  HIGH_CONTROL_LIMIT_VALUE            NUMERIC,
  HIGH_CONTROL_LIMIT_UNIT_MULTIPLIER  VARCHAR(12),
  HIGH_CONTROL_LIMIT_UNIT_SYMBOL      VARCHAR(12),
  MAX_OPERATING_POWER_LIMIT_VALUE     NUMERIC,
  MAX_OPERATING_POWER_UNIT_MULTIPLIER VARCHAR(12),
  MAX_OPERATING_POWER_UNIT_SYMBOL     VARCHAR(12),
  CONTROL_SOURCE                      VARCHAR(24),
  UNIT_TYPE                           VARCHAR(12),
  CONSTRAINT "PK_GENERATING_UNIT_ID" PRIMARY KEY (ID)
)
WITH (OIDS =FALSE
);
ALTER TABLE GENERATING_UNIT OWNER TO cachedb;
*/

CREATE TABLE MEASUREMENT_ANALOG
(
  ID                          INTEGER NOT NULL,
  MRID                        VARCHAR(16) UNIQUE ,
  MEASUREMENT_TYPE            VARCHAR(16),
  MEASUREMENT_UNIT_MULTIPLIER VARCHAR(12),
  MEASUREMENT_UNIT_SYMBOL     VARCHAR(12),
  MIN_VALUE                   NUMERIC,
  MAX_VALUE                   NUMERIC,
  NORMAL_VALUE                NUMERIC,
  SYNCHRONOUS_MACHINE_ID      INTEGER,
  CONSTRAINT "PK_MEASUREMENT_ANALOG" PRIMARY KEY (ID),
  CONSTRAINT "FK_MEASUREMENT_ANALOG_SYNCHRONOUS_MACHINE" FOREIGN KEY (SYNCHRONOUS_MACHINE_ID) REFERENCES SYNCHRONOUS_MACHINE (ID) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS =FALSE
);
ALTER TABLE MEASUREMENT_ANALOG OWNER TO eisman;

CREATE TABLE MEASUREMENT_ANALOG_VALUE
(
  ID                    INTEGER NOT NULL,
  TIMESTAMP_VALUE       VARCHAR(32),
  VALUE                 NUMERIC,
  MEASUREMENT_ANALOG_ID INTEGER,
  CONSTRAINT "PK_MEASUREMENT_ANALOG_VALUE" PRIMARY KEY (ID),
  CONSTRAINT "FK_MEASUREMENT_ANALOG_VALUE_MEASUREMENT_ANALOG" FOREIGN KEY (MEASUREMENT_ANALOG_ID) REFERENCES MEASUREMENT_ANALOG (ID) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS =FALSE
);
ALTER TABLE MEASUREMENT_ANALOG_VALUE OWNER TO eisman;

CREATE TABLE PSR_EVENT
(
  ID                     INTEGER NOT NULL,
  MRID                   VARCHAR(16) UNIQUE ,
  PSR_EVENT_KIND         VARCHAR(16),
  TIMESTAMP_CREATED      VARCHAR(30),
  SYNCHRONOUS_MACHINE_ID INTEGER,
  CONSTRAINT "PK_PSR_EVENT" PRIMARY KEY (ID),
  CONSTRAINT "FK_PSR_EVENT_SYNCHRONOUS_MACHINE" FOREIGN KEY (SYNCHRONOUS_MACHINE_ID) REFERENCES SYNCHRONOUS_MACHINE (ID) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS =FALSE
);
ALTER TABLE PSR_EVENT OWNER TO eisman;



Back to the top