public void TestSelectColumnsWithoutWhereWithGroupBySum()
{
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select city,sum(season) from " + strtogetipldotcsv + " group by city");
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
object rowList = null;
((GroupDataSet)dataSet).GroupedDataSet.TryGetValue("30", out rowList);
GroupDataSet groupDataSet = new GroupDataSet();
groupDataSet.GroupedDataSet.Add("sum(season)", "4032");
groupDataSet.GroupedDataSet.Add("city", "Kanpur");
rowList.Should().NotBeNull("because null not allowed");// "because sum can never be null");
rowList.Should().BeEquivalentTo(groupDataSet);
}
public void TestSelectColumnsWithoutWhereWithGroupByAvg()
{
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select city,avg(win_by_wickets) from " + strtogetipldotcsv + " group by city");
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
object rowList = null;
((GroupDataSet)dataSet).GroupedDataSet.TryGetValue("7", out rowList);
GroupDataSet groupDataSet = new GroupDataSet();
groupDataSet.GroupedDataSet.Add("city", "Chennai");
groupDataSet.GroupedDataSet.Add("avg(win_by_wickets)", "2.31");
rowList.Should().NotBeNull("because null not allowed");
rowList.Should().BeEquivalentTo(groupDataSet);
}
public void TestWithWhereLessThanOrEqualToClause()
{
int totalrecordsexpected = 517;
int recordscounter = 1;
bool dataexpectedstatus = false;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().executeQuery(@"select season,city,winner,team1,team2,player_of_match from " + strtogetipldotcsv + " where season <= 2015");
int counter = 0;
foreach (Row row in dataSet.Rows)
{
if (recordscounter == 1)
{
if (row.RowValues.SequenceEqual(new string[] { "2008", "Bangalore", "Kolkata Knight Riders", "Kolkata Knight Riders", "Royal Challengers Bangalore", "BB McCullum" }))
{
counter++;
}
}
else if (recordscounter == 258)
{
if (row.RowValues.SequenceEqual(new string[] { "2012", "Bangalore", "Kolkata Knight Riders", "Kolkata Knight Riders", "Royal Challengers Bangalore", "L Balaji" }))
{
counter++;
}
}
else if (recordscounter == 517)
{
if (row.RowValues.SequenceEqual(new string[] { "2015", "Kolkata", "Mumbai Indians", "Mumbai Indians", "Chennai Super Kings", "RG Sharma" }))
{
counter++;
}
}
recordscounter++;
}
if (counter > 2)
{
dataexpectedstatus = true;
}
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
totalrecordsexpected.Should().Be(517);
dataSet.Should().NotBeNull("because records exist in ipl.csv file");
dataexpectedstatus.Should().BeTrue("because no. of matching records should be more than 2");
}
public void TestWithWhereEqualAndNotEqualClause()
{
int totalrecordsexpected = 195;
int recordscounter = 1;
bool dataexpectedstatus = false;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().executeQuery(@"select season,city,winner,team1,team2,player_of_match from " + strtogetipldotcsv + " where season >= 2013 and season <= 2015");
int counter = 0;
foreach (Row row in dataSet.Rows)
{
if (recordscounter == 1)
{
if (row.RowValues.SequenceEqual(new string[] { "2013", "Kolkata", "Kolkata Knight Riders", "Delhi Daredevils", "Kolkata Knight Riders", "SP Narine" }))
{
counter++;
}
}
else if (recordscounter == 97)
{
if (row.RowValues.SequenceEqual(new string[] { "2014", "Ranchi", "Chennai Super Kings", "Chennai Super Kings", "Kolkata Knight Riders", "RA Jadeja" }))
{
counter++;
}
}
else if (recordscounter == 195)
{
if (row.RowValues.SequenceEqual(new string[] { "2015", "Kolkata", "Mumbai Indians", "Mumbai Indians", "Chennai Super Kings", "RG Sharma" }))
{
counter++;
}
}
recordscounter++;
}
if (counter > 2)
{
dataexpectedstatus = true;
}
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
totalrecordsexpected.Should().Be(195);
dataSet.Should().NotBeNull("because records exist in ipl.csv file");
dataexpectedstatus.Should().BeTrue("because no. of matching records should be more than 2");
}
public void TestWithWhereLessThanClause()
{
int totalrecordsexpected = 458;
int recordscounter = 1;
bool dataexpectedstatus = false;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().executeQuery(@"select city,winner,team1,team2,player_of_match from " + strtogetipldotcsv + " where season < 2015");
int counter = 0;
foreach (Row row in dataSet.Rows)
{
if (recordscounter == 1)
{
if (row.RowValues.SequenceEqual(new string[] { "Bangalore", "Kolkata Knight Riders", "Kolkata Knight Riders", "Royal Challengers Bangalore", "BB McCullum" }))
{
counter++;
}
}
else if (recordscounter == 229)
{
if (row.RowValues.SequenceEqual(new string[] { "Jaipur", "Royal Challengers Bangalore", "Rajasthan Royals", "Royal Challengers Bangalore", "S Aravind" }))
{
counter++;
}
}
else if (recordscounter == 458)
{
if (row.RowValues.SequenceEqual(new string[] { "Bangalore", "Kolkata Knight Riders", "Kings XI Punjab", "Kolkata Knight Riders", "MK Pandey" }))
{
counter++;
}
}
recordscounter++;
}
if (counter > 2)
{
dataexpectedstatus = true;
}
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
totalrecordsexpected.Should().Be(458);
dataSet.Should().NotBeNull("because records exist in ipl.csv file");
dataexpectedstatus.Should().BeTrue("because no. of matching records should be more than 2");
}
public void TestWithWhereGreaterThanClause()
{
int totalrecordsexpected = 60;
int recordscounter = 1;
bool dataexpectedstatus = false;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().executeQuery(@"select season,city,winner,team1,team2,player_of_match from " + strtogetipldotcsv + " where season > 2015");
int counter = 0;
foreach (Row row in dataSet.Rows)
{
if (recordscounter == 1)
{
if (row.RowValues.SequenceEqual(new string[] { "2016", "Mumbai", "Rising Pune Supergiants", "Mumbai Indians", "Rising Pune Supergiants", "AM Rahane" }))
{
counter++;
}
}
else if (recordscounter == 30)
{
if (row.RowValues.SequenceEqual(new string[] { "2016", "Bangalore", "Kolkata Knight Riders", "Royal Challengers Bangalore", "Kolkata Knight Riders", "AD Russell" }))
{
counter++;
}
}
else if (recordscounter == 60)
{
if (row.RowValues.SequenceEqual(new string[] { "2016", "Bangalore", "Sunrisers Hyderabad", "Sunrisers Hyderabad", "Royal Challengers Bangalore", "BCJ Cutting" }))
{
counter++;
}
}
recordscounter++;
}
if (counter > 2)
{
dataexpectedstatus = true;
}
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
totalrecordsexpected.Should().Be(60);
dataSet.Should().NotBeNull("because records exist in ipl.csv file");
dataexpectedstatus.Should().BeTrue("because no. of matching records should be more than 2");
}
public void TestSelectAllWithoutWhereClause()
{
int totalrecordsexpected = 577;
int recordscounter = 1;
bool dataexpectedstatus = false;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select * from " + strtogetipldotcsv);
int counter = 0;
foreach (Row row in dataSet.Rows)
{
if (recordscounter == 1)
{
if (row.RowValues.SequenceEqual(new string[] { "1", "2008", "Bangalore", "2008-04-18", "Kolkata Knight Riders", "Royal Challengers Bangalore", "Royal Challengers Bangalore", "field", "normal", "0", "Kolkata Knight Riders", "140", "0", "BB McCullum", "M Chinnaswamy Stadium", "Asad Rauf", "RE Koertzen", "" }))
{
counter++;
}
}
else if (recordscounter == 289)
{
if (row.RowValues.SequenceEqual(new string[] { "289", "2012", "Cuttack", "2012-05-01", "Deccan Chargers", "Pune Warriors", "Deccan Chargers", "bat", "normal", "0", "Deccan Chargers", "13", "0", "KC Sangakkara", "Barabati Stadium", "Aleem Dar", "AK Chaudhary", "" }))
{
counter++;
}
}
else if (recordscounter == 577)
{
if (row.RowValues.SequenceEqual(new string[] { "577", "2016", "Bangalore", "2016-05-29", "Sunrisers Hyderabad", "Royal Challengers Bangalore", "Sunrisers Hyderabad", "bat", "normal", "0", "Sunrisers Hyderabad", "8", "0", "BCJ Cutting", "M Chinnaswamy Stadium", "HDPK Dharmasena", "BNJ Oxenford", "" }))
{
counter++;
}
}
recordscounter++;
}
if (counter > 2)
{
dataexpectedstatus = true;
}
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
totalrecordsexpected.Should().Be(577);
//dataSet.Should().NotBeNull("because records exist in ipl.csv file");
dataSet.Should().NotBeNull("because records exist in ipl.csv file");
dataexpectedstatus.Should().BeTrue("because no. of matching records should be more than 2");
}
public void TestWithWhereThreeConditionsEqualOrNotEqualClause()
{
int totalrecordsexpected = 577;
int recordscounter = 1;
bool dataexpectedstatus = false;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select city,winner,team1,team2,player_of_match from " + strtogetipldotcsv + " where season >= 2008 or toss_decision != bat and city = bangalore");
int counter = 0;
foreach (Row row in dataSet.Rows)
{
if (recordscounter == 1)
{
if (row.RowValues.SequenceEqual(new string[] { "Bangalore", "Kolkata Knight Riders", "Kolkata Knight Riders", "Royal Challengers Bangalore", "BB McCullum" }))
{
counter++;
}
}
else if (recordscounter == 289)
{
if (row.RowValues.SequenceEqual(new string[] { "Cuttack", "Deccan Chargers", "Deccan Chargers", "Pune Warriors", "KC Sangakkara" }))
{
counter++;
}
}
else if (recordscounter == 577)
{
if (row.RowValues.SequenceEqual(new string[] { "Bangalore", "Sunrisers Hyderabad", "Sunrisers Hyderabad", "Royal Challengers Bangalore", "BCJ Cutting" }))
{
counter++;
}
}
recordscounter++;
}
if (counter > 2)
{
dataexpectedstatus = true;
}
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
dataSet.Rows.Count.Should().Be(totalrecordsexpected);
dataSet.Should().NotBeNull("because records exist in ipl.csv file");
dataexpectedstatus.Should().BeTrue("because no. of matching records should be more than 2");
}
public void TestSelectColumnsWithoutWhereClause()
{
int totalrecordsexpected = 577;
int recordscounter = 1;
bool dataexpectedstatus = false;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().executeQuery(@"select city, winner, team1, team2 from " + strtogetipldotcsv);
int counter = 0;
foreach (Row row in dataSet.Rows)
{
if (recordscounter == 1)
{
if (row.RowValues.SequenceEqual(new string[] { "Bangalore", "Kolkata Knight Riders", "Kolkata Knight Riders", "Royal Challengers Bangalore" }))
{
counter++;
}
}
else if (recordscounter == 289)
{
if (row.RowValues.SequenceEqual(new string[] { "Cuttack", "Deccan Chargers", "Deccan Chargers", "Pune Warriors" }))
{
counter++;
}
}
else if (recordscounter == 577)
{
if (row.RowValues.SequenceEqual(new string[] { "Bangalore", "Sunrisers Hyderabad", "Sunrisers Hyderabad", "Royal Challengers Bangalore" }))
{
counter++;
}
}
recordscounter++;
}
if (counter > 2)
{
dataexpectedstatus = true;
}
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
totalrecordsexpected.Should().Be(577);
dataSet.Should().NotBeNull("because records exist in ipl.csv file");
dataexpectedstatus.Should().BeTrue("because no. of matching records should be more than 2");
}
public void TestWithOrderBy()
{
int totalRecordsExpected = 577;
int recordscounter = 1;
bool dataexpectedstatus = false;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select player_of_match,city,team1,team2 from " + strtogetipldotcsv + " order by city");
int counter = 0;
foreach (Row row in dataSet.Rows)
{
if (recordscounter == 1)
{
if (row.RowValues.SequenceEqual(new string[] { "PA Patel", "", "Mumbai Indians", "Royal Challengers Bangalore" }))
{
counter++;
}
}
else if (recordscounter == 282)
{
if (row.RowValues.SequenceEqual(new string[] { "MS Dhoni", "East London", "Chennai Super Kings", "Deccan Chargers" }))
{
counter++;
}
}
else if (recordscounter == 570)
{
if (row.RowValues.SequenceEqual(new string[] { "MS Dhoni", "Visakhapatnam", "Kings XI Punjab", "Rising Pune Supergiants" }))
{
counter++;
}
}
recordscounter++;
}
if (counter > 2)
{
dataexpectedstatus = true;
}
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
dataSet.Rows.Count.Should().Be(totalRecordsExpected);
dataSet.Rows.Should().NotBeNull();
dataexpectedstatus.Should().BeTrue("because no. of matching records should be more than 2");
}
public void TestFileNotFound()
{
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
}
public void TestFileNotFound()
{
try
{
// CsvQueryProcessor reader = new CsvQueryProcessor(Path.Combine(Environment.CurrentDirectory, @"data/ipl2.csv"));
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
}
public QueryParserEngineTest()
{
#region Don't Try to Delete/Change the Logic Keep it as it is in Constructor
var mydirectory = Environment.CurrentDirectory;
int indexofmyvar = mydirectory.IndexOf("bin");
string substr = mydirectory.Substring(0, indexofmyvar);
if (substr.Contains("test"))
{
int testindex = substr.IndexOf("test");
rootdir = substr.Substring(0, testindex);
strtogetipldotcsv = rootdir + "data/ipl.csv";
}
this._reader = new CsvQueryProcessor(strtogetipldotcsv);
#endregion
}
public void TestSelectColumnsWithoutWhereWithGroupByCount()
{
int totalRecordsExpected = 31;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select city,count(*) from " + strtogetipldotcsv + " group by city");
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
((GroupDataSet)dataSet).GroupedDataSet.Count.Should().Be(totalRecordsExpected);
}
public void TestWithWhereThreeConditionsOrderBy()
{
int totalRecordsExpected = 420;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select winner,player_of_match,city,team1,team2 from " + strtogetipldotcsv + " where season >= 2013 or toss_decision != bat or city = Bangalore order by winner");
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
dataSet.Rows.Count.Should().Be(totalRecordsExpected);
dataSet.Rows.Should().NotBeNull();
}
public void TestWithWhereThreeConditionsGroupBy()
{
int totalRecordsExpected = 13;
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select team1 from " + strtogetipldotcsv + " where season >= 2013 or toss_decision != bat and city = Bangalore group by team1");
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
((GroupDataSet)dataSet).GroupedDataSet.Count().Should().Be(totalRecordsExpected);
}
/// <summary>
/// Method to execute the query and return the resulting dataset
/// </summary>
/// <param name="queryString">The input query</param>
/// <returns>Returns the resultant dataset if any or null</returns>
public DataSet executeQuery(string queryString)
{
if (Common.IsValidQueryBasic(queryString))
{
queryParser = new QueryParser();
queryParameter = queryParser.ParseQuery(queryString);
CsvQueryProcessor queryProcessor = new CsvQueryProcessor(queryParameter.File);
if (queryProcessor != null)
{
if (string.Equals(queryParameter.QueryType, "SIMPLE_QUERY"))
{
return(queryProcessor.GetDataRow(queryParameter));
}
}
}
return(null);
}
public void TestSelectCountColumnsWithoutWhere()
{
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select count(city) from " + strtogetipldotcsv);
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
object rowList = null;
((GroupDataSet)dataSet).GroupedDataSet.TryGetValue("count(city)", out rowList);
rowList.Should().NotBeNull("because count can never be null");
rowList.Should().BeEquivalentTo("570");
}
/// <summary>
/// Method to evaluate all the conditions in the query
/// </summary>
/// <param name="queryParameter">The query parameter containing properties of the query</param>
/// <param name="rowValues">The field values of a row</param>
/// <returns>True or false based on whether the condition saitisfies or fails</returns>
public static bool EvaluateExpression(QueryParameter queryParameter, List <string> rowValues)
{
///If the file name is different than previous file name then recalculate all the properties related to it
if (!string.Equals(_file, queryParameter.File))
{
_file = queryParameter.File;
_queryProcessor = new CsvQueryProcessor(queryParameter.File);
_headers = _queryProcessor.GetHeader().Headers.ToList();
_dataTypes = _queryProcessor.GetColumnType().DataTypes.ToList();
}
///Find the column positions that are being referred by the where clause
List <int> fieldPositions = queryParameter.Restrictions
.Select(restriction => _headers.IndexOf(restriction.propertyname)).ToList();
///List containing the result of each conditions
List <bool> conditions = new List <bool>();
///Find the result of each result
for (int i = 0; i < queryParameter.Restrictions.Count; i++)
{
conditions.Add(PerformEvaluation(rowValues[fieldPositions[i]],
queryParameter.Restrictions[i].propertyValue,
_dataTypes[fieldPositions[i]], queryParameter.Restrictions[i].condition));
}
///After finding the result of each condition logical operators should be applied if any
if (queryParameter.LogicalOperators != null)
{
List <string> logicalOperators = queryParameter.LogicalOperators.ToList();
///Execute all and conditions first due to priority
PerformSpecificLogicalOperations("and", conditions, logicalOperators);
///Execute or conditions after and
PerformSpecificLogicalOperations("or", conditions, logicalOperators);
}
///The last value in the condition bool list will be the resulting bool
return(conditions.Last());
}
/*
* This method will:
* 1.parse the query and populate the QueryParameter object
* 2.Based on the type of query, it will select the appropriate Query processor.
*
* In this example, we are going to work with only one Query Processor which is
* CsvQueryProcessor, which can work with select queries containing zero, one or
* multiple conditions
*/
public DataSet executeQuery(string queryString)
{
/* instantiate QueryParser class */
/* call parseQuery() method of the class by passing the queryString which will return object of QueryParameter
*/
queryParser = new QueryParser();
queryParameter = queryParser.ParseQuery(queryString);
/*
* Check for Type of Query based on the QueryParameter object. In this assignment, we will process only queries containing zero, one or multiple where conditions i.e. conditions without aggregate functions, order by clause or group by clause
*/
/*
* call the GetDataRow() method of CsvQueryProcessor class by passing the QueryParameter Object to it. This method is supposed to return DataSet
*/
csvQueryProcessor = new CsvQueryProcessor(queryParameter.File);
DataSet dataSet = csvQueryProcessor.GetDataRow(queryParameter);
return(dataSet);
}
public void TestSelectCountColumnsWithoutWhere2()
{
DataSet dataSet = null;
try
{
CsvQueryProcessor reader = new CsvQueryProcessor(strtogetipldotcsv);
dataSet = new Query().ExecuteQuery(@"select count(city),sum(win_by_runs),min(win_by_runs),max(win_by_runs),avg(win_by_runs) from " + strtogetipldotcsv);
}
catch (Exception exception)
{
exception.GetType().ToString().Should().Be("System.IO.FileNotFoundException");
}
object rowList = null;
((GroupDataSet)dataSet).GroupedDataSet.TryGetValue("sum(win_by_runs)", out rowList);
rowList.Should().NotBeNull("because sum can never be null");
rowList.Should().BeEquivalentTo("7914");
((GroupDataSet)dataSet).GroupedDataSet.TryGetValue("count(city)", out rowList);
rowList.Should().NotBeNull("because count can never be null");
rowList.Should().BeEquivalentTo("570");
((GroupDataSet)dataSet).GroupedDataSet.TryGetValue("min(win_by_runs)", out rowList);
rowList.Should().NotBeNull("because minimum can never be null");
rowList.Should().BeEquivalentTo("0");
((GroupDataSet)dataSet).GroupedDataSet.TryGetValue("max(win_by_runs)", out rowList);
rowList.Should().NotBeNull("because maximum can never be null");
rowList.Should().BeEquivalentTo("144"); //rowList.Contains(new Row(new string[] { "144" })).Should().BeTrue("because max of win_by_runs is 144");
((GroupDataSet)dataSet).GroupedDataSet.TryGetValue("avg(win_by_runs)", out rowList);
rowList.Should().NotBeNull("because average can never be null");
rowList.Should().BeEquivalentTo("13.72"); //rowList.Contains(new Row(new string[] { "144" })).Should().BeTrue("because max of win_by_runs is 144");
}
