예제 #1
0
        public void TestComplicatedInsert()
        {
            foreach (DbConnection conn in populousConnections)
            {
                // Get the accepted data for the HarvestObsPred table.
                // This was generated by querying the production DB
                // and saving the result to a csv file.
                string    json    = ReflectionUtilities.GetResourceAsString("APSIM.PerformanceTests.Tests.Resources.accepted-wheat.json");
                DataTable poTests = JsonConvert.DeserializeObject <DataTable>(json);
                poTests.TableName = "PredictedObservedTests";
                Utility.InsertDataIntoDatabase(conn, poTests);

                // Read the ApsimFile object from json. This json file was generated
                // by running the collector on Wheat.apsimx and serializing the result
                // to json. Note that the DB is only partially full - not all sims had been run.
                json = ReflectionUtilities.GetResourceAsString("APSIM.PerformanceTests.Tests.Resources.WheatHarvestObsPred.json");
                ApsimFile file = JsonConvert.DeserializeObject <ApsimFile>(json);

                ApsimFilesController.InsertApsimFile(conn, file, out string error, out int apsimID);
                using (DbCommand command = conn.CreateCommand("SELECT * FROM PredictedObservedTests"))
                {
                    using (DbDataReader reader = command.ExecuteReader())
                    {
                        DataTable table = new DataTable();
                        table.Load(reader);

                        Assert.AreEqual(1649, table.Rows.Count);
                    }
                }
            }
        }
예제 #2
0
        public void EnsureOldPullRequestDataIsDeleted()
        {
            foreach (DbConnection connection in populousConnections)
            {
                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM ApsimFiles"))
                    Assert.AreEqual(1, command.ExecuteScalar());

                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedDetails"))
                    Assert.AreEqual(1, command.ExecuteScalar());

                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedTests"))
                    Assert.AreEqual(11, command.ExecuteScalar());

                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedValues"))
                    Assert.AreEqual(2, command.ExecuteScalar());

                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM Simulations"))
                    Assert.AreEqual(2, command.ExecuteScalar());

                // Now insert the new apsimfile. Number of rows in all tables should be unchanged.
                // The only difference is the run date.
                ApsimFile file = GetSimpleApsimFile();
                file.RunDate = file.RunDate.AddMinutes(1);
                ApsimFilesController.InsertApsimFile(connection, file, out _, out _);

                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM ApsimFiles"))
                    Assert.AreEqual(1, command.ExecuteScalar());

                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedDetails"))
                    Assert.AreEqual(1, command.ExecuteScalar());

                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedTests"))
                    Assert.AreEqual(11, command.ExecuteScalar());

                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedValues"))
                    Assert.AreEqual(2, command.ExecuteScalar());

                using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM Simulations"))
                    Assert.AreEqual(2, command.ExecuteScalar());

                using (DbCommand command = connection.CreateCommand(Utilities.Limit(connection, "SELECT RunDate FROM ApsimFiles", 1)))
                {
                    object actual = command.ExecuteScalar();
                    if (connection is SQLiteConnection)
                    {
                        Assert.AreEqual("2020-01-01 00:01:00", actual);
                    }
                    else
                    {
                        Assert.AreEqual(new DateTime(2020, 1, 1).AddMinutes(1), actual);
                    }
                }
            }
        }
예제 #3
0
        public void TestAcceptedStatsIDAfterInsert()
        {
            foreach (DbConnection connection in populousConnections)
            {
                // Let's pretend that we've accepted the existing pull request's stats.
                using (DbCommand command = connection.CreateCommand("UPDATE ApsimFiles SET StatsAccepted = 1"))
                    command.ExecuteNonQuery();

                // Now insert another pull request - its accepted stats ID should
                // be the ID of this pull request (1).
                ApsimFile file = GetSimpleApsimFile();
                file.PullRequestId = 2;
                ApsimFilesController.InsertApsimFile(connection, file, out _, out _);

                string sql = "SELECT AcceptedPullRequestId FROM ApsimFiles ORDER BY ID DESC";
                sql = Utilities.Limit(connection, sql, 1);
                using (DbCommand command = connection.CreateCommand(sql))
                    Assert.AreEqual(1, command.ExecuteScalar());
            }
        }
예제 #4
0
        public void TestPostApsimFileWithLongSimulationID()
        {
            ApsimFile file = GetSimpleApsimFile();

            DataTable poData = new DataTable("PredictedObserved");

            poData.Columns.Add("SimulationID", typeof(long));
            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);
            file.PredictedObserved.ElementAt(0).Data = poData;

            foreach (DbConnection connection in populousConnections)
            {
                ApsimFilesController.InsertApsimFile(connection, file, out _, out _);
                using (DbCommand command = connection.CreateCommand(Utilities.Limit(connection, "SELECT SimulationsID FROM PredictedObservedValues", 1)))
                    Assert.AreEqual(1, command.ExecuteScalar());
            }
        }
