/// <summary>
///
/// </summary>
/// <returns></returns>
public DataTable GetTable(int startYear, int endYear)
{
if (OpenSQLiteDB() == false)
{
return(null);
}
string sqlQuery = "SELECT " +
RegionColumnName + "," +
LandIdColumnName + "," +
GrassBAColumnName + "," +
LandConColumnName + "," +
StkRateColumnName + "," +
YearColumnName + "," +
//"CutNum," +
MonthColumnName + "," +
GrowthColumnName;
//"BP1," +
//"BP2" +
if (ErosionColumnName != null && ErosionColumnName != "")
{
sqlQuery += "," + ErosionColumnName;
}
if (RunoffColumnName != null || RunoffColumnName != "")
{
sqlQuery += "," + RunoffColumnName;
}
if (RainfallColumnName != null || RainfallColumnName != "")
{
sqlQuery += "," + RainfallColumnName;
}
if (CoverColumnName != null || CoverColumnName != "")
{
sqlQuery += "," + CoverColumnName;
}
if (TBAColumnName != null || TBAColumnName != "")
{
sqlQuery += "," + TBAColumnName;
}
sqlQuery += " FROM " + TableName;
sqlQuery += " WHERE " + YearColumnName + " BETWEEN " + startYear + " AND " + endYear;
try
{
DataTable results = SQLiteReader.ExecuteQuery(sqlQuery);
results.DefaultView.Sort = YearColumnName + ", " + MonthColumnName;
return(results);
}
catch (Exception err)
{
SQLiteReader.CloseDatabase();
ErrorMessage = err.Message;
return(null);
}
}
public void Version9()
{
Directory.SetCurrentDirectory(Path.GetTempPath());
string fileName = Path.Combine(Path.GetTempPath(), "TestConverter.db");
File.Delete(fileName);
SQLite connection = new SQLite();
connection.OpenDatabase(fileName, false);
try
{
connection.ExecuteNonQuery("CREATE TABLE Simulations (ID INTEGER PRIMARY KEY ASC, Name TEXT COLLATE NOCASE)");
connection.ExecuteNonQuery("CREATE TABLE Messages (SimulationID INTEGER, ComponentName TEXT, Date TEXT, Message TEXT, MessageType INTEGER)");
connection.ExecuteNonQuery("CREATE TABLE _Units (TableName TEXT, ColumnHeading TEXT, Units TEXT)");
connection.ExecuteNonQuery("CREATE TABLE Report (Col1 TEXT, Col2 TEXT, Col3 TEXT)");
string fromXML = "<Simulation Version=\"8\"/>";
XmlDocument doc = new XmlDocument();
doc.LoadXml(fromXML);
Assert.IsTrue(APSIMFileConverter.ConvertToLatestVersion(doc.DocumentElement, fileName));
DataTable tableData = connection.ExecuteQuery("SELECT * FROM sqlite_master");
string[] tableNames = DataTableUtilities.GetColumnAsStrings(tableData, "Name");
Assert.AreEqual(tableNames, new string[] { "_Simulations", "_Messages", "_Units", "Report" });
}
finally
{
connection.CloseDatabase();
File.Delete(fileName);
}
}
/// <summary>Connect to the SQLite database.</summary>
/// <param name="forWriting">if set to <c>true</c> [for writing].</param>
/// <exception cref="Models.Core.ApsimXException">Cannot find name of .db file</exception>
private void Open(bool forWriting)
{
lock (Locks)
{
if (Filename == null)
{
Simulations simulations = Apsim.Parent(this, typeof(Simulations)) as Simulations;
if (simulations != null)
{
Filename = Path.ChangeExtension(simulations.FileName, ".db");
}
}
if (Filename != null &&
(Connection == null ||
(ForWriting == false && forWriting == true)))
{
if (Filename == null)
{
throw new ApsimXException(this, "Cannot find name of .db file");
}
Disconnect();
ForWriting = forWriting;
if (!Locks.ContainsKey(Filename))
{
Locks.Add(Filename, new DbMutex());
}
Locks[Filename].Aquire();
try
{
if (!File.Exists(Filename))
{
Connection = new SQLite();
Connection.OpenDatabase(Filename, readOnly: false);
Connection.ExecuteNonQuery("CREATE TABLE Simulations (ID INTEGER PRIMARY KEY ASC, Name TEXT COLLATE NOCASE)");
Connection.ExecuteNonQuery("CREATE TABLE Messages (SimulationID INTEGER, ComponentName TEXT, Date TEXT, Message TEXT, MessageType INTEGER)");
if (!forWriting)
{
Connection.CloseDatabase();
Connection.OpenDatabase(Filename, readOnly: !forWriting);
}
}
else
{
Connection = new SQLite();
Connection.OpenDatabase(Filename, readOnly: !forWriting);
}
}
finally
{
Locks[Filename].Release();
}
}
}
}
/// <summary>Read in data tables from a .db file.</summary>
private void GetDataFromDB(string dbFileName, DataSet dataSet)
{
// Need to change the working folder to the folder where sqlite3.dll is located.
string savedWorkingFolder = Directory.GetCurrentDirectory();
Directory.SetCurrentDirectory(binFolder);
SQLite connection = new SQLite();
try
{
connection.OpenDatabase(dbFileName, readOnly: true);
foreach (string tableName in connection.GetTableNames())
{
if (!tableName.StartsWith("_"))
{
DataTable data = connection.ExecuteQuery("SELECT * FROM " + tableName);
data.TableName = tableName;
dataSet.Tables.Add(data);
}
}
}
finally
{
connection.CloseDatabase();
Directory.SetCurrentDirectory(savedWorkingFolder);
}
}
/// <summary>
/// Rename the "Simulations", "Messages", "InitialConditions" .db tables to be
/// prefixed with an underscore.
/// </summary>
/// <param name="node">The node to upgrade.</param>
/// <param name="fileName">The name of the .apsimx file</param>
private static void UpgradeToVersion14(XmlNode node, string fileName)
{
string dbFileName = Path.ChangeExtension(fileName, ".db");
if (File.Exists(dbFileName))
{
SQLite connection = new SQLite();
connection.OpenDatabase(dbFileName, false);
try
{
DataTable tableData = connection.ExecuteQuery("SELECT * FROM sqlite_master");
foreach (string tableName in DataTableUtilities.GetColumnAsStrings(tableData, "Name"))
{
if (tableName == "Simulations" || tableName == "Messages" || tableName == "InitialConditions")
{
connection.ExecuteNonQuery("ALTER TABLE " + tableName + " RENAME TO " + "_" + tableName);
}
}
}
finally
{
connection.CloseDatabase();
}
}
}
/// <summary>Disconnect from the SQLite database.</summary>
public void Disconnect()
{
if (Connection != null)
{
Connection.CloseDatabase();
Connection = null;
}
}
/// <summary>Close the SQLite database.</summary>
private void Close()
{
if (connection != null)
{
tables.Clear();
simulationIDs.Clear();
connection.CloseDatabase();
connection = null;
}
}
/// <summary>Convert a SQLite table to a string.</summary>
public static string TableToString(string fileName, string tableName)
{
SQLite database = new SQLite();
database.OpenDatabase(fileName, true);
DataTable data = database.ExecuteQuery("SELECT * FROM " + tableName);
database.CloseDatabase();
return(TableToString(data));
}
/// <summary>Convert a SQLite table to a string.</summary>
public static string TableToString(string fileName, string tableName, IEnumerable <string> fieldNames = null)
{
SQLite database = new SQLite();
database.OpenDatabase(fileName, true);
var st = TableToString(database, tableName, fieldNames);
database.CloseDatabase();
return(st);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DataTable GetTable(int startYear, int endYear)
{
if (OpenSQLiteDB() == false)
{
return(null);
}
string sqlQuery = "SELECT " +
RegionColumnName + "," +
LandIdColumnName + "," +
GrassBAColumnName + "," +
LandConColumnName + "," +
StkRateColumnName + "," +
YearColumnName + "," +
//"CutNum," +
MonthColumnName + "," +
GrowthColumnName +
//"BP1," +
//"BP2" +
" FROM " + TableName;
//Region, Soil,GrassBA,LandCon,StkRate,Year,CutNum,Month,Growth,BP1,BP2 FROM Native_Inputs";
//sqlQuery += " WHERE Year BETWEEN " + startYear + " AND " + endYear;
sqlQuery += " WHERE " + YearColumnName + " BETWEEN " + startYear + " AND " + endYear;
try
{
DataTable results = SQLiteReader.ExecuteQuery(sqlQuery);
// results.DefaultView.Sort = "Year, Month";
results.DefaultView.Sort = YearColumnName + ", " + MonthColumnName;
return(results);
}
catch (Exception err)
{
SQLiteReader.CloseDatabase();
ErrorMessage = err.Message;
return(null);
}
}
/// <summary>Open the database.</summary>
/// <param name="readOnly">Open for readonly access?</param>
/// <returns>True if file was successfully opened</returns>
public bool Open(bool readOnly)
{
if (connection != null && !connection.IsOpen)
{
connection = null;
}
if (connection != null && readOnly == connection.IsReadOnly)
{
return(true); // already open.
}
if (connection != null && readOnly && !connection.IsReadOnly)
{
return(false); // can't open for reading as we are currently writing
}
if (FileName == null)
{
Simulations simulations = Apsim.Parent(this, typeof(Simulations)) as Simulations;
if (simulations != null)
{
FileName = Path.ChangeExtension(simulations.FileName, ".db");
}
else
{
throw new Exception("Cannot find a filename for the DataStore database.");
}
}
Close();
connection = new SQLite();
// TODO: generalise the following
if (!File.Exists(FileName))
{
connection.OpenDatabase(FileName, readOnly: false);
connection.ExecuteNonQuery("CREATE TABLE _Checkpoints (ID INTEGER PRIMARY KEY ASC, Name TEXT, Version TEXT, Date TEXT)");
connection.ExecuteNonQuery("CREATE TABLE _CheckpointFiles (CheckpointID INTEGER, FileName TEXT, Contents BLOB)");
connection.ExecuteNonQuery("CREATE TABLE _Simulations (ID INTEGER PRIMARY KEY ASC, Name TEXT COLLATE NOCASE)");
connection.ExecuteNonQuery("CREATE TABLE _Messages (CheckpointID INTEGER, ComponentID INTEGER, SimulationID INTEGER, ComponentName TEXT, Date TEXT, Message TEXT, MessageType INTEGER)");
connection.ExecuteNonQuery("CREATE TABLE _Units (TableName TEXT, ColumnHeading TEXT, Units TEXT)");
connection.ExecuteNonQuery("INSERT INTO [_Checkpoints] (Name) VALUES (\"Current\")");
connection.CloseDatabase();
}
connection.OpenDatabase(FileName, readOnly);
Refresh();
return(true);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DataTable GetTable(int StartYear, int EndYear)
{
if (OpenSQLiteDB() == false)
{
return(null);
}
string SQLquery = "SELECT Region,Soil,GrassBA,LandCon,StkRate,Year,CutNum,Month,Growth,BP1,BP2 FROM Native_Inputs";
SQLquery += " WHERE Year BETWEEN " + StartYear + " AND " + EndYear;
try
{
DataTable results = SQLiteReader.ExecuteQuery(SQLquery);
results.DefaultView.Sort = "Year, Month";
return(results);
}
catch (Exception err)
{
SQLiteReader.CloseDatabase();
ErrorMessage = err.Message;
return(null);
}
}
private void OnSimulationCommencing(object sender, EventArgs e)
{
SQLite sQLiteReader = new SQLite();
try
{
sQLiteReader.OpenDatabase(FullFileName, true);
// check table exists
if (!sQLiteReader.GetTableNames().Contains(TableName))
{
string errorMsg = "The specified table named [" + TableName + "] was not found\r\n. Please note these table names are case sensitive.";
throw new ApsimXException(this, errorMsg);
}
Dictionary <string, string> columnLinks = new Dictionary <string, string>()
{
{ "year", YearColumnName },
{ "month", MonthColumnName },
{ "soil", SoilTypeColumnName },
{ "crop", CropNameColumnName },
{ "amount", AmountColumnName },
{ "N", PercentNitrogenColumnName },
{ "HarvestType", PercentNitrogenColumnName }
};
foreach (var item in columnLinks)
{
// check each column name exists
if (!(item.Key == "N" & item.Value == "" & item.Value == "HarvestType"))
{
if (!sQLiteReader.GetColumnNames(TableName).Contains(item.Value))
{
string errorMsg = "The specified column [o=" + item.Key + "] does not exist in the table named [" + TableName + "] for [x=" + this.Name + "]\r\nEnsure the column name is present in the table. Please not these column names are case sensitive.";
throw new ApsimXException(this, errorMsg);
}
}
}
}
catch (Exception ex)
{
string errorMsg = "@error:There was a problem with the SQLite database [o=" + FileName + "] for [x=" + this.Name + "]\r\n" + ex.Message;
throw new ApsimXException(this, errorMsg);
}
if (sQLiteReader.IsOpen)
{
sQLiteReader.CloseDatabase();
}
}
public void DeleteTable_TableName_TableDeleted()
{
using (DataStore storage = new DataStore(fileName))
{
CreateTable(storage);
storage.DeleteTable("Report1");
Assert.IsFalse(storage.TableNames.Contains("Report1"));
}
SQLite database = new SQLite();
database.OpenDatabase(fileName, true);
DataTable tableData = database.ExecuteQuery("SELECT * FROM sqlite_master");
database.CloseDatabase();
string[] tableNames = DataTableUtilities.GetColumnAsStrings(tableData, "Name");
Assert.AreEqual(Array.IndexOf(tableNames, "Report1"), -1);
}
/// <summary>Open the SQLite database.</summary>
/// <param name="readOnly">Open for readonly access?</param>
/// <returns>True if file was successfully opened</returns>
private bool Open(bool readOnly)
{
if (connection != null && readOnly == connection.IsReadOnly)
{
return(true); // already open.
}
if (connection != null && readOnly && !connection.IsReadOnly)
{
return(false); // can't open for reading as we are currently writing
}
if (FileName == null)
{
Simulations simulations = Apsim.Parent(this, typeof(Simulations)) as Simulations;
if (simulations != null)
{
FileName = Path.ChangeExtension(simulations.FileName, ".db");
}
else
{
throw new Exception("Cannot find a filename for the SQLite database.");
}
}
Close();
connection = new SQLite();
if (!File.Exists(FileName))
{
connection.OpenDatabase(FileName, readOnly: false);
connection.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS _Simulations (ID INTEGER PRIMARY KEY ASC, Name TEXT COLLATE NOCASE)");
connection.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS _Messages (SimulationID INTEGER, ComponentName TEXT, Date TEXT, Message TEXT, MessageType INTEGER)");
connection.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS _Units (TableName TEXT, ColumnHeading TEXT, Units TEXT)");
connection.CloseDatabase();
}
connection.OpenDatabase(FileName, readOnly);
Refresh();
return(true);
}
/// <summary>
/// Upgrades to version 25. Add checkpoint fields and table to .db
/// </summary>
/// <param name="node">The node to upgrade.</param>
/// <param name="fileName">The name of the .apsimx file</param>
private static void UpgradeToVersion25(XmlNode node, string fileName)
{
string dbFileName = Path.ChangeExtension(fileName, ".db");
if (File.Exists(dbFileName))
{
SQLite connection = new SQLite();
connection.OpenDatabase(dbFileName, false);
try
{
DataTable tableData = connection.ExecuteQuery("SELECT * FROM sqlite_master");
List <string> tableNames = DataTableUtilities.GetColumnAsStrings(tableData, "Name").ToList();
if (!tableNames.Contains("_Checkpoints"))
{
connection.ExecuteNonQuery("BEGIN");
foreach (string tableName in tableNames)
{
List <string> columnNames = connection.GetColumnNames(tableName);
if (columnNames.Contains("SimulationID"))
{
connection.ExecuteNonQuery("ALTER TABLE " + tableName + " ADD COLUMN CheckpointID INTEGER DEFAULT 1");
}
}
// Now add a _checkpointfiles table.
connection.ExecuteNonQuery("CREATE TABLE _Checkpoints (ID INTEGER PRIMARY KEY ASC, Name TEXT, Version TEXT, Date TEXT)");
connection.ExecuteNonQuery("CREATE TABLE _CheckpointFiles (CheckpointID INTEGER, FileName TEXT, Contents BLOB)");
connection.ExecuteNonQuery("INSERT INTO [_Checkpoints] (Name) VALUES (\"Current\")");
connection.ExecuteNonQuery("END");
}
}
finally
{
connection.CloseDatabase();
}
}
}
/// <summary>Convert a SQLite table to a string.</summary>
public static string TableToString(string fileName, string tableName, IEnumerable <string> fieldNames = null)
{
SQLite database = new SQLite();
database.OpenDatabase(fileName, true);
string sql = "SELECT ";
if (fieldNames == null)
{
sql += "*";
}
else
{
bool first = true;
foreach (string fieldName in fieldNames)
{
if (first)
{
first = false;
}
else
{
sql += ",";
}
sql += fieldName;
}
}
sql += " FROM " + tableName;
if (database.GetColumnNames(tableName).Contains("CheckpointID"))
{
sql += " ORDER BY CheckpointID";
}
DataTable data = database.ExecuteQuery(sql);
database.CloseDatabase();
return(TableToString(data));
}
/// <summary>
/// Closes the database.
/// </summary>
public static void CloseDatabase()
{
db.CloseDatabase();
isDbOpen = false;
}
public void TestCustomMetData()
{
// Open an in-memory database.
IDatabaseConnection database = new SQLite();
database.OpenDatabase(":memory:", readOnly: false);
string weatherData = ReflectionUtilities.GetResourceAsString("UnitTests.Weather.CustomMetData.met");
string metFile = Path.GetTempFileName();
File.WriteAllText(metFile, weatherData);
try
{
Simulation sim = new Simulation()
{
Children = new List <IModel>()
{
new Clock(),
new MockSummary(),
new Models.Climate.Weather()
{
FullFileName = metFile
},
new Models.Report()
{
VariableNames = new[]
{
"[Manager].Script.MyColumn as x"
},
EventNames = new[]
{
"[Clock].DoReport"
}
},
new Manager()
{
Code = "using System;\nusing Models.Core;\nusing Models.Climate;\n\nnamespace Models\n{\n [Serializable]\n public class Script : Model\n {\n [Link] private Weather weather;\n \n public double MyColumn\n {\n \tget\n \t{\n \t\treturn weather.GetValue(\"my_column_name\");\n \t}\n }\n }\n}\n"
}
}
};
Simulations sims = new Simulations()
{
Children = new List <IModel>()
{
new DataStore(database),
sim
}
};
// Run simulations.
Runner runner = new Runner(sims);
List <Exception> errors = runner.Run();
Assert.NotNull(errors);
if (errors.Count != 0)
{
throw new AggregateException(errors);
}
int[] rawData = new int[] { 6, 7, 2, 3, 4 };
List <object[]> rowData = rawData.Select(x => new object[] { x }).ToList();
DataTable expected = Utilities.CreateTable(new string[] { "x" }, rowData);
Assert.IsTrue(
expected
.IsSame(database.ExecuteQuery("SELECT [x] FROM Report")));
}
finally
{
database.CloseDatabase();
File.Delete(metFile);
}
}
/// <summary>
/// Searches the DataTable created from the Forage File using the specified parameters.
/// <returns></returns>
/// </summary>
/// <param name="soilId">Name of soil or run name in database</param>
/// <param name="cropName">Name of crop in database</param>
/// <param name="startDate">Start date of the simulation</param>
/// <param name="endDate">End date of the simulation</param>
/// <returns>A struct called CropDataType containing the crop data for this month.
/// This struct can be null.
/// </returns>
public List <CropDataType> GetCropDataForEntireRun(string soilId, string cropName,
DateTime startDate, DateTime endDate)
{
// check SQL file
SQLite sQLiteReader = new SQLite();
try
{
sQLiteReader.OpenDatabase(FullFileName, true);
}
catch (Exception ex)
{
ErrorMessage = "@error:There was a problem opening the SQLite database [o=" + FullFileName + "for [x=" + this.Name + "]\n" + ex.Message;
}
// check if Npct column exists in database
nitrogenColumnExists = sQLiteReader.GetColumnNames(TableName).Contains(PercentNitrogenColumnName);
// define SQL filter to load data
string sqlQuery = "SELECT " + YearColumnName + "," + MonthColumnName + "," + CropNameColumnName + "," + SoilTypeColumnName + "," + AmountColumnName + "" + (nitrogenColumnExists ? "," + PercentNitrogenColumnName : "") + " FROM " + TableName
+ " WHERE " + SoilTypeColumnName + " = '" + soilId + "'"
+ " AND " + CropNameColumnName + " = '" + cropName + "'";
if (startDate.Year == endDate.Year)
{
sqlQuery += " AND (( " + YearColumnName + " = " + startDate.Year + " AND " + MonthColumnName + " >= " + startDate.Month + " AND " + MonthColumnName + " < " + endDate.Month + ")"
+ ")";
}
else
{
sqlQuery += " AND (( " + YearColumnName + " = " + startDate.Year + " AND " + MonthColumnName + " >= " + startDate.Month + ")"
+ " OR ( " + YearColumnName + " > " + startDate.Year + " AND " + YearColumnName + " < " + endDate.Year + ")"
+ " OR ( " + YearColumnName + " = " + endDate.Year + " AND " + MonthColumnName + " < " + endDate.Month + ")"
+ ")";
}
DataTable results;
try
{
results = sQLiteReader.ExecuteQuery(sqlQuery);
}
catch (Exception ex)
{
string errorMsg = "@error:There was a problem accessing the SQLite database [o=" + FullFileName + "] for [x=" + this.Name + "]\n" + ex.Message;
throw new ApsimXException(this, errorMsg);
}
if (sQLiteReader.IsOpen)
{
sQLiteReader.CloseDatabase();
}
List <CropDataType> cropDetails = new List <CropDataType>();
if (results.Rows.Count > 0)
{
results.DefaultView.Sort = YearColumnName + "," + MonthColumnName;
// convert to list<CropDataType>
foreach (DataRowView row in results.DefaultView)
{
cropDetails.Add(DataRow2CropData(row.Row));
}
}
return(cropDetails);
}
