# {21} PHI clean-up (1 match)

see #1358

for reference: TracReports, http://trac.edgewall.org/wiki/TracDev/DatabaseSchema

Id | Created | Status | Field | N | Phi_text | Oldvalue | Modified | Serial | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

#1714 | 01/11/13 | closed | comment | 24 | snippet from production heron_load*.log {{{ 2013-04-25 14:48:24.118264: deid_db: bheronFA on localhost insert into BlueHeronMetadata.term_tree ( child_term, parent_term, concept_cd, c_visualattributes) select c.term_id child_term , p.term_id parent_term , c.c_basecode concept_cd , c.c_visualattributes from BlueHeronMetadata.heron_terms c , (select iter.item, rpad('\\[^\\]+', iter.item * length('\\[^\\]+'), '\\[^\\]+') || '\\' expr from (Select Rownum item From dual Connect By Rownum <= 18 ) iter ) path_expr , BlueHeronMetadata.heron_terms p where c.m_applied_path = '@' and path_expr.item - 2 <= c.c_hlevel and p.c_hlevel < c.c_hlevel and p.c_fullname = regexp_substr(c.c_fullname, path_expr.expr, 1, 1) 2013-04-25 18:42:17.657299 end. duration: 3:53:53.539035 rows: 6701294 }}} {{{ 2013-03-18 15:28:24.298440: deid_db: bheronFB on localhost insert into BlueHeronMetadata.term_tree ( child_term, parent_term, concept_cd, c_visualattributes) select c.term_id child_term , p.term_id parent_term , c.c_basecode concept_cd , c.c_visualattributes from BlueHeronMetadata.heron_terms c , (select iter.item, rpad('\\[^\\]+', iter.item * length('\\[^\\]+'), '\\[^\\]+') || '\\' expr from (Select Rownum item From dual Connect By Rownum <= 18 ) iter ) path_expr , BlueHeronMetadata.heron_terms p where c.m_applied_path = '@' and path_expr.item - 1 <= c.c_hlevel and p.c_hlevel < c.c_hlevel and p.c_fullname = regexp_substr(c.c_fullname, path_expr.expr, 1, 1) 2013-03-18 17:30:15.896965 end. duration: 2:01:51.598525 rows: 6453914 }}} Sanity check to make sure we didn't lose parrent/child term relationships from term tree from the previous load. ------------------- Looking through the code we notice that the term_tree orphan check only made sure every term had parent not that it was visible if it should be. {{{ /* All terms got placed in the tree somewere, right? This seems to fail due to some TNM: stuff; no matter. */ select case orphan_qty when 0 then 1 else 1/0 end as all_terms_have_parents from ( select count (*) orphan_qty from ( select c.term_id , c.c_visualattributes , c.c_fullname , cp.concept_cd , c.c_basecode from BlueHeronMetadata.heron_terms c left join ( select ht.c_hlevel, cp.child_term, cp.concept_cd from BlueHeronMetadata.term_tree cp join BlueHeronMetadata.heron_terms ht on ht.term_id = cp.parent_term) cp on c.term_id = cp.child_term -- find parents only, not all ancestors and c.c_hlevel = cp.c_hlevel + 1 where cp.child_term is null and c.m_applied_path = '@' -- not modifiers and c.c_fullname != '\i2b2\' and c.c_fullname != '\i2b2\Diagnoses\zz V-codes\Body mass index (V85)\(V85-4) Body Mass Index 40 an~\' --remove after fixing ticket #1919 and c.c_fullname not like '\i2b2\Procedures\PRC\Metathesaurus CPT Hierarchical Terms\%' --remove after fixing ticket #26 order by c.c_fullname )); }}} The corrected version {{{ /* All terms got placed in the tree somewere visible, right? This seems to fail due to some TNM: stuff; no matter. */ select case orphan_qty when 0 then 1 ... on ht.term_id = cp.parent_term >>> where cp.c_visualattributes not in ('FH', 'LH') <<< ) cp ... and c.m_applied_path = '@' -- not modifiers >>> and c.c_visualattributes not in ('FH', 'LH') <<< and c.c_fullname != '\i2b2\' ... order by c.c_fullname )); }}} (done in changelist (a0d46b06bd16)) makes sure the parents are visible. This caused FB (previous months load) to show ~67000 orphaned concepts. However, FA (current load) had 0 orphaned concepts. So it appears that the previous fix for this ticket did have some effect in getting un-orphaning concepts but there are additional issues with the counts (which is why this ticket was reopened). | -- |

**Note:**See TracReports for help on using and creating reports.