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 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();
}
}
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 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();
}
}
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 void SimpleSelectQueryWithSingleTable()
{
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\":[]}],\"QueryClause\":[]}");
validator.AddTable(table);
Assert.IsTrue(validator.ValidateQuery(queryBuilder.GenerateQuery()));
}
catch (System.Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
