source: main/trunk/db/src/main/scripts/mysql/migrate/1_data-cleanup.sql @ 25198

Last change on this file since 25198 was 25198, checked in by GarthBraithwaite_STG, 3 months ago

db - updated the ds create to key off of SO3 and "Country" and cleaned out dup SO1 and SO3 usage. Added map_name cleanup to set to None vs DEFAULT. Added more select tests. Added test for VALUE_ATTR and null measure value.

File size: 3.7 KB
Line 
1/* General cleanup - adopter cleanup will likely have removed much of these */
2
3/* Older cleanup scripts that did not use the Kim/Lois temp tables needed to
4        remove orphaned DSC and DSS.  These tables are not used in that way any more
5        and thus don't need to clean up as these tables are deleted after migration
6        has been complteted.
7
8        Also, not removing apparent orphaned IVV records as these are manually set
9        by Kim for special splitting of views to have a "constant series".
10*/
11
12/* REMOVE ORPHANED IPVs: delete invalid or orphaned IV records */
13delete from INDICATOR_VIEW where INDICATOR_NAME not in (select NAME from INDICATOR);
14
15/* REMOVE ORPHANED IVV:
16        1) remove IVV that are not associated with an IV
17        2) remove orphaned IVV records that do not have a matching DSC or DSS name and value.
18
19        NOTE: possibility that could have IV records that *MIGHT* need to be deactivated
20        after clearing out IVV records.  When ran update IV only deactivated 1 record.
21*/
22delete from INDICATOR_VIEW_VALUE
23where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW)
24;
25
26
27/* Do NOT remove any DATASOURCES.  The datasource intersection records table is
28        not used and will be dropped when migration is finished.  The DATA_SOURCE
29        table is new and will have to be used values and other DS that have their
30        name changed.
31*/
32
33
34/* set the new v3 datasource.title column */
35update DATA_SOURCE set TITLE = substr(TEXT, 1, 100);
36
37
38/* CLEAN UP NAME: might want to remove these invalid chars from all IP, IPV, IPDS, DIM names:
39'.-+%$#@^*()=/'
40*/
41
42/* CLEAN IVV VALUES: clean all the IVV commas.  The * and ** are used later for
43   setting the value attribute.
44*/
45update INDICATOR_VIEW_VALUE set
46        VALUE       = replace(VALUE,       ',', ''),
47        NUMERATOR   = replace(NUMERATOR,   ',', ''),
48        DENOMINATOR = replace(DENOMINATOR, ',', ''),
49        LOWER_CONFIDENCE_VALUE = replace(LOWER_CONFIDENCE_VALUE, ',', ''),
50        UPPER_CONFIDENCE_VALUE = replace(UPPER_CONFIDENCE_VALUE, ',', '')
51;
52
53
54
55/* 2018 Rules (2013 was the last change).  They want an editor to be able
56        to open and complete it at any time (like 2013).  New change for 2018,
57        is to do away with approved and simply have open and completed.  Only
58        an admin can publish.  As such the modified/save is always available
59        and anyone on the page can set to open and completed.  Publish Option
60        only avail if complete.  As such the publishable status code is now "C".
61*/     
62update INDICATOR set STATUS_CODE = 'C' where STATUS_CODE = 'A';
63
64/* update IP definition to definition + cr/lf + cr/lf delimited definition + data_note */
65update INDICATOR set DEFINITION = concat(DEFINITION, char(10 using utf8), char(10 using utf8), DATA_NOTE)
66where DATA_NOTE is not null
67;
68
69/* CONCISE_TITLE: set the new v3 ip.CONCISE_TITLE column
70   IMPORTANT: MUST BE RAN BEFORE SETTING IV.TITLE.
71 */
72update INDICATOR i set CONCISE_TITLE = ifnull(
73        (select substring(TITLE, 1, 100) 
74     from INDICATOR_VIEW iv
75     where i.DEFAULT_INDICATOR_VIEW_NAME is not null 
76           and iv.INDICATOR_NAME = i.NAME
77       and iv.NAME = i.DEFAULT_INDICATOR_VIEW_NAME
78        )
79        , '')
80;
81
82/* DEMOGRAPHICS: set the indicator demographic flag */
83update INDICATOR set DEMOGRAPHIC_FLAG = 'x' where NAME like 'Demograph%';
84
85
86
87/* SUB_TITLE is now TITLE.  Remove leading " ", "- ", trailing "," */
88update INDICATOR_VIEW set TITLE = SUB_TITLE;
89
90update INDICATOR_VIEW set TITLE = trim(both     ' ' from TITLE);
91update INDICATOR_VIEW set TITLE = trim(leading  '-' from TITLE);
92update INDICATOR_VIEW set TITLE = trim(trailing ',' from TITLE);
93update INDICATOR_VIEW set TITLE = trim(both     ' ' from TITLE);
94
95/*------------------------------ END OF FILE ------------------------------*/
Note: See TracBrowser for help on using the repository browser.