Skip to content
Teleport 

USE master;


IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'teleport')
  CREATE DATABASE teleport;


USE teleport;




IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='teleport_battery_power' and xtype='U')
  CREATE TABLE "teleport_battery_power"
(
  "id" int not null identity(1,1),
  "teleport_hash_id" varchar(32) NOT NULL,
  "asset_identifier" varchar(128) NOT NULL,
  "attempt" int NOT NULL,
  "battery_status" varchar(32) DEFAULT NULL, -- can be "on", "off", "other" or null (see warning and errors in case of 'other')
  "energy_charged" decimal(23,3) DEFAULT NULL, -- in Wh, nonnegative
  "energy_discharged" decimal(23,3) DEFAULT NULL, -- in Wh, nonnegative
  "frequency" decimal(7,2) DEFAULT NULL, -- in Hz, nonnegative
  "active_power" decimal(23,3) DEFAULT NULL, -- in W, positive means discharging, negative charging
  "reactive_power" decimal(23,3) DEFAULT NULL, -- in var, positive is injecting reactive power to the grid and negative is absorbing reactive power
  "power_factor" decimal(5, 4) DEFAULT NULL, -- unitless. For capacitive loads (leading power factor, quadrants 2 and 4), the PF sign is positive. For inductive loads (lagging power factor, quadrants 1 and 3), the PF sign is negative.
  "state_of_charge" decimal(7,4) DEFAULT NULL, -- in %, nonnegative
  "state_of_health" decimal(7,4) DEFAULT NULL, -- in %, nonnegative
  "available_energy" decimal(23,3) DEFAULT NULL, -- in Wh, nonnegative
  "rated_energy" decimal(23,3) DEFAULT NULL, -- in Wh, nonnegative
  "available_active_power_charge" decimal(23,3) DEFAULT NULL, -- in W, nonnegative
  "available_active_power_discharge" decimal(23,3) DEFAULT NULL, -- in W, nonnegative
  "available_reactive_power_inject" decimal(23,3) DEFAULT NULL, -- in var, nonnegative
  "available_reactive_power_absorb" decimal(23,3) DEFAULT NULL, -- in var, nonnegative
  "active_power_setpoint_active_power" decimal(23,3) DEFAULT NULL, -- in W, positive means discharging, negative charging
  "active_power_setpoint_deliver_fcr" decimal(23,3) DEFAULT NULL, -- in W, positive means discharging, negative charging
  "active_power_setpoint_charge_to_percentage" decimal(23,3) DEFAULT NULL, -- in W, positive means discharging, negative charging
  "active_power_setpoint_aggregate" decimal(23,3) DEFAULT NULL, -- in W, positive means discharging, negative charging
  "three_phase_connection_type_high_voltage" varchar(32) DEFAULT NULL, -- can be "wye" or "delta"
  "ac_voltage_medium_voltage_phase_l1" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_medium_voltage_phase_l2" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_medium_voltage_phase_l3" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_medium_voltage_line_l1" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_medium_voltage_line_l2" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_medium_voltage_line_l3" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_current_medium_voltage_phase_l1" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_medium_voltage_phase_l2" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_medium_voltage_phase_l3" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_medium_voltage_line_l1" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_medium_voltage_line_l2" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_medium_voltage_line_l3" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "auxiliary_power_active" decimal(23,3) DEFAULT NULL, -- in W, can be null. Negative means consuming, positive generating, will be negative or 0
  "auxiliary_power_reactive" decimal(23,3) DEFAULT NULL, -- in Var, can be null. Negative means consuming, positive generating, will be negative or 0
  "recent_setpoint_statistics_min" decimal(23,3) DEFAULT NULL, -- in W, can be null
  "recent_setpoint_statistics_max" decimal(23,3) DEFAULT NULL, -- in W, can be null
  "recent_setpoint_statistics_active_power_min" decimal(23,3) DEFAULT NULL, -- in W, can be null
  "recent_setpoint_statistics_active_power_max" decimal(23,3) DEFAULT NULL, -- in W, can be null
  "recent_setpoint_statistics_reactive_power_min" decimal(23,3) DEFAULT NULL, -- in W, can be null
  "recent_setpoint_statistics_reactive_power_max" decimal(23,3) DEFAULT NULL, -- in W, can be null
  "setpoints_confirmed_active_power" decimal(23,3) DEFAULT NULL, -- in W, positive means discharging, negative charging.
  "setpoints_confirmed_reactive_power_reactive_power" decimal(23,3) DEFAULT NULL, -- setpoint for "fixedReactivePower" mode, in var.
  "setpoints_confirmed_reactive_power_power_factor" decimal(5,4) DEFAULT NULL, -- setpoint for "fixedPowerFactor" mode, unitless.
  "setpoints_confirmed_reactive_power_mode" varchar(32) DEFAULT NULL, -- reactive power control mode, either "fixedReactivePower" or "fixedPowerFactor".
  "setpoints_confirmed_deliver_fcr" decimal(23,3) DEFAULT NULL, -- in W, nonnegative
  "setpoints_confirmed_charge_to_percentage" decimal(7,4) DEFAULT NULL, -- in %, nonnegative
  "setpoints_desired_active_power_cloud" decimal(23,3) DEFAULT NULL, -- in W
  "setpoints_desired_active_power_active_control" decimal(23,3) DEFAULT NULL, -- in W
  "setpoints_desired_active_power_grid_operator" decimal(23,3) DEFAULT NULL, -- in W
  "setpoints_desired_active_power_modbus_server" decimal(23,3) DEFAULT NULL, -- in W
  "setpoints_desired_reactive_power_cloud_reactive_power" decimal(23,3) DEFAULT NULL, -- setpoint for "fixedReactivePower" mode, in var.
  "setpoints_desired_reactive_power_cloud_power_factor" decimal(5,4) DEFAULT NULL, -- setpoint for "fixedPowerFactor" mode, unitless.
  "setpoints_desired_reactive_power_cloud_mode" varchar(32) DEFAULT NULL, -- reactive power control mode, either "fixedReactivePower" or "fixedPowerFactor".
  "setpoints_desired_reactive_power_grid_operator_reactive_power" decimal(23,3) DEFAULT NULL, -- setpoint for "fixedReactivePower" mode, in var.
  "setpoints_desired_reactive_power_grid_operator_power_factor" decimal(5,4) DEFAULT NULL, -- setpoint for "fixedPowerFactor" mode, unitless.
  "setpoints_desired_reactive_power_grid_operator_mode" varchar(32) DEFAULT NULL, -- reactive power control mode, either "fixedReactivePower" or "fixedPowerFactor".
  "setpoints_desired_charge_to_percentage_cloud" decimal(7,4) DEFAULT NULL,
  "setpoints_desired_deliver_fcr_cloud" decimal(23,3) DEFAULT NULL, -- in W
  "setpoints_desired_deliver_fcr_modbus_server" decimal(23,3) DEFAULT NULL,   -- in W, can be null. Positive means discharging, negative charging.
  "setpoints_effective_active_power" decimal(23,3) DEFAULT NULL, -- in W
  "setpoints_effective_reactive_power_reactive_power" decimal(23,3) DEFAULT NULL, -- setpoint for "fixedReactivePower" mode, in var.
  "setpoints_effective_reactive_power_power_factor" decimal(5,4) DEFAULT NULL, -- setpoint for "fixedPowerFactor" mode, unitless.
  "setpoints_effective_reactive_power_mode" varchar(32) DEFAULT NULL, -- reactive power control mode, either "fixedReactivePower" or "fixedPowerFactor".
  "setpoints_effective_deliver_fcr" decimal(23,3) DEFAULT NULL, -- in W, can be null. Defines the max rate of the droop curve.
  "warnings" nvarchar(4000) DEFAULT NULL, -- if not null, will contain a JSON aray of string elements. see error codes section
  "errors" nvarchar(4000) DEFAULT NULL, -- if not null, will contain a JSON array of string elements. see error codes section
  "cellular_signal_quality" decimal(7,2) DEFAULT NULL, -- RSSI, in dBm, null means there is no up-to-date signal quality data, optional
  "schedule_complete_until" datetime DEFAULT NULL, -- first gap in the schedule for this asset_identifier
  "scheduled" bit NOT NULL, -- false for measurements that are not part of the regular reporting interval, eg. directly after command execution and at Teleport initialisation
  "measured_at" datetime NOT NULL, -- when read-out started
  "created_at" datetime NOT NULL, -- when written to the table
  PRIMARY KEY ("id"),
  INDEX "teleport" ("teleport_hash_id","created_at"),
  INDEX "teleport_asset" UNIQUE ("teleport_hash_id", "asset_identifier", "measured_at") WITH (IGNORE_DUP_KEY = ON)
);


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


IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='teleport_battery_power_bess' and xtype='U')
  CREATE TABLE "teleport_battery_power_bess"
(
  "id" int not null identity(1,1),
  "teleport_hash_id" varchar(32) NOT NULL,
  "asset_identifier" varchar(128) NOT NULL,
  "bess_identifier" varchar(128) NOT NULL,
  "attempt" int NOT NULL,
  "cell_temperature_min" decimal(6,2) DEFAULT NULL, -- in ºC, min value of all cells
  "cell_temperature_max" decimal(6,2) DEFAULT NULL, -- in ºC, max value of all cells
  "room_temperature" decimal(6,2) DEFAULT NULL, -- in ºC, max value of all cells
  "state_of_charge" decimal(7,4) DEFAULT NULL, -- in %, nonnegative
  "available_energy" decimal(23,3) DEFAULT NULL, -- in Wh, nonnegative
  "available_active_power_charge" decimal(23,3) DEFAULT NULL, -- in W, nonnegative
  "available_active_power_discharge" decimal(23,3) DEFAULT NULL, -- in W, nonnegative
  "three_phase_connection_type_low_voltage" varchar(32) DEFAULT NULL, -- can be "wye" or "delta"
  "ac_voltage_low_voltage_phase_l1" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_low_voltage_phase_l2" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_low_voltage_phase_l3" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_low_voltage_line_l1" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_low_voltage_line_l2" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_voltage_low_voltage_line_l3" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "ac_current_low_voltage_phase_l1" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_low_voltage_phase_l2" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_low_voltage_phase_l3" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_low_voltage_line_l1" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_low_voltage_line_l2" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "ac_current_low_voltage_line_l3" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "warnings" nvarchar(4000) DEFAULT NULL, -- if not null, will contain a JSON aray of string elements. see error codes section
  "errors" nvarchar(4000) DEFAULT NULL, -- if not null, will contain a JSON array of string elements. see error codes section
  "scheduled" bit NOT NULL, -- false for measurements that are not part of the regular reporting interval, eg. directly after command execution and at Teleport initialisation
  "measured_at" datetime NOT NULL, -- when read-out started
  "created_at" datetime NOT NULL, -- when written to the table
  PRIMARY KEY ("id"),
  INDEX "bess" ("bess_identifier","created_at"),
  INDEX "teleport" ("teleport_hash_id","asset_identifier","created_at"),
  INDEX "teleport_asset" UNIQUE ("teleport_hash_id", "asset_identifier", "bess_identifier", "measured_at") WITH (IGNORE_DUP_KEY = ON)
);


IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='teleport_battery_power_bess_rack' and xtype='U')
  CREATE TABLE "teleport_battery_power_bess_rack"
(
  "id" int not null identity(1,1),
  "teleport_hash_id" varchar(32) NOT NULL,
  "asset_identifier" varchar(128) NOT NULL,
  "bess_identifier" varchar(128) NOT NULL,
  "rack_identifier" varchar(128) NOT NULL,
  "attempt" int NOT NULL,
  "rack_status" varchar(32) DEFAULT NULL, -- can be "on", "off", "other" or null (see warning and errors in case of 'other')
  "state_of_charge" decimal(7,4) DEFAULT NULL, -- in %, nonnegative, can be null
  "dc_voltage" decimal(23,3) DEFAULT NULL, -- in V, nonnegative
  "dc_current" decimal(23,3) DEFAULT NULL, -- in A, delivering to the grid is a positive value
  "cell_temperature_min" decimal(6,2) DEFAULT NULL, -- in ºC, can be null, min value of all cells in the rack
  "cell_temperature_max" decimal(6,2) DEFAULT NULL, -- in ºC, can be null, max value of all cells in the rack,
  "cell_temperature_cell_id_min" int DEFAULT NULL, -- can be null, numeric ID of the cell with the lowest temperature in the rack
  "cell_temperature_cell_id_max" int DEFAULT NULL, -- can be null, numeric ID of the cell with the highest temperature in the rack
  "cell_voltage_min" decimal(6,3) DEFAULT NULL, -- in V, can be null, min value of all cells in the rack
  "cell_voltage_max" decimal(6,3) DEFAULT NULL, -- in V, can be null, max value of all cells in the rack
  "cell_voltage_cell_id_min" int DEFAULT NULL, -- can be null, numeric ID of the cell with the lowest voltage in the rack
  "cell_voltage_cell_id_max" int DEFAULT NULL, -- can be null, numeric ID of the cell with the highest voltage in the rack
  "cell_voltage_average" decimal(6,3) DEFAULT NULL, -- in V, can be null, average value of all cells in the rack
  "scheduled" bit NOT NULL, -- false for measurements that are not part of the regular reporting interval, eg. directly after command execution and at Teleport initialisation
  "measured_at" datetime NOT NULL, -- when read-out started
  "created_at" datetime NOT NULL, -- when written to the table
  PRIMARY KEY ("id"),
  INDEX "bess_rack" ("rack_identifier","created_at"),
  INDEX "bess" ("bess_identifier","created_at"),
  INDEX "teleport" ("teleport_hash_id","asset_identifier","created_at"),
  INDEX "teleport_asset" UNIQUE ("teleport_hash_id", "asset_identifier", "bess_identifier", "rack_identifier", "measured_at") WITH (IGNORE_DUP_KEY = ON)
);