NoSQL

Data writing

Database types of type DocumentDB can store JSON documents with any structure. Therefore it is possible to map any structure of the PLC in DocumentDBs. This document can be created automatically using the FB_JSONDataType or assembled using the string blocks. Make sure that the document variable is large enough. If you want to write several documents at the same time, you can transfer them in a JSON array.

The QueryOptionsare defined in preparation. The collection concerned and the query type are specified for this purpose. Each query type has its own structure. The structure T_QueryOptionDocumentDB_Insert is used for writing documents.

VAR
    fbNoSQLQueryBuilder_DocumentDB: FB_NoSQLQueryBuilder_DocumentDB;
    InsertQueryOptions: T_QueryOptionDocumentDB_Insert;
    sDocument : STRING(2000);
END_VAR

InsertQueryOptions.pDocuments:= ADR(sDocument); 
InsertQueryOptions.cbDocuments:= SIZEOF(sDocument);
fbNoSQLQueryBuilder_DocumentDB.eQueryType := E_DocumentDbQueryType.InsertOne;
fbNoSQLQueryBuilder_DocumentDB.sCollectionName := 'myCollection';
fbNoSQLQueryBuilder_DocumentDB.pQueryOptions := ADR(InsertQueryOptions);
fbNoSQLQueryBuilder_DocumentDB.cbQueryOptions := SIZEOF(InsertQueryOptions);

The function block FB_NoSQLQueryEvt is used for writing the document into the database. The Execute() method writes the transferred documents to the database. This execution is asynchronous to the PLC and can take several cycles. The Boolean return value indicates when the function block has completed its process:

VAR
    fbNoSQLQuery: FB_NoSQLQueryEvt(sNetID := '', tTimeout := TIME#15S0MS);
    fbJsonDataType: FB_JsonReadWriteDatatype;
END_VAR

CASE eState OF
    …
    eMyDbState.Write:

        // set the document yourself as json format (Example)
        sDocument := '{"myBool" : true, 
                       "Name" : "Some Name Value", 
                       "Value": 2.3, 
                       "Value2":3, 
                       "Child":{"Name":"Single Child", 
                                "Value":1, 
                                "myBool":true, 
                                "arr":[12.0,13.0,14.0,15.0], 
                                "myBool2" : true}, 
                       "Children":[
                                {"Name":"Child1"
                                ,"Value": 1, 
                                "myBool" : true, 
                                "arr":[12.1,13.1,14.1,15.1], 
                                "myBool2" : true},
                                {"Name":"Child2",
                                "Value":2, 
                                "myBool" : true, 
                                "arr":[12.2,13.2,14.2,15.2], 
                                "myBool2" : true},
                                {"Name":"Child3",
                                "Value":1, 
                                "myBool" : true, 
                                "arr":[12.3,13.3,14.3,15.3], 
                                "myBool2" : true}]
                       }';

        IF fbNoSQLQuery.Execute(1, myQueryBuilder) THEN
            IF fbNoSQLQuery.bError THEN
                InfoResult := fbNoSQLQuery.ipTcResult;
                eState:= eMyDbState.Error;
            ELSE
                eState:= eMyDbState.Idle; 
            END_IF
        END_IF
    …
END_CASE

The databases recognize the data type with which the individual variables are stored. However, as with MongoDB, the data type can be specified explicitly. If a timestamp is to be saved explicitly as a data type, it must be defined in the JSON document:

sDocument := '{…"myTimestamp": ISODate("2019-02-01T14:46:06.0000000"), …}';

The string can not only be formatted via the string formatting function blocks of the TwinCAT 3 libraries, but also via auxiliary function blocks for JSON documents, such as FB_JsonReadWriteDatatype from Tc3_JsonXml.

// set the document by JsonDataType
sTypeName := fbJsonDataType.GetDatatypeNameByAddress(SIZEOF(anyValue[1]), ADR(anyValue[1]));
sDocument := fbJsonDataType.GetJsonStringFromSymbol(sTypeName, SIZEOF(anyValue [1]), ADR(anyValue [1]));

Reading data

The data schema in the document-based database can be different for each document. In contrast, the PLC follows a fixed process image. The data may not correspond to the process image.

There are two different ways of reading data in the database: the find query and the aggregation method. Both return results from the database, although aggregation offers extended options for transforming the data into an appropriate form or for performing operations, such as calculating average values directly.

The QueryOptions are defined in preparation. The collection concerned and the query type are specified for this purpose. Each query type has its own structure. The structure T_QueryOptionDocumentDB_Aggregation is used for aggregating documents.

VAR
    fbNoSQLQueryBuilder_DocumentDB: FB_NoSQLQueryBuilder_DocumentDB;
    AggregationQueryOptions: T_QueryOptionDocumentDB_Aggregate;
    sPipeStages: STRING(1000);
END_VAR

AggregationQueryOptions.pPipeStages := ADR(sPipeStages);
AggregationQueryOptions.cbPipeStages := SIZEOF(sPipeStages);
fbNoSQLQueryBuilder_DocumentDB.eQueryType := E_DocumentDbQueryType.Aggregation;
fbNoSQLQueryBuilder_DocumentDB.sCollectionName := 'myCollection';
fbNoSQLQueryBuilder_DocumentDB.pQueryOptions := ADR(AggregationQueryOptions);
fbNoSQLQueryBuilder_DocumentDB.cbQueryOptions := SIZEOF(AggregationQueryOptions);

