User Manual

GETTING STARTED


OTHER EDITIONS


LAUNCHERS


MODULES


PLATFORM


APPENDIX


TUTORIALS & HELPFUL TRICKS


STRATEGIC PARTNER LINKS

Sepasoft - MES Modules
Cirrus Link - MQTT Modules

RESOURCES

Inductive University
Ignition Demo Project
Knowledge Base Articles
Forum
IA Support

ALL USER MANUAL VERSIONS

Ignition 8.1. Offline Version (04/2021)
Ignition 8 Online Version
Ignition 7.9 Online Version
Ignition 7.8 Online Version

Deprecated Pages

SDK Documentation

Employees

Sign In

Skip to end of metadata
Go to start of metadata


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 thus not recommended.

While it can be useful to manually query out data from these tables, we advise caution when attempting to alter the data or tables. We recommend taking a backup of the database tables before making manual changes to them, with the understanding that altering the data or tables is done at your own risk.

Tag History

The Tag History system utilizes up to 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

On this page ...

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 parition 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. 
vtypeint

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

ValueDescription
0Direct average value over the window
1Minimum value within the window
2Maximum value within the window
4Exit value, or last value, in the window
32Used with either the maximum or minimum, to signify which of the two came first. 
64Entry 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.
providerstring

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

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

end_timelong

Unix 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 TypesNotes
scidintId of the tag group execution entry.
References sqlth_scinfo
start_timelong

Unix Timestamp (milliseconds since epoch) for the first execution of this tag group.

end_timelong

Unix Timestamp (milliseconds since epoch) for the latest execution of this tag group.

rateint

The 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. 
drvidint

The driver this tag group uses.
References sqlth_drv.


sqlth_te

Column NameData TypeNotes
idintUnique id of the Tag.
tagpathstringPath of the Tag in the Tag Provider, i.e., Folder1/tag1.
scidint

The tag group 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

querymodeint

Which 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.
retiredlong

Unix 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 

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_timelong

The starting point for the annotation.

end_timelong

The ending point for the annotation.

typestring

Represents the type of annotation. 

Currently the only defined type is "note", which represents a string that corresponds to a particular point of data. 

datavaluestring

The  value associated with the annotation.

When "type" is set to "note", this column represents the content of the user created note. 

annotationidstring

The following feature is new in Ignition version 8.1.4
Click here to check out the other new features
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. 
tags

This table associates a tag’s id with a tag path for querying purposes. 


annotations

Column NameDatatypeNotes
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 NameDatatypeNotes
versionIntegerOf the single row on this table, the version is typically set to "4". 
createdIntegerA timestamp representing when the schema was created. 

tagdata

Column NameDatatypeNotes
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 NameDatatypeNotes
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 NameDatatypeNotes
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 NameDatatypeNotes
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 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 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 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

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

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 NameDatatypeNotes
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_contextInteger

A numerical description of the origin of the originating system.

ScopeValue
Gateway1
Designer2
Client4

More information can be found in the javadocs

originating_systemStringThe name of the project or system where this action occurred.
status_codeInteger

A 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



  • No labels