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();
            }
        }
Beispiel #2
0
        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);
            }
        }
Beispiel #5
0
        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());
        }
Beispiel #6
0
        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();
            }
        }
Beispiel #8
0
        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);
        }
Beispiel #10
0
        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();
            }
        }
Beispiel #13
0
        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();
            }
        }
Beispiel #14
0
 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();
 }