MS SQL schema

This document describes the static database objects created by schema.sql.

Figure 1. MS SQL schema
MS SQL schema
info:
All objects are created in schema dbo unless stated otherwise.

archive_groups

The archive_groups table contains information about the backend's archive groups.

Columns

Table 1.
Column Type Description
group_name nvarchar(50) archive group name (format: systemName:groupName)
retention_interval int unit of retention period (0:minutes,1:hours,2:days,3:weeks,4:months,5:years)
retention_factor int retention time in units of retention_interval
segment_duration_interval int unit of segment duration (0:minutes,1:hours,2:days,3:weeks,4:months,5:years)
segment_duration_factor int segment duration (factor in units of segment_duration_interval)
backup_interval int unit of backup interval (1:hours,2:days,3:weeks,4:months,5:years)
backup_factor int backup interval time
backup_age_interval int unit of backup age (0:minutes,1:hours,2:days,3:weeks)
backup_age_factor int age of segments that should be backed up
segment_size int segment size (unit depends on backend settings)
system_number int group system id number
datapoint int group datapoint id
dp_name_embedded nvarchar(4000) group DPE name
next_scheduled_backup bigint time of next planned backup (epoch in ns/ms depending on backend)
enabled bit is archive group enabled? (default 1)
alert bit is this alerts archive group? (default 0)
disable_backup_on_deletion bit should backup segments whose retention time was exceeded? (default 0)

Constraints / Indexes

  • PK_archive_groups: PRIMARY KEY (group_name)

configuration

The configuration table is used as a key-value store for internal parameters that are defined when the DB is created.

Columns

Table 2.
Column Type Description
name nvarchar(50) Parameter name
value nvarchar(4000) Parameter value

Constraints / Indexes

  • PK_configuration: PRIMARY KEY (name)

elements

The elements table contains information on the datapoint elements whose values must be read, written, and archived.

Columns

Table 3.
Column Type Description
element_id bigint ID of DPE
sys_id bigint System ID
type_ bigint Element type (integer, float, etc.)
event bit Is this element an event? (default 0)
alert bit Is this element an alert? (default 0)
element_name nvarchar(4000) DPE name
dpt_name nvarchar(4000) Datapoint type name
dpt_id int Datapoint type ID
unit nvarchar(4000) Value unit
alias nvarchar(4000) Alias text
comment_ nvarchar(4000) Comment

Constraints / Indexes

  • PK_elements: PRIMARY KEY (element_id)
  • FK_elements_systems: FOREIGN KEY (sys_id) REFERENCES systems(sys_id)
  • element_name_idx: NONCLUSTERED INDEX (element_name)

elements_to_archive_groups

The elements_to_archive_groups table defines many-to-many relations between "archive groups" and "elements". This table allows storing cross-references for multiple archive groups.

Columns

Column Type Description
element_id bigint Element ID
group_name nvarchar(50) Archive group name

Constraints / Indexes

  • PK_elements_to_archive_groups: PRIMARY KEY (element_id, group_name)
  • FK_to_elements: FOREIGN KEY (element_id) REFERENCES elements(element_id) ON DELETE CASCADE

scheduler_tasks

The table scheduler_tasks contains information about the last successful execution of periodic tasks and their execution period in seconds.

Columns

Column Type Description
name nvarchar(150) Task name
executionperiodinsec bigint How often the task should be executed (seconds)
lastsuccessfulexecutiontimestamp bigint Timestamp when the task was last successfully executed

Constraints / Indexes

  • PK_scheduler_tasks: PRIMARY KEY (name)

segments

The segments table contains a list of segments for archive groups.

Note:

The segment ID value is used in the name of the respective table that contains the historical data for this segment.

Columns

Column Type Description
segment_id bigint Segment ID number (IDENTITY)
group_name nvarchar(50) Archive group name
status int Segment status (0: current, 1: online, 2: onlineAndBackuped, 3: offlineAndBackuped, 4: deleted, 5: restored)
start_time bigint Segment start time
end_time bigint Segment end time

Constraints / Indexes

  • PK_segments: PRIMARY KEY (segment_id)
  • UK_group_name_start_time: UNIQUE (group_name, start_time)
  • FK_to_archive_groups: FOREIGN KEY (group_name) REFERENCES archive_groups(group_name) ON DELETE CASCADE

systems

The systems table contains information on the systems of the WinCC OA project.

Columns

Column Type Description
sys_id bigint System ID number
sys_name nvarchar(4000) System display name

Constraints / Indexes

  • PK_systems: PRIMARY KEY (sys_id)

_event_*_a

Stores simple (non-dyn) event values. These tables are created dynamically as part of the archiving/segmentation process.

Columns

Column Type Description
element_id bigint DP element ID
ts bigint Event creation timestamp
value_number float Numeric value
status bigint Status bits
corrstatus bigint Correction status bits
manager int Manager which set the value
corrmanager int Manager which set the correction value
user_ int Logged-in user when the value was set
corruser_ int Logged-in user when the correction value was set
corrvalue_number float Correction numeric value
value_timestamp bigint Timestamp-type value
corrvalue_timestamp bigint Correction timestamp-type value
value_string nvarchar(max) String value
corrvalue_string nvarchar(max) Correction string value

Constraints / Indexes

  • PRIMARY KEY (element_id, ts)

_event_*_d

Stores events for dyn values. These tables are created dynamically as part of the archiving/segmentation process.

Columns

