public void Updates_database()
{
var settings = new ConnectionSettings("server", "db", true, null, null);
var taskAttributes = new TaskAttributes(settings, "c:\\scripts");
taskAttributes.RequestedDatabaseAction = RequestedDatabaseAction.Update;
var mocks = new MockRepository();
var scriptfolderexecutor = mocks.StrictMock<IScriptFolderExecutor>();
var queryexecutor = mocks.StrictMock<IQueryExecutor>();
queryexecutor.Stub(x => x.CheckDatabaseExists(taskAttributes.ConnectionSettings)).Return(true);
var taskObserver = mocks.StrictMock<ITaskObserver>();
using (mocks.Record())
{
taskObserver.Log(string.Format("Run scripts in Update folder."));
scriptfolderexecutor.ExecuteScriptsInFolder(taskAttributes, "Update", taskObserver);
taskObserver.Log(string.Format("Run scripts in Everytime folder."));
scriptfolderexecutor.ExecuteChangedScriptsInFolder(taskAttributes, "Everytime", taskObserver);
taskObserver.Log(string.Format("Run scripts in RunAlways folder."));
scriptfolderexecutor.ExecuteRunAlwaysScriptsInFolder(taskAttributes, "RunAlways", taskObserver);
}
using (mocks.Playback())
{
IDatabaseActionExecutor updater = new DatabaseUpdater(scriptfolderexecutor, queryexecutor);
updater.Execute(taskAttributes, taskObserver);
}
mocks.VerifyAll();
}
/// <summary>
/// Runs larger queries that may be multiline separated with GO
/// Runs entire sql block in a single transaction that will rollback if any part of the query errors
/// </summary>
/// <param name="settings"></param>
/// <param name="sql"></param>
public void ExecuteNonQueryTransactional(ConnectionSettings settings, string sql)
{
//do all this in a single transaction
using (var scope = new TransactionScope())
{
string connectionString = _connectionStringGenerator.GetConnectionString(settings, true);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandTimeout = 0;
var scripts = SplitSqlStatements(sql);
foreach (var splitScript in scripts)
{
command.CommandText = splitScript;
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
ex.Data.Add("Custom", "Erroring script was run in a transaction and was rolled back.");
throw ex;
}
}
}
scope.Complete();
}
}
}
public void Should_not_fail_if_datebase_does_not_exist()
{
var settings = new ConnectionSettings("server", "db", true, null, null);
var taskAttributes = new TaskAttributes(settings, null);
var mocks = new MockRepository();
var connectionDropper = mocks.DynamicMock<IDatabaseConnectionDropper>();
var taskObserver = mocks.StrictMock<ITaskObserver>();
var queryExecutor = mocks.StrictMock<IQueryExecutor>();
using (mocks.Record())
{
Expect.Call(() => taskObserver.Log("Running against: SQL Server"));
Expect.Call(queryExecutor.ReadFirstColumnAsStringArray(settings, "select @@version")).Return(new string[] { "SQL Server" });
Expect.Call(() => taskObserver.Log("Dropping database: db\n"));
Expect.Call(() => queryExecutor.ExecuteNonQuery(settings, "ALTER DATABASE [db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE drop database [db]"))
.Throw(new Exception("foo message"));
Expect.Call(() => taskObserver.Log("Database 'db' could not be dropped."));
}
using (mocks.Playback())
{
IDatabaseActionExecutor dropper = new DatabaseDropper(connectionDropper, queryExecutor);
dropper.Execute(taskAttributes, taskObserver);
}
mocks.VerifyAll();
}
public void Update_Database_Runs_New_Everytime_Script()
{
//arrange
string scriptsDirectory = Path.Combine("Scripts",GetType().Name.Replace("Tester", ""));
string scriptFileMd5 = ChangeScriptExecutor.GetFileMD5Hash(Path.Combine(scriptsDirectory,"Everytime", "TestScript.sql"));
var settings = new ConnectionSettings(".\\sqlexpress", "aliasqltest", true, null, null);
new ConsoleAliaSQL().UpdateDatabase(settings, scriptsDirectory, RequestedDatabaseAction.Drop);
//act
bool success = new ConsoleAliaSQL().UpdateDatabase(settings, scriptsDirectory, RequestedDatabaseAction.Update);
//assert
int records = 0;
AssertUsdAppliedDatabaseScriptTable(settings, reader =>
{
while (reader.Read())
{
records++;
reader["ScriptFile"].ShouldEqual("TestScript.sql");
reader["hash"].ShouldEqual(scriptFileMd5);
}
});
success.ShouldEqual(true);
records.ShouldEqual(1);
}
public void ExecuteIfChanged(string fullFilename, ConnectionSettings settings, ITaskObserver taskObserver, bool logOnly = false)
{
string scriptFilename = getFilename(fullFilename);
var scriptFileMD5 = GetFileMD5Hash(fullFilename);
if (_executionTracker.EverytimeScriptShouldBeExecuted(settings, scriptFilename, scriptFileMD5))
{
if (!logOnly)
{
string sql = _fileSystem.ReadTextFile(fullFilename);
taskObserver.Log(string.Format("Executing: {0}{1}", getLastFolderName(fullFilename), scriptFilename));
_executor.ExecuteNonQuery(settings, sql, true);
}
else
{
taskObserver.Log(string.Format("Executing: {0}{1} in log only mode", getLastFolderName(fullFilename), scriptFilename));
}
_executionTracker.MarkScriptAsExecuted(settings, scriptFilename, taskObserver, scriptFileMD5);
}
else
{
taskObserver.Log(string.Format("Skipping (unchanged): {0}{1}", getLastFolderName(fullFilename), scriptFilename));
}
}
public void Manages_database()
{
var settings = new ConnectionSettings("server", "db", true, null, null);
var scriptDirectory = @"c:\scripts";
var taskAttributes = new TaskAttributes(settings, scriptDirectory);
taskAttributes.RequestedDatabaseAction= RequestedDatabaseAction.Create;
var mocks = new MockRepository();
var taskObserver = mocks.StrictMock<ITaskObserver>();
var generator = mocks.StrictMock<ILogMessageGenerator>();
var factory = mocks.StrictMock<IDatabaseActionExecutorFactory>();
var creator = mocks.StrictMock<IDatabaseActionExecutor>();
var updater = mocks.StrictMock<IDatabaseActionExecutor>();
var executors = new IDatabaseActionExecutor[] { creator, updater };
using (mocks.Record())
{
Expect.Call(generator.GetInitialMessage(taskAttributes)).Return("starting...");
taskObserver.Log("starting...");
Expect.Call(factory.GetExecutors(RequestedDatabaseAction.Create)).Return(executors);
creator.Execute(taskAttributes, taskObserver);
updater.Execute(taskAttributes, taskObserver);
}
using (mocks.Playback())
{
ISqlDatabaseManager manager = new SqlDatabaseManager(generator, factory);
manager.Upgrade(taskAttributes, taskObserver);
}
mocks.VerifyAll();
}
public void Execute(string fullFilename, ConnectionSettings settings, ITaskObserver taskObserver, bool logOnly = false)
{
string scriptFilename = getFilename(fullFilename);
if (_executionTracker.ScriptAlreadyExecuted(settings, scriptFilename))
{
taskObserver.Log(string.Format("Skipping (already executed): {0}{1}", getLastFolderName(fullFilename),scriptFilename));
}
else
{
if (!logOnly)
{
string sql = _fileSystem.ReadTextFile(fullFilename);
if (!ScriptSupportsTransactions(sql))
{
taskObserver.Log(string.Format("Executing: {0}{1}", getLastFolderName(fullFilename),scriptFilename));
_executor.ExecuteNonQuery(settings, sql, true);
}
else
{
taskObserver.Log(string.Format("Executing: {0}{1} in a transaction", getLastFolderName(fullFilename),scriptFilename));
_executor.ExecuteNonQueryTransactional(settings, sql);
}
}
else
{
taskObserver.Log(string.Format("Executing: {0}{1} in log only mode",getLastFolderName(fullFilename), scriptFilename));
}
_executionTracker.MarkScriptAsExecuted(settings, scriptFilename, taskObserver);
}
}
public void Executes_all_the_scripts_within_a_folder()
{
var settings = new ConnectionSettings("server", "db", true, null, null);
var sqlFiles = new[] { "c:\\scripts\\Update\\001.sql", "c:\\scripts\\Update\\002_data_.sql", "c:\\scripts\\Update\\003.sql" };
var taskAttributes = new TaskAttributes(settings, "c:\\scripts")
{
RequestedDatabaseAction = RequestedDatabaseAction.Update,
};
var mocks = new MockRepository();
var initializer = mocks.StrictMock<ISchemaInitializer>();
var fileLocator = mocks.StrictMock<ISqlFileLocator>();
var executor = mocks.StrictMock<IChangeScriptExecutor>();
var testdataexecutor = mocks.StrictMock<ITestDataScriptExecutor>();
var versioner = mocks.StrictMock<IDatabaseVersioner>();
var taskObserver = mocks.StrictMock<ITaskObserver>();
using (mocks.Record())
{
initializer.EnsureSchemaCreated(settings);
Expect.Call(fileLocator.GetSqlFilenames("c:\\scripts", "Update")).Return(sqlFiles);
executor.Execute("c:\\scripts\\Update\\001.sql", settings, taskObserver);
executor.Execute("c:\\scripts\\Update\\002_data_.sql", settings, taskObserver);
executor.Execute("c:\\scripts\\Update\\003.sql", settings, taskObserver);
versioner.VersionDatabase(settings, taskObserver);
}
using (mocks.Playback())
{
IScriptFolderExecutor folderExecutor = new ScriptFolderExecutor(initializer, fileLocator, executor, testdataexecutor, versioner);
folderExecutor.ExecuteScriptsInFolder(taskAttributes, "Update", taskObserver);
}
mocks.VerifyAll();
}
public void Update_Database_Runs_Changed_Everytime_Script()
{
//arrange
string scriptsDirectory = Path.Combine("Scripts", GetType().Name.Replace("Tester", ""));
var settings = new ConnectionSettings(".\\sqlexpress", "aliasqltest", true, null, null);
new ConsoleAliaSQL().UpdateDatabase(settings, scriptsDirectory, RequestedDatabaseAction.Drop);
//act
//run once
new ConsoleAliaSQL().UpdateDatabase(settings, scriptsDirectory, RequestedDatabaseAction.Update).ShouldBeTrue();
new QueryExecutor().ExecuteScalarInteger(settings, "select 1 from dbo.sysobjects where name = 'TestTable' and type='U'").ShouldEqual(1);
//change contents of script
File.WriteAllText(Path.Combine(scriptsDirectory, "Everytime", "TestScript.sql"), "CREATE TABLE [dbo].[TestTable2]([Id] [int] IDENTITY(1,1) NOT NULL, [FullName] [nvarchar](50) NULL)");
new ConsoleAliaSQL().UpdateDatabase(settings, scriptsDirectory, RequestedDatabaseAction.Update).ShouldBeTrue();
//assert
new QueryExecutor().ExecuteScalarInteger(settings, "select 1 from dbo.sysobjects where name = 'TestTable2' and type='U'").ShouldEqual(1);
//change contents of script back in case you run again without rebuilding
File.WriteAllText(Path.Combine(scriptsDirectory, "Everytime", "TestScript.sql"), "CREATE TABLE [dbo].[TestTable]([Id] [int] IDENTITY(1,1) NOT NULL, [FullName] [nvarchar](50) NULL)");
}
public void Drops_Azure_database_without_dropping_connections()
{
var settings = new ConnectionSettings("server", "db", true, null, null);
var taskAttributes = new TaskAttributes(settings, null);
var mocks = new MockRepository();
var connectionDropper = mocks.StrictMock<IDatabaseConnectionDropper>();
var taskObserver = mocks.StrictMock<ITaskObserver>();
var queryExecutor = mocks.StrictMock<IQueryExecutor>();
using (mocks.Record())
{
Expect.Call(() => taskObserver.Log("Running against: SQL Azure"));
Expect.Call(queryExecutor.ReadFirstColumnAsStringArray(settings, "select @@version")).Return(new string[] { "SQL Azure" });
queryExecutor.ExecuteNonQuery(settings, "drop database [db]");
Expect.Call(() => taskObserver.Log("Dropping database: db\n"));
}
using (mocks.Playback())
{
IDatabaseActionExecutor dropper = new DatabaseDropper(connectionDropper, queryExecutor);
dropper.Execute(taskAttributes, taskObserver);
}
mocks.VerifyAll();
}
public void Creates_database()
{
var settings = new ConnectionSettings("server", "db", true, null, null);
var taskAttributes = new TaskAttributes(settings, "c:\\scripts");
taskAttributes.RequestedDatabaseAction= RequestedDatabaseAction.Create;
var mocks = new MockRepository();
var queryExecutor = mocks.StrictMock<IQueryExecutor>();
var executor = mocks.StrictMock<IScriptFolderExecutor>();
var taskObserver = mocks.StrictMock<ITaskObserver>();
using (mocks.Record())
{
queryExecutor.ExecuteNonQuery(settings, "create database [db]");
taskObserver.Log(string.Format("Run scripts in Create folder."));
executor.ExecuteScriptsInFolder(taskAttributes, "Create", taskObserver);
}
using (mocks.Playback())
{
IDatabaseActionExecutor creator = new DatabaseCreator(queryExecutor, executor);
creator.Execute(taskAttributes, taskObserver);
}
mocks.VerifyAll();
}
public void ProperlyComparesTwoNonIdenticalConnectionSettings()
{
ConnectionSettings settings1 = new ConnectionSettings("server", "database", true, "username", "password");
ConnectionSettings settings2 = new ConnectionSettings("server1", "database", true, "username", "password");
Assert.AreNotEqual(settings1, settings2);
}
public bool CheckDatabaseExists(ConnectionSettings settings)
{
bool result;
var tmpConn = new SqlConnection(_connectionStringGenerator.GetConnectionString(settings, false));
try
{
string sqlCreateDbQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name = '{0}'", settings.Database);
using (tmpConn)
{
using (var sqlCmd = new SqlCommand(sqlCreateDbQuery, tmpConn))
{
tmpConn.Open();
var databaseId = (int)sqlCmd.ExecuteScalar();
tmpConn.Close();
result = (databaseId > 0);
}
}
}
catch (Exception)
{
result = false;
}
return result;
}
/// <summary>
/// Runs queries that are not specific to a database such as Drop, Create, single user mode
/// </summary>
/// <param name="settings"></param>
/// <param name="sql"></param>
/// <param name="includeDatabaseName"></param>
public void ExecuteNonQuery(ConnectionSettings settings, string sql, bool includeDatabaseName = false)
{
string connectionString = _connectionStringGenerator.GetConnectionString(settings, includeDatabaseName);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandTimeout = 0;
var scripts = SplitSqlStatements(sql);
foreach (var splitScript in scripts)
{
command.CommandText = splitScript;
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
ex.Data.Add("Custom", "Erroring script was not run in a transaction and may be partially committed.");
throw ex;
}
}
}
}
}
public void Execute(string fullFilename, ConnectionSettings settings, ITaskObserver taskObserver)
{
string scriptFilename = getFilename(fullFilename);
if (_executionTracker.TestDataScriptAlreadyExecuted(settings, scriptFilename))
{
taskObserver.Log(string.Format("Skipping (already executed): {0}", scriptFilename));
}
else
{
string sql = _fileSystem.ReadTextFile(fullFilename);
if (!_executor.ScriptSupportsTransactions(sql))
{
taskObserver.Log(string.Format("Executing: {0}", scriptFilename));
_executor.ExecuteNonQuery(settings, sql, true);
_executionTracker.MarkTestDataScriptAsExecuted(settings, scriptFilename, taskObserver);
}
else
{
taskObserver.Log(string.Format("Executing: {0} in a transaction", scriptFilename));
_executor.ExecuteNonQueryTransactional(settings, sql);
_executionTracker.MarkTestDataScriptAsExecuted(settings, scriptFilename, taskObserver);
}
}
}
public void Correctly_drops_connections()
{
string assembly = SqlDatabaseManager.SQL_FILE_ASSEMBLY;
string sqlFile = string.Format(SqlDatabaseManager.SQL_FILE_TEMPLATE, "DropConnections");
ConnectionSettings settings = new ConnectionSettings("server", "MyDatabase", true, null, null);
MockRepository mocks = new MockRepository();
ITaskObserver taskObserver = mocks.StrictMock<ITaskObserver>();
IResourceFileLocator fileLocator = mocks.StrictMock<IResourceFileLocator>();
ITokenReplacer replacer = mocks.StrictMock<ITokenReplacer>();
IQueryExecutor queryExecutor = mocks.StrictMock<IQueryExecutor>();
using (mocks.Record())
{
taskObserver.Log("Dropping connections for database MyDatabase\n");
Expect.Call(fileLocator.ReadTextFile(assembly, sqlFile)).Return("Unformatted SQL");
replacer.Text = "Unformatted SQL";
replacer.Replace("DatabaseName", "MyDatabase");
Expect.Call(replacer.Text).Return("Formatted SQL");
queryExecutor.ExecuteNonQuery(settings, "Formatted SQL");
}
using (mocks.Playback())
{
IDatabaseConnectionDropper dropper = new DatabaseConnectionDropper(fileLocator, replacer, queryExecutor);
dropper.Drop(settings, taskObserver);
}
mocks.VerifyAll();
}
public void UpdateDatabaseTester()
{
//arrange
string scriptsDirectory = Path.Combine("Scripts", GetType().Name.Replace("Tester", ""));
var settings = new ConnectionSettings(".\\sqlexpress", "aliasqltest", true, null, null);
new DbUpdater().UpdateDatabase(new ConnectionStringGenerator().GetConnectionString(settings, true), RequestedDatabaseAction.Drop, scriptsDirectory);
//act
bool success = new DbUpdater().UpdateDatabase(new ConnectionStringGenerator().GetConnectionString(settings, true), RequestedDatabaseAction.Create, scriptsDirectory).Success;
//assert
int records = 0;
AssertUsdAppliedDatabaseScriptTable(settings, reader =>
{
while (reader.Read())
{
records++;
reader["ScriptFile"].ShouldEqual("0001-Update.sql");
}
});
success.ShouldEqual(true);
records.ShouldEqual(1);
}
public void Versions_database()
{
string assembly = SqlDatabaseManager.SQL_FILE_ASSEMBLY;
string sqlFile = string.Format(SqlDatabaseManager.SQL_FILE_TEMPLATE, "VersionDatabase");
ConnectionSettings settings = new ConnectionSettings(String.Empty, String.Empty, false, String.Empty, String.Empty);
string sqlScript = "SQL script...";
MockRepository mocks = new MockRepository();
IResourceFileLocator fileLocator = mocks.StrictMock<IResourceFileLocator>();
IQueryExecutor queryExecutor = mocks.StrictMock<IQueryExecutor>();
ITaskObserver taskObserver = mocks.StrictMock<ITaskObserver>();
using (mocks.Record())
{
Expect.Call(fileLocator.ReadTextFile(assembly, sqlFile)).Return(sqlScript);
Expect.Call(queryExecutor.ExecuteScalarInteger(settings, sqlScript)).Return(7);
taskObserver.SetVariable("usdDatabaseVersion", "7");
}
using (mocks.Playback())
{
IDatabaseVersioner versioner = new DatabaseVersioner(fileLocator, queryExecutor);
versioner.VersionDatabase(settings, taskObserver);
}
mocks.VerifyAll();
}
public void MarkTestDataScriptAsExecuted(ConnectionSettings settings, string scriptFilename, ITaskObserver task)
{
string insertTemplate =
"insert into usd_AppliedDatabaseTestDataScript (ScriptFile, DateApplied) values ('{0}', getdate())";
string sql = string.Format(insertTemplate, scriptFilename);
_executor.ExecuteNonQueryTransactional(settings, sql);
}
public void EnsureTestDataSchemaCreated(ConnectionSettings settings)
{
string assembly = SqlDatabaseManager.SQL_FILE_ASSEMBLY;
string sqlFile = string.Format(SqlDatabaseManager.SQL_FILE_TEMPLATE, "CreateTestDataSchema");
string sql = _locator.ReadTextFile(assembly, sqlFile);
_executor.ExecuteNonQueryTransactional(settings, sql);
}
public void VersionDatabase(ConnectionSettings settings, ITaskObserver taskObserver)
{
string assembly = SqlDatabaseManager.SQL_FILE_ASSEMBLY;
string sqlFile = string.Format(SqlDatabaseManager.SQL_FILE_TEMPLATE, "VersionDatabase");
string sql = _fileLocator.ReadTextFile(assembly, sqlFile);
string version = _executor.ExecuteScalarInteger(settings, sql).ToString();
taskObserver.SetVariable(_databaseVersionPropertyName, version);
}
public void CorrectlyGeneratesConnectionStringWithDatabaseAndUserSecurityAndDatabaseExcluded()
{
IConnectionStringGenerator generator = new ConnectionStringGenerator();
ConnectionSettings settings = new ConnectionSettings("server", "db", false, "usr", "pwd");
string connString = generator.GetConnectionString(settings, false);
Assert.AreEqual("Data Source=server;User ID=usr;Password=pwd;", connString);
}
public void CorrectlyGeneratesConnectionStringWithDatabaseAndIntegratedSecurityAndDatabaseExcluded()
{
IConnectionStringGenerator generator = new ConnectionStringGenerator();
ConnectionSettings settings = new ConnectionSettings("server", "db", true, string.Empty, string.Empty);
string connString = generator.GetConnectionString(settings, false);
Assert.AreEqual("Data Source=server;Integrated Security=True;", connString);
}
public void Creates_initial_log_message_for_database_drop()
{
ILogMessageGenerator generator = new LogMessageGenerator();
var settings = new ConnectionSettings("server", "db", true, null, null);
var taskAttributes = new TaskAttributes(settings, "c:\\scripts") {RequestedDatabaseAction = RequestedDatabaseAction.Drop};
string message = generator.GetInitialMessage(taskAttributes);
Assert.That(message, Is.EqualTo("Drop db on server\n"));
}
public void PropertyAccessorsWorkProperly()
{
ConnectionSettings settings = new ConnectionSettings("server", "database", true, "username", "password");
Assert.AreEqual("server", settings.Server);
Assert.AreEqual("database", settings.Database);
Assert.AreEqual(true, settings.IntegratedAuthentication);
Assert.AreEqual("username", settings.Username);
Assert.AreEqual("password", settings.Password);
}
public bool TestDataScriptAlreadyExecuted(ConnectionSettings settings, string scriptFilename)
{
if (_appliedScripts == null)
{
_appliedScripts =
_executor.ReadFirstColumnAsStringArray(settings, "select ScriptFile from usd_AppliedDatabaseTestDataScript");
}
bool alreadyExecuted = Array.IndexOf(_appliedScripts, scriptFilename) >= 0;
return alreadyExecuted;
}
public bool EverytimeScriptShouldBeExecuted(ConnectionSettings settings, string scriptFilename, string md5)
{
bool shouldBeExecuted = false;
if (ScriptAlreadyExecuted(settings, scriptFilename))
{
var filehash = _executor.ReadFirstColumnAsStringArray(settings,"select hash from usd_AppliedDatabaseScript where ScriptFile = '" + scriptFilename + "'");
shouldBeExecuted = filehash.Any() && (filehash[0] != md5);
}
else
{
shouldBeExecuted = true;
}
return shouldBeExecuted;
}
public void MarkScriptAsExecuted(ConnectionSettings settings, string scriptFilename, ITaskObserver task, string hash = "")
{
//for everytime scripts just delete the row. We could update it but either way has the same result
if (!string.IsNullOrEmpty(hash))
{
string deleteTemplate = "delete from usd_AppliedDatabaseScript where ScriptFile = '{0}'";
string deletesql = string.Format(deleteTemplate, scriptFilename);
_executor.ExecuteNonQueryTransactional(settings, deletesql);
}
string insertTemplate = "insert into usd_AppliedDatabaseScript (ScriptFile, DateApplied, hash) values ('{0}', getdate(), '{1}')";
string sql = string.Format(insertTemplate, scriptFilename, hash);
_executor.ExecuteNonQueryTransactional(settings, sql);
}
public void Update_Database_ShouldRun_Old_RunAlways_Script()
{
//arrange
string scriptsDirectory = Path.Combine("Scripts", GetType().Name.Replace("Tester", ""));
var settings = new ConnectionSettings(".\\sqlexpress", "aliasqltest", true, null, null);
new ConsoleAliaSQL().UpdateDatabase(settings, scriptsDirectory, RequestedDatabaseAction.Drop);
//act
//run once
new ConsoleAliaSQL().UpdateDatabase(settings, scriptsDirectory, RequestedDatabaseAction.Update).ShouldBeTrue();
new QueryExecutor().ExecuteScalarInteger(settings, "select 1 from dbo.sysobjects where name = 'TestTable' and type='U'").ShouldEqual(1);
var dateApplied = DateTime.MinValue;
QueryUsdAppliedDatabaseScriptTable(settings, reader =>
{
while (reader.Read())
{
reader["ScriptFile"].ShouldEqual("TestScript.sql");
dateApplied = (DateTime)reader["DateApplied"];
}
});
dateApplied.ShouldBeGreaterThan(DateTime.MinValue);
//delete TestTable to ensure script doesn't run again
new QueryExecutor().ExecuteNonQuery(settings, "drop table TestTable", true);
//run again
bool success = new ConsoleAliaSQL().UpdateDatabase(settings, scriptsDirectory, RequestedDatabaseAction.Update);
//assert
new QueryExecutor().ExecuteScalarInteger(settings, "select 1 from dbo.sysobjects where name = 'TestTable' and type='U'").ShouldEqual(1);
DateTime dateAppliedUpdated = DateTime.MinValue; ;
int records = 0;
QueryUsdAppliedDatabaseScriptTable(settings, reader =>
{
while (reader.Read())
{
records++;
reader["ScriptFile"].ShouldEqual("TestScript.sql");
dateAppliedUpdated = (DateTime) reader["DateApplied"];
}
});
success.ShouldBeTrue();
records.ShouldEqual(1);
dateAppliedUpdated.ShouldBeGreaterThan(dateApplied);
}
public void PendingChangesTester()
{
//arrange
string scriptsDirectory = Path.Combine("Scripts", GetType().Name.Replace("Tester", ""));
var settings = new ConnectionSettings(".\\sqlexpress", "aliasqltest", true, null, null);
new DbUpdater().UpdateDatabase(new ConnectionStringGenerator().GetConnectionString(settings, true), RequestedDatabaseAction.Drop, scriptsDirectory);
bool updated = new DbUpdater().UpdateDatabase(new ConnectionStringGenerator().GetConnectionString(settings, true), RequestedDatabaseAction.Update, scriptsDirectory).Success;
//act
List<string> pendingChanges = new DbUpdater().PendingChanges(new ConnectionStringGenerator().GetConnectionString(settings, true), scriptsDirectory.Replace("DbUpdater", "NewEverytimeScript"));
//assert
pendingChanges.Count.ShouldEqual(1);
}