Differences

This shows you the differences between two versions of the page.

--- en:sw:01-mervis:03-mervis-db [2019/03/05 09:16]
jan_kozak
+++ en:sw:01-mervis:03-mervis-db [2024/04/03 10:11]
avsetula [The service is available in cloud and local variants]
@@ Line 1: / Line 1: @@
 ====== Mervis DB ======
-The Mervis DB is software for a long-term saving of data from the PLC. It is available as a cloud service and also as an on-premises installation.
+{{ 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 =====
+<html><span class="kbBlue-H5alt">Part of Mervis SCADA (cloud)</span></html>\\
+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|]].
+----
+<html><span class="kbBlue-H5alt">Only Mervis DB (cloud)</span></html>\\
+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.
+<WRAP center round box 95%>
+If you are only interested in the Mervis DB, please [[https://www.unipi.technology/contacts|contact our sales department]] for an individual offer.
+</WRAP>
+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|]]
+----
+<html><span class="kbBlue-H5alt">Local installation option</span></html>\\
+The Mervis DB service can also be run on a local server. For more information contact our [[[email protected]|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 [[[email protected]|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 |}}
+<html><span class="kbBlueText">Download:</span></html> \\
+  * {{cs:sw:01-mervis:mervisdb_getdata_example.zip|}}
+  * {{cs:sw:01-mervis:mervisdb_savedata_example.zip|}}
+<html><span class="kbBlueText">Code example:</span></html> \\
+<code csharp| MervisDB_GetData_Example.cs>
+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 "<security mode="Transport"/>" 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();
+  }
+ }
+}
+</code>
+<code csharp| MervisDB_SaveData_Example.cs>
+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 "<security mode="Transport"/>" 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<ValueRecord> records = new List<ValueRecord>();
+					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();
+		}
+	}
+}
+</code>
+<code csharp| MervisDb_GetTransformedData_Example.cs>
+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 "<security mode="Transport"/>" 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();
+		}
+	}
+}
+</code>
+=== 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: <code matlab>help MervisDatabse.supported_function_name</code>
+== Code example ==
+<code matlab| MervisDB_GetData_example.m>
+% 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);
+</code>
+=== PHP ===
+== Code example ==
+<code php| MervisDB_GetData_example.php>
+<?php
+/*
+Mervis DB PHP example - getdata
+*/
+try {
+  $soapClient = new SoapClient('./wsdl/singlewsdl.xml', array(
+	'compression' => 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);
+}
+?>
+</code>
-To view your data use the online Viewer: [[https://db.unipi.technology/viewer]]