public void CreateAlterAndDropTable_CreateMultipleTable_ErrorTest() { 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}]},{\"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 { ValidateByExecutingNonQuery(createQueryResponseObject.Query); ValidateByExecutingNonQuery(dropQueryResponseObject.Query); Validator.ValidateQuery(createQueryResponseObject, true); Validator.ValidateQuery(dropQueryResponseObject, true); } catch (Exception) { try { ValidateByExecutingNonQuery(dropQueryResponseObject.Query); } catch (Exception) { throw; } Assert.IsTrue(true); } }
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> /// Method to validate query response object with QueryResponseObject as argument. /// </summary> /// <param name="queryObject">Query response object to be validated.</param> /// <param name="executeNonQuery">Pass true if query does not return data.</param> /// <returns>Returns true if query is valid.</returns> public virtual bool ValidateQuery(QueryResponseObject queryObject, bool executeNonQuery = false) { if (!executeNonQuery) { /* Set column alias map. */ ColumnAliasMap = queryObject.ColumnAliasMap; /* Set expected column list. */ ExpectedColumnList = queryObject.ExpectedColumnList; /* Execute query, validate response and return result. */ return(ValidateResponse(ExecuteQuery(queryObject.Query))); } else { try { /* Execute non query command and retrurn true if command executed successfully. */ ExecuteNonQuery(queryObject.Query); return(true); } catch (Exception) { throw; } } }
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(); } }
protected override void ProcessQueryResponse(IReadOnlyDictionary <string, AnalysedRedditComment> redditCommentBatch, string jsonResponse) { QueryResponseObject resp = JsonConvert.DeserializeObject <QueryResponseObject>(jsonResponse); foreach (Document document in resp.documents) { AnalysedRedditComment comment; if (redditCommentBatch.TryGetValue(document.id, out comment)) { comment.KeyPhrases = document.keyPhrases.ToArray(); } } }
protected override void ProcessQueryResponse(IReadOnlyDictionary <string, AnalysedRedditComment> redditCommentBatch, string jsonResponse) { QueryResponseObject resp = JsonConvert.DeserializeObject <QueryResponseObject>(jsonResponse); foreach (Document document in resp.documents .Where(d => d.detectedLanguages != null && d.detectedLanguages.Any())) { AnalysedRedditComment comment; if (redditCommentBatch.TryGetValue(document.id, out comment)) { comment.Language = document.detectedLanguages[0].iso6391Name; } } }
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 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 CreateAlterAndDropTable_AlterSingleTable_WithConstraints() { MsSqlQueryBuilder alterQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Alter\",\"QueryName\":\"AlterTableQuery\",\"QueryData\":[{\"TableName\":\"TestTable\",\"ColumnList\":[{\"ColumnOperation\":\"Alter\",\"ColumnName\":\"Password\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"100\"},{\"ColumnOperation\":\"Add\",\"ColumnName\":\"Phone\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"10\",\"DefaultValue\":\"test123\"},{\"ColumnOperation\":\"Drop\",\"ColumnName\":\"Email\",\"IsUnique\":true}]}],\"QueryClause\":[]}"); QueryResponseObject alterQueryResponseObject = alterQueryBuilder.GenerateQuery(); try { CreateTable(); Assert.IsTrue(ValidateByExecutingNonQuery(alterQueryResponseObject.Query) && Validator.ValidateQuery(alterQueryResponseObject, true)); } catch (Exception) { throw; } finally { DropTable(); } }
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 InsertAndUpdateRecord_UpdateMultipleRecord_SingleTable() { MsSqlQueryBuilder updateQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Update\",\"QueryName\":\"UpdateRecordQuery\",\"QueryData\":[{\"TableName\":\"TestTable\",\"ColumnList\":[{\"ColumnName\":\"Password\",\"Values\":[\"UpdatedValue\"]}]}],\"QueryClause\":[]}"); QueryResponseObject updateQueryResponseObject = updateQueryBuilder.GenerateQuery(); try { CreateTable(); InsertDummyRecords(); Assert.IsTrue(ValidateByExecutingNonQuery(updateQueryResponseObject.Query) && Validator.ValidateQuery(updateQueryResponseObject, true)); } catch (Exception) { throw; } finally { DropTable(); } }
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 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 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(); } }