Column Type Description
element_id bigint DP element ID
ts bigint Event creation timestamp
position int Position within the dyn value
valtype smallint Type of stored value
value_dynnumber float Dyn numeric value
value_dyntimestamp bigint Dyn timestamp value
value_dynstring nvarchar(max) Dyn string value

Constraints / Indexes

  • PRIMARY KEY (element_id, ts, position, valtype)

_alert_*_a

Stores alert values. These tables are created dynamically as part of the archiving/segmentation process.

Columns

Column Type Description
element_id bigint DP element ID
ts bigint Alert creation timestamp
ack_time bigint Acknowledgement time
system_time bigint Time when the alert was received by the system
value_timestamp bigint Value of timestamp type
partner bigint Alert time for the partner
ack_state int Identifies how the alert was acknowledged
ack_type int Type of acknowledgement
partn_idx int Alert index for the alert partner
dest int Current alert range
detail int Detail ID / number
state int Status bits
ack_user int User who acknowledged the alert
value_status bigint Status of the value that triggered the alert
value_number float Numeric value
ackable bit Is the alert acknowledgeable?
visible bit Is the alert visible?
direction bit True for CAME, false for WENT
inact_ack bit Inactive alerts must be acknowledged
abbr nvarchar(max) Short sign of priority
panel nvarchar(max) Panel for cross-selection
prior int Priority
single_ack bit Did a single acknowledgement occur?
alert_id nvarchar(max) ID for multi-instance alarm
alert_color nvarchar(max) Background color
class nvarchar(max) Alert class
comment nvarchar(max) User comment
dest_text nvarchar(max) Text for current alert range
text nvarchar(max) Status text for the range
value_string nvarchar(max) String value
alert_fore_color nvarchar(max) Foreground color
alert_font_style nvarchar(max) Font style

Constraints / Indexes

  • PRIMARY KEY (element_id, ts, detail)

_alert_*_add

Stores additional values for alerts. These tables are created dynamically as part of the archiving/segmentation process.

Columns

Column Type Description
element_id bigint DP element ID
ts bigint Alert creation timestamp
valtype bigint Type of stored value
add_value_timestamp bigint Timestamp-type additional value
detail int Detail ID / number
position int Position within the dyn value
add_value_number float Numeric additional value
add_value_string nvarchar(max) String additional value

Constraints / Indexes

  • PRIMARY KEY (element_id, ts, detail, position)

view_events_%system_number%

The view view_events_%system_number% contains the union of all event segment tables with the statuses CURRENT, ONLINE, ONLINE AND BACKUPED and RESTORED.

Note:
You can disable the view creation in the backend configuration.
Note:
The segment status enum (see segments.status) typically maps these to 0, 1, 2, 5.

Columns

Column Type Description
element_id bigint DP element ID
element_name nvarchar(max) DP element name
sys_id bigint System ID
ts bigint Event creation timestamp
position int Position within the dyn value
value_number float Numeric value
status bigint Status bits
corrstatus bigint Correction status bits
manager int Manager which set the value
corrmanager int Manager which set the correction value
user_ int Logged-in user when a value was set
corruser_ int Logged-in user when a correction value was set
corrvalue_number float Correction numeric value
value_timestamp bigint Timestamp-type value
corrvalue_timestamp bigint Correction timestamp-type value
value_string nvarchar(max) String value
corrvalue_string nvarchar(max) Correction string value
valtype smallint Type of stored value
value_dynnumber float Dyn numeric value
value_dyntimestamp bigint Dyn timestamp value
value_dynstring nvarchar(max) Dyn string value

view_alarms_%system_number%

The view view_alarms_%system_number% contains the union of all alert segment tables with the statuses ONLINE, CURRENT, ONLINE AND BACKUPED, and RESTORED.

Note:
You can disable the view creation in the backend configuration.
Note:
This placeholder view_alarms_%system_number% contains the actual system number.

Columns

Column Type Description
element_name nvarchar(max) DP element name
element_id bigint DP element ID
sys_id bigint System ID
ts bigint Alert creation time
ack_time bigint Acknowledgement time
system_time bigint Time the alert was received by the system
value_timestamp bigint Value of timestamp type
partner bigint Alert time for the partner
ack_state int Identifies how the alert was acknowledged
ack_type int Type of acknowledgement
partn_idx int Alert index for the alert partner
dest int Current alert range
detail int Detail ID / number
state int Status bits
ack_user int User who acknowledged the alert
value_status bigint Status of the value that triggered the alert
value_number float Numeric value
ackable bit Is the alert acknowledgeable?
visible bit Is the alert visible?
direction bit True for CAME, false for WENT
inact_ack bit Inactive alerts must be acknowledged
abbr nvarchar(max) Short sign of the priority
panel nvarchar(max) Panel for cross-selection
prior int Priority
single_ack bit Did a single acknowledgement occur?
alert_id nvarchar(max) ID for multi-instance alert
alert_color nvarchar(max) Background color
class nvarchar(max) Alert class
comment nvarchar(max) User comment
dest_text nvarchar(max) Text for current alert range
text nvarchar(max) Status text for the range
value_string nvarchar(max) String value
alert_fore_color nvarchar(max) Foreground color
alert_font_style nvarchar(max) Font style
valtype bigint Type of stored (dyn) value
position int Position within the dyn value
add_value_number float Numeric additional value
add_value_string nvarchar(max) String additional value
add_value_timestamp bigint Timestamp-type additional value