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 storing 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}, where as 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	The value under this column is a flag that represents metadata about the record. This column is only present on pre-processed partitions. The following vtype values are expected. 0: Time-weighted moving average 1: Minimum value within the window 2: Maximum value within the window 4: Exit value, or last value, in the window 32: Used with either the maximum or minimum, to signify which of the two came first. 64: Entry value in the window, as in the actual first value in the window. Note that values within a given row can be combined. For example, a vtype of 97 (64 + 32 + 1) means the record represents the entry value (64), the first value (32), and the minimum value (1).

sqlth_1_data

This is the data table for historian providers that have disabled multiple partitions (by unchecking the "Enable Partitions" setting). The structure of this table matches the sqlt_data_x_x_x table mentioned above.

sqlth_drv

Column Name	Data Type	Notes
id	int	Unique id of the driver.
name	string	Name of the driver. This is typically the system name of the Ignition Gateway storing records. In cases where multiple Ignition systems are storing data into the same database, this column tracks which Gateway the record came from.
provider	string	Name of the tag value's tag provider. In cases where a single Ignition Gateway contains multiple tag providers, this helps track which provider the value came from.

sqlth_partitions

This table defines the "partitions" (tables) that are used to store data, and what time frames they cover. Partitioning in the history system splits data across multiple tables in a way that is compatible with all database systems, making certain maintenance tasks easier. The query system does not expect any particular partition configuration, it simply consults this table for table-to-time associations, and then queries the resulting tables for 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. Partitions are created per driver to keep data separate.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 end time covered by this partition.
blocksize	int	The size, in milliseconds, of time covered by each entry. This is used by pre-processed partitions and would be 0 for normal data partitions.
flags	int	Additional flags that affect how the partition is used. 1 = No seed query support. The system will not execute "bounding value" (or "seed") queries against the table. Useful for database engines that do not support indexing (such as MySQL Archive engine), as these operations can become very time intensive.

sqlth_sce

Column Name	Data Type	Notes
scid	int	Id of the tag group execution entry. 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 rate (in milliseconds) of execution. There are no entries for the 'Execute on Value Change' option.

sqlth_scinfo

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 in cases where the execution rate is not recorded, such as tags set to an "On Change" sample mode.
drvid	int	The driver this tag group uses. References the sqlth_drv table.

sqlth_te

Column Name	Data Type	Notes
id	int	Unique id of the Tag.
tagpath	string	Path of the Tag in the Tag Provider, i.e., Folder1/tag1.
scid	int	The tag group this Tag is storing values with. References the sqlth_scinfo table.
datatype	int	The type of value for this Tag. 0: int, byte, short, boolean 1: float, double, long 2: string 3: date
querymode	int	Which internal mode to use for returning data. The value here is based on the deadband style used to store the value. Typically this column will only contain a 0 or 3: 0: Values are stored using a Discrete deadband style. 3: Values are stored using an Analog deadband style.
created	long	Unix Timestamp (milliseconds since epoch) for when the Tag's entry was created.
retired	long	Unix Timestamp (milliseconds since epoch) for when the Tag was retired. Tags with Tag History enabled can be retired for any of the following reasons: The tag was deleted The tag group on the tag was changed, which results in a new row being created with the updated scid The tag was renamed The tag's data type was changed This value is NULL while the Tag is active. If the tag is disabled, and then deleted, this value will also remain NULL.

sqlth_annotations

This table stores annotations created by the user. Introduced in 8.1.0.

Column Name	Data Type	Notes
id	int	Unique id of the annotation.
tagid	int	The Tag id that the annotation pertains to. References the id values on the sqlth_te table.
start_time	long	The starting point for the annotation.
end_time	long	The ending point for the annotation.
type	string	Represents the type of annotation. Currently the only defined type is "note", which represents a string that corresponds to a particular point of data.
datavalue	string	The value associated with the annotation. When "type" is set to "note", this column represents the content of the user created note.
annotationid	string	New in 8.1.4 A UUID used to track annotations. This identifier is used to track similar annotations across multiple Gateways, such as when storing annotations in a remote provider.

Tag History - Internal History Provider

Internal History Providers use a different table scheme that the other providers. These tables can be found in an IDB file located at IgnitionInstallationDirectory/data/local/tag-historian.

Note that these tables exclusively live in a SQLite database, so a viewer of some sort will be required to access them.

Table Name	Table Description
annotations	This table keeps track of any annotations for a specific tag.
schema_info	Provides information on when the internal provider was created. In most cases there should only ever be a single row in this table.
tagdata	This is where the actual historical records are stored. In addition a syncid is provided that can be used to synchronize data Gateways.
tagdetails	This table provides information about the tags storing to the provider. Note that unlike other history providers, this table does not track scan class/tag group information.
tagproperties	Keeps track of tag’s properties like datatype and interpolation mode.
tags	This table associates a tag’s id with a tag path for querying purposes.

annotations

Column Name	Data Type	Notes
id	Integer	A unique identifier for the table.
tagid	Integer	The tag id this annotation is associated with. Maps to the id column on the tags table.
type	Text	The type of annotation. See the type column on the sqlth_annotations
rangestart	Integer	A timestamp representing the start of the annotation.
rangeend	Integer	A timestamp representing the end of the annotation.
data	Text	Data associated with the annotation, such as text to display.
syncid	Integer	This value can be used to synchronize annotations with other Gateways as needed.
annotationid	Integer	An integer representing a unique identifier for an annotation.
deleted	Boolean	A boolean indicating if the annotation was marked as deleted or not.

schema_info

Column Name	Data Type	Notes
version	Integer	Of the single row on this table, the version is typically set to "4".
created	Integer	A timestamp representing when the schema was created.

tagdata

Column Name	Data Type	Notes
tagid	Integer	The tag id each record is associated with. Maps to the id column on the tags table.
numvalue	Numeric	If the tag value was numerical then this column will hold a stored value for the tag. Otherwise this column will show a NULL value.
strvalue	Text	If the tag value was non-numerical then this column will hold a stored value for the tag. Otherwise this column will show a NULL value.
quality	Integer	Stores a quality code for the stored value.
t_stamp	Integer	A timestamp representing when the stored value was seen by the system.
syncid	Integer	This syncid is used to synchronize records across multiple Gateways.

tagdetails

Column Name	Data Type	Notes
id	Integer	A unique identifier for the table.
tagid	Integer	The tag id each detail is associated with. Maps to the id column on the tags table.
created	Integer	A timestamp representing when this details row was created.
retired	Integer	A timestamp representing when this details row was retired.
datatype	Integer	The datatype of the detail, stored as an integer. See the sqlth_te table's datatype column.
ttl	Integer	"Time To Live". In most cases this column isn't utilized as the maximum time is a higher level historian property, not a setting per tag.
syncid	Integer	This syncid is used to synchronize records across multiple Gateways.

tagproperties

Column Name	Data Type	Notes
tagid	Integer	The tag id each property is associated with. Maps to the id column on the tags table.
name	Text	The name of the property.
value	Text	The stored value of the property.
datatype	Integer	The datatype of the property, stored as an integer. See the sqlth_te table's datatype column.

tags

Column Name	Data Type	Notes
id	Integer	A unique identifier for the table.
tagpath	Text	The full tag path of the tag, including tag provider.

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

See the Journal Properties and Tables page for more information regarding all of the columns in the tables.

note

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

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

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.