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.
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 Name | Table Description | Column References |
|---|---|---|
| sqlt_data_x_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. (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_te | This table stores the non-data details of each Tag. | sqlth_te.scid = sqlth_scinfo.id |
| sqlth_scinfo | This table stores scan class information. | sqlth_scinfo.drvid = sqlth_drv.id |
| sqlth_sce | This table stores start and end times for scan classes. | 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 |
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 Name | Data Type | Notes |
|---|---|---|
| tagid | int | Unique id of the Tag. References sqlth_te. |
| intvalue | int | Holds the value of the Tag if it is datatype 0, NULL otherwise. |
| floatvalue | double | Holds the value of the Tag if it is datatype 1, NULL otherwise. |
| stringvalue | string | Holds the value of the Tag if it is datatype 2, NULL otherwise. |
| datevalue | date | Holds the value of the Tag if it is datatype 3, NULL otherwise. |
| dataintegrity | int | Quality of the Tag for this timestamp. 192 is Good Quality, anything else is bad. See Tag Quality Overlays. |
| t_stamp | long | Unix 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 Name | Data Type | Notes |
|---|---|---|
| id | int | Unique id of the driver. |
| name | string | Name of the driver. This is usually the project name. |
| provider | string | Name 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 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 sqlth_drv. |
| 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
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 Name | Data Type | Notes |
|---|---|---|
| scid | int | Id of the scan class execution entry. References sqlth_scinfo |
| start_time | long | Unix Timestamp (milliseconds since epoch) for the first execution of this scan class. |
| end_time | long | Unix Timestamp (milliseconds since epoch) for the latest execution of this scan class. |
| rate | int | The 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 Name | Data Type | Notes |
|---|---|---|
| id | int | Unique id of the Scan class. |
| scname | string | Name of the scan class. "exempt" for 'Execute on Value Change' option. |
| drvid | int | The 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 Name | Data Type | Notes |
|---|---|---|
| id | int | Unique id of the Tag. |
| tagpath | string | Path of the tag in the Tag Provider. ie: Folder1/tag1 |
| scid | int | The scan class this Tag is storing values with. References sqlth_scinfo. |
| 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. |
| created | long | Unix Timestamp (milliseconds since epoch) for when the Tag was created. |
| retired | long | Unix 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 Name | Table Description | Column 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 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.
*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 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 |
*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 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 |
*The names of the tables are completely configurable in the Audit settings in the Gateway. The default table names are used in the table.