Changeset 25154 in main

Timestamp:

04/29/22 18:00:41 (3 months ago)

Author:

GarthBraithwaite_STG

Message:

db - move split to copy and simplified Kims and script. removed ibisph. prefix. ipv rename to catch ip name. Added default view template record and set IV to default.

Location:

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

Files:

• 11_data-dataset_record.sql (modified) (9 diffs)
• 12_data-dataset_record_ancillary_value.sql (modified) (1 diff)
• 2_data-ip_ipv_rename.sql (modified) (2 diffs)
• 3_data-ip_ipv_copy.sql (moved) (moved from trunk/db/src/main/scripts/mysql/migrate/3_data-ip_ipv_split.sql) (8 diffs)
• 4_data-iv_misc.sql (moved) (moved from trunk/db/src/main/scripts/mysql/migrate/4_data-iv_measure_name.sql) (1 diff)
• 5_data-data_source_to_ipv.sql (modified) (3 diffs)
• tab_c-tmp.sql (modified) (1 diff)
• update.sql (modified) (2 diffs)
• update_cleanup.sql (modified) (1 diff)

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

@@ -166 +166 @@
 /* check DSRs that don't have a dataset. */
 select * from TMP_DATASET_RECORD where DATASET_NAME is null;
-==> 400 rows
+==> 224 rows
 
 /* IVV names that don't match */
@@ -180 +180 @@
   and INDICATOR_VIEW_NAME not in (select INDICATOR_VIEW_NAME from TMP_INDICATOR_VIEW_DATASET)
 ;
-==> removed the above 400 recs
+==> removed the above 224 recs
 
 
@@ -427 +427 @@
   and ((LABEL is null) or (LABEL <= ""))
 ;
-==> 1175
+==> 1190
 
 delete from DATASET_RECORD 
@@ -524 +524 @@
 
         Can do a before update and after to see the dimension1or2 value change.
-        select * from ibisph.DIMENSION_TO_DATASET;
+        select * from DIMENSION_TO_DATASET;
         where DATASET_NAME = 'AlcoholDrinkDriveAdult.PctDrinkDrive.BRFSS'
 */
-select * from ibisph.DIMENSION_TO_DATASET dtd
+select * from DIMENSION_TO_DATASET dtd
 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)
         )
 ;
@@ -556 +556 @@
 */
 select count(*)
