public void TestGetFileCount()
{
foreach (DbConnection connection in emptyConnections)
{
// todo: why is this 0?
Assert.AreEqual(0, DBFunctions.GetFileCount(connection, 1));
}
foreach (DbConnection connection in populousConnections)
{
Assert.AreEqual(0, DBFunctions.GetFileCount(connection, 0));
Assert.AreEqual(1, DBFunctions.GetFileCount(connection, 1));
// Add 3 more apsim files.
DataTable apsimFiles = TableFactory.CreateEmptyApsimFilesTable();
apsimFiles.Rows.Add(1, "wheat.apsimx", "~/wheat.apsimx", new DateTime(2020, 1, 1), 0, 1, "submitdetails", -1, null);
apsimFiles.Rows.Add(1, "wheat.apsimx", "~/wheat.apsimx", new DateTime(2020, 1, 1), 0, 1, "submitdetails", -1, null);
apsimFiles.Rows.Add(1, "wheat.apsimx", "~/wheat.apsimx", new DateTime(2020, 1, 1), 0, 1, "submitdetails", -1, null);
Utility.InsertDataIntoDatabase(connection, apsimFiles);
// At this point, we haven't added any P/O details for these files,
// so the file count should still be 1.
Assert.AreEqual(1, DBFunctions.GetFileCount(connection, 1));
DataTable poDetails = TableFactory.CreateEmptyPredictedObservedDetailsTable();
poDetails.Rows.Add(1, "PredictedObserved", "HarvestReport", "Observations", "xval", null, null, 0, 1, null);
poDetails.Rows.Add(1, "PredictedObserved", "HarvestReport", "Observations", "xval", null, null, 0, 1, null);
poDetails.Rows.Add(1, "PredictedObserved", "HarvestReport", "Observations", "xval", null, null, 0, 1, null);
Utility.InsertDataIntoDatabase(connection, poDetails);
Assert.AreEqual(0, DBFunctions.GetFileCount(connection, 0));
Assert.AreEqual(4, DBFunctions.GetFileCount(connection, 1));
}
}
public void TestGetPercentPassed()
{
foreach (DbConnection connection in populousConnections)
{
// For now, throw if pull request not found.
if (connection is SQLiteConnection)
{
Assert.Throws <Exception>(() => DBFunctions.GetPercentPassed(connection, 0));
}
else
{
Assert.Throws <System.Data.SqlClient.SqlException>(() => DBFunctions.GetPercentPassed(connection, 0));
}
// 0 out of 1 tables passed the tests.
Assert.AreEqual(0, DBFunctions.GetPercentPassed(connection, 1));
// Add 2 more P/O tables, both of which passed the tests.
DataTable poDetails = TableFactory.CreateEmptyPredictedObservedDetailsTable();
poDetails.Rows.Add(1, "DailyPredictedObserved", "Report", "DailyObs", "xval2", null, null, 100, 1, null);
poDetails.Rows.Add(1, "DailyPredictedObserved", "Report", "DailyObs", "xval2", null, null, 100, 1, null);
Utility.InsertDataIntoDatabase(connection, poDetails);
// 2 out of 3 tables passed the tests.
Assert.AreEqual(66, DBFunctions.GetPercentPassed(connection, 1));
// Delete the failed table (if only it were this easy irl).
using (DbCommand command = connection.CreateCommand("DELETE FROM PredictedObservedDetails WHERE PassedTests = 0;"))
command.ExecuteNonQuery();
// 2 out of 2 tables passed the tests.
Assert.AreEqual(100, DBFunctions.GetPercentPassed(connection, 1));
}
}
private ApsimFile GetSimpleApsimFile()
{
DataTable simsTable = TableFactory.CreateEmptyApsimSimulationsTable();
simsTable.Rows.Add(1, "sim1");
simsTable.Rows.Add(2, "sim2");
DataTable poData = new DataTable("PredictedObserved");
poData.Columns.Add("SimulationID", typeof(int));
poData.Columns.Add("Predicted.GrainWt", typeof(double));
poData.Columns.Add("Observed.GrainWt", typeof(double));
poData.Columns.Add("xval", typeof(double));
poData.Rows.Add(1, 0.9, 1.1, 0.1);
poData.Rows.Add(2, 0.5, 1.0, 0.1);
PredictedObservedDetails poDetails = new PredictedObservedDetails()
{
DatabaseTableName = "PredictedObserved",
PredictedTableName = "Report",
ObservedTableName = "HarvestReport",
FieldNameUsedForMatch = "xval",
FieldName2UsedForMatch = string.Empty,
FieldName3UsedForMatch = string.Empty,
Data = poData,
};
return(new ApsimFile()
{
ID = 1,
AcceptedPullRequestId = -1,
FileName = "wheat.apsimx",
FullFileName = "~/wheat.apsimx",
IsMerged = true,
PullRequestId = 1,
RunDate = new DateTime(2020, 1, 1),
StatsAccepted = true,
SubmitDetails = "submitdetails",
Simulations = simsTable,
PredictedObserved = new List <PredictedObservedDetails>()
{
poDetails
},
});
}
public void TestGetAcceptedFileCount()
{
foreach (DbConnection connection in emptyConnections)
{
Assert.Throws <Exception>(() => DBFunctions.GetAcceptedFileCount(connection));
}
foreach (DbConnection connection in populousConnections)
{
Assert.Throws <Exception>(() => DBFunctions.GetAcceptedFileCount(connection));
DataTable acceptStatsLogs = TableFactory.CreateEmptyAcceptStatsLogsTable();
acceptStatsLogs.Rows.Add(1, "foo", new DateTime(2020, 1, 2), "bar", "why not", 1, new DateTime(2020, 1, 2), 1, 3);
acceptStatsLogs.Rows.Add(1, "foo", new DateTime(2020, 1, 2), "baz", "reasons", 1, new DateTime(2020, 1, 2), 0, 4);
Utility.InsertDataIntoDatabase(connection, acceptStatsLogs);
Assert.AreEqual(4, DBFunctions.GetAcceptedFileCount(connection));
}
}
public void TestAddPOTestsData2()
{
foreach (DbConnection connection in populousConnections)
{
// We failed the r2 test, but passed the n test. r2 should not be considered in overall pass/fail status.
DataTable poTests = TableFactory.CreateEmptyPredictedObservedTestsTable();
//PredictedObservedDetailsID, Variable, Test, Accepted, Current, Difference, PassedTest, AcceptedPredictedObservedTestsID, IsImprovement, SortOrder, DifferencePercent
poTests.Rows.Add(1, "TestVar", "R2", 1, 2, 3, 0, null, 0, 0, 100);
poTests.Rows.Add(1, "TestVar", "n", 1, 2, 3, 1, null, 0, 0, 100);
DBFunctions.AddPredictedObservedTestsData(connection, null, 1, null, poTests);
poTests = new DataTable();
using (DbCommand command = connection.CreateCommand("SELECT * FROM PredictedObservedTests"))
using (DbDataReader reader = command.ExecuteReader())
poTests.Load(reader);
Assert.AreEqual(13, poTests.Rows.Count);
DataRow row = poTests.Rows[11];
Assert.AreEqual(1, row["PredictedObservedDetailsID"]);
Assert.AreEqual("TestVar", row["Variable"]);
Assert.AreEqual("R2", row["Test"]);
Assert.AreEqual(1, row["Accepted"]);
Assert.AreEqual(2, row["Current"]);
Assert.AreEqual(3, row["Difference"]);
Assert.AreEqual(false, row["PassedTest"]);
Assert.AreEqual(DBNull.Value, row["AcceptedPredictedObservedTestsID"]);
Assert.AreEqual(false, row["IsImprovement"]);
Assert.AreEqual(1, row["SortOrder"]);
Assert.AreEqual(3, row["Difference"]);
Assert.AreEqual(300, row["DifferencePercent"]);
DataTable poDetails = new DataTable();
using (DbCommand command = connection.CreateCommand("SELECT * FROM PredictedObservedDetails"))
using (DbDataReader reader = command.ExecuteReader())
poDetails.Load(reader);
Assert.AreEqual(1, poDetails.Rows.Count);
row = poDetails.Rows[0];
Assert.AreEqual(1, row["ID"]);
Assert.AreEqual(100, row["PassedTests"]); // This should be a percent!
Assert.AreEqual(1, row["HasTests"]);
}
}
private static void Populate(DbConnection connection)
{
DataTable simulations = TableFactory.CreateEmptySimulationsTable();
simulations.Rows.Add(1, "sim1", 1);
simulations.Rows.Add(1, "sim2", 2);
InsertDataIntoDatabase(connection, simulations);
DataTable apsimFiles = TableFactory.CreateEmptyApsimFilesTable();
apsimFiles.Rows.Add(1, "wheat.apsimx", "~/wheat.apsimx", new DateTime(2020, 1, 1), 0, 1, "submitdetails", -1, null);
InsertDataIntoDatabase(connection, apsimFiles);
DataTable poDetails = TableFactory.CreateEmptyPredictedObservedDetailsTable();
poDetails.Rows.Add(1, "PredictedObserved", "HarvestReport", "Observations", "xval", null, null, 0, 1, null);
InsertDataIntoDatabase(connection, poDetails);
DataTable poValues = TableFactory.CreateEmptyPredictedObservedValuesTable();
poValues.Rows.Add(1, 1, "xval", 0.1, null, null, null, null, "GrainWt", 0.9, 1.1);
poValues.Rows.Add(1, 2, "xval", 0.1, null, null, null, null, "GrainWt", 0.5, 1.0);
InsertDataIntoDatabase(connection, poValues);
DataTable poTests = TableFactory.CreateEmptyPredictedObservedTestsTable();
poTests.Rows.Add(1, "GrainWt", "n", null, 2, null, 0, null, null, 0, null);
poTests.Rows.Add(1, "GrainWt", "Slope", null, 4, null, 0, null, null, 1, null);
poTests.Rows.Add(1, "GrainWt", "Intercept", null, -3.5, null, 0, null, null, 1, null);
poTests.Rows.Add(1, "GrainWt", "SEslope", null, 0, null, 0, null, null, 1, null);
poTests.Rows.Add(1, "GrainWt", "SEintercept", null, 0, null, 0, null, null, 1, null);
poTests.Rows.Add(1, "GrainWt", "R2", null, 1, null, 0, null, null, 1, null);
poTests.Rows.Add(1, "GrainWt", "RMSE", null, 0.380789, null, 0, null, null, 1, null);
poTests.Rows.Add(1, "GrainWt", "NSE", null, -57, null, 0, null, null, 1, null);
poTests.Rows.Add(1, "GrainWt", "ME", null, -0.35, null, 0, null, null, 1, null);
poTests.Rows.Add(1, "GrainWt", "MAE", null, 0.35, null, 0, null, null, 1, null);
poTests.Rows.Add(1, "GrainWt", "RSR", null, 5.385165, null, 0, null, null, 1, null);
InsertDataIntoDatabase(connection, poTests);
}
