public void CreateDatabaseWithConnectionStringAndCollationOptions()
{
const string collation = "Japanese_CI_AS";
var connectionString = new SqlConnectionStringBuilder
{
PersistSecurityInfo = true,
DataSource = LocalInstanceName,
UserID = UserName,
Password = Password
}.ConnectionString;
using var fixture = ThrowawayDatabase.Create(connectionString, new ThrowawayDatabaseOptions
{
Collation = collation
});
fixture.Name
.Should()
.StartWith("ThrowawayDb");
GetCollation(fixture)
.Should()
.Be(collation);
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
protected static bool CheckCommandExecution(ThrowawayDatabase fixture)
{
using var connection = fixture.OpenConnection();
using var cmd = new SqlCommand("SELECT 1", connection);
return(Convert.ToInt32(cmd.ExecuteScalar()) == 1);
}
public void CreateDatabaseWithConnectionStringWithoutCollationIfNullOrWhiteSpace(string collation)
{
const string prefix = nameof(prefix);
var connectionString = new SqlConnectionStringBuilder
{
PersistSecurityInfo = true,
DataSource = LocalInstanceName,
UserID = UserName,
Password = Password
}.ConnectionString;
using var fixture = ThrowawayDatabase.Create(connectionString, new ThrowawayDatabaseOptions
{
DatabaseNamePrefix = prefix,
Collation = collation
});
fixture.Name
.Should()
.StartWith(prefix);
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
public void CreateDatabaseWithConnectionStringAndPrefixCollationOptions()
{
const string prefix = nameof(prefix),
collation = "utf8mb4_ja_0900_as_cs_ks";
var connectionString = new MySqlConnectionStringBuilder
{
PersistSecurityInfo = true,
Server = LocalInstanceName,
UserID = UserName,
Password = Password
}.ConnectionString;
using var fixture = ThrowawayDatabase.Create(connectionString, new ThrowawayDatabaseOptions
{
DatabaseNamePrefix = prefix,
Collation = collation
});
fixture.Name
.Should()
.StartWith(prefix);
GetCollation(fixture)
.Should()
.Be(collation);
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
public void BaseSetUp()
{
if (UseThrowawayDb)
{
_throwaway = ThrowawayDatabase.Create("Server=(localdb)\\mssqllocaldb;Trusted_Connection=True;MultipleActiveResultSets=true");
_connectionString = _throwaway.ConnectionString;
}
else
{
_connectionStringBuilder = new SqlConnectionStringBuilder(_configuration.Test.ConnectionString);
//create a connection string to a database we know exists, but is not the db we intend to create/drop
_connectionStringBuilder.InitialCatalog = _dbOpsDb;
_connectionString = _connectionStringBuilder.ToString();
//connect to master and create our test database
_databaseName = $"{NUnit.Framework.TestContext.CurrentContext.Test.ClassName.Substring(NUnit.Framework.TestContext.CurrentContext.Test.ClassName.LastIndexOf(".") + 1)}-{NUnit.Framework.TestContext.CurrentContext.Test.MethodName}";
_connection = new SqlConnection(_connectionString);
_connection.Open();
_connection.Execute($"create database [{_databaseName}]");
_connection.Close();
_connection.Dispose();
//reformat the connection sring to point to the test database
_connectionStringBuilder.InitialCatalog = _databaseName;
_connectionString = _connectionStringBuilder.ToString();
}
//run migrations on the test database - with initial data - with test data
MigrationRunner.RunMigrationsThrowOnException(_connectionString, true, true);
//initialize the test connection to use the test database
_connection = new SqlConnection(_connectionString);
}
public SqlSyntaxValidationTests()
{
_database = Database.CreateDatabase();
_defaultConfiguration = new Configuration(new Dictionary <string, string>
{
["CONNECTION_STRING"] = _database.ConnectionString,
});
}
public static ThrowawayDatabase CreateDatabase()
{
var database = ThrowawayDatabase.Create(ConnectionString);
using var connection = new NpgsqlConnection(database.ConnectionString);
connection.Open();
InitializeDatabase(connection);
return(database);
}
public static string GetCollation(ThrowawayDatabase fixture, string name = null)
{
using var connection = fixture.OpenConnection();
using var cmd = new MySqlCommand("SELECT default_collation_name FROM information_schema.schemata WHERE schema_name = @name LIMIT 1", connection);
cmd.Parameters.AddWithValue("name", name ?? fixture.Name);
return(Convert.ToString(cmd.ExecuteScalar()));
}
public static string GetCollation(ThrowawayDatabase fixture, string name = null)
{
using var connection = fixture.OpenConnection();
using var cmd = new SqlCommand("SELECT collation_name FROM sys.databases WHERE name = @name", connection);
cmd.Parameters.AddWithValue("name", name ?? fixture.Name);
return(Convert.ToString(cmd.ExecuteScalar()));
}
public void CreateDatabaseWithUserNamePasswordAndOptions()
{
using var fixture = ThrowawayDatabase.Create(UserName, Password, LocalInstanceName, new ThrowawayDatabaseOptions());
fixture.Name
.Should()
.StartWith("ThrowawayDb");
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
static IEnumerable <int> GetItems(ThrowawayDatabase fixture)
{
using var connection = fixture.OpenConnection();
using var cmd = new MySqlCommand($"SELECT * FROM {tblTest}", connection);
using var reader = cmd.ExecuteReader();
while (reader.Read() && reader.HasRows)
{
yield return(reader.GetInt32(0));
}
}
public void CreateNewDatabase()
{
using var database = ThrowawayDatabase.Create("postgres", "postgres", "localhost");
using var connection = new NpgsqlConnection(database.ConnectionString);
connection.Open();
using var cmd = new NpgsqlCommand("SELECT 1", connection);
var result = Convert.ToInt32(cmd.ExecuteScalar());
result
.Should()
.Be(1);
}
public void CreateDatabaseWithUserNamePasswordAndPrefix()
{
const string prefix = nameof(prefix);
using var fixture = ThrowawayDatabase.Create(UserName, Password, LocalInstanceName, prefix);
fixture.Name
.Should()
.StartWith(prefix);
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
// Subclasses can access this method, private would mean only this class.
protected IntegrationTest(ITestOutputHelper output)
{
_output = output;
// The configuration we use for integration testing has Caching and Mailing disabled (flags set to false) - we do not test these aspects here for now.
// These mutations tested to work in .NET Core 3.1
var builder = new ConfigurationBuilder().AddUserSecrets <IntegrationTest>();
Configuration = builder.Build();
var appFactory = new WebApplicationFactory <Startup>()
.WithWebHostBuilder(builder =>
{
builder.ConfigureServices(services =>
{
// Remove certain injected services (from the Startup.cs) in order to mutate and reinject.
foreach (Type t in new Type[] {
typeof(DbContextOptions <AppDbContext>),
typeof(PgSqlSettings),
typeof(RedisSettings),
typeof(MailSettings),
typeof(JwtSettings),
typeof(IConnectionMultiplexer),
typeof(ICacheService),
typeof(ConnectionMultiplexer),
typeof(RedisCacheService)
})
{
services.RemoveAll(t);
//var descriptor = services.SingleOrDefault(d => d.ServiceType == t);
//if (descriptor != null)
//{
// services.Remove(descriptor);
//}
}
// Reinject the settings with the new configuration (these mutations tested to work in .NET Core 3.1)
var settings = ConfigurationInstaller.BindSettings(Configuration, services, disableAll: true);
ConfigurationInstaller.InstallServicesFromSettings(settings, services);
_output.WriteLine(JsonConvert.SerializeObject(settings));
// Construct a throwaway database with it.
_throwawayDatabase = ThrowawayDatabase.Create(settings.PgSqlSettings.Username, settings.PgSqlSettings.Password, settings.PgSqlSettings.Host);
services.AddDbContext <AppDbContext>(options => options.UseNpgsql(_throwawayDatabase.ConnectionString));
});
});
_serviceProvider = appFactory.Services;
TestClient = appFactory.CreateClient();
}
public void CreateDatabaseWithUserNamePasswordCollationNullOrWhiteSpace(string collation)
{
const string prefix = nameof(prefix);
using var fixture = ThrowawayDatabase.Create(UserName, Password, LocalInstanceName, new ThrowawayDatabaseOptions
{
DatabaseNamePrefix = prefix,
Collation = collation
});
fixture.Name
.Should()
.StartWith(prefix);
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
public void Can_Select_1_From_Database()
{
using var database = ThrowawayDatabase.Create(
Settings.Username,
Settings.Password,
Settings.Host
);
testOutputHelper.WriteLine($"Created database {database.Name}");
using var connection = new NpgsqlConnection(database.ConnectionString);
connection.Open();
using var cmd = new NpgsqlCommand("SELECT 1", connection);
var result = Convert.ToInt32(cmd.ExecuteScalar());
Assert.Equal(1, result);
testOutputHelper.WriteLine(result.ToString());
}
static void Main(string[] args)
{
var arguments = String.Join(", ", args);
Console.WriteLine($"Arguments [{arguments}]");
using (var database = ThrowawayDatabase.FromLocalInstance("localhost\\SQLEXPRESS"))
{
using (var connection = new SqlConnection(database.ConnectionString))
{
connection.Open();
using (var cmd = new SqlCommand("SELECT 1", connection))
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine(result);
}
}
}
}
public void CreateDatabase()
{
string databaseName;
using (var fixture = ThrowawayDatabase.FromLocalInstance(LocalInstanceName))
{
databaseName = fixture.Name;
DatabaseExists(databaseName)
.Should()
.BeTrue();
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
DatabaseExists(databaseName)
.Should()
.BeFalse();
public void CreateDatabaseWithConnectionString()
{
var connectionString = new SqlConnectionStringBuilder
{
PersistSecurityInfo = true,
DataSource = LocalInstanceName,
UserID = UserName,
Password = Password
}.ConnectionString;
using var fixture = ThrowawayDatabase.Create(connectionString);
fixture.Name
.Should()
.StartWith("ThrowawayDb");
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
public void CreateDatabaseWithUserNamePasswordAndCollationOptions()
{
const string collation = "Japanese_CI_AS";
using var fixture = ThrowawayDatabase.Create(UserName, Password, LocalInstanceName, new ThrowawayDatabaseOptions
{
Collation = collation
});
fixture.Name
.Should()
.StartWith("ThrowawayDb");
GetCollation(fixture)
.Should()
.Be(collation);
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
static void Main(string[] args)
{
var arguments = String.Join(", ", args);
Console.WriteLine($"Arguments [{arguments}]");
using (var database = ThrowawayDatabase.FromLocalInstance("localhost\\SQLEXPRESS"))
{
Console.WriteLine($"Created database {database.Name}");
// Apply migrations / seed data if necessary
// execute code against this newly generated database
using (var connection = new SqlConnection(database.ConnectionString))
{
connection.Open();
using (var cmd = new SqlCommand("SELECT 1", connection))
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine(result); // prints 1
}
}
}
}
public void CreateDatabaseWithConnectionStringPrefix()
{
const string prefix = nameof(prefix);
var connectionString = new MySqlConnectionStringBuilder
{
PersistSecurityInfo = true,
Server = LocalInstanceName,
UserID = UserName,
Password = Password
}.ConnectionString;
using var fixture = ThrowawayDatabase.Create(connectionString, prefix);
fixture.Name
.Should()
.StartWith(prefix);
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
public void CreateDatabaseWithUserNamePasswordAndPrefixCollationOptions()
{
const string prefix = nameof(prefix),
collation = "utf8mb4_ja_0900_as_cs_ks";
using var fixture = ThrowawayDatabase.Create(UserName, Password, LocalInstanceName, new ThrowawayDatabaseOptions
{
DatabaseNamePrefix = prefix,
Collation = collation
});
fixture.Name
.Should()
.StartWith(prefix);
GetCollation(fixture)
.Should()
.Be(collation);
CheckCommandExecution(fixture)
.Should()
.BeTrue();
}
public void CreateAndRestoreSnapshot()
{
const string tblTest = nameof(tblTest);
using var fixture = ThrowawayDatabase.Create(UserName, Password, LocalInstanceName);
var connection = fixture.OpenConnection();
using (var cmd = new MySqlCommand($"CREATE TABLE {tblTest} (test int)", connection))
cmd.ExecuteNonQuery();
var items = GetItems(fixture).ToArray();
items.Should().BeEmpty();
// Create a snapshot and populate the table
fixture.CreateSnapshot();
using (var cmd = new MySqlCommand($"INSERT {tblTest} VALUES (@i)", connection))
{
cmd.Parameters.Add("i", MySqlDbType.Int32);
foreach (var i in Enumerable.Range(1, 3))
{
cmd.Parameters[0].Value = i;
cmd.ExecuteNonQuery();
}
}
items = GetItems(fixture).ToArray();
items.Should().BeEquivalentTo(new[] { 1, 2, 3 });
// Restore the snapshot
fixture.RestoreSnapshot();
items = GetItems(fixture).ToArray();
items.Should().BeEmpty();
public void BaseTearDown()
{
if (UseThrowawayDb)
{
if (_connection != null)
{
_connection.Close();
_connection.Dispose();
}
if (_throwaway != null)
{
_throwaway.Dispose();
_throwaway = null;
}
}
else
{
_connection.Close();
_connection.Dispose();
_connectionStringBuilder.InitialCatalog = _dbOpsDb;
_connectionString = _connectionStringBuilder.ToString();
_connection = new SqlConnection(_connectionString);
_connection.Execute($"alter database [{_databaseName}] set single_user with rollback immediate");
if (_configuration.Test.OnPersistenceTestCompletion == TestConfiguration.PersistenceTestCompletionAction.DropOnComplete)
{
Drop(_connection, _databaseName);
}
else if (_configuration.Test.OnPersistenceTestCompletion == TestConfiguration.PersistenceTestCompletionAction.RenameOnComplete)
{
_databaseName = RenameWithRunTime(_connection, _databaseName, _runTime);
}
else if (_configuration.Test.OnPersistenceTestCompletion == TestConfiguration.PersistenceTestCompletionAction.DropIfSuccessRenameIfFailures)
{
if (NUnit.Framework.TestContext.CurrentContext.Result.Outcome == NUnit.Framework.Interfaces.ResultState.Failure)
{
_databaseName = RenameWithRunTime(_connection, _databaseName, _runTime);
}
else
{
Drop(_connection, _databaseName);
}
}
else if (_configuration.Test.OnPersistenceTestCompletion == TestConfiguration.PersistenceTestCompletionAction.DropIfSuccess)
{
if (NUnit.Framework.TestContext.CurrentContext.Result.Outcome == NUnit.Framework.Interfaces.ResultState.Success)
{
Drop(_connection, _databaseName);
}
}
else
{
throw new InvalidOperationException("Unknown PersistenceTestCompletionAction");
}
_connection.Close();
_connection.Dispose();
}
_databaseName = "";
_connectionString = "";
_connection = null;
}
