public long?GetRneuronIdFromInputs(KeyValuePair <string, string>[] inputs)
{
long?retVal = null;
List <SqlParameter> parameters = new List <SqlParameter>();
string query = $@"
SELECT DISTINCT
ivr.Rneuron_ID
FROM Input_Values_Rneuron ivr
INNER JOIN Inputs i ON ivr.Input_ID = i.ID
WHERE ";
int cnt = 0;
foreach (var input in inputs)
{
SqlParameter input_name = new SqlParameter($"p{cnt++}", input.Key);
SqlParameter input_val = new SqlParameter($"p{cnt++}", input.Value);
query += $"(i.Name = @{input_name.ParameterName} AND ivr.Value = @{input_val.ParameterName}) AND\n";
parameters.Add(input_name);
parameters.Add(input_val);
}
query = query.Substring(0, query.LastIndexOf("AND"));
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = db.Database.SqlQuery <long?>(query, parameters.ToArray()).FirstOrDefault();
}
return(retVal);
}
public long?GetSolutionIdFromOutputs(KeyValuePair <string, string>[] outputs)
{
long?retVal = null;
List <SqlParameter> parameters = new List <SqlParameter>();
string query = $@"
SELECT DISTINCT
ovs.Solution_ID
FROM Output_Values_Solution ovs
INNER JOIN Outputs o ON ovs.Output_ID = o.ID
WHERE ";
int cnt = 0;
foreach (var output in outputs)
{
SqlParameter output_name = new SqlParameter($"p{cnt++}", output.Key);
SqlParameter output_val = new SqlParameter($"p{cnt++}", output.Value);
query += $"(o.Name = @{output_name.ParameterName} AND ovs.Value = @{output_val.ParameterName}) AND\n";
parameters.Add(output_name);
parameters.Add(output_val);
}
query = query.Substring(0, query.LastIndexOf("AND"));
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = db.Database.SqlQuery <long?>(query, parameters.ToArray()).FirstOrDefault();
}
return(retVal);
}
public IEnumerable <RlmLearnedSession> GetSessionDetails(params long[] sessionIds)
{
IEnumerable <RlmLearnedSession> retVal = null;
var sqlParams = new List <SqlParameter>();
for (int i = 0; i < sessionIds.Length; i++)
{
sqlParams.Add(new SqlParameter($"id_{i}", sessionIds[i]));
}
string query = $@"
WITH TempSessions AS
(SELECT
ID [SessionId],
SessionScore [Score],
DATEDIFF(MILLISECOND, DateTimeStart, DateTimeStop) [Time],
ROW_NUMBER() OVER(ORDER BY DateTimeStart) As SessionNum
FROM [Sessions])
SELECT * FROM TempSessions
WHERE SessionId in ({string.Join(",", sqlParams.Select(a => $"@{a.ParameterName}").ToArray())})";
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = db.Database.SqlQuery <RlmLearnedSession>(query, sqlParams.ToArray()).ToList();
}
return(retVal);
}
public RlmCaseIOHistory GetCaseIOHistory(long caseId, long rneuronId, long solutionId)
{
RlmCaseIOHistory retVal = new RlmCaseIOHistory();
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
string queryIn = $"SELECT Inputs.[ID] AS Id, Inputs.[Name], Input_Values_Rneuron.[Value] FROM Input_Values_Rneuron INNER JOIN Inputs ON Inputs.ID = Input_Values_Rneuron.Input_ID WHERE Rneuron_ID = {rneuronId};";
string queryOut = $"SELECT Outputs.[ID] AS Id, Outputs.[Name], Output_Values_Solution.[Value] FROM Output_Values_Solution INNER JOIN Outputs ON Outputs.ID = Output_Values_Solution.Output_ID WHERE Solution_ID = {solutionId};";
var resultsIn = db.Database.SqlQuery <RlmCaseInputOutput>(queryIn);
var resultsOut = db.Database.SqlQuery <RlmCaseInputOutput>(queryOut);
retVal.Id = caseId;
retVal.Inputs = resultsIn.Select(a =>
{
return(a);
}).ToList();
retVal.Outputs = resultsOut.Select(a =>
{
return(a);
}).ToList();
}
return(retVal);
}
public static int GetNumSessionSinceBestScore(RlmDbEntities db, long rnetworkId)
{
int retVal = 0;
string sql = @"
select count(*) as [NumSessionSinceBestScore]
from
(
select
[ID],
[SessionScore],
ROW_NUMBER() OVER( ORDER BY [ID] DESC) as [Num]
from [Sessions]
where ID > (select top 1 [ID] from [Sessions] order by [SessionScore] desc, [ID] desc) and [Rnetwork_ID] = @p0
) a";
int?result = db.Database.SqlQuery <int?>(sql, rnetworkId).FirstOrDefault();
if (result.HasValue)
{
retVal = result.Value;
}
return(retVal);
}
public static void ResetTrainingData(RlmDbEntities db, long rnetworkId)
{
string sql = $@"
DELETE FROM [Cases] WHERE [Session_ID] IN (SELECT [ID] FROM [Sessions] WHERE [Rnetwork_ID] = @p0);
DELETE FROM [Sessions] WHERE [Rnetwork_ID] = @p0;";
//db.Database.ExecuteSqlCommand(sql, rnetworkId);
}
private static void DropDB(RlmDbEntities ctx, string databaseName)
{
if (ctx != null && !string.IsNullOrEmpty(databaseName))
{
ctx.DropDB(databaseName);
System.Diagnostics.Debug.WriteLine("Db dropped...");
}
}
/// <summary>
/// default contstructor, creates "RyskampLearningMachine" database
/// </summary>
/// <param name="persistData">Allows you to turn on/off the data persistence feature of the RLM. Turned on by default.</param>
public RlmNetwork(bool persistData = true)
{
PersistData = persistData;
if (PersistData)
{
DatabaseName = RlmDbEntities.DetermineDbName();
}
Initialize();
}
private static void DropDB(RlmDbEntities ctx, string databaseName)
{
//if (ctx != null && !string.IsNullOrEmpty(databaseName))
//{
// ctx.DropDB(databaseName);
// System.Diagnostics.Debug.WriteLine("Db dropped...");
//}
// todo migrate to ef core
}
public IEnumerable <RlmLearnedSessionDetails> GetSessionIODetails(params long[] sessionIds)
{
var retVal = new List <RlmLearnedSessionDetails>();
var sqlParams = new List <SqlParameter>();
for (int i = 0; i < sessionIds.Length; i++)
{
sqlParams.Add(new SqlParameter($"id_{i}", sessionIds[i]));
}
string query = $@"
SELECT
i.ID,
i.Name,
ivr.Value,
CAST(1 AS BIT) [IsInput],
c.ID [CaseId],
c.CycleScore,
c.Session_ID [SessionId]
FROM Cases c
INNER JOIN Input_Values_Rneuron ivr on c.Rneuron_ID = ivr.Rneuron_ID
INNER JOIN Inputs i on ivr.Input_ID = ivr.Input_ID
WHERE c.Session_ID in ({string.Join(",", sqlParams.Select(a => $"@{a.ParameterName}").ToArray())})
UNION
SELECT
o.ID,
o.Name,
ovs.Value,
CAST(0 AS BIT) [IsInput],
c.ID [CaseId],
c.CycleScore,
c.Session_ID [SessionId]
FROM Cases c
LEFT JOIN Output_Values_Solution ovs on c.Solution_ID = ovs.Solution_ID
LEFT JOIN Outputs o on ovs.Output_ID = ovs.Output_ID
WHERE c.Session_ID in ({string.Join(",", sqlParams.Select(a => $"@{a.ParameterName}").ToArray())})";
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
var results = db.Database.SqlQuery <RlmIODetails>(query, sqlParams.ToArray()).ToList();
if (results != null)
{
var sessionIODetails = results.GroupBy(a => a.SessionId);
foreach (var item in sessionIODetails)
{
var learnedSess = new RlmLearnedSessionDetails();
learnedSess.SessionId = item.Key;
learnedSess.Inputs = item.Where(a => a.IsInput).ToList();
learnedSess.Outputs = item.Where(a => !a.IsInput).ToList();
retVal.Add(learnedSess);
}
}
}
return(retVal);
}
public static void DropDB(string databaseName)
{
using (RlmDbEntities ctx = new RlmDbEntities(RlmDbEntities.MASTER_DB))
{
if (ctx.DBExists(databaseName))
{
DropDB(ctx, databaseName);
}
}
}
public static bool Exists(string databaseName)
{
bool retVal = false;
using (RlmDbEntities ctx = new RlmDbEntities(RlmDbEntities.MASTER_DB))
{
retVal = ctx.DBExists(databaseName);
}
return(retVal);
}
public int GetTotalSimulationInSeconds()
{
int retVal = 0;
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = RlmUtils.GetTotalSimulationInSeconds(db, CurrentNetworkID);
}
return(retVal);
}
public IEnumerable <Session> GetSessions(int?skip = null, int?take = null, bool descending = false)
{
IEnumerable <Session> retVal = null;
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = RlmUtils.GetSessions(db, CurrentNetworkID, skip, take, descending);
}
return(retVal);
}
public IEnumerable <RlmSessionSummary> GetSessionSummary(int groupBy, bool descending = false)
{
IEnumerable <RlmSessionSummary> retVal = null;
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = RlmUtils.GetSessionSummary(db, CurrentNetworkID, groupBy, descending);
}
return(retVal);
}
public int GetCasesCount(long sessionId)
{
int retVal = 0;
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = db.Cases.Where(a => a.Session.ID == sessionId).Count();
}
return(retVal);
}
public IEnumerable <Case> GetCases(long sessionId, int?skip = null, int?take = null)
{
IEnumerable <Case> retVal = null;
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = RlmUtils.GetCases(db, sessionId, skip, take);
}
return(retVal);
}
public int GetSessionCount()
{
int retVal = 0;
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = db.Sessions.Where(a => a.Rnetwork.ID == CurrentNetworkID).Count();
}
return(retVal);
}
public RlmStats GetStatistics()
{
RlmStats retVal = null;
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = RlmUtils.GetRNetworkStatistics(db, CurrentNetworkID);
}
return(retVal);
}
public long?GetRneuronIdFromInputs(KeyValuePair <string, string>[] inputs)
{
long?retVal = null;
List <SqlParameter> parameters = new List <SqlParameter>();
StringBuilder query = new StringBuilder();
StringBuilder joins = new StringBuilder();
StringBuilder where = new StringBuilder();
query.AppendLine($@"
select distinct
r.ID
from Rneurons r");
int cnt = 0;
foreach (var input in inputs)
{
SqlParameter input_name = new SqlParameter($"p{cnt++}", input.Key);
SqlParameter input_val = new SqlParameter($"p{cnt++}", input.Value);
//query += $"(i.Name = @{input_name.ParameterName} AND ivr.Value = @{input_val.ParameterName}) AND\n";
string alias = $"i{cnt}";
joins.AppendLine($"inner join (select ivr.Rneuron_ID, i.Name, ivr.Value from Input_Values_Rneuron ivr inner join Inputs i on ivr.Input_ID = i.ID) {alias} on r.ID = {alias}.Rneuron_ID");
where.AppendLine($"({alias}.Name = @{input_name.ParameterName} AND {alias}.Value = @{input_val.ParameterName}) AND");
parameters.Add(input_name);
parameters.Add(input_val);
}
var index = where.ToString().LastIndexOf("AND");
if (index > 0)
{
where.Remove(index, 3);
}
query.AppendLine(joins.ToString());
query.AppendLine("where");
query.AppendLine(where.ToString());
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = db.Database.SqlQuery <long?>(query.ToString(), parameters.ToArray()).FirstOrDefault();
}
return(retVal);
}
public double GetVariance(int top)
{
double retVal = 0D;
if (SessionCount > 0)
{
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
RlmUtils.GetVariance(db, CurrentNetworkID, top);
}
}
return(retVal);
}
public static IEnumerable <Case> GetCases(RlmDbEntities db, long sessionId, int?skip = null, int?take = null)
{
IEnumerable <Case> retVal = db.Cases
.Include(a => a.Rneuron.Input_Values_Reneurons.Select(b => b.Input))
.Include(a => a.Solution.Output_Values_Solutions.Select(b => b.Output))
.Where(a => a.Session.ID == sessionId)
.OrderBy(a => a.ID);
if (skip.HasValue && take.HasValue)
{
retVal = retVal.Skip(skip.Value)
.Take(take.Value);
}
return(retVal.ToList());
}
public void ResetNetwork()
{
if (CurrentNetworkID < 0)
{
throw new Exception("Cannot reset a non existing network. You must create or load a network first.");
}
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
RlmUtils.ResetTrainingData(db, CurrentNetworkID);
}
// recreates network from scratch
// TODO don't know if we need this later on when resetting the network. Just creating new one since we are dropping entire DB
//NewNetwork(CurrentNetworkName, Inputs.ToList(), Outputs.ToList());
}
public long?GetNextPreviousLearnedCaseId(long caseId, bool next = false)
{
long?retVal = null;
string tempTablePostfix = Guid.NewGuid().ToString("N");
var caseParam = new SqlParameter("case", caseId);
string query = $@"
DECLARE @temp_table_{tempTablePostfix} TABLE(ID BIGINT, Score FLOAT)
INSERT INTO @temp_table_{tempTablePostfix}
SELECT
c.ID,
s.SessionScore as Score
FROM
(
SELECT
MIN(sub.Id) as ID,
sub.Score
FROM (
SELECT
c.ID as Id,
MAX(s.SessionScore) OVER(ORDER BY c.ID ASC) as Score
FROM Cases c
INNER JOIN [Sessions] s ON c.Session_ID = s.ID
WHERE c.Rneuron_ID = (select Rneuron_ID from Cases where ID = @{caseParam.ParameterName}) AND c.Solution_ID = (select Solution_ID from Cases where ID = @{caseParam.ParameterName})
) sub
GROUP BY sub.Score
) sub
INNER JOIN [Cases] c ON sub.Id = c.ID
INNER JOIN [Sessions] s ON c.Session_ID = s.ID
SELECT
s2.ID AS PreviousCaseId
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Score DESC) ord FROM @temp_table_{tempTablePostfix}) s1
LEFT JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY Score DESC) ord FROM @temp_table_{tempTablePostfix}) s2 on s1.ord = s2.ord {(next ? "+" : "-")} 1
WHERE s1.ID = @{caseParam.ParameterName}";
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = db.Database.SqlQuery <long?>(query, caseParam).FirstOrDefault();
}
return(retVal);
}
public long?GetNextPreviousLearnedSessionId(long sessionId, bool next = false)
{
long?retVal = null;
string tempTablePostfix = Guid.NewGuid().ToString("N");
var sessParam = new SqlParameter("sess", sessionId);
string query = $@"
DECLARE @temp_table_{tempTablePostfix} TABLE(ID BIGINT, Score FLOAT);
WITH cte (ID, [Time], Score)
AS
(
SELECT
ID,
[Time],
MAX(SessionScore) OVER (ORDER BY [Time]) Score
FROM (
SELECT
ID,
SUM(DATEDIFF(ms, DateTimeStart, DateTimeStop)) OVER (ORDER BY DateTimeStart) [Time],
SessionScore
FROM [Sessions]
) s
)
INSERT INTO @temp_table_{tempTablePostfix}
SELECT
MIN(ID) ID,
Score
FROM cte
GROUP BY Score
SELECT
s2.ID AS PreviousSessionId
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Score DESC) ord FROM @temp_table_{tempTablePostfix}) s1
LEFT JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY Score DESC) ord FROM @temp_table_{tempTablePostfix}) s2 on s1.ord = s2.ord {(next ? "+" : "-")} 1
WHERE s1.ID = @{sessParam.ParameterName}";
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
retVal = db.Database.SqlQuery <long?>(query, sessParam).FirstOrDefault();
}
return(retVal);
}
public static double GetVariance(RlmDbEntities db, long rnetworkId, int top)
{
double retVal = 0;
var sessions = db.Sessions
.Where(a => a.Rnetwork.ID == rnetworkId)
.OrderByDescending(a => a.DateTimeStop)
.Select(a => a.SessionScore)
.Take(top);
double max = sessions.Max();
double min = sessions.Min();
double diff = sessions.Max() - sessions.Min();
retVal = (diff <= 0) ? 0 : (diff / max);
return(retVal);
}
/// <summary>
/// Loads the first network in the database, sorted by ID
/// </summary>
/// <returns>Returns true if network is successfully loaded</returns>
public bool LoadNetwork()
{
string networkName = null;
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
networkName = db.Rnetworks.Select(a => a.Name).FirstOrDefault();
}
if (networkName == null)
{
return(false);
}
else
{
return(LoadNetwork(networkName));
}
}
public static void BackupDB(string databaseName)
{
using (RlmDbEntities ctx = new RlmDbEntities(RlmDbEntities.MASTER_DB))
{
if (ctx.FileBackupExists(databaseName))
{
ctx.DeleteFileBackup(databaseName);
System.Diagnostics.Debug.WriteLine("Deleted file backup...");
}
if (ctx.DBExists(databaseName))
{
ctx.BackupDB(databaseName);
System.Diagnostics.Debug.WriteLine("Db backed up...");
DropDB(ctx, databaseName);
}
}
}
public IEnumerable <RlmIODetails>[] GetCaseIODetails(long caseId)
{
const int INPUT_INDEX = 0;
const int OUTPUT_INDEX = 1;
var retVal = new IEnumerable <RlmIODetails> [2];
var caseParam = new SqlParameter("caseId", caseId);
string query = $@"
SELECT
i.ID,
i.Name,
ivr.Value,
CAST(1 AS BIT) [IsInput]
FROM Cases c
INNER JOIN Input_Values_Rneuron ivr on c.Rneuron_ID = ivr.Rneuron_ID
INNER JOIN Inputs i on ivr.Input_ID = ivr.Input_ID
WHERE c.ID = @{caseParam.ParameterName}
UNION
SELECT
o.ID,
o.Name,
ovs.Value,
CAST(0 AS BIT) [IsInput]
FROM Cases c
INNER JOIN Output_Values_Solution ovs on c.Solution_ID = ovs.Solution_ID
INNER JOIN Outputs o on ovs.Output_ID = ovs.Output_ID
WHERE c.ID = @{caseParam.ParameterName}";
using (RlmDbEntities db = new RlmDbEntities(DatabaseName))
{
var results = db.Database.SqlQuery <RlmIODetails>(query, caseParam).ToList();
if (results != null)
{
retVal[INPUT_INDEX] = results.Where(a => a.IsInput).ToList();
retVal[OUTPUT_INDEX] = results.Where(a => !a.IsInput).ToList();
}
}
return(retVal);
}
public static int GetTotalSimulationInSeconds(RlmDbEntities db, long rnetworkId)
{
int retVal = 0;
//var total = db.Sessions
// .Where(a => a.Rnetwork.ID == rnetworkId && (!a.Hidden || a.SessionScore != Int32.MinValue))
// .Select(a => (a.DateTimeStop - a.DateTimeStart))
// .Sum(a => a.TotalSeconds);
string sql = "select datediff(second,'1900-01-01 00:00:00.0000000', convert(datetime,sum(convert(float,DateTimeStop)-Convert(float,DateTimeStart)))) from [Sessions] where Rnetwork_ID = @p0 and hidden = 0";
var result = db.Database.SqlQuery <int?>(sql, rnetworkId);
var resultVal = result.FirstOrDefault();
if (resultVal.HasValue)
{
retVal = resultVal.Value;
}
return(retVal);
}
