Skip to end of metadata
Go to start of metadata

 

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.

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 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 epoc) for this value.


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

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 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_timelong

Unix Timestamp (milliseconds since epoc) for the earliest time covered by this partition.

end_timelong

Unix Timestamp (milliseconds since epoc) 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_timelong

Unix Timestamp (milliseconds since epoc) for the first execution of this scan class.

end_timelong

Unix Timestamp (milliseconds since epoc) for the latest execution of this scan class.

rateint

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 NameData TypeNotes
idintUnique id of the Scan class.
scnamestringName of the scan class. "_exempt_" for 'Execute on Value Change' option.
drvidint

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 NameData TypeNotes
idintUnique id of the Tag.
tagpathstringPath of the tag in the Tag Provider. ie: Folder1/tag1
scidint

The scan class this Tag is storing values with.
References sqlth_scinfo.

datatypeint

The 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 epoc) for when the Tag was created.
retiredlongUnix Timestamp (milliseconds since epoc) for when the Tag was retired (deleted/renamed).
This value is NULL while the Tag is active
  • No labels