-from ibisph.DATASET_RECORD dr1
-where dr1.DIMENSION_2_NAME in (select DIMENSION_1_NAME from ibisph.DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
+from DATASET_RECORD dr1
+where dr1.DIMENSION_2_NAME in (select DIMENSION_1_NAME from DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
 order by dr1.DATASET_NAME, dr1.DIMENSION_1_NAME, dr1.DIMENSION_1_VALUE, dr1.DIMENSION_2_NAME, dr1.DIMENSION_2_VALUE
 ;
 
 select count(*)
-from ibisph.DATASET_RECORD dr1
-where dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from ibisph.DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
+from DATASET_RECORD dr1
+where dr1.DIMENSION_1_NAME in (select DIMENSION_2_NAME from DATASET_RECORD dr2 where dr2.DATASET_NAME = dr1.DATASET_NAME)
 order by dr1.DATASET_NAME, dr1.DIMENSION_1_NAME, dr1.DIMENSION_1_VALUE, dr1.DIMENSION_2_NAME, dr1.DIMENSION_2_VALUE
 ;
@@ -637 +637 @@
 
 
+/*
 issue appears to be dup keys for different IPVs with different measure values...
-
+*/
 select ivv.INDICATOR_VIEW_NAME, 
         ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE,
@@ -673 +674 @@
 
 
+/* test any datasets that don't have any records? */
+select * from DATASET
+where NAME not in (select DATASET_NAME from DATASET_RECORD)
+;
+
+/* test IPs that do NOT have a dataset */
+select NAME from INDICATOR
+where NAME not in (select INDICATOR_NAME from DATASET)
+;
+
+/* show DSR record count by dataset */
+select DATASET_NAME, count(DATASET_NAME) 
+from DATASET_RECORD
+group by DATASET_NAME
+;
+
+/* show IVVs count by IV */
+select INDICATOR_VIEW_NAME, count(INDICATOR_VIEW_NAME) 
+from INDICATOR_VIEW_VALUE
+group by INDICATOR_VIEW_NAME
+;
+
 
 
@@ -719 +742 @@
 limit 5000
 ;
-==> 3021 
+==> 2451 
 
 
@@ -732 +755 @@
 ) as a
 ;
-==> 
+==> 114
 
 will need a mapping file for source IP with a new name and all the assoc

trunk/db/src/main/scripts/mysql/migrate/12_data-dataset_record_ancillary_value.sql

@@ -1 @@
-use ibisph;
-
 /* This script relies on the TMP_DATASET_RECORD table created and updated via
         the data-dataset_record script.

trunk/db/src/main/scripts/mysql/migrate/2_data-ip_ipv_rename.sql

@@ -35 +35 @@
 
 
-update INDICATOR_VIEW set NAME           = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where NAME2 = NAME), NAME);
-update INDICATOR_VIEW set INDICATOR_NAME = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where NAME2 = INDICATOR_NAME), INDICATOR_NAME);
+/* set IV NAME and assoc IP NAME */
+update INDICATOR_VIEW iv 
+inner join TMP_IP23NAME_IP3NAME t on iv.NAME = t.NAME2
+set 
+        iv.NAME           = t.NAME3,
+        iv.INDICATOR_NAME = substring_index(t.NAME3, '.', 1)
+;
 
 
@@ -48 +53 @@
 update DATA_SOURCE_TO_INDICATOR_VIEW set INDICATOR_VIEW_NAME = ifnull( (select NAME3 from TMP_IP23NAME_IP3NAME where NAME2 = INDICATOR_VIEW_NAME), INDICATOR_VIEW_NAME);
 
-/* test */
+
+/* IMPORTANT TEST:  The split hasn't happened yet so every IV MUST have a DS. */
+select NAME from INDICATOR_VIEW 
+where NAME not in (select INDICATOR_VIEW_NAME from DATA_SOURCE_TO_INDICATOR_VIEW);
+
+/* unused datasources but might be needed for the split. */
 select * from DATA_SOURCE_TO_INDICATOR_VIEW 
 where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW);
 
+/* Misc queries that might be ran for basic info:
+select count(*) from DATA_SOURCE;
+select count(*) from DATA_SOURCE_TO_INDICATOR_VIEW;
+
 select * from DATA_SOURCE_TO_INDICATOR_VIEW 
 where INDICATOR_VIEW_NAME not in (select distinct(INDICATOR_VIEW_NAME) from INDICATOR_VIEW_VALUE);
-
+*/
 
 

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

