Skip to main content
Version: 7.9

Ignition Auto-Generated Tables

Overview

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 setup in very specific ways that the system understands. Altering them in any way may make it so the system can no longer automatically interact with the tables. While it can be useful to manually query out the data, we advise caution when attempting to alter the data or tables in any way. We recommend taking a backup of the database tables before making manual changes to them, with the understand that manually altering the data or tables is done at your own risk.

Tag History

The Tag History system utilizes six different tables in the database. Each of the tables are described later on this page.

Table NameTable DescriptionColumn References
sqlt_data_x_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. (sqlt_data_1_2018_01 This table is storing data from the Gateway with an id of 1, for the year 2018, for the month of January)sqlt_data_x_x_x.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 scan class information.sqlth_scinfo.drvid = sqlth_drv.id
sqlth_sceThis table stores start and end times for scan classes.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

sqlt_data_x_x_x

This is the central table that stores the core Tag values. The system stores data in the corresponding xxx value column and leaves the others set to NULL. By default, the partition size is set to 1 month and the table name will appear as sqlt_data_1_yyyy_mm. When you first start storing historical data with this system, there will be only one table.

Column NameData TypeNotes
tagidintUnique id of the Tag. References sqlth_te.
intvalueintHolds the value of the Tag if it is datatype 0, NULL otherwise.
floatvaluedoubleHolds the value of the Tag if it is datatype 1, NULL otherwise.
stringvaluestringHolds the value of the Tag if it is datatype 2, NULL otherwise.
datevaluedateHolds the value of the Tag if it is datatype 3, NULL otherwise.
dataintegrityintQuality of the Tag for this timestamp. 192 is Good Quality, anything else is bad. See Tag Quality Overlays.
t_stamplongUnix Timestamp (milliseconds since epoch) for this value.

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

This table defines the historical providers that are storing data to the database.

Column NameData TypeNotes
idintUnique id of the driver.
namestringName of the driver. This is usually the project name.
providerstringName of the Tag provider.

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 sqlth_drv.
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

A record of executions of scan classes, defined in sqlth_scinfo. This table is primarily used to determine when the system was running correctly. The end_time of an entry is updated, as long as it falls within 2*rate of the last execution. If not, a new entry is made, and data queried will be "stale" during the gap.

Column NameData TypeNotes
scidintId of the scan class execution entry. References sqlth_scinfo
start_timelongUnix Timestamp (milliseconds since epoch) for the first execution of this scan class.
end_timelongUnix Timestamp (milliseconds since epoch) for the latest execution of this scan class.
rateintThe rate (in milliseconds) of execution. There are no entries for the 'Execute on Value Change' option.

sqlth_scinfo

This table defining scan class information for executing Historical Tags.

Column NameData TypeNotes
idintUnique id of the Scan class.
scnamestringName of the scan class. "exempt" for 'Execute on Value Change' option.
drvidintThe driver this scan class uses. References sqlth_drv.

sqlth_te

This table stores the core Tag definitions. The sqlt_data_x_x_x tables use these ids to reference values

Column NameData TypeNotes
idintUnique id of the Tag.
tagpathstringPath of the tag in the Tag Provider. ie: Folder1/tag1
scidintThe scan class this Tag is storing values with. References sqlth_scinfo.
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.
createdlongUnix Timestamp (milliseconds since epoch) for when the Tag was created.
retiredlongUnix Timestamp (milliseconds since epoch) for when the Tag was retired (deleted/renamed). This value is NULL while the Tag is active.

Alarm Journal

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

Table NameTable DescriptionColumn References
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 eventalarm_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.

info

*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_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
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 be 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
info

*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 References
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
caution

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