public static void SaveAsNewDirectory(DbProject project, string projectDirectory)
{
Directory.CreateDirectory(Path.Combine(projectDirectory, "triggers"));
Directory.CreateDirectory(Path.Combine(projectDirectory, "tables"));
Directory.CreateDirectory(Path.Combine(projectDirectory, "procedures"));
Directory.CreateDirectory(Path.Combine(projectDirectory, "states"));
Directory.CreateDirectory(Path.Combine(projectDirectory, "seeds"));
var seeds = new HashSet <string>();
foreach (var statement in project.Statements)
{
if (statement is CreateTableStatement createTable)
{
var sb = new StringBuilder();
DatabaseDiff.WriteCreateTable(createTable, sb);
var path = Path.Combine(projectDirectory, "tables", createTable.TableName + ".sql");
File.WriteAllText(path, sb.ToString());
}
else if (statement is CreateTriggerStatement createTrigger)
{
var sb = new StringBuilder();
DatabaseDiff.WriteTrigger(createTrigger, sb);
var path = Path.Combine(projectDirectory, "triggers", createTrigger.TriggerName + ".sql");
File.WriteAllText(path, sb.ToString());
}
else if (statement is CreateProcedureStatement createProcedure)
{
var sb = new StringBuilder();
DatabaseDiff.WriteProcedure(createProcedure, sb);
var path = Path.Combine(projectDirectory, "procedures", createProcedure.Name + ".sql");
File.WriteAllText(path, sb.ToString());
}
else if (statement is InsertStatement insert)
{
var sb = new StringBuilder();
DatabaseDiff.WriteInsert(insert, sb);
// Unique seed name: group seeds by table name
var path = Path.Combine(projectDirectory, "seeds", insert.TableName + ".sql");
if (seeds.Contains(insert.TableName))
{
File.AppendAllText(path, sb.ToString());
}
else
{
File.WriteAllText(path, sb.ToString());
seeds.Add(insert.TableName);
}
}
else
{
throw new InvalidOperationException("unhandled");
}
}
}
public static void DataDiff(List <Statement> next, List <Statement> previous, List <TableData> nextData, List <TableData> previousData, StringBuilder sql)
{
// Find deleted rows
foreach (var previousTable in previousData)
{
var nextCreateTable = (CreateTableStatement)next.Where(p => p is CreateTableStatement cp && cp.TableName == previousTable.TableName).FirstOrDefault();
if (nextCreateTable == null)
{
// Table exists in prev, not in next: Skip delete, table will be dropped
continue;
}
List <List <TableCellData> > nextRows =
nextData
.Where(t => t.TableName == previousTable.TableName)
.Select(t => t.Rows)
.FirstOrDefault()
?? new List <List <TableCellData> >();
var previousPkNames = GetPrimaryKeys(previous, previousTable.TableName, out var previousColumns);
var nextPkNames = GetPrimaryKeys(next, previousTable.TableName, out var nextColumns);
// dont do this in initial create:
//if (previousPkNames.Count != nextPkNames.Count)
//{
// // in this case delete all and reinsert! we cannot reliable map these
// throw new InvalidOperationException("TODO: number of primary keys changed");
//}
// dont do this in initial create:
var intersect = previousPkNames.Intersect(nextPkNames).ToList();
//if (intersect.Count != previousPkNames.Count)
//{
// throw new InvalidOperationException("TODO: primary keys changed");
//}
foreach (var previousRow in previousTable.Rows)
{
// find row in next med samme pk
var previousPkColumns = previousRow.Where(c => previousPkNames.Contains(c.ColumnName)).ToList();
if (previousPkColumns.Count == 0)
{
throw new InvalidOperationException("No primary key(s) in " + previousTable.TableName);
}
var nextPk = GetRowByPk(nextRows, previousPkColumns);
if (nextPk == null)
{
// row exists in prev, but not in next = delete
sql.Append($"DELETE FROM `{previousTable.TableName}` WHERE ");
DatabaseDiff.WriteWhere(sql, previousPkNames, previousPkColumns);
sql.AppendLine(";");
sql.AppendLine();
}
else
{
// row exists in both; detect changes later
}
}
}
// Find new and updated rows
foreach (var nextTable in nextData)
{
var insertSql = new StringBuilder();
var updateSql = new StringBuilder();
var insertTables = new HashSet <string>();
List <List <TableCellData> > previousRows =
previousData
.Where(t => t.TableName == nextTable.TableName)
.Select(t => t.Rows)
.FirstOrDefault()
?? new List <List <TableCellData> >();
var previousPkNames = GetPrimaryKeys(previous, nextTable.TableName, out var previousColumns);
var nextPkNames = GetPrimaryKeys(next, nextTable.TableName, out var nextColumns);
// dont do this in initial create:
//if (previousPkNames.Count != nextPkNames.Count)
//{
// throw new InvalidOperationException("TODO: number of primary keys changed");
//}
var intersectCount = previousPkNames.Intersect(nextPkNames).Count();
if (intersectCount != previousPkNames.Count)
{
throw new InvalidOperationException("TODO: primary keys changed");
}
foreach (var nextRow in nextTable.Rows)
{
// find row in next med samme pk
var nextPkColumns = nextRow.Where(c => previousPkNames.Contains(c.ColumnName)).ToList();
if (nextPkColumns.Count != previousPkNames.Count)
{
throw new InvalidOperationException("Insert row did not specify a value for all primary keys in " + nextTable.TableName);
}
// dont do this in initial create:
//if (nextPkColumns.Count == 0)
//{
// throw new InvalidOperationException("No primary key(s) in " + nextTable.TableName);
//}
var previousPkRow = GetRowByPk(previousRows, nextPkColumns);
if (previousPkRow == null)
{
// row exists in next, but not in prev = insert
if (!insertTables.Contains(nextTable.TableName))
{
insertTables.Add(nextTable.TableName);
insertSql.AppendLine("INSERT INTO `" + nextTable.TableName + "`(");
DatabaseDiff.WriteColumnNames(nextColumns.Select(c => c.Name).ToList(), insertSql);
// insertSql.Append(string.Join(", ", nextColumns.Select(c => c.Name)));
insertSql.AppendLine(") VALUES");
}
else
{
insertSql.AppendLine(",");
}
//sql.AppendLine("INSERT INTO `" + nextTable.TableName + "`(");
//sql.Append(string.Join(", ", nextColumns.Select(c => c.Name)));
//sql.AppendLine(") VALUES (");
insertSql.Append("(");
for (var i = 0; i < nextColumns.Count; i++)
{
if (i > 0)
{
insertSql.Append(", ");
}
var cell = nextRow.Where(r => r.ColumnName == nextColumns[i].Name).FirstOrDefault();
if (cell != null)
{
insertSql.Append(cell.Token);
}
else
{
insertSql.Append("DEFAULT");
}
}
insertSql.Append(")");
// sql.AppendLine(");");
// sql.AppendLine();
}
else
{
// row exists in both, check if update
// if any of the columns are different -> update, check deleted, then new/edited
var updates = new List <TableCellData>();
foreach (var nextColumn in nextRow)
{
var p = previousPkRow.Where(c => c.ColumnName == nextColumn.ColumnName).FirstOrDefault();
if (p == null || p.Token != nextColumn.Token)
{
updates.Add(nextColumn);
}
}
if (updates.Count > 0)
{
updateSql.Append("UPDATE " + nextTable.TableName + " SET ");
for (var i = 0; i < updates.Count; i++)
{
if (i > 0)
{
updateSql.Append(", ");
}
var update = updates[i];
if (nextPkNames.Contains(update.ColumnName))
{
continue;
}
updateSql.Append(update.ColumnName);
updateSql.Append(" = ");
updateSql.Append(update.Token);
}
updateSql.Append(" WHERE ");
DatabaseDiff.WriteWhere(updateSql, nextPkNames, nextPkColumns);
updateSql.AppendLine(";");
updateSql.AppendLine();
}
}
}
if (insertSql.Length > 0)
{
insertSql.AppendLine(";");
insertSql.AppendLine();
}
sql.Append(insertSql);
sql.Append(updateSql);
}
}