private async Task UpdateTable(DatabaseTableRequestData oldTable, DatabaseTableRequestData newTable)
{
var updateColumns = newTable.Columns.Keys.Where(k => oldTable.Columns.Keys.Contains(k) &&
(oldTable.Columns[k].DataType != newTable.Columns[k].DataType ||
oldTable.Columns[k].IsAutoIncrement != newTable.Columns[k].IsAutoIncrement));
var newColumns = newTable.Columns.Keys.Where(k => !oldTable.Columns.Keys.Contains(k));
var deletedColumns = oldTable.Columns.Keys.Where(k => !newTable.Columns.Keys.Contains(k));
var newColumnDefinitions = newColumns.Select(k => $"ADD COLUMN {k} {newTable.Columns[k].DataType}{(newTable.Columns[k].IsAutoIncrement ? " AUTO_INCREMENT" : null)}");
var deletedColumnDefinitions = deletedColumns.Select(k => $"DROP COLUMN {k}");
var updateColumnDefinitions = updateColumns.Select(k => $"MODIFY COLUMN {k} {newTable.Columns[k].DataType}{(newTable.Columns[k].IsAutoIncrement ? " AUTO_INCREMENT" : null)}");
var alterDefinitions = new List <string>(deletedColumnDefinitions.Concat(newColumnDefinitions).Concat(updateColumnDefinitions));
if (oldTable.Name != newTable.Name)
{
alterDefinitions.Add($"RENAME TO {newTable.Name}");
}
var sql = $"ALTER TABLE {oldTable.Name} {string.Join(',', alterDefinitions)};";
LambdaLogger.Log($"Executing SQL: [{sql}]");
using (var connection = new MySqlConnection(ConnectionString))
{
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
}
}
private async Task CreateTable(DatabaseTableRequestData tableData)
{
var columnDefinitions = tableData.Columns.Select(column =>
$"{column.Key} {column.Value.DataType}{(column.Value.IsAutoIncrement ? " AUTO_INCREMENT" : null)}");
var primaryKeyColumns = tableData.Columns.Where(kvp => kvp.Value.IsPrimaryKey).Select(kvp => kvp.Key).ToArray();
var sql = $"CREATE TABLE {tableData.Name}(";
sql += string.Join(',', columnDefinitions);
if (primaryKeyColumns.Any())
{
var primaryKeyDefinition = string.Join(',', primaryKeyColumns);
sql += $",PRIMARY KEY({primaryKeyDefinition})";
}
sql += ");";
LambdaLogger.Log($"Executing SQL: [{sql}]");
using (var connection = new MySqlConnection(ConnectionString))
{
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
}
}
private async Task DropTable(DatabaseTableRequestData tableData)
{
var sql = $"DROP TABLE IF EXISTS {tableData.Name}";
LambdaLogger.Log($"Executing SQL: [{sql}]");
using (var connection = new MySqlConnection(ConnectionString))
{
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
}
}