Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

 

SQLT Table Descriptions

Important

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 Tag execution. If you are a new user, it is suggested that you read the Understanding Tags section first.

Basic Concepts and Data Flow

Tags operate through tables created in the database.

Tag Configuration Table Names and Descriptions

sqlt_core

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

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

Operations Table Names and Descriptions

 sqlt_scContains the definitions of scan classes, which dictate how tags Tags are executed. 
 sqlt_sciContains an entry for each scan class from sqlt_sc, for each driver currently driving tagsTags. Used to verify that drivers are properly executing.
 sqlt_drvContains an entry for each Tags driver. Only really used for browsing tagsTags.
 sqlt_errContains errors that have occurred executing tagsTags.
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 Tag works by setting its deleted column and then touching config change. This will inform all drivers and providers to remove the tag the Tag from memory. At some point later, a daemon will delete the tag Tag information from the database.

Tag Execution, drivers – Each tag 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 Tag as an external tag  Tag – a tag Tag driven by a different entity – and will only monitor its value.

Tag Execution, scan classes – Each tag Tag is assigned to a scan class. The idea is that scan classes will define how often the tag Tag should execute, as well as provide more advanced options like leased and driven execution. In reality, the tag Tag driver is free to execute tags 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 Tags are being executed.

Tag Monitoring – Both providers and drivers generally monitor tag Tag value and configuration changes. In general, the entities will monitor tags Tags whose drivername isn’t equal to their own, which for providers means all tagsTags, since providers don’t have a driver name. Monitoring occurs by selecting the tag 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 tagTag
namestringName of tagTag
pathstringFolder path, in form of path/to/
drivernamestringName of driver responsible for executing tagsTags
tagtypeinteger / TagType enumThe type of tag Tag - that is, OPC, DB, and so on
datatypeinteger / DataType enumThe type of data provided by the tagTag
enabledinteger (0 or 1)Whether the tag Tag is enabled for execution
accessrightsinteger / AccessRightsenumAccess permissions for the tagTag
scanclassintegerID of the scan class for the tagTag
intvalueintegerValue column used if tag 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 Tag is deleted or not
valuechangedatetimeThe last time that the value changed
configchangedatetimeThe last time that the tagTag'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 Tag that provides low setpoint, if low driven
flag is set

hitagpathstring

Path to tag 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 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 tagTag

sqlt_drv

Column

Data Type

Notes

namestringName of the tag 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 Tag to watch if mode is driven
comparisoninteger / Comparison enumOperation to apply to driving tag Tag in driven mode
comparevaluedoubleValue to compare driving tag 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 Tag to write to
intvalue  integer Value, if tag Tag has integer data type
floatvaluedoubleValue, if tag Tag has float or real data type
stringvaluestringValue, if tag Tag has string data type
datevaluedatetimeValue, if tag 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

 

Next_link