Migration script

• MSSQL
• Meter power
• v1 to v2

IF EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('teleport_meter_power') AND [name] = N'version' AND [minor_id] = 0)
  EXEC sys.sp_updateextendedproperty
      @name=N'version',
      @value=2,
      @level0type=N'SCHEMA',
      @level0name=N'dbo',
      @level1type=N'TABLE',
      @level1name=N'teleport_meter_power'
ELSE
  EXEC sys.sp_addextendedproperty
      @name=N'version',
      @value=2,
      @level0type=N'SCHEMA',
      @level0name=N'dbo',
      @level1type=N'TABLE',
      @level1name=N'teleport_meter_power';

ALTER TABLE teleport_meter_power
ADD
  voltage_phase_l1 decimal(23, 3) DEFAULT NULL,
  voltage_phase_l2 decimal(23, 3) DEFAULT NULL,
  voltage_phase_l3 decimal(23, 3) DEFAULT NULL,
  voltage_line_l1 decimal(23, 3) DEFAULT NULL,
  voltage_line_l2 decimal(23, 3) DEFAULT NULL,
  voltage_line_l3 decimal(23, 3) DEFAULT NULL,
  current_phase_l1 decimal(23, 3) DEFAULT NULL,
  current_phase_l2 decimal(23, 3) DEFAULT NULL,
  current_phase_l3 decimal(23, 3) DEFAULT NULL,
  current_line_l1 decimal(23, 3) DEFAULT NULL,
  current_line_l2 decimal(23, 3) DEFAULT NULL,
  current_line_l3 decimal(23, 3) DEFAULT NULL,
  three_phase_connection_type varchar(32) DEFAULT NULL,
  apparent_power_l1 decimal(23, 3) DEFAULT NULL,
  apparent_power_l2 decimal(23, 3) DEFAULT NULL,
  apparent_power_l3 decimal(23, 3) DEFAULT NULL,
  apparent_power_sum decimal(23, 3) DEFAULT NULL,
  power_factor_l1 decimal(5, 4) DEFAULT NULL,
  power_factor_l2 decimal(5, 4) DEFAULT NULL,
  power_factor_l3 decimal(5, 4) DEFAULT NULL;

-- The phase voltage values are mapped to corresponding phase voltage fields:
-- `voltage_phase_l1 = phase_voltage_l1`, `voltage_phase_l2 = phase_voltage_l2`, `voltage_phase_l3 = phase_voltage_l3`.
-- The line voltage fields are set to NULL, based on the assumption that all connection types are wye-connected, where phase and line voltages are not equal.
-- Current values are mapped to both phase and line fields, as in wye-connected metering, phase and line currents are equal:
-- `current_phase_l1 = current_l1`, `current_phase_l2 = current_l2`, `current_phase_l3 = current_l3`.
-- `current_line_l1 = current_l1`, `current_line_l2 = current_l2`, `current_line_l3 = current_l3`.
UPDATE teleport_meter_power
SET
  voltage_phase_l1 = phase_voltage_l1,
  voltage_phase_l2 = phase_voltage_l2,
  voltage_phase_l3 = phase_voltage_l3,
  current_phase_l1 = current_l1,
  current_phase_l2 = current_l2,
  current_phase_l3 = current_l3,
  current_line_l1 = current_l1,
  current_line_l2 = current_l2,
  current_line_l3 = current_l3;

-- drop phase_voltage_l1 constraint
DECLARE @ConstraintName1 NVARCHAR(200)
SELECT @ConstraintName1 = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('dbo.teleport_meter_power')
  AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                          WHERE NAME = N'phase_voltage_l1'
                            AND object_id = OBJECT_ID(N'dbo.teleport_meter_power'));
IF @ConstraintName1 IS NOT NULL
    EXEC('ALTER TABLE teleport_meter_power DROP CONSTRAINT ' + @ConstraintName1);

-- drop phase_voltage_l2 constraint
DECLARE @ConstraintName2 NVARCHAR(200)
SELECT @ConstraintName2 = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('dbo.teleport_meter_power')
  AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                          WHERE NAME = N'phase_voltage_l2'
                            AND object_id = OBJECT_ID(N'dbo.teleport_meter_power'));
IF @ConstraintName2 IS NOT NULL
    EXEC('ALTER TABLE teleport_meter_power DROP CONSTRAINT ' + @ConstraintName2);

-- drop phase_voltage_l3 constraint
DECLARE @ConstraintName3 NVARCHAR(200)
SELECT @ConstraintName3 = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('dbo.teleport_meter_power')
  AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                          WHERE NAME = N'phase_voltage_l3'
                            AND object_id = OBJECT_ID(N'dbo.teleport_meter_power'));
IF @ConstraintName3 IS NOT NULL
    EXEC('ALTER TABLE teleport_meter_power DROP CONSTRAINT ' + @ConstraintName3);

-- drop current_l1 constraint
DECLARE @ConstraintName4 NVARCHAR(200)
SELECT @ConstraintName4 = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('dbo.teleport_meter_power')
  AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                          WHERE NAME = N'current_l1'
                            AND object_id = OBJECT_ID(N'dbo.teleport_meter_power'));
IF @ConstraintName4 IS NOT NULL
    EXEC('ALTER TABLE teleport_meter_power DROP CONSTRAINT ' + @ConstraintName4);

-- drop current_l2 constraint
DECLARE @ConstraintName5 NVARCHAR(200)
SELECT @ConstraintName5 = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('dbo.teleport_meter_power')
  AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                          WHERE NAME = N'current_l2'
                            AND object_id = OBJECT_ID(N'dbo.teleport_meter_power'));
IF @ConstraintName5 IS NOT NULL
    EXEC('ALTER TABLE teleport_meter_power DROP CONSTRAINT ' + @ConstraintName5);

-- drop current_l3 constraint
DECLARE @ConstraintName6 NVARCHAR(200)
SELECT @ConstraintName6 = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('dbo.teleport_meter_power')
  AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                          WHERE NAME = N'current_l3'
                            AND object_id = OBJECT_ID(N'dbo.teleport_meter_power'));
IF @ConstraintName6 IS NOT NULL
    EXEC('ALTER TABLE teleport_meter_power DROP CONSTRAINT ' + @ConstraintName6);

ALTER TABLE teleport_meter_power
DROP COLUMN
    phase_voltage_l1,
    phase_voltage_l2,
    phase_voltage_l3,
    current_l1,
    current_l2,
    current_l3;