The FB_NoSQLQueryEvt is used for sending the aggregation query. The ExecuteDataReturn() method can be used to transfer the parameters and to place the returned data in the transferred memory reference. This execution is asynchronous to the PLC and takes several cycles. The Boolean return value indicates when the function block has completed its process:

VAR
    fbNoSQLQuery: FB_NoSQLQueryEvt(sNetID := '', tTimeout := TIME#15S0MS);
    fbNoSQLResult: FB_NoSQLResultEvt(sNetID := '', tTimeout := TIME#15S0MS);
END_VAR

CASE eState OF
    …
    eMyDbState.Aggregation:
        sPipeStages :='{$$match :{}}';
        IF fbNoSQLQuery.ExecuteDataReturn(1, myQueryBuilder, pNoSqlResult:= ADR(fbNoSQLResult), nDocumentLength=> nDocumentLength)) THEN
            IF fbNoSQLQuery.bError THEN
                InfoResult := fbNoSQLQuery.ipTcResult;
                eState:= eMyDbState.Error;
            ELSE
                eState:= eMyDbState.Idle; 
            END_IF
        END_IF
    …
END_CASE

The syntax of sPipeStages depends on the database type. It will return all records. Further options (with fictitious records) include:

The complete documentation of the operators is available from the respective database provider.

A reference to the returned data can now be found in the function block FB_NoSQLResultEvt. These can now be read as JSON documents in a string or as a structure. The data is now read directly into an array with a suitable structure. You can use the SQL Query Editor of the Database Server to directly generate a structure that matches the record. Instead of an array, it is also possible to store an address for a single structure when retrieving only one record.

VAR
    fbNoSQLResult: FB_NoSQLResultEvt(sNetID := '', tTimeout := TIME#15S0MS);
    aRdStruct : ARRAY [0..9] OF ST_MyCustomStruct;
    fbNoSqlValidation : FB_NoSQLValidationEvt(sNetID := '', tTimeout := TIME#15S0MS);
END_VAR

CASE eState OF
    …
    eMyDbState.ReadStruct:
        IF fbnoSQLDBResult.ReadAsStruct(0, 4, ADR(aRdStruct), SIZEOF(aRdStruct), bValidate := TRUE, ADR(fbNoSqlValidation), bDataRelease:= TRUE) THEN
            IF fbnoSQLDBResult.bError THEN
                InfoResult := fbnoSQLDBResult.ipTcResult;
                eState:= eMyDbState.Error;
            ELSE
                eState:= eMyDbState.Idle; 
            END_IF
        END_IF
    …
END_CASE

The TwinCAT Database Server takes into account the names of the elements in the record and the names of the variables when assigning record or structure. If these are to differ, the attribute "ElementName" can be used in the PLC:

TYPE ST_WindFarmData :
STRUCT
    {attribute 'ElementName' := '_id'}
    ID: T_ObjectId_MongoDB;
    {attribute 'ElementName' := 'Timestamp'}
    LastTime: DT;
    {attribute 'ElementName' := 'WindPlantData'}
    Data: ST_WindFarmData_WindPlantData;
END_STRUCT
END_TYPE

In this sample, "ElementName" specifies the name of the data in the database document. The start index and the number of records can be used to determine which records are to be returned with this call. In order to avoid possible duplications, please note that these options can already be carried out with operators at the "PipeplineStages".

Data validation

If there were conflicts between the record and the structure in the PLC at FB_NoSQLResult, they can be read out with FB_NoSQLValidationEvt. Examples of conflicts are missing or surplus records, or data type problems. The method GetIssues() can be used to read all conflicts as an array of strings. Surplus data that were not found in the PLC structure can be read as an array of strings in JSON format via GetRemainingData(). If necessary, these can then be read out separately into the correct structure or interpreted via the TwinCAT JSON library.

VAR
    fbNoSqlValidation : FB_NoSQLValidationEvt(sNetID := '', tTimeout := TIME#15S0MS);
    aIssues : ARRAY[0..99] OF STRING(512);
    aRemaining : ARRAY [0..9] OF STRING(1000);
END_VAR

CASE eState OF
    …
    eMyDbState.ValidationIssues:
        IF fbValidation.GetIssues(ADR(aIssues), SIZEOF(aIssues), FALSE) THEN
            IF fbValidation.bError THEN
                InfoResult := fbValidation.ipTcResult;
                eState:= eMyDbState.Error;
            ELSE
                eState:= eMyDbState.Idle; 
            END_IF

    eMyDbState.ValidationRemaining:
        IF fbValidation.GetRemainingData(ADR(aRemaining), SIZEOF(aRemaining), SIZEOF(aRemaining[1]), bDataRelease:= FALSE)THEN
            IF fbValidation.bError THEN
                InfoResult := fbValidation.ipTcResult;
                eState:= eMyDbState.Error;
            ELSE
                eState:= eMyDbState.Idle; 
            END_IF
    …
END_CASE