Competition Set-up (SQL)

The application is primarily data driven and setting up a competition involves entering sql via scripts as described below.

(i) First Round Fixtures All the fixtures are loaded into the database from a sql script using an insert statement of the following form;

insert into fixtures (matchdate, matchtime, matchid, venue, stageid, groupid, team1, team2)
values ('10/06/2016','21:00',1,"Stade de France, Saint-Denis",1,1,2,3);

(ii) Knock-out Stage Fixtures Since the team ids will not be known for the knock-out stage fixtures, 0''s are entered for these games and the team ids are generated later using the Match Generator function as described in the about section. However, we do know when and where each match will be played, just not which teams will be there! The data the 'Match Generator' function requires in order to populate the knock out stages is as followings;

insert into match_generator (stageid, matchid, homeaway, groupid, grouppos) values('2', 25, 1, 1, 1);
insert into match_generator (stageid, matchid, homeaway, groupid, grouppos) values('2', 25, 2, 2, 2);

So the above 2 lines of sql script, create two database entries that the MatchGenerator will use to create a future fixture. The fixture in question is in stage 2, has a matchid=25, the first row is for the home team, finishing first in group 1 and the second row sets the away team in the fixture as the team finishing second in group 2.

In this way it is relatively simple to programmatically update the existing stage 2 fixtures with the home and away team ids at the end of the first round.

(iii) Calculating the League Tables from the results The following query shows how data is generated from which the league tables are calculated (both actual and predicted tables). This data is then processed to determine the league tables.

In this query the tables are joined using a left join because the results table may not contain a match for the fixture (where the game is not yet played). There is also a union because the first part of the query selects data for matches where each team is considered at home, so we also need to add the data for when the teams are playing an away game. This enables a simple for-loop to run through the data only once, aggregating the league data (games played, wins, draws, losses, goals for & against and points) for each team, rather than having to run through twice.

			" SELECT f.groupid groupid, f.matchid, f.team1 teamid, r.result1, r.result2, f.team2 oteamid " +
			" FROM " + DBConstants.STR_TABLE_FIXTURES + " f " +
			" LEFT JOIN " + DBConstants.STR_TABLE_RESULTS + " r ON f.matchid = r.matchid " +
			" WHERE f.groupid = " + groupId +
			" union " +
			" SELECT f.groupid groupid, f.matchid, f.team2 teamid, r.result2, r.result1, f.team1 oteamid " +
			" FROM " + DBConstants.STR_TABLE_FIXTURES + " f " +
			" LEFT JOIN " + DBConstants.STR_TABLE_RESULTS + " r ON f.matchid = r.matchid " +
			" WHERE f.groupid = " + groupId +
			" ORDER BY groupid, teamid ";

There is an intention to simplify these queries and to refactor the calculations to work in both directions, but that is a task for another time.