/// <summary>Refresh this instance to reflect the database connection.</summary> public void Refresh() { simulationIDs.Clear(); checkpointIDs.Clear(); tables.Clear(); // Read in simulation ids. if (connection.TableExists("_Simulations")) { var data = connection.ExecuteQuery("SELECT * FROM _Simulations"); foreach (DataRow row in data.Rows) { simulationIDs.Add(row["Name"].ToString(), Convert.ToInt32(row["ID"])); } } // Read in checkpoint ids. if (connection.TableExists("_Checkpoints")) { var data = connection.ExecuteQuery("SELECT * FROM _Checkpoints"); foreach (DataRow row in data.Rows) { checkpointIDs.Add(row["Name"].ToString(), Convert.ToInt32(row["ID"])); } } // For each table in the database, read in field names. foreach (var tableName in connection.GetTableNames()) { tables.Add(tableName, connection.GetTableColumns(tableName)); } // Get the units table. units = new DataView(GetData("_Units")); }
/// <summary> /// Read the database connection for simulation and checkpoint ids. /// </summary> /// <param name="dbConnection">The database connection to read from.</param> private void ReadExistingDatabase(IDatabaseConnection dbConnection) { if (dbConnection == null) { return; } simulationIDs.Clear(); if (dbConnection.TableExists("_Simulations")) { var data = dbConnection.ExecuteQuery("SELECT * FROM [_Simulations]"); foreach (DataRow row in data.Rows) { int id = Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture); string folderName = row["FolderName"].ToString(); simulationIDs.Add(row["Name"].ToString(), new SimulationDetails() { ID = id, FolderName = folderName }); } } checkpointIDs.Clear(); if (dbConnection.TableExists("_Checkpoints")) { var data = dbConnection.ExecuteQuery("SELECT * FROM [_Checkpoints]"); foreach (DataRow row in data.Rows) { checkpointIDs.Add(row["Name"].ToString(), Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture)); } } }
/// <summary>Set the database connection.Constructor</summary> /// <param name="dbConnection">The database connection to write to.</param> public void SetConnection(IDatabaseConnection dbConnection) { Connection = dbConnection; ReadExistingDatabase(dbConnection); if (dbConnection is SQLite && !(dbConnection as SQLite).IsInMemory) { // For disk-based databases, these pragmas greatly improve performance dbConnection.ExecuteQuery("PRAGMA journal_mode=WAL"); dbConnection.ExecuteQuery("PRAGMA synchronous=NORMAL"); } }
/// <summary> /// Move all data from the specified table in destination to source. /// </summary> /// <param name="source">The source database.</param> /// <param name="destination">The destination database.</param> /// <param name="tableName">The name of the table to merge.</param> /// <param name="oldIDNewIDMapping">A mapping from source IDs to destination IDs.</param> private static void MergeTable(IDatabaseConnection source, IDatabaseConnection destination, string tableName, Dictionary <int, int> oldIDNewIDMapping) { var sourceData = source.ExecuteQuery("SELECT * FROM " + tableName); DataTable destinationData = null; if (destination.GetTableNames().Contains(tableName)) { destinationData = destination.ExecuteQuery("SELECT * FROM " + tableName); } else { // Need to create the table. var colNames = sourceData.Columns.Cast <DataColumn>().Select(col => col.ColumnName).ToList(); var colTypes = sourceData.Columns.Cast <DataColumn>().Select(col => source.GetDBDataTypeName(col.DataType)).ToList(); destination.CreateTable(tableName, colNames, colTypes); } DataView view = null; var columnNames = DataTableUtilities.GetColumnNames(sourceData).ToList(); foreach (DataRow simulationRow in sourceData.Rows) { if (columnNames.Contains("SimulationID")) { var oldID = Convert.ToInt32(simulationRow["SimulationID"]); if (oldIDNewIDMapping.TryGetValue(oldID, out int newID)) { // Change the ID to new ID simulationRow["SimulationID"] = newID; } } if (tableName == "_Units") { // For the units table only copy the row if it doesn't already exist. if (view == null) { view = new DataView(destinationData); } var sourceTableName = (string)simulationRow["TableName"]; var sourceColumnHeading = (string)simulationRow["ColumnHeading"]; view.RowFilter = $"TableName='{sourceTableName}' and ColumnHeading='{sourceColumnHeading}'"; if (view.Count == 0) { destination.InsertRows(tableName, columnNames, new List <object[]>() { simulationRow.ItemArray }); } } else { destination.InsertRows(tableName, columnNames, new List <object[]>() { simulationRow.ItemArray }); } } }
/// <summary> /// Read the database connection for simulation and checkpoint ids. /// </summary> /// <param name="dbConnection">The database connection to read from.</param> private void ReadExistingDatabase(IDatabaseConnection dbConnection) { if (dbConnection == null) { return; } lock (lockObject) simulationIDs.Clear(); if (dbConnection.TableExists("_Simulations")) { var data = dbConnection.ExecuteQuery("SELECT * FROM [_Simulations]"); foreach (DataRow row in data.Rows) { int id = Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture); string folderName = null; if (data.Columns.Contains("FolderName")) { folderName = row["FolderName"].ToString(); } lock (lockObject) simulationIDs.Add(row["Name"].ToString(), new SimulationDetails() { ID = id, FolderName = folderName }); } } checkpointIDs.Clear(); if (dbConnection.TableExists("_Checkpoints")) { var data = dbConnection.ExecuteQuery("SELECT * FROM [_Checkpoints]"); foreach (DataRow row in data.Rows) { checkpointIDs.Add(row["Name"].ToString(), new Checkpoint() { ID = Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture), ShowOnGraphs = data.Columns["OnGraphs"] != null && !Convert.IsDBNull(row["OnGraphs"]) && Convert.ToInt32(row["OnGraphs"], CultureInfo.InvariantCulture) == 1 }); } } }
public void RemoveOldRowsWhenWritingSimulationData() { // Write first report data. var data1 = new ReportData() { CheckpointName = "Current", SimulationName = "Sim1", TableName = "Report", ColumnNames = new string[] { "Col" }, ColumnUnits = new string[] { null } }; data1.Rows.Add(new List <object>() { 1 }); data1.Rows.Add(new List <object>() { 2 }); DataStoreWriter writer = new DataStoreWriter(database); writer.WriteTable(data1); writer.Stop(); // Now do it again this time writing different data for the same sim. var data2 = new ReportData() { CheckpointName = "Current", SimulationName = "Sim1", TableName = "Report", ColumnNames = new string[] { "Col" }, ColumnUnits = new string[] { null } }; data2.Rows.Add(new List <object>() { 3 }); writer = new DataStoreWriter(database); var cleanCommand = writer.Clean(new List <string>() { "Sim1" }); cleanCommand.Run(null); writer.WriteTable(data2); writer.Stop(); Assert.IsTrue( Utilities.CreateTable(new string[] { "CheckpointID", "SimulationID", "Col" }, new List <object[]> { new object[] { 1, 1, 3 } }) .IsSame(database.ExecuteQuery("SELECT * FROM [Report] ORDER BY [Col]"))); }
/// <summary> /// Read the database connection for simulation and checkpoint ids. /// </summary> /// <param name="dbConnection">The database connection to read from.</param> private void ReadExistingDatabase(IDatabaseConnection dbConnection) { simulationIDs.Clear(); if (dbConnection.TableExists("_Simulations")) { var data = dbConnection.ExecuteQuery("SELECT * FROM _Simulations"); foreach (DataRow row in data.Rows) { simulationIDs.Add(row["Name"].ToString(), Convert.ToInt32(row["ID"])); } } checkpointIDs.Clear(); if (dbConnection.TableExists("_Checkpoints")) { var data = dbConnection.ExecuteQuery("SELECT * FROM _Checkpoints"); foreach (DataRow row in data.Rows) { checkpointIDs.Add(row["Name"].ToString(), Convert.ToInt32(row["ID"])); } } }
public void EnsureSimulationRuns() { foreach (var typeOfRun in runTypes) { // Open an in-memory database. database = new SQLite(); database.OpenDatabase(":memory:", readOnly: false); // Create a simulation and add a datastore. var simulation = new Simulation() { Name = "Sim", FileName = Path.GetTempFileName(), Children = new List <IModel>() { new Clock() { StartDate = new DateTime(1980, 1, 1), EndDate = new DateTime(1980, 1, 2) }, new MockSummary(), new Models.Report() { Name = "Report", VariableNames = new string[] { "[Clock].Today" }, EventNames = new string[] { "[Clock].DoReport" } }, new DataStore(database) } }; // Run simulations. Runner runner = new Runner(simulation, runType: typeOfRun); List <Exception> errors = runner.Run(); Assert.NotNull(errors); Assert.AreEqual(0, errors.Count); Assert.IsTrue( Utilities.CreateTable(new string[] { "Clock.Today" }, new List <object[]> { new object[] { new DateTime(1980, 01, 01) }, new object[] { new DateTime(1980, 01, 02) } }) .IsSame(database.ExecuteQuery("SELECT [Clock.Today] FROM Report ORDER BY [Clock.Today]"))); database.CloseDatabase(); } }
internal ObservableCollection <Patient> GetPatientRows() { ObservableCollection <Patient> rows = new ObservableCollection <Patient>(); try { var reader = _database.ExecuteQuery("Select * from Patients"); if (reader.HasRows) { while (reader.Read()) { Patient patient = new Patient(reader.GetInt64(0), reader.GetString(1).Trim(), reader.GetString(2).Trim(), reader.GetDateTime(3), reader.GetString(4).Trim(), reader.GetDateTime(5)); rows.Add(patient); } } reader.Close(); } catch (Exception e) { log.Error(e.Message); MessageBox.Show("Das Verbidnen mit der Datenbank ist nicht möglich. Patientendaten können nicht gelesen werden", "Keine Verbindung"); } return(rows); }
/// <summary>Convert a SQLite table to a string.</summary> public static string TableToString(IDatabaseConnection database, string tableName, IEnumerable <string> fieldNames = null) { 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 + "]"; var orderByFieldNames = new List <string>(); if (database.GetColumnNames(tableName).Contains("CheckpointID")) { orderByFieldNames.Add("[CheckpointID]"); } if (database.GetColumnNames(tableName).Contains("SimulationID")) { orderByFieldNames.Add("[SimulationID]"); } if (database.GetColumnNames(tableName).Contains("Clock.Today")) { orderByFieldNames.Add("[Clock.Today]"); } if (orderByFieldNames.Count > 0) { sql += " ORDER BY " + StringUtilities.BuildString(orderByFieldNames.ToArray(), ","); } DataTable data = database.ExecuteQuery(sql); return(TableToString(data)); }
/// <summary> /// Constructor. /// </summary> /// <param name="modulProperties"></param> public VideoChatModule() : base(new VideoChatProperties()) { _view = new VideoChatModuleView(this); ///init the Session field with informations from VideoChatProperties _session = new Session(Context.Instance, GetProperties().GetProperty(GetProperties().Key_API_KEY, "no api key"), GetProperties().GetProperty(GetProperties().Key_SESSION_ID, "no session id")); _session.Connected += Session_Connected; _session.Disconnected += Session_Disconnected; _session.Error += Session_Error; _session.StreamReceived += Session_StreamReceived; _session.StreamDropped += Session_StreamDropped; IDatabaseConnection db = (IDatabaseConnection)SurrogateFramework.MainController.ConnectionHandler.GetConnection(FrameworkConstants.DatabaseName); IDictionary <string, SqlDbType> columns = new Dictionary <string, SqlDbType> { { "ID", SqlDbType.Int }, { "username", SqlDbType.Text }, { "Firstname", SqlDbType.Text }, { "Name", SqlDbType.Text } }; try { SqlDataReader resultsReader = db.ExecuteQuery(String.Format("SELECT * FROM {0}", VideoChatProperties.TableName)); if (resultsReader.HasRows) { while (resultsReader.Read()) { var contact = new VideoChatContact(resultsReader.GetInt32(0), resultsReader.GetString(1), resultsReader.GetString(2), resultsReader.GetString(3)); _availableContacts.Add(contact.Username, contact); } } resultsReader.Close(); } catch (Exception sqlException) { _availableContacts.Add("simonjw", new VideoChatContact(1, "simonjw", "simon", "wimmer")); _availableContacts.Add("drmueller", new VideoChatContact(2, "drmueller", "Dr. Hans", "Mueller")); MessageBox.Show("Es konnten keine Kontakte aus der Datenbanktabelle geladen werden. Beispieldaten wurden eingefügt.", "Fehler in Datenbankverbindung"); log.Debug(sqlException.Message); } InvokeContactData(); }
/// <summary>Lookup and return units for the specified column.</summary> /// <param name="connection">Database connection</param> /// <param name="columnName">The column name to return units for</param> private string LookupUnitsForColumn(IDatabaseConnection connection, string columnName) { StringBuilder sql = new StringBuilder(); sql.Append("SELECT Units FROM _Units WHERE TableName='"); sql.Append(Name); sql.Append("' AND ColumnHeading='"); sql.Append(columnName.Trim('\'')); sql.Append("'"); DataTable data = connection.ExecuteQuery(sql.ToString()); if (data.Rows.Count > 0) { return((string)data.Rows[0][0]); } else { return(null); } }
/// <summary> /// Merge a source .db file into a destination .db file. /// </summary> /// <param name="source"></param> /// <param name="destination"></param> public static void Merge(IDatabaseConnection source, SQLite destination) { destination.BeginTransaction(); if (source.GetTableNames().Contains("_Simulations")) { var sourceData = source.ExecuteQuery("SELECT * FROM _Simulations"); var destinationData = destination.ExecuteQuery("SELECT * FROM _Simulations"); var simulationNameIDMapping = destinationData .AsEnumerable() .ToDictionary(row => row.Field <string>(1), row => row.Field <int>(0)); var oldIDNewIDMapping = new Dictionary <int, int>(); var columnNames = DataTableUtilities.GetColumnNames(destinationData).ToList(); foreach (DataRow simulationRow in sourceData.Rows) { string name = simulationRow["Name"].ToString(); if (!simulationNameIDMapping.TryGetValue(name, out int id)) { // Add a new row to destination. var newID = simulationNameIDMapping.Values.Max() + 1; simulationNameIDMapping.Add(name, newID); var oldID = Convert.ToInt32(simulationRow["ID"]); oldIDNewIDMapping.Add(oldID, newID); simulationRow["ID"] = newID; destination.InsertRows("_Simulations", columnNames, new List <object[]>() { simulationRow.ItemArray }); } } foreach (var tableName in source.GetTableNames().Where(t => t != "_Simulations" && t != "_Checkpoints")) { MergeTable(source, destination, tableName, oldIDNewIDMapping); } } destination.EndTransaction(); }
/// <summary> /// Move all data from the specified table in destination to source. /// </summary> /// <param name="source">The source database.</param> /// <param name="destination">The destination database.</param> /// <param name="tableName">The name of the table to merge.</param> /// <param name="oldIDNewIDMapping">A mapping from source IDs to destination IDs.</param> private static void MergeTable(IDatabaseConnection source, IDatabaseConnection destination, string tableName, Dictionary <int, int> oldIDNewIDMapping) { var sourceData = source.ExecuteQuery("SELECT * FROM " + tableName); DataTable destinationData; if (destination.GetTableNames().Contains(tableName)) { destinationData = destination.ExecuteQuery("SELECT * FROM " + tableName); } else { // Need to create the table. var colNames = sourceData.Columns.Cast <DataColumn>().Select(col => col.ColumnName).ToList(); var colTypes = sourceData.Columns.Cast <DataColumn>().Select(col => source.GetDBDataTypeName(col.DataType)).ToList(); destination.CreateTable(tableName, colNames, colTypes); } var columnNames = DataTableUtilities.GetColumnNames(sourceData).ToList(); foreach (DataRow simulationRow in sourceData.Rows) { if (columnNames.Contains("SimulationID")) { var oldID = Convert.ToInt32(simulationRow["SimulationID"]); if (oldIDNewIDMapping.TryGetValue(oldID, out int newID)) { // Change the ID to new ID simulationRow["SimulationID"] = newID; } } destination.InsertRows(tableName, columnNames, new List <object[]>() { simulationRow.ItemArray }); } }
public void RunDirectoryOfFiles() { var simulations = new Simulations() { Name = "Simulations", Version = Converter.LatestVersion, Children = new List <IModel>() { new Simulation() { Name = "Sim1", FileName = Path.GetTempFileName(), Children = new List <IModel>() { new Clock() { StartDate = new DateTime(1980, 1, 1), EndDate = new DateTime(1980, 1, 2) }, new Summary() } }, new DataStore(), } }; // Create a temporary directory. var path = Path.Combine(Path.GetTempPath(), "RunDirectoryOfFiles"); if (Directory.Exists(path)) { Directory.Delete(path, true); } Directory.CreateDirectory(path); File.WriteAllText(Path.Combine(path, "Sim1.apsimx"), FileFormat.WriteToString(simulations)); simulations.Children[0].Name = "Sim2"; File.WriteAllText(Path.Combine(path, "Sim2.apsimx"), FileFormat.WriteToString(simulations)); var runner = new Runner(Path.Combine(path, "*.apsimx")); runner.Run(); // Check simulation 1 database database = new SQLite(); database.OpenDatabase(Path.Combine(path, "Sim1.db"), readOnly: true); Assert.IsTrue( Utilities.CreateTable(new string[] { "Message" }, new List <object[]> { new object[] { "Simulation terminated normally" } }) .IsSame(database.ExecuteQuery("SELECT [Message] FROM _Messages"))); database.CloseDatabase(); // Check simulation 2 database database = new SQLite(); database.OpenDatabase(Path.Combine(path, "Sim2.db"), readOnly: true); Assert.IsTrue( Utilities.CreateTable(new string[] { "Message" }, new List <object[]> { new object[] { "Simulation terminated normally" } }) .IsSame(database.ExecuteQuery("SELECT [Message] FROM _Messages"))); database.CloseDatabase(); }
public void EnsureOnlySelectedSimulationsAreRun() { foreach (var typeOfRun in runTypes) { // Open an in-memory database. database = new SQLite(); database.OpenDatabase(":memory:", readOnly: false); // Create a folder of 2 simulations. var folder = new Folder() { Children = new List <IModel>() { new DataStore(database), new Simulation() { Name = "Sim1", FileName = Path.GetTempFileName(), Children = new List <IModel>() { new Clock() { StartDate = new DateTime(1980, 1, 1), EndDate = new DateTime(1980, 1, 2) }, new MockSummary(), new Models.Report() { Name = "Report", VariableNames = new string[] { "[Clock].Today" }, EventNames = new string[] { "[Clock].DoReport" } }, } }, new Simulation() { Name = "Sim2", FileName = Path.GetTempFileName(), Children = new List <IModel>() { new Clock() { StartDate = new DateTime(1980, 1, 3), EndDate = new DateTime(1980, 1, 4) }, new MockSummary(), new Report() { Name = "Report", VariableNames = new string[] { "[Clock].Today" }, EventNames = new string[] { "[Clock].DoReport" } }, } } } }; Runner runner = new Runner(folder, runType: typeOfRun, simulationNamesToRun: new string[] { "Sim1" }); // Run simulations. List <Exception> errors = runner.Run(); Assert.NotNull(errors); Assert.AreEqual(0, errors.Count); // Check that data was written to database. Assert.IsTrue( Utilities.CreateTable(new string[] { "Clock.Today" }, new List <object[]> { new object[] { new DateTime(1980, 01, 01) }, new object[] { new DateTime(1980, 01, 02) } }) .IsSame(database.ExecuteQuery("SELECT [Clock.Today] FROM Report ORDER BY [Clock.Today]"))); database.CloseDatabase(); } }
/// <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>Convert a SQLite query to a string.</summary> public static string TableToStringUsingSQL(IDatabaseConnection database, string sql) { var data = database.ExecuteQuery(sql); return(TableToString(data)); }
/// <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"); }