connect with WCA database about cubecomp HOT 10 CLOSED

I would move the WCA DB and its API to a different app/web service/whatever. I started doing it three years ago (https://github.com/timhabermaas/wca-api) and it worked so far. Character encoding and the non-standard dump MySQL produces were a freaking pain in the ass though...

Why? I don't want to touch this repo if they break the WCA export.

from cubecomp.

I'm okay with that approach... Would be cool if the WCA API in this repo here would be optional, so if I want to run this app somewhere but I don't also want to setup the WCA API, I can just disable it in the config and everything that doesn't depend on it still works.

Can you get your wca-api repo into up-to-date shape? (bump all gems to recent versions, see if it still works with the latest WCA export)

from cubecomp.

I think I might be able to do so. I just noticed there's a TSV export, so importing might become slightly easier?

from cubecomp.

This is the script I use in my weekly cron task to import the latest WCA db

#!/bin/sh

FILENAME=$(lynx --source http://www.worldcubeassociation.org/results/misc/export.html | grep -E "WCA_export[0-9_]+\.sql.zip" -o | head -n1)

wget -c http://www.worldcubeassociation.org/results/misc/$FILENAME &&
unzip -o $FILENAME &&
sed -e "s/CHARSET=latin1/CHARSET=utf8/g" < WCA_export.sql > WCA_export.sql.utf8
mysql --default-character-set=utf8 wca -u wca -p xxxxx < WCA_export.sql.utf8 &&
echo $FILENAME > latest.txt &&
rm -f WCA_export.sql WCA_export.sql.utf8

from cubecomp.

Up to you, but I would expect TSV import to be much slower than just restoring a SQL dump... The encoding is messed up for some reason, but my script "fixes" that.

from cubecomp.

Thanks for sharing, that was my script: https://github.com/timhabermaas/wca-api/blob/master/Rakefile I tried to make it work with PostgreSQL. Big mistake. ^^

Reading the TSV files using Ruby's csv works like a charm (encoding isn't broken either). As you mentioned, importing will probably be slow though.

from cubecomp.

only downside here that I see is that this will slow down some things when inlining in requests from within the app (the checklist for example).. in the old software, the checklist shows things like "Anniversary! Competition 20!" if someone has a competition number that's divisible by 5 or something like that... similar stuff is on the printable nametags sometimes.

can you do batching (query multiple persons in one request) with your app?

I guess we could also always background-fetch this stuff in a crontask or something like that... or fetch it only once when the person registers and live with the number not being up-to-date..

from cubecomp.

Batching is definitely on my list (e.g. for the psych sheet: /records?eventId=333&competitorIds=2007HABE01,2007WEIN01). If latency is a problem (it always is...) we could deploy it on the same server as cubecomps. The HTTP + JSON parsing overhead should be in the same ballpark as the overhead introduced by fetching it from MySQL + using ActiveRecord (wild guess, no data to back it up).

The cron-task thing actually sounds more complicated than simply putting the data directly into cubecomps. I wanted to make it easer to maintain, not harder. If this doesn't work out, I'll scrap the idea.

from cubecomp.

I started implementing the autocompletion in #47.

from cubecomp.

Gonna close this issue since we have separate issues for the individual features now

from cubecomp.