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(); }