public void TestMySqlStatementBuilderCreateInsertWithIdentity()
{
// Setup
var builder = StatementBuilderMapper.Get <MySqlConnection>();
// Act
var query = builder.CreateInsert(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
null,
new DbField("Id", false, true, false, typeof(int), null, null, null, null));
var expected = "INSERT INTO `Table` ( `Name`, `Address` ) VALUES ( @Name, @Address ) ; SELECT LAST_INSERT_ID() AS `Result` ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestMySqlStatementBuilderCreateSumWithExpression()
{
// Setup
var builder = StatementBuilderMapper.Get <MySqlConnection>();
// Act
var query = builder.CreateSum(new QueryBuilder(),
"Table",
new Field("Field", typeof(int)),
QueryGroup.Parse(new { Id = 1 }),
null);
var expected = "SELECT SUM(`Field`) AS `SumValue` FROM `Table` WHERE (`Id` = @Id) ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestMySqlStatementBuilderCreateSum()
{
// Setup
var builder = StatementBuilderMapper.Get <MySqlConnection>();
// Act
var query = builder.CreateSum(new QueryBuilder(),
"Table",
new Field("Field", typeof(int)),
null,
null);
var expected = "SELECT SUM(`Field`) AS `SumValue` FROM `Table` ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestMySqlStatementBuilderCreateInsert()
{
// Setup
var builder = StatementBuilderMapper.Get <MySqlConnection>();
// Act
var query = builder.CreateInsert(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
null,
null);
var expected = "INSERT INTO `Table` ( `Id`, `Name`, `Address` ) VALUES ( @Id, @Name, @Address ) ; SELECT NULL AS `Result` ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestBaseStatementBuilderCreateExistsWithHints()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var hints = "WITH (NOLOCK)";
// Act
var actual = statementBuilder.CreateExists(queryBuilder: queryBuilder,
tableName: tableName,
hints: hints);
var expected = "SELECT TOP (1) 1 AS [ExistsValue] FROM [Table] WITH (NOLOCK) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void ThrowExceptionOnBaseStatementBuilderCreateInsertIfTheIdentityIsNotReallyAnIdentity()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var qualifiers = Field.From("Field1");
var identifyField = new DbField("Field2", false, false, false, typeof(int), null, null, null, null);
// Act
statementBuilder.CreateInsert(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
primaryField: null,
identityField: identifyField);
}
public void TestMySqlStatementBuilderCreateBatchQueryWithPage()
{
// Setup
var builder = StatementBuilderMapper.Get <MySqlConnection>();
// Act
var query = builder.CreateBatchQuery(new QueryBuilder(),
"Table",
Field.From("Id", "Name"),
3,
10,
OrderField.Parse(new { Id = Order.Ascending }));
var expected = "SELECT `Id`, `Name` FROM `Table` ORDER BY `Id` ASC LIMIT 30, 10 ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestBaseStatementBuilderCreateQueryAllWithUnquotedTableSchema()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
// Act
var actual = statementBuilder.CreateQueryAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
var expected = "SELECT [Field1], [Field2], [Field3] FROM [dbo].[Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void ThrowExceptionOnSqlServerStatementBuilderCreateMergeAllIfThereAreNoFields()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var qualifiers = Field.From("Id");
// Act
statementBuilder.CreateMergeAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: null,
qualifiers: qualifiers,
batchSize: 1,
primaryField: null,
identityField: null);
}
public void ThrowExceptionOnBaseStatementBuilderCreateInsertAllIfThePrimaryIsNotReallyAPrimary()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var primaryField = new DbField("Field1", false, false, false, typeof(int), null, null, null, null);
// Act
statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
batchSize: 1,
primaryField: primaryField,
identityField: null);
}
public void TestBaseStatementBuilderCreateQuery()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
var expected = "SELECT [Field1], [Field2], [Field3] FROM [Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqLiteStatementBuilderCreateMergeWithIdentity()
{
// Setup
var builder = StatementBuilderMapper.Get <SQLiteConnection>();
// Act
var query = builder.CreateMerge(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
null,
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 BIGINT) AS [Result] ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestBaseStatementBuilderCreateDeleteWithHints()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
// Act
var actual = statementBuilder.CreateDelete(queryBuilder: queryBuilder,
tableName: tableName,
where : null,
hints: "WITH (TABLOCK)");
var expected = "DELETE FROM [Table] WITH (TABLOCK) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void ThrowExceptionOnSqlServerStatementBuilderCreateMergeIfThePrimaryIsNotReallyAPrimary()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var primaryField = new DbField("Field1", false, false, false, typeof(int), null, null, null, null);
// Act
statementBuilder.CreateMerge(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: null,
primaryField: primaryField,
identityField: null);
}
public void ThrowExceptionOnSqlServerStatementBuilderCreateMergeIfTheTableIsWhitespace()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection));
var queryBuilder = new QueryBuilder();
var tableName = " ";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var qualifiers = Field.From("Field1");
// Act
statementBuilder.CreateMerge(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: qualifiers,
primaryField: null,
identityField: null);
}
public void ThrowExceptionOnSqlServerStatementBuilderCreateBatchQueryIfTheFieldsAreNull()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var orderBy = OrderField.Parse(new { Field1 = Order.Ascending });
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: null,
page: 0,
rowsPerBatch: 10,
orderBy: orderBy,
where : null);
}
public void ThrowExceptionOnSqlServerStatementBuilderCreateBatchQueryIfThereAreNoOrderFields()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2" });
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 0,
rowsPerBatch: 10,
orderBy: null,
where : null);
}
public void TestSqlServerStatementBuilderCreateCountWithHints()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var hints = "WITH (NOLOCK)";
// Act
var actual = statementBuilder.CreateCount(queryBuilder: queryBuilder,
tableName: tableName,
hints: hints);
var expected = "SELECT COUNT_BIG (*) AS [CountValue] FROM [Table] WITH (NOLOCK) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateCountAllWithHints()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var hints = "WITH (NOLOCK)";
// Act
var actual = statementBuilder.CreateCountAll(queryBuilder: queryBuilder,
tableName: tableName,
hints: hints);
var expected = "SELECT COUNT (*) AS [CountValue] FROM [Table] WITH (NOLOCK) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestPostgreSqlStatementBuilderCreateMergeWithIdentity()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
var query = builder.CreateMerge(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
null,
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 \"Result\" ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestSdsSqLiteStatementBuilderCreateBatchQueryWithPage()
{
// Setup
var builder = StatementBuilderMapper.Get <SQLiteConnection>();
// Act
var query = builder.CreateBatchQuery(new QueryBuilder(),
"Table",
Field.From("Id", "Name"),
3,
10,
OrderField.Parse(new { Id = Order.Ascending }));
var expected = "SELECT [Id], [Name] FROM [Table] ORDER BY [Id] ASC LIMIT 30, 10 ;";
// Assert
Assert.AreEqual(expected, query);
}
public void ThrowExceptionOnBaseStatementBuilderCreateUpdateAllIfThereAreNoQualifiers()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
// Act
statementBuilder.CreateUpdateAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: null,
batchSize: 1,
primaryField: null,
identityField: null);
}
public void TestPostgreSqlStatementBuilderCreateBatchQueryWithPage()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
var query = builder.CreateBatchQuery(new QueryBuilder(),
"Table",
Field.From("Id", "Name"),
3,
10,
OrderField.Parse(new { Id = Order.Ascending }));
var expected = "SELECT \"Id\", \"Name\" FROM \"Table\" ORDER BY \"Id\" ASC LIMIT 10 OFFSET 30 ;";
// Assert
Assert.AreEqual(expected, query);
}
public void ThrowExceptionOnBaseStatementBuilderCreateUpdateAllIfTheBatchSizeIsGreaterThan1AndTheMultipleStatementExecutionIsNotSupported()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get(typeof(SingleStatementSupportBaseStatementBuilderDbConnection));
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" });
var qualifiers = Field.From("Field1");
// Act
statementBuilder.CreateUpdateAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: qualifiers,
batchSize: 10,
primaryField: null,
identityField: null);
}
public void TestPostgreSqlStatementBuilderCreateQueryOrderByFieldsMultiDirection()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
var query = builder.CreateQuery(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
null,
OrderField.Parse(new { Id = Order.Ascending, Name = Order.Descending }),
null,
null);
var expected = "SELECT \"Id\", \"Name\", \"Address\" FROM \"Table\" ORDER BY \"Id\" ASC, \"Name\" DESC ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestPostgreSqlStatementBuilderCreateQueryWithTop()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
var query = builder.CreateQuery(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
null,
null,
10,
null);
var expected = "SELECT \"Id\", \"Name\", \"Address\" FROM \"Table\" LIMIT 10 ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestPostgreSqlStatementBuilderCreateQueryWithExpression()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
var query = builder.CreateQuery(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
QueryGroup.Parse(new { Id = 1, Name = "Michael" }),
null,
null,
null);
var expected = "SELECT \"Id\", \"Name\", \"Address\" FROM \"Table\" WHERE (\"Id\" = @Id AND \"Name\" = @Name) ;";
// Assert
Assert.AreEqual(expected, query);
}
public void TestBaseStatementBuilderCreateAverageAllWithUnquotedTableSchema()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
var field = new Field("Value");
// Act
var actual = statementBuilder.CreateAverageAll(queryBuilder: queryBuilder,
tableName: tableName,
field: field,
hints: null);
var expected = "SELECT AVG ([Value]) AS [AverageValue] FROM [dbo].[Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateAverageAllForNonAverageableFieldType()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <DefinedBaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var field = new Field("Value", typeof(string));
// Act
var actual = statementBuilder.CreateAverageAll(queryBuilder: queryBuilder,
field: field,
tableName: tableName,
hints: null);
var expected = "SELECT AVG (CONVERT([NVARCHAR], [Value])) AS [AverageValue] FROM [Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestBaseStatementBuilderCreateMax()
{
// Setup
var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var field = new Field("Value");
// Act
var actual = statementBuilder.CreateMax(queryBuilder: queryBuilder,
field: field,
tableName: tableName,
hints: null);
var expected = "SELECT MAX ([Value]) AS [MaxValue] FROM [Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
