SoC Ideas Stats Server

Wesnoth has an infrastructure which records details of campaigns that players play into a centralized MySQL database. However, we only have rudimentary reports based on this MySQL database available at this time, at [stats.wesnoth.org]

This project would involve writing a stats reporting web site which would take the data from the MySQL database and produce reports in chart and table form. Campaign designers would be able to use these reports to gather feedback on their campaigns and get ideas for improvements.

A student could largely make their choice of infrastructure for creating the Website -- whether they prefer Python, Perl, Ruby, PHP, etc. This is a great opportunity for someone who doesn't want to dive into hardcore C++ to make a valuable contribution to Wesnoth.

It maybe worth while to track stats from multiplayer games. It could provide useful insights. There are some major difficulties because Wesnoth does not record the outcome of multi-player games.

Database Details

The MySQL database is created with these commands:

CREATE TABLE GAMES (
  game_id INT NOT NULL AUTO_INCREMENT, 
  timestamp DATETIME NOT NULL, 
  user_id CHAR (14) NOT NULL, 
  serial CHAR (18) NOT NULL, 
  platform CHAR(8), 
  version CHAR (14), 
  campaign CHAR(40), 
  difficulty CHAR(20), 
  gold INT, 
  turns INT, 
  scenario CHAR (40), 
  start_turn INT, 
  time INT, 
  result ENUM ('victory', 'defeat', 'quit'), 
  end_time INT, 
  end_gold INT, 
  end_turn INT, 
  PRIMARY KEY (game_id)
);

CREATE TABLE SPECIAL_UNITS (
  game_id INT, 
  name CHAR (40), 
  level INT, 
  experience INT, 
  FOREIGN KEY (game_id) REFERENCES GAMES(game_id) ON DELETE CASCADE
);

CREATE TABLE UNITS (
  game_id INT NOT NULL, 
  level INT NOT NULL, 
  type CHAR (30), 
  count INT NOT NULL, 
  FOREIGN KEY (game_id) REFERENCES GAMES(game_id) ON DELETE CASCADE
);

Example

There is an example of a website which queries from the stats database here: [[1]] -- this website reports the number of Wesnoth scenarios that have been completed, each day since the release of Wesnoth 1.6.

It is an interesting statistic, but what we want is a generic framework for building lots of different, interesting reports. For instance, someone might want to see how many people who use Windows have played, or how many people have been defeated in scenarios, or how many people have played a specific campaign. There are many possibilities, and we'd like a very user friendly framework which allows displaying many different interesting reports.

Aggregation

An additional problem is that the framework can't afford to do a full table scan for every single query. That will just grow too expensive, especially as our database grows larger and larger. Instead, a proposal should work out a way to aggregate common queries into an 'aggregate' table. These aggregate tables would be computed periodically by a separate job.