Changeset 25052 in main


Ignore:
Timestamp:
04/13/22 21:39:28 (4 months ago)
Author:
GarthBraithwaite_STG
Message:

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

Location:
trunk/db/src/main/scripts/mysql/migrate
Files:
1 edited
9 moved

Legend:

Unmodified
Added
Removed

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

    r25051 r25052  
    3939
    4040
    41 /* REMOVE ORPHANDED DATASOURCES: Clean up the datasource intersection records.
    42    NOTE: The DS to IP really isn't used.  DS to IPV are the key tables.
     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.
    4345*/
    44 delete from DATA_SOURCE_TO_INDICATOR where INDICATOR_NAME   not in (select NAME from INDICATOR)   or INDICATOR_NAME is null;
    45 delete from DATA_SOURCE_TO_INDICATOR where DATA_SOURCE_NAME not in (select NAME from DATA_SOURCE) or DATA_SOURCE_NAME is null;
    46 delete from DATA_SOURCE_TO_INDICATOR_VIEW where INDICATOR_VIEW_NAME not in (select distinct NAME from INDICATOR_VIEW) or INDICATOR_VIEW_NAME is null;
    47 delete from DATA_SOURCE_TO_INDICATOR_VIEW where DATA_SOURCE_NAME    not in (select NAME from DATA_SOURCE)    or DATA_SOURCE_NAME is null;
    4846
    4947

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

    r25051 r25052  
    1 /* IP AND IPV Renames.  Uses temp tables populated from Kim's excel sheets.
     1/* IP AND IPV NAME changes.  Uses Kim's TMP_IP23NAME_IP3NAME table.
    22        INDICATOR: NAME, DEFAULT_INDICATOR_VIEW_NAME
    33        INDICATOR_VIEW: NAME, INDICATOR_NAME

  • trunk/db/src/main/scripts/mysql/migrate/3_data-ip_ipv_split.sql

    r25051 r25052  
    1 /* IP AND IPV Renames.  Uses temp tables populated from Kim's excel sheets.
    2         INDICATOR: NAME, DEFAULT_INDICATOR_VIEW_NAME
    3         INDICATOR_VIEW: NAME, INDICATOR_NAME
    4         INDICATOR_VIEW_VALUE: INDICATOR_VIEW_NAME
    5         INDICATOR_TO_INDICATOR: INDICATOR_NAME, RELATED_INDICATOR_NAME
    6         INDICATOR_TO_INDICATOR_SET: INDICATOR_NAME
    7         INDICATOR_TO_RELATION: INDICATOR_NAME
    8         DATA_SOURCE_TO_INDICATOR: INDICATOR_NAME
    9         DATA_SOURCE_TO_INDICATOR_VIEW: INDICATOR_VIEW_NAME
    10         USER_TO_INDICATOR: INDICATOR_NAME
     1/* Splits / creates new IPs AND IPVs based on Kim's TMP_SPLIT_IP_IPV table.
     2        NOTE: temp table needs to contain the new IP and IPV names.
     3
     4        Handles:
     5        INDICATOR
     6        INDICATOR_VIEW
     7        INDICATOR_VIEW_VALUE
     8        INDICATOR_TO_INDICATOR
     9        INDICATOR_TO_INDICATOR_SET
     10        INDICATOR_TO_RELATION
     11        DATA_SOURCE_TO_INDICATOR_VIEW
     12        USER_TO_INDICATOR
    1113*/
    1214

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

    r25051 r25052  
     1/* Sets the IV measure name and title based on Kim's TMP_IPV_MEASURE table */
    12
    23update INDICATOR_VIEW set MEASURE_NAME           = ifnull  ((select MEASURE_NAME from TMP_IPV_MEASURE where IPV_NAME3 = NAME), 'DEFAULT');

  • trunk/db/src/main/scripts/mysql/migrate/5_data-data_source_to_ipv.sql

    r25051 r25052  
    1 /* Update the DS to IPV with the new datasource name and sort order */
     1/* Update the DS to IPV with the new datasource name and sort order based on
     2        Kim's TMP_DS23NAME_DS3NAME table.
     3       
     4        NOTE: temp table DS names and IPV names must be the new names.
     5*/
    26
    37

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

    r25051 r25052  
    1 /* Sets the IV dimension names based on Kim's Excel Sheet.  This script also
     1/* Sets the IV dimension names based on Kim's temp tables.  This script also
    22        sets the period dim and map dim names.  The series and cat dim names are set
    33        to provide an initial starting point for downstream migration scripts as

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

    r25033 r25052  
    1 /* script needed to convert db from 2.3 to 3.0
     1/* Script needed to convert db from 2.3 to 3.0
    22
    33        *** Make sure all new records have their ACTIVE_FLAG set appropriately.
    44
    55        0.1) Prior to handing off for migration the db to be migrated should have:
    6                 - Indicator, View, and datasource records to be removed have sort order set to 999 and 99.
     6                - Indicator and View records to be removed with a sort order of 999 or 99.
    77                - Data source to indicator view sort orders set as per Lois (if not
    88                        handled in Step 1.x). 
    9                 - Any downsizing of text like IP DATA_NOTEs, TITLEs etc.
    10                 - View Y titles cleaned up and as standardized as possible.
    11                 - View Period titles cleaned up and as standardized as possible with
     9                - Downsize length limited name, text, title fields like IP DATA_NOTEs, TITLEs etc.
     10                - IP view Y titles cleaned up and as standardized as possible.
     11                - IP view Period titles cleaned up and as standardized as possible with
    1212                        values that match associated dimension title/values.
    13                 - Set all views desirable value.
     13                - Set all IP views desirable value.
    1414        0.2) Create new multi use data sources (adhere to column sizes like TEXT etc.).
    1515        0.3) Include items listed in Lois's doc that are not accounted for in Step 1.x.
    … …  
    1919        ----------- Step 1.x - Prep 2.3 Migration
    2020
    21         1.0) Import 2.3 db into development enviro.
     21        1.0) Import 2.3 db into a migration environment.
    2222        1.1) Create/add the new v3 tables.
    2323        1.2) Update tables - add/rename the new v3 columns.
    2424
    2525        1.3) Add/Edit standard validation data as needed (typically NM db table
    26                 export or mysql file, or edit table directly):
    27                 - TOPIC, VALUE_ATTRIBUTE, ANCILLARY_VALUE, INITIATIVE, INITIATIVE_TOPIC,
     26                export or mysql file, or edit table directly). 
     27                Could include:
     28                        TOPIC, VALUE_ATTRIBUTE, ANCILLARY_VALUE, INITIATIVE, INITIATIVE_TOPIC,
    2829                        INITIATIVE_TOPIC_OBJECTIVE, INITIATIV_TOPIC_OBJ_TO_INDICAT.
    29                 - Likely other more specific validation tables like MEASURE, VALUE_TYPE,
    30                         DIMENSION etc. are created by Kim (see Step 2.x).
     30                Adopter specific validation tables are typically hand created and added
     31                in the next 2.x Step. 
     32                These likely include:
     33                        MEASURE, VALUE_TYPE, DATA_SOURCE, DIMENSION etc.
    3134        1.4) Create temp 2.3 to 3 mapping tables (so Kim can populate in next section).
    32         1.5) Do initial orphaned record cleanup.
    33         1.6) Zip mysql db files and send to Kim (or do an export).
     35        1.5) Do basic orphaned record cleanup.
     36        [1.6] Optional - depending on if Garth/Paul are running the migration scripts
     37                vs Kim doing it all.  If Kim not doing it all aip mysql db files and send
     38                to Kim or do an export.
    3439
    3540        ----------- Step 2.x - Prep Data
    3641
    37         2.0) Kim updates local enviro with zipped mysql files or imports .sql.
    38         2.1) Kim populates/udpates validation tables:
    39                 - MEASURE, DIMENSION, DIMENSION_VALUE, VALUE_TYPE, DATA_SOURCE,
     42        [2.0] Optional - if Kim running everything this is not needed. Otherwise
     43                Kim updates her local enviro with zipped mysql files or imports .sql.
     44        2.1) Kim populates/udpates any adopter specfici validation tables:
     45                        MEASURE, DIMENSION, DIMENSION_VALUE, VALUE_TYPE, DATA_SOURCE,
    4046                        DATA_SOURCE_TO_INDICATOR_VIEW (data source to indicator view sort
    4147                        orders set as per Lois).
    4248        2.2) Kim populates temp mapping tables.
    43                 - TMP_SPLIT_IP, TMP_IP23NAME_IP3NAME, TMP_IPV_MEASURE, TMP_DS23NAME_DS3NAME,
     49                        TMP_SPLIT_IP, TMP_IP23NAME_IP3NAME, TMP_IPV_MEASURE, TMP_DS23NAME_DS3NAME,
    4450                        TMP_DSC23_DIM, TMP_DSS23_DIM, TMP_IPV_CONSTANT_DIM_NAME.
    4551        2.3) Kim updates the IV "None" DS series to set single use, "constant" type
    … …  
    5157                series) but needs to have the specific SERIES VALUE set (Male=1, FM=2). 
    5258
    53         2.5) Kim zips entire mysql db and sends to Garth (or does an export).
     59        [2.5] Optional - if Kim running everything then not needed.  Else Kim zips
     60                entire mysql db and sends to Garth (or does an export).
    5461
    5562        ----------- Step 3.x - Migrate
    5663
    57         3.0) Import prepped 2.3 db into development enviro
    58         3.1) Cleanup
     64        [3.0] Optional - if Kim running everything.  Otherwise Garth/Paul imports
     65                Kim's prepped 2.3 db into development enviro.
     66        3.1) Cleanup (script 1).
    5967                - delete orphaned IP and IPV and IPVV.
    6068                - delete orphaned DSC and DSS records.
     69                - title length changes etc.
    6170
     71        3.2) IP and IPV Name Changes (script 2).
    6272                - IP/IPV/IPVV name changes.
    63                 - Other name changes - data sources.
    64                 - Other updates like user info etc.
     73                - Processes all assoc intersection tables etc.
    6574
    66         3.2) IV and IVV Updates
    67                 - Set the IV MEASURE_NAME and IP value type.
     75        3.3) Split/create new IP and IPV records (script 3).
     76                - Split/create new IP/IPV based on temp table.
     77                - Processes all assoc intersection tables etc.
    6878
    69                 *** The IV and IVV tables provide a mapping/lookup between the old
    70                 dataset series/category name/values to the new dimension name/values.
     79        3.4) IPV measure updates (script 4).
     80                - Sets the IV measure name and title based on TMP_IPV_MEASURE.
    7181
     82        3.5) DS to IPV updates (script 5).
     83                - Update the DS to IPV with the new datasource name and sort order
     84                        based on TMP_DS23NAME_DS3NAME table.
     85
     86        3.6) Sets the new IV dim names - map, period, ser, cat, const (script 6).
    7287                - Set the period dim name and constant dim names and the temp IV series
    7388                and cat dimension names.  Set the IVV period dim, dim1, and dim2 names
    7489                and values based on the temp mapping table and current category and
    7590                series names that match the temp table mappings.
    76                
    77         3.3) Create the view dimensions records
     91
     92                NOTE: The IV and IVV tables provide a mapping/lookup between the old
     93                dataset series/category name/values to the new dimension name/values.
     94
     95        3.7) Sets the new IVV dim names and values - period, dim1, dim2 (script 7).
     96
     97        3.8) Populate each view's dimension usage records (script 8).
     98                - Setup view's dimension values based on current usage.
    7899                - Set each view's series, category, and/or constant dimension usage. 
    79100                        At a minimum each view MUST have 2 dimension usages specified.  One
    … …  
    82103                        MUST have another series or constant specified.
    83104
    84         3.4) Create each view's dimension values records. 
    85                 - Setup view's dimension values based on current usage.
    86        
    87         3.5) Create the indicator datasets based on the assoc IV via a temp table.
    88         3.6) Create the dataset's dims - again based on the IV using the temp
    89                 table.
     105        3.9) Populate each view's dimension value records (script 9).
    90106
    91         3.7) Create each dataset's value records.
    92                 - Based on the assoc IVVs, dataset, and dataset dimensions.
     107        3.10) Create the all the indicator datasets type tables (script 10).
     108                DATASET, DATA_SOURCE_TO_DATASET, DATASET_TO_INDICATOR_VIEW,
     109                DIMENSION_TO_DATASET, and DATA_SOURCE data issues if not set.
     110
     111        3.11) Create each dataset's value records (script 11).
     112
     113        3.12) Create each dataset value records' AV records (script 12).
    93114                - add in the associated ancillary values.
    94115
    95         3.8) drop old unused columns
    96         3.9) drop temp and old unused tables
    97         3.10) export the migrated version 3 db.
     116        3.13) Cleanup.
     117                - drop old unused columns
     118                - drop temp and old unused tables
     119                - remove orphaned records.
     120
     121        3.14) export the migrated version 3 db.
    98122       
    99123        ----------- Step 4.x - Deploy
    … …  
    147171
    148172/* ---------------- Step 3.x - Run Migration Scripts - Get DB from Kim */
    149         0_data-cleanup.sql                                      /* Rerun basic orphaned records cleanup and few misc updates. */
    150         1_data-ip_ipv_rename.sql               
    151         2_data-ip_ipv_split.sql         
     173        1_data-cleanup.sql                                      /* Rerun basic orphaned records cleanup and few misc updates. */
     174        2_data-ip_ipv_rename.sql               
     175        3_data-ip_ipv_split.sql         
    152176
    153         2_data-iv_measure_name.sql                      /* Kims mapping with MEASURE_TITLE_OVERRIDE IVN, MN, CMT */
    154         3_data-data_source_to_ipv.sql           /* Kims mapping with SO */
    155         4_data-iv_dimensions.sql                /* Kim's mapping - will send when finished DSC & S : VALUE to DIM NAME : VALUE */
    156         5_data-ivv_dimensions.sql               
    157         6_data-iv_dimension_usage.sql           /* sets the iv dimension usage intersection table records. */
    158         7_data-iv_dimension_value.sql           /* sets the iv dimension values intersection table records. */
     177        4_data-iv_measure_name.sql                      /* Kims mapping with MEASURE_TITLE_OVERRIDE IVN, MN, CMT */
     178        5_data-data_source_to_ipv.sql           /* Kims mapping with SO */
     179        6_data-iv_dimensions.sql                /* Kim's mapping - will send when finished DSC & S : VALUE to DIM NAME : VALUE */
     180        7_data-ivv_dimensions.sql               
     181        8_data-iv_dimension_usage.sql           /* sets the iv dimension usage intersection table records. */
     182        9_data-iv_dimension_value.sql           /* sets the iv dimension values intersection table records. */
    159183
    160184        10_data-dataset.sql                                     /* creates the main DATASET records and associates Dims to DSs and DSs to IVs */
Note: See TracChangeset for help on using the changeset viewer.