public void SetupTest()
{
_sqlFormatter = new SqlFormatter("[", "]", "TOP", "");
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
ClassDef.ClassDefs.Clear();
// new Address();
}
public void TestDelimitTable()
{
//-------------Setup Test Pack ------------------
const string leftDelimiter = "LEFT_DELIMIT";
const string rightDelimiter = "RIGHT_DELIMIT";
SqlFormatter sqlFormatter = new SqlFormatter(leftDelimiter, rightDelimiter,"","");
const string tableName = "MY_TABLE";
//-------------Execute test ---------------------
string delimitedField = sqlFormatter.DelimitTable(tableName);
//-------------Test Result ----------------------
Assert.AreEqual(String.Format("{0}{1}{2}", leftDelimiter, tableName, rightDelimiter), delimitedField);
}
public void TestCreateSqlFormatter()
{
//-------------Setup Test Pack ------------------
const string leftFieldDelimiter = "LEFT_DELIMIT";
const string rightFieldDelimiter = "RIGHT_DELIMIT";
//-------------Execute test ---------------------
SqlFormatter sqlFormatter = new SqlFormatter(leftFieldDelimiter, rightFieldDelimiter, "","");
//-------------Test Result ----------------------
Assert.AreEqual(leftFieldDelimiter, sqlFormatter.LeftFieldDelimiter);
Assert.AreEqual(rightFieldDelimiter, sqlFormatter.RightFieldDelimiter);
}
public void GetFormattedStringWith_WhenConstructedWithNoField_ReturnsFormattedSQLString()
{
//---------------Set up test pack-------------------
var sut = Create();
var formatter = new SqlFormatter("[", "]", "", "");
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var result = sut.GetFormattedStringWith(formatter, EmptyAliases());
//---------------Test Result -----------------------
Assert.AreEqual("count(*)", result);
}
public void Test_DelimitTable_WithSchemaPrefix_ShouldReturnWellFormattedDelimiters()
{
//-------------Setup Test Pack ------------------
const string leftDelimiter = "[";
const string rightDelimiter = "]";
const string expected = "[MY_SCHEMA].[MY_TABLE]";
SqlFormatter sqlFormatter = new SqlFormatter(leftDelimiter, rightDelimiter, "", "");
const string tableName = "MY_SCHEMA.MY_TABLE";
//-------------Execute test ---------------------
string delimitedField = sqlFormatter.DelimitTable(tableName);
//-------------Test Result ----------------------
Assert.AreEqual(expected, delimitedField);
}
public void GetFormattedStringWith_ParameterIsString_ShouldQuoteParameter(string left, string right)
{
//---------------Set up test pack-------------------
var sut = Create(CreateQueryFieldFor("SomeColumn"));
var formatter = new SqlFormatter(left, right, "", "");
var expected = string.Join("", new[] { "count(", left, "SomeColumn", right, ")" });
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var result = sut.GetFormattedStringWith(formatter, EmptyAliases());
//---------------Test Result -----------------------
Assert.AreEqual(expected, result);
}
/// <summary>
/// Constructor that initialises a new set of null connections
/// </summary>
protected DatabaseConnection()
{
_connections = new List<IDbConnection>(5);
_sqlFormatter = new SqlFormatter("[", "]", "TOP", "");
}
public void Test_PrepareValue_WithTimeSpan_WhenHasDays_ShouldReturnDateTime()
{
//---------------Set up test pack-------------------
SqlFormatter sqlFormatter = new SqlFormatter("", "", "", "");
var value = new TimeSpan(1,23,59,59,999);
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
object preparedValue = sqlFormatter.PrepareValue(value);
//---------------Test Result -----------------------
var expected = new DateTime(1900,1,2,23,59,59,999);
Assert.AreEqual(expected, preparedValue, "PrepareValue is not preparing timespans correctly.");
}
/// <summary>
/// Constructor to initialise the connection object with an
/// assembly name, class name and connection string
/// </summary>
/// <param name="assemblyName">The assembly name</param>
/// <param name="className">The class name</param>
/// <param name="connectString">The connection string, which can be
/// generated using ConnectionStringSqlServerFactory.CreateConnectionString()
/// </param>
public DatabaseConnectionSQLite(string assemblyName, string className, string connectString)
: base(assemblyName, className, connectString)
{
_sqlFormatter = new SqlFormatter("\"", "\"", "", "limit");
}
public void Test_CreateSQL_LoadWithLimit_AtEnd_PaginatedFind_HasCorrectFieldNames_WithDelimiters()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
IClassDef classDef = ContactPersonTestBO.LoadDefaultClassDef();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
selectQuery.FirstRecordToLoad = 3;
selectQuery.Limit = 5;
selectQuery.OrderCriteria = OrderCriteria.FromString("Surname");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Assert Precondition----------------
Assert.AreEqual(3, selectQuery.FirstRecordToLoad);
Assert.AreEqual(5, selectQuery.Limit);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
string actualStatement = statement.Statement.ToString();
//---------------Test Result -----------------------
const string expectedFirstSelect = "(SELECT a1.[ContactPersonID], a1.[Surname_field], a1.[FirstName_field], a1.[DateOfBirth] FROM [contact_person] a1 ORDER BY a1.[Surname_field] ASC LIMIT 8) As [FirstSelect]";
StringAssert.Contains(expectedFirstSelect, actualStatement);
string expectedSecondSelect = string.Format("(SELECT [FirstSelect].[ContactPersonID], [FirstSelect].[Surname_field], [FirstSelect].[FirstName_field], [FirstSelect].[DateOfBirth] FROM {0} ORDER BY [FirstSelect].[Surname_field] DESC LIMIT 5) As [SecondSelect]", expectedFirstSelect);
StringAssert.Contains(expectedSecondSelect, actualStatement);
string expectedMainSelect = string.Format("SELECT [SecondSelect].[ContactPersonID], [SecondSelect].[Surname_field], [SecondSelect].[FirstName_field], [SecondSelect].[DateOfBirth] FROM {0} ORDER BY [SecondSelect].[Surname_field] ASC", expectedSecondSelect);
Assert.AreEqual(expectedMainSelect, actualStatement);
}
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 DatabaseConnectionStub(SqlFormatter sqlFormatter)
: base("MySql.Data", "MySql.Data.MySqlClient.MySqlConnection")
{
_sqlFormatter = sqlFormatter;
}
public void Test_CreateSQL_LoadWithLimit_PaginatedFind_HasCorrectFieldNames()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
IClassDef classDef = ContactPersonTestBO.LoadDefaultClassDef();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
selectQuery.FirstRecordToLoad = 3;
selectQuery.Limit = 5;
selectQuery.OrderCriteria = OrderCriteria.FromString("Surname");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("", "", "TOP", "");
//---------------Assert Precondition----------------
Assert.AreEqual(3, selectQuery.FirstRecordToLoad);
Assert.AreEqual(5, selectQuery.Limit);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
string actualStatement = statement.Statement.ToString();
//---------------Test Result -----------------------
const string expectedFirstSelect = "(SELECT TOP 8 a1.ContactPersonID, a1.Surname_field, a1.FirstName_field, a1.DateOfBirth FROM contact_person a1 ORDER BY a1.Surname_field ASC) As FirstSelect";
StringAssert.Contains(expectedFirstSelect, actualStatement);
string expectedSecondSelect = string.Format("(SELECT TOP 5 FirstSelect.ContactPersonID, FirstSelect.Surname_field, FirstSelect.FirstName_field, FirstSelect.DateOfBirth FROM {0} ORDER BY FirstSelect.Surname_field DESC ) As SecondSelect", expectedFirstSelect);
StringAssert.Contains(expectedSecondSelect, actualStatement);
string expectedMainSelect = string.Format("SELECT SecondSelect.ContactPersonID, SecondSelect.Surname_field, SecondSelect.FirstName_field, SecondSelect.DateOfBirth FROM {0} ORDER BY SecondSelect.Surname_field ASC", expectedSecondSelect);
Assert.AreEqual(expectedMainSelect, actualStatement);
}
public void TestCreateSqlStatement_WithLimit_AtBeginning()
{
//---------------Set up test pack-------------------
// DatabaseConnection.CurrentConnection = new DatabaseConnectionStub_LimitClauseAtBeginning();
SelectQuery selectQuery = new SelectQuery {Limit = 10};
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);
SqlFormatter sqlFormatter = new SqlFormatter("", "", "TOP ROWS", "");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.StartsWith("SELECT TOP ROWS 10 ", 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_GetLimitClauseForEnd()
{
//---------------Set up test pack-------------------
const string limitClauseAtEnd = "LIMIT";
SqlFormatter sqlFormatter = new SqlFormatter("", "", "", limitClauseAtEnd);
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
string limitClauseForEnd = sqlFormatter.GetLimitClauseCriteriaForEnd(10);
//---------------Test Result -----------------------
const string expectedLimitClauseForEnd = limitClauseAtEnd + " 10";
Assert.AreEqual(expectedLimitClauseForEnd, limitClauseForEnd);
}
public void GetFormattedStringWith_WhenConstructedWithQueryFieldWithTableName_ShouldReturnCorrectString(string left, string right)
{
//---------------Set up test pack-------------------
var queryField = new QueryField("property", "field", new Source("table"));
var sut = Create(queryField);
var formatter = new SqlFormatter(left, right, "", "");
var expected = string.Join("", new[] { "count(", left, "table", right, ".", left, "field", right, ")" });
var aliases = new Dictionary<string, string>();
aliases["table"] = "table";
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var result = sut.GetFormattedStringWith(formatter, aliases);
//---------------Test Result -----------------------
Assert.AreEqual(expected, result);
}
/// <summary>
/// Constructor to initialise the connection object with an
/// assembly name, class name and connection string
/// </summary>
/// <param name="assemblyName">The assembly name</param>
/// <param name="className">The class name</param>
/// <param name="connectString">The connection string, which can be
/// generated using ConnectionStringOracleFactory.CreateConnectionString()
/// </param>
public DatabaseConnectionOracle(string assemblyName, string className, string connectString)
: base(assemblyName, className, connectString)
{
_sqlFormatter = new SqlFormatter("", "", "", "ROWNUM <=");
}
public void GetFormattedStringWith_WhenConstructedWithQueryFieldWithNoTableName_ShouldReturnCorrectString(string left, string right)
{
//---------------Set up test pack-------------------
var queryField = new QueryField("property", "field", null);
var sut = Create(queryField);
var formatter = new SqlFormatter(left, right, "", "");
var expected = string.Join("", new[] { "count(", left, "field", right, ")" });
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var result = sut.GetFormattedStringWith(formatter, EmptyAliases());
//---------------Test Result -----------------------
Assert.AreEqual(expected, result);
}
public void TestCreateSQL_WithJoin_WithDelimiter()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = CreateAndAddJoin(source, joinSource);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
SqlFormatter myFormatter = new SqlFormatter("[", "]", "TOP", "");
string sql = sourceDB.CreateSQL(myFormatter, CreateAliases(source, joinSource));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
string expectedSql = string.Format("([{0}] a1 JOIN [{1}] a2 ON a1.[{2}] = a2.[{3}])", source.EntityName, joinSource.EntityName,
joinField.FromField.FieldName, joinField.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public DatabaseConnectionStub_LimitClauseAtBeginning()
{
_sqlFormatter = new SqlFormatter("`", "`", "TOP ROWS", "");
}
public void Test_CreateSQL_ShouldUseAliasesInJoins()
{
//---------------Set up test pack-------------------
var mysource = new Source("mysource");
QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource);
Source joinedTableSource = new Source("myjoinedtosource");
QueryField fieldOnJoinedTableSource = new QueryField("testfield", "testfield", joinedTableSource);
mysource.Joins.Add(new Source.Join(mysource, joinedTableSource));
mysource.Joins[0].JoinFields.Add(new Source.Join.JoinField(fieldOnMySource, fieldOnJoinedTableSource));
SourceDB sourceDB = new SourceDB(mysource);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
IDictionary<string, string> aliases = new Dictionary<string, string>() { { mysource.ToString(), "a1" }, { joinedTableSource.ToString(), "a2"} };
//---------------Execute Test ----------------------
string sql = sourceDB.CreateSQL(sqlFormatter, aliases);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(
"([mysource] a1 JOIN [myjoinedtosource] a2 on a1.[testfield] = a2.[testfield])", sql);
}
public void TestCreateSqlStatement_WithOrder_WithLimit_AtEnd()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub(new SqlFormatter("", "", "", "LIMIT"));
SelectQuery selectQuery = new SelectQuery {Limit = 10};
const string fieldName = "Field1";
selectQuery.Fields.Add(fieldName, new QueryField(fieldName, fieldName, null));
selectQuery.Source = new Source("Table1");
selectQuery.OrderCriteria = OrderCriteria.FromString(fieldName);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("", "", "", "LIMIT");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.EndsWith(" LIMIT 10", statementString);
//---------------Tear Down -------------------------
}
/// <summary>
/// Constructor to initialise the connection object with an
/// assembly name, class name and connection string
/// </summary>
/// <param name="assemblyName">The assembly name</param>
/// <param name="className">The class name</param>
/// <param name="connectString">The connection string, which can be
/// generated using ConnectionStringMySqlFactory.CreateConnectionString()
/// </param>
public DatabaseConnectionFirebird(string assemblyName, string className, string connectString)
: base(assemblyName, className, connectString)
{
_sqlFormatter = new SqlFormatter("", "", "FIRST", "");
}
public void Test_CreateSQL_LoadWithLimit_AtEnd_PaginatedFind()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
IClassDef classDef = MyBO.LoadClassDefs_OneProp();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
selectQuery.FirstRecordToLoad = 2;
selectQuery.Limit = 4;
selectQuery.OrderCriteria = OrderCriteria.FromString("MyBOID");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("", "", "", "LIMIT");
//---------------Assert Precondition----------------
Assert.AreEqual(2, selectQuery.FirstRecordToLoad);
Assert.AreEqual(4, selectQuery.Limit);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
string actualStatement = statement.Statement.ToString();
//---------------Test Result -----------------------
const string expectedFirstSelect = "(SELECT a1.MyBoID FROM MyBO a1 ORDER BY a1.MyBOID ASC LIMIT 6) As FirstSelect";
StringAssert.Contains(expectedFirstSelect, actualStatement);
string expectedSecondSelect = string.Format("(SELECT FirstSelect.MyBoID FROM {0} ORDER BY FirstSelect.MyBOID DESC LIMIT 4) As SecondSelect", expectedFirstSelect);
StringAssert.Contains(expectedSecondSelect, actualStatement);
string expectedMainSelect = string.Format("SELECT SecondSelect.MyBoID FROM {0} ORDER BY SecondSelect.MyBOID ASC", expectedSecondSelect);
Assert.AreEqual(expectedMainSelect, actualStatement);
}
private void SetupSqlFormatter()
{
_sqlFormatter = new SqlFormatter("\"", "\"", "FIRST", "");
}
public void Test_CreateSQL_SelectCountQuery_WithCriteria()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
IClassDef classDef = MyBO.LoadDefaultClassDef();
Criteria criteria = new Criteria("TestProp", Criteria.ComparisonOp.Equals, "test");
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//-------------Assert Preconditions -------------
//---------------Execute Test ----------------------
ISelectQuery selectQuery = QueryBuilder.CreateSelectCountQuery(classDef, criteria);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.Contains("SELECT [Count(*)] FROM [MyBO] a1 WHERE ", statement.Statement.ToString());
StringAssert.EndsWith("WHERE a1.[TestProp] = ?Param0", statementString);
Assert.AreEqual("?Param0", statement.Parameters[0].ParameterName);
Assert.AreEqual("test", statement.Parameters[0].Value);
}
/// <summary>
/// Constructor to initialise the connection object with an
/// assembly name, class name and connection string
/// </summary>
/// <param name="assemblyName">The assembly name</param>
/// <param name="className">The class name</param>
/// <param name="connectString">The connection string, which can be
/// generated using ConnectionStringMySqlFactory.CreateConnectionString()
/// </param>
public DatabaseConnectionMySql(string assemblyName, string className, string connectString)
: base(assemblyName, className, connectString)
{
_sqlFormatter = new SqlFormatter("`", "`", "", "LIMIT");
}
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());
}
public void Test_PrepareValue_WithGuid()
{
//---------------Set up test pack-------------------
SqlFormatter sqlFormatter = new SqlFormatter("", "", "", "");
Guid g = Guid.NewGuid();
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
object preparedValue = sqlFormatter.PrepareValue(g);
//---------------Test Result -----------------------
string strg = g.ToString("B").ToUpper();
Assert.AreEqual(strg, preparedValue, "PrepareValue is not preparing guids correctly.");
}
