Changeset 25033 in main


Ignore:
Timestamp:
04/12/22 23:55:04 (4 months ago)
Author:
GarthBraithwaite_STG
Message:

db - initial whack at new migration scripts and steps.

Location:
trunk/db/src/main/scripts/mysql/migrate
Files:
1 added
6 edited

Legend:

Unmodified
Added
Removed

  • trunk/db/src/main/scripts/mysql/migrate/0_data-cleanup.sql

    r25032 r25033  
    11/* General cleanup - adopter cleanup will likely have removed much of these */
    22
    3 /* Use collate so that character set data can be compared.
    4         Get rid of cats and series that are not used in views or have any values.
     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.
    510*/
    6 delete from DATASET_CATEGORY where NAME collate SQL_Latin1_General_CP1_CI_AS not in (select distinct DATASET_CATEGORY_NAME from INDICATOR_VIEW);
    7 delete from DATASET_CATEGORY where NAME collate SQL_Latin1_General_CP1_CI_AS not in (select distinct DATASET_CATEGORY_NAME from DATASET_CATEGORY_VALUE);
    8 
    9 delete from DATASET_SERIES   where NAME collate SQL_Latin1_General_CP1_CI_AS not in (select distinct DATASET_SERIES_NAME   from INDICATOR_VIEW);
    10 delete from DATASET_SERIES   where NAME collate SQL_Latin1_General_CP1_CI_AS not in (select distinct DATASET_SERIES_NAME   from DATASET_SERIES_VALUE);
    11 
    12 delete from DATASET_CATEGORY_VALUE where DATASET_CATEGORY_NAME is null or DATASET_CATEGORY_NAME collate SQL_Latin1_General_CP1_CI_AS not in (select NAME from DATASET_CATEGORY);
    13 delete from DATASET_SERIES_VALUE   where DATASET_SERIES_NAME   is null or DATASET_SERIES_NAME   collate SQL_Latin1_General_CP1_CI_AS not in (select NAME from DATASET_SERIES);
    14 
    15 
    16 /* REMOVE IPs that don't have any views */
    17 delete from INDICATOR where NAME collate SQL_Latin1_General_CP1_CI_AS not in (select INDICATOR_NAME from INDICATOR_VIEW);
    1811
    1912
    2013/* REMOVE ORPHANED IPVs: delete invalid or orphaned IV records */
    21 delete from INDICATOR_VIEW where INDICATOR_NAME collate SQL_Latin1_General_CP1_CI_AS not in (select NAME from INDICATOR);
     14delete from INDICATOR_VIEW where INDICATOR_NAME not in (select NAME from INDICATOR);
    2215delete from INDICATOR_VIEW
    2316where DATASET_CATEGORY_NAME is null
    24    or DATASET_CATEGORY_NAME collate SQL_Latin1_General_CP1_CI_AS not in (select NAME from DATASET_CATEGORY)
    25    or DATASET_SERIES_NAME   collate SQL_Latin1_General_CP1_CI_AS not in (select NAME from DATASET_SERIES)
     17   or DATASET_CATEGORY_NAME not in (select NAME from DATASET_CATEGORY)
     18   or DATASET_SERIES_NAME   not in (select NAME from DATASET_SERIES)
    2619;
    27 
    28 /* REMOVE IVs that don't have any IVVs */
    29 delete from INDICATOR_VIEW
    30 where NAME collate SQL_Latin1_General_CP1_CI_AS not in (select distinct INDICATOR_VIEW_NAME from INDICATOR_VIEW_VALUE)
    31 ;
    32 
    3320
    3421/* REMOVE ORPHANED IVV:
    … …  
    4027*/
    4128delete from INDICATOR_VIEW_VALUE
    42 where INDICATOR_VIEW_NAME collate SQL_Latin1_General_CP1_CI_AS not in (select NAME from INDICATOR_VIEW)
     29where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW)
    4330;
    4431
    4532delete from INDICATOR_VIEW_VALUE
    4633where DATASET_CATEGORY_NAME  is null
    47    or DATASET_CATEGORY_NAME  collate SQL_Latin1_General_CP1_CI_AS != (select DATASET_CATEGORY_NAME from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
    48    or DATASET_SERIES_NAME    collate SQL_Latin1_General_CP1_CI_AS != (select DATASET_SERIES_NAME   from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
    49    or DATASET_SERIES_VALUE   collate SQL_Latin1_General_CP1_CI_AS not in (select VALUE from DATASET_SERIES_VALUE dsv   where dsv.DATASET_SERIES_NAME   = INDICATOR_VIEW_VALUE.DATASET_SERIES_NAME)
    50    or DATASET_CATEGORY_VALUE collate SQL_Latin1_General_CP1_CI_AS not in (select VALUE from DATASET_CATEGORY_VALUE dcv where dcv.DATASET_CATEGORY_NAME = INDICATOR_VIEW_VALUE.DATASET_CATEGORY_NAME)
     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)
    5138;
    5239
    … …  
    5542   NOTE: The DS to IP really isn't used.  DS to IPV are the key tables.
    5643*/
    57 delete from DATA_SOURCE_TO_INDICATOR where INDICATOR_NAME   collate SQL_Latin1_General_CP1_CI_AS not in (select NAME from INDICATOR)   or INDICATOR_NAME is null;
    58 delete from DATA_SOURCE_TO_INDICATOR where DATA_SOURCE_NAME collate SQL_Latin1_General_CP1_CI_AS not in (select NAME from DATA_SOURCE) or DATA_SOURCE_NAME is null;
    59 delete from DATA_SOURCE_TO_INDICATOR_VIEW where INDICATOR_VIEW_NAME collate SQL_Latin1_General_CP1_CI_AS not in (select distinct NAME from INDICATOR_VIEW) or INDICATOR_VIEW_NAME is null;
    60 delete from DATA_SOURCE_TO_INDICATOR_VIEW where DATA_SOURCE_NAME    collate SQL_Latin1_General_CP1_CI_AS not in (select NAME from DATA_SOURCE)    or DATA_SOURCE_NAME is null;
    61 
    62 /* unused DSs */
    63 delete from DATA_SOURCE where
    64         NAME collate SQL_Latin1_General_CP1_CI_AS not in (select distinct DATA_SOURCE_NAME from DATA_SOURCE_TO_INDICATOR_VIEW)
    65         and
    66         NAME collate SQL_Latin1_General_CP1_CI_AS not in (select distinct DATA_SOURCE_NAME from DATA_SOURCE_TO_INDICATOR)
    67 ;
    68 
     44delete from DATA_SOURCE_TO_INDICATOR where INDICATOR_NAME   not in (select NAME from INDICATOR)   or INDICATOR_NAME is null;
     45delete from DATA_SOURCE_TO_INDICATOR where DATA_SOURCE_NAME not in (select NAME from DATA_SOURCE) or DATA_SOURCE_NAME is null;
     46delete from DATA_SOURCE_TO_INDICATOR_VIEW where INDICATOR_VIEW_NAME not in (select distinct NAME from INDICATOR_VIEW) or INDICATOR_VIEW_NAME is null;
     47delete from DATA_SOURCE_TO_INDICATOR_VIEW where DATA_SOURCE_NAME    not in (select NAME from DATA_SOURCE)    or DATA_SOURCE_NAME is null;
    6948
    7049
    7150/* set the new v3 datasource.title column */
    7251update DATA_SOURCE set TITLE = substr(TEXT, 1, 100);
    73 
    74 
    75 
    76 
    77 
    78 /* IMPORTANT NOTE: Can't do any orphaned DS to IVV cleanup here because the new
    79         intersection table is using the new v3 IPV name and the IP/IPV rename has
    80         not been ran yet.  This cleanup will need to be ran post migration.
    81 */
    82 
    8352
    8453
    … …  
    141110
    142111/*------------------------------ END OF FILE ------------------------------*/
    143 

  • trunk/db/src/main/scripts/mysql/migrate/1_data-ip_ipv_rename.sql

    r25029 r25033  
    2525update INDICATOR set DEFAULT_INDICATOR_VIEW_NAME = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where DEFAULT_INDICATOR_VIEW_NAME = NAME2), DEFAULT_INDICATOR_VIEW_NAME);
    2626
    27 update INDICATOR_TO_INDICATOR   set INDICATOR_NAME         = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where INDICATOR_NAME = NAME2) , INDICATOR_NAME);
    28 update INDICATOR_TO_INDICATOR   set RELATED_INDICATOR_NAME = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where RELATED_INDICATOR_NAME = NAME2), RELATED_INDICATOR_NAME);
     27update INDICATOR_TO_INDICATOR set INDICATOR_NAME         = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where INDICATOR_NAME = NAME2) , INDICATOR_NAME);
     28update INDICATOR_TO_INDICATOR set RELATED_INDICATOR_NAME = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where RELATED_INDICATOR_NAME = NAME2), RELATED_INDICATOR_NAME);
    2929
    30 update INDICATOR_TO_RELATION    set INDICATOR_NAME         = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where INDICATOR_NAME = NAME2), INDICATOR_NAME);
     30update INDICATOR_TO_RELATION  set INDICATOR_NAME         = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where INDICATOR_NAME = NAME2), INDICATOR_NAME);
    3131
    32 update INDICATOR_TO_INDICATOR_SET set INDICATOR_NAME       = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where INDICATOR_NAME = NAME2), INDICATOR_NAME);
     32update INDICATOR_TO_INDICATOR_SET set INDICATOR_NAME     = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where INDICATOR_NAME = NAME2), INDICATOR_NAME);
    3333
    34 update USER_TO_INDICATOR        set INDICATOR_NAME         = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where INDICATOR_NAME = NAME2), INDICATOR_NAME);
     34update USER_TO_INDICATOR      set INDICATOR_NAME         = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where INDICATOR_NAME = NAME2), INDICATOR_NAME);
    3535
    3636
    … …  
    6464select * from INDICATOR_VIEW where ACTIVE_FLAG = 'x' and not exists (select 1 from INDICATOR_VIEW_VALUE where INDICATOR_VIEW_NAME = NAME and ACTIVE_FLAG = 'x');
    6565
    66 
    67 
    68 
    69 
    70 
    71 
    72 split:
    73 4 columns 2.3 ip name to new 3 ip name, same for views...
    74 
    75 insert into INDICATOR
    76         NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME,
    77         TITLE, CONCISE_TITLE,
    78         DEFINITION, NUMERATOR, DENOMINATOR, DATA_NOTE, DATA_ISSUES, DATA_AS_OF_DATE,
    79         WHY_IMPORTANT, HEALTHY_PEOPLE_OBJECTIVE_NAME, OTHER_OBJECTIVE, EVIDENCE_BASED_PRACTICES,
    80         HOW_DOING, HOW_WE_COMPARE, WHAT_DOING, OTHER_PROGRAM_INFORMATION,
    81         MORE_RESOURCES_TITLE, MORE_RESOURCES, SERVICES_AVAILABLE_TO_PUBLIC,
    82         ORG_UNIT_NAME, OWNER_USER_ID, STATUS_CODE, STATUS_DATE, PUBLISHED_DATE,
    83         DEMOGRAPHIC_FLAG, NOT_SELECTABLE_FLAG,  FOOTNOTE_REFERENCES,
    84         NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
    85 select t.NEW_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME,
    86         TITLE, CONCISE_TITLE,
    87         DEFINITION, NUMERATOR, DENOMINATOR, DATA_NOTE, DATA_ISSUES, DATA_AS_OF_DATE,
    88         WHY_IMPORTANT, HEALTHY_PEOPLE_OBJECTIVE_NAME, OTHER_OBJECTIVE, EVIDENCE_BASED_PRACTICES,
    89         HOW_DOING, HOW_WE_COMPARE, WHAT_DOING, OTHER_PROGRAM_INFORMATION,
    90         MORE_RESOURCES_TITLE, MORE_RESOURCES, SERVICES_AVAILABLE_TO_PUBLIC,
    91         ORG_UNIT_NAME, OWNER_USER_ID, STATUS_CODE, STATUS_DATE, PUBLISHED_DATE,
    92         DEMOGRAPHIC_FLAG, NOT_SELECTABLE_FLAG,  FOOTNOTE_REFERENCES,
    93         NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
    94 from INDICATOR i, TMP_SPLIT_IP t
    95 where i.NAME = t.CURRENT_NAME
    96 ;
    97 
    98 insert into INDICATOR_VIEW
    99         NAME, INDICATOR_NAME, DESCRIPTION, TITLE, SUB_TITLE,
    100         PERIOD_TITLE, Y_TITLE,
    101         DATASET_SERIES_NAME, DATASET_CATEGORY_NAME,
    102         PERIOD_DIMENSION_NAME, SERIES_DIMENSION_NAME, CATEGORY_DIMENSION_NAME, CONSTANT_DIMENSION_NAME
    103         MAP_DIMENSION_NAME,
    104         CHART_NAME, CHART_NARRATIVE, CHART_TITLE_OVERRIDE,
    105         MAP_NAME, MAP_NARRATIVE, MAP_TITLE_OVERRIDE,
    106         VALUE_TYPE_NAME, MEASURE_NAME, MEASURE_TITLE_OVERRIDE,
    107         TARGET_VALUE,
    108         DATA_NOTE, NUMERATOR_OVERRIDE, DENOMINATOR_OVERRIDE
    109         EXTERNAL_DATAVIZ_TYPE, EXTERNAL_DATAVIZ_TITLE, EXTERNAL_DATAVIZ_VALUE, EXTERNAL_DATAVIZ_NARRATIVE,
    110         EXTERNAL_CONTENT_URL, EXTERNAL_CONTENT_SELECTOR, EXTERNAL_CONTENT_DEST_SELECTOR,
    111         VIEW_TEMPLATE_NAME,
    112         NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
    113 select tmp.NEW_NAME, xyz
    114 from INDICATOR_VIEW iv, TMP_SPLIT_IP t
    115 where iv.NAME = CURRENT_NAME
    116 ;
    117 
    118 insert into INDICATOR_VIEW_VALUE
    119         NAME, INDICATOR_NAME, DESCRIPTION, TITLE, SUB_TITLE,
    120         PERIOD_TITLE, Y_TITLE,
    121         DATASET_SERIES_NAME, DATASET_CATEGORY_NAME,
    122         PERIOD_DIMENSION_NAME, SERIES_DIMENSION_NAME, CATEGORY_DIMENSION_NAME, CONSTANT_DIMENSION_NAME
    123         MAP_DIMENSION_NAME,
    124         CHART_NAME, CHART_NARRATIVE, CHART_TITLE_OVERRIDE,
    125         MAP_NAME, MAP_NARRATIVE, MAP_TITLE_OVERRIDE,
    126         VALUE_TYPE_NAME, MEASURE_NAME, MEASURE_TITLE_OVERRIDE,
    127         TARGET_VALUE,
    128         DATA_NOTE, NUMERATOR_OVERRIDE, DENOMINATOR_OVERRIDE
    129         EXTERNAL_DATAVIZ_TYPE, EXTERNAL_DATAVIZ_TITLE, EXTERNAL_DATAVIZ_VALUE, EXTERNAL_DATAVIZ_NARRATIVE,
    130         EXTERNAL_CONTENT_URL, EXTERNAL_CONTENT_SELECTOR, EXTERNAL_CONTENT_DEST_SELECTOR,
    131         VIEW_TEMPLATE_NAME,
    132         NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
    133 select tmp.NEW_NAME, xyz
    134 from INDICATOR_VIEW_VALUE ivv, TMP_SPLIT_IP t
    135 where ivv.INDICATOR_VIEWNAME = CURRENT_NAME
    136 ;
    137 
    138 
    139 indicator_to_relation
    140 INDICATOR_NAME, RELATION_NAME, TEXT
    141 
    142 indicator_to_indicator_set
    143 INDICATOR_NAME, INDICATOR_SET_NAME, SORT_ORDER
    144 
    145 indicator_to_indicator
    146 INDICATOR_NAME, RELATION_NAME, RELATED_INDICATOR_NAME, SORT_ORDER
    147 
    148 data_source_to_indicator_view
    149 DATA_SOURCE_NAME, INDICATOR_VIEW_NAME, SORT_ORDER
    150 
    151 user_to_indicator
    152 INDICATOR_NAME, USER_ID
    153 
    154 
    15566/*------------------------------ END OF FILE ------------------------------*/

  • trunk/db/src/main/scripts/mysql/migrate/4_data-iv_dimensions.sql

    r25032 r25033  
    1212select * from TMP_IPV_CONSTANT_DIM_NAME;
    1313
    14 
    15 /* Clean out new IV dim names and populate using above mappings */
    16 update INDICATOR_VIEW
    17 set CATEGORY_DIMENSION_NAME = null,
    18         SERIES_DIMENSION_NAME   = null,
    19         CONSTANT_DIMENSION_NAME = null,
    20         PERIOD_DIMENSION_NAME   = '',
    21         MAP_DIMENSION_NAME      = null
    22 ;
    2314
    2415/* Most of these will be correct but some are split into several new dims which

  • trunk/db/src/main/scripts/mysql/migrate/tab_c-misc.sql

    r25032 r25033  
    1 /* New v3 validation and intersection tables */
     1/* Misc new v3 validation and intersection tables */
    22
    33create table MEASURE(
    … …  
    1717    primary key (NAME)
    1818);
     19
    1920create table VALUE_ATTRIBUTE(
    2021  NAME                            varchar  (100)  not null,
    … …  
    3132    primary key (NAME)
    3233);
     34
    3335create table ANCILLARY_VALUE(
    3436  NAME                            varchar  (100)  not null,
    … …  
    4648    primary key (NAME)
    4749);
     50
    4851
    4952create table DIMENSION(
    … …  
    8386);
    8487
     88
    8589create table TOPIC(
    8690  NAME                            varchar  (100)  not null,
    … …  
    106110    primary key (INDICATOR_NAME, TOPIC_NAME)
    107111);
     112
    108113
    109114/* subset of datasets for an IPV based on the assoc IP name and IPV Measure */
    … …  
    135140    unique (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
    136141);
     142
    137143
    138144create table INDICATOR_SET(

  • trunk/db/src/main/scripts/mysql/migrate/tab_c-tmp.sql

    r25032 r25033  
    9292        all NAMEs are v3 names.
    9393*/
    94 drop table if exists TMP_SPLIT_IP;
    95 create table if not exists TMP_SPLIT_IP_OLD_NAME_IP_NEW_NAME (
    96   CURRENT_NAME                    varchar  (100)  not null,
    97   NEW_NAME                        varchar  (100)  not null,
    98   constraint TMP_SPLIT_IP_PK
    99     primary key (CURRENT_NAME, NEW_NAME)
     94drop table if exists TMP_SPLIT_IP_IPV;
     95create table if not exists TMP_SPLIT_IP_IPV (
     96  SOURCE_IP_NAME                  varchar  (100)  not null,
     97  SOURCE_IPV_NAME                 varchar  (100)  not null,
     98  NEW_IP_NAME                     varchar  (100)  not null,
     99  NEW_IPV_NAME                    varchar  (100)  not null,
     100  constraint TMP_SPLIT_IP_IPV_PK
     101    primary key (SOURCE_IPV_NAME, NEW_IPV_NAME)
    100102);
    101103

  • trunk/db/src/main/scripts/mysql/migrate/update.sql

    r25032 r25033  
    114114
    115115
    116 /* ---------------- Step 1.x - Prep 2.3 Migration */
    117         use migration;
     116/* ---------------- Step 1.x - Prep 2.3 Migration - Get 2.3 DB */
    118117
    119118/* run adopter specific data cleanup - remove orphaned records */
    … …  
    137136
    138137
    139 /* ---------------- Step 2.x - Manual Migration Data Updates */
    140 /* Kim will populate specific validation tables:
     138/* ---------------- Step 2.x - Manual Migration Data Updates - Send to Kim */
     139/* Kim will populate specific validation tables and mod IV and IVV series/const:
    141140        value_type
    142141        measure
    … …  
    147146
    148147
    149 /* ---------------- Step 3.x - Run Migration Scripts */
    150         /*
    151                 remove 99 and 999 SO IP and IPVs,
    152                 unused and orphanded I, IV, IVV,
    153                 DSC DSCV, DSS, DSSV and few misc updates
    154         */
    155         0_data-cleanup.sql     
     148/* ---------------- Step 3.x - Run Migration Scripts - Get DB from Kim */
     149        0_data-cleanup.sql                                      /* Rerun basic orphaned records cleanup and few misc updates. */
    156150        1_data-ip_ipv_rename.sql               
     151        2_data-ip_ipv_split.sql         
    157152
    158153        2_data-iv_measure_name.sql                      /* Kims mapping with MEASURE_TITLE_OVERRIDE IVN, MN, CMT */
    159154        3_data-data_source_to_ipv.sql           /* Kims mapping with SO */
    160 
    161155        4_data-iv_dimensions.sql                /* Kim's mapping - will send when finished DSC & S : VALUE to DIM NAME : VALUE */
    162156        5_data-ivv_dimensions.sql               
Note: See TracChangeset for help on using the changeset viewer.