SQLiteValue Handler

The SQLiteValue handler (ExternalTypes.SQLiteValue) enables CDP properties to store their values in SQLite databases instead of XML configuration.

Use Cases:

• For nodes whose values should persist between application re-deployments (i.e., XML overwrite)
• For nodes whose values need to be shared between multiple applications
• For historical tracking of node value changes (using external database on-change trigger features)
• For storing sensitive data away from CDP configuration XML files
• For very large values that would clutter the XML configuration

SQLiteValue handler supports:

• Automatic database and table creation
• Configurable SQL statements for full database control (i.e., use any SQL schema needed)
• Complex key structures using multiple node values
• Write operation aggregation, i.e., when many value changes happen quickly and are in the write queue, then only the latest value change will be posted to the SQLite database
• Retry mechanisms for handling database errors

SQLiteValue handler can be configured using the following properties. The properties can be set globally under the CDP.ExternalTypesProvider configuration, or per-node using the ExternalType="SQLiteValue;{semicolon-separated list of key=value configuration parameters}" model attribute. Configuration Properties:

Attaching SQLiteValue to a Property

To attach the SQLiteValue handler to a property, set the ExternalType attribute in a model:

<Attribute Name="Value" Type="double" ExternalType="SQLiteValue"/>

Same with custom configuration (can be used to override SQLiteValue global configuration):

<Attribute Name="Value" Type="double" ExternalType="SQLiteValue;KeyNodes=.NodeID;DBName=custom.db"/>

Alternatively, in C++ code, you add the SQLiteValue handler to a node like this:

node->SetExternalType("SQLiteValue");

or

node->SetExternalType("SQLiteValue;KeyNodes=.NodeID;DBName=custom.db");

Note that the initial value from the database is loaded asynchronously, so the property value will be initially invalid at application startup until the external type handler finishes fetching the actual value. Applications should therefore be prepared to handle the situation and wait for the value to become valid (e.g., by listening to node API value change events).

In the CDP C++ node value listener API (in AbstractValueListener class), there are also convenience callbacks that get notified when:

• the database value has been loaded for a node (after node creation and configuring)
• there was an error in loading the external value for a node (or also later, e.g., on write errors)

So the simplest C++ value listener API usage looks like this:

class MyValueListener : public CDP::StudioAPI::AbstractValueListener
{
 void ExternalValueLoaded(CDP::StudioAPI::ICDPNode* node) override
 {
   // add code to handle the initial external value for the 'node' loaded event
   // (e.g., start actions that were postponed until the actual value from database is loaded)
 }
 void ExternalTypeError(CDP::StudioAPI::ICDPNode* node, const std::string& error) override
 {
   // add code to handle the external type 'error' event for the 'node'
 }
}

MyValueListener listener;
node->GetNodeAPI()->AddValueListener(&listener);

Best Practices Using SQLiteValue

When using the SQLiteValue handler, consider the following best practices:

• Set ExternalType="SQLiteValue" for values that should persist across application deployments (i.e., XML overwrite)
• Design database schemas to support your requirements
• Monitor the HandlerError alarm for external type issues
• Use appropriate WorkerCount based on your controller speed and the number of nodes that are using SQLiteValue as a backend.
• Test external type configurations with your specific data sources

Configuration Example With Value Change Count

SQLiteValue supports advanced database schema configurations. For example, you can extend the schema to track the number of changes made to each value. You can achieve this by modifying the CreateSQL and UpdateSQL properties by adding a ChangeCount column that increments with each update, like this:

Similarly, you can even add a separate table to log all historical changes if needed.

Configuration Example With Multiple KeyNodes

SQLiteValue supports using multiple values as a key to identify the value in the database. You can specify multiple relative node names in the KeyNodes property, separated by semicolons. For that, you also need to adjust the CreateSQL, SelectSQL, and UpdateSQL statements accordingly to handle multiple key columns.

This setup identifies each property value in the database using a Tag and Name pair as a key. The SQLiteValue handler uses the values from the specified KeyNodes to form the composite key when performing database operations. In other words, KeyNodes=.Tag;Name expects that the property node has to have a sibling node named "Tag" that provides the Tag part of the key, while the property name itself provides the Name part of the key.

Configuration Example With UID or SUID as a Key

SQLiteValue can also use the CDP unique identifier (UID or SUID) TypeHint feature as a key for storing values in the database. This is beneficial when you want to ensure that the property, once added, is uniquely identified in the database regardless of its position in the configuration, even after the node is renamed or moved around in the application, causing its path (i.e. long-name) to change.

To use UID or SUID as a key, set the KeyNodes property to point to the UID or SUID attribute of the node.

For example, to use UID as a key:

In the model XML, attributes should then look like this:

...
 <Attribute Name="Value" Type="double" ExternalType="SQLiteValue;KeyNodes=.UID"/>
 <Attribute Name="UID" Type="string" TypeHint="UID" ReadOnly="1" Required="1"/>
...

or even simpler, assuming the KeyNodes=" .UID" was set globally in SQLiteValue handler configuration, like that:

...
 <Attribute Name="Value" Type="double" ExternalType="SQLiteValue"/>
 <Attribute Name="UID" Type="string" TypeHint="UID" ReadOnly="1" Required="1"/>
...

When a node, having a model like this, is added to the configuration, the "UID" property value will set (at project load or application startup) to an automatically generated unique identifier, and the SQLiteValue handler will then use this UID value as a key to store and retrieve the "Value" property.