@@ -1 @@
-/* Splits / creates new IPs AND IPVs based on Kim's TMP_SPLIT_IP_IPV table.
+/* Splits / creates new IPs AND IPVs based on Kim's TMP_COPY_IP_IPV table.
         NOTE: temp table needs to contain the new IP and IPV names.
 
@@ -27 +27 @@
         NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
 )
-select t.NEW_IP_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME, 
+select t.NEW_NAME, DESCRIPTION, DEFAULT_INDICATOR_VIEW_NAME, 
         TITLE, CONCISE_TITLE, 
         DEFINITION, NUMERATOR, DENOMINATOR, DATA_NOTE, DATA_ISSUES, DATA_AS_OF_DATE,
@@ -36 +36 @@
         DEMOGRAPHIC_FLAG, NOT_SELECTABLE_FLAG,  FOOTNOTE_REFERENCES,
         NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
-from INDICATOR i, TMP_SPLIT_IP_IPV t
-where i.NAME = t.SOURCE_IP_NAME
-  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
-  and t.NEW_IP_NAME
-  not in (select NAME from INDICATOR)
+from INDICATOR i, TMP_COPY_IP_IPV t
+where i.NAME = t.SOURCE_NAME
+  and t.NEW_NAME not in (select NAME from INDICATOR)
+group by t.NEW_NAME
 ;
 
 insert into INDICATOR_TO_INDICATOR (INDICATOR_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.INDICATOR_NAME = t.SOURCE_IP_NAME
-  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
+select t.NEW_NAME, RELATED_INDICATOR_NAME, RELATION_NAME
+from INDICATOR_TO_INDICATOR iti, TMP_COPY_IP_IPV t
+where iti.INDICATOR_NAME = t.SOURCE_NAME
   and not exists
         (
                 select 1 from INDICATOR_TO_INDICATOR iti2
-                where iti2.INDICATOR_NAME         = t.NEW_IP_NAME
+                where iti2.INDICATOR_NAME         = t.NEW_NAME
                   and iti2.RELATED_INDICATOR_NAME = iti.RELATED_INDICATOR_NAME
                   and iti2.RELATION_NAME          = iti.RELATION_NAME
         )
+group by t.NEW_NAME
 ;
 
 insert into INDICATOR_TO_RELATION (INDICATOR_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
-  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
+select t.NEW_NAME, RELATION_NAME, TEXT
+from INDICATOR_TO_RELATION itr, TMP_COPY_IP_IPV t
+where itr.INDICATOR_NAME = t.SOURCE_NAME
   and not exists
         (
                 select 1 from INDICATOR_TO_RELATION itr2
-                where itr2.INDICATOR_NAME         = t.NEW_IP_NAME
+                where itr2.INDICATOR_NAME         = t.NEW_NAME
                   and itr2.RELATION_NAME          = itr.RELATION_NAME
         )
+group by t.NEW_NAME
 ;
 
@@ -74 +73 @@
         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
-  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
+select t.NEW_NAME, itis.INDICATOR_SET_NAME, itis.SORT_ORDER
+from INDICATOR_TO_INDICATOR_SET itis, TMP_COPY_IP_IPV t
+where itis.INDICATOR_NAME = t.SOURCE_NAME
   and not exists
         (
                 select 1 from INDICATOR_TO_INDICATOR_SET itis2
-                where itis2.INDICATOR_NAME     = t.NEW_IP_NAME
+                where itis2.INDICATOR_NAME     = t.NEW_NAME
                   and itis2.INDICATOR_SET_NAME = itis.INDICATOR_SET_NAME
         )
+group by t.NEW_NAME
 ;
 
 insert into USER_TO_INDICATOR (INDICATOR_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
-  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
+select t.NEW_NAME, USER_ID
+from USER_TO_INDICATOR uti, TMP_COPY_IP_IPV t
+where uti.INDICATOR_NAME = t.SOURCE_NAME
   and not exists
         (
                 select 1 from USER_TO_INDICATOR uti2
-                where uti2.INDICATOR_NAME = t.NEW_IP_NAME
+                where uti2.INDICATOR_NAME = t.NEW_NAME
                   and uti2.USER_ID        = uti.USER_ID
         )
+group by t.NEW_NAME
 ;
 
@@ -119 +118 @@
         NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
 )
-select t.NEW_IPV_NAME, t.NEW_IP_NAME, 
+select t.NEW_NAME, substring_index(t.NEW_NAME, '.', 1), 
         DESCRIPTION, TITLE, SUB_TITLE, 
         PERIOD_TITLE, Y_TITLE, 
@@ -134 +133 @@
         VIEW_TEMPLATE_NAME, 
         NOTE, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE, MODIFIED_DESCRIPTION
-from INDICATOR_VIEW iv, TMP_SPLIT_IP_IPV t
-where iv.NAME = t.SOURCE_IPV_NAME
-  and t.NEW_IPV_NAME not in (select NAME from INDICATOR_VIEW)
+from INDICATOR_VIEW iv, TMP_COPY_IP_IPV t
+where iv.NAME = t.SOURCE_NAME
+  and t.NEW_NAME not in (select NAME from INDICATOR_VIEW)
 ;
 
 
 insert into DATA_SOURCE_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DATA_SOURCE_NAME, SORT_ORDER)
-select t.NEW_IPV_NAME, DATA_SOURCE_NAME, SORT_ORDER
-from DATA_SOURCE_TO_INDICATOR_VIEW dstiv, TMP_SPLIT_IP_IPV t
-where dstiv.INDICATOR_VIEW_NAME = t.SOURCE_IPV_NAME
+select t.NEW_NAME, DATA_SOURCE_NAME, SORT_ORDER
+from DATA_SOURCE_TO_INDICATOR_VIEW dstiv, TMP_COPY_IP_IPV t
+where dstiv.INDICATOR_VIEW_NAME = t.SOURCE_NAME
   and not exists 
         (
                 select 1 from DATA_SOURCE_TO_INDICATOR_VIEW dstiv2
-                where dstiv2.INDICATOR_VIEW_NAME = t.NEW_IPV_NAME
+                where dstiv2.INDICATOR_VIEW_NAME = t.NEW_NAME
                   and dstiv2.DATA_SOURCE_NAME    = dstiv.DATA_SOURCE_NAME
         )
 ;
+
+/* IMPORTANT TEST: Every IV MUST have a DS. */
+select NAME from INDICATOR_VIEW 
+where NAME not in (select INDICATOR_VIEW_NAME from DATA_SOURCE_TO_INDICATOR_VIEW);
+
+/* Unused datasources */
+select * from DATA_SOURCE_TO_INDICATOR_VIEW 
+where INDICATOR_VIEW_NAME not in (select NAME from INDICATOR_VIEW);
+
 
 
@@ -165 +173 @@
         NOTE, ACTIVE_FLAG
 )
