private List <string> ExtractCommonData(string srcDBName, string destDBName, List <TableDefinition> definitions)
{
List <string> sqlCommandList = new List <string>();
TableNames.Where(tableName => !IsTableMatched(tableName, definitions)).ToList().ForEach(tableName =>
{
// copy table now.
string sqlCommandText = $"CREATE TABLE IF NOT EXISTS `{destDBName}`.`{tableName}` LIKE `{srcDBName}`.`{tableName}`; INSERT INTO `{destDBName}`.`{tableName}` SELECT {tableName}.* FROM `{srcDBName}`.`{tableName}`";
sqlCommandList.Add(sqlCommandText);
});
return(sqlCommandList);
}
private List <string> ExtractUnitData(string srcDBName, string destDBName, List <TableDefinition> definitions, Dictionary <string, object> unitDataDictionary)
{
List <string> sqlCommandList = new List <string>();
TableNames.Where(tableName => IsTableMatched(tableName, definitions)).ToList().ForEach(tableName =>
{
var filteredDefinitions = GetMatchedTableDefinitions(tableName, definitions);
filteredDefinitions.ForEach(definition =>
{
if (definition.Type == DefinitionType.Skip)
{
return; // Skip: continue to next definition.
}
// Default: Copy whole table.
string sqlCommandText = $"CREATE TABLE IF NOT EXISTS `{destDBName}`.`{tableName}` LIKE `{srcDBName}`.`{tableName}`; " +
$"INSERT INTO `{destDBName}`.`{tableName}` " +
$"SELECT {tableName}.* FROM `{srcDBName}`.`{tableName}` ";
// Extract: Append Join & Where
if (definition.Type == DefinitionType.Extract)
{
string value = unitDataDictionary[definition.Value].ToString();
string extraWhereClause = string.IsNullOrWhiteSpace(definition.ExtraWhereClause) ? "" : $"AND {definition.ExtraWhereClause}";
if (string.IsNullOrWhiteSpace(definition.DataClassTable))
{
sqlCommandText += $"JOIN `{srcDBName}`.`{definition.ForeignTable}` ON {tableName}.{definition.ForeignKey} = {definition.ForeignTablePrimaryKey} " +
$"WHERE {definition.Filter} {definition.Operator} '{value}' {extraWhereClause}";
}
else
{
// If DataClassTable exists:
// SELECT * FROM Table
// JOIN DataClassTable ON Table.dataId = DataClassTable.id
// JOIN ForeignTable ON DataClassTable.Column = ForeignTable.FK
sqlCommandText += $"JOIN `{srcDBName}`.`{definition.DataClassTable}` ON {tableName}.dataId = {definition.DataClassTable}.id " +
$"JOIN `{srcDBName}`.`{definition.ForeignTable}` ON {definition.DataClassTable}.{definition.ForeignKey} = {definition.ForeignTablePrimaryKey} " +
$"WHERE {definition.Filter} {definition.Operator} '{value}' {extraWhereClause}";
}
}
sqlCommandList.Add(sqlCommandText);
});
});
return(sqlCommandList);
}
public CodeTableEntries this[string key]
{
get
{
if (!_tableEntriesDict.ContainsKey(key))
{
KeyValuePair <int, string> table = TableNames.Where(t => t.Value.Equals(key)).FirstOrDefault();
if (!table.Equals(default(KeyValuePair <int, string>)))
{
_tableEntriesDict.Add(table.Value, new CodeTableEntries(table.Key));
}
}
return(_tableEntriesDict[key]);
}
}
