====== Mervis DB ======
{{ cs:sw:01-mervis:mervis_db.png?nolink&400 |}}
Mervis DB is a highly optimized database designed for long-term storage of data from units, applications and many other devices that use an open API database.
For Unipi units is the main use as storage of historical data and their analysis using [[en:sw:01-mervis:05-mervis-scada|Mervis SCADA]].
===== The service is available in cloud and local variants =====
Part of Mervis SCADA (cloud)\\
The standard way of using Mervis DB is the cloud service variant. The Mervis DB credentials are generated **together with the Mervis SCADA project** and are intended for all PLCs connected to this SCADA project.
To use Mervis DB together with Mervis SCADA, follow the instructions [[en:sw:01-mervis:mervis-scada-project-hidden|]].
----
Only Mervis DB (cloud)\\
The Mervis DB cloud service can also be operated **separately**, without Mervis SCADA. In this case, credentials are generated individually and their validity must be agreed in advance.
If you are only interested in the Mervis DB, please [[https://www.unipi.technology/contacts|contact our sales department]] for an individual offer.
Instructions for setting up a unit and saving data to the Mervis DB can be found in the manual: [[en:sw:01-mervis:storing-data-to-mervis-db-hidden|]]
----
Local installation option\\
The Mervis DB service can also run on a local server. For more information contact our [[info@unipi.technology|Sales Department]].
===== Data migration and management =====
As part of the paid service, we also offer several options of data management:
* Transfer of data between Mervis DB accounts, including possible timestamp editing
* Import data from Excel or CSV file
* Export data to Excel or CSV file
* Delete unwanted data from Mervis DB
If you are interested in any of the above mentioned services, contact our [[info@unipi.technology|Sales Department]].
\\
{{ en:sw:01-mervis:migrace_mervis-db_en.png?direct |}}
===== Mervis DB Viewer =====
The Mervis DB Viewer can be used to view stored data from a cloud instalation of Mervis DB on Unipi servers. It is an online application that connects to the database and displays database data according to the specified parameters (e.g. time range).
URL address: [[https://db.unipi.technology/viewer]].
{{ cs:sw:01-mervis:db-mervis-info-viewer.png?direct |}}
===== API =====
Mervis DB supports open SOAP API described by this WSDL:
* {{cs:sw:01-mervis:mervisdb_wsdl-2017-07-12.zip|}}
For special use, there are other interfaces for communication with Mervis DB - for their use and description, please contact [[en:contact:00-start|technical support]].
==== List of supported functions ====
^ Function ^ Description ^
| SaveData | Save one or multiple records from one or multiple variables |
| GetData/GetDataAsync | Read one or multiple records from one or multiple variables. |
| GetParticularData/GetParticulatDataAsync | Optimized method for reading of „special“ data - last value, last value after certain time moment etc. |
| GetVariables | Get the list of variables. |
| GetAllVariables | |
| ServerAlive | Obsolete |
| DeleteVariables | |
| DeleteData | |
| EnsureVariables | |
| GetTransformedData/GetTransformedDataAsync | Method for reading of aggregated results with given time period, the aggregation may be Min, Max, Integral, Weighted average, First, Last and Median |
| GetServerVersion | |
| GetVariableStats | Obsolete |
| GetVariableStatsAsync | Obsolete |
| CheckCredentials | |
==== Object Properties ====
* (UTC)TimeStamp - UTC time stamp of the record
* GoodThrough - UTC time stamp of the end of validity of particular record
* Interval - period of time (in seconds) when next value should be recorded
==== Examples ====
=== C# ===
{{ cs:sw:01-mervis:mervis_db_csharp.png?direct |}}
Download: \\
* {{cs:sw:01-mervis:mervisdb_getdata_example.zip|}}
* {{cs:sw:01-mervis:mervisdb_savedata_example.zip|}}
Code example: \\
using System;
using System.Linq;
using System.Threading.Tasks;
using MervisDb_GetData_Example.MervisDbService;
namespace MervisDb_GetData_Example {
class Program {
static void Main(string[] args) {
//
// Demo of asynchronous methods.
//
var task = Task.Run(async() => {
//
// Create access to the real server.
// Without SSL connections, you have to remove binding parameters "" in the App.config.
// The client is automatically generated from the WSDL file available here: https://kb.mervis.info/doku.php/en:mervis-db:10-api
//
using(HistoryDbAccessClient client = new HistoryDbAccessClient("HistoryAccess", "http://db.unipi.technology/dbaccess")) {
// Authentication credetials in the database.
Credentials credentials = new Credentials {
Name = "XXXX", Password = "YYYY"
};
// Specification of the variables through Key-Value.
// Here we use 2 variables.
KeyValuePair[][] keys = new KeyValuePair[][] {
new KeyValuePair[] {
new KeyValuePair {
Key = "DPGuid", Value = "338E2882-D72B-4D17-A241-73E8BC30F458"
}, new KeyValuePair {
Key = "StationName", Value = "AAABBB"
}
},
new KeyValuePair[] {
new KeyValuePair {
Key = "DPGuid", Value = "CC80211D-3D29-4CC2-91A2-F69483D566B5"
}, new KeyValuePair {
Key = "StationName", Value = "AAABBB"
}
}
};
// From-To dates. The dates must be specified in the UTC time zone.
DateTime utcTo = DateTime.UtcNow;
DateTime utcFrom = DateTime.UtcNow.AddDays(-1);
// Retrieving the data goes through cycling of values and variables. The server returns recommended values needed for the the next cycle.
int variableOffset = 0; // the offset of the variable
int variableCount = 10; // maximal number of variables returned in one request
int valueOffset; // the offset of the value
int valueCount = 1000; // maximal values in on request
Console.WriteLine("Reading values...");
do {
valueOffset = 0;
do {
// Execute the request.
var result = await client.GetDataAsyncAsync(credentials, keys, utcFrom, utcTo, variableOffset, variableCount, valueOffset, valueCount);
// Check the return code. "0;OK" is what we want.
if (!result.ReturnCode.StartsWith("0;")) {
Console.WriteLine("Error on reading: {0}", result.ReturnCode);
}
// Cycle through the data and print it out.
foreach(var valRecord in result.Data) {
Console.WriteLine("Variable: {0}", String.Concat(valRecord.Keys.Select((i) => {
return String.Format("{0}={1}; ", i.Key, i.Value);
})));
foreach(var value in valRecord.Vals) {
Console.WriteLine("{0}->{1} [{2}] {3}", value.Ts, value.Gt, value.Hvt, value.Dv /* Use value type according to the Hvt (History Value Type) */ );
}
}
valueOffset = result.NextValueOffset;
variableOffset = result.NextVariableOffset;
}
while (valueOffset != -1);
}
while (variableOffset != -1);
}
});
Console.WriteLine("DB communication is running in background");
try {
task.Wait();
} catch (Exception exc) {
Console.WriteLine("Exception: {0}", exc.ToString());
}
Console.WriteLine("DB communication finished");
Console.ReadLine();
}
}
}
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using MervisDb_SaveData_Example.MervisDbService;
namespace MervisDb_SaveData_Example
{
class Program
{
static void Main(string[] args)
{
//
// Demo of asynchronous methods.
//
var task = Task.Run(async () =>
{
//
// Create access to the real server.
// Without SSL connections, you have to remove binding parameters "" in the App.config.
// The client is automatically generated from the WSDL file available here: https://kb.mervis.info/doku.php/en:mervis-db:10-api
//
using (HistoryDbAccessClient client = new HistoryDbAccessClient("HistoryAccess", "http://db.unipi.technology/dbaccess"))
{
// Authentication credetials in the database.
Credentials credentials = new Credentials { Name = "XXXX", Password = "YYYY" };
// Adding records...
List records = new List();
DateTime utcTimeStamp = new DateTime(2018, 1, 1, 12, 0, 0, DateTimeKind.Utc);
// Each value is stored in a ValueRecord structure
ValueRecord rec = new ValueRecord();
rec.UtcTimeStamp = utcTimeStamp; // time stamp of the value, UTC date time must be used
rec.HistoryValueType = HistoryValueType.Double; // type of the value
rec.DoubleValue = 23.4; // value itself
rec.Interval = 60.0; // sampling period in seconds. When the difference of timestamps of subsequent values if greater than 1.1 multiple of the interval, the data should be considered incomplete.
rec.Keys = new KeyValuePair[] // identification of the variable. At least one key-value pair must be "IsKey" to identify a variable.
{
new KeyValuePair { IsKey = true, Key = "Root", Value = "Building A"},
new KeyValuePair { IsKey = true, Key = "Level1", Value = "Floor 1"},
new KeyValuePair { IsKey = true, Key = "Level2", Value = "Room 7"},
new KeyValuePair { IsKey = true, Key = "Level3", Value = "Temperature"},
new KeyValuePair { IsKey = false, Key = "Description", Value = "Temperature in the room 7"},
new KeyValuePair { IsKey = false, Key = "Unit", Value = "°C"},
};
records.Add(rec);
// Save data method returns the number of stored records
int recordsSaved = await client.SaveDataAsync(credentials, records.ToArray());
Console.WriteLine("Records saved: {0}", recordsSaved);
}
});
Console.WriteLine("DB communication is running in background");
try
{
task.Wait();
}
catch (Exception exc)
{
Console.WriteLine("Exception: {0}", exc.ToString());
}
Console.WriteLine("DB communication finished");
Console.ReadLine();
}
}
}
using System;
using System.Linq;
using System.Threading.Tasks;
using MervisDb_GetTransformedData_Example.MervisDbService;
namespace MervisDb_GetTransformedData_Example
{
class Program
{
static void Main(string[] args)
{
//
// Demo of asynchronous method.
//
var task = Task.Run(async () =>
{
//
// Create access to the real server.
// Without SSL connections, you have to remove binding parameters "" in the App.config.
// The client is automatically generated from the WSDL file available here: https://kb.mervis.info/doku.php/en:mervis-db:10-api
//
using (HistoryDbAccessClient client = new HistoryDbAccessClient("HistoryAccess", "http://db.unipi.technology/dbaccess"))
{
// Authentication credentials in the database.
Credentials credentials = new Credentials { Name = "XXXXX", Password = "YYYYY" };
// Specification of the variables through Key-Value.
// Here we use 2 variables.
KeyValuePair[][] variableKeys = new KeyValuePair[][] {
new KeyValuePair[] {
new KeyValuePair (false, /*Key*/ "DPGuid", /*Value*/ "338E2882-D72B-4D17-A241-73E8BC30F458"),
new KeyValuePair (false, /*Key*/ "StationName", /*Value*/ "AAABBB")
},
new KeyValuePair[] {
new KeyValuePair (false, /*Key*/ "DPGuid", /*Value*/ "CC80211D-3D29-4CC2-91A2-F69483D566B5"),
new KeyValuePair (false, /*Key*/ "StationName", /*Value*/ "AAABBB")
}
};
// Aggregation request
var aggregation = new AggregationRequest();
aggregation.Types = new AggregationType[] { AggregationType.Last };
aggregation.Interpolation = InterpolationType.None;
//aggregation.MaxNeighborDistance = ..only for interpolation;
aggregation.IsoPattern = new string[] { "PT15M" }; // 15 min period (ISO 8601 duration pattern)
aggregation.From = new DateTime(2018, 11, 24, 0, 0, 0, 0, DateTimeKind.Unspecified);
aggregation.To = new DateTime(2018, 11, 25, 0, 0, 0, 0, DateTimeKind.Unspecified);
aggregation.TimeZone = @"Europe/Prague";
// Retrieving the data goes through cycling of intervals and variables. The server returns recommended intervals needed for the the next cycle.
int variableOffset = 0; // the offset of the variable
int variableCount = 10; // maximal number of variables returned in one request
int intervalOffset; // the offset of the interval
int intervalCount = 1000; // maximal values in on request
Console.WriteLine("Reading values...");
do
{
intervalOffset = 0;
do
{
// Execute the request.
var result = await client.GetTransformedDataAsyncAsync(credentials, aggregation, variableKeys, variableOffset, variableCount, intervalOffset, intervalCount);
// Check the return code. "0;OK" is what we want.
if (!result.ReturnCode.StartsWith("0;"))
{
Console.WriteLine("Error on reading: {0}", result.ReturnCode);
}
// Cycle through the data and print it out.
if (result.Data != null)
{
foreach (VariableAggregate vag in result.Data)
{
Console.WriteLine("Variable: {0}", String.Concat(vag.Keys.Select((i) => { return String.Format("{0}={1}; ", i.Key, i.Value); })));
foreach (Interval value in vag.Data)
{
Console.Write(" {0}->{1} quality: {2:0.00} ", value.Begin, value.End, value.DataQuality);
if (value.Last != null)
{
Console.Write(" Last Value: {0} (stamp {1}, origin {2})", value.Last.Value, value.Last.Stamp, value.Last.Origin);
}
Console.WriteLine();
}
}
}
variableOffset = result.NextVariableOffset;
intervalOffset = result.NextIntervalOffset;
} while (intervalOffset != -1);
} while (variableOffset != -1);
}
});
Console.WriteLine("DB communication is running in background");
try
{
task.Wait();
}
catch (Exception exc)
{
Console.WriteLine("Exception: {0}", exc.ToString());
}
Console.WriteLine("DB communication finished");
Console.ReadLine();
}
}
}
=== Matlab ===
{{ cs:sw:01-mervis:mervisdb_matlab_api.png?direct |}}
== Supported functions: ==
^ Function ^ Description ^
| SaveData | Save one or multiple records from one or multiple variables. |
| GetData | Read one or multiple records from one or multiple variables. |
| GetDataUnmerged | Read one or multiple records from one or multiple variables. Allow more setting options than GetData function. |
| GetDataOrigin | Read data exactly as are stored in the database. |
| DeleteVariable | Delete entire variable or some certain interval. |
| GetRequiredTimestamp | Get time stamp depending on input options. |
== Toolbox ==
You need to import specialized toolbox to communicate with Mervis DB from Matlab:
* {{cs:sw:01-mervis:mervisdb_matlabtoolbox.zip|}}
* There is a help available for all toolbox functions. To display the help use: help MervisDatabse.supported_function_name
== Code example ==
% Instance of Mervis DB
mervisDB = MervisDatabase('username', 'password', 'dbUrl');
% Time interval definition
from = localMatlab2utcMatlab(datenum(2017, 1, 1)); %1.1.2017 in utc
to = localMatlab2utcMatlab(datenum(2017, 31, 1)); %31.1.2017 in utc
samplePeriod = 5*60; % 5 minutes
outputTimeZone = 'Local'; % possible 'Local' or 'Utc' parameter;
% Specifying varibles to download
varKeys1 = {'DPGuid', '338E2882-D72B-4D17-A241-73E8BC30F458';...
'StationName', 'XXXXXXX'}; % Keys definition of first variable
varKeys2 = {'DPGuid', 'CC80211D-3D29-4CC2-91A2-F69483D566B5';...
'StationName', 'YYYYYYY'}; % Keys definition of second variable
variable1 = MervisDbVariable(varKeys1); % Create MervisDbVariable object from defined keys
variable2 = MervisDbVariable(varKeys2);
arrayOfVarKeys = [variable1; variable2]; % array of MervisDbVariables objects
% help for function getData
help MervisDatabase.getData
% Download required data
[data, time] = mervisDB.getData(arrayOfVarKeys, from, to, samplePeriod, outputTimeZone);
% Get all variables with defined keys
allVaribales = mervisDB.getAllVars(varKeys1);
=== PHP ===
== Code example ==
SOAP_COMPRESSION_ACCEPT | SOAP_COMPRESSION_GZIP,
'trace' => 0,
'features' => SOAP_SINGLE_ELEMENT_ARRAYS
));
$soapClient->__setLocation('http://db.unipi.technology/dbaccess');
$credentials = array(
'Name' => 'XXXXXXX',
'Password' => 'YYYYYYY'
);
$utcTZ = new DateTimezone('UTC'); //all time realted values are expected in UTC
$utcFrom = new DateTime('-120minutes', $utcTZ); //
$utcTo = new DateTime('now', $utcTZ); //
$valOffset = 0;
$valCount = 20; //how many values should be returned in one request at most. Recommended value: 5000
$varOffset = 0;
$varCount = 4; //how many variable should be returned in on request at most
$variablesKey = array(
array(
array(
'IsKey' => true,
'Key' => 'DPGuid',
'Value' => '338E2882-D72B-4D17-A241-73E8BC30F458'
),
array(
'IsKey' => true,
'Key' => 'StationName',
'Value' => 'AAAAAAAAAA'
)
),
array(
array(
'IsKey' => true,
'Key' => 'DPGuid',
'Value' => 'CC80211D-3D29-4CC2-91A2-F69483D566B5'
),
array(
'IsKey' => true,
'Key' => 'StationName',
'Value' => 'BBBBBBB'
)
)
);
echo "Reading values from: {$utcFrom->format('c')} to: {$utcTo->format('c')}\n\n";
$counter = 0;
do {
$response = null;
$valOffset = 0;
do {
$response = $soapClient->GetData(array(
'credentials' => $credentials,
'variablesKey' => $variablesKey,
'utcFrom' => $utcFrom->format('Y-m-d\TH:i:s\Z'),
'utcTo' => $utcTo->format('Y-m-d\TH:i:s\Z'),
'valueOffset' => $valOffset,
'valueCount' => $valCount,
'variableCount' => $varCount,
'variableOffset' => $varOffset
));
$valOffset = $response->nextValueOffset;
$varOffset = $response->nextVariableOffset;
foreach ($response->GetDataResult->Mvr as $varArray) {
foreach ($varArray->Keys->KeyValuePair as $kvp) //the set of keys depends on the source of the data
{
if ($kvp->Key == 'DPName') {
echo "\n{$kvp->Value}\n";
$counter++;
}
}
if (isset($varArray->Vals->I)) {
foreach ($varArray->Vals->I as $val) {
echo " t: {$val->Ts} Interval: {$val->Ivl} {$valOffset} {$varOffset}\n";
}
} else {
echo "empty\n";
}
}
} while ($response->nextValueOffset != -1); //-1 - no more data available
} while ($response->nextVariableOffset != -1); //-1 - no more data available
}
catch (Exception $e) {
print_r($e);
}
?>