DB from API (example)

Discuss developing tools and get details on Conquer Club's API.

Moderators: Tech Team, Global Moderators

Forum rules
Please read the Community Guidelines before posting.

DB from API (example)

Postby barrycarter on Sat Sep 25, 2010 12:18 am

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.
Sergeant 1st Class barrycarter
 
Posts: 51
Joined: Sat Jul 31, 2010 10:42 am
Medals: 7
Standard Achievement (2) Doubles Achievement (2) Manual Troops Achievement (2) General Achievement (1)

Re: DB from API (example)

Postby chipv on Sat Sep 25, 2010 7:22 am

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)
User avatar
Colonel chipv
 
Posts: 2756
Joined: Mon Apr 28, 2008 5:30 pm
Medals: 44
Standard Achievement (2) Doubles Achievement (2) Triples Achievement (2) Quadruples Achievement (2) Terminator Achievement (2)
Manual Troops Achievement (1) Freestyle Achievement (1) Nuclear Spoils Achievement (2) Fog of War Achievement (2) Cross-Map Achievement (3)
Ratings Achievement (2) Tournament Achievement (5) General Achievement (2) Clan Achievement (5) Map Contribution (1)
General Contribution (10)

Re: DB from API (example)

Postby barrycarter on Sat Sep 25, 2010 4:04 pm

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.
Sergeant 1st Class barrycarter
 
Posts: 51
Joined: Sat Jul 31, 2010 10:42 am
Medals: 7
Standard Achievement (2) Doubles Achievement (2) Manual Troops Achievement (2) General Achievement (1)


Return to Tools Development

Who is online

Users browsing this forum: No registered users

cron
Login