Changeset 25154 in main


Ignore:
Timestamp:
04/29/22 18:00:41 (3 months ago)
Author:
GarthBraithwaite_STG
Message:

db - move split to copy and simplified Kims and script. removed ibisph. prefix. ipv rename to catch ip name. Added default view template record and set IV to default.

Location:
trunk/db/src/main/scripts/mysql/migrate
Files:
7 edited
2 moved

Legend:

Unmodified
Added
Removed
  • trunk/db/src/main/scripts/mysql/migrate/11_data-dataset_record.sql

    r25095 r25154  
    166166/* check DSRs that don't have a dataset. */
    167167select * from TMP_DATASET_RECORD where DATASET_NAME is null;
    168 ==> 400 rows
     168==> 224 rows
    169169
    170170/* IVV names that don't match */
     
    180180  and INDICATOR_VIEW_NAME not in (select INDICATOR_VIEW_NAME from TMP_INDICATOR_VIEW_DATASET)
    181181;
    182 ==> removed the above 400 recs
     182==> removed the above 224 recs
    183183
    184184
     
    427427  and ((LABEL is null) or (LABEL <= ""))
    428428;
    429 ==> 1175
     429==> 1190
    430430
    431431delete from DATASET_RECORD
     
    524524
    525525        Can do a before update and after to see the dimension1or2 value change.
    526         select * from ibisph.DIMENSION_TO_DATASET;
     526        select * from DIMENSION_TO_DATASET;
    527527        where DATASET_NAME = 'AlcoholDrinkDriveAdult.PctDrinkDrive.BRFSS'
    528528*/
    529 select * from ibisph.DIMENSION_TO_DATASET dtd
     529select * from DIMENSION_TO_DATASET dtd
    530530where DIMENSION_USAGE = 'dimension1'
    531531  and DIMENSION_NAME in   
    532532        (select dr1.DIMENSION_1_NAME
    533          from ibisph.DATASET_RECORD dr1
     533         from DATASET_RECORD dr1
    534534         where dr1.DATASET_NAME = dtd.DATASET_NAME
    535            and dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from ibisph.DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
     535           and dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
    536536        )
    537537;
     
    556556*/
    557557select count(*)
    558 from ibisph.DATASET_RECORD dr1
    559 where dr1.DIMENSION_2_NAME in (select DIMENSION_1_NAME from ibisph.DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
     558from DATASET_RECORD dr1
     559where dr1.DIMENSION_2_NAME in (select DIMENSION_1_NAME from DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
    560560order by dr1.DATASET_NAME, dr1.DIMENSION_1_NAME, dr1.DIMENSION_1_VALUE, dr1.DIMENSION_2_NAME, dr1.DIMENSION_2_VALUE
    561561;
    562562
    563563select count(*)
    564 from ibisph.DATASET_RECORD dr1
    565 where dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from ibisph.DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
     564from DATASET_RECORD dr1
     565where dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
    566566order by dr1.DATASET_NAME, dr1.DIMENSION_1_NAME, dr1.DIMENSION_1_VALUE, dr1.DIMENSION_2_NAME, dr1.DIMENSION_2_VALUE
    567567;
     
    637637
    638638
     639/*
    639640issue appears to be dup keys for different IPVs with different measure values...
    640 
     641*/
    641642select ivv.INDICATOR_VIEW_NAME,
    642643        ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE,
     
    673674
    674675
     676/* test any datasets that don't have any records? */
     677select * from DATASET
     678where NAME not in (select DATASET_NAME from DATASET_RECORD)
     679;
     680
     681/* test IPs that do NOT have a dataset */
     682select NAME from INDICATOR
     683where NAME not in (select INDICATOR_NAME from DATASET)
     684;
     685
     686/* show DSR record count by dataset */
     687select DATASET_NAME, count(DATASET_NAME)
     688from DATASET_RECORD
     689group by DATASET_NAME
     690;
     691
     692/* show IVVs count by IV */
     693select INDICATOR_VIEW_NAME, count(INDICATOR_VIEW_NAME)
     694from INDICATOR_VIEW_VALUE
     695group by INDICATOR_VIEW_NAME
     696;
     697
    675698
    676699
     
    719742limit 5000
    720743;
    721 ==> 3021
     744==> 2451
    722745
    723746
     
    732755) as a
    733756;
    734 ==>
     757==> 114
    735758
    736759will need a mapping file for source IP with a new name and all the assoc
  • trunk/db/src/main/scripts/mysql/migrate/12_data-dataset_record_ancillary_value.sql

    r24731 r25154  
    1 use ibisph;
    2 
    31/* This script relies on the TMP_DATASET_RECORD table created and updated via
    42        the data-dataset_record script.
  • trunk/db/src/main/scripts/mysql/migrate/2_data-ip_ipv_rename.sql

    r25052 r25154  
    3535
    3636
    37 update INDICATOR_VIEW set NAME           = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where NAME2 = NAME), NAME);
    38 update INDICATOR_VIEW set INDICATOR_NAME = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where NAME2 = INDICATOR_NAME), INDICATOR_NAME);
     37/* set IV NAME and assoc IP NAME */
     38update INDICATOR_VIEW iv
     39inner join TMP_IP23NAME_IP3NAME t on iv.NAME = t.NAME2
     40set
     41        iv.NAME           = t.NAME3,
     42        iv.INDICATOR_NAME = substring_index(t.NAME3, '.', 1)
     43;
    3944
    4045
     
    4853update DATA_SOURCE_TO_INDICATOR_VIEW set INDICATOR_VIEW_NAME = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where NAME2 = INDICATOR_VIEW_NAME), INDICATOR_VIEW_NAME);
    4954
    50 /* test */
     55
     56/* IMPORTANT TEST:  The split hasn't happened yet so every IV MUST have a DS. */
     57select NAME from INDICATOR_VIEW
     58where NAME not in (select INDICATOR_VIEW_NAME from DATA_SOURCE_TO_INDICATOR_VIEW);
     59
     60/* unused datasources but might be needed for the split. */
    5161select * from DATA_SOURCE_TO_INDICATOR_VIEW
    5262where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW);
    5363
     64/* Misc queries that might be ran for basic info:
     65select count(*) from DATA_SOURCE;
     66select count(*) from DATA_SOURCE_TO_INDICATOR_VIEW;
     67
    5468select * from DATA_SOURCE_TO_INDICATOR_VIEW
    5569where INDICATOR_VIEW_NAME not in (select distinct(INDICATOR_VIEW_NAME) from INDICATOR_VIEW_VALUE);
    56 
     70*/
    5771
    5872
  • trunk/db/src/main/scripts/mysql/migrate/3_data-ip_ipv_copy.sql

    r25153 r25154  
    1 /* Splits / creates new IPs AND IPVs based on Kim's TMP_SPLIT_IP_IPV table.
     1/* Splits / creates new IPs AND IPVs based on Kim's TMP_COPY_IP_IPV table.
    22        NOTE: temp table needs to contain the new IP and IPV names.
    33
     
    2727        NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
    2828)
    29 select t.NEW_IP_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME,
     29select t.NEW_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME,
    3030        TITLE, CONCISE_TITLE,
    3131        DEFINITION, NUMERATOR, DENOMINATOR, DATA_NOTE, DATA_ISSUES, DATA_AS_OF_DATE,
     
    3636        DEMOGRAPHIC_FLAG, NOT_SELECTABLE_FLAG,  FOOTNOTE_REFERENCES,
    3737        NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
    38 from INDICATOR i, TMP_SPLIT_IP_IPV t
    39 where i.NAME = t.SOURCE_IP_NAME
    40   and t.SOURCE_IP_NAME != t.NEW_IP_NAME
    41   and t.NEW_IP_NAME
    42   not in (select NAME from INDICATOR)
     38from INDICATOR i, TMP_COPY_IP_IPV t
     39where i.NAME = t.SOURCE_NAME
     40  and t.NEW_NAME not in (select NAME from INDICATOR)
     41group by t.NEW_NAME
    4342;
    4443
    4544insert into INDICATOR_TO_INDICATOR (INDICATOR_NAME, RELATED_INDICATOR_NAME, RELATION_NAME)
    46 select t.NEW_IP_NAME, RELATED_INDICATOR_NAME, RELATION_NAME
    47 from INDICATOR_TO_INDICATOR iti, TMP_SPLIT_IP_IPV t
    48 where iti.INDICATOR_NAME = t.SOURCE_IP_NAME
    49   and t.SOURCE_IP_NAME != t.NEW_IP_NAME
     45select t.NEW_NAME, RELATED_INDICATOR_NAME, RELATION_NAME
     46from INDICATOR_TO_INDICATOR iti, TMP_COPY_IP_IPV t
     47where iti.INDICATOR_NAME = t.SOURCE_NAME
    5048  and not exists
    5149        (
    5250                select 1 from INDICATOR_TO_INDICATOR iti2
    53                 where iti2.INDICATOR_NAME         = t.NEW_IP_NAME
     51                where iti2.INDICATOR_NAME         = t.NEW_NAME
    5452                  and iti2.RELATED_INDICATOR_NAME = iti.RELATED_INDICATOR_NAME
    5553                  and iti2.RELATION_NAME          = iti.RELATION_NAME
    5654        )
     55group by t.NEW_NAME
    5756;
    5857
    5958insert into INDICATOR_TO_RELATION (INDICATOR_NAME, RELATION_NAME, TEXT)
    60 select t.NEW_IP_NAME, RELATION_NAME, TEXT
    61 from INDICATOR_TO_RELATION itr, TMP_SPLIT_IP_IPV t
    62 where itr.INDICATOR_NAME = t.SOURCE_IP_NAME
    63   and t.SOURCE_IP_NAME != t.NEW_IP_NAME
     59select t.NEW_NAME, RELATION_NAME, TEXT
     60from INDICATOR_TO_RELATION itr, TMP_COPY_IP_IPV t
     61where itr.INDICATOR_NAME = t.SOURCE_NAME
    6462  and not exists
    6563        (
    6664                select 1 from INDICATOR_TO_RELATION itr2
    67                 where itr2.INDICATOR_NAME         = t.NEW_IP_NAME
     65                where itr2.INDICATOR_NAME         = t.NEW_NAME
    6866                  and itr2.RELATION_NAME          = itr.RELATION_NAME
    6967        )
     68group by t.NEW_NAME
    7069;
    7170
     
    7473        INDICATOR_NAME, INDICATOR_SET_NAME, SORT_ORDER
    7574)
    76 select t.NEW_IP_NAME, itis.INDICATOR_SET_NAME, itis.SORT_ORDER
    77 from INDICATOR_TO_INDICATOR_SET itis, TMP_SPLIT_IP_IPV t
    78 where itis.INDICATOR_NAME = t.SOURCE_IP_NAME
    79   and t.SOURCE_IP_NAME != t.NEW_IP_NAME
     75select t.NEW_NAME, itis.INDICATOR_SET_NAME, itis.SORT_ORDER
     76from INDICATOR_TO_INDICATOR_SET itis, TMP_COPY_IP_IPV t
     77where itis.INDICATOR_NAME = t.SOURCE_NAME
    8078  and not exists
    8179        (
    8280                select 1 from INDICATOR_TO_INDICATOR_SET itis2
    83                 where itis2.INDICATOR_NAME     = t.NEW_IP_NAME
     81                where itis2.INDICATOR_NAME     = t.NEW_NAME
    8482                  and itis2.INDICATOR_SET_NAME = itis.INDICATOR_SET_NAME
    8583        )
     84group by t.NEW_NAME
    8685;
    8786
    8887insert into USER_TO_INDICATOR (INDICATOR_NAME, USER_ID)
    89 select t.NEW_IP_NAME, USER_ID
    90 from USER_TO_INDICATOR uti, TMP_SPLIT_IP_IPV t
    91 where uti.INDICATOR_NAME = t.SOURCE_IP_NAME
    92   and t.SOURCE_IP_NAME != t.NEW_IP_NAME
     88select t.NEW_NAME, USER_ID
     89from USER_TO_INDICATOR uti, TMP_COPY_IP_IPV t
     90where uti.INDICATOR_NAME = t.SOURCE_NAME
    9391  and not exists
    9492        (
    9593                select 1 from USER_TO_INDICATOR uti2
    96                 where uti2.INDICATOR_NAME = t.NEW_IP_NAME
     94                where uti2.INDICATOR_NAME = t.NEW_NAME
    9795                  and uti2.USER_ID        = uti.USER_ID
    9896        )
     97group by t.NEW_NAME
    9998;
    10099
     
    119118        NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
    120119)
    121 select t.NEW_IPV_NAME, t.NEW_IP_NAME,
     120select t.NEW_NAME, substring_index(t.NEW_NAME, '.', 1),
    122121        DESCRIPTION, TITLE, SUB_TITLE,
    123122        PERIOD_TITLE, Y_TITLE,
     
    134133        VIEW_TEMPLATE_NAME,
    135134        NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
    136 from INDICATOR_VIEW iv, TMP_SPLIT_IP_IPV t
    137 where iv.NAME = t.SOURCE_IPV_NAME
    138   and t.NEW_IPV_NAME not in (select NAME from INDICATOR_VIEW)
     135from INDICATOR_VIEW iv, TMP_COPY_IP_IPV t
     136where iv.NAME = t.SOURCE_NAME
     137  and t.NEW_NAME not in (select NAME from INDICATOR_VIEW)
    139138;
    140139
    141140
    142141insert into DATA_SOURCE_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DATA_SOURCE_NAME, SORT_ORDER)
    143 select t.NEW_IPV_NAME, DATA_SOURCE_NAME, SORT_ORDER
    144 from DATA_SOURCE_TO_INDICATOR_VIEW dstiv, TMP_SPLIT_IP_IPV t
    145 where dstiv.INDICATOR_VIEW_NAME = t.SOURCE_IPV_NAME
     142select t.NEW_NAME, DATA_SOURCE_NAME, SORT_ORDER
     143from DATA_SOURCE_TO_INDICATOR_VIEW dstiv, TMP_COPY_IP_IPV t
     144where dstiv.INDICATOR_VIEW_NAME = t.SOURCE_NAME
    146145  and not exists
    147146        (
    148147                select 1 from DATA_SOURCE_TO_INDICATOR_VIEW dstiv2
    149                 where dstiv2.INDICATOR_VIEW_NAME = t.NEW_IPV_NAME
     148                where dstiv2.INDICATOR_VIEW_NAME = t.NEW_NAME
    150149                  and dstiv2.DATA_SOURCE_NAME    = dstiv.DATA_SOURCE_NAME
    151150        )
    152151;
     152
     153/* IMPORTANT TEST: Every IV MUST have a DS. */
     154select NAME from INDICATOR_VIEW
     155where NAME not in (select INDICATOR_VIEW_NAME from DATA_SOURCE_TO_INDICATOR_VIEW);
     156
     157/* Unused datasources */
     158select * from DATA_SOURCE_TO_INDICATOR_VIEW
     159where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW);
     160
    153161
    154162
     
    165173        NOTE, ACTIVE_FLAG
    166174)
    167 select t.NEW_IPV_NAME,
     175select t.NEW_NAME,
    168176        DATASET_SERIES_NAME,  DATASET_CATEGORY_NAME,
    169177        DATASET_SERIES_VALUE, DATASET_CATEGORY_VALUE,
     
    174182        NUMERATOR, DENOMINATOR, LABEL,
    175183        NOTE, ACTIVE_FLAG
    176 from INDICATOR_VIEW_VALUE ivv, TMP_SPLIT_IP_IPV t
    177 where ivv.INDICATOR_VIEW_NAME = t.SOURCE_IPV_NAME
     184from INDICATOR_VIEW_VALUE ivv, TMP_COPY_IP_IPV t
     185where ivv.INDICATOR_VIEW_NAME = t.SOURCE_NAME
    178186  and not exists
    179187        (
    180188                select 1 from INDICATOR_VIEW_VALUE ivv2
    181                 where ivv2.INDICATOR_VIEW_NAME    = t.NEW_IPV_NAME
     189                where ivv2.INDICATOR_VIEW_NAME    = t.NEW_NAME
    182190                  and ivv2.DATASET_CATEGORY_NAME  = ivv.DATASET_CATEGORY_NAME
    183191                  and ivv2.DATASET_CATEGORY_VALUE = ivv.DATASET_CATEGORY_VALUE
  • trunk/db/src/main/scripts/mysql/migrate/4_data-iv_misc.sql

    r25153 r25154  
    77update INDICATOR_VIEW set MEASURE_TITLE_OVERRIDE = (select MEASURE_TITLE_OVERRIDE from TMP_IPV_MEASURE where IPV_NAME3 = NAME);
    88
     9
     10
     11/* add the default view template and set all IPV's to use the default template. */
     12insert into VIEW_TEMPLATE(NAME, TITLE, DESCRIPTION, VIEW_TYPE, FILEPATH_AND_NAME, SORT_ORDER, ACTIVE_FLAG)
     13values ('IndicatorViewDefault', 'Default Indicator View', 'Default indicator view XSLT', 'INDICATOR_VIEW', 'html/indicator/profile/view/ViewPage.xslt', 1, 'x')
     14;
     15
     16update INDICATOR_VIEW set VIEW_TEMPLATE_NAME = 'IndicatorViewDefault';
     17
    918/*------------------------------ END OF FILE ------------------------------*/
  • trunk/db/src/main/scripts/mysql/migrate/5_data-data_source_to_ipv.sql

    r25052 r25154  
    2929
    3030
     31/* IMPORTANT TESTS: Make sure all IV's have valid DS */
    3132select * from DATA_SOURCE_TO_INDICATOR_VIEW
    3233where SORT_ORDER is null
     
    3435   or DATA_SOURCE_NAME = 'NO DS3 NAME'
    3536;
    36 delete from DATA_SOURCE_TO_INDICATOR_VIEW
    37 where SORT_ORDER is null
    38    or SORT_ORDER = -9
    39    or DATA_SOURCE_NAME = 'NO DS3 NAME'
    40 ;
    4137
    42 
    43 /* IMPORTANT TEST: Need to see IPV's that don't have a data source */
    4438select * from INDICATOR_VIEW
    4539where NAME not in (select INDICATOR_VIEW_NAME from DATA_SOURCE_TO_INDICATOR_VIEW)
    4640;
     41
    4742
    4843/* TEST AND REMOVE IPV DSs that do not match / REMOVE ORPHANDED DATASOURCES. */
     
    5348delete   from DATA_SOURCE_TO_INDICATOR_VIEW where DATA_SOURCE_NAME not in (select NAME from DATA_SOURCE) or DATA_SOURCE_NAME is null;
    5449
    55 /* repeat the tests to make sure counts match */
     50
     51/* Sanity checks.  Extra DS to IVs no IV name match, IV's that don't have a DS */
    5652select count(*) from DATA_SOURCE_TO_INDICATOR_VIEW;
    5753select count(*) from DATA_SOURCE_TO_INDICATOR_VIEW where INDICATOR_VIEW_NAME not in (select distinct NAME from INDICATOR_VIEW) or INDICATOR_VIEW_NAME is null;
  • trunk/db/src/main/scripts/mysql/migrate/tab_c-tmp.sql

    r25056 r25154  
    2020
    2121
    22 /* Used to split IP and IPVs.  Since IP and IPV names are unique script uses
    23         the same table for both split opertations.
    24         all NAMEs are v3 names.
     22/* Used to copy/split IP and IPVs.  Since IP and IPV names are unique script 
     23        uses the same table for both copy opertations.  All NAMEs are v3 names.
    2524*/
    26 drop table if exists TMP_SPLIT_IP_IPV;
    27 create table if not exists TMP_SPLIT_IP_IPV (
    28   SOURCE_IP_NAME                  varchar  (100)  not null,
    29   SOURCE_IPV_NAME                 varchar  (100)  not null,
    30   NEW_IP_NAME                     varchar  (100)  not null,
    31   NEW_IPV_NAME                    varchar  (100)  not null,
    32   constraint TMP_SPLIT_IP_IPV_PK
    33     primary key (SOURCE_IPV_NAME, NEW_IPV_NAME)
     25drop table if exists TMP_COPY_IP_IPV;
     26create table if not exists TMP_COPY_IP_IPV (
     27  SOURCE_NAME                     varchar  (100)  not null,
     28  NEW_NAME                        varchar  (100)  not null,
     29  constraint TMP_COPY_IP_IPV_PK
     30    primary key (SOURCE_NAME, NEW_NAME)
    3431);
    3532
  • trunk/db/src/main/scripts/mysql/migrate/update.sql

    r25095 r25154  
    7777                - Processes all assoc intersection tables etc.
    7878
    79         3.3) Split/create new IP and IPV records (script 3).
    80                 - Split/create new IP/IPV based on temp table.
     79        3.3) Create/copy new IP and IPV records (script 3).
     80                - Create new IP/IPV record copy based on temp table.
    8181                - Processes all assoc intersection tables etc.
    8282
     
    177177/* ---------------- Step 3.x - Run Migration Scripts - Get DB from Kim */
    178178        1_data-cleanup.sql                                      /* Rerun basic orphaned records cleanup and few misc updates. */
    179         2_data-ip_ipv_rename.sql               
    180         3_data-ip_ipv_split.sql         
     179        2_data-ip_ipv_rename.sql                        /* rename IP and IPVs with assoc intersection records */
     180        3_data-ip_ipv_copy.sql                          /* create new IP and/or IPVs with assoc intersection records */
    181181
    182         4_data-iv_measure_name.sql                      /* Kims mapping with MEASURE_TITLE_OVERRIDE IVN, MN, CMT */
     182        4_data-iv_misc.sql                                      /* Kims mapping with MEASURE_TITLE_OVERRIDE IVN, MN, CMT, and View type */
    183183        6_data-iv_dimensions.sql                /* Kim's mapping - will send when finished DSC & S : VALUE to DIM NAME : VALUE */
    184184        7_data-ivv_dimensions.sql               
  • trunk/db/src/main/scripts/mysql/migrate/update_cleanup.sql

    r25095 r25154  
    3535
    3636drop table if exists TMP_IP23NAME_IP3NAME;
    37 drop table if exists TMP_SPLIT_IP_IPV;
     37drop table if exists TMP_COPY_IP_IPV;
    3838drop table if exists TMP_IPV_MEASURE;
    3939drop table if exists TMP_DS23NAME_DS3NAME;
Note: See TracChangeset for help on using the changeset viewer.