SoC Ideas Stats Server - Revision history

Aethaeryn: categorizing

2011-03-06T03:23:47Z

categorizing

← Older revision		Revision as of 03:23, 6 March 2011
Line 57:		Line 57:

	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.		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.

			[[Category:Summer of Code]]

Crab: /* Database Details */

2010-04-28T17:13:13Z

Database Details

← Older revision		Revision as of 17:13, 28 April 2010
Line 47:		Line 47:
	FOREIGN KEY (game_id) REFERENCES GAMES(game_id) ON DELETE CASCADE		FOREIGN KEY (game_id) REFERENCES GAMES(game_id) ON DELETE CASCADE
	);		);

	~~There is a [http://www.wesnoth.org/cgi-bin/stats/dump.pl page dumping a sample of data from each table in the database].~~

	== Example ==		== Example ==

Tomdesp: /* Database Details */ wikified

2009-04-08T06:41:12Z

Database Details: wikified

@@ Line 11: / Line 11: @@
 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 GAMES (
-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 SPECIAL_UNITS (
-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)
+ CREATE TABLE UNITS (
-There is a page dumping a sample of data from each table in the database here: [[http://www.wesnoth.org/cgi-bin/stats/dump.pl]]
+There is a [http://www.wesnoth.org/cgi-bin/stats/dump.pl page dumping a sample of data from each table in the database].
 == Example ==

Dave at 18:19, 24 March 2009

2009-03-24T18:19:25Z

← Older revision		Revision as of 18:19, 24 March 2009
Line 16:		Line 16:

	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)		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)

			There is a page dumping a sample of data from each table in the database here: [[http://www.wesnoth.org/cgi-bin/stats/dump.pl]]

			== Example ==

			There is an example of a website which queries from the stats database here: [[http://www.wesnoth.org/cgi-bin/stats/usage-1.6.pl]] -- 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.

Chains at 01:11, 23 March 2009

2009-03-23T01:11:10Z

← Older revision		Revision as of 01:11, 23 March 2009
Line 4:		Line 4:

	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.		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 ==		== Database Details ==

Dave at 05:27, 20 March 2009

2009-03-20T05:27:55Z

← Older revision		Revision as of 05:27, 20 March 2009
Line 10:		Line 10:

	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 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 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)		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)

Dave: New page: 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...

2009-03-20T05:27:29Z

New page: 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...

New page

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.

== 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)