adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V2.15__Orchestration_Status_Valid_Action_State_Transition_Table_Updates.sql

   1 USE catalogdb;
   2
   3 ALTER TABLE building_block_detail
   4 CHANGE COLUMN building_block_name BUILDING_BLOCK_NAME VARCHAR(50) NOT NULL,
   5 CHANGE COLUMN resource_type RESOURCE_TYPE VARCHAR(25) NOT NULL,
   6 CHANGE COLUMN target_action TARGET_ACTION VARCHAR(25) NOT NULL;
   7 /*
   8 SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE ', COLUMN_NAME, ' ', UPPER(COLUMN_NAME), ';')
   9 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'building_block_detail' AND COLUMN_NAME <> 'id';
  10 */
  11
  12 ALTER TABLE orchestration_status_state_transition_directive
  13 CHANGE COLUMN resource_type RESOURCE_TYPE VARCHAR(25) NOT NULL,
  14 CHANGE COLUMN orchestration_status ORCHESTRATION_STATUS VARCHAR(25) NOT NULL,
  15 CHANGE COLUMN target_action TARGET_ACTION VARCHAR(25) NOT NULL,
  16 CHANGE COLUMN flow_directive FLOW_DIRECTIVE VARCHAR(25) NOT NULL;
  17
  18 /*
  19 SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE ', COLUMN_NAME, ' ', UPPER(COLUMN_NAME), ';')
  20 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'orchestration_status_state_transition_directive' AND COLUMN_NAME <> 'id';
  21 */
  22
  23 ALTER TABLE building_block_detail
  24 DROP INDEX building_block_name;
  25
  26 ALTER TABLE building_block_detail
  27 ADD UNIQUE KEY UK_building_block_name (BUILDING_BLOCK_NAME);
  28
  29
  30 /*
  31 SELECT DISTINCT FLOW_NAME FROM orchestration_flow_reference WHERE FLOW_NAME NOT IN
  32 (SELECT building_block_name FROM building_block_detail);
  33  */
  34
  35 UPDATE orchestration_flow_reference
  36 SET FLOW_NAME = 'ActivateServiceInstanceBB'
  37 WHERE FLOW_NAME = 'ActivateServiceInstance';
  38
  39 DELETE FROM orchestration_flow_reference
  40 WHERE FLOW_NAME = 'DeactivateNetworkCollectionBB';
  41
  42 UPDATE orchestration_flow_reference
  43 SET SEQ_NO = SEQ_NO - 1
  44 WHERE COMPOSITE_ACTION = 'Service-Macro-Delete' AND SEQ_NO > 8;
  45
  46 UPDATE orchestration_flow_reference
  47 SET SEQ_NO = SEQ_NO - 1
  48 WHERE COMPOSITE_ACTION = 'NetworkCollection-Macro-Delete' AND SEQ_NO > 4;
  49
  50 UPDATE building_block_detail
  51 SET BUILDING_BLOCK_NAME = 'UnassignVolumeGroupBB'
  52 WHERE BUILDING_BLOCK_NAME = 'UnassignVolumeGroup';
  53
  54 UPDATE building_block_detail
  55 SET TARGET_ACTION = 'CHANGE_MODEL'
  56 WHERE TARGET_ACTION = 'CHANGEMODEL';
  57
  58 INSERT INTO building_block_detail(BUILDING_BLOCK_NAME, RESOURCE_TYPE, TARGET_ACTION)
  59 VALUES
  60 ('UnassignVfModuleBB', 'VF_MODULE', 'UNASSIGN'),
  61 ('AssignAndActivateVpnBondingLinksBB', 'CUSTOM', 'CUSTOM'),
  62 ('AvpnAssignServiceInstanceBB', 'CUSTOM', 'CUSTOM'),
  63 ('CreateCustomerVpnBindingBB', 'CUSTOM', 'CUSTOM'),
  64 ('SniroHoming', 'CUSTOM', 'CUSTOM'),
  65 ('DeactivateAndUnassignVpnBondingLinksBB', 'CUSTOM', 'CUSTOM'),
  66 ('DeactivateNetworkCollectionBB', 'CUSTOM', 'CUSTOM'),
  67 ('AAICheckVnfInMaintBB', 'CUSTOM', 'CUSTOM'),
  68 ('AAISetVnfInMaintBB', 'CUSTOM', 'CUSTOM'),
  69 ('AAIUnsetVnfInMaintBB', 'CUSTOM', 'CUSTOM'),
  70 ('SDNOVnfHealthCheckBB', 'CUSTOM', 'CUSTOM'),
  71 ('VNF-Macro-Replace', 'CUSTOM', 'CUSTOM'),
  72 ('HomingV2', 'CUSTOM', 'CUSTOM');
  73
  74 ALTER TABLE building_block_detail
  75 MODIFY COLUMN BUILDING_BLOCK_NAME VARCHAR(200);
  76
  77 ALTER TABLE orchestration_flow_reference
  78 ADD CONSTRAINT fk_orchestration_flow_reference__building_block_detail
  79 FOREIGN KEY (FLOW_NAME) REFERENCES building_block_detail(BUILDING_BLOCK_NAME)
  80 ON DELETE CASCADE
  81 ON UPDATE CASCADE;
  82
  83 INSERT INTO orchestration_status_state_transition_directive(RESOURCE_TYPE, ORCHESTRATION_STATUS, TARGET_ACTION, FLOW_DIRECTIVE)
  84 VALUES
  85 ('CUSTOM', 'ACTIVE', 'CUSTOM', 'CONTINUE'),
  86 ('CUSTOM', 'ASSIGNED', 'CUSTOM', 'CONTINUE'),
  87 ('CUSTOM', 'CREATED', 'CUSTOM', 'CONTINUE'),
  88 ('CUSTOM', 'INVENTORIED', 'CUSTOM', 'CONTINUE'),
  89 ('CUSTOM', 'PENDING', 'CUSTOM', 'CONTINUE'),
  90 ('CUSTOM', 'PENDING_ACTIVATION', 'CUSTOM', 'CONTINUE'),
  91 ('CUSTOM', 'PENDING_CREATE', 'CUSTOM', 'CONTINUE'),
  92 ('CUSTOM', 'PENDING_DELETE', 'CUSTOM', 'CONTINUE'),
  93 ('CUSTOM', 'PRECREATED', 'CUSTOM', 'CONTINUE');