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

/* General cleanup - adopter cleanup will likely have removed much of these */

/* Older cleanup scripts that did not use the Kim/Lois temp tables needed to 
        remove orphaned DSC and DSS.  These tables are not used in that way any more
        and thus don't need to clean up as these tables are deleted after migration
        has been complteted.

        NOTE: When mixing myisam tables need to use "collate" so that character set 
        data can be compared.  If using .sql exports then not needed.
*/


/* REMOVE ORPHANED IPVs: delete invalid or orphaned IV records */
delete from INDICATOR_VIEW where INDICATOR_NAME not in (select NAME from INDICATOR);
delete from INDICATOR_VIEW 
where DATASET_CATEGORY_NAME is null 
   or DATASET_CATEGORY_NAME not in (select NAME from DATASET_CATEGORY)
   or DATASET_SERIES_NAME   not in (select NAME from DATASET_SERIES)
;

/* REMOVE ORPHANED IVV: 
        1) remove IVV that are not associated with an IV
        2) remove orphaned IVV records that do not have a matching DSC or DSS name and value.

        NOTE: possibility that could have IV records that *MIGHT* need to be deactivated
        after clearing out IVV records.  When ran update IV only deactivated 1 record.
*/
delete from INDICATOR_VIEW_VALUE 
where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW)
;

delete from INDICATOR_VIEW_VALUE 
where DATASET_CATEGORY_NAME  is null
   or DATASET_CATEGORY_NAME   != (select DATASET_CATEGORY_NAME from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
   or DATASET_SERIES_NAME     != (select DATASET_SERIES_NAME   from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
   or DATASET_SERIES_VALUE   not in (select VALUE from DATASET_SERIES_VALUE dsv   where dsv.DATASET_SERIES_NAME   = INDICATOR_VIEW_VALUE.DATASET_SERIES_NAME)
   or DATASET_CATEGORY_VALUE not in (select VALUE from DATASET_CATEGORY_VALUE dcv where dcv.DATASET_CATEGORY_NAME = INDICATOR_VIEW_VALUE.DATASET_CATEGORY_NAME)
;


/* Do NOT remove any DATASOURCES.  The datasource intersection records table is
        not used and will be dropped when migration is finished.  The DATA_SOURCE
        table is new and will have to be used values and other DS that have their 
        name changed.
*/


/* set the new v3 datasource.title column */
update DATA_SOURCE set TITLE = substr(TEXT, 1, 100);


/* CLEAN UP NAME: might want to remove these invalid chars from all IP, IPV, IPDS, DIM names:
'.-+%$#@^*()=/'
*/

/* CLEAN IVV VALUES: clean all the IVV commas.  The * and ** are used later for 
   setting the value attribute.
*/
update INDICATOR_VIEW_VALUE set
        VALUE       = replace(VALUE,       ',', ''),
        NUMERATOR   = replace(NUMERATOR,   ',', ''),
        DENOMINATOR = replace(DENOMINATOR, ',', ''),
        LOWER_CONFIDENCE_VALUE = replace(LOWER_CONFIDENCE_VALUE, ',', ''),
        UPPER_CONFIDENCE_VALUE = replace(UPPER_CONFIDENCE_VALUE, ',', '')
;



/* 2018 Rules (2013 was the last change).  They want an editor to be able 
        to open and complete it at any time (like 2013).  New change for 2018, 
        is to do away with approved and simply have open and completed.  Only 
        an admin can publish.  As such the modified/save is always available 
        and anyone on the page can set to open and completed.  Publish Option 
        only avail if complete.  As such the publishable status code is now "C".
*/      
update INDICATOR set STATUS_CODE = 'C' where STATUS_CODE = 'A';

/* update IP definition to definition + cr/lf + cr/lf delimited definition + data_note */
update INDICATOR set DEFINITION = concat(DEFINITION, char(10 using utf8), char(10 using utf8), DATA_NOTE)
where DATA_NOTE is not null
;

/* CONCISE_TITLE: set the new v3 ip.CONCISE_TITLE column
   IMPORTANT: MUST BE RAN BEFORE SETTING IV.TITLE.
 */
update INDICATOR i set CONCISE_TITLE = ifnull(
        (select substring(TITLE, 1, 100) 
     from INDICATOR_VIEW iv 
     where i.DEFAULT_INDICATOR_VIEW_NAME is not null 
           and iv.INDICATOR_NAME = i.NAME 
       and iv.NAME = i.DEFAULT_INDICATOR_VIEW_NAME
        )
        , '')
;

/* DEMOGRAPHICS: set the indicator demographic flag */
update INDICATOR set DEMOGRAPHIC_FLAG = 'x' where NAME like 'Demograph%';



/* SUB_TITLE is now TITLE.  Remove leading " ", "- ", trailing "," */
update INDICATOR_VIEW set TITLE = SUB_TITLE;

update INDICATOR_VIEW set TITLE = trim(both     ' ' from TITLE);
update INDICATOR_VIEW set TITLE = trim(leading  '-' from TITLE);
update INDICATOR_VIEW set TITLE = trim(trailing ',' from TITLE);
update INDICATOR_VIEW set TITLE = trim(both     ' ' from TITLE);

/*------------------------------ END OF FILE ------------------------------*/