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