source: main/trunk/db/src/main/scripts/mysql/data-cleanup.sql @ 18890

Last change on this file since 18890 was 18890, checked in by GarthBraithwaite_STG, 6 weeks ago

db, view - IPV title xslt rules and data updates.

File size: 15.6 KB
Line 
1use ibisph;
2
3/* REMOVE UNUSED DSC & DSS: clean out unused DSC and DSS records and del orphaned value records */
4delete from DATASET_CATEGORY where NAME not in (select distinct DATASET_CATEGORY_NAME from INDICATOR_VIEW);
5delete from DATASET_CATEGORY where NAME not in (select distinct DATASET_CATEGORY_NAME from DATASET_CATEGORY_VALUE);
6
7delete from DATASET_SERIES   where NAME not in (select distinct DATASET_SERIES_NAME   from INDICATOR_VIEW);
8delete from DATASET_SERIES   where NAME not in (select distinct DATASET_SERIES_NAME   from DATASET_SERIES_VALUE);
9
10delete from DATASET_CATEGORY_VALUE where DATASET_CATEGORY_NAME is null or DATASET_CATEGORY_NAME not in (select NAME from DATASET_CATEGORY);
11delete from DATASET_SERIES_VALUE   where DATASET_SERIES_NAME   is null or DATASET_SERIES_NAME   not in (select NAME from DATASET_SERIES);
12
13
14/* REMOVE IPs: delete specific IP's and Views */
15delete from INDICATOR where NAME like 'Demo%' and (ACTIVE_FLAG is null or ACTIVE_FLAG != 'x');
16delete from INDICATOR where NAME = 'HAI_CLABSI';
17delete from INDICATOR_VIEW where NAME = 'HlthInsurCover.Uninsured.States';
18
19
20/* REMOVE ORPHANED IPVs: delete invalid or orphaned IV records */
21delete from INDICATOR_VIEW where INDICATOR_NAME not in (select NAME from INDICATOR);
22delete from INDICATOR_VIEW
23where DATASET_CATEGORY_NAME is null 
24   or DATASET_SERIES_NAME   is null 
25   or DATASET_CATEGORY_NAME not in (select NAME from DATASET_CATEGORY)
26   or DATASET_SERIES_NAME   not in (select NAME from DATASET_SERIES)
27;
28
29
30/* REMOVE INACTIVE VIEWS NO IVVs: inactive views that do not have any values.  */
31delete from INDICATOR_VIEW
32where NAME not in (select distinct INDICATOR_VIEW_NAME from INDICATOR_VIEW_VALUE)
33  and (ACTIVE_FLAG is null or ACTIVE_FLAG != 'x')
34;
35/* INACTIVE ACTIVE VIEWS NO IVVs: inactive views that are active but do not have any values.  */
36update INDICATOR_VIEW set ACTIVE_FLAG = null
37where ACTIVE_FLAG = 'x' 
38  and NAME not in (select distinct INDICATOR_VIEW_NAME from INDICATOR_VIEW_VALUE)
39;
40
41
42/* REMOVE ORPHANED IVV: remove orphaned IVV records that do not have a matching DSC or DSS name and value */
43delete from INDICATOR_VIEW_VALUE where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW);
44
45delete from INDICATOR_VIEW_VALUE
46where DATASET_SERIES_NAME    is null 
47   or DATASET_CATEGORY_NAME  is null
48   or DATASET_CATEGORY_NAME  != (select DATASET_CATEGORY_NAME from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
49   or DATASET_SERIES_NAME    != (select DATASET_SERIES_NAME   from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
50   or DATASET_SERIES_VALUE   not in (select VALUE from DATASET_SERIES_VALUE dsv   where dsv.DATASET_SERIES_NAME   = INDICATOR_VIEW_VALUE.DATASET_SERIES_NAME)
51   or DATASET_CATEGORY_VALUE not in (select VALUE from DATASET_CATEGORY_VALUE dcv where dcv.DATASET_CATEGORY_NAME = INDICATOR_VIEW_VALUE.DATASET_CATEGORY_NAME)
52;
53
54
55/* REMOVE ORPHANDED DATASOURCES: Clean up the datasource intersection records.
56   NOTE: The DS to IP really isn't used.  DS to IPV are the key tables.
57*/
58delete from DATA_SOURCE_TO_INDICATOR where INDICATOR_NAME   not in (select NAME from INDICATOR)   or INDICATOR_NAME is null;
59delete from DATA_SOURCE_TO_INDICATOR where DATA_SOURCE_NAME not in (select NAME from DATA_SOURCE) or DATA_SOURCE_NAME is null;
60
61/* TEST AND REMOVE IPV DSs that do not exist */
62select * from DATA_SOURCE_TO_INDICATOR_VIEW where INDICATOR_VIEW_NAME not in (select distinct NAME from INDICATOR_VIEW) or INDICATOR_VIEW_NAME is null;
63delete   from DATA_SOURCE_TO_INDICATOR_VIEW where INDICATOR_VIEW_NAME not in (select distinct NAME from INDICATOR_VIEW) or INDICATOR_VIEW_NAME is null;
64
65select * from DATA_SOURCE_TO_INDICATOR_VIEW where DATA_SOURCE_NAME    not in (select NAME from DATA_SOURCE)    or DATA_SOURCE_NAME is null;
66delete   from DATA_SOURCE_TO_INDICATOR_VIEW where DATA_SOURCE_NAME    not in (select NAME from DATA_SOURCE)    or DATA_SOURCE_NAME is null;
67
68/* unused DSs.  Delete the ones Lois has tagged to be removed. */
69select * from DATA_SOURCE where NAME not in (select distinct DATA_SOURCE_NAME from DATA_SOURCE_TO_INDICATOR_VIEW);
70delete from DATA_SOURCE where name in 
71        ('ABLES', 'CDC_Flu', 'CDC_HAI', 'CntyHlthRankings',
72                'EMSTrack', 'HealthyHomes', 'TB', 'UNM_HSC',
73                'US_Census_CPS', 'US_Census_Hlth_Ins'
74        )
75;
76
77/* set the new v3 datasource.title column */
78update DATA_SOURCE set TITLE = substr(TEXT, 1, 100);
79
80/* basic test for IPV's that don't have a data source */
81select * from INDICATOR_VIEW where NAME not in (select distinct INDICATOR_VIEW_NAME from DATA_SOURCE_TO_INDICATOR_VIEW);
82
83
84
85/* CLEAN UP NAME: might want to remove these invalid chars from all IP, IPV, IPDS, DIM names:
86'.-+%$#@^*()=/'
87*/
88
89/* CLEAN IVV VALUES: clean all the IVV commas, *, ** */
90update INDICATOR_VIEW_VALUE set
91        VALUE       = replace(VALUE,       ',', ''),
92        NUMERATOR   = replace(NUMERATOR,   ',', ''),
93        DENOMINATOR = replace(DENOMINATOR, ',', ''),
94        LOWER_CONFIDENCE_VALUE = replace(LOWER_CONFIDENCE_VALUE, ',', ''),
95        UPPER_CONFIDENCE_VALUE = replace(UPPER_CONFIDENCE_VALUE, ',', '')
96;
97
98/* CLEAN Last trailing comma from IV TITLE */
99update INDICATOR_VIEW set TITLE = trim(trailing ',' from TITLE);
100
101
102
103/* RENAME DSS: do some renaming of DSS */
104/* do some testing to see if used
105        select count(*) from indicator_View where dataset_series_name in ('Admission', 'NM_TX');
106        select count(*) from DATASET_SERIES_VALUE where dataset_series_name in ('Admission', 'NM_TX');
107       
108*/
109update DATASET_SERIES       set NAME                = 'Admission' where NAME                = 'NM_TX';
110update DATASET_SERIES_VALUE set DATASET_SERIES_NAME = 'Admission' where DATASET_SERIES_NAME = 'NM_TX';
111update INDICATOR_VIEW       set DATASET_SERIES_NAME = 'Admission' where DATASET_SERIES_NAME = 'NM_TX';
112update INDICATOR_VIEW_VALUE set DATASET_SERIES_NAME = 'Admission' where DATASET_SERIES_NAME = 'NM_TX';
113
114update DATASET_SERIES       set NAME                = 'SexStateUS' where NAME                = 'NM_USBySex';
115update DATASET_SERIES_VALUE set DATASET_SERIES_NAME = 'SexStateUS' where DATASET_SERIES_NAME = 'NM_USBySex';
116update INDICATOR_VIEW       set DATASET_SERIES_NAME = 'SexStateUS' where DATASET_SERIES_NAME = 'NM_USBySex';
117update INDICATOR_VIEW_VALUE set DATASET_SERIES_NAME = 'SexStateUS' where DATASET_SERIES_NAME = 'NM_USBySex';
118
119update DATASET_SERIES       set NAME                = 'Fire' where NAME                = 'NM_USFire';
120update DATASET_SERIES_VALUE set DATASET_SERIES_NAME = 'Fire' where DATASET_SERIES_NAME = 'NM_USFire';
121update INDICATOR_VIEW       set DATASET_SERIES_NAME = 'Fire' where DATASET_SERIES_NAME = 'NM_USFire';
122update INDICATOR_VIEW_VALUE set DATASET_SERIES_NAME = 'Fire' where DATASET_SERIES_NAME = 'NM_USFire';
123
124/* NM doesn't have any of these - left in for future adopters */
125update DATASET_SERIES       set NAME                = 'GeoState' where NAME                in ('States', 'State', 'GeoUSStates');
126update DATASET_SERIES_VALUE set DATASET_SERIES_NAME = 'GeoState' where DATASET_SERIES_NAME in ('States', 'State', 'GeoUSStates');
127update INDICATOR_VIEW       set DATASET_SERIES_NAME = 'GeoState' where DATASET_SERIES_NAME in ('States', 'State', 'GeoUSStates');
128update INDICATOR_VIEW_VALUE set DATASET_SERIES_NAME = 'GeoState' where DATASET_SERIES_NAME in ('States', 'State', 'GeoUSStates');
129
130
131/* RENAME DSC: do some DSCs. */
132update DATASET_CATEGORY       set NAME                  = 'GeoState' where NAME                  in ('States', 'State', 'GeoUSStates');
133update DATASET_CATEGORY_VALUE set DATASET_CATEGORY_NAME = 'GeoState' where DATASET_CATEGORY_NAME in ('States', 'State', 'GeoUSStates');
134update INDICATOR_VIEW         set DATASET_CATEGORY_NAME = 'GeoState' where DATASET_CATEGORY_NAME in ('States', 'State', 'GeoUSStates');
135update INDICATOR_VIEW_VALUE   set DATASET_CATEGORY_NAME = 'GeoState' where DATASET_CATEGORY_NAME in ('States', 'State', 'GeoUSStates');
136
137
138/* SERIES = CAT: clean up some invalid NM entries. */
139update INDICATOR_VIEW set DATASET_SERIES_NAME = 'None'   
140where DATASET_SERIES_NAME = DATASET_CATEGORY_NAME
141;
142
143
144/* IV MAP_NAME to 'None': if not a Geo or a Geo but has a series */
145update INDICATOR_VIEW set MAP_NAME = 'None' 
146where DATASET_CATEGORY_NAME  not like 'Geo%'
147   or ((DATASET_CATEGORY_NAME like 'Geo%')
148                and
149           ((DATASET_SERIES_NAME is not null) and (DATASET_SERIES_NAME != 'None'))
150          )
151;
152
153/* IV MAP_DIMENSION_NAME: Set if MAP_NAME is not null or 'None' */
154update INDICATOR_VIEW set MAP_DIMENSION_NAME = DATASET_CATEGORY_NAME
155where DATASET_CATEGORY_NAME  like 'Geo%'
156  and ((MAP_NAME is not null) and (MAP_NAME != 'None'))
157;
158
159
160
161
162/* 2018 Rules (2013 was the last change).  They want an editor to be able
163        to open and complete it at any time (like 2013).  New change for 2018,
164        is to do away with approved and simply have open and completed.  Only
165        an admin can publish.  As such the modified/save is always available
166        and anyone on the page can set to open and completed.  Publish Option
167        only avail if complete.  As such the publishable status code is now "C".
168*/     
169update INDICATOR set STATUS_CODE = 'C' where STATUS_CODE = 'A';
170
171/* update IP definition to definition + cr/lf + cr/lf delimited definition + data_note */
172update INDICATOR set DEFINITION = concat(DEFINITION, char(10 using utf8), char(10 using utf8), DATA_NOTE)
173where DATA_NOTE is not null
174;
175
176/* CONCISE_TITLE: set the new v3 ip.CONCISE_TITLE column
177   IMPORTANT: MUST BE RAN BEFORE SETTING IV.TITLE.
178 */
179update INDICATOR i set CONCISE_TITLE = ifnull((select substring(TITLE, 1, 100) from INDICATOR_VIEW iv where iv.NAME = i.DEFAULT_INDICATOR_VIEW_NAME), '');
180
181
182/* SUB_TITLE is now TITLE.  Remove leading " ", "- ", trailing "," */
183update INDICATOR_VIEW set TITLE = substring(SUB_TITLE, 1, length(SUB_TITLE) - 1)
184where SUB_TITLE like '% '
185;
186update INDICATOR_VIEW set TITLE = substring(SUB_TITLE, 1, length(SUB_TITLE) - 1)
187where SUB_TITLE like '%,'
188;
189update INDICATOR_VIEW set TITLE = substring(SUB_TITLE, 2, length(SUB_TITLE) - 1)
190where SUB_TITLE like ' %'
191;
192update INDICATOR_VIEW set TITLE = substring(SUB_TITLE, 2, length(SUB_TITLE) - 1)
193where SUB_TITLE like '-%'
194;
195update INDICATOR_VIEW set TITLE = substring(SUB_TITLE, 2, length(SUB_TITLE) - 1)
196where SUB_TITLE like ' %'
197;
198
199
200/* set IV's that don't have any VALUEs to INACTIVE. */
201update INDICATOR_VIEW set ACTIVE_FLAG = null
202where not exists (select INDICATOR_VIEW_NAME from INDICATOR_VIEW_VALUE where INDICATOR_VIEW_NAME = NAME)
203;
204
205
206
207/* DEMOGRAPHICS: set the indicator demographic flag and clean NM out of the name */
208update INDICATOR set DEMOGRAPHIC_FLAG = 'x' where NAME like 'NMPopDemo%';
209
210update INDICATOR set NAME = substring(NAME, 3), DEFAULT_INDICATOR_VIEW_NAME = substring(DEFAULT_INDICATOR_VIEW_NAME, 3) where NAME like 'NMPopDemo%';
211update INDICATOR_VIEW    set INDICATOR_NAME = substring(INDICATOR_NAME, 3) where INDICATOR_NAME like 'NMPopDemo%';
212update USER_TO_INDICATOR set INDICATOR_NAME = substring(INDICATOR_NAME, 3) where INDICATOR_NAME like 'NMPopDemo%';              /* was INDICATOR_EDITOR */
213update INDICATOR_TO_INDICATOR set INDICATOR_NAME = substring(INDICATOR_NAME, 3) where INDICATOR_NAME like 'NMPopDemo%';
214update INDICATOR_TO_INDICATOR set RELATED_INDICATOR_NAME = substring(RELATED_INDICATOR_NAME, 3) where RELATED_INDICATOR_NAME like 'NMPopDemo%';
215update INDICATOR_TO_RELATION  set INDICATOR_NAME = substring(INDICATOR_NAME, 3) where INDICATOR_NAME like 'NMPopDemo%';
216update DATA_SOURCE_TO_INDICATOR set INDICATOR_NAME = substring(INDICATOR_NAME, 3) where INDICATOR_NAME like 'NMPopDemo%';
217
218update INDICATOR_VIEW set NAME = substring(NAME, 3) where NAME like 'NMPopDemo%';
219update INDICATOR_VIEW_VALUE set INDICATOR_VIEW_NAME = substring(INDICATOR_VIEW_NAME, 3) where INDICATOR_VIEW_NAME like 'NMPopDemo%';
220update DATA_SOURCE_TO_INDICATOR_VIEW set INDICATOR_VIEW_NAME = substring(INDICATOR_VIEW_NAME, 3) where INDICATOR_VIEW_NAME like 'NMPopDemo%';
221
222
223
224
225/* NM.US to NM_US: fix any inconsistenly named IPs, IPVs etc. */
226update INDICATOR_VIEW set NAME = replace(NAME, "NM.US", "NM_US") where NAME like "%NM.US%";
227update INDICATOR set DEFAULT_INDICATOR_VIEW_NAME = replace(DEFAULT_INDICATOR_VIEW_NAME, "NM.US", "NM_US") where DEFAULT_INDICATOR_VIEW_NAME like "%NM.US%";
228update INDICATOR_VIEW_VALUE set INDICATOR_VIEW_NAME = replace(INDICATOR_VIEW_NAME, "NM.US", "NM_US") where INDICATOR_VIEW_NAME like "%NM.US%";
229update DATA_SOURCE_TO_INDICATOR_VIEW set INDICATOR_VIEW_NAME = replace(INDICATOR_VIEW_NAME, "NM.US", "NM_US") where INDICATOR_VIEW_NAME like "%NM.US%";
230
231/* YEAR.NM_US: update some IV names to Year.NM_US */
232update INDICATOR_VIEW set NAME = replace(NAME, '.NM_US', '.Year.NM_US') 
233where NAME in ('AsthmaPrevAdult.Current.NM_US', 'AsthmaPrevAdultAdult.NM_US', 'AsthmaPrevChild.Current.NM_US', 'DiabHosp.NM_US.1vsAnyDiag', 'GenHlth.NM_US', 'ImmunPneumoAdult.NM_US', 'InjuryUnintenDeath.NM_US')
234;
235update INDICATOR_VIEW_VALUE set INDICATOR_VIEW_NAME = replace(INDICATOR_VIEW_NAME, '.NM_US', '.Year.NM_US') 
236where INDICATOR_VIEW_NAME in ('AsthmaPrevAdult.Current.NM_US', 'AsthmaPrevAdultAdult.NM_US', 'AsthmaPrevChild.Current.NM_US', 'DiabHosp.NM_US.1vsAnyDiag', 'GenHlth.NM_US', 'ImmunPneumoAdult.NM_US', 'InjuryUnintenDeath.NM_US')
237;
238update DATA_SOURCE_TO_INDICATOR_VIEW set INDICATOR_VIEW_NAME = replace(INDICATOR_VIEW_NAME, '.NM_US', '.Year.NM_US') 
239where INDICATOR_VIEW_NAME in ('AsthmaPrevAdult.Current.NM_US', 'AsthmaPrevAdultAdult.NM_US', 'AsthmaPrevChild.Current.NM_US', 'DiabHosp.NM_US.1vsAnyDiag', 'GenHlth.NM_US', 'ImmunPneumoAdult.NM_US', 'InjuryUnintenDeath.NM_US')
240;
241
242/* need to swap this one */
243update INDICATOR_VIEW set                               NAME='InjuryUnintenHosp.Year.NM_US' where                NAME='InjuryUnintenHosp.NM_US.Year'; 
244update INDICATOR_VIEW_VALUE set          INDICATOR_VIEW_NAME='InjuryUnintenHosp.Year.NM_US' where INDICATOR_VIEW_NAME='InjuryUnintenHosp.NM_US.Year'; 
245update DATA_SOURCE_TO_INDICATOR_VIEW set INDICATOR_VIEW_NAME='InjuryUnintenHosp.Year.NM_US' where INDICATOR_VIEW_NAME='InjuryUnintenHosp.NM_US.Year'; 
246
247
248/* ADD NM_US: rename that end with .Year to end with .Year.NM_US to be consist. */
249update INDICATOR_VIEW set NAME = replace(NAME, '.Year', '.Year.NM_US') 
250where NAME like '%.Year'
251  and NAME in
252        (
253                select INDICATOR_VIEW_NAME
254                from INDICATOR_VIEW_VALUE
255                where INDICATOR_VIEW_NAME like '%.Year'
256                  and (((DATASET_SERIES_NAME = 'NM_US') and (DATASET_SERIES_VALUE = 2))
257                                or 
258                           ((DATASET_CATEGORY_NAME = 'NM_US') and (DATASET_CATEGORY_VALUE = 2))
259                           )
260        )
261;
262/* MUST be done before updating the IVV table because of reliance */
263update DATA_SOURCE_TO_INDICATOR_VIEW set INDICATOR_VIEW_NAME = replace(INDICATOR_VIEW_NAME, '.Year', '.Year.NM_US') 
264where INDICATOR_VIEW_NAME like '%.Year'
265  and INDICATOR_VIEW_NAME in
266        (
267                select INDICATOR_VIEW_NAME
268                from INDICATOR_VIEW_VALUE
269                where INDICATOR_VIEW_NAME like '%.Year'
270                  and (((DATASET_SERIES_NAME = 'NM_US') and (DATASET_SERIES_VALUE = 2))
271                                or 
272                           ((DATASET_CATEGORY_NAME = 'NM_US') and (DATASET_CATEGORY_VALUE = 2))
273                           )
274        )
275;
276update INDICATOR_VIEW_VALUE set INDICATOR_VIEW_NAME = replace(INDICATOR_VIEW_NAME, '.Year', '.Year.NM_US') 
277where INDICATOR_VIEW_NAME like '%.Year'
278  and (((DATASET_SERIES_NAME = 'NM_US') and (DATASET_SERIES_VALUE = 2))
279                or 
280           ((DATASET_CATEGORY_NAME = 'NM_US') and (DATASET_CATEGORY_VALUE = 2))
281          )
282;
283
284/* RENAME STATE IVs: HlthInsurCover.Uninsured.GeoUSStates to "State" */
285update INDICATOR_VIEW set NAME = replace(NAME, '.GeoUSStates', '.State');
286update INDICATOR_VIEW_VALUE          set INDICATOR_VIEW_NAME = replace(INDICATOR_VIEW_NAME, '.GeoUSStates', '.State');
287update DATA_SOURCE_TO_INDICATOR_VIEW set INDICATOR_VIEW_NAME = replace(INDICATOR_VIEW_NAME, '.GeoUSStates', '.State');
288
289/*------------------------------ END OF FILE ------------------------------*/
290
Note: See TracBrowser for help on using the repository browser.