Changeset 25029 in main


Ignore:
Timestamp:
04/12/22 11:32:42 (4 months ago)
Author:
GarthBraithwaite_STG
Message:

db - before refactoring / cleaning up.

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

Legend:

Unmodified
Added
Removed
  • trunk/db/src/main/scripts/mysql/nj/0_data-cleanup.sql

    r24813 r25029  
    2323delete from INDICATOR_VIEW_VALUE
    2424where DATASET_CATEGORY_NAME  is null
    25    or DATASET_CATEGORY_NAME  != (select DATASET_CATEGORY_NAME from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
     25   or DATASET_CATEGORY_NAME  not in (select DATASET_CATEGORY_NAME from INDICATOR_VIEW where NAME = INDICATOR_VIEW_NAME)
    2626   or DATASET_CATEGORY_VALUE not in (select VALUE from DATASET_CATEGORY_VALUE dcv where dcv.DATASET_CATEGORY_NAME = INDICATOR_VIEW_VALUE.DATASET_CATEGORY_NAME)
    2727;
     
    3030
    3131/* Did have code to REMOVE UNUSED DSC & DSS but these are not used with Kim's
    32         approach so leave all DSC and DSS data as is becauses those tables are
     32        approach so leave all DSC and DSS data as is because those tables are
    3333        dropped in the post migration cleanup.
    3434*/
     
    8383   IMPORTANT: MUST BE RAN BEFORE SETTING IV.TITLE.
    8484 */
    85 update INDICATOR i set CONCISE_TITLE = ifnull((select substring(TITLE, 1, 100) from INDICATOR_VIEW iv where iv.NAME = i.DEFAULT_INDICATOR_VIEW_NAME), '');
     85update INDICATOR i set CONCISE_TITLE = ifnull(
     86        (select substring(TITLE, 1, 100)
     87     from INDICATOR_VIEW iv
     88     where i.DEFAULT_INDICATOR_VIEW_NAME is not null
     89           and iv.INDICATOR_NAME = i.NAME
     90       and iv.NAME = i.DEFAULT_INDICATOR_VIEW_NAME
     91        )
     92        , '')
     93;
    8694
    8795/* DEMOGRAPHICS: set the indicator demographic flag */
  • trunk/db/src/main/scripts/mysql/nj/10_data-dataset.sql

    r24813 r25029  
    9494
    9595
    96 
    97 
    98 
    99 
    100 /* Insert the SO3 entries for US uses and special split US uses.
    101 ???????????????????????????????
    102 
    103 insert into TMP_INDICATOR_VIEW_DATASET (
    104                 INDICATOR_NAME, INDICATOR_VIEW_NAME,
    105                 NUMERATOR_DATA_SOURCE_NAME, DATA_SOURCE_SORT_ORDER,
    106                 PERIOD_DIMENSION_NAME, DIMENSION_1_NAME, DIMENSION_2_NAME
    107         )
    108 select  substring_index(ivv.INDICATOR_VIEW_NAME, '.', 1), ivv.INDICATOR_VIEW_NAME,
    109         dstiv.DATA_SOURCE_NAME, dstiv.SORT_ORDER,
    110         ivv.PERIOD_DIMENSION_NAME, ivv.DIMENSION_1_NAME, ifnull(ivv.DIMENSION_2_NAME, 'NA')
    111 from INDICATOR_VIEW_VALUE ivv, DATA_SOURCE_TO_INDICATOR_VIEW dstiv
    112 where dstiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
    113   and dstiv.SORT_ORDER = 3
    114   and ((ivv.DIMENSION_1_NAME in ('GeoCountry', 'ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'Girls15-17_18-19', 'DiagPriAny'))
    115                 or
    116            (ivv.DIMENSION_2_NAME in ('GeoCountry', 'ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'Girls15-17_18-19', 'DiagPriAny'))
    117           )
    118 group by 2, 3, 4, 5, 6, 7
    119 ;
    120 */
    121 
    122 
    123 
    124 
    125 
    126 
    127 
    128 
    129 
    130 
    13196/* Insert all the SO1. */
    13297insert into TMP_INDICATOR_VIEW_DATASET (
     
    144109;
    145110
    146 
    147111/* quick test to see what DS_TO_IV is not used */
    148112select distinct INDICATOR_VIEW_NAME
     
    156120
    157121
    158 
    159 
    160 
    161 
    162 
    163 
    164 
    165 
    166 
    167 
    168 /* ????????????????????
    169         The SO3 insert is very specific to only include those records that have a
     122/* Insert the US SO3 entries */
     123insert into TMP_INDICATOR_VIEW_DATASET (
     124                INDICATOR_NAME, INDICATOR_VIEW_NAME,
     125                NUMERATOR_DATA_SOURCE_NAME, DATA_SOURCE_SORT_ORDER,
     126                PERIOD_DIMENSION_NAME, DIMENSION_1_NAME, DIMENSION_2_NAME
     127        )
     128select  substring_index(ivv.INDICATOR_VIEW_NAME, '.', 1), ivv.INDICATOR_VIEW_NAME,
     129        dstiv.DATA_SOURCE_NAME, dstiv.SORT_ORDER,
     130        ivv.PERIOD_DIMENSION_NAME, ivv.DIMENSION_1_NAME, ifnull(ivv.DIMENSION_2_NAME, 'NA')
     131from INDICATOR_VIEW_VALUE ivv, DATA_SOURCE_TO_INDICATOR_VIEW dstiv
     132where dstiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
     133  and dstiv.SORT_ORDER = 3
     134group by 2, 3, 4, 5, 6, 7
     135
     136
     137
     138/*  The SO3 insert is very specific to only include those records that have a
    170139        US related dimension.  Inserting the blanket SO1 results in some dup US type
    171140        entries as the insert keys off of the view name, dataset, SO, and DIM 1 & 2. 
     
    175144       
    176145        Note that the "select" and "delete" counts should match.
     146*/
    177147select *
    178148from TMP_INDICATOR_VIEW_DATASET
    179149where DATA_SOURCE_SORT_ORDER = 1
    180   and ((DIMENSION_1_NAME = 'GeoCountry') or (DIMENSION_2_NAME = 'GeoCountry'))
    181150  and INDICATOR_VIEW_NAME in
    182151        (select INDICATOR_VIEW_NAME
    183152         from TMP_INDICATOR_VIEW_DATASET
    184153         where DATA_SOURCE_SORT_ORDER = 3
    185            and ((DIMENSION_1_NAME = 'GeoCountry') or (DIMENSION_2_NAME = 'GeoCountry'))
    186154        )
    187155order by INDICATOR_VIEW_NAME, NUMERATOR_DATA_SOURCE_NAME, DIMENSION_1_NAME,  DIMENSION_2_NAME
     
    191159from TMP_INDICATOR_VIEW_DATASET
    192160where DATA_SOURCE_SORT_ORDER = 1
    193   and ((DIMENSION_1_NAME = 'GeoCountry') or (DIMENSION_2_NAME = 'GeoCountry'))
    194161  and INDICATOR_VIEW_NAME in
    195162        (select t1.INDICATOR_VIEW_NAME from
     
    197164                 from TMP_INDICATOR_VIEW_DATASET
    198165                 where DATA_SOURCE_SORT_ORDER = 3
    199                    and ((DIMENSION_1_NAME = 'GeoCountry') or (DIMENSION_2_NAME = 'GeoCountry'))
    200166                ) t1
    201167        )
    202168;
    203 */
    204 
    205 
    206 
    207 
    208 
    209 
    210 ??????????????????????????
     169
     170
    211171
    212172/* Now process for the special SO3 uses - shouldn't be any - if some delete */
     
    214174from TMP_INDICATOR_VIEW_DATASET tivd
    215175where DATA_SOURCE_SORT_ORDER = 1
    216   and ((DIMENSION_1_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'Girls15-17_18-19', 'DiagPriAny'))
    217                 or
    218            (DIMENSION_2_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'Girls15-17_18-19', 'DiagPriAny'))
    219           )
    220176  and INDICATOR_VIEW_NAME in
    221177        (select tivd2.INDICATOR_VIEW_NAME
    222178         from TMP_INDICATOR_VIEW_DATASET tivd2
    223179         where tivd2.DATA_SOURCE_SORT_ORDER = 3
    224            and ((tivd2.DIMENSION_1_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'Girls15-17_18-19', 'DiagPriAny'))
    225                         or
    226                         (tivd2.DIMENSION_2_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'Girls15-17_18-19', 'DiagPriAny'))
    227                    )
    228180           and tivd2.NUMERATOR_DATA_SOURCE_NAME = tivd.NUMERATOR_DATA_SOURCE_NAME
    229181        )
     
    232184
    233185
    234 
    235 ?????????????????????????????
    236186
    237187/* Finally, should not have any of these but test/remove all SO3's that only have 1 US SO1 */
     
    244194         where t.DATA_SOURCE_SORT_ORDER = 1
    245195           and t.INDICATOR_VIEW_NAME = tivd.INDICATOR_VIEW_NAME
    246            and ((t.DIMENSION_1_NAME = 'GeoCountry') or (t.DIMENSION_2_NAME = 'GeoCountry'))
    247196        )
    248197;
  • trunk/db/src/main/scripts/mysql/nj/11_data-dataset_record.sql

    r24813 r25029  
    4545  /* */
    4646  SORT_ORDER                      int             default null,
    47   ACTIVE_FLAG                     varchar    (1)
     47  ACTIVE_FLAG                     varchar    (1),
     48  /* */
     49  constraint TMP_DATASET_RECORD_UK
     50    unique key (INDICATOR_VIEW_NAME, PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE,
     51                DIMENSION_1_NAME, DIMENSION_1_VALUE, DIMENSION_2_NAME, DIMENSION_2_VALUE
     52        )
    4853);
    4954
     
    148153
    149154
    150 
    151 
    152 
    153 
    154 
    155 
    156 ????????????????????????????????
    157 
    158 /* Special split use dataset temp record's dataset name update.  Match the IV name and
    159         dimension names against the TMP_INDICATOR_VIEW_DATASET's values to get the
    160         tivd dataset name.
    161        
    162         NOTE: 'Girls15-17_18-19' is already correct - it only has values 1 & 3 which
    163         are both a data source of: BVRHS_Birth
    164 */
    165 update TMP_DATASET_RECORD tdr
    166         inner join
    167         (select INDICATOR_VIEW_NAME, DIMENSION_1_NAME, DIMENSION_2_NAME, PERIOD_DIMENSION_NAME, DATA_SOURCE_SORT_ORDER, DATASET_NAME
    168          from TMP_INDICATOR_VIEW_DATASET
    169         ) tivd_ij
    170         on (
    171                ( tivd_ij.INDICATOR_VIEW_NAME = tdr.INDICATOR_VIEW_NAME)
    172            and ( tivd_ij.DIMENSION_1_NAME    = tdr.DIMENSION_1_NAME)
    173            and ((tivd_ij.DIMENSION_2_NAME    = tdr.DIMENSION_2_NAME) or (tdr.DIMENSION_2_NAME is null))
    174            and ( tivd_ij.PERIOD_DIMENSION_NAME = tdr.PERIOD_DIMENSION_NAME)
    175            and ((tivd_ij.DIMENSION_1_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'DiagPriAny'))
    176                         or
    177                     (tivd_ij.DIMENSION_2_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'DiagPriAny'))
    178                    )
    179            and ( tivd_ij.DATA_SOURCE_SORT_ORDER = 1)
    180         )
    181 set tdr.DATASET_NAME = tivd_ij.DATASET_NAME
    182 where ((tdr.DIMENSION_1_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'DiagPriAny'))
    183                 and
    184            (tdr.DIMENSION_1_VALUE in ('1', '2', '5'))
    185           )
    186    or
    187           ((tdr.DIMENSION_2_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'DiagPriAny'))
    188                 and
    189            (tdr.DIMENSION_2_VALUE in ('1', '2', '5'))
    190           )
    191 ;
    192 
    193 update TMP_DATASET_RECORD tdr
    194         inner join
    195         (select INDICATOR_VIEW_NAME, DIMENSION_1_NAME, DIMENSION_2_NAME, PERIOD_DIMENSION_NAME, DATA_SOURCE_SORT_ORDER, DATASET_NAME
    196          from TMP_INDICATOR_VIEW_DATASET
    197         ) tivd_ij
    198         on (
    199                ( tivd_ij.INDICATOR_VIEW_NAME = tdr.INDICATOR_VIEW_NAME)
    200            and ( tivd_ij.DIMENSION_1_NAME    = tdr.DIMENSION_1_NAME)
    201            and ((tivd_ij.DIMENSION_2_NAME    = tdr.DIMENSION_2_NAME) or (tdr.DIMENSION_2_NAME is null))
    202            and ( tivd_ij.PERIOD_DIMENSION_NAME = tdr.PERIOD_DIMENSION_NAME)
    203            and ((tivd_ij.DIMENSION_1_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'DiagPriAny'))
    204                         or
    205                     (tivd_ij.DIMENSION_2_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'DiagPriAny'))
    206                    )
    207            and ( tivd_ij.DATA_SOURCE_SORT_ORDER = 3)
    208         )
    209 set tdr.DATASET_NAME = tivd_ij.DATASET_NAME
    210 where ((tdr.DIMENSION_1_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'DiagPriAny'))
    211                 and
    212            (tdr.DIMENSION_1_VALUE in ('3', '4', '6'))
    213           )
    214    or
    215           ((tdr.DIMENSION_2_NAME in ('ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'DiagPriAny'))
    216                 and
    217            (tdr.DIMENSION_2_VALUE in ('3', '4', '6'))
    218           )
    219 ;
    220 
    221 
    222 
    223 
    224 
    225 
    226 
    227 
    228 
    229 
    230155/* should never have these */
    231156select * from TMP_DATASET_RECORD tdr
     
    239164;
    240165
    241 /* have 286 out of 52,291 active, 58k total records (IVV and TMP_DS_REC) that don't
    242         have a dataset name (match on IVN, D1N, and DIM2N).
    243 
    244         Removed DIM1 NAME MATCH ==> 670 rows.  Almost all are GeoState
    245         Removed DIM2 NAME MATCH ==> 197 rows.  Most are GeoCnty with a value of 'US', others are GeoState with value of 35 == NM.
    246         Simply matching only on the IPV N matching ==> 852 rows. 
    247         IVN doesn't match: 1203
    248 */
     166/* check DSRs that don't have a dataset. */
    249167select * from TMP_DATASET_RECORD where DATASET_NAME is null;
     168==> 400 rows
    250169
    251170/* IVV names that don't match */
     
    256175;
    257176
    258 /* These appear to be orphans.  Not sure how they got this far as data-cleanup.sql
    259         line 38 should have removed these???
    260 */
     177/* These appear to be orphans.  Not sure how they got this far as data-cleanup.sql */
    261178delete from TMP_DATASET_RECORD
    262179where DATASET_NAME is null
    263180  and INDICATOR_VIEW_NAME not in (select INDICATOR_VIEW_NAME from TMP_INDICATOR_VIEW_DATASET)
    264181;
     182==> removed the above 400 recs
    265183
    266184
     
    509427  and ((LABEL is null) or (LABEL <= ""))
    510428;
     429==> 1175
    511430
    512431delete from DATASET_RECORD
     
    517436
    518437
    519 
    520 
    521 
    522 
    523 
    524 
    525 
    526 ????????????????????????????
    527438
    528439
     
    587498
    588499
    589 /* stuff added 7/19/2019 for dual purpose... */
    590 
    591500/* Some Dataset Records have dim1 & 2 as a geo.  Script should be checked to see
    592501        if this can be correct.  Most are State and Country.  Other state and country
    593502        records worked so this  might have to be done manually.  For now put a prefix
    594503        on the records so that they are easily seen.
    595        
    596         625 records that have this issue.
    597504*/
    598505select * from DATASET_RECORD
     
    600507  and DIMENSION_2_NAME like 'Geo%'
    601508;
    602 
    603 update DATASET_RECORD
    604 set DIMENSION_2_NAME = concat('LOIS_', DIMENSION_2_NAME),
    605         ACTIVE_FLAG      = null
    606 where  DIMENSION_1_NAME like 'Geo%'
    607   and DIMENSION_2_NAME like 'Geo%'
    608 ;
    609 
    610 select * from DATASET_RECORD
    611 where DATASET_NAME like 'AlcoholBingeDrinkAdult%'
    612   and DIMENSION_2_NAME like 'LOIS_Geo%'
    613 ;
    614 
    615 select * from `23`.DBO_INDICATOR_VIEW_VALUE
    616 where INDICATOR_VIEW_NAME like 'AlcoholBingeDrinkAdult.%'
    617   and DATASET_CATEGORY_VALUE like '35000%'
    618 ;
    619 select * from `23`.DBO_INDICATOR_VIEW_VALUE
    620 where INDICATOR_VIEW_NAME like 'AlcoholBingeDrinkAdult.Cnty%'
    621   and DATASET_SERIES_VALUE like '35000%'
    622 
    623 
    624 select DIMENSION_1_NAME, DIMENSION_1_VALUE, DIMENSION_2_NAME, DIMENSION_2_VALUE, count(*) from DATASET_RECORD
    625 where DIMENSION_2_NAME like 'LOIS_Geo%'
    626 group by DIMENSION_1_NAME, DIMENSION_1_VALUE, DIMENSION_2_NAME, DIMENSION_2_VALUE
    627 ;
    628 
    629 select DIMENSION_1_NAME, DIMENSION_1_VALUE, DIMENSION_2_NAME, DIMENSION_2_VALUE, count(*) from DATASET_RECORD
    630 where DIMENSION_1_NAME in ('GeoState', 'GeoCountry')
    631 group by DIMENSION_1_NAME, DIMENSION_1_VALUE, DIMENSION_2_NAME, DIMENSION_2_VALUE
    632 ;
    633 
    634 
    635 /* Lois said these are not correct and to delete them. */
    636 delete from DATASET_RECORD
    637 where DIMENSION_2_NAME like 'LOIS_%'
    638 ;
    639 
    640 
    641 
    642 
    643 
    644 
    645 
    646 
    647509
    648510
     
    689551/* Here's some tests that show the dataset records that are associated with this.
    690552        First do the dim1's that have a matching dim2 (usually the most used).
    691         2300 of these.  1000 where dim2 matches dim1.
    692553
    693554        Since these are the DSR, the view and DSRs are already setup correctly.
     
    708569
    709570
    710 /* DIMENSION_1_OR_2 issues to be aware of.  Of the 54 dual usages, 4 or 5 have
    711         multiples.  Of the mults Homicide had some series switched/out of sync.
    712         Did not dig into it but could be missing values and the way the script loops
    713         through and populates the dims for these records?
     571/* DIMENSION_1_OR_2 issues to be aware of.  Of the dual usages, some have
     572        multiples.  For NM; of the mults Homicide had some series switched/out of
     573        sync.  Did not dig into it but could be missing values and the way the
     574        script loops through and populates the dims for these records?
    714575       
    715576        In anycase something to be aware of watch for.
     
    731592*/
    732593
     594
     595/* even indexed this takes forever to run
     596select * from INDICATOR_VIEW_VALUE ivv
     597where not exists
     598        (
     599        select 1
     600        from TMP_DATASET_RECORD tdr
     601        where tdr.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
     602          and tdr.DIMENSION_1_NAME  = ivv.DIMENSION_1_NAME
     603          and tdr.DIMENSION_1_VALUE = ivv.DIMENSION_1_VALUE
     604          and tdr.DIMENSION_2_NAME  = ivv.DIMENSION_2_NAME
     605          and tdr.DIMENSION_2_VALUE = ivv.DIMENSION_2_VALUE
     606          and tdr.PERIOD_DIMENSION_NAME  = ivv.PERIOD_DIMENSION_NAME
     607          and tdr.PERIOD_DIMENSION_VALUE = ivv.PERIOD_DIMENSION_VALUE
     608        )
     609order by INDICATOR_VIEW_NAME
     610limit 100
     611;
     612*/
     613
     614
     615/* this does the same thing but MySQL doesn't support minus
     616select INDICATOR_VIEW_NAME,
     617        DIMENSION_1_NAME, DIMENSION_1_VALUE,
     618        DIMENSION_2_NAME, DIMENSION_2_VALUE,
     619        PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE
     620from INDICATOR_VIEW_VALUE
     621minus
     622select ivv.INDICATOR_VIEW_NAME,
     623        ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE,
     624        ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE,
     625        ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
     626from INDICATOR_VIEW_VALUE ivv, TMP_DATASET_RECORD tdr
     627where tdr.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
     628  and tdr.DIMENSION_1_NAME  = ivv.DIMENSION_1_NAME
     629  and tdr.DIMENSION_1_VALUE = ivv.DIMENSION_1_VALUE
     630  and tdr.DIMENSION_2_NAME  = ivv.DIMENSION_2_NAME
     631  and tdr.DIMENSION_2_VALUE = ivv.DIMENSION_2_VALUE
     632  and tdr.PERIOD_DIMENSION_NAME  = ivv.PERIOD_DIMENSION_NAME
     633  and tdr.PERIOD_DIMENSION_VALUE = ivv.PERIOD_DIMENSION_VALUE
     634order by INDICATOR_VIEW_NAME
     635;
     636*/
     637
     638
     639issue appears to be dup keys for different IPVs with different measure values...
     640
     641select ivv.INDICATOR_VIEW_NAME,
     642        ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE,
     643        ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE,
     644        ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
     645from INDICATOR_VIEW_VALUE ivv
     646left join TMP_DATASET_RECORD tdr on
     647  tdr.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
     648  and tdr.DIMENSION_1_NAME  = ivv.DIMENSION_1_NAME
     649  and tdr.DIMENSION_1_VALUE = ivv.DIMENSION_1_VALUE
     650  and tdr.DIMENSION_2_NAME  = ivv.DIMENSION_2_NAME
     651  and tdr.DIMENSION_2_VALUE = ivv.DIMENSION_2_VALUE
     652  and tdr.PERIOD_DIMENSION_NAME  = ivv.PERIOD_DIMENSION_NAME
     653  and tdr.PERIOD_DIMENSION_VALUE = ivv.PERIOD_DIMENSION_VALUE
     654order by INDICATOR_VIEW_NAME
     655;
     656
     657select *
     658from TMP_DATASET_RECORD
     659group by INDICATOR_NAME, MEASURE_NAME,
     660        DIMENSION_1_NAME, DIMENSION_1_VALUE,
     661        DIMENSION_2_NAME, DIMENSION_2_VALUE,
     662        PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE
     663having count(MEASURE_NAME) > 1
     664  and count(DIMENSION_1_NAME) > 1
     665  and count(DIMENSION_1_VALUE) > 1
     666  and count(DIMENSION_2_NAME) > 1
     667  and count(DIMENSION_2_VALUE) > 1
     668  and count(PERIOD_DIMENSION_NAME) > 1
     669  and count(PERIOD_DIMENSION_VALUE) > 1
     670;
     671
     672
     673
     674
     675
     676
     677select INDICATOR_NAME, count(INDICATOR_VIEW_NAME) count_ivn,
     678        MEASURE_NAME,
     679        PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE,
     680        DIMENSION_1_NAME, DIMENSION_1_VALUE,
     681        DIMENSION_2_NAME, DIMENSION_2_VALUE
     682from TMP_DATASET_RECORD
     683where INDICATOR_NAME like 'LBW%'
     684group by INDICATOR_NAME, MEASURE_NAME,
     685        DIMENSION_1_NAME, DIMENSION_1_VALUE,
     686        DIMENSION_2_NAME, DIMENSION_2_VALUE,
     687        PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE
     688having count(INDICATOR_VIEW_NAME) > 1
     689;
     690
     691
     692select INDICATOR_VIEW_NAME,
     693        MEASURE_NAME,
     694        PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE,
     695        DIMENSION_1_NAME, DIMENSION_1_VALUE,
     696        DIMENSION_2_NAME, DIMENSION_2_VALUE
     697from TMP_DATASET_RECORD
     698where INDICATOR_NAME like 'LBW%'
     699  and PERIOD_DIMENSION_NAME = 'Year'
     700  and PERIOD_DIMENSION_VALUE = 2019
     701  and DIMENSION_1_NAME = 'Country'
     702  and DIMENSION_1_VALUE = 'US'
     703  and MEASURE_NAME = 'PctBirthLow'
     704;
     705
     706
     707
     708select INDICATOR_NAME, count(INDICATOR_VIEW_NAME) count_ivn,
     709        MEASURE_NAME,
     710        PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE,
     711        DIMENSION_1_NAME, DIMENSION_1_VALUE,
     712        DIMENSION_2_NAME, DIMENSION_2_VALUE
     713from TMP_DATASET_RECORD
     714group by INDICATOR_NAME, MEASURE_NAME,
     715        DIMENSION_1_NAME, DIMENSION_1_VALUE,
     716        DIMENSION_2_NAME, DIMENSION_2_VALUE,
     717        PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE
     718having count(INDICATOR_VIEW_NAME) > 1
     719limit 5000
     720;
     721==> 3021
     722
     723
     724select distinct(INDICATOR_NAME) from
     725(
     726select INDICATOR_NAME from TMP_DATASET_RECORD
     727group by INDICATOR_NAME, MEASURE_NAME,
     728        DIMENSION_1_NAME, DIMENSION_1_VALUE,
     729        DIMENSION_2_NAME, DIMENSION_2_VALUE,
     730        PERIOD_DIMENSION_NAME, PERIOD_DIMENSION_VALUE
     731having count(INDICATOR_VIEW_NAME) > 1
     732) as a
     733;
     734==>
     735
     736will need a mapping file for source IP with a new name and all the assoc
     737views.  Script would then need to create all the data sources and other
     738related intersections as well as renaming views and view values. 
     739
    733740/*------------------------------ END OF FILE ------------------------------*/
  • trunk/db/src/main/scripts/mysql/nj/1_data-ip_ipv_rename.sql

    r24813 r25029  
    1111*/
    1212
    13 /* since IP and IPV names are unique use same table for both */
    14 drop table if exists TMP_IP23NAME_IP3NAME;
     13/* since IP and IPV names are unique use same table for both.
     14        See tab_c_tmp.sql for table create.
    1515create table if not exists TMP_IP23NAME_IP3NAME (
    1616  NAME2                           varchar  (100)  not null,
     
    1919    primary key (NAME2, NAME3)
    2020);
    21 
    22 
    23 select * from TMP_IP23NAME_IP3NAME;
    24 
    25 /* Paste Data either MS-Access or via MySQL Workbench. */
    26 
    2721delete from TMP_IP23NAME_IP3NAME where binary NAME2 = NAME3;
    28 
    29 
     22*/
    3023
    3124update INDICATOR set NAME = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where NAME2 = NAME), NAME);
     
    7164select * 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');
    7265
     66
     67
     68
     69
     70
     71
     72split:
     734 columns 2.3 ip name to new 3 ip name, same for views...
     74
     75insert 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
     85select 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
     94from INDICATOR i, TMP_SPLIT_IP t
     95where i.NAME = t.CURRENT_NAME
     96;
     97
     98insert 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
     113select tmp.NEW_NAME, xyz
     114from INDICATOR_VIEW iv, TMP_SPLIT_IP t
     115where iv.NAME = CURRENT_NAME
     116;
     117
     118insert 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
     133select tmp.NEW_NAME, xyz
     134from INDICATOR_VIEW_VALUE ivv, TMP_SPLIT_IP t
     135where ivv.INDICATOR_VIEWNAME = CURRENT_NAME
     136;
     137
     138
     139indicator_to_relation
     140INDICATOR_NAME, RELATION_NAME, TEXT
     141
     142indicator_to_indicator_set
     143INDICATOR_NAME, INDICATOR_SET_NAME, SORT_ORDER
     144
     145indicator_to_indicator
     146INDICATOR_NAME, RELATION_NAME, RELATED_INDICATOR_NAME, SORT_ORDER
     147
     148data_source_to_indicator_view
     149DATA_SOURCE_NAME, INDICATOR_VIEW_NAME, SORT_ORDER
     150
     151user_to_indicator
     152INDICATOR_NAME, USER_ID
     153
     154
    73155/*------------------------------ END OF FILE ------------------------------*/
  • trunk/db/src/main/scripts/mysql/nj/2_data-iv_measure_name.sql

    r24764 r25029  
    1 /* Sets the IV Measure Name.  Again temp table pop from Kim's Excel Sheet */
    2 
    3 drop table if exists TMP_IPV_MEASURE;
    4 create table if not exists TMP_IPV_MEASURE (
    5   IPV_NAME3                       varchar  (100)  not null,
    6   MEASURE_NAME                    varchar  (100)  not null,
    7   MEASURE_TITLE_OVERRIDE          varchar  (100),
    8   constraint TMP_IPV_MEASURE_PK
    9     primary key (IPV_NAME3, MEASURE_NAME)
    10 );
    11 
    12 
    13 select * from TMP_IPV_MEASURE;
    14 
    15 /* Paste Data either MS-Access or via MySQL Workbench. */
    16 
    17 select * from TMP_IPV_MEASURE;
    18 
    191
    202update INDICATOR_VIEW set MEASURE_NAME           = ifnull  ((select MEASURE_NAME from TMP_IPV_MEASURE where IPV_NAME3 = NAME), 'DEFAULT');
  • trunk/db/src/main/scripts/mysql/nj/3_data-data_source_to_ipv.sql

    r24813 r25029  
    11/* Update the DS to IPV with the new datasource name and sort order */
    2 
    3 drop table if exists TMP_DS23NAME_DS3NAME;
    4 create table if not exists TMP_DS23NAME_DS3NAME (
    5   DATA_SOURCE_NAME2                varchar  (100)  not null,
    6   DATA_SOURCE_NAME3                varchar  (100)  not null,
    7   SORT_ORDER                       int,
    8   constraint TMP_DS23NAME_DS3NAME_PK
    9     primary key (DATA_SOURCE_NAME2, DATA_SOURCE_NAME3)
    10 );
    11 
    12 
    13 KIMS SPREADSHEET NOTES:
    14 1) Manually reorder data source columns to match TMP table order.
    15 2) Unmerged rows and duplicated data where old DS now maps to single v3 DS. 
    16 ??? use SO = 999 to remove unused records ???   
    17 ??? assume TITLE and TEXT will be manually updated by Kim either before or after migration run ???
    18 
    19 
    20 delete from TMP_DS23NAME_DS3NAME;
    21 select * from TMP_DS23NAME_DS3NAME;
    22 
    23 /* Paste Data either MS-Access or via MySQL Workbench. */
    24 
    25 select * from TMP_DS23NAME_DS3NAME;
    26 
    272
    283
    294/* do some basic tests */
     5select * from TMP_DS23NAME_DS3NAME;
     6
    307select count(*) from INDICATOR_VIEW;
    318select count(*) from DATA_SOURCE;
  • trunk/db/src/main/scripts/mysql/nj/4_data-iv_dimensions.sql

    r24813 r25029  
    88*/
    99
    10 drop table if exists TMP_DSC23_DIM;
    11 create table if not exists TMP_DSC23_DIM (
    12   DSC_NAME                        varchar  (100)  not null,
    13   DSC_VALUE                       varchar  (100)  not null,
    14   DIMENSION_NAME                  varchar  (100)  not null,
    15   DIMENSION_VALUE                 varchar  (100)  not null,
    16   constraint TMP_DSC23_DIM_PK
    17     primary key (DSC_NAME, DSC_VALUE, DIMENSION_NAME, DIMENSION_VALUE)
    18 );
    19 
    2010select * from TMP_DSC23_DIM;
    21 
    22 /* Paste Data either MS-Access or via MySQL Workbench. */
    23 
    24 select * from TMP_DSC23_DIM;
    25 
    26 
    27 
    28 drop table if exists TMP_DSS23_DIM;
    29 create table if not exists TMP_DSS23_DIM (
    30   DSS_NAME                        varchar  (100)  not null,
    31   DSS_VALUE                       varchar  (100)  not null,
    32   DIMENSION_NAME                  varchar  (100)  not null,
    33   DIMENSION_VALUE                 varchar  (100)  not null,
    34   constraint TMP_DSS23_DIM_PK
    35     primary key (DSS_NAME, DSS_VALUE, DIMENSION_NAME, DIMENSION_VALUE)
    36 );
    37 
    3811select * from TMP_DSS23_DIM;
    39 
    40 /* Paste Data either MS-Access or via MySQL Workbench. */
    41 
    42 select * from TMP_DSS23_DIM;
    43 
    44 
    45 
    46 drop table if exists TMP_IPV_CONSTANT_DIM_NAME;
    47 create table if not exists TMP_IPV_CONSTANT_DIM_NAME (
    48   INDICATOR_VIEW_NAME             varchar  (100)  not null,
    49   DIMENSION_NAME                  varchar  (100)  not null,
    50   DIMENSION_VALUE                 varchar  (100)  not null,
    51   constraint TMP_IPV_CONSTANT_DIM_NAME_PK
    52     primary key (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
    53 );
    54 
    5512select * from TMP_IPV_CONSTANT_DIM_NAME;
    56 
    57 /* Paste Data either MS-Access or via MySQL Workbench. */
    58 
    59 select * from TMP_IPV_CONSTANT_DIM_NAME;
    60 
    6113
    6214
  • trunk/db/src/main/scripts/mysql/nj/update.sql

    r24813 r25029  
    11/* script needed to convert db from 2.3 to 3.0
    22
    3         0) Prior to handing off for migration the db to be migrated should have:
     3        *** Make sure all new records have their ACTIVE_FLAG set appropriately.
     4
     5        0.1) Prior to handing off for migration the db to be migrated should have:
    46                - Indicator and View records to be removed have sort order set to 999 and 99.
    57                - Data source to indicator view sort orders set.
     
    810                        values that match associated dimension title/values.
    911                - All other items listed in Lois's doc.
     12        0.1) Adopter freezes 2.3 database.
     13        0.2) Export production 2.3 db (mysql files or .sql export)
    1014
    11         1) Create/add the new v3 tables.
    12         2) Update tables - add/rename the new v3 columns.
    13         3) Insert new validation data (either via SQL script or copy db files):
    14              - topics,
    15                  - initiatives,
    16                  - measures,
    17                  - value attr & type,
    18                  - dimension and assoc values.
     15        ----------- Step 1.x - Prep 2.3 Migration
    1916
    20                 *** Repair tables due to struct and data changes.
     17        1.0) Import 2.3 db into development enviro
     18        1.1) Create/add the new v3 tables.
     19        1.2) Update tables - add/rename the new v3 columns.
    2120
    22         4) Cleanup
     21        1.3) Add/Edit standard validation data as needed (typically NM db table
     22                export or mysql file, or edit table directly):
     23             - TOPIC, MEASURE, VALUE_TYPE, VALUE_ATTRIBUTE, ANCILLARY_VALUE, INDICATOR_SET
     24                 - INITIATIVE, INITIATIVE_TOPIC, INITIATIVE_TOPIC_OBJECTIVE, INITIATIV_TOPIC_OBJ_TO_INDICAT
     25
     26        1.4) Create temp 2.3 to 3 mapping tables.
     27        1.5) Zip mysql db files and send to Kim (or do an export).
     28
     29        ----------- Step 2.x - Prep Data
     30
     31        2.0) Kim updates local enviro with zipped mysql files or imports .sql.
     32        2.1) Kim populates/udpates validation tables:
     33                DATA_SOURCE, MEASURE, DIMENSION, DIM_VALUE, VALUE_TYPE, TOPIC, etc.
     34        2.2) Kim populates temp mapping tables.
     353.2 Kim updates IV series for the constant
     36        2.3) Kim updates the IV "None" DS series to set single use constant type
     37                view uses.  This is when an adopter wants a Male or Female only view
     38                by county and a specific year.  The year is the constant with county
     39                being the category.
     40        2.4) Kim zips entire mysql db and sends to Garth (or does an export).
     41
     42**** look at the 2.3 series being changed from none to something for the constant handling
     43**** add temp tables to the create script to send to kim
     44**** creating the IP and IPV name splits
     45
     46        ----------- Step 3.x - Migrate
     47
     48        3.0) Import enhanced 2.3 db into development enviro
     49        3.1) Cleanup
    2350                - delete orphaned IP and IPV and IPVV.
    2451                - delete orphaned DSC and DSS records.
     
    2855                - Other updates like user info etc.
    2956
    30         5) IV and IVV Updates
     57        3.2) IV and IVV Updates
    3158                - Set the IV MEASURE_NAME and IP value type.
    3259
     
    3966                series names that match the temp table mappings.
    4067               
    41         6) Create the view dimensions records
     68        3.3) Create the view dimensions records
    4269                - Set each view's series, category, and/or constant dimension usage. 
    4370                        At a minimum each view MUST have 2 dimension usages specified.  One
     
    4673                        MUST have another series or constant specified.
    4774
    48         7) Create each view's dimension values records. 
     75        3.4) Create each view's dimension values records. 
    4976                - Setup view's dimension values based on current usage.
    5077       
    51         8) Create the indicator datasets based on the assoc IV via a temp table.
    52         9) Create the dataset's dims - again based on the IV using the temp
     78        3.5) Create the indicator datasets based on the assoc IV via a temp table.
     79        3.6) Create the dataset's dims - again based on the IV using the temp
    5380                table.
    5481
    55         10) Create each dataset's value records.
     82        3.7) Create each dataset's value records.
    5683                - Based on the assoc IVVs, dataset, and dataset dimensions.
    5784                - add in the associated ancillary values.
    5885
    59         11) drop old unused columns
    60         12) drop temp and old unused tables
     86        3.8) drop old unused columns
     87        3.9) drop temp and old unused tables
     88        3.10) export the migrated version 3 db.
     89       
     90        ----------- Step 4.x - Deploy
     91
     92        4.0 Import the migrated version 3 db.
     93        4.1 Install and configure the v3 admin app.
     94        4.2 Manually edit data via admin app or direct table edit or via scripts
     95                - data-indicator_topics.sql                     
     96                - data-indicator_indicator_set.sql
     97                - data-indicator_initiatives.sql
     98                - User privs
     99                - Split indicators and views
     100                - Any other one offs that need to be entered by hand.
    61101*/
    62102use nj;
     
    64104show table status from nj;
    65105
    66 
     106/* ---------------- Step 1.x - Prep 2.3 Migration */
    67107/* Create the new v3 tables: */
    68108        tab_c-dataset.sql
     
    73113        update_tabs.sql
    74114
    75         table-repair.sql
    76 
    77 /* Manually entered validation data:
    78         Use Kim's MySQL data files:
     115/* ---------------- Step 2.x - Manual Migration Data Updates */
     116/* Send to Kim, Integrate any validation data:
    79117        value_type
    80118        measure
     
    84122*/
    85123
    86 /* inserts some basic records: */
    87         data_other.sql
    88124
    89 
    90 /* Data update scripts: */
     125/* ---------------- Step 3.x - Run Migration Scripts */
    91126        /*
    92127                remove 99 and 999 SO IP and IPVs,
     
    98133
    99134        2_data-iv_measure_name.sql                      /* Kims mapping with MEASURE_TITLE_OVERRIDE IVN, MN, CMT */
    100         3_data-data_source_to_ipv.sql           /* Kims mapping with SO */?
     135        3_data-data_source_to_ipv.sql           /* Kims mapping with SO */
    101136
    102137        4_data-iv_dimensions.sql                /* Kim's mapping - will send when finished DSC & S : VALUE to DIM NAME : VALUE */
     
    111146        update_cleanup.sql       /* Drop temp, unused v3, deprecated v2.3 tables and columns */
    112147
    113 
    114 /* Manually add NM data - run insert statements via export or MySQL data files: */
    115         nm-topic.sql
    116         nm-initiative.sql  (this involves multiple tables)
    117         nm-ancillary_value.sql
    118         nm-value_attribute.sql
    119         nm-indicator_set.sql
    120 
    121 /* Manually edit data via admin app or direct table edit or via scripts */
    122         data-indicator_topics.sql                       
    123         data-indicator_indicator_set.sql
    124         data-indicator_initiatives.sql
    125 
    126148/*------------------------------ END OF FILE ------------------------------*/
    127149
  • trunk/db/src/main/scripts/mysql/update_cleanup.sql

    r24805 r25029  
    3737drop table if exists TMP_DSC23_DIM;
    3838drop table if exists TMP_DSS23_DIM;
    39 
    4039drop table if exists TMP_IPV_CONSTANT_DIM_NAME;
    41 
     40drop table if exists TMP_SPLIT_IP;
    4241
    4342
Note: See TracChangeset for help on using the changeset viewer.