public async Task <Blob> GetAsync(string sensorId, string fileName, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithFunction(SelectBlobByName);
builder.WithParameter("sensorid", sensorId, NpgsqlDbType.Varchar);
builder.WithParameter("filename", fileName, NpgsqlDbType.Text);
await using var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
if (!await reader.ReadAsync(ct).ConfigureAwait(false))
{
return(null);
}
return(new Blob {
ID = reader.GetInt64(0),
SensorID = reader.GetString(1),
FileName = reader.GetString(2),
Path = reader.GetString(3),
StorageType = (StorageType)reader.GetInt32(4),
Timestamp = reader.GetDateTime(5),
FileSize = reader.GetInt32(6)
});
}
public async Task AddActionAsync(Trigger trigger, TriggerAction action, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithParameter("triggerid", trigger.ID, NpgsqlDbType.Bigint);
builder.WithParameter("channel", (int)action.Channel, NpgsqlDbType.Integer);
builder.WithParameter("target", action.Target, NpgsqlDbType.Varchar);
builder.WithParameter("message", action.Message, NpgsqlDbType.Text);
builder.WithFunction(NetworkApi_CreateAction);
try {
var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
if (await reader.ReadAsync(ct).ConfigureAwait(false))
{
action.ID = reader.GetInt64(0);
}
await reader.DisposeAsync().ConfigureAwait(false);
} catch (PostgresException exception) {
if (exception.SqlState == PostgresErrorCodes.UniqueViolation)
{
throw new FormatException("Trigger action for this channel already exists!");
}
}
}
public async Task <IEnumerable <Blob> > GetAsync(string sensorId, int skip = -1, int limit = -1, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithFunction(SelectBlobsBySensorID);
builder.WithParameter("sensorid", sensorId, NpgsqlDbType.Varchar);
builder.WithParameter("offst", GetNullableInteger(skip), NpgsqlDbType.Integer);
builder.WithParameter("lim", GetNullableInteger(limit), NpgsqlDbType.Integer);
await using var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
var list = new List <Blob>();
while (await reader.ReadAsync(ct).ConfigureAwait(false))
{
var blob = new Blob {
ID = reader.GetInt64(0),
SensorID = reader.GetString(1),
FileName = reader.GetString(2),
Path = reader.GetString(3),
StorageType = (StorageType)reader.GetInt32(4),
Timestamp = reader.GetDateTime(5),
FileSize = reader.GetInt32(6)
};
list.Add(blob);
}
return(list);
}
public async Task DeleteAsync(ObjectId sensor, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithFunction(DeleteBlobBySensorID);
builder.WithParameter("sensorid", sensor.ToString(), NpgsqlDbType.Varchar);
var result = await builder.ExecuteAsync(ct).ConfigureAwait(false);
await result.DisposeAsync();
}
public async Task <bool> DeleteAsync(long id, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithFunction(DeleteBlobByID);
builder.WithParameter("id", id, NpgsqlDbType.Bigint);
await using var result = await builder.ExecuteAsync(ct).ConfigureAwait(false);
return(result.HasRows);
}
public async Task <bool> DeleteBySensorAsync(ObjectId sensor, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_netDb.Connection);
builder.WithFunction(DeleteSensorLinkBySensorID);
builder.WithParameter("sensorid", sensor.ToString(), NpgsqlDbType.Varchar);
await using var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
return(reader.HasRows);
}
public async Task <bool> DeleteAsync(SensorLink link, CancellationToken token = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_netDb.Connection);
builder.WithFunction(DeleteSensorLink);
builder.WithParameter("sensorid", link.SensorId, NpgsqlDbType.Varchar);
builder.WithParameter("userid", Guid.Parse(link.UserId), NpgsqlDbType.Uuid);
await using var reader = await builder.ExecuteAsync(token).ConfigureAwait(false);
return(reader.HasRows);
}
public async Task DeleteBySensorAsync(string sensorId, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithParameter("sensorid", sensorId, NpgsqlDbType.Varchar);
builder.WithFunction(NetworkApi_DeleteTriggersBySensorID);
var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
await reader.DisposeAsync().ConfigureAwait(false);
}
public async Task DeleteAsync(long id, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithParameter("id", id, NpgsqlDbType.Bigint);
builder.WithFunction(NetworkApi_DeleteTriggerByID);
var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
await reader.DisposeAsync().ConfigureAwait(false);
}
public async Task IncrementRequestCountAsync(string key, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithParameter("key", key, NpgsqlDbType.Text);
builder.WithFunction(NetworkApi_IncrementRequestCount);
var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
await reader.DisposeAsync().ConfigureAwait(false);
}
public async Task <IEnumerable <Trigger> > GetAsync(string sensorId, TriggerType type, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithParameter("sensorid", sensorId, NpgsqlDbType.Varchar);
builder.WithFunction(NetworkApi_SelectTriggersBySensorID);
await using var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
var result = await GetTriggersFromReaderAsync(reader, ct).ConfigureAwait(false);
return(result.Where(x => x.Type == type));
}
public async Task CreateAsync(AuditLog log, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithParameter("route", log.Route, NpgsqlDbType.Text);
builder.WithParameter("method", (int)log.Method, NpgsqlDbType.Integer);
builder.WithParameter("address", log.Address, NpgsqlDbType.Inet);
builder.WithParameter("author", log.AuthorId, NpgsqlDbType.Text);
builder.WithFunction(NetworkApi_CreateAuditLog);
var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
await reader.DisposeAsync().ConfigureAwait(false);
}
public async Task <long> CountAsync(SensateUser user, CancellationToken token = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Database.GetDbConnection());
builder.WithFunction(DataApi_Count);
builder.WithParameter("userid", Guid.Parse(user.Id), NpgsqlDbType.Uuid);
await using var reader = await builder.ExecuteAsync(token).ConfigureAwait(false);
if (!await reader.ReadAsync(token).ConfigureAwait(false))
{
return(0);
}
return(reader.GetInt64(0));
}
public async Task RemoveActionAsync(Trigger trigger, TriggerChannel channel, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithParameter("triggerid", trigger.ID, NpgsqlDbType.Bigint);
builder.WithParameter("channel", (int)channel, NpgsqlDbType.Integer);
builder.WithFunction(NetworkApi_DeleteTriggerAction);
await using var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
var result = await reader.ReadAsync(ct).ConfigureAwait(false);
if (!result)
{
throw new ArgumentException("Unable to remove non-existing trigger action.");
}
}
public async Task CreateAsync(Trigger trigger, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithParameter("sensorid", trigger.SensorID, NpgsqlDbType.Varchar);
builder.WithParameter("keyvalue", trigger.KeyValue, NpgsqlDbType.Varchar);
builder.WithParameter("loweredge", trigger.LowerEdge, NpgsqlDbType.Numeric);
builder.WithParameter("upperedge", trigger.UpperEdge, NpgsqlDbType.Numeric);
builder.WithParameter("formallanguage", trigger.FormalLanguage, NpgsqlDbType.Text);
builder.WithParameter("type", (int)trigger.Type, NpgsqlDbType.Integer);
builder.WithFunction(NetworkApi_CreateTrigger);
var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
if (await reader.ReadAsync(ct).ConfigureAwait(false))
{
trigger.ID = reader.GetInt64(0);
}
await reader.DisposeAsync().ConfigureAwait(false);
}
public async Task <IEnumerable <SensorLink> > GetByUserAsync(Guid userId, CancellationToken token = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_netDb.Connection);
builder.WithFunction(SelectLinkByUserID);
builder.WithParameter("userid", userId, NpgsqlDbType.Uuid);
await using var reader = await builder.ExecuteAsync(token).ConfigureAwait(false);
var list = new List <SensorLink>();
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
var link = new SensorLink {
SensorId = reader.GetString(0),
UserId = reader.GetGuid(1).ToString()
};
list.Add(link);
}
return(list);
}
public async Task <Trigger> GetAsync(long id, CancellationToken ct = default)
{
using var builder = StoredProcedureBuilder.Create(this.m_ctx.Connection);
builder.WithParameter("id", id, NpgsqlDbType.Bigint);
builder.WithFunction(NetworkApi_SelectTriggerByID);
await using var reader = await builder.ExecuteAsync(ct).ConfigureAwait(false);
if (await reader.ReadAsync(ct))
{
var trigger = new Trigger {
ID = id
};
await GetTriggerFromReaderAsync(trigger, reader, ct).ConfigureAwait(false);
return(trigger);
}
return(null);
}
