· 8 years ago · May 07, 2018, 04:52 PM
1
2 
3BEGIN
4 PERFORM VOCABULARY_PACK.SetLatestUpdate(
5 pVocabularyName => 'PPI',
6 pVocabularyDate => TO_DATE ('2018-05-06' ,'yyyy-mm-dd') ,
7 pVocabularyVersion => 'Codebook Version 0.2.87',
8 pVocabularyDevSchema => 'DEV_PPI'
9)
10
11END $_$;
12;
13
14
15
16CREATE OR REPLACE FUNCTION devv5.genericupdate (
17)
18RETURNS void AS
19$body$
20BEGIN
21 -- Prerequisites:
22 ---- Check stage tables for incorrect rows [CURRENTLY NOT WORKING FOR SNOMED!]
23 /*DO $_$
24 BEGIN
25 PERFORM QA_TESTS.Check_Stage_Tables();
26 END $_$;*/
27
28 -- Update concept_id in concept_stage from concept for existing concepts
29 UPDATE concept_stage cs
30 SET concept_id = c.concept_id
31 FROM concept c
32 WHERE cs.concept_code = c.concept_code
33 AND cs.vocabulary_id = c.vocabulary_id;
34
35 -- ANALYSING
36 ANALYSE concept_stage;
37 ANALYSE concept_relationship_stage;
38 ANALYSE concept_synonym_stage;
39
40 -- 1. clearing the concept_name
41
42 --remove double spaces, carriage return, newline, vertical tab and form feed
43 UPDATE concept_stage
44 SET concept_name = REGEXP_REPLACE(concept_name, '[[:cntrl:]]+', ' ')
45 WHERE concept_name ~ '[[:cntrl:]]';
46
47 UPDATE concept_stage
48 SET concept_name = REGEXP_REPLACE(concept_name, ' {2,}', ' ')
49 WHERE concept_name ~ ' {2,}';
50
51 --remove leading and trailing spaces
52 UPDATE concept_stage
53 SET concept_name = TRIM(concept_name)
54 WHERE concept_name <> TRIM(concept_name);
55
56 --remove long dashes
57 UPDATE concept_stage
58 SET concept_name = REPLACE(concept_name, '–', '-')
59 WHERE concept_name LIKE '%–%';
60
61 /***************************
62 * Update the concept table *
63 ****************************/
64
65 -- 2. Update existing concept details from concept_stage.
66 -- All fields (concept_name, domain_id, concept_class_id, standard_concept, valid_start_date, valid_end_date, invalid_reason) are updated
67 -- with the exception of vocabulary_id (already there), concept_id (already there) and invalid_reason (below).
68
69 UPDATE concept c
70 SET concept_name = cs.concept_name,
71 domain_id = cs.domain_id,
72 concept_class_id = cs.concept_class_id,
73 standard_concept = cs.standard_concept,
74 valid_start_date = CASE -- if we have a real date in concept_stage, use it. If it is only the release date, use the existing
75 WHEN cs.valid_start_date = v.latest_update
76 THEN c.valid_start_date
77 ELSE cs.valid_start_date
78 END,
79 valid_end_date = cs.valid_end_date,
80 invalid_reason = cs.invalid_reason -- invalid_reason might be set below based on the valid_end_date
81 FROM concept_stage cs,
82 vocabulary v
83 WHERE c.concept_id = cs.concept_id -- concept exists in both, meaning, is not new. But information might be new
84 AND v.vocabulary_id = cs.vocabulary_id;
85
86 -- 3. Deprecate concepts missing from concept_stage and are not already deprecated.
87 -- This only works for vocabularies where we expect a full set of active concepts in concept_stage.
88 -- If the vocabulary only provides changed concepts, this should not be run, and the update information is already dealt with in step 1.
89
90 UPDATE concept c SET
91 invalid_reason = 'D',
92 valid_end_date = (SELECT latest_update-1 FROM vocabulary WHERE vocabulary_id = c.vocabulary_id)
93 WHERE NOT EXISTS (SELECT 1 FROM concept_stage cs WHERE cs.concept_id = c.concept_id AND cs.vocabulary_id = c.vocabulary_id) -- if concept missing from concept_stage
94 AND c.vocabulary_id IN (SELECT vocabulary_id FROM vocabulary WHERE latest_update IS NOT NULL) -- only for current vocabularies
95 AND c.invalid_reason IS NULL -- not already deprecated
96 AND CASE -- all vocabularies that give us a full list of active concepts at each release we can safely assume to deprecate missing ones (THEN 1)
97 WHEN c.vocabulary_id = 'SNOMED' THEN 1
98 WHEN c.vocabulary_id = 'LOINC' AND c.concept_class_id = 'LOINC Answers' THEN 1 -- Only LOINC answers are full lists
99 WHEN c.vocabulary_id = 'LOINC' THEN 0 -- LOINC gives full account of all concepts
100 WHEN c.vocabulary_id = 'ICD9CM' THEN 1
101 WHEN c.vocabulary_id = 'ICD9Proc' THEN 1
102 WHEN c.vocabulary_id = 'ICD10' THEN 1
103 WHEN c.vocabulary_id = 'RxNorm' THEN 1
104 WHEN c.vocabulary_id = 'NDFRT' THEN 1
105 WHEN c.vocabulary_id = 'VA Product' THEN 1
106 WHEN c.vocabulary_id = 'VA Class' THEN 1
107 WHEN c.vocabulary_id = 'ATC' THEN 1
108 WHEN c.vocabulary_id = 'NDC' THEN 0
109 WHEN c.vocabulary_id = 'SPL' THEN 0
110 WHEN c.vocabulary_id = 'MedDRA' THEN 1
111 WHEN c.vocabulary_id = 'CPT4' THEN 1
112 WHEN c.vocabulary_id = 'HCPCS' THEN 1
113 WHEN c.vocabulary_id = 'Read' THEN 1
114 WHEN c.vocabulary_id = 'ICD10CM' THEN 1
115 WHEN c.vocabulary_id = 'GPI' THEN 1
116 WHEN c.vocabulary_id = 'OPCS4' THEN 1
117 WHEN c.vocabulary_id = 'MeSH' THEN 1
118 WHEN c.vocabulary_id = 'GCN_SEQNO' THEN 1
119 WHEN c.vocabulary_id = 'ETC' THEN 1
120 WHEN c.vocabulary_id = 'Indication' THEN 1
121 WHEN c.vocabulary_id = 'DA_France' THEN 1
122 WHEN c.vocabulary_id = 'DPD' THEN 1
123 WHEN c.vocabulary_id = 'NFC' THEN 1
124 WHEN c.vocabulary_id = 'ICD10PCS' THEN 1
125 WHEN c.vocabulary_id = 'EphMRA ATC' THEN 1
126 WHEN c.vocabulary_id = 'dm+d' THEN 1
127 WHEN c.vocabulary_id = 'RxNorm Extension' THEN 0
128 WHEN c.vocabulary_id = 'Gemscript' THEN 1
129 WHEN c.vocabulary_id = 'Cost Type' THEN 1
130 WHEN c.vocabulary_id = 'BDPM' THEN 1
131 WHEN c.vocabulary_id = 'AMT' THEN 1
132 WHEN c.vocabulary_id = 'GRR' THEN 0
133 WHEN c.vocabulary_id = 'CVX' THEN 1
134 WHEN c.vocabulary_id = 'LPD_Australia' THEN 1
135 WHEN c.vocabulary_id = 'PPI' THEN 1
136 WHEN c.vocabulary_id = 'ICDO3' THEN 1
137 WHEN c.vocabulary_id = 'CDT' THEN 1
138 WHEN c.vocabulary_id = 'ISBT' THEN 0
139 WHEN c.vocabulary_id = 'ISBT Attributes' THEN 0
140 WHEN c.vocabulary_id = 'GGR' THEN 1
141 WHEN c.vocabulary_id = 'LPD_Belgium' THEN 1
142 WHEN c.vocabulary_id = 'APC' THEN 1
143 ELSE 0 -- in default we will not deprecate
144 END = 1;
145
146 -- 4. Add new concepts from concept_stage
147 -- Create sequence after last valid one
148 DO $$
149 DECLARE
150 ex INTEGER;
151 BEGIN
152 --SELECT MAX(concept_id)+1 INTO ex FROM concept WHERE concept_id<500000000; -- Last valid below HOI concept_id
153 DROP SEQUENCE IF EXISTS v5_concept;
154 SELECT concept_id + 1 INTO ex FROM (
155 SELECT concept_id, next_id, next_id - concept_id - 1 free_concept_ids
156 FROM (SELECT concept_id, LEAD (concept_id) OVER (ORDER BY concept_id) next_id FROM concept where concept_id >= 581480 and concept_id < 500000000) AS t
157 WHERE concept_id <> next_id - 1 AND next_id - concept_id > (SELECT COUNT (*) FROM concept_stage WHERE concept_id IS NULL)
158 ORDER BY next_id - concept_id
159 FETCH FIRST 1 ROW ONLY
160 ) AS sq;
161 EXECUTE 'CREATE SEQUENCE v5_concept INCREMENT BY 1 START WITH ' || ex || ' NO CYCLE CACHE 20';
162 END$$;
163
164 INSERT INTO concept (
165 concept_id,
166 concept_name,
167 domain_id,
168 vocabulary_id,
169 concept_class_id,
170 standard_concept,
171 concept_code,
172 valid_start_date,
173 valid_end_date,
174 invalid_reason
175 )
176 SELECT NEXTVAL('v5_concept'),
177 cs.concept_name,
178 cs.domain_id,
179 cs.vocabulary_id,
180 cs.concept_class_id,
181 cs.standard_concept,
182 cs.concept_code,
183 cs.valid_start_date,
184 cs.valid_end_date,
185 cs.invalid_reason
186 FROM concept_stage cs
187 WHERE cs.concept_id IS NULL;-- new because no concept_id could be found for the concept_code/vocabulary_id combination
188
189 DROP SEQUENCE v5_concept;
190
191 ANALYZE concept;
192
193 -- 5. Make sure that invalid concepts are standard_concept = NULL
194 UPDATE concept c
195 SET standard_concept = NULL
196 WHERE c.valid_end_date != TO_DATE('20991231', 'YYYYMMDD')
197 AND c.standard_concept IS NOT NULL
198 AND c.vocabulary_id IN (
199 SELECT vocabulary_id
200 FROM vocabulary
201 WHERE latest_update IS NOT NULL
202 );-- only for current vocabularies
203
204 /****************************************
205 * Update the concept_relationship table *
206 ****************************************/
207
208 -- 6. Turn all relationship records so they are symmetrical if necessary
209 INSERT INTO concept_relationship_stage (
210 concept_code_1,
211 concept_code_2,
212 vocabulary_id_1,
213 vocabulary_id_2,
214 relationship_id,
215 valid_start_date,
216 valid_end_date,
217 invalid_reason
218 )
219 SELECT crs.concept_code_2,
220 crs.concept_code_1,
221 crs.vocabulary_id_2,
222 crs.vocabulary_id_1,
223 r.reverse_relationship_id,
224 crs.valid_start_date,
225 crs.valid_end_date,
226 crs.invalid_reason
227 FROM concept_relationship_stage crs
228 JOIN relationship r ON r.relationship_id = crs.relationship_id
229 WHERE NOT EXISTS (
230 -- the inverse record
231 SELECT 1
232 FROM concept_relationship_stage i
233 WHERE crs.concept_code_1 = i.concept_code_2
234 AND crs.concept_code_2 = i.concept_code_1
235 AND crs.vocabulary_id_1 = i.vocabulary_id_2
236 AND crs.vocabulary_id_2 = i.vocabulary_id_1
237 AND r.reverse_relationship_id = i.relationship_id
238 );
239
240 -- 7. Update all relationships existing in concept_relationship_stage, including undeprecation of formerly deprecated ones
241 ANALYZE concept_relationship_stage;
242
243 WITH crs
244 AS (
245 SELECT c1.concept_id c_id1,
246 c2.concept_id c_id2,
247 crs.relationship_id,
248 crs.valid_end_date,
249 crs.invalid_reason
250 FROM concept_relationship_stage crs
251 JOIN concept c1 ON c1.concept_code = crs.concept_code_1
252 AND c1.vocabulary_id = crs.vocabulary_id_1
253 JOIN concept c2 ON c2.concept_code = crs.concept_code_2
254 AND c2.vocabulary_id = crs.vocabulary_id_2
255 )
256 UPDATE concept_relationship cr
257 SET valid_end_date = crs.valid_end_date,
258 invalid_reason = crs.invalid_reason
259 FROM crs
260 WHERE cr.concept_id_1 = crs.c_id1
261 AND cr.concept_id_2 = crs.c_id2
262 AND cr.relationship_id = crs.relationship_id
263 AND cr.valid_end_date <> crs.valid_end_date;
264
265 -- 8. Deprecate missing relationships, but only if the concepts are fresh. If relationships are missing because of deprecated concepts, leave them intact.
266 -- Also, only relationships are considered missing if the combination of vocabulary_id_1, vocabulary_id_2 AND relationship_id is present in concept_relationship_stage
267 -- The latter will prevent large-scale deprecations of relationships between vocabularies where the relationship is defined not here, but together with the other vocab
268
269 -- Do the deprecation
270 WITH relationships AS (
271 SELECT * FROM UNNEST(ARRAY[
272 'Concept replaced by',
273 'Concept same_as to',
274 'Concept alt_to to',
275 'Concept poss_eq to',
276 'Concept was_a to',
277 'Maps to']) AS relationship_id
278 ),
279 vocab_combinations as (
280 -- Create a list of vocab1, vocab2 and relationship_id existing in concept_relationship_stage, except 'Maps' to and replacement relationships
281 -- Also excludes manual mappings from concept_relationship_manual
282 SELECT vocabulary_id_1, vocabulary_id_2, relationship_id
283 FROM (
284 SELECT concept_code_1, concept_code_2, vocabulary_id_1, vocabulary_id_2, relationship_id FROM concept_relationship_stage
285 EXCEPT
286 (
287 SELECT concept_code_1, concept_code_2, vocabulary_id_1, vocabulary_id_2, relationship_id FROM concept_relationship_manual
288 UNION ALL
289 --add reverse mappings for exclude
290 SELECT concept_code_2, concept_code_1, vocabulary_id_2, vocabulary_id_1, reverse_relationship_id
291 FROM concept_relationship_manual JOIN relationship USING (relationship_id)
292 )
293 ) AS s1
294 WHERE vocabulary_id_1 NOT IN ('SPL','RxNorm Extension')
295 AND vocabulary_id_2 NOT IN ('SPL','RxNorm Extension')
296 AND relationship_id NOT IN (
297 SELECT relationship_id FROM relationships
298 UNION ALL
299 SELECT reverse_relationship_id FROM relationships JOIN relationship USING (relationship_id)
300 )
301 GROUP BY vocabulary_id_1, vocabulary_id_2, relationship_id
302 )
303 UPDATE concept_relationship d
304 SET valid_end_date = (SELECT MAX(v.latest_update)-1 FROM vocabulary v WHERE v.vocabulary_id=c1.vocabulary_id OR v.vocabulary_id=c2.vocabulary_id),
305 invalid_reason = 'D'
306 -- Whether the combination of vocab1, vocab2 and relationship exists (in subquery)
307 -- (intended to be covered by this particular vocab udpate)
308 -- And both concepts exist (don't deprecate relationships of deprecated concepts)
309 FROM concept c1, concept c2
310 WHERE c1.concept_id = d.concept_id_1 AND c2.concept_id = d.concept_id_2
311 AND (c1.vocabulary_id,c2.vocabulary_id,d.relationship_id) IN (SELECT vocabulary_id_1,vocabulary_id_2,relationship_id FROM vocab_combinations)
312 AND c1.invalid_reason is null
313 AND c2.invalid_reason is null
314 -- And the record is currently fresh and not already deprecated
315 AND d.invalid_reason is null
316 -- And it was started before release date
317 AND d.valid_start_date < (
318 -- One of latest_update (if we have more than one vocabulary in concept_relationship_stage) may be NULL, therefore use aggregate function MAX() to get one non-null date
319 SELECT MAX(v.latest_update)-1 FROM vocabulary v WHERE v.vocabulary_id=c1.vocabulary_id OR v.vocabulary_id=c2.vocabulary_id --take both concept ids to get proper latest_update
320 )
321 -- And it is missing from the new concept_relationship_stage
322 AND NOT EXISTS (
323 SELECT 1 FROM concept_relationship_stage crs
324 WHERE crs.concept_code_1=c1.concept_code
325 AND crs.vocabulary_id_1=c1.vocabulary_id
326 AND crs.concept_code_2=c2.concept_code
327 AND crs.vocabulary_id_2=c2.vocabulary_id
328 AND crs.relationship_id = d.relationship_id
329 );
330
331 --9. Deprecate old 'Maps to' and replacement records, but only if we have a new one in concept_relationship_stage with the same source concept
332 --part 1 (direct mappings)
333 WITH relationships AS (
334 SELECT relationship_id FROM relationship
335 WHERE relationship_id IN (
336 'Concept replaced by',
337 'Concept same_as to',
338 'Concept alt_to to',
339 'Concept poss_eq to',
340 'Concept was_a to',
341 'Maps to'
342 )
343 )
344 UPDATE concept_relationship r
345 SET valid_end_date =
346 (SELECT MAX(v.latest_update) -- one of latest_update (if we have more than one vocabulary in concept_relationship_stage) may be NULL, therefore use aggregate function MAX() to get one non-null date
347 FROM concept c JOIN vocabulary v ON c.vocabulary_id = v.vocabulary_id
348 WHERE c.concept_id IN (r.concept_id_1, r.concept_id_2) --take both concept ids to get proper latest_update
349 ) - 1,
350 invalid_reason = 'D'
351 FROM concept c, relationships rel
352 WHERE r.concept_id_2=c.concept_id
353 AND r.invalid_reason IS NULL
354 AND r.relationship_id=rel.relationship_id
355 AND r.concept_id_1<>r.concept_id_2
356 AND EXISTS (
357 SELECT 1 FROM concept_relationship_stage crs, concept c1
358 WHERE crs.concept_code_1=c1.concept_code
359 AND crs.vocabulary_id_1=c1.vocabulary_id
360 AND crs.relationship_id=r.relationship_id
361 AND crs.invalid_reason IS NULL
362 AND c1.concept_id=r.concept_id_1
363 AND (
364 crs.vocabulary_id_2=c.vocabulary_id
365 OR (/*AVOF-459*/
366 crs.vocabulary_id_2 IN ('RxNorm','RxNorm Extension') AND c.vocabulary_id IN ('RxNorm','RxNorm Extension')
367 )
368 )
369 )
370 AND NOT EXISTS (
371 SELECT 1 FROM concept_relationship_stage crs, concept c1, concept c2
372 WHERE crs.concept_code_1=c1.concept_code
373 AND crs.vocabulary_id_1=c1.vocabulary_id
374 AND crs.concept_code_2=c2.concept_code
375 AND crs.vocabulary_id_2=c2.vocabulary_id
376 AND crs.relationship_id=r.relationship_id
377 AND crs.invalid_reason IS NULL
378 AND c1.concept_id=r.concept_id_1
379 AND c2.concept_id=r.concept_id_2
380 );
381
382 --part 2 (reverse mappings)
383 WITH relationships AS (
384 SELECT reverse_relationship_id FROM relationship
385 WHERE relationship_id in (
386 'Concept replaced by',
387 'Concept same_as to',
388 'Concept alt_to to',
389 'Concept poss_eq to',
390 'Concept was_a to',
391 'Maps to'
392 )
393 )
394 UPDATE concept_relationship r
395 SET valid_end_date =
396 (SELECT MAX(v.latest_update) -- one of latest_update (if we have more than one vocabulary in concept_relationship_stage) may be NULL, therefore use aggregate function MAX() to get one non-null date
397 FROM concept c JOIN vocabulary v ON c.vocabulary_id = v.vocabulary_id
398 WHERE c.concept_id IN (r.concept_id_1, r.concept_id_2) --take both concept ids to get proper latest_update
399 ) - 1,
400 invalid_reason = 'D'
401 FROM concept c, relationships rel
402 WHERE r.concept_id_1=c.concept_id
403 AND r.invalid_reason IS NULL
404 AND r.relationship_id=rel.reverse_relationship_id
405 AND r.concept_id_1<>r.concept_id_2
406 AND EXISTS (
407 SELECT 1 FROM concept_relationship_stage crs, concept c1
408 WHERE crs.concept_code_2=c1.concept_code
409 AND crs.vocabulary_id_2=c1.vocabulary_id
410 AND crs.relationship_id=r.relationship_id
411 AND crs.invalid_reason IS NULL
412 AND c1.concept_id=r.concept_id_2
413 AND (
414 crs.vocabulary_id_1=c.vocabulary_id
415 OR (/*AVOF-459*/
416 crs.vocabulary_id_1 IN ('RxNorm','RxNorm Extension') AND c.vocabulary_id IN ('RxNorm','RxNorm Extension')
417 )
418 )
419 )
420 AND NOT EXISTS (
421 SELECT 1 FROM concept_relationship_stage crs, concept c1, concept c2
422 WHERE crs.concept_code_1=c1.concept_code
423 AND crs.vocabulary_id_1=c1.vocabulary_id
424 AND crs.concept_code_2=c2.concept_code
425 AND crs.vocabulary_id_2=c2.vocabulary_id
426 AND crs.relationship_id=r.relationship_id
427 AND crs.invalid_reason IS NULL
428 AND c1.concept_id=r.concept_id_1
429 AND c2.concept_id=r.concept_id_2
430 );
431
432 -- 10. Insert new relationships if they don't already exist
433 INSERT INTO concept_relationship
434 SELECT c1.concept_id AS concept_id_1,
435 c2.concept_id AS concept_id_2,
436 crs.relationship_id,
437 crs.valid_start_date,
438 crs.valid_end_date,
439 crs.invalid_reason
440 FROM concept_relationship_stage crs
441 JOIN concept c1 ON c1.concept_code = crs.concept_code_1 AND c1.vocabulary_id = crs.vocabulary_id_1
442 JOIN concept c2 ON c2.concept_code = crs.concept_code_2 AND c2.vocabulary_id = crs.vocabulary_id_2
443 WHERE NOT EXISTS (
444 SELECT 1
445 FROM concept_relationship cr_int
446 WHERE cr_int.concept_id_1 = c1.concept_id
447 AND cr_int.concept_id_2 = c2.concept_id
448 AND cr_int.relationship_id = crs.relationship_id
449 );
450
451 /*********************************************************
452 * Update the correct invalid reason in the concept table *
453 * This should rarely happen *
454 *********************************************************/
455
456 -- 11. Make sure invalid_reason = 'U' if we have an active replacement record in the concept_relationship table
457 UPDATE concept c
458 SET valid_end_date = v.latest_update - 1, -- day before release day
459 invalid_reason = 'U',
460 standard_concept = NULL
461 FROM concept_relationship cr, vocabulary v
462 WHERE c.vocabulary_id = v.vocabulary_id
463 AND cr.concept_id_1 = c.concept_id
464 AND cr.invalid_reason IS NULL
465 AND cr.relationship_id IN (
466 'Concept replaced by',
467 'Concept same_as to',
468 'Concept alt_to to',
469 'Concept poss_eq to',
470 'Concept was_a to'
471 )
472 AND v.latest_update IS NOT NULL -- only for current vocabularies
473 AND (c.invalid_reason IS NULL OR c.invalid_reason = 'D'); -- not already upgraded
474
475 -- 12. Make sure invalid_reason = 'D' if we have no active replacement record in the concept_relationship table for upgraded concepts
476 UPDATE concept c
477 SET valid_end_date = (
478 SELECT v.latest_update
479 FROM vocabulary v
480 WHERE c.vocabulary_id = v.vocabulary_id
481 ) - 1, -- day before release day
482 invalid_reason = 'D',
483 standard_concept = NULL
484 WHERE NOT EXISTS (
485 SELECT 1
486 FROM concept_relationship r
487 WHERE r.concept_id_1 = c.concept_id
488 AND r.invalid_reason IS NULL
489 AND r.relationship_id IN (
490 'Concept replaced by',
491 'Concept same_as to',
492 'Concept alt_to to',
493 'Concept poss_eq to',
494 'Concept was_a to'
495 )
496 )
497 AND c.vocabulary_id IN (
498 SELECT vocabulary_id
499 FROM vocabulary
500 WHERE latest_update IS NOT NULL
501 ) -- only for current vocabularies
502 AND c.invalid_reason = 'U';-- not already deprecated
503
504 -- The following are a bunch of rules for Maps to and Maps from relationships.
505 -- Since they work outside the _stage tables, they will be restricted to the vocabularies worked on
506
507 -- 13. 'Maps to' and 'Mapped from' relationships from concepts to self should exist for all concepts where standard_concept = 'S'
508 WITH to_be_upserted AS (
509 SELECT c.concept_id, v.latest_update, lat.relationship_id
510 FROM concept c, vocabulary v, LATERAL (SELECT case when generate_series=1 then 'Maps to' ELSE 'Mapped from' END AS relationship_id FROM generate_series(1,2)) lat
511 WHERE v.vocabulary_id = c.vocabulary_id AND v.latest_update IS NOT NULL AND c.standard_concept = 'S' AND invalid_reason IS NULL
512 ),
513 to_be_updated AS (
514 UPDATE concept_relationship cr
515 SET invalid_reason = NULL, valid_end_date = TO_DATE ('20991231', 'yyyymmdd')
516 FROM to_be_upserted up
517 WHERE cr.invalid_reason IS NOT NULL
518 AND cr.concept_id_1 = up.concept_id AND cr.concept_id_2 = up.concept_id AND cr.relationship_id = up.relationship_id
519 RETURNING cr.*
520 )
521 INSERT INTO concept_relationship
522 SELECT tpu.concept_id, tpu.concept_id, tpu.relationship_id, tpu.latest_update, TO_DATE ('20991231', 'yyyymmdd'), NULL
523 FROM to_be_upserted tpu
524 WHERE (tpu.concept_id, tpu.concept_id, tpu.relationship_id)
525 NOT IN (
526 SELECT up.concept_id_1, up.concept_id_2, up.relationship_id FROM to_be_updated up
527 UNION ALL
528 SELECT cr_int.concept_id_1, cr_int.concept_id_2, cr_int.relationship_id FROM concept_relationship cr_int
529 WHERE cr_int.concept_id_1=cr_int.concept_id_2 AND cr_int.relationship_id IN ('Maps to','Mapped from')
530 );
531
532 -- 14. 'Maps to' or 'Mapped from' relationships should not exist where
533 -- a) the source concept has standard_concept = 'S', unless it is to self
534 -- b) the target concept has standard_concept = 'C' or NULL
535 -- c) the target concept has invalid_reason='D' or 'U'
536
537 UPDATE concept_relationship r
538 SET valid_end_date = (SELECT MAX(v.latest_update)-1 FROM vocabulary v WHERE v.vocabulary_id=c1.vocabulary_id OR v.vocabulary_id=c2.vocabulary_id), -- day before release day
539 invalid_reason = 'D'
540 FROM concept c1, concept c2, vocabulary v
541 WHERE r.concept_id_1 = c1.concept_id
542 AND r.concept_id_2 = c2.concept_id
543 AND (
544 (c1.standard_concept = 'S' AND c1.concept_id != c2.concept_id) -- rule a)
545 OR COALESCE (c2.standard_concept, 'X') != 'S' -- rule b)
546 OR c2.invalid_reason IN ('U', 'D') -- rule c)
547 )
548 AND v.vocabulary_id IN (c1.vocabulary_id, c2.vocabulary_id)
549 AND v.latest_update IS NOT NULL -- only the current vocabularies
550 AND r.relationship_id = 'Maps to'
551 AND r.invalid_reason IS NULL;
552
553 -- And reverse
554 UPDATE concept_relationship r
555 SET valid_end_date = (SELECT MAX(v.latest_update)-1 FROM vocabulary v WHERE v.vocabulary_id=c1.vocabulary_id OR v.vocabulary_id=c2.vocabulary_id), -- day before release day
556 invalid_reason = 'D'
557 FROM concept c1, concept c2, vocabulary v
558 WHERE r.concept_id_1 = c1.concept_id
559 AND r.concept_id_2 = c2.concept_id
560 AND (
561 (c2.standard_concept = 'S' AND c1.concept_id != c2.concept_id) -- rule a)
562 OR COALESCE (c1.standard_concept, 'X') != 'S' -- rule b)
563 OR c1.invalid_reason IN ('U', 'D') -- rule c)
564 )
565 AND v.vocabulary_id IN (c1.vocabulary_id, c2.vocabulary_id)
566 AND v.latest_update IS NOT NULL -- only the current vocabularies
567 AND r.relationship_id = 'Mapped from'
568 AND r.invalid_reason IS NULL;
569
570 -- 15. Make sure invalid_reason = null if the valid_end_date is 31-Dec-2099
571 UPDATE concept
572 SET invalid_reason = NULL
573 WHERE valid_end_date = TO_DATE ('20991231', 'YYYYMMDD') -- deprecated date
574 AND vocabulary_id IN (SELECT vocabulary_id FROM vocabulary WHERE latest_update IS NOT NULL) -- only for current vocabularies
575 AND invalid_reason IS NOT NULL; -- if wrongly deprecated
576
577 --16 Post-processing (some concepts might be deprecated when they missed in source, so load_stage doesn't know about them and DO NOT deprecate relationships proper)
578 --Deprecate replacement records if target concept was deprecated
579 UPDATE concept_relationship cr
580 SET invalid_reason = 'D',
581 valid_end_date = (SELECT MAX (v.latest_update) FROM concept c JOIN vocabulary v ON c.vocabulary_id = v.vocabulary_id WHERE c.concept_id IN (cr.concept_id_1, cr.concept_id_2))-1
582 FROM (
583 WITH RECURSIVE hierarchy_concepts (concept_id_1, concept_id_2, relationship_id, full_path) AS
584 (
585 SELECT concept_id_1, concept_id_2, relationship_id, ARRAY [concept_id_1] AS full_path
586 FROM upgraded_concepts
587 WHERE concept_id_2 IN (SELECT concept_id_2 FROM upgraded_concepts WHERE invalid_reason = 'D')
588 UNION ALL
589 SELECT c.concept_id_1, c.concept_id_2, c.relationship_id, hc.full_path || c.concept_id_1 AS full_path
590 FROM upgraded_concepts c
591 JOIN hierarchy_concepts hc on hc.concept_id_1=c.concept_id_2
592 WHERE c.concept_id_1 <> ALL (full_path)
593 ),
594 upgraded_concepts AS (
595 SELECT r.concept_id_1,
596 r.concept_id_2,
597 r.relationship_id,
598 c2.invalid_reason
599 FROM concept c1, concept c2, concept_relationship r
600 WHERE r.relationship_id IN (
601 'Concept replaced by',
602 'Concept same_as to',
603 'Concept alt_to to',
604 'Concept poss_eq to',
605 'Concept was_a to'
606 )
607 AND r.invalid_reason IS NULL
608 AND c1.concept_id = r.concept_id_1
609 AND c2.concept_id = r.concept_id_2
610 AND EXISTS (SELECT 1 FROM vocabulary WHERE latest_update IS NOT NULL AND vocabulary_id IN (c1.vocabulary_id,c2.vocabulary_id))
611 AND c2.concept_code <> 'OMOP generated'
612 AND r.concept_id_1 <> r.concept_id_2
613 )
614 SELECT concept_id_1, concept_id_2, relationship_id FROM hierarchy_concepts
615 ) i
616 WHERE cr.concept_id_1 = i.concept_id_1 AND cr.concept_id_2 = i.concept_id_2 AND cr.relationship_id = i.relationship_id;
617
618 --Deprecate concepts if we have no active replacement record in the concept_relationship
619 UPDATE concept c
620 SET valid_end_date = (
621 SELECT v.latest_update
622 FROM vocabulary v
623 WHERE c.vocabulary_id = v.vocabulary_id
624 ) - 1, -- day before release day
625 invalid_reason = 'D',
626 standard_concept = NULL
627 WHERE NOT EXISTS (
628 SELECT 1
629 FROM concept_relationship r
630 WHERE r.concept_id_1 = c.concept_id
631 AND r.invalid_reason IS NULL
632 AND r.relationship_id IN (
633 'Concept replaced by',
634 'Concept same_as to',
635 'Concept alt_to to',
636 'Concept poss_eq to',
637 'Concept was_a to'
638 )
639 )
640 AND c.vocabulary_id IN (
641 SELECT vocabulary_id
642 FROM vocabulary
643 WHERE latest_update IS NOT NULL
644 ) -- only for current vocabularies
645 AND c.invalid_reason = 'U';-- not already deprecated
646
647 --Deprecate 'Maps to' mappings to deprecated and upgraded concepts
648 UPDATE concept_relationship r
649 SET valid_end_date = (
650 SELECT MAX(v.latest_update)
651 FROM concept c
652 JOIN vocabulary v ON c.vocabulary_id = v.vocabulary_id
653 WHERE c.concept_id IN (
654 r.concept_id_1,
655 r.concept_id_2
656 )
657 ) - 1,
658 invalid_reason = 'D'
659 WHERE r.relationship_id = 'Maps to'
660 AND r.invalid_reason IS NULL
661 AND EXISTS (
662 SELECT 1
663 FROM concept c
664 WHERE c.concept_id = r.concept_id_2
665 AND c.invalid_reason IN (
666 'U',
667 'D'
668 )
669 )
670 AND EXISTS (
671 SELECT 1
672 FROM concept c
673 JOIN vocabulary v ON c.vocabulary_id = v.vocabulary_id
674 WHERE c.concept_id IN (
675 r.concept_id_1,
676 r.concept_id_2
677 )
678 AND v.latest_update IS NOT NULL
679 );
680
681 --Reverse for deprecating
682 UPDATE concept_relationship r
683 SET invalid_reason = r1.invalid_reason,
684 valid_end_date = r1.valid_end_date
685 FROM concept_relationship r1
686 JOIN relationship rel ON r1.relationship_id = rel.relationship_id
687 WHERE r1.relationship_id IN (
688 'Concept replaced by',
689 'Concept same_as to',
690 'Concept alt_to to',
691 'Concept poss_eq to',
692 'Concept was_a to',
693 'Maps to'
694 )
695 AND EXISTS (
696 SELECT 1
697 FROM concept c
698 JOIN vocabulary v ON c.vocabulary_id = v.vocabulary_id
699 WHERE c.concept_id IN (
700 r1.concept_id_1,
701 r1.concept_id_2
702 )
703 AND v.latest_update IS NOT NULL
704 )
705 AND r.concept_id_1 = r1.concept_id_2
706 AND r.concept_id_2 = r1.concept_id_1
707 AND r.relationship_id = rel.reverse_relationship_id
708 AND r.valid_end_date <> r1.valid_end_date;
709
710 --17. fix valid_start_date for incorrect concepts (bad data in sources)
711 UPDATE concept c
712 SET valid_start_date = valid_end_date - 1
713 WHERE c.valid_end_date < c.valid_start_date
714 AND c.vocabulary_id IN (
715 SELECT vocabulary_id
716 FROM vocabulary
717 WHERE latest_update IS NOT NULL
718 );-- only for current vocabularies
719
720 /***********************************
721 * Update the concept_synonym table *
722 ************************************/
723
724 -- 18. Add all missing synonyms
725 INSERT INTO concept_synonym_stage (
726 synonym_concept_id,
727 synonym_concept_code,
728 synonym_name,
729 synonym_vocabulary_id,
730 language_concept_id
731 )
732 SELECT NULL AS synonym_concept_id,
733 c.concept_code AS synonym_concept_code,
734 c.concept_name AS synonym_name,
735 c.vocabulary_id AS synonym_vocabulary_id,
736 4180186 AS language_concept_id
737 FROM concept_stage c
738 WHERE NOT EXISTS (
739 SELECT 1
740 FROM concept_synonym_stage css
741 WHERE css.synonym_concept_code = c.concept_code
742 AND css.synonym_vocabulary_id = c.vocabulary_id
743 );
744
745 -- 19. Remove all existing synonyms for concepts that are in concept_stage
746 -- Synonyms are built from scratch each time, no life cycle
747
748 ANALYZE concept_synonym_stage;
749
750 DELETE
751 FROM concept_synonym csyn
752 WHERE csyn.concept_id IN (
753 SELECT c.concept_id
754 FROM concept c,
755 concept_stage cs
756 WHERE c.concept_code = cs.concept_code
757 AND cs.vocabulary_id = c.vocabulary_id
758 );
759
760 -- 20. Add new synonyms for existing concepts
761 INSERT INTO concept_synonym (
762 concept_id,
763 concept_synonym_name,
764 language_concept_id
765 )
766 SELECT c.concept_id,
767 REGEXP_REPLACE(TRIM(synonym_name), '[[:space:]]+', ' '),
768 4180186 -- for English
769 FROM concept_synonym_stage css,
770 concept c,
771 concept_stage cs
772 WHERE css.synonym_concept_code = c.concept_code
773 AND css.synonym_vocabulary_id = c.vocabulary_id
774 AND cs.concept_code = c.concept_code
775 AND cs.vocabulary_id = c.vocabulary_id
776 AND REGEXP_REPLACE(TRIM(synonym_name), '[[:space:]]+', ' ') IS NOT NULL --fix for empty GPI names
777 GROUP BY c.concept_id,
778 REGEXP_REPLACE(TRIM(synonym_name), '[[:space:]]+', ' ');
779
780 -- 21. Fillig drug_strength
781 -- Special rules for RxNorm Extension: same as 'Maps to' rules, but records from deprecated concepts will be deleted
782 DELETE
783 FROM drug_strength
784 WHERE drug_concept_id IN (
785 SELECT c.concept_id
786 FROM concept c
787 JOIN vocabulary v ON c.vocabulary_id = v.vocabulary_id
788 WHERE latest_update IS NOT NULL
789 AND v.vocabulary_id <> 'RxNorm Extension'
790 );
791
792 -- Replace with fresh records (only for 'RxNorm Extension')
793 DELETE
794 FROM drug_strength ds
795 WHERE EXISTS (
796 SELECT 1
797 FROM drug_strength_stage dss
798 JOIN concept c1 ON c1.concept_code = dss.drug_concept_code
799 AND c1.vocabulary_id = dss.vocabulary_id_1
800 AND ds.drug_concept_id = c1.concept_id
801 JOIN vocabulary v ON v.vocabulary_id = c1.vocabulary_id
802 WHERE v.latest_update IS NOT NULL
803 AND v.vocabulary_id = 'RxNorm Extension'
804 );
805
806 -- Insert new records
807 INSERT INTO drug_strength (
808 drug_concept_id,
809 ingredient_concept_id,
810 amount_value,
811 amount_unit_concept_id,
812 numerator_value,
813 numerator_unit_concept_id,
814 denominator_value,
815 denominator_unit_concept_id,
816 box_size,
817 valid_start_date,
818 valid_end_date,
819 invalid_reason
820 )
821 SELECT c1.concept_id,
822 c2.concept_id,
823 ds.amount_value,
824 ds.amount_unit_concept_id,
825 ds.numerator_value,
826 ds.numerator_unit_concept_id,
827 ds.denominator_value,
828 ds.denominator_unit_concept_id,
829 regexp_replace(bs.concept_name, '.+Box of ([0-9]+).*', '\1')::INT AS box_size,
830 ds.valid_start_date,
831 ds.valid_end_date,
832 ds.invalid_reason
833 FROM drug_strength_stage ds
834 JOIN concept c1 ON c1.concept_code = ds.drug_concept_code
835 AND c1.vocabulary_id = ds.vocabulary_id_1
836 JOIN concept c2 ON c2.concept_code = ds.ingredient_concept_code
837 AND c2.vocabulary_id = ds.vocabulary_id_2
838 JOIN vocabulary v ON v.vocabulary_id = c1.vocabulary_id
839 LEFT JOIN concept bs ON bs.concept_id = c1.concept_id
840 AND bs.vocabulary_id = 'RxNorm Extension'
841 AND bs.concept_name LIKE '%Box of%'
842 WHERE v.latest_update IS NOT NULL;
843
844 -- Delete drug if concept is deprecated (only for 'RxNorm Extension')
845 DELETE
846 FROM drug_strength ds
847 WHERE EXISTS (
848 SELECT 1
849 FROM concept c1
850 JOIN vocabulary v ON v.vocabulary_id = c1.vocabulary_id
851 WHERE ds.drug_concept_id = c1.concept_id
852 AND v.latest_update IS NOT NULL
853 AND v.vocabulary_id = 'RxNorm Extension'
854 AND c1.invalid_reason IS NOT NULL
855 );
856
857 -- 22. Fillig pack_content
858 -- Special rules for RxNorm Extension: same as 'Maps to' rules, but records from deprecated concepts will be deleted
859 DELETE
860 FROM pack_content
861 WHERE pack_concept_id IN (
862 SELECT c.concept_id
863 FROM concept c
864 JOIN vocabulary v ON c.vocabulary_id = v.vocabulary_id
865 WHERE latest_update IS NOT NULL
866 AND v.vocabulary_id <> 'RxNorm Extension'
867 );
868
869 -- Replace with fresh records (only for 'RxNorm Extension')
870 DELETE
871 FROM pack_content pc
872 WHERE EXISTS (
873 SELECT 1
874 FROM pack_content_stage pcs
875 JOIN concept c1 ON c1.concept_code = pcs.pack_concept_code
876 AND c1.vocabulary_id = pcs.pack_vocabulary_id
877 AND pc.pack_concept_id = c1.concept_id
878 JOIN vocabulary v ON v.vocabulary_id = c1.vocabulary_id
879 WHERE v.latest_update IS NOT NULL
880 AND v.vocabulary_id = 'RxNorm Extension'
881 );
882
883 INSERT INTO pack_content (
884 pack_concept_id,
885 drug_concept_id,
886 amount,
887 box_size
888 )
889 SELECT c1.concept_id,
890 c2.concept_id,
891 ds.amount,
892 ds.box_size
893 FROM pack_content_stage ds
894 JOIN concept c1 ON c1.concept_code = ds.pack_concept_code
895 AND c1.vocabulary_id = ds.pack_vocabulary_id
896 JOIN concept c2 ON c2.concept_code = ds.drug_concept_code
897 AND c2.vocabulary_id = ds.drug_vocabulary_id
898 JOIN vocabulary v ON v.vocabulary_id = c1.vocabulary_id
899 WHERE v.latest_update IS NOT NULL;
900
901 -- Delete if concept is deprecated (only for 'RxNorm Extension')
902 DELETE
903 FROM pack_content pc
904 WHERE EXISTS (
905 SELECT 1
906 FROM concept c1
907 JOIN vocabulary v ON v.vocabulary_id = c1.vocabulary_id
908 WHERE pc.pack_concept_id = c1.concept_id
909 AND v.latest_update IS NOT NULL
910 AND v.vocabulary_id = 'RxNorm Extension'
911 AND c1.invalid_reason IS NOT NULL
912 );
913
914 -- 23. check if current vocabulary exists in vocabulary_conversion table
915 INSERT INTO vocabulary_conversion (
916 vocabulary_id_v4,
917 vocabulary_id_v5
918 )
919 SELECT rownum + (
920 SELECT MAX(vocabulary_id_v4)
921 FROM vocabulary_conversion
922 ) AS rn,
923 a [rownum] AS vocabulary_id
924 FROM (
925 SELECT a,
926 generate_series(1, array_upper(a, 1)) AS rownum
927 FROM (
928 SELECT ARRAY(SELECT vocabulary_id FROM vocabulary
929
930 EXCEPT
931
932 SELECT vocabulary_id_v5 FROM vocabulary_conversion) AS a
933 ) AS s1
934 ) AS s2;
935
936 -- 24. update latest_update on vocabulary_conversion
937 UPDATE vocabulary_conversion vc
938 SET latest_update = v.latest_update
939 FROM vocabulary v
940 WHERE v.latest_update IS NOT NULL
941 AND v.vocabulary_id = vc.vocabulary_id_v5;
942
943 -- 25. drop column latest_update
944 DO $_$
945 DECLARE
946 z vocabulary.vocabulary_id%TYPE;
947 BEGIN
948 SELECT vocabulary_id INTO z FROM vocabulary WHERE latest_update IS NOT NULL LIMIT 1;
949
950 IF z <> 'RxNorm'
951 THEN
952 ALTER TABLE vocabulary DROP COLUMN latest_update;
953 ALTER TABLE vocabulary DROP COLUMN dev_schema_name;
954 END IF;
955 END
956 $_$ LANGUAGE plpgsql;
957
958 -- 26. Final ANALYSING for base tables
959 ANALYZE concept;
960 ANALYZE concept_relationship;
961 ANALYZE concept_synonym;
962 -- QA (should return NULL)
963 -- select * from QA_TESTS.GET_CHECKS();
964END;
965$body$
966LANGUAGE 'plpgsql'
967VOLATILE
968CALLED ON NULL INPUT
969SECURITY INVOKER
970COST 100
971SET client_min_messages = error;