Changeset 25095 in main


Ignore:
Timestamp:
04/21/22 23:31:47 (4 months ago)
Author:
GarthBraithwaite_STG
Message:

db - minor syntax tweaks.

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

Legend:

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

    r25029 r25095  
    133133  and dstiv.SORT_ORDER = 3
    134134group by 2, 3, 4, 5, 6, 7
    135 
     135;
    136136
    137137
  • trunk/db/src/main/scripts/mysql/migrate/11_data-dataset_record.sql

    r25029 r25095  
    537537;
    538538
    539 update ibisph.DIMENSION_TO_DATASET dtd
     539update DIMENSION_TO_DATASET dtd
    540540set DIMENSION_USAGE = 'dimension1or2'
    541541where DIMENSION_USAGE = 'dimension1'
    542542  and DIMENSION_NAME in   
    543543        (select dr1.DIMENSION_1_NAME
    544          from ibisph.DATASET_RECORD dr1
     544         from DATASET_RECORD dr1
    545545         where dr1.DATASET_NAME = dtd.DATASET_NAME
    546            and dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from ibisph.DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
     546           and dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
    547547        )
    548548;
  • trunk/db/src/main/scripts/mysql/migrate/3_data-ip_ipv_split.sql

    r25052 r25095  
    2727        NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
    2828)
    29 select t.NEW_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME,
     29select t.NEW_IP_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME,
    3030        TITLE, CONCISE_TITLE,
    3131        DEFINITION, NUMERATOR, DENOMINATOR, DATA_NOTE, DATA_ISSUES, DATA_AS_OF_DATE,
     
    4444
    4545insert into INDICATOR_TO_INDICATOR (INDICATOR_NAME, RELATED_INDICATOR_NAME, RELATION_NAME)
    46 select t.NEW_NAME, RELATED_INDICATOR_NAME, RELATION_NAME
     46select t.NEW_IP_NAME, RELATED_INDICATOR_NAME, RELATION_NAME
    4747from INDICATOR_TO_INDICATOR iti, TMP_SPLIT_IP_IPV t
    48 where iti.NAME = t.SOURCE_IP_NAME
     48where iti.INDICATOR_NAME = t.SOURCE_IP_NAME
    4949  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
    5050  and not exists
     
    5858
    5959insert into INDICATOR_TO_RELATION (INDICATOR_NAME, RELATION_NAME, TEXT)
    60 select t.NEW_NAME, RELATION_NAME, TEXT
     60select t.NEW_IP_NAME, RELATION_NAME, TEXT
    6161from INDICATOR_TO_RELATION itr, TMP_SPLIT_IP_IPV t
    6262where itr.INDICATOR_NAME = t.SOURCE_IP_NAME
     
    6666                select 1 from INDICATOR_TO_RELATION itr2
    6767                where itr2.INDICATOR_NAME         = t.NEW_IP_NAME
    68                   and itr2.RELATION_NAME          = iti.RELATION_NAME
     68                  and itr2.RELATION_NAME          = itr.RELATION_NAME
    6969        )
    7070;
     
    7272insert into INDICATOR_TO_INDICATOR_SET
    7373(
    74         INDICATOR_NAME, INDICATOR_SET_NAME,
    75         COMPARISON_MEASURE_NAME, COMPARISON_PERIOD_DIMENSION_NAME,
    76         COMPARISON_PERIOD_DIMENSION_VALUE
    77 )
    78 select t.NEW_NAME, INDICATOR_SET_NAME,
    79          COMPARISON_MEASURE_NAME, COMPARISON_PERIOD_DIMENSION_NAME,
    80          COMPARISON_PERIOD_DIMENSION_VALUE
     74        INDICATOR_NAME, INDICATOR_SET_NAME, SORT_ORDER
     75)
     76select t.NEW_IP_NAME, itis.INDICATOR_SET_NAME, itis.SORT_ORDER
    8177from INDICATOR_TO_INDICATOR_SET itis, TMP_SPLIT_IP_IPV t
    8278where itis.INDICATOR_NAME = t.SOURCE_IP_NAME
     
    9187
    9288insert into USER_TO_INDICATOR (INDICATOR_NAME, USER_ID)
    93 select t.NEW_NAME, USER_ID
     89select t.NEW_IP_NAME, USER_ID
    9490from USER_TO_INDICATOR uti, TMP_SPLIT_IP_IPV t
    9591where uti.INDICATOR_NAME = t.SOURCE_IP_NAME
     
    197193/* finally set the default view name as the first assoc view */
    198194update INDICATOR i set DEFAULT_INDICATOR_VIEW_NAME =
     195(
    199196        select iv.NAME
    200197        from INDICATOR_VIEW iv
    201198        where i.NAME = iv.INDICATOR_NAME
    202199        limit 1
    203 ;
     200);
    204201
    205202/*------------------------------ END OF FILE ------------------------------*/
  • trunk/db/src/main/scripts/mysql/migrate/7_data-ivv_dimensions.sql

    r25052 r25095  
    204204where DIMENSION_1_NAME  is null
    205205  and DIMENSION_1_VALUE is null
     206  and DIMENSION_2_NAME  is null
     207  and DIMENSION_2_VALUE is null
    206208  and PERIOD_DIMENSION_NAME  is not null
    207209  and PERIOD_DIMENSION_VALUE is not null
  • trunk/db/src/main/scripts/mysql/migrate/9_data-iv_dimension_value.sql

    r25052 r25095  
    77        correct DIM and VALUE rows for the given IPV's DIMs and VALUES. 
    88*/
     9
     10/* check problem records */
     11select *
     12from INDICATOR_VIEW_VALUE ivv
     13where ACTIVE_FLAG = 'x'
     14  and ivv.DIMENSION_1_NAME  = ivv.DIMENSION_2_NAME
     15  and ivv.DIMENSION_1_VALUE = ivv.DIMENSION_2_VALUE
     16;
     17
    918
    1019delete from DIMENSION_VALUE_TO_IND_VIEW;
  • trunk/db/src/main/scripts/mysql/migrate/update.sql

    r25056 r25095  
    1515        0.3) Include items listed in Lois's doc that are not accounted for in Step 1.x.
    1616        0.4) Adopter freezes 2.3 database.
    17         0.5) Export 2.3 db (mysql files or .sql export)
     17        0.5) Export 2.3 db (mysql files or a .sql export).
    1818
    1919        ----------- Step 1.x - Prep 2.3 Migration
    2020
    2121        1.0) Import 2.3 db into a migration environment.
    22         1.1) Create/add the new v3 tables.
    23         1.2) Update tables - add/rename the new v3 columns.
     22        1.1) Create/add the new v3 tables (via tab_c-misc.sql script).
     23        1.2) Update tables - add/rename the new v3 columns (via tab_update.sql script).
    2424
    2525        1.3) Add/Edit standard validation data as needed (typically NM db table
     
    3232                These likely include:
    3333                        MEASURE, VALUE_TYPE, DATA_SOURCE, DIMENSION etc.
    34         1.4) Create temp 2.3 to 3 mapping tables (so Kim can populate in next section).
    35         1.5) Do basic orphaned record cleanup.
     34        1.4) Create temp 2.3 to 3 mapping tables (so Kim can populate in next step)
     35                (tab_c-tmp.sql script).
     36        1.5) Do basic orphaned record cleanup (via adopter_data-cleanup.sql script).
    3637        [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.
     38                vs Kim doing it all.  If Kim not doing it all then zip mysql myisam db 
     39                files or do an export via mysql workbench then send to Kim.
    3940
    4041        ----------- Step 2.x - Prep Data
    4142
    4243        [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                Kim updates her local mysql db with zipped mysql files or imports the
     45                step 1 exported .sql via the mysql workbench.
    4446        2.1) Kim populates/udpates any adopter specific validation tables:
    4547                        MEASURE, DIMENSION, DIMENSION_VALUE, VALUE_TYPE, DATA_SOURCE
     
    6062
    6163        [2.6] Optional - if Kim running everything then not needed.  Else Kim zips
    62                 entire mysql db and sends to Garth (or does an export).
     64                entire mysql db (or does an export via mysql workbench) and sends to Garth.
    6365
    6466        ----------- Step 3.x - Migrate
    6567
    6668        [3.0] Optional - if Kim running everything.  Otherwise Garth/Paul imports
    67                 Kim's prepped 2.3 db into development enviro.
     69                Kim's prepped step 2 2.3 db.
    6870        3.1) Cleanup (script 1).
    6971                - delete orphaned IP and IPV and IPVV.
     
    117119                - add in the associated ancillary values.
    118120
    119         3.13) Cleanup.
     121        3.13) Cleanup (via update_cleanup.sql script).
    120122                - drop old unused columns
    121123                - drop temp and old unused tables
  • trunk/db/src/main/scripts/mysql/migrate/update_cleanup.sql

    r25034 r25095  
    3535
    3636drop table if exists TMP_IP23NAME_IP3NAME;
     37drop table if exists TMP_SPLIT_IP_IPV;
    3738drop table if exists TMP_IPV_MEASURE;
    3839drop table if exists TMP_DS23NAME_DS3NAME;
     
    4041drop table if exists TMP_DSS23_DIM;
    4142drop table if exists TMP_IPV_CONSTANT_DIM_NAME;
    42 drop table if exists TMP_SPLIT_IP_IPV;
    4343
    4444
Note: See TracChangeset for help on using the changeset viewer.