|
Important
The information provided here requires an understanding of SQLTags and how they work. It is an advanced reference to how the tables of external SQLTags 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 SQLTags section that resides in the Project Design area of the Ignition user manual first.
Basic Concepts and Data Flow
SQLTags operate through 9 tables created in the database.
Tag Configuration Tables
| 1. | 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. |
| 2. | sqlt_meta - Provides additional properties for tags. Only consulted when tag configuration has changed. |
| 3. | sqlt_as - Provides alert state configuration for tags which utilize alerting. |
| 4. | sqlt_perm - Provides custom permission settings for tags set to use them. |
Operations Tables
| 5. | sqlt_sc - Contains the definitions of scan classes, which dictate how tags are executed. |
| 6. | sqlt_sci - Contains an entry for each scan class from sqlt_sc, for each driver currently driving tags. Used to verify that drivers are properly executing. |
| 7. | sqlt_drv - Contains an entry for each SQLTags driver. Only really used for browsing tags. |
| 8. | sqlt_err - Contains errors that have occurred executing tags. |
| 9. | 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
|
id
|
integer
|
Auto-incrementing, unique id for the tag
|
name
|
string
|
Name of tag
|
path
|
string
|
Folder path, in form of "path/to/"
|
drivername
|
string
|
Name of driver responsible for executing tags
|
tagtype
|
integer / TagType enum
|
The type of tag - ie. OPC, DB, etc.
|
datatype
|
integer / DataType enum
|
The type of data provided by the tag
|
enabled
|
integer (0 or 1)
|
Whether the tag is enabled for execution
|
accessrights
|
integer / AccessRights enum
|
Access permissions for the tag
|
scanclass
|
integer
|
ID of the scan class for the tag
|
intvalue
|
integer
|
Value column used if tag has integer data
|
float value
|
double
|
Value column for float/real data
|
stringvalue
|
string
|
Value column for string data
|
datevalue
|
datetime
|
Value column for date data
|
dataintegrity
|
integer / DataQuality enum
|
Current quality of the value
|
deleted
|
integer (0 or 1)
|
Whether the tag is deleted or not
|
valuechange
|
datetime
|
The last time that the value changed
|
configchange
|
datetime
|
The last time that the tag's config changed
|
sqlt_meta
Column
|
Type
|
Notes
|
tagid
|
integer
|
ID of tag that the property belongs to
|
name
|
string
|
The well-known property name
|
intval
|
integer
|
Value, if property has integer type
|
floatval
|
double
|
Value if property has float type
|
stringval
|
string
|
Value, if property has string type
|
sqlt_as
Column
|
Data Type
|
Notes
|
id
|
integer
|
Unique id of alert state
|
statename
|
string
|
Name of alert state
|
severity
|
integer / Severity enum
|
|
low
|
double
|
Low setpoint
|
high
|
double
|
High setpoint
|
flags
|
integer / Alert Flags
|
Flags that dictate how the state acts.
|
lotagpath
|
string
|
Path to tag that provides low setpoint, if low driven flag is set
|
hitagpath
|
string
|
Path to tag that Provides high setpoint, if high driven flag is set
|
timedeadband
|
double
|
Time deadband value
|
timedbunits
|
integer / TimeUnits enum
|
Time deadband units
|
sqlt_perm
Column
|
Type
|
Notes
|
tagid
|
integer
|
ID of tag that the permission belongs to
|
rolename
|
string
|
Name of the role that this permission is applied to
|
accessrights
|
integer / AccessRights enum
|
Access rights for the given role on the given tag
|
sqlt_drv
Column
|
Type
|
Notes
|
name
|
string
|
Name of the tag driver
|
ipaddr
|
string
|
Address of browse server, blank or null if browsing isn't available
|
port
|
integer
|
Port of browse server
|
sqlt_sc
Column
|
Data Type
|
Notes
|
id
|
integer
|
Auto-incrementing unique id
|
name
|
string
|
Name of the scan class
|
lorate
|
integer
|
The slower rate to run at, in milliseconds. Only rate used if scan class mode is direct
|
hirate
|
integer
|
Higher rate, in ms. Only used if scan class is driver or leased
|
drivingtagpath
|
string
|
Path to tag to watch if mode is driven
|
comparison
|
integer / Comparison enum
|
Operation to apply to driving tag in driven mode
|
comparevalue
|
double
|
Value to compare driving tag to for driven mode
|
mode
|
integer / Scan class mode enum
|
The mode of the scan class
|
staletimeout
|
integer
|
Time, in milliseconds, before scan class is determined to not be running
|
leaseexpire
|
datetime
|
The time that the lease should expire, if using leased mode
|
configchange
|
datetime
|
The last time that the scan class has been modified
|
deleted
|
integer (0 or 1)
|
Whether the scan class has been deleted
|
sqlt_sci
Column
|
Data Type
|
Notes
|
sc_id
|
integer
|
The id of the scan class represented
|
drivername
|
string
|
The driver executing this instance
|
lastexec
|
datetime
|
Last time that the scan class executed
|
lastexecrate
|
integer
|
The rate of the scan class at last execution
|
lastecexduration
|
integer
|
Time, in ms, that the scan class took to execute
|
lastexecopcwrites
|
integer
|
Writes to OPC performed during last execution
|
lastexecopcreads
|
integer
|
Value updates from OPC processed in last execution
|
lastexecdbwrites
|
integer
|
Writes to DB performed during last execution
|
lastexecdbreads
|
integer
|
Value updates from the database processed during the last execution
|
lastecexdelay
|
integer
|
The delay between when the scan class should have ran and when it actually ran for the last execution
|
avgexecduration
|
integer
|
The average duration time of the scan class, in ms
|
execcount
|
integer
|
The number of times the scan class has executed
|
nextexec
|
datetime
|
The 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
|
fload value
|
double
|
Value, if tag has float or real data type
|
stringvalue
|
string
|
Value, if tag has string data type
|
datevalue
|
datetime
|
Value, if tag has date data type
|
responsecode
|
integer / Write Response enum
|
The state of the write request. When created, the response code should be set to 2 - Pending
|
responsemsg
|
string
|
Write error if operation failed
|
t_stamp
|
datetime
|
The time that the write request was created
|
sqlt_err
Column
|
Data Type
|
Notes
|
objectid
|
integer
|
ID of the object with the error
|
objectype
|
integer / Object Type enum
|
The type of object. Used with objectid to identify the item that caused the message
|
lifecycleid
|
integer / Lifecycle enum
|
When the message was generated
|
msgtype
|
integer / Message Type enum
|
|
errormesg
|
string
|
The primary message
|
stack
|
string
|
Additional error ingormation
|
t_stamp
|
datetime
|
When the message was generated
|
Enum Reference
Enums are well-known values that are stored as integers in the database
Tag Type
0
|
OPC Tag
|
1
|
DB Tag
|
2
|
Client Tag
|
6
|
Folder Tag
|
Data Type
0
|
Int1
|
1
|
Int2
|
2
|
Int4
|
3
|
Int8
|
4
|
Float4
|
5
|
Float8
|
6
|
Boolean
|
7
|
String
|
8
|
DateTime
|
9
|
DataSet
|
Data Quality
0
|
Bad Data from OPC
|
4
|
CONFIG_ERROR
|
8
|
NOT_CONNECTED
|
12
|
DEVICE_FAILURE
|
16
|
SENSOR_FAILURE
|
20
|
Bad, showing last value
|
24
|
COMM_FAIL
|
28
|
OUT_OF_SERVICE
|
32
|
WAITING
|
64
|
UNCERTAIN
|
68
|
UNCERTAIN showing last value
|
80
|
SENSOR_BAD
|
84
|
LIMIT_EXCEEDED
|
88
|
SUB_NORMAL
|
28
|
SERVER_DOWN
|
192
|
Good Data
|
216
|
Good, with local override
|
256
|
OPC_UNKNOWN
|
300
|
Config Error
|
301
|
Comm Error
|
310
|
Expr Eval Error
|
330
|
Tag exec error (fsql)
|
340
|
Type conversion error
|
403
|
Access Denied
|
404
|
Not Found
|
410
|
Disabled
|
500
|
Stale
|
600
|
Unknown (loading)
|
700
|
Write Pending
|
Access Rights
0
|
Read Only
|
1
|
Read/Write
|
2
|
Custom
|
Scan Class Modes
0
|
Direct
|
1
|
Driven
|
2
|
Leased
|
Comparison Mode
0
|
Equal
|
1
|
Not Equal
|
2
|
Less Than
|
3
|
Less Than Equal
|
4
|
Greater Than
|
5
|
Greater Than Equal
|
Alert Flags
0x01
|
Low Exclusive
|
0x02
|
Low Infinite
|
0x04
|
High Exclusive
|
0x08
|
High Infinite
|
0x10
|
Any Change
|
0x20
|
Low Driven
|
0x40
|
High Driven
|
Write Response
0
|
Failure
|
1
|
Success
|
2
|
Pending
|
|