public void InsertTest_WithTableNameSet(SupportedDatabaseTypes databaseTypes, string schema, string expectedSql)
{
const string tableName = "EntityTable";
var useSchema = !string.IsNullOrEmpty(schema);
var dbConfig = DbConfigDatabaseTargets.Create(databaseTypes, schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var insert = builder.Insert <Entity>();
var sql = insert
.OnTable(tableName)
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields(x => x.FP(f => f.Id, "IdParam"))
.Fields(x => x.FV(f => f.Name, "Arild"))
.Fields(x => x.FP(f => f.Description))
.Build();
sql.Should().Be(expectedSql);
}
public void SelectTestOneTableOnly(SupportedDatabaseTypes databaseTypes, bool useSchema, string schemaPrefixId, string expectedSql)
{
SqlAliasHelper.ClearAliases();
var addDictionary = new Dictionary <string, string> {
{ "database:schemaPrefix:Id", schemaPrefixId }
};
using (var scope = TestServiceProvider.GetDatabaseExampleServiceProvider(databaseTypes, addDictionary).CreateScope())
{
var dbConfig = scope.ServiceProvider.GetService <IDbConfigSchemaTargets>();
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.CreateSqlBuilder();
var select = builder.Select();
var sqls = new List <string>
{
select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build()
};
foreach (var sql in sqls)
{
sql.Should().Be(expectedSql);
}
}
}
public static async Task ExecuteDbExample(
SupportedDatabaseTypes databaseType,
Dictionary <string, string> overrideConfig = null)
{
var provider = DbExampleBuilder.BuildDbExample(
databaseType,
overrideConfig);
var persons = CreatePersons(10).ToArray();
using (var scope = provider.CreateScope())
{
var dbConnection = scope.ServiceProvider.GetService <IDbConnection>();
var repository = scope.ServiceProvider.GetService <IPersonRepository>();
foreach (var person in persons)
{
await repository.InsertPerson(person, dbConnection);
}
(await repository.CountAlivePersons(dbConnection)).Should().BeInRange(10, 20); // using range because of parallell test runs...
(await repository.SelectPerson(persons.First().Id, dbConnection)).Id.Should().Be(persons.First().Id);
var subsetPersons = persons.Take(persons.Length / 2).ToArray();
var selectedPersons = (await repository.SelectPersons(subsetPersons.Select(x => x.Id).ToArray(), dbConnection)).Select(x => x.Id).ToArray();
selectedPersons.Length.Should().Be(subsetPersons.Length);
selectedPersons.Should().Contain(subsetPersons.Select(x => x.Id));
persons.First().Alive = false;
persons.First().Username = "******";
await repository.UpdatePerson(persons.First(), dbConnection);
(await repository.SelectPerson(persons.First().Id, dbConnection)).Id.Should().Be(persons.First().Id);
await repository.DeletePerson(persons.First().Id, dbConnection);
(await repository.SelectPersons(persons.Select(x => x.Id).ToArray(), dbConnection)).Should().NotContain(persons.First());
}
}
//[InlineData(SupportedDatabaseTypes.Oracle)]
public async Task DbProvider_ExampleRepository_Success(SupportedDatabaseTypes databaseType)
{
var overrideConfig = OverrideConfig.GetInMemoryOverrideConfig(databaseType);
overrideConfig.TryGetValue("database:schema", out var schema);
//overrideConfig.Add("database:schemaprefix:id", "EX");
//overrideConfig.Add("database:migration:schemaprefix:id", "EX");
var logFile = $"DbProvider_ExampleRepository_Success_{schema}_{databaseType}.sql";
var loglogFile = $"DbProvider_ExampleRepository_Success_{schema}_{databaseType}.log";
overrideConfig.Add("Logging:Migration:File:ShowSql", "false");
overrideConfig.Add("Logging:Migration:ShowElapsedTime", "True");
overrideConfig.Add("Logging:Migration:File", logFile);
overrideConfig.Add("Logging:Migration:Console:ShowSql", "false");
//overrideConfig.Add("Logging:Migration:Console", "True");
overrideConfig.Add("Logging:File", loglogFile);
var provider = MigrationBuilder.BuildMigration(databaseType, overrideConfig, assemblies: new [] { typeof(AddPersonTable).Assembly });
using var scope = provider.CreateScope();
try
{
var migrationRunner = scope.ServiceProvider.GetService <IMigrationRunner>();
var configuration = scope.ServiceProvider.GetService <IConfiguration>();
logFile = configuration.GetMigrationLogFile();
migrationRunner.DropSchema(scope.ServiceProvider.GetVersionTableMetaData());
migrationRunner.MigrateUp();
await DbExampleExecutor.ExecuteDbExample(databaseType, overrideConfig);
migrationRunner.DropSchema(scope.ServiceProvider.GetVersionTableMetaData());
}
catch (InvalidOperationException)
{
//
}
ShowLogFileContent(logFile);
}
public void DeleteTest1(SupportedDatabaseTypes databaseTypes, bool useSchema, string schemaPrefixId, string expectedSql)
{
var addDictionary = new Dictionary <string, string> {
{ "database:schemaPrefix:Id", schemaPrefixId }
};
using (var scope = TestServiceProvider.GetDatabaseExampleServiceProvider(databaseTypes, addDictionary).CreateScope())
{
var dbConfig = scope.ServiceProvider.GetService <IDbConfigSchemaTargets>();
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.CreateSqlBuilder();
var delete = builder.Delete <Entity>();
var sql = delete
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Where(x => x.WP(item => item.Id, "IdParam"))
.Where(x => x.WV(item => item.Name, "Arild"))
.Build();
sql.Should().Be(expectedSql);
}
}
public void SelectCountTestOneTableOnly(SupportedDatabaseTypes databaseTypes, string schema, string schemaPrefixId, string expectedSql)
{
SqlAliasHelper.ClearAliases();
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId() ?? string.Empty);
var builder = dbConfig.SqlBuilder();
var select = builder.Select();
var sqls = new List <string>
{
select
.Count()
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
select
.Count()
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build()
};
foreach (var sql in sqls)
{
sql.Should().Be(expectedSql);
}
}
public void UpdateTestWithWhereIfTest(SupportedDatabaseTypes databaseTypes, bool ifStatementResult, string schemaPrefixId, string expectedSql)
{
var addDictionary = new Dictionary <string, string> {
{ "database:schemaPrefix:Id", schemaPrefixId }
};
using (var scope = TestServiceProvider.GetDatabaseExampleServiceProvider(databaseTypes, addDictionary).CreateScope())
{
var dbConfig = scope.ServiceProvider.GetService <IDbConfigSchemaTargets>();
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.CreateSqlBuilder();
var update = builder.Update <Entity>();
var sql = update
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => ifStatementResult)
.Fields(x => x.FP(f => f.Description)
.FV(f => f.Name, "Arild"))
.Where(x => x.WP(item => item.Id, "IdParam"))
.WhereIf(x => x.WV(item => item.Name, "Arild", OP.DI), () => ifStatementResult)
.Build();
sql.Should().Be(expectedSql);
}
}
public void InsertTest2(SupportedDatabaseTypes databaseTypes, bool useSchema, string schemaPrefixId, string expectedSql)
{
var addDictionary = new Dictionary <string, string> {
{ "database:schemaPrefix:Id", schemaPrefixId }
};
using (var scope = TestServiceProvider.GetDatabaseExampleServiceProvider(databaseTypes, addDictionary).CreateScope())
{
var dbConfig = scope.ServiceProvider.GetService <IDbConfigSchemaTargets>();
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.CreateSqlBuilder();
var resolver = dbConfig.CreateDatabaseParameterResolver();
var insert = builder.Insert <Entity>();
var sql = insert
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields(x => x.FV(f => f.Id, resolver.WithNextSequence("seq"), true))
.Fields(x => x.FV(f => f.Name, "Arild"))
.Fields(x => x.FP(f => f.Description))
.Build();
sql.Should().Be(expectedSql);
}
}
private static void MigrateData(SupportedDatabaseTypes databaseType)
{
var configuration = OverrideConfig.GetDefaultConfiguration(databaseType);
configuration.MigrateUpSchema();
}
public SqlBuilderDbConfigSchemaTargets(string schema, string schemaPrefixId, SupportedDatabaseTypes dbType = SupportedDatabaseTypes.Oracle)
{
SchemaPrefixId = schemaPrefixId;
Schema = schema;
DbType = dbType;
}
/// <summary>
///
/// </summary>
/// <param name="dbType"></param>
/// <returns></returns>
public static IDbConnectionStringBuilder GetConnectionStringProvider(this SupportedDatabaseTypes dbType)
{
AssertDbConnectionImplemented(dbType);
return(DbConnectionProviders[dbType]);
}
public static IDbConfigDatabaseTargets CreateTestDbConfig(SupportedDatabaseTypes databaseType, string schema = null)
{
return(new MsDbConfig(CreateTestConfiguration(databaseType, schema)));
}
public void SelectTest2WithJoins(SupportedDatabaseTypes databaseTypes, bool useSchema, string expectedSql)
{
using (var scope = TestServiceProvider.GetDatabaseExampleServiceProvider(databaseTypes).CreateScope())
{
var dbConfig = scope.ServiceProvider.GetService <IDbConfigDatabaseTargets>();
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var select = builder.Select();
var sqls = new List <string>
{
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.From <Entity>()
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
};
foreach (var sql in sqls)
{
sql.Should().Be(expectedSql);
}
}
}
public static IDbTypeTranslator GetDbTypeTranslator(this SupportedDatabaseTypes databaseType)
{
return(DbTypeTranslators[databaseType]);
}
/// <summary>
/// Change <see cref="DefaultDbType">DefaultDbType</see> to <paramref name="dbType"/>
/// </summary>
/// <param name="dbType"></param>
/// <returns></returns>
public static SupportedDatabaseTypes WithDefaultDbType(SupportedDatabaseTypes dbType)
{
DefaultDbType = dbType;
return(DefaultDbType);
}
public void SelectTest2WithJoins(SupportedDatabaseTypes databaseTypes, string schema, string expectedSql)
{
var dbConfig = OverrideConfig.CreateTestDbConfig(databaseTypes, schema);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var select = builder.Select();
var sqls = new List <string>
{
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.From <Entity>()
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
};
foreach (var sql in sqls)
{
sql.Should().Be(expectedSql);
}
}
/// <summary>
/// Add/Register dbProviderFactory to DbProviderFactories
/// </summary>
/// <param name="dbProviderFactory"></param>
/// <param name="databaseType"></param>
/// <param name="skipIfAlreadyRegistered"></param>
/// <returns></returns>
public static DbProviderFactory Register(this DbProviderFactory dbProviderFactory, SupportedDatabaseTypes databaseType, bool skipIfAlreadyRegistered = false)
{
if (skipIfAlreadyRegistered && DbProviderFactories.ContainsKey(databaseType))
{
return(DbProviderFactories[databaseType]);
}
DbProviderFactories[databaseType] = dbProviderFactory;
return(dbProviderFactory);
}
public void DbConfigDbType_WithDefaultDbTypeSetToOracle_ShouldBeCorrectDbType(SupportedDatabaseTypes?configDbType, SupportedDatabaseTypes expectedDbType)
{
lock (TestCollectionFixtures.LockObj)
{
try
{
var configuration = GetConfiguration(configDbType);
DefaultDbConfigValues.WithOracleDefaultDbType();
configuration.CreateDbConfig().DbType.Should().Be(expectedDbType);
}
finally
{
DefaultDbConfigValues.WithLibraryDefaultDbType();
}
}
}
public void Migration_PreviewOnlyAndInvalidAdminPassword_ShouldThrowDatabaseException(SupportedDatabaseTypes databaseType)
{
var inMemoryOverrideConfig = OverrideConfig.GetInMemoryOverrideConfig(databaseType, OverrideConfig.NewRandomSchema);
inMemoryOverrideConfig.TryGetValue("database:schema", out var schema);
var logFile = $"Migration_Success_{schema}_{databaseType}.sql";
inMemoryOverrideConfig.Add("Logging:Migration:ShowSql", "True");
inMemoryOverrideConfig.Add("Logging:Migration:ShowElapsedTime", "True");
inMemoryOverrideConfig.Add("Logging:Migration:File", logFile);
inMemoryOverrideConfig["database:adminPassword"] = "******";
var provider = MigrationBuilder.BuildMigration(databaseType, inMemoryOverrideConfig,
collection =>
collection.Configure <ProcessorOptions>(opt => opt.PreviewOnly = true));
using (var scope = provider.CreateScope())
{
Action action = () => scope.ServiceProvider.GetService <IMigrationRunner>().MigrateUp();
if (databaseType == SupportedDatabaseTypes.Postgres)
{
action.Should().Throw <PostgresException>().Which.SqlState.Should().Be("28P01");
}
if (databaseType == SupportedDatabaseTypes.Oracle)
{
action.Should().Throw <OracleException>().Which.Number.Should().Be(1017);
}
}
ShowLogFileContent(logFile);
}
public async Task DbProvider_ExampleRepository_WithDbProviderFactory_Success(SupportedDatabaseTypes databaseType)
{
var overrideConfig = OverrideConfig.GetInMemoryOverrideConfig(databaseType);
await DbExampleExecutor.ExecuteDbExample(databaseType, true, overrideConfig);
}
