Skip to main content
Version: 8.1

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 NameTable DescriptionColumn References
sqlt_data_X_XThis 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_dataThis 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_teThis table stores the non-data details of each Tag.sqlth_te.scid = sqlth_scinfo.id
sqlth_scinfoThis table stores tag group information.sqlth_scinfo.drvid = sqlth_drv.id
sqlth_sceThis table stores start and end times for tag groups.sqlth_sce.scid = sqlth_scinfo.id
sqlth_partitionsThis table stores start and end times for each sqlt_data table.sqlth_partitions.drvid = sqlth_drv.id
sqlth_drvThis table stores information about the drivers of the historical data.none
sqlth_annotationsThis table stores annotations for the Tag history system, such as those created by the Power Chartnone

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 NameData TypeNotes
dataintegrityintQuality of the Tag for this timestamp. 192 is Good Quality, anything else is bad. See Tag Quality Overlays.
datevaluedateHolds the value of the Tag if it is data type 3, NULL otherwise.
floatvaluedoubleHolds the value of the Tag if it is data type 1, NULL otherwise.
intvalueintHolds the value of the Tag if it is data type 0, NULL otherwise.
stringvaluestringHolds the value of the Tag if it is data type 2, NULL otherwise.
t_stamplongUnix Timestamp (milliseconds since epoch) for this value.
tagidintUnique id of the Tag. References the sqlth_te table.
vtypeintThe 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 NameData TypeNotes
idintUnique id of the driver.
namestringName 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.
providerstringName 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 NameData TypeNotes
pnamestringThe name of the table that contains this partition's data.
drvidintThe id of the driver that owns this data table. Partitions are created per driver to keep data separate.References the sqlth_drv table.
start_timelongUnix Timestamp (milliseconds since epoch) for the earliest time covered by this partition.
end_timelongUnix Timestamp (milliseconds since epoch) for the end time covered by this partition.
blocksizeintThe size, in milliseconds, of time covered by each entry. This is used by pre-processed partitions and would be 0 for normal data partitions.
flagsintAdditional 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 NameData TypeNotes
scidintId of the tag group execution entry. References the sqlth_scinfo table.
start_timelongUnix Timestamp (milliseconds since epoch) for the first execution of this tag group.
end_timelongUnix Timestamp (milliseconds since epoch) for the latest execution of this tag group.
rateintThe rate (in milliseconds) of execution. There are no entries for the 'Execute on Value Change' option.

sqlth_scinfo​

Column NameData TypeNotes
idintUnique id of the tag group.
scnamestringName 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.
drvidintThe driver this tag group uses. References the sqlth_drv table.

sqlth_te​

Column NameData TypeNotes
idintUnique id of the Tag.
tagpathstringPath of the Tag in the Tag Provider, i.e., Folder1/tag1.
scidintThe tag group this Tag is storing values with. References the sqlth_scinfo table.
datatypeintThe type of value for this Tag.
  • 0: int, byte, short, boolean
  • 1: float, double, long
  • 2: string
  • 3: date
querymodeintWhich 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.
createdlongUnix Timestamp (milliseconds since epoch) for when the Tag's entry was created.
retiredlongUnix 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 NameData TypeNotes
idintUnique id of the annotation.
tagidintThe Tag id that the annotation pertains to. References the id values on the sqlth_te table.
start_timelongThe starting point for the annotation.
end_timelongThe ending point for the annotation.
typestringRepresents the type of annotation.
Currently the only defined type is "note", which represents a string that corresponds to a particular point of data.
datavaluestringThe value associated with the annotation.
When "type" is set to "note", this column represents the content of the user created note.
annotationidstring
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 NameTable Description
annotationsThis table keeps track of any annotations for a specific tag.
schema_infoProvides information on when the internal provider was created. In most cases there should only ever be a single row in this table.
tagdataThis is where the actual historical records are stored. In addition a syncid is provided that can be used to synchronize data Gateways.
tagdetailsThis 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.
tagpropertiesKeeps track of tag’s properties like datatype and interpolation mode.
tagsThis table associates a tag’s id with a tag path for querying purposes.

annotations​

Column NameData TypeNotes
idIntegerA unique identifier for the table.
tagidIntegerThe tag id this annotation is associated with. Maps to the id column on the tags table.
typeTextThe type of annotation. See the type column on the sqlth_annotations
rangestartIntegerA timestamp representing the start of the annotation.
rangeendIntegerA timestamp representing the end of the annotation.
dataTextData associated with the annotation, such as text to display.
syncidIntegerThis value can be used to synchronize annotations with other Gateways as needed.
annotationidIntegerAn integer representing a unique identifier for an annotation.
deletedBooleanA boolean indicating if the annotation was marked as deleted or not.

schema_info​

Column NameData TypeNotes
versionIntegerOf the single row on this table, the version is typically set to "4".
createdIntegerA timestamp representing when the schema was created.

tagdata​

Column NameData TypeNotes
tagidIntegerThe tag id each record is associated with. Maps to the id column on the tags table.
numvalueNumericIf the tag value was numerical then this column will hold a stored value for the tag. Otherwise this column will show a NULL value.
strvalueTextIf 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.
qualityIntegerStores a quality code for the stored value.
t_stampIntegerA timestamp representing when the stored value was seen by the system.
syncidIntegerThis syncid is used to synchronize records across multiple Gateways.

tagdetails​

Column NameData TypeNotes
idIntegerA unique identifier for the table.
tagidIntegerThe tag id each detail is associated with. Maps to the id column on the tags table.
createdIntegerA timestamp representing when this details row was created.
retiredIntegerA timestamp representing when this details row was retired.
datatypeIntegerThe datatype of the detail, stored as an integer. See the sqlth_te table's datatype column.
ttlInteger"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.
syncidIntegerThis syncid is used to synchronize records across multiple Gateways.

tagproperties​

Column NameData TypeNotes
tagidIntegerThe tag id each property is associated with. Maps to the id column on the tags table.
nameTextThe name of the property.
valueTextThe stored value of the property.
datatypeIntegerThe datatype of the property, stored as an integer. See the sqlth_te table's datatype column.

tags​

Column NameData TypeNotes
idIntegerA unique identifier for the table.
tagpathTextThe full tag path of the tag, including tag provider.

Alarm Journal​

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

Table NameTable Description
alarm_eventsThis table stores every event (active, cleared, acknowledged) that happened to any alarms that fit within the Journal filter parameters. Each row is a new eventalarm_events.id = alarm_event_data.id
alarm_events_dataThis 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 NameTable DescriptionColumn References
scada_usersThis table stores each user contained within the user source, along with basic user information. Each row is a new user.none
scada_rolesThis table stores all of the possible roles within the user source. Each row is a new role.none
scada_user_rlThis 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_saThis 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_ciThis 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_exThis 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 NameTable DescriptionColumn Reference
AUDIT_EVENTSThis 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 NameData TypeNotes
audit_events_idIntegerA unique identifier for the table.
actionStringBrief description of the action.
action_targetStringThe target of the action.
action_valueStringThe value acted upon the action target.
actorStringThe logged in user when the action occurred or a description of the system that generated the action.
actor_hostStringThe host computer where the action occurred.
event_timestampDatetimeThe time when the action occurred.
originating_contextIntegerA 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_systemStringThe name of the project or system where this action occurred.
status_codeIntegerA 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.