/// <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>
/// Return the simulation id (from the simulations table) for the specified name.
/// If this name doesn't exist in the table then append a new row to the table and
/// returns its id.
/// </summary>
/// <param name="simulationName">Name of the simulation.</param>
/// <returns></returns>
private int GetSimulationID(string simulationName)
{
if (!TableExists("Simulations"))
return -1;
string selectSQL = "SELECT ID FROM Simulations WHERE Name = '" + simulationName + "'";
int ID = Connection.ExecuteQueryReturnInt(selectSQL, 0);
if (ID == -1)
{
Locks[Filename].Aquire();
ID = Connection.ExecuteQueryReturnInt(selectSQL, 0);
if (ID == -1)
{
if (ForWriting == false)
{
Disconnect();
Connection = new SQLite();
Connection.OpenDatabase(Filename, readOnly: false);
ForWriting = true;
}
Connection.ExecuteNonQuery("INSERT INTO [Simulations] (Name) VALUES ('" + simulationName + "')");
ID = Connection.ExecuteQueryReturnInt("SELECT ID FROM Simulations WHERE Name = '" + simulationName + "'", 0);
}
Locks[Filename].Release();
}
return ID;
}
/// <summary>
/// Go through the specified names and add them to the specified table if they are not
/// already there.
/// </summary>
/// <param name="Connection">The connection.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="names">The names.</param>
/// <param name="types">The types.</param>
private static void AddMissingColumnsToTable(SQLite Connection, string tableName, string[] names, Type[] types)
{
List<string> columnNames = Connection.GetColumnNames(tableName);
for (int i = 0; i < names.Length; i++)
{
if (!columnNames.Contains(names[i], StringComparer.OrdinalIgnoreCase))
{
string sql = "ALTER TABLE " + tableName + " ADD COLUMN [";
sql += names[i] + "] " + GetSQLColumnType(types[i]);
Connection.ExecuteNonQuery(sql);
}
}
}
/// <summary>Go prepare an insert into query and return the query.</summary>
/// <param name="Connection">The connection.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="names">The names.</param>
/// <returns></returns>
private static IntPtr PrepareInsertIntoTable(SQLite Connection, string tableName, string[] names)
{
string Cmd = "INSERT INTO " + tableName + "(";
for (int i = 0; i < names.Length; i++)
{
if (i > 0)
Cmd += ",";
Cmd += "[" + names[i] + "]";
}
Cmd += ") VALUES (";
for (int i = 0; i < names.Length; i++)
{
if (i > 0)
Cmd += ",";
Cmd += "?";
}
Cmd += ")";
return Connection.Prepare(Cmd);
}
/// <summary>Disconnect from the SQLite database.</summary>
public void Disconnect()
{
if (Connection != null)
{
Connection.CloseDatabase();
Connection = null;
}
}
