/
update-5.26-psql.sql
257 lines (211 loc) · 9.71 KB
/
update-5.26-psql.sql
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
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
-- part 1: can be applied on archive running archive 5.25
alter table series
add receiving_hl7_app varchar(255),
add receiving_hl7_facility varchar(255),
add sending_hl7_app varchar(255),
add sending_hl7_facility varchar(255);
alter table mpps
add accno_entity_id varchar(255),
add accno_entity_uid varchar(255),
add accno_entity_uid_type varchar(255);
alter table mwl_item
add accno_entity_id varchar(255),
add accno_entity_uid varchar(255),
add accno_entity_uid_type varchar(255),
add admid_entity_id varchar(255),
add admid_entity_uid varchar(255),
add admid_entity_uid_type varchar(255);
alter table patient_id
add entity_id varchar(255),
add entity_uid varchar(255),
add entity_uid_type varchar(255);
alter table series_req
add accno_entity_id varchar(255),
add accno_entity_uid varchar(255),
add accno_entity_uid_type varchar(255);
alter table study
add accno_entity_id varchar(255),
add accno_entity_uid varchar(255),
add accno_entity_uid_type varchar(255),
add admid_entity_id varchar(255),
add admid_entity_uid varchar(255),
add admid_entity_uid_type varchar(255);
alter table ups
add admid_entity_id varchar(255),
add admid_entity_uid varchar(255),
add admid_entity_uid_type varchar(255);
alter table ups_req
add accno_entity_id varchar(255),
add accno_entity_uid varchar(255),
add accno_entity_uid_type varchar(255);
create table rel_ups_station_class_code (ups_fk int8 not null, station_class_code_fk int8 not null);
create table rel_ups_station_location_code (ups_fk int8 not null, station_location_code_fk int8 not null);
create table rel_ups_station_name_code (ups_fk int8 not null, station_name_code_fk int8 not null);
alter table rel_ups_station_class_code
add constraint FK_q26e06qk9gwviwe2ug0f86doa foreign key (station_class_code_fk) references code;
alter table rel_ups_station_class_code
add constraint FK_e1ioaswm010jlsq6kl7y3um1c foreign key (ups_fk) references ups;
alter table rel_ups_station_location_code
add constraint FK_kl60ab0k5c1p8qii9ya16424x foreign key (station_location_code_fk) references code;
alter table rel_ups_station_location_code
add constraint FK_9f0l4glqwpq12d11w9osd475m foreign key (ups_fk) references ups;
alter table rel_ups_station_name_code
add constraint FK_jtv4r8f88f6gfte0fa36w5y9o foreign key (station_name_code_fk) references code;
alter table rel_ups_station_name_code
add constraint FK_8jf5xe8ot2yammv3ksd5xrgif foreign key (ups_fk) references ups;
alter table hl7psu_task
add pps_status int4;
alter table hl7psu_task
drop constraint UK_p5fraoqdbaywmlyumaeo16t56;
alter table hl7psu_task
add constraint UK_1t3jge4o2fl1byp3y8ljmkb3m unique (study_iuid, pps_status);
update patient_id
set (entity_id, entity_uid, entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where issuer_fk = issuer.pk)
where issuer_fk is not null;
update mpps
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null;
update mwl_item
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null;
update mwl_item
set (admid_entity_id, admid_entity_uid, admid_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where admid_issuer_fk = issuer.pk)
where admid_issuer_fk is not null;
update series_req
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null;
update study
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null;
update study
set (admid_entity_id, admid_entity_uid, admid_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where admid_issuer_fk = issuer.pk)
where admid_issuer_fk is not null;
update ups
set (admid_entity_id, admid_entity_uid, admid_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where ups.admission_issuer_fk = issuer.pk)
where admission_issuer_fk is not null;
update ups_req
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null;
create index UK_ffpftwfkijejj09tlbxr7u5g8 on series (sending_hl7_app);
create index UK_1e4aqxc5w1557hr3fb3lqm2qb on series (sending_hl7_facility);
create index UK_gj0bxgi55bhjic9s3i4dp2aee on series (receiving_hl7_app);
create index UK_pbay159cdhwbtjvlmel6d6em2 on series (receiving_hl7_facility);
create index UK_tkyjkkxxhnr0fem7m0h3844jk on patient_id (pat_id);
create index UK_d1sdyupb0vwvx23jownjnyy72 on patient_id (entity_id);
create index UK_m2jq6xe87vegohf6g10t5ptew on patient_id (entity_uid, entity_uid_type);
create index FK_q26e06qk9gwviwe2ug0f86doa on rel_ups_station_class_code (station_class_code_fk) ;
create index FK_e1ioaswm010jlsq6kl7y3um1c on rel_ups_station_class_code (ups_fk) ;
create index FK_kl60ab0k5c1p8qii9ya16424x on rel_ups_station_location_code (station_location_code_fk) ;
create index FK_9f0l4glqwpq12d11w9osd475m on rel_ups_station_location_code (ups_fk) ;
create index FK_jtv4r8f88f6gfte0fa36w5y9o on rel_ups_station_name_code (station_name_code_fk) ;
create index FK_8jf5xe8ot2yammv3ksd5xrgif on rel_ups_station_name_code (ups_fk) ;
-- part 2: shall be applied on stopped archive before starting 5.26
update patient_id
set (entity_id, entity_uid, entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where issuer_fk = issuer.pk)
where issuer_fk is not null
and entity_id is null
and entity_uid is null;
insert into rel_ups_station_name_code
select ups.pk, ups.station_name_fk from ups where ups.station_name_fk is not null;
insert into rel_ups_station_class_code
select ups.pk, ups.station_class_fk from ups where ups.station_class_fk is not null;
insert into rel_ups_station_location_code
select ups.pk, ups.station_location_fk from ups where ups.station_location_fk is not null;
update mpps
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null
and accno_entity_id is null
and accno_entity_uid is null;
update mwl_item
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null
and accno_entity_id is null
and accno_entity_uid is null;
update mwl_item
set (admid_entity_id, admid_entity_uid, admid_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where admid_issuer_fk = issuer.pk)
where admid_issuer_fk is not null
and admid_entity_id is null
and admid_entity_uid is null;
update series_req
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null
and accno_entity_id is null
and accno_entity_uid is null;
update study
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null
and accno_entity_id is null
and accno_entity_uid is null;
update study
set (admid_entity_id, admid_entity_uid, admid_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where admid_issuer_fk = issuer.pk)
where admid_issuer_fk is not null
and admid_entity_id is null
and admid_entity_uid is null;
update ups
set (admid_entity_id, admid_entity_uid, admid_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where ups.admission_issuer_fk = issuer.pk)
where admission_issuer_fk is not null
and admid_entity_id is null
and admid_entity_uid is null;
update ups_req
set (accno_entity_id, accno_entity_uid, accno_entity_uid_type) =
(select issuer.entity_id, issuer.entity_uid, issuer.entity_uid_type
from issuer where accno_issuer_fk = issuer.pk)
where accno_issuer_fk is not null
and accno_entity_id is null
and accno_entity_uid is null;
-- part 3: can be applied on already running archive 5.26
alter table mpps
drop accno_issuer_fk;
alter table mwl_item
drop accno_issuer_fk,
drop admid_issuer_fk;
alter table patient_id
drop issuer_fk;
alter table series_req
drop accno_issuer_fk;
alter table study
drop accno_issuer_fk,
drop admid_issuer_fk;
alter table ups
drop admission_issuer_fk,
drop station_name_fk,
drop station_class_fk,
drop station_location_fk;
alter table ups_req
drop accno_issuer_fk;
drop table issuer;
drop sequence issuer_pk_seq;