예제 #5
0
        public void TestPostSimpleApsimFile()
        {
            foreach (DbConnection connection in emptyConnections)
            {
                // Create a simple apsim file.
                ApsimFile file = GetSimpleApsimFile();
                PredictedObservedDetails poDetails = file.PredictedObserved.ElementAt(0);

                // Insert it into the database.
                ApsimFilesController.InsertApsimFile(connection, file, out string err, out int id);

                // Verify results.
                DataTable result = new DataTable();

                // Check ApsimFiles table.
                using (DbCommand command = connection.CreateCommand("SELECT * FROM ApsimFiles"))
                    using (DbDataReader reader = command.ExecuteReader())
                        result.Load(reader);

                Assert.NotNull(result);
                Assert.AreEqual(1, result.Rows.Count);
                Assert.AreEqual(10, result.Columns.Count);
                DataRow row = result.Rows[0];

                Assert.AreEqual(file.ID, row["ID"]);
                Assert.AreEqual(1, row["PullRequestID"]);
                Assert.AreEqual(file.FileName, row["FileName"]);
                Assert.AreEqual(file.FullFileName, row["FullFileName"]);
                if (connection is SQLiteConnection) // fixme - change sql server implementation to use varchar for dates????
                {
                    Assert.AreEqual(file.RunDate.ToString("yyyy-MM-dd HH:mm:ss"), row["RunDate"]);
                }
                else
                {
                    Assert.AreEqual(file.RunDate, row["RunDate"]);
                }
                Assert.AreEqual(file.IsMerged, row["IsMerged"]);
                Assert.AreEqual(file.StatsAccepted, row["StatsAccepted"]);
                Assert.AreEqual(file.SubmitDetails, row["SubmitDetails"]);
                Assert.AreEqual(file.AcceptedPullRequestId, row["AcceptedPullRequestId"]);
                //Assert.AreEqual("", row["AcceptedRunDate"]);//fixme

                // Check Simulations table.
                result = new DataTable();
                using (DbCommand command = connection.CreateCommand("SELECT * FROM Simulations"))
                    using (DbDataReader reader = command.ExecuteReader())
                        result.Load(reader);

                Assert.AreEqual(2, result.Rows.Count);
                Assert.AreEqual(4, result.Columns.Count);

                row = result.Rows[0];
                Assert.AreEqual(1, row["ID"]);
                Assert.AreEqual(1, row["ApsimFilesID"]);
                Assert.AreEqual("sim1", row["Name"]);
                Assert.AreEqual(1, row["OriginalSimulationID"]);

                row = result.Rows[1];
                Assert.AreEqual(2, row["ID"]);
                Assert.AreEqual(1, row["ApsimFilesID"]);
                Assert.AreEqual("sim2", row["Name"]);
                Assert.AreEqual(2, row["OriginalSimulationID"]);

                // Check PredictedObservedDetails.
                result = new DataTable();
                using (DbCommand command = connection.CreateCommand("SELECT * FROM PredictedObservedDetails"))
                    using (DbDataReader reader = command.ExecuteReader())
                        result.Load(reader);

                Assert.AreEqual(1, result.Rows.Count);
                Assert.AreEqual(11, result.Columns.Count);

                row = result.Rows[0];
                Assert.AreEqual(1, row["ID"]);
                Assert.AreEqual(file.ID, row["ApsimFilesID"]);
                Assert.AreEqual(poDetails.DatabaseTableName, row["TableName"]);
                Assert.AreEqual(poDetails.PredictedTableName, row["PredictedTableName"]);
                Assert.AreEqual(poDetails.ObservedTableName, row["ObservedTableName"]);
                Assert.AreEqual(poDetails.FieldNameUsedForMatch, row["FieldNameUsedForMatch"]);
                Assert.AreEqual(poDetails.FieldName2UsedForMatch, row["FieldName2UsedForMatch"]);
                Assert.AreEqual(poDetails.FieldName3UsedForMatch, row["FieldName3UsedForMatch"]);

                // todo: test the last 3 columns after implementing tests
                Assert.AreEqual(0, row["PassedTests"]);
                Assert.AreEqual(1, row["HasTests"]);
                Assert.AreEqual(DBNull.Value, row["AcceptedPredictedObservedDetailsID"]);

                // Check PredictedObservedValues.
                result = new DataTable();
                using (DbCommand command = connection.CreateCommand("SELECT * FROM PredictedObservedValues"))
                    using (DbDataReader reader = command.ExecuteReader())
                        result.Load(reader);

                // #Rows = #Sims * #Variables * #PredictedObservedTables
                // Note that this isn't a general rule, because not every simulation
                // will necessarily generate predicted data for this p/o table.
                int nSims   = file.Simulations.Rows.Count;
                int nVars   = poDetails.Data.Columns.Cast <DataColumn>().Where(c => c.ColumnName.StartsWith("Predicted.")).Count();
                int nTables = file.PredictedObserved.Count();
                int nRows   = nSims * nVars * nTables;

                Assert.AreEqual(nRows, result.Rows.Count);
                Assert.AreEqual(12, result.Columns.Count);

                for (int i = 0; i < result.Rows.Count; i++)
                {
                    row = result.Rows[i];
                    Assert.AreEqual(i + 1, row["ID"]);
                    Assert.AreEqual(1, row["PredictedObservedDetailsID"]);

                    // Note: this will not always be true in the more general case.
                    Assert.AreEqual(poDetails.Data.Rows[i]["SimulationID"], row["SimulationsID"]);

                    Assert.AreEqual(poDetails.FieldNameUsedForMatch, row["MatchName"]);

                    // Match value column will be of type string, to allow for matching on stage name.
                    Assert.AreEqual(poDetails.Data.Rows[i][poDetails.FieldNameUsedForMatch].ToString(), row["MatchValue"]);

                    Assert.AreEqual(poDetails.FieldName2UsedForMatch, row["MatchName2"]);
                    if (!string.IsNullOrEmpty(poDetails.FieldName2UsedForMatch))
                    {
                        Assert.AreEqual(poDetails.Data.Rows[i][poDetails.FieldName2UsedForMatch].ToString(), row["MatchValue2"]);
                    }
                    Assert.AreEqual(poDetails.FieldName3UsedForMatch, row["MatchName3"]);
                    if (!string.IsNullOrEmpty(poDetails.FieldName3UsedForMatch))
                    {
                        Assert.AreEqual(poDetails.Data.Rows[i][poDetails.FieldName3UsedForMatch].ToString(), row["MatchValue3"]);
                    }

                    Assert.AreEqual("GrainWt", row["ValueName"]);
                    Assert.AreEqual(poDetails.Data.Rows[i]["Predicted.GrainWt"], row["PredictedValue"]);
                    Assert.AreEqual(poDetails.Data.Rows[i]["Observed.GrainWt"], row["ObservedValue"]);
                }

                // Check PredictedObservedTests.
                result = new DataTable();
                using (DbCommand command = connection.CreateCommand("SELECT * FROM PredictedObservedTests"))
                    using (DbDataReader reader = command.ExecuteReader())
                        result.Load(reader);

                string[] variables = new[]
                {
                    "n",
                    "Slope",
                    "Intercept",
                    "SEslope",
                    "SEintercept",
                    "R2",
                    "RMSE",
                    "NSE",
                    "ME",
                    "MAE",
                    "RSR",
                };

                Assert.AreEqual(variables.Length, result.Rows.Count);
                Assert.AreEqual(12, result.Columns.Count);

                List <string> actualVariables = new List <string>();
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    row = result.Rows[i];

                    Assert.AreEqual(i + 1, row["ID"]);
                    Assert.AreEqual(1, row["PredictedObservedDetailsID"]);
                    Assert.AreEqual("GrainWt", row["Variable"]);
                    actualVariables.Add(row["Test"].ToString());

                    Assert.AreEqual(DBNull.Value, row["Accepted"]);
                }
                Assert.AreEqual(variables, actualVariables);

                Assert.AreEqual(2, GetCurrentValue(result, "n"), 1e-10);
                Assert.AreEqual(4, GetCurrentValue(result, "Slope"), 1e-10);
                Assert.AreEqual(-3.5, GetCurrentValue(result, "Intercept"), 1e-10);
                Assert.AreEqual(0, GetCurrentValue(result, "SEslope"), 1e-10);
                Assert.AreEqual(0, GetCurrentValue(result, "SEintercept"), 1e-10);
                Assert.AreEqual(1, GetCurrentValue(result, "R2"), 1e-10);
                Assert.AreEqual(0.380789, GetCurrentValue(result, "RMSE"), 1e-10);
                Assert.AreEqual(-57, GetCurrentValue(result, "NSE"), 1e-10);
                Assert.AreEqual(-0.35, GetCurrentValue(result, "ME"), 1e-10);
                Assert.AreEqual(0.35, GetCurrentValue(result, "MAE"), 1e-10);
                Assert.AreEqual(5.385165, GetCurrentValue(result, "RSR"), 1e-10);
            }
        }