/// <summary>
/// Add a select based view to the data table for SQLite datastores
/// </summary>
/// <param name="name">name of the view</param>
/// <param name="selectSQL">select SQL statement</param>
public void AddView(string name, string selectSQL)
{
if (connection is SQLite)
{
if (connection.ViewExists(name))
{
connection.ExecuteNonQuery($"DROP VIEW {name}");
}
connection.ExecuteNonQuery($"CREATE VIEW {name} AS {selectSQL}");
}
else
{
throw new NotImplementedException();
}
}
internal void SavePatients(Collection <Patient> patients)
{
try
{
foreach (var row in patients)
{
_database.ExecuteNonQuery(String.Format("EXEC insertOrUpdatePatients {0}, '{1}', '{2}', '{3}', '{4}', '{5}';", row.Id, row.Name, row.Firstname, row.Birthday, row.Adress, row.Entry));
}
}
catch (Exception e)
{
log.Error(e.Message);
MessageBox.Show("Das Verbidnen mit der Datenbank ist nicht möglich. Patientendaten können nicht eingefügt werden", "Keine Verbindung");
}
}
/// <summary>Alter an existing table ensuring all columns exist.</summary>
/// <param name="connection">The database connection to write to</param>
private void AlterTable(IDatabaseConnection connection)
{
List <string> existingColumns = connection.GetTableColumns(Name);
lock (lockObject)
{
foreach (Column col in Columns)
{
if (!existingColumns.Contains(col.Name))
{
string dataTypeString;
if (col.DatabaseDataType == null)
{
dataTypeString = "integer";
}
else
{
dataTypeString = col.DatabaseDataType;
}
string sql = "ALTER TABLE " + Name + " ADD COLUMN [" + col.Name + "] " + dataTypeString;
connection.ExecuteNonQuery(sql);
}
}
}
}
/// <summary>Called to run the command. Can throw on error.</summary>
/// <param name="cancelToken">Is cancellation pending?</param>
public void Run(CancellationTokenSource cancelToken)
{
if (database.TableExists(table))
{
string sql = string.Empty;
bool dropEntireTable = false;
if (simIds == null)
{
if (checkId == 0)
{
dropEntireTable = true;
}
else
{
sql = string.Format("DELETE FROM [{0}] WHERE [CheckpointID]={1}", table, checkId);
}
}
else
{
List <string> columns = database.GetTableColumns(table);
string ids = StringUtilities.BuildString(simIds, ",");
if (columns.Contains("SimulationID"))
{
sql = string.Format("DELETE FROM [{0}] WHERE [CheckpointID]={1} AND [SimulationID] IN ({2})", table, checkId, ids);
}
else if (columns.Contains("SimulationName"))
{
sql = string.Format("DELETE T FROM [{0}] T INNER JOIN [_Simulations] S ON T.[SimulationName]=S.[SimulationName] WHERE [SimulationID] IN ({1})", table, ids);
if (checkId != 0)
{
sql += string.Format(" AND [CheckpointID]={0}", checkId);
}
}
else
{
dropEntireTable = true;
}
}
if (dropEntireTable)
{
database.DropTable(table);
}
else
{
database.ExecuteNonQuery(sql);
// If there are no rows left in table then drop the table.
if (database.TableIsEmpty(table))
{
database.DropTable(table);
}
}
}
}
/// <summary>Called to run the command. Can throw on error.</summary>
/// <param name="cancelToken">Is cancellation pending?</param>
public void Run(CancellationTokenSource cancelToken)
{
if (dataToWrite.Rows.Count > 0)
{
if (!tables.TryGetValue(dataToWrite.TableName, out var table))
{
table = new DatabaseTableDetails(connection, dataToWrite.TableName);
tables.Add(dataToWrite.TableName, table);
}
var query = new InsertQuery(dataToWrite);
// Make sure the table has the correct columns.
table.EnsureTableExistsAndHasRequiredColumns(dataToWrite);
// Get a list of column names.
var columnNames = dataToWrite.Columns.Cast <DataColumn>().Select(col => col.ColumnName);
try
{
connection.BeginTransaction();
if (deleteExistingRows)
{
// fixme - this assumes that "Current" checkpoint ID is always 1.
// This should always be correct afaik, but it would be better to
// verify this at runtime.
bool tableHasCheckpointID = connection.GetColumns(dataToWrite.TableName).Any(c => c.Item1 == "CheckpointID");
connection.ExecuteNonQuery($"DELETE FROM [{dataToWrite.TableName}] {(tableHasCheckpointID ? "WHERE CheckpointID = 1" : "")}");
}
// Write all rows.
foreach (DataRow row in dataToWrite.Rows)
{
query.ExecuteQuery(connection, columnNames, row.ItemArray);
}
}
finally
{
connection.EndTransaction();
query.Close(connection);
}
}
}
/// <summary>Called to run the command. Can throw on error.</summary>
/// <param name="cancelToken">Is cancellation pending?</param>
public void Run(CancellationTokenSource cancelToken)
{
if (database.TableExists(table))
{
string sql;
if (simIds == null)
{
if (checkId == 0)
{
sql = string.Format("DROP TABLE {0}", table);
}
else
{
sql = string.Format("DELETE FROM {0} WHERE CheckpointID={1}", table, checkId);
}
}
else
{
List <string> columns = database.GetTableColumns(table);
string ids = StringUtilities.BuildString(simIds, ",");
if (columns.Contains("SimulationID"))
{
sql = string.Format("DELETE FROM {0} WHERE CheckpointID={1} AND SimulationID IN ({2})", table, checkId, ids);
}
else if (columns.Contains("SimulationName"))
{
sql = string.Format("DELETE T FROM {0} T INNER JOIN _Simulations S ON SimulationName=SimulationName WHERE SimulationID IN ({1})", table, ids);
if (checkId != 0)
{
sql += string.Format(" AND CheckpointID={0}", checkId);
}
}
else
{
sql = string.Format("DROP TABLE {0}", table);
}
}
database.ExecuteNonQuery(sql);
}
}
/// <summary>Called to run the command. Can throw on error.</summary>
/// <param name="cancelToken">Is cancellation pending?</param>
public void Run(CancellationTokenSource cancelToken)
{
if (database == null)
{
return;
}
if (database.TableExists("_Checkpoints"))
{
var checkpointData = new DataView(database.ExecuteQuery("SELECT * FROM [_Checkpoints]"));
checkpointData.RowFilter = "Name='Current'";
if (checkpointData.Count == 1)
{
int checkId = Convert.ToInt32(checkpointData[0]["ID"], CultureInfo.InvariantCulture);
// Delete current data from all tables.
foreach (string tableName in database.GetTableNames())
{
if (!tableName.StartsWith("_") && database.TableExists(tableName))
{
database.ExecuteNonQuery(string.Format("DELETE FROM [{0}] WHERE [CheckpointID]={1}", tableName, checkId));
}
}
}
}
else
{
// No checkpoint information, so get rid of everything
// Delete current data from all tables.
foreach (string tableName in database.GetTableNames())
{
if (!tableName.StartsWith("_"))
{
database.ExecuteNonQuery(string.Format("DELETE FROM [{0}]", tableName));
}
}
}
// Delete empty tables.
List <string> tableNamesToDelete = new List <string>();
bool allTablesEmpty = true;
foreach (string tableName in database.GetTableNames())
{
if (!tableName.StartsWith("_"))
{
if (database.TableIsEmpty(tableName))
{
tableNamesToDelete.Add(tableName);
}
else
{
allTablesEmpty = false;
}
}
}
// If all data tables were emptied then delete all tables.
if (allTablesEmpty)
{
tableNamesToDelete = database.GetTableNames();
}
foreach (string tableName in tableNamesToDelete)
{
database.DropTable(tableName);
}
}
/// <summary>Called to run the command. Can throw on error.</summary>
/// <param name="cancelToken">Is cancellation pending?</param>
public void Run(CancellationTokenSource cancelToken)
{
if (database.TableExists("_Checkpoints"))
{
var checkpointData = new DataView(database.ExecuteQuery("SELECT * FROM _Checkpoints"));
checkpointData.RowFilter = "Name='Current'";
if (checkpointData.Count == 1)
{
int checkId = Convert.ToInt32(checkpointData[0]["ID"]);
// Delete current data from all tables.
foreach (string tableName in database.GetTableNames())
{
if (!tableName.StartsWith("_"))
{
database.ExecuteNonQuery(string.Format("DELETE FROM {0} WHERE CheckpointID={1}", tableName, checkId));
}
}
}
}
else
{
// No checkpoint information, so get rid of everything
// Delete current data from all tables.
foreach (string tableName in database.GetTableNames())
{
if (!tableName.StartsWith("_"))
{
database.ExecuteNonQuery(string.Format("DELETE FROM {0}", tableName));
}
}
}
// Delete empty tables.
List <string> tableNamesToDelete = new List <string>();
bool allTablesEmpty = true;
foreach (string tableName in database.GetTableNames())
{
if (!tableName.StartsWith("_"))
{
var data = database.ExecuteQuery(string.Format("SELECT * FROM {0} LIMIT 1", tableName));
if (data.Rows.Count == 0)
{
tableNamesToDelete.Add(tableName);
}
else
{
allTablesEmpty = false;
}
}
}
// If all data tables were emptied then delete all tables.
if (allTablesEmpty)
{
tableNamesToDelete = database.GetTableNames();
}
foreach (string tableName in tableNamesToDelete)
{
database.ExecuteNonQuery(string.Format("DROP TABLE {0}", tableName));
}
database.ExecuteNonQuery("VACUUM");
}
