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