public bool QueryDashboard(string[] dashKeys, string perspective, out DashboardData dashboard)
{
if (dashKeys?.Length > 0)
{
using (var connection = Sqlite.ConnectDatabase(_filename))
{
try
{
int dashboardId = GetDashboardId(dashKeys, perspective, connection);
if (dashboardId > 0)
{
var dashboardName = GetDashboardName(dashboardId, connection);
dashboard = new DashboardData(dashboardId, dashboardName);
return(true);
}
}
catch (Exception e)
{
_logger.LogError(e, $"QueryDashboard query failed for nodeid(s): '{ToTabString(dashKeys)}' and perspective: '{perspective}'");
}
}
dashboard = null;
return(false);
}
else
{
var msg = "Dashboard query failed: at least one NodeId must be supplied";
_logger.LogError(msg);
throw new ArgumentException(msg);
}
}
private void CreateIfNotExists(string filename)
{
if (!File.Exists(filename))
{
_logger.LogDebug($"Create db starting: {filename}");
using (var connection = Sqlite.CreateDatabase(filename, new[] { createPerspectivesDb })) { }
using (var connection = Sqlite.CreateDatabase(filename, new[] { createDashboardDb })) { }
using (var connection = Sqlite.CreateDatabase(filename, new[] { createRelationsDb })) { }
using (var connection = Sqlite.ConnectDatabase(filename))
{
var sql = "INSERT INTO Perspectives (Name) VALUES ('Operator')";
try
{
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
_logger.LogError(e, "Inserting default perspective failed");
return;
}
}
_logger.LogDebug("Create db success");
}
}
public UaResult RemoveDashboardMapping(string[] nodeIds, string perspective)
{
if (nodeIds?.Length > 0 && !string.IsNullOrEmpty(perspective))
{
using (var connection = Sqlite.ConnectDatabase(_filename))
{
try
{
int dashboardId = GetDashboardId(nodeIds, perspective, connection);
if (dashboardId > 0)
{
var relationsCount = GetDashboardIdCount(dashboardId, connection);
if (relationsCount == nodeIds.Length)
{
RemoveMappingForDashboard(dashboardId, connection);
}
}
}
catch (Exception e)
{
var msg = $"RemoveMapping query failed for nodeid(s): '{ToTabString(nodeIds)}' and perspective: '{perspective}'";
_logger.LogError(e, msg);
return(new UaResult()
{
success = false, error = msg
});
}
}
}
return(new UaResult());
}
private UaResult <int> AddDashboard(string dashboard, string perspective)
{
using (var connection = Sqlite.ConnectDatabase(_filename))
{
var sql = "INSERT INTO Dashboards (Dashboard, PerspectiveId) VALUES ($dashboard, (SELECT Id FROM Perspectives WHERE Perspectives.Name == $perspective))";
try
{
using (DbCommand cmd = connection.CreateCommand())
{
DbParameter parDashboard = new SQLiteParameter("$dashboard", dashboard);
DbParameter parPerspective = new SQLiteParameter("$perspective", perspective);
cmd.Parameters.Add(parDashboard);
cmd.Parameters.Add(parPerspective);
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
var numRowsAffected = cmd.ExecuteNonQuery();
if (numRowsAffected > 0)
{
cmd.Parameters.Clear();
cmd.CommandText = _lastRowIdQuery;
using (var rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
var pk = rdr.GetInt32(0);
return(new UaResult <int>()
{
Value = pk, Success = true, Error = string.Empty
});
}
}
}
return(new UaResult <int>()
{
Value = 0, Success = false, Error = "Adding dashboard failed"
});
}
}
catch (Exception e)
{
var msg = $"AddDashboard failed for dashboard = '{dashboard}' and perspective = '{perspective}': '{e.Message}'";
_logger.LogError(e, msg);
return(new UaResult <int>()
{
Value = 0, Success = false, Error = msg
});
}
}
}
public void RemoveMapping(string nodeId, string perspective)
{
using (var connection = Sqlite.ConnectDatabase(_filename))
{
try
{
int dashboardId = GetDashboardId(new[] { nodeId }, perspective, connection);
if (dashboardId > 0)
{
RemoveMappingForDashboard(dashboardId, connection);
}
}
catch (Exception e)
{
_logger.LogError(e, $"RemoveIfExists query failed for nodeid: '{nodeId}' and perspective: '{perspective}'");
}
}
}
public UaResult AddDashboardMapping(string[] nodeIds, string dashboard, string perspective)
{
if (nodeIds?.Length > 0)
{
RemoveDashboardMapping(nodeIds, perspective);
var addRes = AddDashboard(dashboard, perspective);
int dashboardId;
if (addRes.Success)
{
dashboardId = addRes.Value;
}
else
{
return new UaResult()
{
success = false, error = addRes.Error
}
};
for (int i = 0; i < nodeIds.Length; i++)
{
_logger.LogDebug($"AddDashboardMapping for nodeid: '{nodeIds[i]}' to dashboard '{dashboard}'");
using (var connection = Sqlite.ConnectDatabase(_filename))
{
var sql = "INSERT INTO DashboardRelations (NodeId, DashboardId) "
+ "VALUES ($nodeId, " + dashboardId + ")";
_logger.LogDebug("AddDashboardMapping sql: " + sql);
try
{
using (DbCommand cmd = connection.CreateCommand())
{
DbParameter parNodeId = new SQLiteParameter("$nodeId", nodeIds[i]);
cmd.Parameters.Add(parNodeId);
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
var numRowsAffected = cmd.ExecuteNonQuery();
if (numRowsAffected == 0)
{
var msg = $"AddDashboardMapping failed for nodeid = '{nodeIds[i]}', dashboard = '{dashboard}', perspective = '{perspective}'";
_logger.LogError(msg);
return(new UaResult()
{
success = false, error = msg
});
}
}
}
catch (Exception e)
{
var msg = $"AddDashboardMapping failed for nodeid = '{nodeIds[i]}', dashboard = '{dashboard}', perspective = '{perspective}'";
_logger.LogError(e, msg);
}
}
}
return(new UaResult()
{
success = true
});
}
return(new UaResult()
{
success = false, error = "No NodeIds specified"
});
}