Changeset 25095 in main

Timestamp:

04/21/22 23:31:47 (4 weeks ago)

Author:

GarthBraithwaite_STG

Message:

db - minor syntax tweaks.

Location:

trunk/db/src/main/scripts/mysql/migrate

Files:

• 10_data-dataset.sql (modified) (1 diff)
• 11_data-dataset_record.sql (modified) (1 diff)
• 3_data-ip_ipv_split.sql (modified) (7 diffs)
• 7_data-ivv_dimensions.sql (modified) (1 diff)
• 9_data-iv_dimension_value.sql (modified) (1 diff)
• update.sql (modified) (4 diffs)
• update_cleanup.sql (modified) (2 diffs)

trunk/db/src/main/scripts/mysql/migrate/10_data-dataset.sql

@@ -133 +133 @@
   and dstiv.SORT_ORDER = 3
 group by 2, 3, 4, 5, 6, 7
-
+;
 
 

trunk/db/src/main/scripts/mysql/migrate/11_data-dataset_record.sql

@@ -537 +537 @@
 ;
 
-update ibisph.DIMENSION_TO_DATASET dtd
+update DIMENSION_TO_DATASET dtd
 set DIMENSION_USAGE = 'dimension1or2'
 where DIMENSION_USAGE = 'dimension1'
   and DIMENSION_NAME in   
         (select dr1.DIMENSION_1_NAME 
-         from ibisph.DATASET_RECORD dr1
+         from DATASET_RECORD dr1
          where dr1.DATASET_NAME = dtd.DATASET_NAME
-           and dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from ibisph.DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
+           and dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
         )
 ;

trunk/db/src/main/scripts/mysql/migrate/3_data-ip_ipv_split.sql

@@ -27 +27 @@
         NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
 )
-select t.NEW_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME, 
+select t.NEW_IP_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME, 
         TITLE, CONCISE_TITLE, 
         DEFINITION, NUMERATOR, DENOMINATOR, DATA_NOTE, DATA_ISSUES, DATA_AS_OF_DATE,
@@ -44 +44 @@
 
 insert into INDICATOR_TO_INDICATOR (INDICATOR_NAME, RELATED_INDICATOR_NAME, RELATION_NAME)
-select t.NEW_NAME, RELATED_INDICATOR_NAME, RELATION_NAME
+select t.NEW_IP_NAME, RELATED_INDICATOR_NAME, RELATION_NAME
 from INDICATOR_TO_INDICATOR iti, TMP_SPLIT_IP_IPV t
-where iti.NAME = t.SOURCE_IP_NAME
+where iti.INDICATOR_NAME = t.SOURCE_IP_NAME
   and t.SOURCE_IP_NAME != t.NEW_IP_NAME
   and not exists
@@ -58 +58 @@
 
 insert into INDICATOR_TO_RELATION (INDICATOR_NAME, RELATION_NAME, TEXT)
-select t.NEW_NAME, RELATION_NAME, TEXT
+select t.NEW_IP_NAME, RELATION_NAME, TEXT
 from INDICATOR_TO_RELATION itr, TMP_SPLIT_IP_IPV t
 where itr.INDICATOR_NAME = t.SOURCE_IP_NAME
@@ -66 +66 @@
                 select 1 from INDICATOR_TO_RELATION itr2
                 where itr2.INDICATOR_NAME         = t.NEW_IP_NAME
-                  and itr2.RELATION_NAME          = iti.RELATION_NAME
+                  and itr2.RELATION_NAME          = itr.RELATION_NAME
         )
 ;
@@ -72 +72 @@
 insert into INDICATOR_TO_INDICATOR_SET 
 (
-        INDICATOR_NAME, INDICATOR_SET_NAME,
-        COMPARISON_MEASURE_NAME, COMPARISON_PERIOD_DIMENSION_NAME, 
-        COMPARISON_PERIOD_DIMENSION_VALUE
-)
-select t.NEW_NAME, INDICATOR_SET_NAME,
-         COMPARISON_MEASURE_NAME, COMPARISON_PERIOD_DIMENSION_NAME, 
-         COMPARISON_PERIOD_DIMENSION_VALUE
+        INDICATOR_NAME, INDICATOR_SET_NAME, SORT_ORDER
+)
+select t.NEW_IP_NAME, itis.INDICATOR_SET_NAME, itis.SORT_ORDER
 from INDICATOR_TO_INDICATOR_SET itis, TMP_SPLIT_IP_IPV t
 where itis.INDICATOR_NAME = t.SOURCE_IP_NAME
@@ -91 +87 @@
 
 insert into USER_TO_INDICATOR (INDICATOR_NAME, USER_ID)
-select t.NEW_NAME, USER_ID
+select t.NEW_IP_NAME, USER_ID
 from USER_TO_INDICATOR uti, TMP_SPLIT_IP_IPV t
 where uti.INDICATOR_NAME = t.SOURCE_IP_NAME
