public void test_SelectBetweenTeams()
{
/* Test with test data , kärpät, tps and tappara. Function should
* returns all rows which contain only teams from parameter list (=6). */
List <string> teams = new List <string>();
teams.Add("Kärpät");
teams.Add("Tappara");
teams.Add("TPS");
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectBetweenTeams(teams));
List <Match> rows = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(6, rows.Count);
teams.Clear();
teams.Add("TPS");
teams.Add("TPS");
teams.Add("TPS");
mq.clearSubQueries();
mq.addSubQuery(db.SelectBetweenTeams(teams));
rows = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(0, rows.Count);
}
public void test_getQueryString()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery("test ");
mq.addSubQuery("test 2 ");
mq.addSubQuery("test 3 ");
string expected = "SELECT * FROM matches WHERE test AND test 2 AND test 3 ;";
Assert.AreEqual(expected, mq.getQueryString());
}
public void test_QueryMatches_GD_atLeast2_overtime()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectWhereGD(2, true));
mq.addSubQuery(db.SelectWhereOvertime(true));
System.Console.WriteLine(mq.getQueryString());
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(0, matches.Count);
}
public void test_SelectWhereGD_GD_0()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectWhereGD(0, false));
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(0, matches.Count);
mq.clearSubQueries();
mq.addSubQuery(db.SelectWhereGD(0, true));
matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(18, matches.Count);
}
public void test_SelectWhereGD_GD_negative()
{
/*negative goal difference is changed to 0, so test results should be equal
* to test_SelectWhereGD_GD_0().*/
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectWhereGD(-1, false));
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(0, matches.Count);
mq.clearSubQueries();
mq.addSubQuery(db.SelectWhereGD(-2, true));
matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(18, matches.Count);
}
public void test_addSubQuery()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery("test");
Assert.AreEqual(1, mq.get_sub_queries().Count);
}
public void test_addSubQuery_null_string()
{
/*empty string should not add anything to list.*/
MatchQuery mq = new MatchQuery();
mq.addSubQuery(null);
Assert.AreEqual(0, mq.get_sub_queries().Count);
}
public void test_SelectWherePlayoff_false()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectWherePlayoff(false));
List <Match> regularSeason = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(13, regularSeason.Count);
}
public void test_SelectWherePlayoff_true()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectWherePlayoff(true));
List <Match> playoff = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(5, playoff.Count);
}
public void test_SelectWhereGD_isAtLeast_false()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectWhereGD(2, false));
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(14, matches.Count);
}
public void test_SelectWhereOvertime_false()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectWhereOvertime(false));
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(10, matches.Count);
}
public void test_QueryMatches_FromTeams_FromSeasons_playoff()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectFromTeams(new List <string>()
{
"Kärpät", "Tappara"
}));
mq.addSubQuery(db.SelectFromSeasons(new List <string>()
{
"17-18"
}));
mq.addSubQuery(db.SelectWherePlayoff(true));
System.Console.WriteLine(mq.getQueryString());
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(2, matches.Count);
}
public void test_SelectNLastFromTeam()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectNLastFromTeam(2, "Kärpät"));
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(2, matches.Count);
Assert.AreEqual("2017-03-26", matches[0].date);
Assert.AreEqual("2017-03-25", matches[1].date);
}
public void test_SelectBeforeOrAfterDate()
{
/*
* 1. Select * before the date "2017-03-25". Should return 13 matches.
* 2. Select * after date "2016-03-26". Should return 14 matches.
* 3. Join should return 9 matches.
* 4. Union should return 18 matches.
* 5. & 6:Test with date "20933-13-13". Should throw DateConversionError.
*/
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectBeforeOrAfterDate("2017-03-25", false));
List <Match> before = db.QueryMatches(mq.getQueryString());
mq.addSubQuery(db.SelectBeforeOrAfterDate("2016-03-26", true));
List <Match> join = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(13, before.Count);
Assert.AreEqual(9, join.Count);
}
public void test_SelectFromSeasons()
{
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectFromSeasons(new List <string>()
{
"17-18"
}));
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(9, matches.Count);
}
public void test_SelectWhereAwayteam()
{
MatchQuery mq = new MatchQuery();
List <string> teams = new List <string>()
{
"Kärpät", "asdf"
};
mq.addSubQuery(db.SelectWhereAwayteam(teams));
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(4, matches.Count);
}
public void test_SelectBetweenTeamsFromSeasons()
{
MatchQuery mq = new MatchQuery();
List <string> teams = new List <string>()
{
"Kärpät", "Tappara"
};
List <string> seasons = new List <string>()
{
"16-17"
};
mq.addSubQuery(db.SelectBetweenTeamsFromSeasons(teams, seasons));
List <Match> matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(2, matches.Count);
seasons.RemoveAt(0);
mq.clearSubQueries();
mq.addSubQuery(db.SelectBetweenTeamsFromSeasons(teams, seasons));
matches = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(4, matches.Count);
}
public void test_SelectBeforeOrAfterDate_teamOverload()
{
/* 1. Test with incorrect date format, should throw DateConversionError
* 2. Test with a list of Kärpät, Kärpät after date '2017/03/25'.
* Should return 2 matches.
* 3. Test with list of Kärpät, Kärpät, TPS after date '2017-03-25.
* Should return 3 matches.'*/
List <string> teams = new List <string>()
{
"Kärpät", "Kärpät"
};
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectBeforeOrAfterDate(teams, "2017/03/25", true));
List <Match> rows = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(2, rows.Count);
teams.Add("TPS");
mq.clearSubQueries();
mq.addSubQuery(db.SelectBeforeOrAfterDate(teams, "2017-03-25", true));
rows = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(3, rows.Count);
}
public void test_SelectFromTeams()
{
/* Test with test data , kärpät, tps and tappara. Function should
* returns all rows which contain teams from parameter list (=6).
* Add SaiPa to list, should return 12 matches.*/
List <string> teams = new List <string>();
teams.Add("Kärpät");
teams.Add("Tappara");
teams.Add("TPS");
MatchQuery mq = new MatchQuery();
mq.addSubQuery(db.SelectFromTeams(teams));
List <Match> rows = db.QueryMatches(mq.getQueryString());
Assert.AreEqual(12, rows.Count);
}
/// <summary>
/// Performs a query based on the parameters, returns a list of matches that fit the query.
/// </summary>
/// <param name="between">When true, only matches between parameter teams are searched for.
/// When teams has no items it is ignored.</param>
/// <param name="gd_is_at_least">When true, only matches which have a goal difference of at least goal_difference are searched for, when false matches which have a goal difference of at most
/// goal_difference are searched for. When null goal difference is not a search parameter.</param>
/// <param name="goal_difference">Integer indicating what is the limit of goal_difference between home-
/// and awayteam that is used in the search.</param>
/// <param name="match_end_in_overtime">true = Only matches that ended in overtime
/// are returned. false = only matches that ended in regular time are returned. When set to null match ending
/// is not a search parameter.</param>
/// <param name="played_at_home">When true, only home matches are returned from parameter teams. When false, only away matches are returned from
/// parameter teams. When null, both home and away matches are returned.</param>
/// <param name="playoff">When true, only playoff mathches are returned. When false, only
/// regular season matches are returned. When null, all match types are returned.</param>
/// <param name="seasons">A list of seasons where the matches are searched for. When no seasons are given,
/// matches are searched from all the seasons.</param>
/// <param name="teams">List of team names whose matches are searched for. When no teams are given
/// all teams are considered in the search.</param>
/// <param name="endDate">Only matches earlier than this date are searched for.</param>
/// <param name="startDate">Only matches later than this date are searched.</param>
/// <returns>A list of matches that where in all of the queries.</returns>
public List <Match> getmatches(List <string> seasons, List <string> teams, string startDate = null, string endDate = null, bool between = false, int?goal_difference = null, bool?gd_is_at_least = null, bool?playoff = null, bool?played_at_home = null, bool?match_end_in_overtime = null)
{
/*
* 1.Get the path to database from file filePath.txt
* 2.Set connectionString
* 3.Set seasons and teams null if they have no items.
* 4.Create the joined list which will include all separate query results.
* 5. Add basequery to join
* 6. Add miniqueries to join.
* 7.Return db.Join(join)*/
string path = File.ReadAllText(@"Application Files\filePath.txt");
string pathCombined = Path.Combine(path, "db\\liiga.db");
if (File.Exists(pathCombined))
{
Database db = new Database();
db.setConnectionString(String.Format("Data Source = {0}; Version = 3;", pathCombined));
if (seasons.Count == 0)
{
seasons = null;
}
if (teams.Count == 0)
{
teams = null;
}
MatchQuery mq = new MatchQuery();
if (teams == null && seasons == null && startDate == null && endDate == null && goal_difference == null && gd_is_at_least == null && playoff == null && played_at_home == null && match_end_in_overtime == null) //basequery
{
return(db.QueryMatches(mq.getQueryString()));
}
else if (teams == null && seasons != null)
{
mq.addSubQuery(db.SelectFromSeasons(seasons));
}
else if (teams != null && seasons == null && !between)
{
mq.addSubQuery(db.SelectFromTeams(teams));
}
else if (teams != null && seasons == null && between)
{
mq.addSubQuery(db.SelectBetweenTeams(teams));
}
else if (teams != null && seasons != null && between)
{
mq.addSubQuery(db.SelectBetweenTeamsFromSeasons(teams, seasons));
}
else if (teams != null && seasons != null && !between)
{
mq.addSubQuery(db.SelectFromTeams(teams));
mq.addSubQuery(db.SelectFromSeasons(seasons));
} //basequery ends
if (gd_is_at_least != null && goal_difference != null)
{
mq.addSubQuery(db.SelectWhereGD((int)goal_difference, (bool)gd_is_at_least));
}
if (playoff != null)
{
mq.addSubQuery(db.SelectWherePlayoff((bool)playoff));
}
if (played_at_home != null && teams != null)
{
if ((bool)played_at_home)
{
mq.addSubQuery(db.SelectWhereHometeam(teams));
}
if ((bool)!played_at_home)
{
mq.addSubQuery(db.SelectWhereAwayteam(teams));
}
}
if (match_end_in_overtime != null)
{
mq.addSubQuery(db.SelectWhereOvertime((bool)match_end_in_overtime));
}
if (!String.IsNullOrEmpty(startDate))
{
mq.addSubQuery(db.SelectBeforeOrAfterDate(startDate, true));
}
if (!String.IsNullOrEmpty(endDate))
{
mq.addSubQuery(db.SelectBeforeOrAfterDate(endDate, false));
}
return(db.QueryMatches(mq.getQueryString()));
}
else
{
throw new APIError("File " + path + " not found");
}
}
