This document provides an overview of the database persistence plugin subsystem within ThingsGateway. Database persistence plugins are responsible for storing collected variable data, alarm events, and system events into various database systems for historical analysis and real-time monitoring.
The database persistence plugins implement the outbound data flow from the ThingsGateway runtime engine to persistent storage. These plugins extend the BusinessBaseWithCacheIntervalVariable base class (for variable data) or BusinessBaseWithCacheAlarm base class (for alarm/event data), inheriting queue-based processing, interval triggering, and C# scripting capabilities.
For information about data collection from industrial devices, see Data Collection Plugins. For publishing data to messaging systems, see Data Publishing and Distribution Plugins. For UI components related to database configuration, see Dynamic SQL Scripts and Configuration UI.
ThingsGateway provides three specialized database persistence plugins, each optimized for different use cases:
| Plugin | Primary Use Case | Key Features | Base Class |
|---|---|---|---|
| SqlDB | General-purpose relational storage | History + real-time tables, type partitioning, split tables | BusinessBaseWithCacheIntervalVariable |
| QuestDB | Time-series optimized storage | REST API support, PostgreSQL wire protocol | BusinessBaseWithCacheIntervalVariable |
| TDengine | IoT-specific time-series database | Super table design, stable names | BusinessBaseWithCacheIntervalVariable |
| SqlHistoryAlarm | Alarm and event logging | Dedicated alarm history, plugin event logging | BusinessBaseWithCacheAlarm |
All database plugins share common architectural patterns including queue-based processing, bulk operations, variable grouping, and dynamic SQL scripting.
Sources:
Sources:
Sources:
The SqlDBProducer uniquely implements two operational modes that can be enabled independently or simultaneously:
IsHistoryDB)History mode stores time-series data into time-partitioned history tables. Data is written on value change or time interval triggers.
Table Structure:
SQLNumberHistoryValue - numeric and boolean valuesSQLHistoryValue - string and complex type valuesKey Operations:
InserableAsync() [src/ThingsGateway.Plugin.DB/SqlDB/SqlDbProducer.other.cs:142-202]
├── Partition by data type (number vs string)
├── Bulk insert using Fastest<T>().SplitTable().BulkCopyAsync()
└── Optional custom script via BigTextScriptHistoryTable
IsReadDB)Real-time mode maintains a single table with the latest value for each variable. The table is initialized once with all configured variables, then updated as values change.
Table Structure:
SQLRealValue - latest value cache for all variablesKey Operations:
UpdateAsync() [src/ThingsGateway.Plugin.DB/SqlDB/SqlDbProducer.other.cs:204-263]
├── First run: Initialize missing variables using BulkCopyAsync()
├── Subsequent runs: Update existing rows using BulkUpdateAsync()
└── Optional custom script via BigTextScriptRealTable
Implementation Details:
_initRealData flag ensures one-time initializationRealTimeVariables dictionary tracks pending updatesSources:
Database plugins partition variable data by type to optimize storage and query performance:
SQL Database Implementation:
NumberTableNameStringTableNameQuestDB Implementation:
NumberTableNameStringTableNameSources:
Database plugins support grouping variables to enable atomic batch writes:
| Property | Type | Description |
|---|---|---|
GroupUpdate | bool | Enable variable grouping |
BusinessGroup | string | Group identifier (per variable) |
BusinessGroupUpdateTrigger | bool | Trigger entire group when this variable changes |
EnableAtomicBatchEnqueue | bool | Enqueue ungrouped variables as single batch |
Implementation:
TimeIntervalUpdateVariable() grouping logicUpdateVariable() group trigger logicTrigger-Based Group Updates:
When BusinessGroupUpdateTrigger is enabled on a variable, changing that variable triggers a batch write of all variables in the same group:
VariableRuntimeGroups dictionary to retrieve all group membersSources:
All database plugins support custom C# scripts to override default database operations. This enables specialized data transformations, custom table structures, or integration with external systems.
Scripts must implement the DynamicSQLBase interface:
DynamicSQLBase.DBInsertable(ISqlSugarClient db, List<T> data, CancellationToken cancellationToken)
| Plugin | History Table Script | Real-Time Table Script | Event Script |
|---|---|---|---|
| SqlDBProducer | BigTextScriptHistoryTable | BigTextScriptRealTable | N/A |
| QuestDBProducer | BigTextScriptHistoryTable | N/A | N/A |
| SqlHistoryAlarm | BigTextScriptHistoryTable | N/A | BigTextScriptPluginEventDataHistoryTable |
SqlDBProducer Script Execution:
SqlHistoryAlarm Script Requirements:
Sources:
The SqlHistoryAlarm plugin extends BusinessBaseWithCacheAlarm to persist alarm state changes and plugin system events.
| Data Type | Source | Queue Type | Script Requirement |
|---|---|---|---|
| Alarm Variables | AlarmChange() | CacheDBItem<AlarmVariable> | Optional |
| Plugin Events | PluginChange() | CacheDBItem<PluginEventData> | Mandatory |
Implementation Details:
Sources:
Database plugins implement several optimization techniques for high-throughput scenarios:
All database writes use bulk copy operations instead of individual inserts:
| Operation | Method | Performance Characteristic |
|---|---|---|
| History Insert | Fastest<T>().SplitTable().BulkCopyAsync() | Optimized for large batches |
| Real-Time Update | Fastest<T>().BulkUpdateAsync() | Atomic update of multiple rows |
| QuestDB REST | RestApi().BulkCopyAsync() | HTTP-based batch insert |
| QuestDB SQL | Insertable().ExecuteCommandAsync() | PostgreSQL wire protocol |
Implementation Examples:
The CacheDBItem<T> queue system decouples data collection from database writes, providing:
Reduces database writes by filtering offline devices:
WhereIf(OnlineFilter, a => a.IsOnline == true)All database operations include diagnostic logging:
Stopwatch to measure elapsed millisecondsSources:
The QuestDBProducer plugin provides specialized support for QuestDB's time-series optimizations:
QuestDB supports two data ingestion methods:
REST API Mode:
HttpPort configuration propertySQL Mode:
Unlike SQL databases, QuestDB plugins do not use split table strategies:
NumberTableName and StringTableNameSources:
Database plugins implement consistent error handling patterns:
Plugins maintain a success flag to track operation state and log only on state changes:
if (success != result.IsSuccess)
{
if (!result.IsSuccess)
LogMessage?.LogWarning(result.ToString());
success = result.IsSuccess;
}
Implementation Locations:
This pattern prevents log spam during persistent failures while alerting on state transitions.
All database operations are wrapped in try-catch blocks:
try
{
db.Ado.CancellationToken = cancellationToken;
// Database operations
return OperResult.Success;
}
catch (Exception ex)
{
return new OperResult(ex);
}
Implementation Examples:
All async database operations honor cancellation tokens:
db.Ado.CancellationToken = cancellationTokenSources:
Database plugins share a common configuration model with plugin-specific extensions. For detailed configuration properties and UI components, see:
This overview establishes the foundational architecture and patterns used by database persistence plugins. For detailed implementation guidance:
For information on collecting data to persist, see Data Collection Plugins. For alternative data distribution methods, see Data Publishing and Distribution Plugins.
Refresh this wiki