public void MsSqlQueryValidatorUnitTest_QueryType()
{
try
{
string query = "SELECT TOP (1000) * FROM [DimProduct];";
InitTable();
Assert.IsTrue(Validator.ValidateQuery(query));
}
catch (Exception)
{
throw;
}
finally
{
Destroy();
}
}
public void CreateAlterAndDropTable_CreateSingleTable()
{
MsSqlQueryBuilder createQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Create\",\"QueryName\":\"CreateTableQuery\",\"QueryData\":[{\"TableName\":\"TestTable\",\"ColumnList\":[{\"ColumnName\":\"UserName\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"50\",\"IsPrimary\":true},{\"ColumnName\":\"Password\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"50\"},{\"ColumnName\":\"Email\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"200\",\"IsUnique\":true}]}],\"QueryClause\":[]}");
MsSqlQueryBuilder dropQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Drop\",\"QueryName\":\"DropTableQuery\",\"QueryData\":[{\"TableName\":\"TestTable\"}]}");
QueryResponseObject createQueryResponseObject = createQueryBuilder.GenerateQuery();
QueryResponseObject dropQueryResponseObject = dropQueryBuilder.GenerateQuery();
try
{
Assert.IsTrue(ValidateByExecutingNonQuery(createQueryResponseObject.Query) && ValidateByExecutingNonQuery(dropQueryResponseObject.Query) && Validator.ValidateQuery(createQueryResponseObject, true) && Validator.ValidateQuery(dropQueryResponseObject, true));
}
catch (Exception)
{
ValidateByExecutingNonQuery(dropQueryResponseObject.Query);
throw;
}
}
/// <summary>
/// Method to generate query string from JSON string.
/// </summary>
/// <param name="jsonString">JSON data to generate query.</param>
/// <returns>Generated query string.</returns>
public string GenerateQuery(string jsonString)
{
try
{
QueryBuilder.LoadJson(jsonString);
var queryObject = QueryBuilder.GenerateQuery();
LoadTablesToValidator();
Validator.ValidateQuery(queryObject, IsRequiredToExecuteAsNonQuery());
return(queryObject.Query);
}
catch (Exception)
{
throw;
}
}
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();
}
}
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 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 SelectWithWhereClause_SingleCondition_LikeOperator()
{
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 '%Crankarm'\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
bool isValid = true;
while (reader.Read())
{
if (!reader.GetString(3).EndsWith("Crankarm", StringComparison.OrdinalIgnoreCase))
{
isValid = false;
break;
}
}
return(isValid);
}));
Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
validator.Destroy();
}
}
public void InsertAndUpdateRecord_InsertSingleRecord_SingleTable()
{
MsSqlQueryBuilder insertQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Insert\",\"QueryName\":\"InsertRecordQuery\",\"QueryData\":[{\"TableName\":\"TestTable\",\"ColumnList\":[{\"ColumnName\":\"ID\",\"Values\":[1]},{\"ColumnName\":\"UserName\",\"Values\":[\"t1\"]},{\"ColumnName\":\"Password\",\"Values\":[\"pass1\"]},{\"ColumnName\":\"Email\",\"Values\":[\"email1\"]}]}]}");
QueryResponseObject insertQueryResponseObject = insertQueryBuilder.GenerateQuery();
try
{
CreateTable();
Assert.IsTrue(ValidateByExecutingNonQuery(insertQueryResponseObject.Query) && Validator.ValidateQuery(insertQueryResponseObject, true));
}
catch (Exception)
{
throw;
}
finally
{
DropTable();
}
}
public void SelectWithGroupByClause_WithSumOperation_WithWhereClause_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\":\"Color\"},{\"ColumnName\":\"SafetyStockLevel\",\"Alias\":\"Safety Stock Level\",\"ColumnOperation\":\"SUM\"}],\"TableClause\":[{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"Color IN ('Black', 'Grey')\"},{\"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("Grey", 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 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 SelectWithDistinctClause()
{
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\"}],\"TableClause\":[{\"ClauseType\":\"DISTINCT\"}]}],\"QueryClause\":[]}");
validator.AddTable(table);
QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
List <string> colorList = new List <string>();
bool isValid = true;
while (reader.Read())
{
var color = reader.GetString(0);
if (colorList.Contains(color))
{
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 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();
}
}