public void InsertInstanceQueryForIdentifierStrategyDbGenerated()
{
ObjectInfo.MappingConvention = new ConventionMappingConvention(
UnitTest.GetConventionMappingSettings(IdentifierStrategy.DbGenerated));
var customer = new Customer
{
Created = new DateTime(2011, 12, 24),
CreditLimit = 10500.00M,
DateOfBirth = new System.DateTime(1975, 9, 18),
Id = 134875,
Name = "Joe Bloggs",
Status = CustomerStatus.Active,
Updated = DateTime.Now,
Website = new Uri("http://microliteorm.wordpress.com")
};
var sqlDialect = new MySqlDialect();
var sqlQuery = sqlDialect.BuildInsertSqlQuery(ObjectInfo.For(typeof(Customer)), customer);
Assert.Equal("INSERT INTO `Sales`.`Customers` (`Created`,`CreditLimit`,`DateOfBirth`,`Name`,`CustomerStatusId`,`Website`) VALUES (@p0,@p1,@p2,@p3,@p4,@p5)", sqlQuery.CommandText);
Assert.Equal(6, sqlQuery.Arguments.Count);
Assert.Equal(customer.Created, sqlQuery.Arguments[0]);
Assert.Equal(customer.CreditLimit, sqlQuery.Arguments[1]);
Assert.Equal(customer.DateOfBirth, sqlQuery.Arguments[2]);
Assert.Equal(customer.Name, sqlQuery.Arguments[3]);
Assert.Equal(1, sqlQuery.Arguments[4]);
Assert.Equal("http://microliteorm.wordpress.com/", sqlQuery.Arguments[5]);
}
public void BuildSelectInsertIdSqlQuery()
{
var sqlDialect = new MySqlDialect();
var sqlQuery = sqlDialect.BuildSelectInsertIdSqlQuery(ObjectInfo.For(typeof(Customer)));
Assert.Equal("SELECT LAST_INSERT_ID()", sqlQuery.CommandText);
Assert.Equal(0, sqlQuery.Arguments.Count);
}
public void PageNonQualifiedQuery()
{
var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers");
var sqlDialect = new MySqlDialect();
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers LIMIT @p0,@p1", paged.CommandText);
Assert.Equal(0, paged.Arguments[0]);
Assert.Equal(25, paged.Arguments[1]);
}
public void UpdateInstanceQueryForIdentifierStrategyAssigned()
{
ObjectInfo.MappingConvention = new ConventionMappingConvention(
UnitTest.GetConventionMappingSettings(IdentifierStrategy.Assigned));
var customer = new Customer
{
Created = new DateTime(2011, 12, 24),
CreditLimit = 10500.00M,
DateOfBirth = new System.DateTime(1975, 9, 18),
Id = 134875,
Name = "Joe Bloggs",
Status = CustomerStatus.Active,
Updated = DateTime.Now,
Website = new Uri("http://microliteorm.wordpress.com")
};
var sqlDialect = new MySqlDialect();
var sqlQuery = sqlDialect.BuildUpdateSqlQuery(ObjectInfo.For(typeof(Customer)), customer);
Assert.Equal("UPDATE `Sales`.`Customers` SET `CreditLimit` = @p0,`DateOfBirth` = @p1,`Name` = @p2,`CustomerStatusId` = @p3,`Updated` = @p4,`Website` = @p5 WHERE `Id` = @p6", sqlQuery.CommandText);
Assert.Equal(7, sqlQuery.Arguments.Count);
Assert.Equal(customer.CreditLimit, sqlQuery.Arguments[0]);
Assert.Equal(customer.DateOfBirth, sqlQuery.Arguments[1]);
Assert.Equal(customer.Name, sqlQuery.Arguments[2]);
Assert.Equal(1, sqlQuery.Arguments[3]);
Assert.Equal(customer.Updated, sqlQuery.Arguments[4]);
Assert.Equal("http://microliteorm.wordpress.com/", sqlQuery.Arguments[5]);
Assert.Equal(customer.Id, sqlQuery.Arguments[6]);
}
public void SupportsIdentityReturnsTrue()
{
var sqlDialect = new MySqlDialect();
Assert.True(sqlDialect.SupportsIdentity);
}
public void PageWithWhereButNoOrderBy()
{
var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0", CustomerStatus.Active);
var sqlDialect = new MySqlDialect();
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0 LIMIT @p1,@p2", paged.CommandText);
Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]);
Assert.Equal(0, paged.Arguments[1]);
Assert.Equal(25, paged.Arguments[2]);
}
public void PageWithWhereAndOrderByMultiLine()
{
var sqlQuery = new SqlQuery(@"SELECT
CustomerId,
Name,
DateOfBirth,
CustomerStatusId
FROM
Customers
WHERE
CustomerStatusId = @p0
ORDER BY
Name ASC", new object[] { CustomerStatus.Active });
var sqlDialect = new MySqlDialect();
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0 ORDER BY Name ASC LIMIT @p1,@p2", paged.CommandText);
Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]);
Assert.Equal(0, paged.Arguments[1]);
Assert.Equal(25, paged.Arguments[2]);
}
public void PageQueryThrowsArgumentNullExceptionForNullSqlCharacters()
{
var sqlDialect = new MySqlDialect();
var exception = Assert.Throws<ArgumentNullException>(
() => sqlDialect.PageQuery(null, PagingOptions.None));
}
public void SupportsSelectInsertedIdentifierReturnsTrue()
{
var sqlDialect = new MySqlDialect();
Assert.True(sqlDialect.SupportsSelectInsertedIdentifier);
}
public void PageWithNoWhereOrOrderBySecondResultsPage()
{
var sqlDialect = new MySqlDialect();
var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 2, resultsPerPage: 25));
Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers LIMIT @p0,@p1", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(25, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(25, paged.Arguments[1].Value);
}
public void PageWithMultiWhereAndMultiOrderByMultiLine()
{
var sqlDialect = new MySqlDialect();
var sqlQuery = new SqlQuery(@"SELECT
CustomerId,
Name,
DateOfBirth,
CustomerStatusId
FROM
Customers
WHERE
(CustomerStatusId = @p0 AND DoB > @p1)
ORDER BY
Name ASC,
DoB ASC", CustomerStatus.Active, new DateTime(1980, 01, 01));
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE (CustomerStatusId = @p0 AND DoB > @p1) ORDER BY Name ASC, DoB ASC LIMIT @p2,@p3", paged.CommandText);
Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]);
Assert.Equal(sqlQuery.Arguments[1], paged.Arguments[1]);
Assert.Equal(DbType.Int32, paged.Arguments[2].DbType);
Assert.Equal(0, paged.Arguments[2].Value);
Assert.Equal(DbType.Int32, paged.Arguments[3].DbType);
Assert.Equal(25, paged.Arguments[3].Value);
}
public void PageNonQualifiedWildcardQuery()
{
var sqlDialect = new MySqlDialect();
var sqlQuery = new SqlQuery("SELECT * FROM Customers");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT * FROM Customers LIMIT @p0,@p1", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(0, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(25, paged.Arguments[1].Value);
}