/// <summary>
/// Verify that a default exists on a column.
/// </summary>
/// <param name="connection">The connection to test.</param>
/// <param name="table">The name of the table.</param>
/// <param name="column">The name of the column.</param>
/// <param name="value">If specified, the expected value of the default.</param>
/// <returns>True if the default exists as expected.</returns>
private bool DefaultExists(RecordingDbConnection connection, string table, string column, string value = null)
{
return(connection.DoNotLog(() =>
{
string definition = connection.ExecuteScalarSql <string>(@"SELECT definition
FROM sys.default_constraints d
JOIN sys.objects o ON (d.parent_object_id = o.object_id)
JOIN sys.columns c ON (d.parent_object_id = c.object_id AND d.parent_column_id = c.column_id)
WHERE o.name = @Table AND c.name = @Column" ,
new Dictionary <string, object>()
{
{ "Table", table }, { "Column", column }
});
if (definition == null)
{
return false;
}
if (value == null)
{
return true;
}
return definition == value;
}));
}
/// <summary>
/// Run a test and clean up the databases when complete.
/// </summary>
/// <param name="connectionString">The connection string for the database.</param>
/// <param name="action">The test to run.</param>
internal static void TestWithRollback(string connectionString, Action <RecordingDbConnection> action)
{
// make sure the database exists
if (!SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.CreateDatabase(connectionString);
}
// do all of the work in a transaction so we can clean up our changes
using (TransactionScope transaction = new TransactionScope())
using (SqlConnection connection = new SqlConnection(connectionString))
using (RecordingDbConnection recordingConnection = new RecordingDbConnection(connection))
{
recordingConnection.Open();
try
{
action(recordingConnection);
}
finally
{
Console.WriteLine("== BEGIN SCRIPT ============================");
Console.WriteLine(recordingConnection.ScriptLog.ToString());
Console.WriteLine("== END SCRIPT ============================");
}
}
}
public RunGroupOrderScenarios()
{
upgradeResult = null;
scripts = new List <SqlScript>
{
new SqlScript("ZZZScript1.sql", "create table Foo (Id int identity)", new SqlScriptOptions {
ScriptType = ScriptType.RunOnce, RunGroupOrder = DbUpDefaults.DefaultRunGroupOrder
}),
new SqlScript("ZZZScript2.sql", "alter table Foo add column Name varchar(255)", new SqlScriptOptions {
ScriptType = ScriptType.RunOnce, RunGroupOrder = DbUpDefaults.DefaultRunGroupOrder
}),
new SqlScript("AAAScript3.sql", "insert into Foo (Name) values ('test')", new SqlScriptOptions {
ScriptType = ScriptType.RunOnce, RunGroupOrder = DbUpDefaults.DefaultRunGroupOrder + 1
})
};
logger = new CaptureLogsLogger();
recordingConnection = new RecordingDbConnection(logger, "SchemaVersions");
testConnectionFactory = new DelegateConnectionFactory(_ => recordingConnection);
upgradeEngineBuilder = DeployChanges.To
.SqlDatabase("testconn")
.WithScripts(new TestScriptProvider(scripts))
.OverrideConnectionFactory(testConnectionFactory)
.LogTo(logger);
}
public void CanHandleDelimiter()
{
var logger = new CaptureLogsLogger();
var recordingDbConnection = new RecordingDbConnection(logger, "schemaversions");
recordingDbConnection.SetupRunScripts();
var upgrader = DeployChanges.To
.MySqlDatabase(string.Empty)
.OverrideConnectionFactory(recordingDbConnection)
.LogTo(logger)
.WithScript("Script0003", @"USE `test`;
DROP procedure IF EXISTS `testSproc`;
DELIMITER $$
USE `test`$$
CREATE PROCEDURE `testSproc`(
IN ssn VARCHAR(32)
)
BEGIN
SELECT id
FROM customer as c
WHERE c.ssn = ssn ;
END$$").Build();
var result = upgrader.PerformUpgrade();
result.Successful.ShouldBe(true);
this.Assent(logger.Log, new Configuration().UsingSanitiser(Scrubbers.ScrubDates));
}
private void DbUpSetupToUseTransactionPerScript()
{
testConnection = new RecordingDbConnection(false);
upgradeEngineBuilder = DeployChanges.To
.TestDatabase(testConnection)
.WithTransactionPerScript();
}
public UpgradeDatabaseScenarios()
{
upgradeResult = null;
scripts = new List <SqlScript>
{
new SqlScript("Script1.sql", "create table Foo (Id int identity)"),
new SqlScript("Script2.sql", "alter table Foo add column Name varchar(255)"),
new SqlScript("Script3.sql", "insert into Foo (Name) values ('test')")
};
executedScripts = new List <ExecutedSqlScript>
{
new ExecutedSqlScript {
Name = "Script1.sql", Hash = "a"
},
new ExecutedSqlScript {
Name = "Script2.sql", Hash = "b"
},
new ExecutedSqlScript {
Name = "Script3.sql", Hash = "c"
}
};
logger = new CaptureLogsLogger();
recordingConnection = new RecordingDbConnection(logger, "SchemaVersions");
testConnectionFactory = new DelegateConnectionFactory(_ => recordingConnection);
upgradeEngineBuilder = DeployChanges.To
.SqlDatabase("testconn")
.WithScripts(new TestScriptProvider(scripts))
.OverrideConnectionFactory(testConnectionFactory)
.LogTo(logger);
}
/// <summary>
/// Initializes a new instance of the ReliableCommand class, and bind it to the specified ReliableConnection and innerCommand.
/// </summary>
/// <param name="retryStrategy">The retry strategy to use for the command.</param>
/// <param name="innerCommand">The innerCommand to bind to.</param>
public RecordingDbCommand(DbCommand innerCommand, RecordingDbConnection recordingConnection)
{
if (innerCommand is RecordingDbCommand)
throw new InvalidOperationException("Cannot record from within a RecordingDbCommand");
InnerCommand = innerCommand;
RecordingConnection = recordingConnection;
}
void DbUpSetupToNotUseTransactions()
{
testConnection = new RecordingDbConnection(logger, "SchemaVersions");
upgradeEngineBuilder = DeployChanges.To
.TestDatabase(testConnection)
.JournalToSqlTable("dbo", "SchemaVersions")
.WithoutTransaction();
}
private Action Deploy(Func <SupportedDatabases, UpgradeEngineBuilder> deployTo)
{
return(() =>
{
scripts = new List <SqlScript>();
recordingConnection = new RecordingDbConnection(false);
testConnectionFactory = new DelegateConnectionFactory(_ => recordingConnection);
upgradeEngineBuilder = deployTo(DeployChanges.To)
.WithScripts(scripts)
.OverrideConnectionFactory(testConnectionFactory);
});
}
Action Deploy(Func <SupportedDatabases, UpgradeEngineBuilder> deployTo, Func <UpgradeEngineBuilder, string, string, UpgradeEngineBuilder> addCustomNamedJournal)
{
return(() =>
{
scripts = new List <SqlScript>();
logger = new CaptureLogsLogger();
recordingConnection = new RecordingDbConnection(logger, "SchemaVersions");
testConnectionFactory = new DelegateConnectionFactory(_ => recordingConnection);
upgradeEngineBuilder = deployTo(DeployChanges.To)
.WithScripts(scripts)
.OverrideConnectionFactory(testConnectionFactory)
.LogTo(logger);
addCustomNamedJournalToBuilder = addCustomNamedJournal;
});
}
public ConfigurationHelperTests()
{
scripts = new List <SqlScript>
{
new SqlScript("Script1.sql", "create table Foo (Id int identity)")
//new SqlScript("Script2.sql", "alter table Foo add column Name varchar(255)"),
//new SqlScript("Script3.sql", "insert into Foo (Name) values ('test')")
};
logger = new CaptureLogsLogger();
recordingConnection = new RecordingDbConnection(logger, "SchemaVersions");
testConnectionFactory = new DelegateConnectionFactory(_ => recordingConnection);
upgradeEngineBuilder = DeployChanges.To
.SqlDatabase("testconn")
.WithScripts(new TestScriptProvider(scripts))
.OverrideConnectionFactory(testConnectionFactory)
.LogTo(logger);
}
/// <summary>
/// Manages the signatures of objects in the schema database
/// </summary>
/// <param name="connection">The connection to the database</param>
/// <param name="schemaGroup">The name of the schema group to modify</param>
public SchemaRegistry (RecordingDbConnection connection, string schemaGroup)
{
SchemaGroup = schemaGroup;
Connection = connection;
// make sure we have a table to work with
EnsureSchemaTable();
// load in the entries from the database
Connection.DoNotLog(() =>
{
Entries = Connection.QuerySql(
String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}] WHERE SchemaGroup = @SchemaGroup", SchemaRegistryTableName),
new Dictionary<string, object>() { { "SchemaGroup", schemaGroup } }).Select(
(dynamic e) => new SchemaRegistryEntry()
{
SchemaGroup = e.SchemaGroup,
ObjectName = e.ObjectName,
Signature = e.Signature,
Type = (SchemaObjectType)Enum.Parse(typeof(SchemaObjectType), e.Type),
OriginalOrder = e.OriginalOrder
}).ToList();
// automatically handle the old format for entries
// WAS: 'ROLE [foo]'
// NOW: '[foo]'
foreach (var entry in Entries.Where(e =>
e.Type == SchemaObjectType.Schema ||
e.Type == SchemaObjectType.Login ||
e.Type == SchemaObjectType.User ||
e.Type == SchemaObjectType.Role ||
e.Type == SchemaObjectType.Queue ||
e.Type == SchemaObjectType.Service))
entry.ObjectName = _registryUpgradeRegex.Replace(entry.ObjectName, "");
// automatically reformat names to fully qualified name
// WAS: '[foo]'
// NOW: '[dbo].[foo]'
foreach (var entry in Entries)
entry.ObjectName = new SchemaObject(entry.Type, entry.ObjectName, null).Name;
});
}
/// <summary>
/// Verify all of the objects in the database and registry.
/// </summary>
/// <param name="schema">The schema to verify.</param>
/// <param name="connection">The connection to use.</param>
private static void VerifyObjectsAndRegistry(IEnumerable <string> schema, RecordingDbConnection connection)
{
connection.DoNotLog(() =>
{
// make sure the schema registry was updated
SchemaRegistry registry = new SchemaRegistry(connection, TestSchemaGroup);
// make sure all of the objects exist in the database
foreach (var schemaObject in schema.Select(s => new SchemaObject(s)))
{
// azure doesn't support xml index, so lets comment those out
if (schemaObject.Sql.Contains("XML INDEX") && connection.IsAzure())
{
continue;
}
Assert.True(schemaObject.Exists(connection), "Object {0} is missing from database", schemaObject.Name);
Assert.True(registry.Contains(schemaObject), "Object {0} is missing from registry", schemaObject.Name);
}
});
}
public void CanHandleDelimiter()
{
var recordingDbConnection = new RecordingDbConnection(true);
var upgrader = DeployChanges.To
.MySqlDatabase(string.Empty)
.OverrideConnectionFactory(recordingDbConnection)
.WithScript("Script0003", @"USE `test`;
DROP procedure IF EXISTS `testSproc`;
DELIMITER $$
USE `test`$$
CREATE PROCEDURE `testSproc`(
IN ssn VARCHAR(32)
)
BEGIN
SELECT id
FROM customer as c
WHERE c.ssn = ssn ;
END$$").Build();
var result = upgrader.PerformUpgrade();
result.Successful.ShouldBe(true);
var commandLog = recordingDbConnection.GetCommandLog();
try
{
Approvals.Verify(commandLog, Scrubbers.ScrubDates);
}
catch (Exception)
{
Console.WriteLine(commandLog);
throw;
}
}
/// <summary>
/// Run a test and clean up the databases when complete.
/// </summary>
/// <param name="connectionString">The connection string for the database.</param>
/// <param name="action">The test to run.</param>
internal static void TestWithRollback(string connectionString, Action<RecordingDbConnection> action)
{
// make sure the database exists
if (!SchemaInstaller.DatabaseExists(connectionString))
SchemaInstaller.CreateDatabase(connectionString);
// do all of the work in a transaction so we can clean up our changes
using (TransactionScope transaction = new TransactionScope())
using (SqlConnection connection = new SqlConnection(connectionString))
using (RecordingDbConnection recordingConnection = new RecordingDbConnection(connection))
{
recordingConnection.Open();
try
{
action(recordingConnection);
}
finally
{
Console.WriteLine("== BEGIN SCRIPT ============================");
Console.WriteLine(recordingConnection.ScriptLog.ToString());
Console.WriteLine("== END SCRIPT ============================");
}
}
}
/// <summary>
/// Install the object into the database
/// </summary>
/// <param name="connection">The database connection to use</param>
internal void Install(RecordingDbConnection connection, IEnumerable<SchemaObject> objects)
{
_implementation.Install(connection, objects);
}
/// <summary>
/// Verify that a default exists on a column.
/// </summary>
/// <param name="connection">The connection to test.</param>
/// <param name="table">The name of the table.</param>
/// <param name="column">The name of the column.</param>
/// <param name="value">If specified, the expected value of the default.</param>
/// <returns>True if the default exists as expected.</returns>
private bool DefaultExists(RecordingDbConnection connection, string table, string column, string value = null)
{
return connection.DoNotLog(() =>
{
string definition = connection.ExecuteScalarSql<string>(@"SELECT definition
FROM sys.default_constraints d
JOIN sys.objects o ON (d.parent_object_id = o.object_id)
JOIN sys.columns c ON (d.parent_object_id = c.object_id AND d.parent_column_id = c.column_id)
WHERE o.name = @Table AND c.name = @Column",
new Dictionary<string, object>() { { "Table", table }, { "Column", column } });
if (definition == null)
return false;
if (value == null)
return true;
return definition == value;
});
}
/// <summary>
/// Install a schema into a database.
/// </summary>
/// <param name="connection">The connection to use.</param>
/// <param name="sql"the SQL to install.</param>
private static void InstallAndVerify(RecordingDbConnection connection, IEnumerable<string> sql)
{
Install(connection, sql);
VerifyObjectsAndRegistry(sql, connection);
}
/// <summary>
/// Verify all of the objects in the database and registry.
/// </summary>
/// <param name="schema">The schema to verify.</param>
/// <param name="connection">The connection to use.</param>
private static void VerifyObjectsAndRegistry(IEnumerable<string> schema, RecordingDbConnection connection)
{
connection.DoNotLog(() =>
{
// make sure the schema registry was updated
SchemaRegistry registry = new SchemaRegistry(connection, TestSchemaGroup);
// make sure all of the objects exist in the database
foreach (var schemaObject in schema.Select(s => new SchemaObject(s)))
{
// azure doesn't support xml index, so lets comment those out
if (schemaObject.Sql.Contains("XML INDEX") && connection.IsAzure())
continue;
Assert.True(schemaObject.Exists(connection), "Object {0} is missing from database", schemaObject.Name);
Assert.True(registry.Contains(schemaObject), "Object {0} is missing from registry", schemaObject.Name);
}
});
}
internal bool Exists(RecordingDbConnection connection)
{
return _implementation.Exists(connection);
}
/// <summary>
/// Determine if this is a type of object that we can modify.
/// </summary>
/// <param name="type">The type of the object.</param>
/// <returns>True if we know how to drop the object.</returns>
internal bool CanModify(SchemaInstaller.InstallContext context, RecordingDbConnection connection)
{
return connection.DoNotLog(() => _implementation.CanModify(context, connection));
}
/// <summary>
/// Install a schema into a database.
/// </summary>
/// <param name="connection">The connection to use.</param>
/// <param name="sql"the SQL to install.</param>
private static void InstallAndVerify(RecordingDbConnection connection, IEnumerable <string> sql)
{
Install(connection, sql);
VerifyObjectsAndRegistry(sql, connection);
}
public ConfigLoaderTests()
{
Logger = new CaptureLogsLogger();
recordingConnection = new RecordingDbConnection(Logger, "SchemaVersions");
testConnectionFactory = new DelegateConnectionFactory(_ => recordingConnection);
}
public VariableSubstitutionTests()
{
Logger = new CaptureLogsLogger();
recordingConnection = new RecordingDbConnection(Logger, "SchemaVersions");
testConnectionFactory = new DelegateConnectionFactory(_ => recordingConnection);
}
