public void TestCriteria()
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery {Source = new Source("bob")};
//---------------Execute Test ----------------------
var criteria = new Criteria("test", Criteria.ComparisonOp.Equals, "testValue");
selectQuery.Criteria = criteria;
//---------------Test Result -----------------------
Assert.AreSame(criteria, selectQuery.Criteria);
//---------------Tear Down -------------------------
}
public void TestConstruct()
{
//---------------Set up test pack-------------------
//---------------Execute Test ----------------------
var selectQuery = new SelectQuery();
//---------------Test Result -----------------------
Assert.IsNull(selectQuery.Criteria);
Assert.AreEqual(0, selectQuery.Fields.Count);
Assert.AreEqual(0, selectQuery.OrderCriteria.Fields.Count);
Assert.IsNull(selectQuery.Source);
//---------------Tear Down -------------------------
}
private static void AddAllPropsToQuery(IClassDef classDef, SelectQuery selectQuery)
{
foreach (IPropDef propDef in classDef.PropDefColIncludingInheritance.ToList())
{
if (propDef.Persistable)
{
IClassDef fieldClassDef = classDef;
if (!((ClassDef)classDef).IsUsingConcreteTableInheritance())
fieldClassDef = propDef.ClassDef;
QueryField queryField = CreateQueryField(fieldClassDef, propDef);
selectQuery.Fields.Add(propDef.PropertyName, queryField);
}
}
}
public void TestSetCriteria_AddsJoins()
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery();
const string sourceName = "mysource";
selectQuery.Source = new Source(sourceName);
var fieldSource = new Source(sourceName);
var expectedSourceName = TestUtil.GetRandomString();
fieldSource.JoinToSource(new Source(expectedSourceName));
var field = new QueryField("testfield", "testfield", fieldSource);
var criteria = new Criteria(field, Criteria.ComparisonOp.Equals, "value");
//---------------Execute Test ----------------------
selectQuery.Criteria = criteria;
//---------------Test Result -----------------------
Assert.AreEqual(1, selectQuery.Source.Joins.Count);
Assert.AreEqual(selectQuery.Source, selectQuery.Source.Joins[0].FromSource);
Assert.AreEqual(expectedSourceName, selectQuery.Source.Joins[0].ToSource.Name);
}
/// <summary>
/// Creates a SelectQuery using the given classdef with the given Criteria. All information in the ClassDef will be taken into account
/// (such as inheritance structures).
/// </summary>
/// <param name="classDef">The <see cref="ClassDef" /> to create the SelectQuery for.</param>
/// <param name="criteria">The criteria to be set on the SelectQuery</param>
/// <returns>A SelectQuery that can be used to load objects of the type the given ClassDef represents</returns>
public static ISelectQuery CreateSelectQuery(IClassDef classDef, Criteria criteria)
{
if (classDef == null) throw new ArgumentNullException("classDef");
SelectQuery selectQuery = new SelectQuery();
AddAllPropsToQuery(classDef, selectQuery);
//Add discriminator Criteria for Inheritance (e.g. single table Inheritance).
Criteria discriminatorCriteria = null;
AddDiscriminatorFields(selectQuery, classDef, ref discriminatorCriteria);
selectQuery.DiscriminatorCriteria = discriminatorCriteria;
Source source = null;
PrepareSource(classDef, ref source);
selectQuery.Source = source;
//foreach (var queryField in selectQuery.Fields)
//{
// //if (queryField.Value.Source.Name == classDef.ClassName)
// queryField.Value.Source = source;
//}
PrepareCriteria(classDef, criteria);
PrepareDiscriminatorCriteria(classDef, discriminatorCriteria);
selectQuery.Criteria = criteria;
selectQuery.ClassDef = classDef;
return selectQuery;
}
public void TestCreateSqlStatement_WithNoLimit_AtBeginning()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub_LimitClauseAtBeginning();
SelectQuery selectQuery = new SelectQuery {Limit = (-1)};
const string fieldName = "Field1";
selectQuery.Fields.Add(fieldName, new QueryField(fieldName, fieldName, null));
selectQuery.Source = new Source("Table1");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.StartsWith("SELECT [Field1]", statementString);
//---------------Tear Down -------------------------
}
public void TestCreateSqlStatement_NoSourceNameInQueryFields()
{
//---------------Set up test pack-------------------
ISelectQuery selectQuery = new SelectQuery();
selectQuery.Fields.Add("Surname", new QueryField("Surname", "Surname", null));
selectQuery.Fields.Add("ContactPersonID", new QueryField("ContactPersonID", "ContactPersonID", null));
selectQuery.Source = new Source("bob");
//---------------Execute Test ----------------------
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.AreEqualIgnoringCase("SELECT [Surname], [ContactPersonID] FROM [bob] a1", statementString);
//---------------Tear Down -------------------------
}
public void Test_CreateSQL_ShouldUseAliasesInOrderByClause_WhenOrderByFieldSourceIsSpecified()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
var mysource = new Source("mysource");
selectQuery.Source = mysource;
QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource);
selectQuery.Fields.Add(fieldOnMySource.FieldName, fieldOnMySource);
var orderCriteriaField = OrderCriteriaField.FromString("testfield");
orderCriteriaField.Source = mysource;
selectQuery.OrderCriteria = new OrderCriteria();
selectQuery.OrderCriteria.Add(orderCriteriaField);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(
"SELECT a1.[testfield] FROM [mysource] a1 ORDER BY a1.[testfield] ASC", statement.Statement.ToString());
}
public void Test_CreateSQL_ShouldUseAliasesInCriteria_WhenNotCriteria()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
const string sourceName = "mysource";
var source1 = new Source(sourceName);
selectQuery.Source = source1;
Source field1Source = source1;
QueryField field1 = new QueryField("testfield", "testfield", field1Source);
selectQuery.Fields.Add(field1.FieldName, field1);
selectQuery.Criteria = new Criteria(
null,
Criteria.LogicalOp.Not,
new Criteria(field1, Criteria.ComparisonOp.Is, null));
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase("SELECT a1.[testfield] FROM [mysource] a1 WHERE NOT (a1.[testfield] IS NULL)", statement.Statement.ToString());
}
public void TestSetOrderCriteria_AddsJoinToSource()
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery();
const string sourceName = "mysource";
selectQuery.Source = new Source(sourceName);
var orderSource = new Source(sourceName);
var expectedSourceName = TestUtil.GetRandomString();
orderSource.JoinToSource(new Source(expectedSourceName));
var orderOrderCriteriaField = new OrderCriteriaField("testfield", "testfield", orderSource, SortDirection.Ascending);
var orderCriteria = new OrderCriteria().Add(orderOrderCriteriaField);
//---------------Execute Test ----------------------
selectQuery.OrderCriteria = orderCriteria;
//---------------Test Result -----------------------
Assert.AreEqual(1, selectQuery.Source.Joins.Count);
Assert.AreEqual(selectQuery.Source, selectQuery.Source.Joins[0].FromSource);
Assert.AreEqual(expectedSourceName, selectQuery.Source.Joins[0].ToSource.Name);
}
public void TestFirstRecordToLoad()
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery();
//---------------Execute Test ----------------------
const int firstRecordToLoad = 40;
selectQuery.FirstRecordToLoad = firstRecordToLoad;
//---------------Test Result -----------------------
Assert.AreEqual(firstRecordToLoad, selectQuery.FirstRecordToLoad);
//---------------Tear Down -------------------------
}
public void TestLimit_NotSet()
{
//---------------Set up test pack-------------------
//---------------Execute Test ----------------------
var selectQuery = new SelectQuery();
//---------------Test Result -----------------------
Assert.AreEqual(-1, selectQuery.Limit);
Assert.AreEqual(0, selectQuery.FirstRecordToLoad);
//---------------Tear Down -------------------------
}
public void TestLimit()
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery();
//---------------Execute Test ----------------------
const int limit = 40;
selectQuery.Limit = limit;
//---------------Test Result -----------------------
Assert.AreEqual(limit, selectQuery.Limit);
//---------------Tear Down -------------------------
}
public void TestSource()
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery();
//---------------Execute Test ----------------------
Source source = new Source("testsource");
selectQuery.Source = source;
//---------------Test Result -----------------------
Assert.AreSame(source, selectQuery.Source);
//---------------Tear Down -------------------------
}
public void TestOrderCriteriaWithNoSource()
{
try
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery();
//---------------Execute Test ----------------------
var orderCriteria = new OrderCriteria().Add("testfield");
selectQuery.OrderCriteria = orderCriteria;
//---------------Test Result -----------------------
Assert.AreSame(orderCriteria, selectQuery.OrderCriteria);
//---------------Tear Down -------------------------
Assert.Fail("Expected to throw an HabaneroApplicationException");
}
//---------------Test Result -----------------------
catch (HabaneroApplicationException ex)
{
StringAssert.Contains("You cannot set an OrderCriteria for a SelectQuery if no Source has been set", ex.Message);
}
}
public void TestCreateSqlStatement_WithNoLimit_AtEnd()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub(new SqlFormatter("", "", "", "LIMIT"));
SelectQuery selectQuery = new SelectQuery();
selectQuery.Limit = -1;
const string fieldName = "Field1";
selectQuery.Fields.Add(fieldName, new QueryField(fieldName, fieldName, null));
selectQuery.Source = new Source("Table1");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.EndsWith("FROM [Table1] a1", statementString);
//---------------Tear Down -------------------------
}
public void Test_SetOrderCriteriaToNull_NoError()
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery
{
Source = new Source(TestUtil.GetRandomString()),
OrderCriteria = null
};
//---------------Execute Test ----------------------
//---------------Test Result -----------------------
Assert.IsNull(selectQuery.OrderCriteria);
}
public void TestSetupAliases_SetsAliasOnSource()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
const string sourceName = "mysource";
var source1 = new Source(sourceName);
selectQuery.Source = source1;
selectQuery.Criteria = null;
//---------------Execute Test ----------------------
SelectQueryDB selectQueryDb = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Test Result -----------------------
Assert.AreEqual("a1", selectQueryDb.Aliases[source1.ToString()]);
}
public void TestSetOrderCriteria()
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery {Source = new Source(TestUtil.GetRandomString())};
//---------------Execute Test ----------------------
var orderCriteria = new OrderCriteria().Add("testfield");
selectQuery.OrderCriteria = orderCriteria;
//---------------Test Result -----------------------
Assert.AreSame(orderCriteria, selectQuery.OrderCriteria);
Assert.AreEqual(0, selectQuery.Source.Joins.Count);
//---------------Tear Down -------------------------
}
public void Test_CreateSQL_ShouldUseAliasesInJoins()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
var mysource = new Source("mysource");
selectQuery.Source = mysource;
QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource);
Source joinedTableSource = new Source("mysource");
joinedTableSource.JoinToSource(new Source("myjoinedtosource"));
QueryField fieldOnJoinedTableSource = new QueryField("testfield", "testfield", joinedTableSource);
joinedTableSource.Joins[0].JoinFields.Add(new Source.Join.JoinField(fieldOnMySource, fieldOnJoinedTableSource));
selectQuery.Fields.Add(fieldOnMySource.FieldName, fieldOnMySource);
selectQuery.Criteria = new Criteria(fieldOnJoinedTableSource, Criteria.ComparisonOp.Equals, "myvalue");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(
"SELECT a1.[testfield] FROM ([mysource] a1 " +
"JOIN [myjoinedtosource] a2 on a1.[testfield] = a2.[testfield]) " +
"WHERE a2.[testfield] = ?Param0", statement.Statement.ToString());
}
private static SelectQuery CreateManualSelectQueryOrderedByDateOfBirth(DateTime now, BusinessObject cp1)
{
var source = new Source(cp1.ClassDef.ClassNameExcludingTypeParameter, cp1.ClassDef.TableName);
SelectQuery query = new SelectQuery(new Criteria("DateOfBirth", Criteria.ComparisonOp.GreaterThan, now));
query.Fields.Add("DateOfBirth", new QueryField("DateOfBirth", "DateOfBirth", source));
query.Fields.Add("ContactPersonID", new QueryField("ContactPersonID", "ContactPersonID", source));
query.Source = source;
query.OrderCriteria = new OrderCriteria().Add("DateOfBirth");
return query;
}
