DB from API (example)

[barrycarter]

To demonstrate what I plan to do w/ API data, I've now created a
public read-only database of my conquerclub games:

http://conquerclub.db.94y.info/

Reminder: this only includes *my* games, not everyone's.

Questions I can now answer (SQL query appears on listed page):

% How many games have I won/lost?

http://27c2c6afc1831bbe82a27228ee925100 ... .94y.info/

% What are my best maps (Bayesian estimator)?

http://f7accc0b7c484ca7eff9a266afbba498 ... .94y.info/

% What's the highest score I ever achieved? [1647]

http://711d2b4e6effb5fc90894bce4c625f3f ... .94y.info/

% What's the most games I've been playing at once? [104]

http://55f18331f84f809e3011d4efc6beb877 ... .94y.info/

% How's my performance in games based on number of players?

http://c220e38d13dc6ef3c4d4845463c75331 ... .94y.info/

% How current is this database? [2010-09-22 12:40:59]

http://1cf4af6f0201c8c2ffbdc953453344ca ... .94y.info/

% Who do I play against most often? [iznogoud]

http://cda4326dbc3b55d7a9ddd51336a951a9 ... .94y.info/

% Which games did I play against iznogoud?

http://70b6269311b36765e46d57cd9c3d69b3 ... .94y.info/

% OK, how about links to those games?

http://4176373846be198781a3779e152a83c0 ... .94y.info/

% What is the ultimate answer?

http://513a1b7bb1a03f59bc4b127272864a80 ... .94y.info/

Disclaimer: Not all data is necessarily 100% accurate. In particular,
the queries above behave oddly for games where I've been eliminated,
but the game hasn't yet ended.

[chipv]

Very good work indeed. We have been speaking on PM and I like the idea in principle.

How long does it take to extract data for one player? (How much effort)

[barrycarter]

I actually cheated a bit here and used the more extensive data from
the HTML pages, which meant I had to download 495 pages. This only
took a few minutes (I didn't time it exactly).

The Perl script to process the pages and put them in an SQLite db also
took a few minutes.

I agree that this won't scale well: the time (and even space) required
for 6M games might be excessive.

I've now written a Perl script that parses the XML data:

http://github.com/barrycarter/bcapps/bl ... 2sqlite.pl

The resulting db is:

http://ccgames.db.94y.info/

and includes only 1 player's games, since I'm waiting until the API
upgrade to download data for all games.

I haven't really played w/ this much. If someone's interested, take a
look at the schema:

http://schema.ccgames.db.94y.info/

I need to tweak my script to include log items, but things like start
time aren't in the XML dump, so can't be in the database.