public void SimpleSelectQueryWithSingleTable_Sum()
{
MsSqlQueryValidator validator = new MsSqlQueryValidator();
try
{
TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");
MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"SafetyStockLevel\",\"ColumnOperation\":\"SUM\",\"Alias\":\"Safety Stock Level Total\"}],\"TableClause\":[]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
reader.Read();
return(reader.GetInt32(0) == 300092);
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
public void SelectWithWhereClause_MultiCondition_1()
{
MsSqlQueryValidator validator = new MsSqlQueryValidator();;
try
{
TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");
MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"},{\"ColumnName\":\"Color\"}],\"TableClause\":[{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"EnglishProductName LIKE 'Chainring%' AND Color = 'Black'\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
reader.Read();
return(reader.GetString(3).StartsWith("Chainring", StringComparison.OrdinalIgnoreCase) && (reader.GetInt32(0) == 11));
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
public void SelectWithTopClause()
{
MsSqlQueryValidator validator = new MsSqlQueryValidator();;
try
{
TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");
MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"},{\"ColumnName\":\"Color\"}],\"TableClause\":[{\"ClauseType\":\"TOP\",\"ClauseValue\":\"20\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
int rowCount = 0;
while (reader.Read())
{
rowCount++;
}
return(rowCount == 20);
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
/// <summary>
/// Common method to create sql connection, execute query and call predicate function to check if it satisfies the query conditions.
/// </summary>
/// <param name="queryString">Query to be executed.</param>
/// <param name="predicate"></param>
/// <returns>Return true if predicate function satisfies the query conditions.</returns>
protected bool ValidateUsingDbResponse(string queryString, PredicateFunction predicate)
{
try
{
bool isValid = false;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
/* Invoke predicate function to validate result. */
isValid = predicate(reader);
reader.Close();
}
return(isValid);
}
catch (Exception)
{
return(false);
}
}
public uint[] GetUIDsByPredicate(PredicateFunction func, uint[] fromUIDs)
{
QueryCount++;
mySqlComm = mySql.CreateCommand();
mySqlComm.CommandText = "SELECT UID FROM DBLOCKS WHERE UID IN " + MakeSQLArrayString(fromUIDs);
UInt32[] uids = GetUIDsFromCommand(mySqlComm);
List <UInt32> matchedUIDs = new List <UInt32>();
foreach (UInt32 uid in uids)
{
DemodulatedBlock db = GetDBlock(uid);
if (func(db))
{
matchedUIDs.Add(uid);
}
}
return(matchedUIDs.ToArray());
}
public void SelectWithJoin_InnerJoin_2_Tables()
{
MsSqlQueryBuilder joinQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SelectTableQueryWithJoin\",\"QueryData\":[{\"TableName\":\"TestTable1\",\"ColumnList\":[{\"ColumnName\":\"ID\",\"BelongsToTable\":\"TestTable1\"},{\"ColumnName\":\"UserName\",\"BelongsToTable\":\"TestTable1\"},{\"ColumnName\":\"Email\",\"BelongsToTable\":\"TestTable1\",\"Alias\":\"Email\"},{\"ColumnName\":\"Address\",\"BelongsToTable\":\"TestTable2\"},{\"ColumnName\":\"Contact_Number\",\"BelongsToTable\":\"TestTable2\",\"Alias\":\"Contact\"}],\"TableClause\":[{\"ClauseType\":\"JOIN\",\"ClauseValue\":\"TestTable1.UserName = TestTable2.UserName\",\"JoinType\":\"Inner\",\"JoinTableName\":\"TestTable2\"}]}],\"QueryClause\":[]}");
QueryResponseObject joinQueryResponseObject = joinQueryBuilder.GenerateQuery();
ColumnAliasMap = joinQueryResponseObject.ColumnAliasMap;
try
{
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
bool isValid = true;
List <string> resultantColumns = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
resultantColumns.Add(reader.GetName(i));
}
foreach (var expectedColumnName in joinQueryResponseObject.ExpectedColumnList)
{
if (!resultantColumns.Contains(GetColumnAliasName(expectedColumnName)))
{
isValid = false;
break;
}
}
return(isValid);
}));
CreateTable();
Assert.IsTrue(ValidateUsingDbResponse(joinQueryResponseObject.Query, predicateFunction) && Validator.ValidateQuery(joinQueryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
DropTable();
}
}
public void SelectWithGroupByClause_WithSumOperation()
{
MsSqlQueryValidator validator = new MsSqlQueryValidator();;
try
{
TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");
MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"Color\"},{\"ColumnName\":\"SafetyStockLevel\",\"Alias\":\"Safety Stock Level\",\"ColumnOperation\":\"SUM\"}],\"TableClause\":[{\"ClauseType\":\"GROUPBY\",\"ClauseValue\":\"Color\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
bool isValid = true;
List <string> colorList = new List <string>();
while (reader.Read())
{
var color = reader.GetString(0);
if (colorList.Contains(color) && reader.GetInt32(1) > -1)
{
isValid = false;
break;
}
colorList.Add(color);
}
return(isValid);
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
public void SingleSelectWithMultiClausesAndConditions_Top_WhereClauseWithSingleCondition()
{
MsSqlQueryValidator validator = new MsSqlQueryValidator();;
try
{
TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");
MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"},{\"ColumnName\":\"Color\"}],\"TableClause\":[{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"Color = 'black'\"},{\"ClauseType\":\"TOP\",\"ClauseValue\":\"20\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
bool isValid = true;
int rowCount = 0;
while (reader.Read())
{
if (!reader.GetString(4).Equals("Black", StringComparison.OrdinalIgnoreCase))
{
isValid = false;
break;
}
rowCount++;
}
return(isValid && rowCount == 20);
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (System.Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
ISpecificationExpression <Ticket> GetSpecFromFunction(PredicateFunction function)
{
ISpecificationExpression <Ticket> spec = null;
if (function.FunctionName == PredicateName.Function.IsEmpty)
{
spec = new HasNoStoryPoints();
}
if (function.FunctionName == PredicateName.Function.IsAnyOf)
{
spec = new StoryPointsIsOneOf(valueResolver.ResolveAll <int>(function.Parameters));
}
if (spec != null && function.Inverted)
{
spec = spec.Not();
}
return(spec);
}
public void SelectWithWhereClause_SingleCondition_BetweenOperator()
{
MsSqlQueryValidator validator = new MsSqlQueryValidator();;
try
{
TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");
MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"},{\"ColumnName\":\"Color\"}],\"TableClause\":[{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"ProductKey BETWEEN 12 AND 25\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
bool isValid = true;
while (reader.Read())
{
var key = reader.GetInt32(0);
if (key < 12 && key > 25)
{
isValid = false;
break;
}
}
return(isValid);
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
public void SelectWithOrderByClause_WithGroupByClause_Asc()
{
MsSqlQueryValidator validator = new MsSqlQueryValidator();;
try
{
TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");
MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"Color\"},{\"ColumnName\":\"SafetyStockLevel\",\"Alias\":\"Safety Stock Sum\",\"ColumnOperation\":\"SUM\"}],\"TableClause\":[{\"ClauseType\":\"GROUPBY\",\"ClauseValue\":\"Color\"},{\"ClauseType\":\"ORDERBY\",\"ClauseValue\":\"SUM(SafetyStockLevel) ASC\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
List <int> listAsInResponse = new List <int>(), sortedList = new List <int>();
while (reader.Read())
{
var value = reader.GetInt32(1);
listAsInResponse.Add(value);
sortedList.Add(value);
}
sortedList.OrderBy(x => x);
return(listAsInResponse.SequenceEqual(sortedList));
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
public void SelectWithOrderByClause_WithWhereClause_Desc()
{
MsSqlQueryValidator validator = new MsSqlQueryValidator();;
try
{
TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");
MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"}],\"TableClause\":[{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"Color IN ('Black', 'Silver')\"},{\"ClauseType\":\"ORDERBY\",\"ClauseValue\":\"ProductAlternateKey DESC\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
List <string> listAsInResponse = new List <string>(), sortedList = new List <string>();
while (reader.Read())
{
var value = reader.GetString(1);
listAsInResponse.Add(value);
sortedList.Add(value);
}
sortedList.OrderByDescending(x => x);
return(listAsInResponse.SequenceEqual(sortedList));
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
public void SelectWithHavingClause_WithGroupByAndWhereClause_WithCountOperation()
{
MsSqlQueryValidator validator = new MsSqlQueryValidator();;
try
{
TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");
MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"Color\"},{\"ColumnName\":\"SafetyStockLevel\",\"Alias\":\"Safety Stock Count\",\"ColumnOperation\":\"COUNT\"}],\"TableClause\":[{\"ClauseType\":\"HAVING\",\"ClauseValue\":\"SUM(SafetyStockLevel) > 500\"},{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"Color IN ('Black','Red')\"},{\"ClauseType\":\"GROUPBY\",\"ClauseValue\":\"Color\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
bool isValid = true;
while (reader.Read())
{
var color = reader.GetString(0);
if (!(color.Equals("Black", StringComparison.OrdinalIgnoreCase) || color.Equals("Red", StringComparison.OrdinalIgnoreCase)) && reader.GetInt32(1) > -1)
{
isValid = false;
break;
}
}
return(isValid);
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
public uint[] GetUIDsByPredicate(PredicateFunction func, uint[] fromUIDs)
{
QueryCount++;
mySqlComm = mySql.CreateCommand();
mySqlComm.CommandText = "SELECT UID FROM DBLOCKS WHERE UID IN " + MakeSQLArrayString(fromUIDs);
UInt32[] uids = GetUIDsFromCommand(mySqlComm);
List<UInt32> matchedUIDs = new List<UInt32>();
foreach (UInt32 uid in uids)
{
DemodulatedBlock db = GetDBlock(uid);
if (func(db)) matchedUIDs.Add(uid);
}
return matchedUIDs.ToArray();
}
