public void PageWithWhereAndOrderByMultiLine()
{
var sqlDialect = new PostgreSqlDialect();
var sqlQuery = new SqlQuery(@"SELECT
CustomerId,
Name,
DateOfBirth,
CustomerStatusId
FROM
Customers
WHERE
CustomerStatusId = @p0
ORDER BY
Name ASC", CustomerStatus.Active);
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 OFFSET @p2", paged.CommandText);
Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(25, paged.Arguments[1].Value);
Assert.Equal(DbType.Int32, paged.Arguments[2].DbType);
Assert.Equal(0, paged.Arguments[2].Value);
}
public void PageQueryThrowsArgumentNullExceptionForNullSqlCharacters()
{
var sqlDialect = new PostgreSqlDialect();
var exception = Assert.Throws <ArgumentNullException>(
() => sqlDialect.PageQuery(null, PagingOptions.None));
}
public void GetPagingSql()
{
var dialect = new PostgreSqlDialect();
var dictionary = new Dictionary <string, object>();
Assert.Equal("select * from TestEntity LIMIT @maxResults OFFSET @pageStartRowNbr", dialect.GetPagingSql("select * from TestEntity", 1, 2, dictionary));
Assert.Equal(2, dictionary["@maxResults"]);
Assert.Equal(2, dictionary["@pageStartRowNbr"]);
}
public void GetColumnNameShouldThrowsExceptionIfSqlIsNullOrEmpty(string sql)
{
var dialect = new PostgreSqlDialect();
Assert.Throws <ArgumentNullException>("columnName", () =>
{
dialect.GetColumnName(string.Empty, sql, string.Empty);
});
}
public void BuildSelectInsertIdSqlQuery()
{
var sqlDialect = new PostgreSqlDialect();
var sqlQuery = sqlDialect.BuildSelectInsertIdSqlQuery(ObjectInfo.For(typeof(Customer)));
Assert.Equal("SELECT lastval()", sqlQuery.CommandText);
Assert.Equal(0, sqlQuery.Arguments.Count);
Assert.Equal(0, sqlQuery.Arguments.Count);
}
public static void QuoteIdentifier_GivenNameWithQuoteAtStartAndEnd_ReturnsQuotedIdentifier()
{
const string input = "\"test\"table";
const string expected = "\"\"\"test\"\"table\"";
var dialect = new PostgreSqlDialect();
var result = dialect.QuoteIdentifier(input);
Assert.That(result, Is.EqualTo(expected));
}
public static void QuoteIdentifier_GivenNameWithDotSeparators_ReturnsQuotedIdentifier()
{
const string input = "test.table.name";
const string expected = "\"test.table.name\"";
var dialect = new PostgreSqlDialect();
var result = dialect.QuoteIdentifier(input);
Assert.That(result, Is.EqualTo(expected));
}
public static void QuoteIdentifier_GivenRegularLocalName_ReturnsQuotedIdentifier()
{
const string input = "test_table";
const string expected = "\"test_table\"";
var dialect = new PostgreSqlDialect();
var result = dialect.QuoteIdentifier(input);
Assert.That(result, Is.EqualTo(expected));
}
public void Upsert_WhenUpsertNotAutoGenerateMapper_ShouldGetCorrectSql()
{
var sut = new PostgreSqlDialect();
var mapper = GetClassMapper();
var result = sut.GetUpsertSql(mapper);
var expected = "INSERT INTO \"table\" (\"id\", \"string\", \"datetime\", \"guid\") VALUES (@Id, @String, @DateTime, @Guid) ON CONFLICT (\"id\") DO UPDATE SET \"id\" = @Id, \"string\" = @String, \"datetime\" = @DateTime, \"guid\" = @Guid;";
Assert.Equal(expected, result);
}
public void PageWithNoWhereOrOrderBySecondResultsPage()
{
var sqlDialect = new PostgreSqlDialect();
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 OFFSET @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 PageNonQualifiedWildcardQuery()
{
var sqlDialect = new PostgreSqlDialect();
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 OFFSET @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(0, paged.Arguments[1].Value);
}
public void UpdateInstanceQuery()
{
ObjectInfo.MappingConvention = new ConventionMappingConvention(
UnitTest.GetConventionMappingSettings(IdentifierStrategy.Assigned));
var sqlDialect = new PostgreSqlDialect();
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 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(DbType.Decimal, sqlQuery.Arguments[0].DbType);
Assert.Equal(customer.CreditLimit, sqlQuery.Arguments[0].Value);
Assert.Equal(DbType.DateTime, sqlQuery.Arguments[1].DbType);
Assert.Equal(customer.DateOfBirth, sqlQuery.Arguments[1].Value);
Assert.Equal(DbType.String, sqlQuery.Arguments[2].DbType);
Assert.Equal(customer.Name, sqlQuery.Arguments[2].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType);
Assert.Equal((int)customer.Status, sqlQuery.Arguments[3].Value);
Assert.Equal(DbType.DateTime, sqlQuery.Arguments[4].DbType);
Assert.Equal(customer.Updated, sqlQuery.Arguments[4].Value);
Assert.Equal(DbType.String, sqlQuery.Arguments[5].DbType);
Assert.Equal("http://microliteorm.wordpress.com/", sqlQuery.Arguments[5].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[6].DbType);
Assert.Equal(customer.Id, sqlQuery.Arguments[6].Value);
}
public static ISqlDialect ConvertESqlDialect(ESqlDialect sqlDialect)
{
ISqlDialect SqlDialect = null;
switch (sqlDialect)
{
case ESqlDialect.DB2:
SqlDialect = new DB2Dialect();
break;
case ESqlDialect.MySQL:
SqlDialect = new MySqlDialect();
break;
case ESqlDialect.Oracle:
SqlDialect = new OracleDialect();
break;
case ESqlDialect.PostgreSql:
SqlDialect = new PostgreSqlDialect();
break;
case ESqlDialect.SqlCe:
SqlDialect = new SqlCeDialect();
break;
case ESqlDialect.Sqlite:
SqlDialect = new SqliteDialect();
break;
case ESqlDialect.SqlServer:
SqlDialect = new SqlServerDialect();
break;
}
return(SqlDialect);
}
public void GetIdentitySql()
{
var dialect = new PostgreSqlDialect();
Assert.Equal("SELECT LASTVAL() AS Id", dialect.GetIdentitySql("table"));
}
public static void QuoteName_GivenNullOrWhiteSpaceName_ThrowsArgumentNullException(string name)
{
var dialect = new PostgreSqlDialect();
Assert.That(() => dialect.QuoteName(name), Throws.ArgumentNullException);
}
public static void QuoteIdentifier_GivenNullOrWhiteSpaceIdentifier_ThrowsArgumentNullException(string identifier)
{
var dialect = new PostgreSqlDialect();
Assert.That(() => dialect.QuoteIdentifier(identifier), Throws.ArgumentNullException);
}
public void SetUp()
{
_dialect = new PostgreSqlDialect();
}
public void SupportsSelectInsertedIdentifierReturnsTrue()
{
var sqlDialect = new PostgreSqlDialect();
Assert.True(sqlDialect.SupportsSelectInsertedIdentifier);
}
public void GetColumnName()
{
var dialect = new PostgreSqlDialect();
Assert.Equal("\"name\" AS \"first_name\"", dialect.GetColumnName("pr", "name", "first_name"));
}
public void Setup()
{
Dialect = new PostgreSqlDialect();
}
public PostgreSqlDialectTests()
{
_dialect = new PostgreSqlDialect();
}