-select t.NEW_IPV_NAME, 
+select t.NEW_NAME, 
         DATASET_SERIES_NAME,  DATASET_CATEGORY_NAME, 
         DATASET_SERIES_VALUE, DATASET_CATEGORY_VALUE, 
@@ -174 +182 @@
         NUMERATOR, DENOMINATOR, LABEL,
         NOTE, ACTIVE_FLAG
-from INDICATOR_VIEW_VALUE ivv, TMP_SPLIT_IP_IPV t
-where ivv.INDICATOR_VIEW_NAME = t.SOURCE_IPV_NAME
+from INDICATOR_VIEW_VALUE ivv, TMP_COPY_IP_IPV t
+where ivv.INDICATOR_VIEW_NAME = t.SOURCE_NAME
   and not exists 
         (
                 select 1 from INDICATOR_VIEW_VALUE ivv2
-                where ivv2.INDICATOR_VIEW_NAME    = t.NEW_IPV_NAME
+                where ivv2.INDICATOR_VIEW_NAME    = t.NEW_NAME
                   and ivv2.DATASET_CATEGORY_NAME  = ivv.DATASET_CATEGORY_NAME
                   and ivv2.DATASET_CATEGORY_VALUE = ivv.DATASET_CATEGORY_VALUE

trunk/db/src/main/scripts/mysql/migrate/4_data-iv_misc.sql

@@ -7 +7 @@
 update INDICATOR_VIEW set MEASURE_TITLE_OVERRIDE = (select MEASURE_TITLE_OVERRIDE from TMP_IPV_MEASURE where IPV_NAME3 = NAME);
 
+
+
+/* add the default view template and set all IPV's to use the default template. */
+insert into VIEW_TEMPLATE(NAME, TITLE, DESCRIPTION, VIEW_TYPE, FILEPATH_AND_NAME, SORT_ORDER, ACTIVE_FLAG)
+values ('IndicatorViewDefault', 'Default Indicator View', 'Default indicator view XSLT', 'INDICATOR_VIEW', 'html/indicator/profile/view/ViewPage.xslt', 1, 'x')
+;
+
+update INDICATOR_VIEW set VIEW_TEMPLATE_NAME = 'IndicatorViewDefault';
+
 /*------------------------------ END OF FILE ------------------------------*/

trunk/db/src/main/scripts/mysql/migrate/5_data-data_source_to_ipv.sql

@@ -29 +29 @@
 
 
+/* IMPORTANT TESTS: Make sure all IV's have valid DS */
 select * from DATA_SOURCE_TO_INDICATOR_VIEW 
 where SORT_ORDER is null 
@@ -34 +35 @@
    or DATA_SOURCE_NAME = 'NO DS3 NAME'
 ;
-delete from DATA_SOURCE_TO_INDICATOR_VIEW 
-where SORT_ORDER is null 
-   or SORT_ORDER = -9
-   or DATA_SOURCE_NAME = 'NO DS3 NAME'
-;
 
-
-/* IMPORTANT TEST: Need to see IPV's that don't have a data source */
 select * from INDICATOR_VIEW
 where NAME not in (select INDICATOR_VIEW_NAME from DATA_SOURCE_TO_INDICATOR_VIEW)
 ;
+
 
 /* TEST AND REMOVE IPV DSs that do not match / REMOVE ORPHANDED DATASOURCES. */
@@ -53 +48 @@
 delete   from DATA_SOURCE_TO_INDICATOR_VIEW where DATA_SOURCE_NAME not in (select NAME from DATA_SOURCE) or DATA_SOURCE_NAME is null;
 
-/* repeat the tests to make sure counts match */
+
+/* Sanity checks.  Extra DS to IVs no IV name match, IV's that don't have a DS */
 select count(*) from DATA_SOURCE_TO_INDICATOR_VIEW;
 select count(*) from DATA_SOURCE_TO_INDICATOR_VIEW where INDICATOR_VIEW_NAME not in (select distinct NAME from INDICATOR_VIEW) or INDICATOR_VIEW_NAME is null;

trunk/db/src/main/scripts/mysql/migrate/tab_c-tmp.sql

@@ -20 +20 @@
 
 
-/* Used to split IP and IPVs.  Since IP and IPV names are unique script uses 
-        the same table for both split opertations.
-        all NAMEs are v3 names.
+/* Used to copy/split IP and IPVs.  Since IP and IPV names are unique script  
+        uses the same table for both copy opertations.  All NAMEs are v3 names.
 */
-drop table if exists TMP_SPLIT_IP_IPV;
-create table if not exists TMP_SPLIT_IP_IPV (
-  SOURCE_IP_NAME                  varchar  (100)  not null,
-  SOURCE_IPV_NAME                 varchar  (100)  not null,
-  NEW_IP_NAME                     varchar  (100)  not null,
-  NEW_IPV_NAME                    varchar  (100)  not null,
-  constraint TMP_SPLIT_IP_IPV_PK
-    primary key (SOURCE_IPV_NAME, NEW_IPV_NAME)
+drop table if exists TMP_COPY_IP_IPV;
+create table if not exists TMP_COPY_IP_IPV (
+  SOURCE_NAME                     varchar  (100)  not null,
+  NEW_NAME                        varchar  (100)  not null,
+  constraint TMP_COPY_IP_IPV_PK
+    primary key (SOURCE_NAME, NEW_NAME)
 );
 

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

@@ -77 +77 @@
                 - Processes all assoc intersection tables etc.
 
-        3.3) Split/create new IP and IPV records (script 3).
-                - Split/create new IP/IPV based on temp table.
+        3.3) Create/copy new IP and IPV records (script 3).
+                - Create new IP/IPV record copy based on temp table.
                 - Processes all assoc intersection tables etc.
 
@@ -177 +177 @@
 /* ---------------- Step 3.x - Run Migration Scripts - Get DB from Kim */
         1_data-cleanup.sql                                      /* Rerun basic orphaned records cleanup and few misc updates. */
-        2_data-ip_ipv_rename.sql                
-        3_data-ip_ipv_split.sql         
+        2_data-ip_ipv_rename.sql                        /* rename IP and IPVs with assoc intersection records */
+        3_data-ip_ipv_copy.sql                          /* create new IP and/or IPVs with assoc intersection records */
 
-        4_data-iv_measure_name.sql                      /* Kims mapping with MEASURE_TITLE_OVERRIDE IVN, MN, CMT */
+        4_data-iv_misc.sql                                      /* Kims mapping with MEASURE_TITLE_OVERRIDE IVN, MN, CMT, and View type */
         6_data-iv_dimensions.sql                /* Kim's mapping - will send when finished DSC & S : VALUE to DIM NAME : VALUE */
         7_data-ivv_dimensions.sql               

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_COPY_IP_IPV;
 drop table if exists TMP_IPV_MEASURE;
 drop table if exists TMP_DS23NAME_DS3NAME;