• open62541开发：添加sqlite3 历史数据库

                

        历史数据库在OPCUA  应用中十分重要，例如OPCUA 网关和OPCUA 汇聚服务器中都需要历史数据库功能。但是open62541 协议栈中仅包含了基于内存的历史数据库，在实际应用中是不够的。本博文讨论open62541 中添加sqlite3 为基础的历史数据库若干问题。

    借鉴

            Github上有一些open62541 添加sqlite 的项目的项目，在CSDN 上

    学习open62541 --- [58] 使用数据库存储历史数据

    的博文中，介绍了如何将sqlite 改成Linux 下的实现。它的项目在：

    GitHub - nicolasr75/open62541_sqlite:  

          他的项目对我帮助很大，但是，它的sqlite 不是加载到open62541 的Plugin 中，而是单独地添加在应用程序的项目中的。 他将c程序和h 文件合在了一起，只有使用sqlite 历史数据时才会被include 并且编译。另一方面，该项目只支持单变量历史数据存储。

          我的项目是将open62541 安装到系统目录中的，所以希望将sqlite 结合到open61541 程序中。另外扩展为多变量历史数据存储。下面记录我的实现过程。

    在open62541 包中加入sqlite3 

    安装sqlite3 库

    sudo apt -y install libsqlite3-dev

            将SQLiteBackend.h 放置到open62541 的Plugins/include /plugin/history_data 中。

    重新编译open62541 就可以了。也可以单独地将SQLiteBackend.h放置在应用程序中。

    修改成多节点存储

            在参考文章中只是单变量存储的方法。不支持多个变量的存储。我将它进行了修改：

    方法1：使用NodeId区分不同变量

    将MearsuringPointID 改为NodeId.节点的字符串（ns=1；i=5055）。注意要带单括号（‘）。

    1.  strncat(query, "') AND (MeasuringPointID='", QUERY_BUFFER_SIZE);
    2.      char measuringPointID[60];
    3.     snprintf(measuringPointID, 60, "ns=%d;i=%d",nodeId->namespaceIndex,nodeId->identifier.numeric);
    4.     strncat(query, measuringPointID, QUERY_BUFFER_SIZE);

    方法2 将不同变量的数据构建不同的表（table）

    这种方法的好处是访问数据库效率高。

    在这里，我们仅实现第一种方式。

    源代码（SQLiteBackend.h)

    1. #ifndef BACKEND_H
    2. #define BACKEND_H
    3.  
    4. #include 
    5. #include 
    6. #include 
    7. #include "sqlite3.h"
    8.  
    9.  
    10. static const size_t END_OF_DATA = SIZE_MAX;
    11. static const size_t QUERY_BUFFER_SIZE = 500;
    12.  
    13. char *UA_String2string(UA_String uaString)
    14. {
    15.   char *convert = (char *)UA_malloc(sizeof(char) * uaString.length + 1);
    16.   memcpy(convert, uaString.data, uaString.length);
    17.   convert[uaString.length] = '\0';
    18.   return convert;
    19. }
    20. UA_Int64 convertTimestampStringToUnixSeconds(const char* timestampString)
    21. {
    22.     UA_DateTimeStruct dts;
    23.     memset(&dts, 0, sizeof(dts));
    24.  //   printf("convertTimestampStringToUnixSeconds:%s\n",timestampString);
    25.     sscanf(timestampString, "%hu-%hu-%hu %hu:%hu:%hu", 
    26.         &dts.year, &dts.month, &dts.day, &dts.hour, &dts.min, &dts.sec);
    27.    // printf("%d %d\n",dts.year, dts.month);
    28.     UA_DateTime dt = UA_DateTime_fromStruct(dts);
    29.  
    30.     UA_Int64 t = UA_DateTime_toUnixTime(dt);
    31.  //   printf("convertTimestampStringToUnixSeconds=%lu\n",t);
    32.     return t;
    33. }
    34.  
    35.  
    36. const char* convertUnixSecondsToTimestampString(UA_Int64 unixSeconds)
    37. {
    38.     static char buffer[20];
    39.  
    40.    //  printf("convertUnixSecondsToTimestampString=%lu\n",unixSeconds);
    41.     UA_DateTime dt = UA_DateTime_fromUnixTime(unixSeconds);
    42.     UA_DateTimeStruct dts = UA_DateTime_toStruct(dt);
    43.  
    44.     struct tm tm;
    45.     memset(&tm, 0, sizeof(tm));
    46.     tm.tm_year = dts.year- 1900;
    47.     tm.tm_mon  = dts.month - 1;
    48.     tm.tm_mday = dts.day;
    49.     tm.tm_hour = dts.hour;
    50.     tm.tm_min  = dts.min;
    51.     tm.tm_sec  = dts.sec;
    52.    // printf("=%d %d\n",tm.tm_year,tm.tm_mon);
    53.     memset(buffer, 0, 20);
    54.     
    55.     strftime(buffer, 20, "%Y-%m-%d %H:%M:%S", &tm);
    56.   //  printf("convertUnixSecondsToTimestampString:%s\n",buffer);
    57.     return buffer;
    58. }
    59.  
    60.  
    61.  
    62. //Context that is needed for the SQLite callback for copying data.
    63. struct context_copyDataValues {
    64.     size_t maxValues;
    65.     size_t counter;
    66.     UA_DataValue *values;
    67. };
    68.  
    69. typedef  struct context_copyDataValues  context_copyDataValues;
    70.  
    71.  
    72. struct context_sqlite {
    73.  
    74.     sqlite3* sqlite;
    75.  
    76.     const char* measuringPointID;
    77. };
    78.  
    79.  
    80. static struct context_sqlite*
    81. generateContext_sqlite(const char* filename) 
    82. {
    83.  
    84.     sqlite3* handle;
    85.     char *errorMessage;
    86.  
    87.     int res = sqlite3_open(filename, &handle);
    88.  
    89.     if (res != SQLITE_OK)
    90.         return NULL;
    91.  
    92.     struct context_sqlite* ret = (struct context_sqlite*)UA_calloc(1, sizeof(struct context_sqlite));
    93.     if (ret == NULL)
    94.     {
    95.         return NULL;
    96.     }
    97.  
    98.     const char *sql = "DROP TABLE IF EXISTS PeriodicValues;" 
    99.                       "CREATE TABLE PeriodicValues(MeasuringPointID STRING, Value DOUBLE, Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP);";
    100.  
    101.     
    102.     res = sqlite3_exec(handle, sql, NULL, NULL, &errorMessage);
    103.     if (res != SQLITE_OK)
    104.     {
    105.         printf("%s | Error | %s\n", __func__, errorMessage);
    106.         sqlite3_free(errorMessage);
    107.         sqlite3_close(handle);
    108.  
    109.         return NULL;
    110.     }
    111.  
    112.  
    113.     ret->sqlite = handle;
    114.  
    115.     //For this demo we have only one source measuring point which we hardcode in the context.
    116.     //A more advanced demo should determine the available measuring points from the source
    117.     //itself or maybe an external configuration file.
    118.     ret->measuringPointID = "1";
    119.  
    120.     return ret;
    121. }
    122.  
    123.  
    124. static UA_StatusCode
    125. serverSetHistoryData_sqliteHDB(UA_Server *server,
    126. 	void *hdbContext,
    127. 	const UA_NodeId *sessionId,
    128. 	void *sessionContext,
    129. 	const UA_NodeId *nodeId,
    130. 	UA_Boolean historizing,
    131. 	const UA_DataValue *value)
    132. {
    133.     struct context_sqlite* context = (struct context_sqlite*)hdbContext;
    134.  
    135.     size_t result;
    136.     char* errorMessage;
    137.  
    138.     char query[QUERY_BUFFER_SIZE];
    139.   //  UA_String Id=nodeId->identifier.string;
    140.     char measuringPointID[60];
    141.     snprintf(measuringPointID, 60, "ns=%d;i=%d",nodeId->namespaceIndex,nodeId->identifier.numeric);
    142.     strncpy(query, "INSERT INTO PeriodicValues VALUES('", QUERY_BUFFER_SIZE);
    143.      strncat(query, measuringPointID, QUERY_BUFFER_SIZE);
    144.     if (value->hasValue && 
    145.         value->status == UA_STATUSCODE_GOOD &&
    146.         value->value.type == &UA_TYPES[UA_TYPES_DOUBLE])
    147.     {
    148.         char remaining[60];
    149.         snprintf(remaining, 60, "',%f, CURRENT_TIMESTAMP);", *(double*)(value->value.data));//datetime(CURRENT_TIMESTAMP,'localtime')
    150.         strncat(query, remaining, QUERY_BUFFER_SIZE);
    151.     }
    152.     else
    153.     {
    154.         printf("%s | Error | historical value is invalid\n", __func__);
    155.         return UA_STATUSCODE_BADINTERNALERROR;
    156.     }
    157.     printf("serverSetHistoryData_sqliteHDB:%s\n",query);
    158.     int res = sqlite3_exec(context->sqlite, query, NULL, NULL, &errorMessage);
    159.     if (res != SQLITE_OK)
    160.     {
    161.         printf("%s | Error | %s\n", __func__, errorMessage);
    162.         sqlite3_free(errorMessage);
    163.  
    164.         return UA_STATUSCODE_BADINTERNALERROR;
    165.     }
    166.  
    167.     return UA_STATUSCODE_GOOD;
    168. }
    169.  
    170.  
    171. static size_t
    172. getEnd_sqliteHDB(UA_Server *server,
    173. 	void *hdbContext,
    174. 	const UA_NodeId *sessionId,
    175. 	void *sessionContext,
    176. 	const UA_NodeId *nodeId)
    177. {
    178.     return END_OF_DATA;
    179. }
    180.  
    181.  
    182. //This is a callback for all queries that return a single timestamp as the number of Unix seconds
    183. static int timestamp_callback(void* result, int count, char **data, char **columns)
    184. {
    185.     *(UA_Int64*)result = convertTimestampStringToUnixSeconds(data[0]);
    186.     printf("timestamp_callback:%s\n",data[0]);
    187.  //   printf("timestamp_callback%lu\n",*(UA_Int64*)result);
    188.     return 0;
    189. }
    190.  
    191.  
    192. static int resultSize_callback(void* result, int count, char **data, char **columns)
    193. {
    194.     *(size_t*)result = strtol(data[0], NULL, 10);
    195.  
    196.     return 0;
    197. }
    198.  
    199.  
    200. static size_t
    201. lastIndex_sqliteHDB(UA_Server *server,
    202.     void *hdbContext,
    203.     const UA_NodeId *sessionId,
    204.     void *sessionContext,
    205.     const UA_NodeId *nodeId)
    206. {
    207.     struct context_sqlite* context = (struct context_sqlite*)hdbContext;
    208.  
    209.     size_t result;
    210.     char* errorMessage;
    211.  
    212.     char query[QUERY_BUFFER_SIZE];
    213.     strncpy(query, "SELECT Timestamp FROM PeriodicValues WHERE MeasuringPointID='", QUERY_BUFFER_SIZE);
    214.      char measuringPointID[60];
    215.     snprintf(measuringPointID, 60, "ns=%d;i=%d",nodeId->namespaceIndex,nodeId->identifier.numeric);
    216.     strncat(query, measuringPointID, QUERY_BUFFER_SIZE);
    217.     strncat(query, "'  ORDER BY Timestamp DESC LIMIT 1", QUERY_BUFFER_SIZE);//DESC
    218.     printf("lastIndex:%s\n",query);
    219.     int res = sqlite3_exec(context->sqlite, query, timestamp_callback, &result, &errorMessage);
    220.  
    221.     if (res != SQLITE_OK)
    222.     {
    223.         printf("%s | Error | %s\n", __func__, errorMessage);
    224.         sqlite3_free(errorMessage);
    225.         return END_OF_DATA;
    226.     }
    227.  
    228.     return result;
    229. }
    230.  
    231. static size_t
    232. firstIndex_sqliteHDB(UA_Server *server,
    233.     void *hdbContext,
    234.     const UA_NodeId *sessionId,
    235.     void *sessionContext,
    236.     const UA_NodeId *nodeId)
    237. {
    238.     struct context_sqlite* context = (struct context_sqlite*)hdbContext;
    239.  
    240.     size_t result;
    241.     char* errorMessage;
    242.  
    243.     char query[QUERY_BUFFER_SIZE];
    244.     strncpy(query, "SELECT Timestamp FROM PeriodicValues WHERE MeasuringPointID='", QUERY_BUFFER_SIZE);
    245.      char measuringPointID[60];
    246.     snprintf(measuringPointID, 60, "ns=%d;i=%d",nodeId->namespaceIndex,nodeId->identifier.numeric);
    247.     strncat(query, measuringPointID, QUERY_BUFFER_SIZE);
    248.     strncat(query, "' ORDER BY Timestamp LIMIT 1", QUERY_BUFFER_SIZE);
    249.  
    250.     int res = sqlite3_exec(context->sqlite, query, timestamp_callback, &result, &errorMessage);
    251.     printf("firsrIndex:%s\n",query);
    252.     if (res != SQLITE_OK)
    253.     {
    254.         printf("%s | Error | %s\n", __func__, errorMessage);
    255.         sqlite3_free(errorMessage);
    256.         return END_OF_DATA;
    257.     }
    258.  
    259.     return result;
    260. }
    261.  
    262.  
    263. static UA_Boolean
    264. search_sqlite(struct context_sqlite* context,const UA_NodeId *nodeId,
    265. 	UA_Int64 unixSeconds, MatchStrategy strategy,
    266. 	size_t *index) 
    267. {	
    268.     *index = END_OF_DATA; // TODO
    269.     char* errorMessage;
    270.  
    271.     char query[QUERY_BUFFER_SIZE];
    272.     strncpy(query, "SELECT Timestamp FROM PeriodicValues WHERE MeasuringPointID='", QUERY_BUFFER_SIZE);
    273.      char measuringPointID[60];
    274.     snprintf(measuringPointID, 60, "ns=%d;i=%d",nodeId->namespaceIndex,nodeId->identifier.numeric);
    275.     strncat(query, measuringPointID, QUERY_BUFFER_SIZE);
    276.     strncat(query, "' AND ", QUERY_BUFFER_SIZE);
    277.  
    278.     switch (strategy)
    279.     {
    280.     case MATCH_EQUAL_OR_AFTER:
    281.         strncat(query, "Timestamp>='", QUERY_BUFFER_SIZE);
    282.         strncat(query, convertUnixSecondsToTimestampString(unixSeconds), QUERY_BUFFER_SIZE);
    283.         strncat(query, "' ORDER BY Timestamp LIMIT 1", QUERY_BUFFER_SIZE);
    284.         break;
    285.     case MATCH_AFTER:
    286.         strncat(query, "Timestamp>'", QUERY_BUFFER_SIZE);
    287.         strncat(query, convertUnixSecondsToTimestampString(unixSeconds), QUERY_BUFFER_SIZE);
    288.         strncat(query, "' ORDER BY Timestamp LIMIT 1", QUERY_BUFFER_SIZE);
    289.         break;
    290.     case MATCH_EQUAL_OR_BEFORE:
    291.         strncat(query, "Timestamp<='", QUERY_BUFFER_SIZE);
    292.         strncat(query, convertUnixSecondsToTimestampString(unixSeconds), QUERY_BUFFER_SIZE);
    293.         strncat(query, "' ORDER BY Timestamp DESC LIMIT 1", QUERY_BUFFER_SIZE);
    294.         break;
    295.     case MATCH_BEFORE:
    296.         strncat(query, "Timestamp<'", QUERY_BUFFER_SIZE);
    297.         strncat(query, convertUnixSecondsToTimestampString(unixSeconds), QUERY_BUFFER_SIZE);
    298.         strncat(query, "' ORDER BY Timestamp DESC LIMIT 1", QUERY_BUFFER_SIZE);
    299.         break;
    300.     default:
    301.         return false;
    302.     }
    303.  
    304.     printf("search_sqlite:%s\n",query);
    305.     int res = sqlite3_exec(context->sqlite, query, timestamp_callback, index, &errorMessage);
    306.  
    307.     if (res != SQLITE_OK)
    308.     {
    309.         printf("%s | Error | %s\n", __func__, errorMessage);
    310.         sqlite3_free(errorMessage);
    311.         return false;
    312.     }
    313.     else
    314.     {
    315.         return true;
    316.     }
    317.  
    318. }
    319.  
    320. static size_t
    321. getDateTimeMatch_sqliteHDB(UA_Server *server,
    322.     void *hdbContext,
    323.     const UA_NodeId *sessionId,
    324.     void *sessionContext,
    325.     const UA_NodeId *nodeId,
    326.     const UA_DateTime timestamp,
    327.     const MatchStrategy strategy)
    328. {
    329.     struct context_sqlite* context = (struct context_sqlite*)hdbContext;
    330.  
    331.     UA_Int64 ts =UA_DateTime_toUnixTime(timestamp);
    332.     printf("getDateTimeMatch_sqliteHDB:%s\n",convertUnixSecondsToTimestampString(ts));
    333.     printf("strategy:%u\n",strategy);
    334.     size_t result = END_OF_DATA;
    335.  
    336.     UA_Boolean res = search_sqlite(context,nodeId, ts, strategy, &result);
    337.  
    338.     return result;
    339. }
    340.  
    341.  
    342. static size_t
    343. resultSize_sqliteHDB(UA_Server *server,
    344.     void *hdbContext,
    345.     const UA_NodeId *sessionId,
    346.     void *sessionContext,
    347.     const UA_NodeId *nodeId,
    348.     size_t startIndex,
    349.     size_t endIndex)
    350. {
    351.     struct context_sqlite* context = (struct context_sqlite*)hdbContext;
    352.  
    353.     char* errorMessage;
    354.     size_t result = 0;
    355.     printf("resultSize_sqliteHDB:startIndex:%lu\n",startIndex);
    356.      printf("resultSize_sqliteHDB:endIndex:%lu\n",endIndex);
    357.     char query[QUERY_BUFFER_SIZE];
    358.     strncpy(query, "SELECT COUNT(*) FROM PeriodicValues WHERE ", QUERY_BUFFER_SIZE);
    359.     strncat(query, "(Timestamp>='", QUERY_BUFFER_SIZE);
    360.     strncat(query, convertUnixSecondsToTimestampString(startIndex), QUERY_BUFFER_SIZE);
    361.     strncat(query, "') AND (Timestamp<='", QUERY_BUFFER_SIZE);
    362.     strncat(query, convertUnixSecondsToTimestampString(endIndex), QUERY_BUFFER_SIZE);
    363.     strncat(query, "') AND (MeasuringPointID='", QUERY_BUFFER_SIZE);
    364.      char measuringPointID[60];
    365.     snprintf(measuringPointID, 60, "ns=%d;i=%d",nodeId->namespaceIndex,nodeId->identifier.numeric);
    366.     strncat(query, measuringPointID, QUERY_BUFFER_SIZE);
    367.     strncat(query, "')", QUERY_BUFFER_SIZE);
    368.     printf("resultSize_sqliteHDB:%s\n",query);
    369.     int res = sqlite3_exec(context->sqlite, query, resultSize_callback, &result, &errorMessage);
    370.  
    371.     if (res != SQLITE_OK)
    372.     {
    373.         printf("%s | Error | %s\n", __func__, errorMessage);
    374.         sqlite3_free(errorMessage);
    375.         return 0; // no data
    376.     }
    377.  
    378.     return result;
    379. }
    380.  
    381.  
    382. static int copyDataValues_callback(void* result, int count, char **data, char **columns)
    383. {
    384.     UA_DataValue dv;
    385.     UA_DataValue_init(&dv);
    386.  
    387.     dv.status = UA_STATUSCODE_GOOD;
    388.     dv.hasStatus = true;
    389.  
    390.     dv.sourceTimestamp = UA_DateTime_fromUnixTime(convertTimestampStringToUnixSeconds(data[0]));
    391.     dv.hasSourceTimestamp = true;
    392.  
    393.     dv.serverTimestamp = dv.sourceTimestamp;
    394.     dv.hasServerTimestamp = true;
    395.  
    396.     double value = strtod(data[1], NULL);
    397.  
    398.     UA_Variant_setScalarCopy(&dv.value, &value, &UA_TYPES[UA_TYPES_DOUBLE]);
    399.     dv.hasValue = true;
    400.  
    401.     context_copyDataValues* ctx = (context_copyDataValues*)result;
    402.  
    403.     UA_DataValue_copy(&dv, &ctx->values[ctx->counter]);
    404.  
    405.     ctx->counter++;
    406.  
    407.     if (ctx->counter == ctx->maxValues)
    408.     {
    409.         return 1;
    410.     }
    411.     else
    412.     {
    413.         return 0;
    414.     }
    415. }
    416.  
    417.  
    418. static UA_StatusCode
    419. copyDataValues_sqliteHDB(UA_Server *server,
    420.     void *hdbContext,
    421.     const UA_NodeId *sessionId,
    422.     void *sessionContext,
    423.     const UA_NodeId *nodeId,
    424.     size_t startIndex,
    425.     size_t endIndex,
    426.     UA_Boolean reverse,
    427.     size_t maxValues,
    428.     UA_NumericRange range,
    429.     UA_Boolean releaseContinuationPoints,
    430.     const UA_ByteString *continuationPoint,
    431.     UA_ByteString *outContinuationPoint,
    432.     size_t *providedValues,
    433.     UA_DataValue *values)
    434. {
    435.     //NOTE: this demo does not support continuation points!!!
    436.     struct context_sqlite* context = (struct context_sqlite*)hdbContext;
    437.  
    438.     char* errorMessage;
    439.   //  const char* measuringPointID = "1";
    440.  
    441.     char query[QUERY_BUFFER_SIZE];
    442.     strncpy(query, "SELECT Timestamp, Value FROM PeriodicValues WHERE ", QUERY_BUFFER_SIZE);
    443.     strncat(query, "(Timestamp>='", QUERY_BUFFER_SIZE);
    444.     strncat(query, convertUnixSecondsToTimestampString(startIndex), QUERY_BUFFER_SIZE);
    445.     strncat(query, "') AND (Timestamp<='", QUERY_BUFFER_SIZE);
    446.     strncat(query, convertUnixSecondsToTimestampString(endIndex), QUERY_BUFFER_SIZE);
    447.     strncat(query, "') AND (MeasuringPointID='", QUERY_BUFFER_SIZE);
    448.      char measuringPointID[60];
    449.     snprintf(measuringPointID, 60, "ns=%d;i=%d",nodeId->namespaceIndex,nodeId->identifier.numeric);
    450.     strncat(query, measuringPointID, QUERY_BUFFER_SIZE);
    451.     strncat(query, "')", QUERY_BUFFER_SIZE);
    452.  
    453.     context_copyDataValues ctx;
    454.     ctx.maxValues = maxValues;
    455.     ctx.counter = 0;
    456.     ctx.values = values;
    457.  
    458.     int res = sqlite3_exec(context->sqlite, query, copyDataValues_callback, &ctx, &errorMessage);
    459.  
    460.     if (res != SQLITE_OK)
    461.     {
    462.         if (res == SQLITE_ABORT) // if reach maxValues, then request abort, so this is not error
    463.         {
    464.             sqlite3_free(errorMessage);
    465.             return UA_STATUSCODE_GOOD;
    466.         }
    467.         else
    468.         {
    469.             printf("%s | Error | %s\n", __func__, errorMessage);
    470.             sqlite3_free(errorMessage);
    471.             return UA_STATUSCODE_BADINTERNALERROR;
    472.         }
    473.             
    474.     }
    475.     else
    476.     {
    477.         return UA_STATUSCODE_GOOD;
    478.     }
    479. }
    480.  
    481. static const UA_DataValue*
    482. getDataValue_sqliteHDB(UA_Server *server,
    483.     void *hdbContext,
    484.     const UA_NodeId *sessionId,
    485.     void *sessionContext,
    486.     const UA_NodeId *nodeId,
    487. 	size_t index)
    488. {
    489.     struct context_sqlite* context = (struct context_sqlite*)hdbContext;
    490.  
    491.     return NULL;
    492. }
    493.  
    494.  
    495. static UA_Boolean
    496. boundSupported_sqliteHDB(UA_Server *server,
    497.     void *hdbContext,
    498.     const UA_NodeId *sessionId,
    499.     void *sessionContext,
    500.     const UA_NodeId *nodeId)
    501. {
    502. 	return false; // We don't support returning bounds in this demo
    503. }
    504.  
    505.  
    506. static UA_Boolean
    507. timestampsToReturnSupported_sqliteHDB(UA_Server *server,
    508.     void *hdbContext,
    509.     const UA_NodeId *sessionId,
    510.     void *sessionContext,
    511.     const UA_NodeId *nodeId,
    512.     const UA_TimestampsToReturn timestampsToReturn)
    513. {
    514.     return true;
    515. }
    516.  
    517.  
    518. UA_HistoryDataBackend
    519. UA_HistoryDataBackend_sqlite(const char* filename)
    520. {
    521.     UA_HistoryDataBackend result;
    522.     memset(&result, 0, sizeof(UA_HistoryDataBackend));
    523.     result.serverSetHistoryData = &serverSetHistoryData_sqliteHDB;
    524.     result.resultSize = &resultSize_sqliteHDB;
    525.     result.getEnd = &getEnd_sqliteHDB;
    526.     result.lastIndex = &lastIndex_sqliteHDB;
    527.     result.firstIndex = &firstIndex_sqliteHDB;
    528.     result.getDateTimeMatch = &getDateTimeMatch_sqliteHDB;
    529.     result.copyDataValues = ©DataValues_sqliteHDB;
    530.     result.getDataValue = &getDataValue_sqliteHDB;
    531.     result.boundSupported = &boundSupported_sqliteHDB;
    532.     result.timestampsToReturnSupported = ×tampsToReturnSupported_sqliteHDB;
    533.     result.deleteMembers = NULL; // We don't support deleting in this demo
    534.     result.getHistoryData = NULL; // We don't support the high level API in this demo
    535.     result.context = generateContext_sqlite(filename);
    536.     return result;
    537. }
    538.  
    539.  
    540. #endif

    应用程序测试

    server 修改为添加两个变量。NodeId 使用Numeric 方式。

    源代码（server.h)

    1. #include 
    2. #include 
    3. #include 
    4. #include 
    5. #include 
    6. #include 
    7. #include 
    8. #include "SQLiteBackend.h"
    9.  
    10.  
    11. static UA_Boolean running = true;
    12.  
    13. static void stopHandler(int sign) 
    14. {
    15.     (void)sign;
    16.     UA_LOG_INFO(UA_Log_Stdout, UA_LOGCATEGORY_SERVER, "received ctrl-c");
    17.     running = false;
    18. }
    19.  
    20. UA_NodeId addHistoryVariable(UA_Server *server,char *VairableName){
    21.      /* Define the attribute of the uint32 variable node */
    22.     UA_VariableAttributes attr = UA_VariableAttributes_default;
    23.     UA_Double myDouble = 17.2;
    24.     UA_Variant_setScalar(&attr.value, &myDouble, &UA_TYPES[UA_TYPES_DOUBLE]);
    25.     attr.description = UA_LOCALIZEDTEXT("en-US", VairableName);
    26.     attr.displayName = UA_LOCALIZEDTEXT("en-US", VairableName);
    27.     attr.dataType = UA_TYPES[UA_TYPES_DOUBLE].typeId;
    28.  
    29.     /*
    30.     * We set the access level to also support history read
    31.     * This is what will be reported to clients
    32.     */
    33.     attr.accessLevel = UA_ACCESSLEVELMASK_READ | UA_ACCESSLEVELMASK_WRITE | UA_ACCESSLEVELMASK_HISTORYREAD;
    34.  
    35.     /*
    36.     * We also set this node to historizing, so the server internals also know from it.
    37.     */
    38.     attr.historizing = true;
    39.  
    40.     /* Add the variable node to the information model */
    41.     UA_NodeId doubleNodeId = UA_NODEID_STRING(1, VairableName);
    42.     UA_QualifiedName doubleName = UA_QUALIFIEDNAME(1, VairableName);
    43.     UA_NodeId parentNodeId = UA_NODEID_NUMERIC(0, UA_NS0ID_OBJECTSFOLDER);
    44.     UA_NodeId parentReferenceNodeId = UA_NODEID_NUMERIC(0, UA_NS0ID_ORGANIZES);
    45.     UA_NodeId outNodeId;
    46.     UA_NodeId_init(&outNodeId);
    47.     UA_StatusCode retval = UA_Server_addVariableNode(server,
    48.         UA_NODEID_NULL,
    49.         parentNodeId,
    50.         parentReferenceNodeId,
    51.         doubleName,
    52.         UA_NODEID_NUMERIC(0, UA_NS0ID_BASEDATAVARIABLETYPE),
    53.         attr,
    54.         NULL,
    55.         &outNodeId);
    56.  
    57.     UA_LOG_INFO(UA_Log_Stdout, UA_LOGCATEGORY_SERVER, "UA_Server_addVariableNode %s", UA_StatusCode_name(retval));
    58.  
    59.     
    60. UA_LOG_INFO(UA_Log_Stdout, UA_LOGCATEGORY_SERVER,"addHistoryVariable:%d",outNodeId.identifier.numeric);
    61. return outNodeId;
    62. }
    63. int main(void) 
    64. {
    65.     signal(SIGINT, stopHandler);
    66.     signal(SIGTERM, stopHandler);
    67.  
    68.  
    69.     UA_Server *server = UA_Server_new();
    70.     UA_ServerConfig *config = UA_Server_getConfig(server);
    71.     UA_ServerConfig_setDefault(config);
    72.  
    73.  
    74.     UA_HistoryDataGathering gathering = UA_HistoryDataGathering_Default(1);
    75.  
    76.    
    77.     config->historyDatabase = UA_HistoryDatabase_default(gathering);
    78.  
    79. UA_NodeId VariableANodeId=addHistoryVariable(server,"myDoubleValueA");
    80. UA_NodeId VariableBNodeId=addHistoryVariable(server,"myDoubleValueB");
    81.    
    82.     UA_HistorizingNodeIdSettings setting;
    83.  
    84.  
    85.     setting.historizingBackend = UA_HistoryDataBackend_sqlite("database.sqlite");
    86.  
    87.     
    88.     setting.maxHistoryDataResponseSize = 100;
    89.  
    90.  
    91.     setting.historizingUpdateStrategy = UA_HISTORIZINGUPDATESTRATEGY_VALUESET;
    92.  
    93.     
    94.     UA_StatusCode   retval = gathering.registerNodeId(server, gathering.context, &VariableANodeId, setting);
    95.   retval = gathering.registerNodeId(server, gathering.context, &VariableBNodeId, setting);
    96.     UA_LOG_INFO(UA_Log_Stdout, UA_LOGCATEGORY_SERVER, "registerNodeId %s", UA_StatusCode_name(retval));
    97.  
    98.  retval = UA_Server_run(server, &running);
    99.     UA_LOG_INFO(UA_Log_Stdout, UA_LOGCATEGORY_SERVER, "UA_Server_run %s", UA_StatusCode_name(retval));
    100.  
    101.  
    102.     UA_Server_delete(server);
    103.  
    104.     return (int)retval;
    105.  
    106. }

    使用DB Browser for SQLite 工具查看数据库数据

    下载一个DB Browser for SQLite 工具可以查看数据库中的数据：

    使用uaExpert

             使用uaExpert 修改变量的数据 ，并且查看历史数据。具体方法可以参照引用的博文。特别注意的是；在数据库中存储的时间标签是UTC标准时间，而uaExpert 使用的是北京时间，它们相差了8小时。在查看历史数据时，要注意起始时间和结束时间,设置错了，不会读出。可以通过DB Browser 查看记录数据的时间标签，并将小时加8。

    结束语

            开源软件并不完全是拿来就够了。真正实际应用，需要大量的开发工作。要充分估计开源软件的学习和扩展功能的工作量。

  • 相关阅读:
    招投标系统简介 企业电子招投标采购系统源码之电子招投标系统 —降低企业采购成本
    金典成为饿了么小蓝盒首个低碳“盒”伙人：战略合作迎绿色亚运
    JavaScript常用工具函数汇总（一）
    docker+mongo主从仲裁+用户密码认证
    IMX6--＞8 RTC同步程序涉及到的知识点
    布尔矩阵的奇偶性
    一个电子小说阅读系统源码，thinkphp开发的小说系统系统
    1.8 打好shell基础
    python与java的一些不同简录
    uni-app学习（1）
  • 原文地址：https://blog.csdn.net/yaojiawan/article/details/133159557