oastat - get your stats into a mySQL or postgreSQL database

[sago007]

A very long time ago I mentioned that I was working on a program to take the log file and write it to a postgreSQL database. As time has progressed there have been added support for mySQL and XML output too. Things like these can be developed forever but with the release of 0.8.8 it was time to make a public release. Feedback is welcome.

The program is designed to do one job and do it well. I recognize that people are interested in different parts of the data. Some might want to put a website in front of it. Some might want to use advanced report tools or dashboards and others might just like to do queries for data of many special cases (like who have most kills on maps beginning with an 's' between 5. February and 22. March then only victims with at least 5 kills and those names start with an 'A' are counted).

This program has also been used as a way to determent what was missing in the games.log file hopefully the extra log will benefit other programs like oastat as well.

For this reason, the structure of the database are considered important. Currently the database looks like this:


Other notes:
oastat_uservars are not currently filled
The program has no Windows support at the moment.
The program is designed to read from stdin (like "tail -f games.log > oastat -f """) so that it can update the database live (it can also read from file if desired)
Output can be to XML-files, mySQL or postgreSQL
If writing output to XML-files it can be run a script on the file afterwards if needed.
Requires 0.8.8 or later because the "Init:"-message must contain a timestamp. The timestamp/servername is used for duplicate checking.
Players are recognized by GUID
GUID is stored hashed in the database
The resulting database have approximately the same size as the log file it has parsed, so this is not a way to compress the log file.

The webpage is:
http://code.google.com/p/oastat/
This contains information about compiling and running.

The original post was posted in "Club Nub" on January 08, 2011, 19:38:02. For non registered users I will just post the original message here:

It is a long time since I started on the project that was originally suggested by Czestmyr back in 2007 (http://openarena.ws/board/index.php?topic=866.0) and started to be added in OAX back in 2008 (http://openarena.ws/board/index.php?topic=1908.msg14792#msg14792).

I have created a program that parses the log files and places the data in a database. It is not that stable yet but it is getting there.

The idea is that all tasks should be performed by a program optimized for the task. The 3 programs needed:

1. A game (OpenArena): Must host the game and write log files with all events
2. A log-file parser (oastat): Must parse the log files and write to database
3. A front-end: Must present the information to the user.
Any one of the should not depend strongly on any of the others. I should be able to change front-end or upgrade the log-file parser without changing the two other programs.

[GrosBedo]

That's AMAZING! Will be of a great use to forensic player's history. Plus your database scheme seems to be very clean. Is it Boyce-Codd Normal Form?

[sago007]

I have tried to keep the database scheme normalized. While developing and testing I had to make a few changes that I did not plan for:

• Originally oastat_players did not have a playerid but joined on guid instead. This used too much space. If anyone reads the code and wonder about the sub-selects in the insert statetments this is the reason.
  
• A unique constaint was added to oastat_userinfo on (gamenumber,second) so only the last userinfo change per second is saved. This makes oastat_userinfo.eventNumber redundant.
  
• oastat_team_events have a gametype attribute because some events might be shared across certain gametypes (ie. CtfEliminatin might use events from standard Elimination events ) however in OpenArena's case this attribute can always be derived from oastat_games.gametype because I choose not to share events in OAX.
  
• Originally I planed to use a table per gametype but I cut a corner there.

All foreign keys have cascade so deleting from oastat_games deletes the connected events and trying to delete a referenced player should fail.