Ignition Database Table Reference

Ignition has a lot of systems built-in that will query the database automatically without requiring you to build a query. These systems automatically create the necessary tables in the database, insert the relevant data, and even query it back out for you. However, because this data is all stored in simple database tables, it can be manually accessed using queries to customize how you see the data.

caution

These tables are configured in very specific ways. Altering them may cause unforeseen issues, and is not recommended.

While it can be useful to manually query out data from these tables, we recommend taking a backup of the database tables before making changes, with the understanding that altering the data or tables is done at your own risk.

Tag History

The Tag History system utilizes at least six different tables in the database:

Table Name	Table Description	Column References
sqlt_data_X_X	This table stores the raw tag data. There will be multiple tables that fit this format depending on the name of the Gateway and the date. (For example, a table named "sqlt_data_1_2018_01" would store data from the driver with an id of 1, for the year 2018, for the month of January)	sqlt_data_x_x_x.tagid = sqlth_te.id
sqlth_1_data	This table stores raw tag data, and is only used when the provider is configured to use a single partition (The provider's "Enable Partitioning" setting is unchecked).	sqlt_1_data.tagid = sqlth_te.id
sqlth_te	This table stores the non-data details of each tag.	sqlth_te.scid = sqlth_scinfo.id
sqlth_scinfo	This table stores tag group information.	sqlth_scinfo.drvid = sqlth_drv.id
sqlth_sce	This table stores start and end times for tag groups.	sqlth_sce.scid = sqlth_scinfo.id
sqlth_partitions	This table stores start and end times for each sqlt_data table.	sqlth_partitions.drvid = sqlth_drv.id
sqlth_drv	This table stores information about the drivers of the historical data.	none
sqlth_annotations	This table stores annotations for the Tag History system, such as those created by the Power Chart.	none

sqlt_data_X_X

This is the central table that stores the core tag values. The system stores data in tables based on the history provider's partition length and units. For example, a monthly partition would use a table named like sqlt_data_{driverId}_{year}_{month}, whereas a daily partition would use sqlt_data_{driverId}_{yearMonthDay}. The duration of each partition is also tracked on the sqlth_partitions table.

When pre-processed partitions are enabled, an additional sql_data table will be created for each partition. The system tracks which partitions are pre-processed by the "blocksize" column on the sqlth_partitions table.

Column Name	Data Type	Notes
dataintegrity	int	Quality of the tag for this timestamp. 192 is Good Quality, anything else is bad. See Tag Quality Overlays.
datevalue	date	Holds the value of the tag if it is data type 3, NULL otherwise.
floatvalue	double	Holds the value of the tag if it is data type 1, NULL otherwise.
intvalue	int	Holds the value of the tag if it is data type 0, NULL otherwise.
stringvalue	string	Holds the value of the tag if it is data type 2, NULL otherwise.
t_stamp	long	Unix Timestamp (milliseconds since epoch) for this value.
tagid	int	Unique id of the tag. References the sqlth_te table.
vtype	int	Represents metadata about the record, used for pre-processed partitions. For details, see Pre-Processed Partitions.

note

Default indexing includes:

• tagid
• t_stamp

sqlth_1_data

This is the data table for historian providers that have disabled multiple partitions (by unchecking the "Enable Partitions" setting).

Column Name	Data Type	Notes
dataintegrity	int	Quality of the tag for this timestamp. 192 is Good Quality, anything else is bad. See Tag Quality Overlays.
datevalue	date	Holds the value of the tag if it is data type 3, NULL otherwise.
floatvalue	double	Holds the value of the tag if it is data type 1, NULL otherwise.
intvalue	int	Holds the value of the tag if it is data type 0, NULL otherwise.
stringvalue	string	Holds the value of the tag if it is data type 2, NULL otherwise.
t_stamp	long	Unix Timestamp (milliseconds since epoch) for this value.
tagid	int	Unique id of the tag. References the sqlth_te table.
vtype	int	Represents metadata about the record, used for pre-processed partitions.

note

Default indexing includes:

• tagid
• t_stamp

sqlth_te

This table stores non-data details for each tag. The sqlth_te table is the primary reference for tag definitions.

Column Name	Data Type	Notes
id	int	Unique id of the tag.
tagpath	string	Path of the tag in the Tag Provider, e.g., Folder1/tag1.
scid	int	References the sqlth_scinfo table. Indicates the Tag Group for this Tag.
datatype	int	The type of value stored for the tag. For details, see Tag Data Types.

note

Default indexing includes:

• id
• scid

sqlth_scinfo

This table stores information about Tag Groups, including their execution rates and associated drivers.

Column Name	Data Type	Notes
id	int	Unique id of the Tag Group.
scname	string	Name of the Tag Group. A value of "exempt" is used when the execution rate is not recorded.
drvid	int	The driver this Tag Group uses. References the sqlth_drv table.

note

Default indexing includes:

• id
• drvid

sqlth_sce

This table stores the start and end times for Tag Group execution periods. Each entry tracks execution periods for a specific Tag Group.

Column Name	Data Type	Notes
scid	int	References the sqlth_scinfo table.
start_time	long	Unix Timestamp (milliseconds since epoch) for the first execution of this Tag Group.
end_time	long	Unix Timestamp (milliseconds since epoch) for the latest execution of this Tag Group.
rate	int	The execution rate (in milliseconds) of the Tag Group.

note

Default indexing includes:

• scid
• start_time

sqlth_partitions

This table defines the partitions used by the history system to segment data across multiple tables. Partitioning allows efficient storage and retrieval of data.

Column Name	Data Type	Notes
pname	string	The name of the table that contains this partition's data.
drvid	int	The id of the driver that owns this data table. References the sqlth_drv table.
start_time	long	Unix Timestamp (milliseconds since epoch) for the earliest time covered by this partition.
end_time	long	Unix Timestamp (milliseconds since epoch) for the latest time covered by this partition.
blocksize	int	The size (in milliseconds) of time covered by each entry, used for pre-processed partitions.
flags	int	Additional flags affecting partition usage. For example, a flag of 1 disables seed queries.

note

Default indexing includes:

• pname
• start_time

sqlth_drv

This table stores information about the drivers used in the history system. Each driver corresponds to a source of historical data.

Column Name	Data Type	Notes
id	int	Unique id of the driver.
name	string	Name of the driver. Typically, this is the system name of the Ignition Gateway storing records.
provider	string	The name of the Tag provider associated with the driver.

note

Default indexing includes:

• id

sqlth_annotations

This table stores annotations related to Tag History. These are often user-created notes associated with specific data points.

Column Name	Data Type	Notes
id	int	Unique id of the annotation.
tagid	int	The tag id the annotation pertains to. References the sqlth_te table.
start_time	long	Unix Timestamp (milliseconds since epoch) for the start time of the annotation.
end_time	long	Unix Timestamp (milliseconds since epoch) for the end time of the annotation.
type	string	The type of annotation. Currently, the only defined type is "note."
datavalue	string	The content of the annotation, when type is "note."
annotationid	string	A UUID used to track the annotation across multiple Gateways.

note

Default indexing includes:

• tagid
• start_time

Tag History - Internal History Provider

Internal History Providers use a different table scheme compared to external providers. These tables are stored in an IDB file located at IgnitionInstallationDirectory/data/local/tag-historian.

Note that these tables exclusively live in a SQLite database, so a database viewer is required to access them.

Table Name	Table Description
annotations	This table keeps track of annotations for specific tags.
schema_info	Provides information on when the internal provider was created. Usually contains only one row.
tagdata	Stores the actual historical records, along with a sync id for synchronizing data between Gateways.
tagdetails	Provides information about tags storing data. Does not track scan class/tag group information.
tagproperties	Tracks properties of tags, such as datatype and interpolation mode.
tags	Associates each tag’s id with a path for query purposes.

annotations

Column Name	Data Type	Notes
id	int	Unique id of the annotation.
tagid	int	The tag id the annotation pertains to. Maps to the id column on the tags table.
type	text	Type of the annotation.
rangestart	int	Timestamp representing the start of the annotation.
rangeend	int	Timestamp representing the end of the annotation.
data	text	Data associated with the annotation, such as user-entered content.
syncid	int	Sync id for synchronizing annotations with other Gateways.
annotationid	int	A UUID used to uniquely identify the annotation.
deleted	boolean	Indicates whether the annotation is marked as deleted.

note

Default indexing includes:

• tagid
• rangestart

schema_info

Column Name	Data Type	Notes
version	int	Version of the schema. Typically set to "4."
created	int	Unix Timestamp (milliseconds since epoch) for when the schema was created.

note

Default indexing includes:

• version

tagdata

Column Name	Data Type	Notes
tagid	int	References the tag id on the tags table.
numvalue	numeric	Numeric value of the tag, if applicable.
strvalue	text	String value of the tag, if applicable.
quality	int	Quality code for the value.
t_stamp	int	Unix Timestamp (milliseconds since epoch) for the stored value.
syncid	int	Sync id for synchronizing records across Gateways.

note

Default indexing includes:

• tagid
• t_stamp

tagdetails

Column Name	Data Type	Notes
id	int	Unique id of the detail.
tagid	int	References the tag id on the tags table.
created	int	Unix Timestamp (milliseconds since epoch) for when this detail entry was created.
retired	int	Unix Timestamp (milliseconds since epoch) for when this detail entry was retired.
datatype	int	The datatype of the detail.
ttl	int	Time-to-live.
syncid	int	Sync id for synchronizing records across Gateways.

note

Default indexing includes:

• tagid
• created

tagproperties

Column Name	Data Type	Notes
tagid	int	References the tag id on the tags table.
name	text	Name of the property.
value	text	Value of the property.
datatype	int	The datatype of the property.

note

Default indexing includes:

• tagid
• name

tags

Column Name	Data Type	Notes
id	int	Unique identifier for the tag.
tagpath	text	Full path of the tag, including the tag provider.

note

Default indexing includes:

• id
• tagpath

Alarm Journal

The Alarm Journal system utilizes two different tables in the database:

Table Name	Table Description
alarm_events	This table stores every event (active, cleared, acknowledged) that happened to any alarms that fit within the Journal filter parameters. Each row is a new event	alarm_events.id = alarm_event_data.id
alarm_events_data	This table stores unique information pertaining to each event. Each row is a specific property of a specific event, so alarm events with multiple properties will have multiple rows in the table.	none

The names of the tables are completely configurable in the Journal settings in the Gateway. The default table names are used in the table. See the Journal Properties and Tables page for more information regarding all of the columns in the tables.

note

Default indexing for the tables include:

alarm_events

• id

alarm_events_data

• id

Authentication

The Database Authentication system utilizes six different tables in the database:

Table Name	Table Description	Column References
scada_users	This table stores each user contained within the user source, along with basic user information. Each row is a new user.	none
scada_roles	This table stores all of the possible roles within the user source. Each row is a new role.	none
scada_user_rl	This table stores a mapping of users to roles. Each row is a user and a paired role, so users with multiple roles will have multiple rows in the table.	scada_users_rl.user_id = scada_users.id<br/>scada_users_rl.role_id = scada_roles.id
scada_user_sa	This table stores a list of all upcoming schedule adjustments for each user. Each row is a new schedule adjustment, so users with multiple schedule adjustments will have multiple rows in the table.	scada_user_sa.user_id = scada_users.id
scada_user_ci	This table stores a list of all contact information items for each user. Each row is a new contact information item, so users with multiple contact information items will have multiple rows in the table.	scada_user_ci.user_id = scada_users.id
scada_user_ex	This table stores a list of all extra properties for each user, with properties and values stored 1 for 1. Each row is a new property and value pair, so users with multiple extra properties will have multiple rows in the table. Extra properties are added for modules that want to associate data with a user, such as the Voice Notification Module, which adds a Security PIN setting.	scada_user_ex.user_id = scada_users.id

The prefix of the tables are configurable in the User Source settings in the Gateway. The default prefix of "scada_" is used in the table.

note

Default indexing includes:

scada_users

• id

scada_roles

• id

scada_user_rl

• user_id
• role_id

scada_user_sa

• user_id

scada_user_ci

• user_id

scada_user_ex

• user_id

Audit Log

The Audit system utilizes one table in the database:

Table Name	Table Description	Column Reference
AUDIT_EVENTS	This table stores each auditable event (save, publish, edits, etc.) that has happened for each project or system that has auditing enabled. Each row is a new event.	none

The names of the tables are completely configurable in the Audit settings in the Gateway. The default table names are used in the table.

AUDIT_EVENTS

Column Name	Data Type	Notes
audit_events_id	Integer	A unique identifier for the table.
action	String	Brief description of the action.
action_target	String	The target of the action.
action_value	String	The value acted upon the action target.
actor	String	The logged in user when the action occurred or a description of the system that generated the action.
actor_host	String	The host computer where the action occurred.
event_timestamp	Datetime	The time when the action occurred.
originating_context	Integer	A numerical description of the origin of the originating system. Gateway Scope - 1 Designer Scope - 2 Client Scope - 4 More information can be found in the javadocs.
originating_system	String	The name of the project or system where this action occurred.
status_code	Integer	A 32 bit integer that represents a bitmask of severity (good, bad, uncertain) and a sub code indicating a specific problem. The bitmask represents the status of the action recorded. For example, a tag write failing would result in a "bad" with an appropriate sub code. A list of possible sub codes can be found in the javadocs.

note

Default indexing includes:

• audit_events_id
• event_timestamp