Changeset 25036 in main


Ignore:
Timestamp:
04/13/22 12:22:49 (4 months ago)
Author:
GarthBraithwaite_STG
Message:

db - updated untested split script.

File:
1 edited

Legend:

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

    r25034 r25036  
    3737where i.NAME = t.SOURCE_IP_NAME
    3838  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
     39  and t.NEW_IP_NAME
     40  not in (select NAME from INDICATOR)
    3941;
    4042
     
    4446where iti.NAME = t.SOURCE_IP_NAME
    4547  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
     48  and not exists
     49        (
     50                select 1 from INDICATOR_TO_INDICATOR iti2
     51                where iti2.INDICATOR_NAME         = t.NEW_IP_NAME
     52                  and iti2.RELATED_INDICATOR_NAME = iti.RELATED_INDICATOR_NAME
     53                  and iti2.RELATION_NAME          = iti.RELATION_NAME
     54        )
    4655;
    4756
     
    5160where itr.INDICATOR_NAME = t.SOURCE_IP_NAME
    5261  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
     62  and not exists
     63        (
     64                select 1 from INDICATOR_TO_RELATION itr2
     65                where itr2.INDICATOR_NAME         = t.NEW_IP_NAME
     66                  and itr2.RELATION_NAME          = iti.RELATION_NAME
     67        )
    5368;
    5469
     
    6580where itis.INDICATOR_NAME = t.SOURCE_IP_NAME
    6681  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
     82  and not exists
     83        (
     84                select 1 from INDICATOR_TO_INDICATOR_SET itis2
     85                where itis2.INDICATOR_NAME     = t.NEW_IP_NAME
     86                  and itis2.INDICATOR_SET_NAME = itis.INDICATOR_SET_NAME
     87        )
    6788;
    6889
     
    7293where uti.INDICATOR_NAME = t.SOURCE_IP_NAME
    7394  and t.SOURCE_IP_NAME != t.NEW_IP_NAME
     95  and not exists
     96        (
     97                select 1 from USER_TO_INDICATOR uti2
     98                where uti2.INDICATOR_NAME = t.NEW_IP_NAME
     99                  and uti2.USER_ID        = uti.USER_ID
     100        )
    74101;
    75102
     
    97124from INDICATOR_VIEW iv, TMP_SPLIT_IP_IPV t
    98125where iv.NAME = t.SOURCE_IPV_NAME
     126  and t.NEW_IPV_NAME not in (select NAME from INDICATOR_VIEW)
    99127;
     128
     129
     130insert into DATA_SOURCE_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DATA_SOURCE_NAME, SORT_ORDER)
     131select t.NEW_IPV_NAME, DATA_SOURCE_NAME, SORT_ORDER
     132from DATA_SOURCE_TO_INDICATOR_VIEW dstiv, TMP_SPLIT_IP_IPV t
     133where dstiv.INDICATOR_VIEW_NAME = t.SOURCE_IPV_NAME
     134  and not exists
     135        (
     136                select 1 from DATA_SOURCE_TO_INDICATOR_VIEW dstiv2
     137                where dstiv2.INDICATOR_VIEW_NAME = t.NEW_IPV_NAME
     138                  and dstiv2.DATA_SOURCE_NAME    = dstiv.DATA_SOURCE_NAME
     139        )
     140;
     141
    100142
    101143insert into INDICATOR_VIEW_VALUE
     
    122164from INDICATOR_VIEW_VALUE ivv, TMP_SPLIT_IP_IPV t
    123165where ivv.INDICATOR_VIEW_NAME = t.SOURCE_IPV_NAME
     166  and not exists
     167        (
     168                select 1 from INDICATOR_VIEW_VALUE ivv2
     169                where ivv2.INDICATOR_VIEW_NAME    = t.NEW_IPV_NAME
     170                  and ivv2.DATASET_CATEGORY_NAME  = ivv.DATASET_CATEGORY_NAME
     171                  and ivv2.DATASET_CATEGORY_VALUE = ivv.DATASET_CATEGORY_VALUE
     172                  and ivv2.DATASET_SERIES_NAME    = ivv.DATASET_SERIES_NAME
     173                  and ivv2.DATASET_SERIES_VALUE   = ivv.DATASET_SERIES_VALUE
     174                  and ivv2.VALUE                  = ivv.VALUE
     175        )
    124176;
    125177 
    126 insert into DATA_SOURCE_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DATA_SOURCE_NAME, SORT_ORDER)
    127 select t.NEW_IPV_NAME, DATA_SOURCE_NAME, SORT_ORDER
    128 from DATA_SOURCE_TO_INDICATOR_VIEW dstiv, TMP_SPLIT_IP_IPV t
    129 where dstiv.INDICATOR_VIEW_NAME = t.SOURCE_IPV_NAME
    130 ;
    131178
    132179
Note: See TracChangeset for help on using the changeset viewer.