@@ -197 +193 @@
 /* finally set the default view name as the first assoc view */
 update INDICATOR i set DEFAULT_INDICATOR_VIEW_NAME = 
+(
         select iv.NAME 
         from INDICATOR_VIEW iv
         where i.NAME = iv.INDICATOR_NAME
         limit 1
-;
+);
 
 /*------------------------------ END OF FILE ------------------------------*/

trunk/db/src/main/scripts/mysql/migrate/7_data-ivv_dimensions.sql

@@ -204 +204 @@
 where DIMENSION_1_NAME  is null
   and DIMENSION_1_VALUE is null
+  and DIMENSION_2_NAME  is null
+  and DIMENSION_2_VALUE is null
   and PERIOD_DIMENSION_NAME  is not null
   and PERIOD_DIMENSION_VALUE is not null

trunk/db/src/main/scripts/mysql/migrate/9_data-iv_dimension_value.sql

@@ -7 +7 @@
         correct DIM and VALUE rows for the given IPV's DIMs and VALUES.  
 */
+
+/* check problem records */
+select *
+from INDICATOR_VIEW_VALUE ivv
+where ACTIVE_FLAG = 'x'
+  and ivv.DIMENSION_1_NAME  = ivv.DIMENSION_2_NAME
+  and ivv.DIMENSION_1_VALUE = ivv.DIMENSION_2_VALUE
+;
+
 
 delete from DIMENSION_VALUE_TO_IND_VIEW;

trunk/db/src/main/scripts/mysql/migrate/update.sql

@@ -15 +15 @@
         0.3) Include items listed in Lois's doc that are not accounted for in Step 1.x.
         0.4) Adopter freezes 2.3 database.
-        0.5) Export 2.3 db (mysql files or .sql export)
+        0.5) Export 2.3 db (mysql files or a .sql export).
 
         ----------- Step 1.x - Prep 2.3 Migration
 
         1.0) Import 2.3 db into a migration environment.
-        1.1) Create/add the new v3 tables.
-        1.2) Update tables - add/rename the new v3 columns.
+        1.1) Create/add the new v3 tables (via tab_c-misc.sql script).
+        1.2) Update tables - add/rename the new v3 columns (via tab_update.sql script).
 
         1.3) Add/Edit standard validation data as needed (typically NM db table 
@@ -32 +32 @@
                 These likely include:
                         MEASURE, VALUE_TYPE, DATA_SOURCE, DIMENSION etc. 
-        1.4) Create temp 2.3 to 3 mapping tables (so Kim can populate in next section).
-        1.5) Do basic orphaned record cleanup.
+        1.4) Create temp 2.3 to 3 mapping tables (so Kim can populate in next step)
+                (tab_c-tmp.sql script).
+        1.5) Do basic orphaned record cleanup (via adopter_data-cleanup.sql script).
         [1.6] Optional - depending on if Garth/Paul are running the migration scripts
-                vs Kim doing it all.  If Kim not doing it all aip mysql db files and send 
-                to Kim or do an export.
+                vs Kim doing it all.  If Kim not doing it all then zip mysql myisam db   
+                files or do an export via mysql workbench then send to Kim.
 
         ----------- Step 2.x - Prep Data
 
         [2.0] Optional - if Kim running everything this is not needed. Otherwise
-                Kim updates her local enviro with zipped mysql files or imports .sql.
+                Kim updates her local mysql db with zipped mysql files or imports the 
+                step 1 exported .sql via the mysql workbench.
         2.1) Kim populates/udpates any adopter specific validation tables:
                         MEASURE, DIMENSION, DIMENSION_VALUE, VALUE_TYPE, DATA_SOURCE
@@ -60 +62 @@
 
         [2.6] Optional - if Kim running everything then not needed.  Else Kim zips 
-                entire mysql db and sends to Garth (or does an export).
+                entire mysql db (or does an export via mysql workbench) and sends to Garth.
 
         ----------- Step 3.x - Migrate
 
         [3.0] Optional - if Kim running everything.  Otherwise Garth/Paul imports 
-                Kim's prepped 2.3 db into development enviro.
+                Kim's prepped step 2 2.3 db.
         3.1) Cleanup (script 1).
                 - delete orphaned IP and IPV and IPVV.
@@ -117 +119 @@
                 - add in the associated ancillary values.
 
-        3.13) Cleanup.
+        3.13) Cleanup (via update_cleanup.sql script).
                 - drop old unused columns
                 - drop temp and old unused tables

trunk/db/src/main/scripts/mysql/migrate/update_cleanup.sql

@@ -35 +35 @@
 
 drop table if exists TMP_IP23NAME_IP3NAME;
+drop table if exists TMP_SPLIT_IP_IPV;
 drop table if exists TMP_IPV_MEASURE;
 drop table if exists TMP_DS23NAME_DS3NAME;
@@ -40 +41 @@
 drop table if exists TMP_DSS23_DIM;
 drop table if exists TMP_IPV_CONSTANT_DIM_NAME;
-drop table if exists TMP_SPLIT_IP_IPV;