Changeset 25095 in main
- Timestamp:
- 04/21/22 23:31:47 (4 weeks ago)
- 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 133 133 and dstiv.SORT_ORDER = 3 134 134 group by 2, 3, 4, 5, 6, 7 135 135 ; 136 136 137 137 -
trunk/db/src/main/scripts/mysql/migrate/11_data-dataset_record.sql
r25029 r25095 537 537 ; 538 538 539 update ibisph.DIMENSION_TO_DATASET dtd539 update DIMENSION_TO_DATASET dtd 540 540 set DIMENSION_USAGE = 'dimension1or2' 541 541 where DIMENSION_USAGE = 'dimension1' 542 542 and DIMENSION_NAME in 543 543 (select dr1.DIMENSION_1_NAME 544 from ibisph.DATASET_RECORD dr1544 from DATASET_RECORD dr1 545 545 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) 547 547 ) 548 548 ; -
trunk/db/src/main/scripts/mysql/migrate/3_data-ip_ipv_split.sql
r25052 r25095 27 27 NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION 28 28 ) 29 select t.NEW_ NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME,29 select t.NEW_IP_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME, 30 30 TITLE, CONCISE_TITLE, 31 31 DEFINITION, NUMERATOR, DENOMINATOR, DATA_NOTE, DATA_ISSUES, DATA_AS_OF_DATE, … … 44 44 45 45 insert into INDICATOR_TO_INDICATOR (INDICATOR_NAME, RELATED_INDICATOR_NAME, RELATION_NAME) 46 select t.NEW_ NAME, RELATED_INDICATOR_NAME, RELATION_NAME46 select t.NEW_IP_NAME, RELATED_INDICATOR_NAME, RELATION_NAME 47 47 from INDICATOR_TO_INDICATOR iti, TMP_SPLIT_IP_IPV t 48 where iti. NAME = t.SOURCE_IP_NAME48 where iti.INDICATOR_NAME = t.SOURCE_IP_NAME 49 49 and t.SOURCE_IP_NAME != t.NEW_IP_NAME 50 50 and not exists … … 58 58 59 59 insert into INDICATOR_TO_RELATION (INDICATOR_NAME, RELATION_NAME, TEXT) 60 select t.NEW_ NAME, RELATION_NAME, TEXT60 select t.NEW_IP_NAME, RELATION_NAME, TEXT 61 61 from INDICATOR_TO_RELATION itr, TMP_SPLIT_IP_IPV t 62 62 where itr.INDICATOR_NAME = t.SOURCE_IP_NAME … … 66 66 select 1 from INDICATOR_TO_RELATION itr2 67 67 where itr2.INDICATOR_NAME = t.NEW_IP_NAME 68 and itr2.RELATION_NAME = it i.RELATION_NAME68 and itr2.RELATION_NAME = itr.RELATION_NAME 69 69 ) 70 70 ; … … 72 72 insert into INDICATOR_TO_INDICATOR_SET 73 73 ( 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 ) 76 select t.NEW_IP_NAME, itis.INDICATOR_SET_NAME, itis.SORT_ORDER 81 77 from INDICATOR_TO_INDICATOR_SET itis, TMP_SPLIT_IP_IPV t 82 78 where itis.INDICATOR_NAME = t.SOURCE_IP_NAME … … 91 87 92 88 insert into USER_TO_INDICATOR (INDICATOR_NAME, USER_ID) 93 select t.NEW_ NAME, USER_ID89 select t.NEW_IP_NAME, USER_ID 94 90 from USER_TO_INDICATOR uti, TMP_SPLIT_IP_IPV t 95 91 where uti.INDICATOR_NAME = t.SOURCE_IP_NAME … … 197 193 /* finally set the default view name as the first assoc view */ 198 194 update INDICATOR i set DEFAULT_INDICATOR_VIEW_NAME = 195 ( 199 196 select iv.NAME 200 197 from INDICATOR_VIEW iv 201 198 where i.NAME = iv.INDICATOR_NAME 202 199 limit 1 203 ;200 ); 204 201 205 202 /*------------------------------ END OF FILE ------------------------------*/ -
trunk/db/src/main/scripts/mysql/migrate/7_data-ivv_dimensions.sql
r25052 r25095 204 204 where DIMENSION_1_NAME is null 205 205 and DIMENSION_1_VALUE is null 206 and DIMENSION_2_NAME is null 207 and DIMENSION_2_VALUE is null 206 208 and PERIOD_DIMENSION_NAME is not null 207 209 and PERIOD_DIMENSION_VALUE is not null -
trunk/db/src/main/scripts/mysql/migrate/9_data-iv_dimension_value.sql
r25052 r25095 7 7 correct DIM and VALUE rows for the given IPV's DIMs and VALUES. 8 8 */ 9 10 /* check problem records */ 11 select * 12 from INDICATOR_VIEW_VALUE ivv 13 where 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 9 18 10 19 delete from DIMENSION_VALUE_TO_IND_VIEW; -
trunk/db/src/main/scripts/mysql/migrate/update.sql
r25056 r25095 15 15 0.3) Include items listed in Lois's doc that are not accounted for in Step 1.x. 16 16 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). 18 18 19 19 ----------- Step 1.x - Prep 2.3 Migration 20 20 21 21 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). 24 24 25 25 1.3) Add/Edit standard validation data as needed (typically NM db table … … 32 32 These likely include: 33 33 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). 36 37 [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 send38 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. 39 40 40 41 ----------- Step 2.x - Prep Data 41 42 42 43 [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. 44 46 2.1) Kim populates/udpates any adopter specific validation tables: 45 47 MEASURE, DIMENSION, DIMENSION_VALUE, VALUE_TYPE, DATA_SOURCE … … 60 62 61 63 [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. 63 65 64 66 ----------- Step 3.x - Migrate 65 67 66 68 [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. 68 70 3.1) Cleanup (script 1). 69 71 - delete orphaned IP and IPV and IPVV. … … 117 119 - add in the associated ancillary values. 118 120 119 3.13) Cleanup .121 3.13) Cleanup (via update_cleanup.sql script). 120 122 - drop old unused columns 121 123 - drop temp and old unused tables -
trunk/db/src/main/scripts/mysql/migrate/update_cleanup.sql
r25034 r25095 35 35 36 36 drop table if exists TMP_IP23NAME_IP3NAME; 37 drop table if exists TMP_SPLIT_IP_IPV; 37 38 drop table if exists TMP_IPV_MEASURE; 38 39 drop table if exists TMP_DS23NAME_DS3NAME; … … 40 41 drop table if exists TMP_DSS23_DIM; 41 42 drop table if exists TMP_IPV_CONSTANT_DIM_NAME; 42 drop table if exists TMP_SPLIT_IP_IPV;43 43 44 44
Note: See TracChangeset
for help on using the changeset viewer.