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

Last change on this file since 25052 was 25052, checked in by GarthBraithwaite_STG, 4 months ago

db - renamed script files to match updated steps. Minor tweaks.

File size: 4.5 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        NOTE: When mixing myisam tables need to use "collate" so that character set
9        data can be compared.  If using .sql exports then not needed.
10*/
11
12
13/* REMOVE ORPHANED IPVs: delete invalid or orphaned IV records */
14delete from INDICATOR_VIEW where INDICATOR_NAME not in (select NAME from INDICATOR);
15delete from INDICATOR_VIEW
16where DATASET_CATEGORY_NAME is null 
17   or DATASET_CATEGORY_NAME not in (select NAME from DATASET_CATEGORY)
18   or DATASET_SERIES_NAME   not in (select NAME from DATASET_SERIES)
19;
20
21/* REMOVE ORPHANED IVV:
22        1) remove IVV that are not associated with an IV
23        2) remove orphaned IVV records that do not have a matching DSC or DSS name and value.
24
25        NOTE: possibility that could have IV records that *MIGHT* need to be deactivated
26        after clearing out IVV records.  When ran update IV only deactivated 1 record.
27*/
28delete from INDICATOR_VIEW_VALUE
29where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW)
30;
31
32delete from INDICATOR_VIEW_VALUE
33where DATASET_CATEGORY_NAME  is null
34   or DATASET_CATEGORY_NAME   != (select DATASET_CATEGORY_NAME from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
35   or DATASET_SERIES_NAME     != (select DATASET_SERIES_NAME   from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
36   or DATASET_SERIES_VALUE   not in (select VALUE from DATASET_SERIES_VALUE dsv   where dsv.DATASET_SERIES_NAME   = INDICATOR_VIEW_VALUE.DATASET_SERIES_NAME)
37   or DATASET_CATEGORY_VALUE not in (select VALUE from DATASET_CATEGORY_VALUE dcv where dcv.DATASET_CATEGORY_NAME = INDICATOR_VIEW_VALUE.DATASET_CATEGORY_NAME)
38;
39
40
41/* Do NOT remove any DATASOURCES.  The datasource intersection records table is
42        not used and will be dropped when migration is finished.  The DATA_SOURCE
43        table is new and will have to be used values and other DS that have their
44        name changed.
45*/
46
47
48/* set the new v3 datasource.title column */
49update DATA_SOURCE set TITLE = substr(TEXT, 1, 100);
50
51
52/* CLEAN UP NAME: might want to remove these invalid chars from all IP, IPV, IPDS, DIM names:
53'.-+%$#@^*()=/'
54*/
55
56/* CLEAN IVV VALUES: clean all the IVV commas.  The * and ** are used later for
57   setting the value attribute.
58*/
59update INDICATOR_VIEW_VALUE set
60        VALUE       = replace(VALUE,       ',', ''),
61        NUMERATOR   = replace(NUMERATOR,   ',', ''),
62        DENOMINATOR = replace(DENOMINATOR, ',', ''),
63        LOWER_CONFIDENCE_VALUE = replace(LOWER_CONFIDENCE_VALUE, ',', ''),
64        UPPER_CONFIDENCE_VALUE = replace(UPPER_CONFIDENCE_VALUE, ',', '')
65;
66
67
68
69/* 2018 Rules (2013 was the last change).  They want an editor to be able
70        to open and complete it at any time (like 2013).  New change for 2018,
71        is to do away with approved and simply have open and completed.  Only
72        an admin can publish.  As such the modified/save is always available
73        and anyone on the page can set to open and completed.  Publish Option
74        only avail if complete.  As such the publishable status code is now "C".
75*/     
76update INDICATOR set STATUS_CODE = 'C' where STATUS_CODE = 'A';
77
78/* update IP definition to definition + cr/lf + cr/lf delimited definition + data_note */
79update INDICATOR set DEFINITION = concat(DEFINITION, char(10 using utf8), char(10 using utf8), DATA_NOTE)
80where DATA_NOTE is not null
81;
82
83/* CONCISE_TITLE: set the new v3 ip.CONCISE_TITLE column
84   IMPORTANT: MUST BE RAN BEFORE SETTING IV.TITLE.
85 */
86update INDICATOR i set CONCISE_TITLE = ifnull(
87        (select substring(TITLE, 1, 100) 
88     from INDICATOR_VIEW iv
89     where i.DEFAULT_INDICATOR_VIEW_NAME is not null 
90           and iv.INDICATOR_NAME = i.NAME
91       and iv.NAME = i.DEFAULT_INDICATOR_VIEW_NAME
92        )
93        , '')
94;
95
96/* DEMOGRAPHICS: set the indicator demographic flag */
97update INDICATOR set DEMOGRAPHIC_FLAG = 'x' where NAME like 'Demograph%';
98
99
100
101/* SUB_TITLE is now TITLE.  Remove leading " ", "- ", trailing "," */
102update INDICATOR_VIEW set TITLE = SUB_TITLE;
103
104update INDICATOR_VIEW set TITLE = trim(both     ' ' from TITLE);
105update INDICATOR_VIEW set TITLE = trim(leading  '-' from TITLE);
106update INDICATOR_VIEW set TITLE = trim(trailing ',' from TITLE);
107update INDICATOR_VIEW set TITLE = trim(both     ' ' from TITLE);
108
109/*------------------------------ END OF FILE ------------------------------*/
Note: See TracBrowser for help on using the repository browser.