/// <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); }