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