Important

If you are using the default Tag provider, none of this is applicable. The following information is only valid for new Tag providers that you create using the External Tag Provider type.

The information provided here requires an understanding of Tags and how they work. It is an advanced reference to how the tables of external Tags providers are structured, and an overview of the concepts of Tag execution. If you are a new user, it is suggested that you read the Understanding Tags section first.

SQLT Table Descriptions

Basic Concepts and Data Flow

Tags operate through tables created in the database.

Tag Configuration Table Names and Descriptions

sqlt_core

The core Tag information table, has one entry per Tag. Defines fundamental properties like data type, as well as the current value of the Tag. Is monitored by the provider to determine value and configuration changes.

sqlt_meta Provides additional properties for Tags. Only consulted when Tag configuration has changed.
sqlt_asProvides alert state configuration for Tags which utilize alerting.
sqlt_perm Provides custom permission settings for Tags set to use them.

Operations Table Names and Descriptions

 sqlt_scContains the definitions of scan classes, which dictate how Tags are executed. 
 sqlt_sciContains an entry for each scan class from sqlt_sc, for each driver currently driving Tags. Used to verify that drivers are properly executing.
 sqlt_drvContains an entry for each Tags driver. Only really used for browsing Tags.
 sqlt_errContains errors that have occurred executing Tags.
sqlt_wq  The "write queue". All write requests are entered into this table, where the driver will detect and execute them. The result will be written back by the driver, and will be noticed by the provider.

 

Tag Execution Concepts

Polling – Many operations require polling of the database by either the driver or the provider. To ensure efficiency, all polling operations utilize indexed timestamp fields. This allows the database to do very little work when nothing has changed.

Tag Configuration – Tags are configured by inserting or modifying the appropriate entries in the configuration tables above. Configuration change is signaled to the provider by updating the configchange of sqlt_core to be the current time. Deleting a Tag works by setting its deleted column and then touching config change. This will inform all drivers and providers to remove the Tag from memory. At some point later, a daemon will delete the Tag information from the database.

Tag Execution, drivers – Each Tag has a drivername property that indicates which driver is responsible for executing it. Other drivers and providers with different names will treat the Tag as an external Tag – a Tag driven by a different entity – and will only monitor its value.

Tag Execution, scan classes – Each Tag is assigned to a scan class. The idea is that scan classes will define how often the Tag should execute, as well as provide more advanced options like leased and driven execution. In reality, the Tag driver is free to execute Tags as it desires, but it is important to understand how the scan classes and the sqlt_sci table are expected to work, as that is how the provider will verify that the Tags are being executed.

Tag Monitoring – Both providers and drivers generally monitor Tag value and configuration changes. In general, the entities will monitor Tags whose drivername isn’t equal to their own, which for providers means all Tags, since providers don’t have a driver name. Monitoring occurs by selecting the Tag values (or any information desired) from the appropriate table where one of the indexed timestamp columns is greater than the last checked time. The provider/driver will then store that time in memory as the last check, and will use it in the next poll.

 

Table Reference

The following is a reference list for the table structures of all the tables listed above. In general, all integer time values are in milliseconds.

sqlt_core

Column

Data Type

Notes

idintegerAuto-incrementing, unique id for the Tag
namestringName of Tag
pathstringFolder path, in form of path/to/
drivernamestringName of driver responsible for executing Tags
tagtypeinteger / TagType enumThe type of Tag - that is, OPC, DB, and so on
datatypeinteger / DataType enumThe type of data provided by the Tag
enabledinteger (0 or 1)Whether the Tag is enabled for execution
accessrightsinteger / AccessRightsenumAccess permissions for the Tag
scanclassintegerID of the scan class for the Tag
intvalueintegerValue column used if Tag has integer data
floatvaluedoubleValue column for float/real data
stringvaluestringValue column for string data
datevaluedatetimeValue column for date data
dataintegrityinteger / DataQualityenumCurrent quality of the value
deletedinteger (0 or 1)Whether the Tag is deleted or not
valuechangedatetimeThe last time that the value changed
configchangedatetimeThe last time that the Tag's config changed

