public void Execute(IDatabaseConnection aConnection) { if (aConnection.CanConnect()) { aConnection.CreateTable <Entry>(); aConnection.CreateTable <PhoneBook>(); } }
public void GetUnitsForAColumn() { CreateTable(database); // Create a _Units table. var columnNames = new List <string>() { "TableName", "ColumnHeading", "Units" }; var columnTypes = new List <string>() { "char(50)", "char(50)", "char(50)" }; database.CreateTable("_Units", columnNames, columnTypes); var rows = new List <object[]> { new object[] { "Report", "Col1", "g" }, new object[] { "Report", "Col2", "g/m2" }, new object[] { "Report2", "Col1", "t/ha" }, new object[] { "Report2", "Col2", "t" } }; database.InsertRows("_Units", columnNames, rows); DataStoreReader reader = new DataStoreReader(database); string units = reader.Units(tableName: "Report", columnHeading: "Col2"); Assert.AreEqual(units, "g/m2"); }
/// <summary>Create a table that matches the specified table.</summary> /// <param name="table">The table definition to write to the database.</param> private void CreateTable(DataTable table) { List <string> colTypes = new List <string>(); foreach (DataColumn column in table.Columns) { columnNamesInDb.Add(column.ColumnName); bool allowLongStrings = table.TableName.StartsWith("_"); colTypes.Add(connection.GetDBDataTypeName(column.DataType, allowLongStrings)); } connection.CreateTable(Name, columnNamesInDb.ToList(), colTypes); if (table.Columns.Contains("ID") && table.TableName.StartsWith("_")) { connection.CreateIndex(table.TableName, new List <string>() { "ID" }, true); } else if (table.Columns.Contains("CheckpointID") && table.Columns.Contains("SimulationID")) { connection.CreateIndex(table.TableName, new List <string>() { "CheckpointID", "SimulationID" }, false); } TableExistsInDb = true; }
/// <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>Create a table that matches the specified table.</summary> /// <param name="table">The table definition to write to the database.</param> private void CreateTable(DataTable table) { List <string> colTypes = new List <string>(); foreach (DataColumn column in table.Columns) { columnNamesInDb.Add(column.ColumnName); colTypes.Add(connection.GetDBDataTypeName(column.DataType)); } connection.CreateTable(Name, columnNamesInDb.ToList(), colTypes); TableExistsInDb = true; }
/// <summary>Ensure columns exist in .db file</summary> /// <param name="connection">The database connection to write to</param> private void CreateTable(IDatabaseConnection connection) { List <string> colNames = new List <string>(); List <string> colTypes = new List <string>(); foreach (Column col in Columns) { colNames.Add(col.Name); colTypes.Add(col.DatabaseDataType); } lock (lockObject) { connection.CreateTable(Name, colNames, colTypes); } }
/// <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 }); } }
/// <summary>Create a table</summary> /// <param name="database">The db to create the table in.</param> /// <param name="tableName">The name of the table to create.</param> /// <param name="columnNames">The column names.</param> /// <param name="columnTypes">The column types.</param> /// <param name="rowValues">The row values to insert into the table.</param> private void CreateTable(IDatabaseConnection database, string tableName, List <string> columnNames, List <string> columnTypes, List <object[]> rowValues) { database.CreateTable(tableName, columnNames, columnTypes); database.InsertRows(tableName, columnNames, rowValues); }
/// <summary>Create a table that we can test</summary> public static void CreateTable(IDatabaseConnection database) { // Create a _Checkpoints table. List <string> columnNames = new List <string>() { "ID", "Name", "Version", "Date", "OnGraphs" }; List <string> columnTypes = new List <string>() { "integer", "char(50)", "char(50)", "date", "integer" }; database.CreateTable("_Checkpoints", columnNames, columnTypes); List <object[]> rows = new List <object[]> { new object[] { 1, "Current", string.Empty, string.Empty }, new object[] { 2, "Saved1", string.Empty, string.Empty } }; database.InsertRows("_Checkpoints", columnNames, rows); // Create a _Simulations table. columnNames = new List <string>() { "ID", "Name", "FolderName" }; columnTypes = new List <string>() { "integer", "char(50)", "char(50)" }; database.CreateTable("_Simulations", columnNames, columnTypes); rows = new List <object[]> { new object[] { 1, "Sim1", string.Empty }, new object[] { 2, "Sim2", string.Empty } }; database.InsertRows("_Simulations", columnNames, rows); // Create a Report table. columnNames = new List <string>() { "CheckpointID", "SimulationID", "Col1", "Col2" }; columnTypes = new List <string>() { "integer", "integer", "date", "real" }; database.CreateTable("Report", columnNames, columnTypes); rows = new List <object[]> { new object[] { 1, 1, new DateTime(2017, 1, 1), 1.0 }, new object[] { 1, 1, new DateTime(2017, 1, 2), 2.0 }, new object[] { 2, 1, new DateTime(2017, 1, 1), 11.0 }, new object[] { 2, 1, new DateTime(2017, 1, 2), 12.0 }, new object[] { 1, 2, new DateTime(2017, 1, 1), 21.0 }, new object[] { 1, 2, new DateTime(2017, 1, 2), 22.0 }, new object[] { 2, 2, new DateTime(2017, 1, 1), 31.0 }, new object[] { 2, 2, new DateTime(2017, 1, 2), 32.0 } }; database.InsertRows("Report", columnNames, rows); }
/// <summary>Create a table that we can test</summary> private static void CreateTable(IDatabaseConnection database) { // Create a _Checkpoints table. List <string> columnNames = new List <string>() { "ID", "Name", "Version", "Date", "OnGraphs" }; List <string> columnTypes = new List <string>() { "integer", "char(50)", "char(50)", "date", "integer" }; database.CreateTable("_Checkpoints", columnNames, columnTypes); List <object[]> rows = new List <object[]> { new object[] { 1, "Current", string.Empty, string.Empty } }; database.InsertRows("_Checkpoints", columnNames, rows); // Create a _Simulations table. columnNames = new List <string>() { "ID", "Name", "FolderName" }; columnTypes = new List <string>() { "integer", "char(50)", "char(50)" }; database.CreateTable("_Simulations", columnNames, columnTypes); rows = new List <object[]> { new object[] { 1, "Sim1", string.Empty }, new object[] { 2, "Sim2", string.Empty } }; database.InsertRows("_Simulations", columnNames, rows); // Create a Report table. columnNames = new List <string>() { "CheckpointID", "SimulationID", "Col1", "Col2" }; columnTypes = new List <string>() { "integer", "integer", "integer", "integer" }; database.CreateTable("Report", columnNames, columnTypes); rows = new List <object[]> { new object[] { 1, 1, 1, 8 }, new object[] { 1, 1, 2, 9 }, new object[] { 1, 1, 3, 10 }, new object[] { 1, 2, 4, 11 }, new object[] { 1, 2, 5, 12 }, new object[] { 1, 2, 6, 13 }, new object[] { 1, 2, 7, 14 } }; database.InsertRows("Report", columnNames, rows); // Create a _Units table. columnNames = new List <string>() { "TableName", "ColumnHeading", "Units" }; columnTypes = new List <string>() { "char(50)", "char(50)", "char(50)" }; database.CreateTable("_Units", columnNames, columnTypes); rows = new List <object[]> { new object[] { "Report", "Col1", "g" } }; database.InsertRows("_Units", columnNames, rows); }