public void AddIndex()
{
var previousTable1 = GetTable1();
var previousTable2 = GetTable2();
var previous = new List <Statement>()
{
previousTable1,
previousTable2,
};
var nextTable1 = GetTable1();
var nextTable2 = GetTable2();
var next = new List <Statement>()
{
nextTable1,
nextTable2,
};
previousTable1.Columns.RemoveAll(c => c is TableKey cc && cc.Name == "test_key_1");
var upSql = DatabaseDiff.Diff(next, previous);
Assert.AreEqual("CREATE UNIQUE INDEX `test_key_1` ON `test` (`inventory`);\r\n", upSql);
var downSql = DatabaseDiff.Diff(previous, next);
Console.WriteLine(downSql);
Assert.AreEqual("DROP INDEX `test_key_1` ON `test`;\r\n", downSql);
}
public void AddColumn()
{
var previousTable1 = GetTable1();
var previousTable2 = GetTable2();
var previous = new List <Statement>()
{
previousTable1,
previousTable2,
};
var nextTable1 = GetTable1();
var nextTable2 = GetTable2();
var next = new List <Statement>()
{
nextTable1,
nextTable2,
};
previousTable1.Columns.RemoveAll(c => c is TableColumn cc && cc.Name == "name");
var upSql = DatabaseDiff.Diff(next, previous);
Assert.AreEqual("ALTER TABLE `test` ADD COLUMN `name` varchar(127);\r\n", upSql);
var downSql = DatabaseDiff.Diff(previous, next);
Assert.AreEqual("ALTER TABLE `test` DROP COLUMN `name`;\r\n", downSql);
}
public void AddConstraint()
{
var previousTable1 = GetTable1();
var previousTable2 = GetTable2();
var previous = new List <Statement>()
{
previousTable1,
previousTable2,
};
var nextTable1 = GetTable1();
var nextTable2 = GetTable2();
var next = new List <Statement>()
{
nextTable1,
nextTable2,
};
previousTable1.Columns.RemoveAll(c => c is TableConstraint cc && cc.Name == "test_constraint_1");
var upSql = DatabaseDiff.Diff(next, previous);
Assert.AreEqual("ALTER TABLE `test` ADD CONSTRAINT `test_constraint_1` FOREIGN KEY (`other_id`) REFERENCES `other` (`other_id`) ON UPDATE RESTRICT ON DELETE RESTRICT;\r\n", upSql);
var downSql = DatabaseDiff.Diff(previous, next);
Assert.AreEqual("ALTER TABLE `test` DROP FOREIGN KEY `test_constraint_1`;\r\n", downSql);
}
public void InsertRowAndChangePk()
{
var previousTable1 = GetTable1();
var previousTable2 = GetTable2();
var previous = new List <Statement>()
{
GetTable1(),
GetTable2(),
// GetInsert1(),
};
var nextTable1 = GetTable1();
var nextTable2 = GetTable2();
var next = new List <Statement>()
{
nextTable1,
nextTable2,
GetInsert1(),
};
var pk = (TableKey)nextTable1.Columns.Where(c => c is TableKey ck && ck.Primary).First();
pk.Columns.Add("shelf_id");
var upSql = DatabaseDiff.Diff(next, previous);
Assert.AreEqual(ReadEmbeddedRessourceToString("InsertRowAndChangePk.up.sql"), upSql);
var downSql = DatabaseDiff.Diff(previous, next);
Assert.AreEqual("DELETE FROM `test` WHERE `test_id` = 1;\r\n\r\nDELETE FROM `test` WHERE `test_id` = 2;\r\n\r\n", downSql);
}
public void InsertRow()
{
var previous = new List <Statement>()
{
GetTable1(),
GetTable2(),
// GetInsert1(),
};
var nextTable1 = GetTable1();
var nextTable2 = GetTable2();
var next = new List <Statement>()
{
nextTable1,
nextTable2,
GetInsert1(),
};
var upSql = DatabaseDiff.Diff(next, previous);
Assert.AreEqual("INSERT INTO `test`(\r\n`test_id`, `shelf_id`, `name`, `inventory`) VALUES\r\n(1, DEFAULT, \"Name1\", DEFAULT),\r\n(2, DEFAULT, \"Name2\", DEFAULT);\r\n\r\n", upSql);
var downSql = DatabaseDiff.Diff(previous, next);
Assert.AreEqual("DELETE FROM `test` WHERE `test_id` = 1;\r\n\r\nDELETE FROM `test` WHERE `test_id` = 2;\r\n\r\n", downSql);
}
public override int Run(string[] remainingArguments)
{
var sourceDb = new Database();
var targetDb = new Database();
sourceDb.Connection = _source;
targetDb.Connection = _target;
sourceDb.Load();
targetDb.Load();
DatabaseDiff diff = sourceDb.Compare(targetDb);
if (diff.IsDiff)
{
Console.WriteLine("Databases are different.");
Console.WriteLine(diff.SummarizeChanges(_verbose));
if (!string.IsNullOrEmpty(_outDiff))
{
Console.WriteLine();
if (!_overwrite && File.Exists(_outDiff))
{
if (!ConsoleQuestion.AskYN(string.Format("{0} already exists - do you want to replace it", _outDiff)))
{
return(1);
}
}
File.WriteAllText(_outDiff, diff.Script());
Console.WriteLine("Script to make the databases identical has been created at {0}", Path.GetFullPath(_outDiff));
}
return(1);
}
Console.WriteLine("Databases are identical.");
return(0);
}
public static void RegisterWithApp(CommandLineApplication app)
{
app.Command("diff", cmdApp =>
{
cmdApp.Description = "Generates SQL with the difference between the source and target projects." + Environment.NewLine +
"Used to generate up/down migration SQL, or SQL to rebuild a database from scratch (using --source null)." + Environment.NewLine +
"The generated SQL can be executed by the MySQL command line client.";
var sourceOptions = RegisterProjectOptions(cmdApp, "source");
var targetOptions = RegisterProjectOptions(cmdApp, "target");
cmdApp.OnExecute(() =>
{
var sourceProject = GetProjectFromArguments(cmdApp, "source", sourceOptions);
if (sourceProject == null)
{
return(1);
}
var targetProject = GetProjectFromArguments(cmdApp, "target", targetOptions);
if (targetProject == null)
{
return(2);
}
var sql = DatabaseDiff.Diff(targetProject.Statements, sourceProject.Statements);
Console.WriteLine(sql);
return(0);
});
});
}
public void TestDiffScript()
{
TestHelper.DropDb("TEST_SOURCE");
TestHelper.DropDb("TEST_COPY");
//create the dbs from sql script
string script = File.ReadAllText(ConfigHelper.TestSchemaDir + "\\BOP_QUOTE.sql");
TestHelper.ExecSql("CREATE DATABASE TEST_SOURCE", "");
TestHelper.ExecBatchSql(script, "TEST_SOURCE");
script = File.ReadAllText(ConfigHelper.TestSchemaDir + "\\BOP_QUOTE_2.sql");
TestHelper.ExecSql("CREATE DATABASE TEST_COPY", "");
TestHelper.ExecBatchSql(script, "TEST_COPY");
var source = new Database("TEST_SOURCE");
source.Connection = TestHelper.GetConnString("TEST_SOURCE");
source.Load();
var copy = new Database("TEST_COPY");
copy.Connection = TestHelper.GetConnString("TEST_COPY");
copy.Load();
//execute migration script to make SOURCE the same as COPY
DatabaseDiff diff = copy.Compare(source);
TestHelper.ExecBatchSql(diff.Script(), "TEST_SOURCE");
//compare the dbs to make sure they are the same
string cmd = string.Format("/c {0}\\SQLDBDiffConsole.exe {1} {2} {0}\\{3}", ConfigHelper.SqlDbDiffPath,
"localhost\\SQLEXPRESS TEST_COPY NULL NULL Y", "localhost\\SQLEXPRESS TEST_SOURCE NULL NULL Y",
"SqlDbDiff.XML CompareResult.txt null");
var proc = new Process();
proc.StartInfo.FileName = "cmd.exe";
proc.StartInfo.Arguments = cmd;
proc.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
proc.Start();
proc.WaitForExit();
Assert.AreEqual("no difference", File.ReadAllLines("CompareResult.txt")[0]);
}
