Changeset 25056 in main
- Timestamp:
- 04/14/22 12:45:20 (5 weeks ago)
- Location:
- trunk/db/src/main/scripts/mysql/migrate
- Files:
-
- 2 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/db/src/main/scripts/mysql/migrate/tab_c-tmp.sql
r25033 r25056 20 20 21 21 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. 25 */ 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) 34 ); 35 36 22 37 /* Used to set the IV Measure Name. */ 23 38 drop table if exists TMP_IPV_MEASURE; … … 28 43 constraint TMP_IPV_MEASURE_PK 29 44 primary key (IPV_NAME3, MEASURE_NAME) 30 );31 32 33 /* Used to rename data sources.34 KIMS SPREADSHEET NOTES:35 1) Manually reorder data source columns to match TMP table order.36 2) Unmerged rows and duplicated data where old DS now maps to single v3 DS.37 ??? use SO = 999 to remove unused records ???38 ??? assume TITLE and TEXT will be manually updated by Kim either before or after migration run ???39 */40 drop table if exists TMP_DS23NAME_DS3NAME;41 create table if not exists TMP_DS23NAME_DS3NAME (42 DATA_SOURCE_NAME2 varchar (100) not null,43 DATA_SOURCE_NAME3 varchar (100) not null,44 SORT_ORDER int,45 constraint TMP_DS23NAME_DS3NAME_PK46 primary key (DATA_SOURCE_NAME2, DATA_SOURCE_NAME3)47 45 ); 48 46 … … 86 84 ); 87 85 88 89 90 /* Used to split IP and IPVs. Since IP and IPV names are unique script uses 91 the same table for both split opertations. 92 all NAMEs are v3 names. 93 */ 94 drop table if exists TMP_SPLIT_IP_IPV; 95 create table if not exists TMP_SPLIT_IP_IPV ( 96 SOURCE_IP_NAME varchar (100) not null, 97 SOURCE_IPV_NAME varchar (100) not null, 98 NEW_IP_NAME varchar (100) not null, 99 NEW_IPV_NAME varchar (100) not null, 100 constraint TMP_SPLIT_IP_IPV_PK 101 primary key (SOURCE_IPV_NAME, NEW_IPV_NAME) 102 ); 103 86 /*------------------------------ END OF FILE ------------------------------*/ -
trunk/db/src/main/scripts/mysql/migrate/update.sql
r25052 r25056 42 42 [2.0] Optional - if Kim running everything this is not needed. Otherwise 43 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, 46 DATA_SOURCE_TO_INDICATOR_VIEW (data source to indicator view sort 47 orders set as per Lois). 48 2.2) Kim populates temp mapping tables. 49 TMP_SPLIT_IP, TMP_IP23NAME_IP3NAME, TMP_IPV_MEASURE, TMP_DS23NAME_DS3NAME, 44 2.1) Kim populates/udpates any adopter specific validation tables: 45 MEASURE, DIMENSION, DIMENSION_VALUE, VALUE_TYPE, DATA_SOURCE 46 2.2) Kim updates the DATA_SOURCE_TO_INDICATOR_VIEW table. This table needs 47 to be 100% complete with all version 3 DATA_SOURCE_NAME and 48 INDICATOR_VIEW_NAME values. Needs to include any split IPV and all 49 SORT_ORDER value must be set correctly (as per Lois). 50 2.3) Kim populates temp mapping tables. 51 TMP_SPLIT_IP, TMP_IP23NAME_IP3NAME, TMP_IPV_MEASURE, 50 52 TMP_DSC23_DIM, TMP_DSS23_DIM, TMP_IPV_CONSTANT_DIM_NAME. 51 2. 3) Kim updates the IV "None" DS series to set single use, "constant" type53 2.4) Kim updates the IV "None" DS series to set single use, "constant" type 52 54 (not a constant but a single value series). For example an adopter 53 55 wants a view for Male and view for Female by county and a specific year. 54 56 The year is the constant with county being the category. 55 2. 4) Kim updates the associated INDICATOR_VIEW_VALUE "None" type DS series57 2.5) Kim updates the associated INDICATOR_VIEW_VALUE "None" type DS series 56 58 to set single use, "constant" type (not a constant but a single value 57 59 series) but needs to have the specific SERIES VALUE set (Male=1, FM=2). 58 60 59 [2. 5] Optional - if Kim running everything then not needed. Else Kim zips61 [2.6] Optional - if Kim running everything then not needed. Else Kim zips 60 62 entire mysql db and sends to Garth (or does an export). 61 63 … … 80 82 - Sets the IV measure name and title based on TMP_IPV_MEASURE. 81 83 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. 84 3.5) DS to IPV updates (script N/A). This step was removed 4/14/2022 as per 85 Kim and Garth decision to have Kim update the DS to IPV table to be 100% 86 v3 complete which includes all IPV splits, v3 data source names, v3 IPV 87 names, and correct sort order used to determine numerator and denominator. 85 88 86 89 3.6) Sets the new IV dim names - map, period, ser, cat, const (script 6). … … 176 179 177 180 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 181 6_data-iv_dimensions.sql /* Kim's mapping - will send when finished DSC & S : VALUE to DIM NAME : VALUE */ 180 182 7_data-ivv_dimensions.sql
Note: See TracChangeset
for help on using the changeset viewer.