Ignition SDK Programmer's Guide
How-to Articles
Strategic Partner Links
Sepasoft - MES Modules
Cirrus Link - MQTT Modules
Resources
Inductive University
Ignition Demo Project
Knowledge Base Articles
Forum
IA Support
SDK Examples
This reference provides definitions of the different database tables used by the Tag History system.
Table Structure
- sqlt_data_x_x_x: This table stores the raw Tag data. There will be multiple tables that fit this format with date information where any "x" is.
- sqlth_drv: This table stores information about the drivers.
- sqlth_partitions: This table stores start and end times for each sqlt_data table.
- sqlth_sce: This table stores start and end times for scan classes.
- sqlth_scinfo: This table stores scan class information.
- sqlth_te: This table stores non-data details about each Tag.
- sqlth_annotations: This table stores annotations.
sqlt_data_x_x_x
This is the central table that stores the core Tag values. The system stores data in the corresponding xxxvalue 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 epoc) for this value. |
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 epoc) for the earliest time covered by this partition. |
| end_time | long | Unix Timestamp (milliseconds since epoc) 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 epoc) for the first execution of this scan class. |
| end_time | long | Unix Timestamp (milliseconds since epoc) 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. |
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. |
| datatype | int | The type of value for this Tag. |
| querymode | int | Which internal mode to use for returning data. |
| created | long | Unix Timestamp (milliseconds since epoc) for when the Tag was created. |
| retired | long | Unix Timestamp (milliseconds since epoc) for when the Tag was retired (deleted/renamed). This value is NULL while the Tag is active |
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 Tag. |
| 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. |
Overview
Content Tools
Activity