public void TestSqLiteStatementBuilderCreateMergeAllWithIdentity()
{
// Setup
var builder = StatementBuilderMapper.Get <SQLiteConnection>();
// Act
var query = builder.CreateMergeAll(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
null,
3,
new DbField("Id", true, false, false, typeof(int), null, null, null, null),
new DbField("Id", false, true, false, typeof(int), null, null, null, null));
var expected = "INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id, @Name, @Address ) ; SELECT CAST(COALESCE(last_insert_rowid(), @Id) AS INT) AS [Result] ; " +
"INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id_1, @Name_1, @Address_1 ) ; SELECT CAST(COALESCE(last_insert_rowid(), @Id_1) AS INT) AS [Result] ; " +
"INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id_2, @Name_2, @Address_2 ) ; SELECT CAST(COALESCE(last_insert_rowid(), @Id_2) AS INT) AS [Result] ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestMySqlStatementBuilderCreateMergeAllWithPrimaryAsQualifier()
{
// Setup
var builder = StatementBuilderMapper.Get <MySqlConnection>();
// Act
var query = builder.CreateMergeAll(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
Field.From("Id"),
3,
new DbField("Id", true, false, false, typeof(int), null, null, null, null),
null);
var expected = "INSERT INTO `Table` ( `Id`, `Name`, `Address` ) VALUES ( @Id, @Name, @Address ) ON DUPLICATE KEY UPDATE `Id` = @Id, `Name` = @Name, `Address` = @Address ; SELECT COALESCE(@Id, LAST_INSERT_ID()) AS `Result` ; " +
"INSERT INTO `Table` ( `Id`, `Name`, `Address` ) VALUES ( @Id_1, @Name_1, @Address_1 ) ON DUPLICATE KEY UPDATE `Id` = @Id_1, `Name` = @Name_1, `Address` = @Address_1 ; SELECT COALESCE(@Id_1, LAST_INSERT_ID()) AS `Result` ; " +
"INSERT INTO `Table` ( `Id`, `Name`, `Address` ) VALUES ( @Id_2, @Name_2, @Address_2 ) ON DUPLICATE KEY UPDATE `Id` = @Id_2, `Name` = @Name_2, `Address` = @Address_2 ; SELECT COALESCE(@Id_2, LAST_INSERT_ID()) AS `Result` ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestPostgreSqlStatementBuilderCreateMergeAllWithIdentity()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
var query = builder.CreateMergeAll(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
null,
3,
new DbField("Id", true, false, false, typeof(int), null, null, null, null),
new DbField("Id", false, true, false, typeof(int), null, null, null, null));
var expected = "INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) OVERRIDING SYSTEM VALUE VALUES ( @Id, @Name, @Address ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name, \"Address\" = @Address RETURNING CAST(\"Id\" AS INTEGER) AS \"Id\", @__RepoDb_OrderColumn_0 AS \"OrderColumn\" ; " +
"INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) OVERRIDING SYSTEM VALUE VALUES ( @Id_1, @Name_1, @Address_1 ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name_1, \"Address\" = @Address_1 RETURNING CAST(\"Id\" AS INTEGER) AS \"Id\", @__RepoDb_OrderColumn_1 AS \"OrderColumn\" ; " +
"INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) OVERRIDING SYSTEM VALUE VALUES ( @Id_2, @Name_2, @Address_2 ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name_2, \"Address\" = @Address_2 RETURNING CAST(\"Id\" AS INTEGER) AS \"Id\", @__RepoDb_OrderColumn_2 AS \"OrderColumn\" ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestSqLiteStatementBuilderCreateMergeAllWithPrimaryAsQualifier()
{
// Setup
var builder = StatementBuilderMapper.Get <SqliteConnection>();
// Act
var query = builder.CreateMergeAll(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
Field.From("Id"),
3,
new DbField("Id", true, false, false, typeof(int), null, null, null, null),
null);
var expected = "INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id, @Name, @Address ) ; SELECT CAST(@Id AS BIGINT) AS [Result] ; " +
"INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id_1, @Name_1, @Address_1 ) ; SELECT CAST(@Id_1 AS BIGINT) AS [Result] ; " +
"INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id_2, @Name_2, @Address_2 ) ; SELECT CAST(@Id_2 AS BIGINT) AS [Result] ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestPostgreSqlStatementBuilderCreateMergeAllWithPrimaryAsQualifier()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
var query = builder.CreateMergeAll(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
Field.From("Id"),
3,
new DbField("Id", true, false, false, typeof(int), null, null, null, null),
null);
var expected = "INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) VALUES ( @Id, @Name, @Address ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name, \"Address\" = @Address RETURNING @Id AS \"Result\" ; " +
"INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) VALUES ( @Id_1, @Name_1, @Address_1 ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name_1, \"Address\" = @Address_1 RETURNING @Id AS \"Result\" ; " +
"INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) VALUES ( @Id_2, @Name_2, @Address_2 ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name_2, \"Address\" = @Address_2 RETURNING @Id AS \"Result\" ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestBaseStatementBuilderCreateExistsWithWhereExpression()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var where = new QueryGroup(new QueryField("Id", 1));
// Act
var actual = statementBuilder.CreateExists(queryBuilder: queryBuilder,
tableName: tableName,
where : where);
var expected = $"" +
$"SELECT TOP (1) 1 AS [ExistsValue] " +
$"FROM [Table] " +
$"WHERE ([Id] = @Id) ;";
// Assert
Assert.AreEqual(expected, actual);
}
private void Setup(IDbSetting settings)
{
if (_isInitialized)
{
return;
}
lock (_mutex)
{
DbSettingMapper.Add(typeof(SQLiteConnection), settings, true);
// Map the DbHelper
DbHelperMapper.Add(typeof(SQLiteConnection), new SqLiteDbHelper(settings, new SdsSqLiteDbTypeNameToClientTypeResolver()), true);
// Map the Statement Builder
StatementBuilderMapper.Add(typeof(SQLiteConnection), new SqLiteStatementBuilder(settings), true);
// Set the flag
_isInitialized = true;
}
}
public void TestDbHelperForQueryAll()
{
// Prepare
var dbHelper = new Mock <IDbHelper>();
var connection = new CustomDbConnectionForDbHelper();
// Act
CommandTextCache.Flush();
DbFieldCache.Flush();
StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlStatementBuilder(), true);
DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true);
// Act
connection.QueryAll <DataEntityForDbHelper>();
// Assert
dbHelper.Verify(builder =>
builder.GetFields(
It.Is <string>(s => s == connection.ConnectionString),
It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>())), Times.Exactly(1));
}
public void TestSqlServerStatementBuilderCreateCountWithWhereExpression()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var where = new QueryGroup(new QueryField("Id", 1));
// Act
var actual = statementBuilder.CreateCount(queryBuilder: queryBuilder,
tableName: tableName,
where : where);
var expected = $"" +
$"SELECT COUNT_BIG (*) AS [CountValue] " +
$"FROM [Table] " +
$"WHERE ([Id] = @Id) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateQueryAllWithOrderBy()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var orderBy = OrderField.Parse(new { Field1 = Order.Ascending, Field2 = Order.Descending });
// Act
var actual = statementBuilder.CreateQueryAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
orderBy: orderBy);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [Table] " +
$"ORDER BY [Field1] ASC, [Field2] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateCountWithWhereExpressionAndWithHints()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var where = new QueryGroup(new QueryField("Id", 1));
var hints = "WITH (NOLOCK)";
// Act
var actual = statementBuilder.CreateCount(queryBuilder: queryBuilder,
tableName: tableName,
where : where,
hints: hints);
var expected = $"" +
$"SELECT COUNT (*) AS [CountValue] " +
$"FROM [Table] WITH (NOLOCK) " +
$"WHERE ([Id] = @Id) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateQueryWithWhereExpression()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var where = new QueryGroup(new QueryField("Id", 1));
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
where : where);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [Table] " +
$"WHERE ([Id] = @Id) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateInsertWithUnquotedTableSchema()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
// Act
var actual = statementBuilder.CreateInsert(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
primaryField: null,
identityField: null);
var expected = $"" +
$"INSERT INTO [dbo].[Table] " +
$"( [Field1], [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field3 ) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateSumWithWhereExpression()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var field = new Field("Value");
var where = new QueryGroup(new QueryField("Id", 1));
// Act
var actual = statementBuilder.CreateSum(queryBuilder: queryBuilder,
tableName: tableName,
field: field,
where : where);
var expected = $"" +
$"SELECT SUM ([Value]) AS [SumValue] " +
$"FROM [Table] " +
$"WHERE ([Id] = @Id) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestDbHelperForInsert()
{
// Prepare
var dbHelper = new Mock <IDbHelper>();
var connection = new CustomDbConnectionForDbHelper();
// Act
CommandTextCache.Flush();
DbFieldCache.Flush();
StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlServerStatementBuilder(), true);
DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true);
// Act
connection.Insert <DataEntityForDbHelper>(new DataEntityForDbHelper {
Id = 1, Name = "Name"
});
// Assert
dbHelper.Verify(builder =>
builder.GetFields(
It.Is <IDbConnection>(s => s == connection),
It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>()),
It.IsAny <IDbTransaction>()), Times.Exactly(1));
}
public void TestBaseStatementBuilderCreateInsertWithPrimary()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var primaryField = new DbField("Field1", true, false, false, typeof(int), null, null, null, null);
// Act
var actual = statementBuilder.CreateInsert(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
primaryField: primaryField,
identityField: null);
var expected = $"" +
$"INSERT INTO [Table] " +
$"( [Field1], [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field3 ) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlServerStatementBuilderCreateInsertAllWithIdentityForThreeBatches()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var identityField = new DbField("Field1", false, true, false, typeof(int), null, null, null, null);
// Act
var actual = statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
batchSize: 3,
primaryField: null,
identityField: identityField);
var expected = $"" +
$"INSERT INTO [Table] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2, @Field3 ) ; " +
$"SELECT CONVERT(INT, SCOPE_IDENTITY()) ; " +
$"INSERT INTO [Table] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2_1, @Field3_1 ) ; " +
$"SELECT CONVERT(INT, SCOPE_IDENTITY()) ; " +
$"INSERT INTO [Table] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2_2, @Field3_2 ) ; " +
$"SELECT CONVERT(INT, SCOPE_IDENTITY()) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateUpdateWithUnquotedTableSchema()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var where = (QueryGroup)null;
// Act
var actual = statementBuilder.CreateUpdate(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
where : where,
primaryField: null,
identityField: null);
var expected = $"" +
$"UPDATE [dbo].[Table] " +
$"SET [Field1] = @Field1, [Field2] = @Field2, [Field3] = @Field3 ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlServerStatementBuilderCreateInsert()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
// Act
var actual = statementBuilder.CreateInsert(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
primaryField: null,
identityField: null);
var expected = $"" +
$"INSERT INTO [Table] " +
$"( [Field1], [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field3 ) ; " +
$"SELECT NULL AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateQueryAllWithOrderByAndWithHints()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var orderBy = OrderField.Parse(new { Field1 = Order.Ascending, Field2 = Order.Descending });
var hints = SqlServerTableHints.NoLock;
// Act
var actual = statementBuilder.CreateQueryAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
orderBy: orderBy,
hints: hints);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [Table] WITH (NOLOCK) " +
$"ORDER BY [Field1] ASC, [Field2] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void Initialize()
{
DbSettingMapper.Add(typeof(CacheDbConnection), new CustomDbSetting(), true);
StatementBuilderMapper.Add(typeof(CacheDbConnection), new CustomStatementBuilder(), true);
}
public void Initialize()
{
DbSettingMapper.Add <CacheDbConnection>(new CustomDbSetting(), true);
DbHelperMapper.Add <CacheDbConnection>(new CustomDbHelper(), true);
StatementBuilderMapper.Add <CacheDbConnection>(new CustomStatementBuilder(), true);
}
public void Initialize()
{
StatementBuilderMapper.Add <BaseStatementBuilderDbConnection>(new CustomBaseStatementBuilder(), true);
StatementBuilderMapper.Add <NonHintsSupportingBaseStatementBuilderDbConnection>(new CustomNonHintsSupportingBaseStatementBuilder(), true);
}
public void Initialize()
{
StatementBuilderMapper.Add(typeof(BaseStatementBuilderDbConnection), new CustomBaseStatementBuilder(), true);
}
public void Initialize()
{
DbSettingMapper.Add(typeof(PropertyHandlerConnection), new CustomDbSetting(), true);
DbHelperMapper.Add(typeof(PropertyHandlerConnection), new CustomDbHelper(), true);
StatementBuilderMapper.Add(typeof(PropertyHandlerConnection), new CustomStatementBuilder(), true);
}
public void Cleanup()
{
StatementBuilderMapper.Clear();
}
public void Initialize()
{
StatementBuilderMapper.Add <BaseStatementBuilderDbConnection>(new CustomBaseStatementBuilder(), true);
}
public void Initialize()
{
StatementBuilderMapper.Add(typeof(BaseStatementBuilderDbConnection), new CustomBaseStatementBuilder(), true);
StatementBuilderMapper.Add(typeof(DefinedBaseStatementBuilderDbConnection), new CustomDefinedBaseStatementBuilder(), true);
StatementBuilderMapper.Add(typeof(NonHintsSupportingBaseStatementBuilderDbConnection), new CustomNonHintsSupportingBaseStatementBuilder(), true);
}
static Helper()
{
StatementBuilder = StatementBuilderMapper.Get <SqlConnection>();
EpocDate = new DateTime(1970, 1, 1, 0, 0, 0);
}
public static void ClassInitialize(TestContext context)
{
DbSettingMapper.Add(typeof(TraceDbConnection), new CustomDbSetting(), true);
DbHelperMapper.Add(typeof(TraceDbConnection), new CustomDbHelper(), true);
StatementBuilderMapper.Add(typeof(TraceDbConnection), new CustomStatementBuilder(), true);
}