sqlt_meta

Column

Data Type

Notes

tagidIntegerID of tag that the property belongs to
namestringThe well-known property name
intvalintegerValue, if property has integer type
floatvaldoubleValue if property has float type
stringvalstringValue, if property has string type

sqlt_as

Column

Data Type

Notes

idintegerUnique id of alert state
statenamestringName of alert state
severityinteger /Severity enum 
lowdoubleLow setpoint
highdoubleHigh setpoint
flagsinteger / Alert FlagsFlags that dictate how the state acts
lotagpathstring

Path to Tag that provides low setpoint, if low driven
flag is set

hitagpathstring

Path to Tag that Provides high setpoint, if high driven
flag is set

timedeadbanddoubleTime deadband value
timedbunitsinteger / TimeUnits enumTime deadband units

sqlt_perm

Column

Data Type

Notes

tagidintegerID of Tag that the permission belongs to
rolenamestringName of the role that this permission is applied to
accessrightsinteger / AccessRights enumAccess rights for the given role on the given Tag

sqlt_drv

Column

Data Type

Notes

namestringName of the Tag drive
ipaddrstringAddress of browser server, blank or null if browsing isn't available
portintegerPort of browse server

sqlt_sc

Column

Data Type

Notes

idintegerAuto-incrementing unique id
namestringName of the scan class
lorateinteger

The slower rate to run at, in milliseconds. Only rate
used if scan class mode is direct

hirateinteger

Higher rate, in ms. Only used if scan class is driver
or leased

drivingtagpathstringPath to Tag to watch if mode is driven
comparisoninteger / Comparison enumOperation to apply to driving Tag in driven mode
comparevaluedoubleValue to compare driving Tag to for driven mode
mode

integer / Scan class mode
enum

The mode of the scan class
staletimeoutinteger

Time, in milliseconds, before scan class is
determined to not be running

leaseexpiredatetime

The time that the lease should expire, if using
leased mode

configchangedatetimeThe last time that the scan class has been modified
deletedinteger (0 or 1)Whether the scan class has been deleted

sqlt_sci

Column

Data Type

Notes

sc_idintegerThe id of the scan class represented
drivernamestringThe driver executing this instance
lastexecdatetimeLast time that the scan class executed
lastexecrateintegerThe rate of the scan class at last execution
lastecexdurationintegerTime, in ms, that the scan class took to execute
lastexecopcwriteintegerWrites to OPC performed during last execution
lastexecopcreadsinteger

Value updates from OPC processed in last execution

lastexecdbwritesintegerWrites to DB performed during last execution
lastexecdbreadsinteger

Value updates from the database processed during the last execution

lastecexdelayinteger

The delay between when the scan class should have ran and when it actually ran for the last execution

avgexecdurationintegerThe average duration time of the scan class, in ms
execcountintegerThe number of times the scan class has executed
nextexecdatetimeThe next time that the scan class should execute

sqlt_wq

Column

Data Type

Notes

 id integer Auto-incrementing unique id for the write operation
tagid  integer ID of the Tag to write to
intvalue  integer Value, if Tag has integer data type
floatvaluedoubleValue, if Tag has float or real data type
stringvaluestringValue, if Tag has string data type
datevaluedatetimeValue, if Tag has date data type
responsecodeinteger / Write Responseenum

The state of the write request. When created, the
response code should be set to 2 - Pending

responsemsgstringWrite error if operation failed
t_stampdatetimeThe time that the write request was created
objectidintegerID of the object with the error
objectypeinteger / Object Type enum

The type of object. Used with objectid to identify the
item that caused the message

lifecycleidinteger/ Lifecycle enumWhen the message was generated
msgtypeinteger / Message Type enum 
errormsgstringThe primary message
stackstringAdditional error information
t_stampdatetimeWhen the message was generated