MS SQL schema

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