public async Task DataStream_Postgres_StreamQuery_Test()
{
var stream = GetValidStream();
var connector = new PostgresqlConnector(_logger);
AssertSuccessful(await connector.InitAsync(stream));
var deviceId = "DEV001";
for (var idx = 0; idx < 20; ++idx)
{
AssertSuccessful(await AddRecord(connector, stream, deviceId, idx + 300, idx + 200));
}
var filteredItems = new Dictionary <string, object>()
{
};
var query = "select time_bucket('1.5 minutes', timeStamp) as period, avg(int1)";
var result = await connector.GetTimeSeriesAnalyticsAsync(query, filteredItems, new Core.Models.UIMetaData.ListRequest()
{
PageSize = 50
});
Assert.IsTrue(result.Successful);
}
public async Task DataStream_Postgres_ExecSQL_Test()
{
var stream = GetValidStream();
var connector = new PostgresqlConnector(_logger);
AssertSuccessful(await connector.InitAsync(stream));
var deviceId = "DEV001";
for (var idx = 0; idx < 20; ++idx)
{
AssertSuccessful(await AddRecord(connector, stream, deviceId, idx + 300, idx + 200));
}
var filteredItems = new List <SQLParameter>()
{
new SQLParameter()
{
Name = "@start", Value = "2021-04-12"
}
};
var query = "select timeStamp, int1 from information where timeStamp > TO_TIMESTAMP(@start, 'yyyy-mm-dd')";
var result = await connector.ExecSQLAsync(query, filteredItems);
Assert.IsTrue(result.Successful);
}
public async Task DataStream_Postgres_GetFilteredData_Test()
{
var stream = GetValidStream();
var connector = new PostgresqlConnector(_logger);
AssertSuccessful(await connector.InitAsync(stream));
var deviceId = "DEV001";
const int rowcount = 20;
for (var idx = 0; idx < rowcount; ++idx)
{
AssertSuccessful(await AddRecord(connector, stream, deviceId, idx, idx + 100));
}
var filteredItems = new Dictionary <string, object>()
{
{ "int1", 5 },
};
var results = await connector.GetItemsAsync(filteredItems, new Core.Models.UIMetaData.ListRequest());
Assert.AreEqual(1, results.Model.Count());
Assert.AreEqual(5, results.Model.First()["int1"]);
}
public async Task DataStream_Postgres_AddRecord_Test()
{
var stream = GetValidStream();
var connector = new PostgresqlConnector(_logger);
AssertSuccessful(await connector.InitAsync(stream));
AssertSuccessful(await AddRecord(connector, stream, "DEV001", 100, 100));
}
public async Task DataStream_Postgres_InitTest_NoDB()
{
var stream = GetValidStream();
var connector = new PostgresqlConnector(_logger);
AssertSuccessful(await connector.InitAsync(stream));
}
private async Task <InvokeResult> AddRecord(PostgresqlConnector connector, DataStream stream, String deviceId, int int1, int int2, string dateStamp = "")
{
var record = GetRecord(stream, deviceId, String.IsNullOrEmpty(dateStamp) ? DateTime.Now.ToJSONString() : dateStamp,
new System.Collections.Generic.KeyValuePair <string, object>("int1", int1),
new System.Collections.Generic.KeyValuePair <string, object>("int2", int2),
new System.Collections.Generic.KeyValuePair <string, object>("datetime1", DateTime.Now.ToJSONString()),
new System.Collections.Generic.KeyValuePair <string, object>("dec1", 12.2),
new System.Collections.Generic.KeyValuePair <string, object>("str1", "value"),
new System.Collections.Generic.KeyValuePair <string, object>("local1", "22.34234,-82.2342342"),
new System.Collections.Generic.KeyValuePair <string, object>("pointindex1", 1000)
);
return(await connector.AddItemAsync(record));
}
public async Task DataStream_Postgres_InitTest_ExistingDB()
{
var stream = GetValidStream();
/* First time through it will create the DB */
var connector1 = new PostgresqlConnector(_logger);
AssertSuccessful(await connector1.InitAsync(stream));
/* this time it's already it shouldn't create the db and table but should still succeed */
var connector2 = new PostgresqlConnector(_logger);
//Now do an init again when the DB is created
AssertSuccessful(await connector2.InitAsync(stream));
}
public async Task DataStream_Postgres_GetData_Test()
{
var stream = GetValidStream();
var connector = new PostgresqlConnector(_logger);
AssertSuccessful(await connector.InitAsync(stream));
var deviceId = "DEV001";
const int rowcount = 20;
for (var idx = 0; idx < rowcount; ++idx)
{
AssertSuccessful(await AddRecord(connector, stream, deviceId, idx, idx + 100));
}
var results = await connector.GetItemsAsync(deviceId, new Core.Models.UIMetaData.ListRequest());
Assert.AreEqual(deviceId, results.Model.First()[stream.DeviceIdFieldName].ToString());
Assert.AreEqual(rowcount, results.Model.Count());
}
public async Task DataStream_Postgres_Stream_Request_Test()
{
var stream = GetValidStream();
var connector = new PostgresqlConnector(_logger);
AssertSuccessful(await connector.InitAsync(stream));
var deviceId = "DEV001";
var recordCount = 20;
for (var idx = 0; idx < 20; ++idx)
{
var dateStamp = DateTime.Now.AddSeconds((idx - recordCount) * 20);
AssertSuccessful(await AddRecord(connector, stream, deviceId, idx + 300, idx + 200, dateStamp.ToJSONString()));
}
var request = new TimeSeriesAnalyticsRequest()
{
Window = Windows.Minutes,
WindowSize = 1,
};
request.Fields.Add(new TimeSeriesAnalyticsRequestField()
{
Name = "int1", Operation = Operations.Average
});
var results = await connector.GetTimeSeriesAnalyticsAsync(request, new Core.Models.UIMetaData.ListRequest()
{
PageSize = 50
});
foreach (var result in results.Model)
{
Console.WriteLine(result);
}
}
public async Task DataStream_Postgres_UpdateItem_Test()
{
var stream = GetValidStream();
var connector = new PostgresqlConnector(_logger);
AssertSuccessful(await connector.InitAsync(stream));
var deviceId = "DEV001";
AssertSuccessful(await AddRecord(connector, stream, deviceId, 100, 356));
var filteredItems = new Dictionary <string, object>()
{
{ "deviceid", deviceId },
{ "int2", 356 },
};
var results = await connector.GetItemsAsync(filteredItems, new Core.Models.UIMetaData.ListRequest());
Assert.AreEqual(1, results.Model.Count());
Assert.AreEqual(100, results.Model.First()["int1"]);
var updatedItems = new Dictionary <string, object>()
{
{ "int1", 53 },
{ "dec1", 12.5 },
{ "str1", 12.5 },
};
await connector.UpdateItem(updatedItems, filteredItems);
results = await connector.GetItemsAsync(filteredItems, new Core.Models.UIMetaData.ListRequest());
Assert.AreEqual(1, results.Model.Count());
Assert.AreEqual(53, results.Model.First()["int1"]);
}
