WCA website crawling

R

Ron

Veteran
Joined
Sep 9, 2006
Messages
337
Location
Amsterdam
WCA ID
2003BRUC01
Hi guys,

As you may have noticed the WCA website has become slower recently.
This is because our provider moved the WCA website to another (busier?) server. The reason mentioned was that some people are periodically crawling the WCA results.

So if you are using some tool to update your database of WCA results, then please stop doing this. If you need data, then send me an e-mail and I will gladly send you the data in a standardised format.
If there are generic requirements for data delivery, then let me know too. The discussion on the WCA Forum about this subject did not come to a conclusion.

Thanks,

Ron
 
Hey Ron,

Approximately how large is the database?
I have a rather large server here, soon with FIOS connection, and there may be a chance of me being able to host at least a portion of the database, if that would be helpful.

StachuK
 
I'm guilty off running automated searches of the WCA site but always with consideration and not very regularly.

It would be extremely useful if the data normally searchable from the web interface were made available, perhaps on a mirror server somewhere, and kept up to date (e.g. once a week) in a reasonable relational format (perhaps a SQLite DB or MySQL dbdump).
 
Stachuk1992 said:
I have a rather large server here, soon with FIOS connection, and there may be a chance of me being able to host at least a portion of the database, if that would be helpful.
Click to expand...

msemtd said:
It would be extremely useful if the data normally searchable from the web interface were made available, perhaps on a mirror server somewhere, and kept up to date (e.g. once a week) in a reasonable relational format (perhaps a SQLite DB or MySQL dbdump).
Click to expand...

These two sound like they could go together well :) I can see privacy being an issue though. Perhaps don't include birthdates or other sensitive info on the mirror?

If this comes through, while its being set up, the database could certainly use some restructuring... I talked with Dave briefly about this, and the "everything in one big table" format can be improved quite significantly to reduce memory usage and server load.
 
Last edited:
Hi all,

The WCA database is now downloable at http://www.worldcubeassociation.org/results/wcadumpyyyymm.xlsx
We will post at the end of every month, so the first file is wcadump200907.xlsx (size 6.5MB).
If we forget to post, then send an e-mail to [email protected].
Now that we have the database online please stop crawling the WCA website to update your own databases.

Looking forward to your feedback.

Thanks,

Ron
 
I cannot open the file as it was created by a newer version of Excel than the one i am running (and i am running 2002). I'd personally rather see an sql dump file than an Excel file.

Secondly, i see you have a naming convention in place, which is fine if you are trying to have versioning available. But i would suggest still making one "current" version that is always the same name each time. So i download the same file anytime i wish and i will get the latest version. I could then use the specific file names with the YYMMDD for any old version.

In the end, i don't crawl the WCA site, nor do i wish to have my own version. I just want to be able to query the current version for selected data. Which of course brings me back to my webservice request which is documented on the WCA board.
 
Hi Dave,

Thanks for your feedback.

I specifically chose not to use a current file. Just to make sure that noone is downloading 6.5MB from the server just to check if there is a new version.

The problem with a web service is:
= if we have one service per competitor or so, then I am pretty sure we will get extremely heavy loads on the server, just by people who make unconsciously incompetent mistakes, e.g. calling the web service every time another web page is downloaded
= if we have one webservice for all competitors/results, then I am afraid caching will be a problem, which could again overload our server

I agree with you that the solution is not perfect, but at least it is easy for me and should still be workable for people who need the data.

About the Excel version: yes, I used Excel 2007 format, because that format includes compression. Otherwise the file size would be 18MB instead of 6.5MB.
The reason I chose Excel is that I think I tried everything, but exporting from MySQL to SQL queries gives problems with the special characters, like éöà. Any ideas on that are welcome.

Have fun,

Ron
 
Last edited:
I noticed in the queries used to create ths Excel file that he didn't select * from the person table. I assume that would be to protect sensitive information like birthdates. The mysqldump would expose that information and might be a legal issue due to the number of minors in the database.
 
masterofthebass said:
David, I can create a sql dump for you if you would like. (as i'm sure that many people could :p)
Click to expand...
As far as I know there is no such thing as an sql dump. You could make a mysql dump only if the backup was done in mysql and that would only be useful for people that run a mysql database.

I believe that Excel is a good option and that http://support.microsoft.com/kb/953336 (free Excel 2007 Viewer) might be helpful for some
 
Ron said:
Hi Dave,
Click to expand...

Ron

That is fair about the current-file approach. Perhaps it would be a wise project to get one of your developers to do up a simple page that has a list of current files and when they were updated, so people are not doing it blindly.

Not sure i completely understand your concern with the web service. It would be a basic REST architecture approach. The service just acts as a gateway to the data, and allows you to control which data you make available and even who you make it available to.

The request to the web service contains what you are looking for, and who is looking for it. This approach allows you to permit or deny people as you see fit, even allowing certain data available to only certain people. You could charge a fee to people that wish to have the service. You also log their activity to ensure they are not sharing the access key around.

To summarize, i would send a request like so:

http://www.worldcubeassociation.com/webservice.php?Key=1234&Query=Person&Value=Dave+Campbell


The data returned, instead of an HTML page that we are accustomed to receive via a browser, is an XML file with the response. I attached a very simple mock up of the type of XML file that could be returned.

And lastly, i am sure there is a way to handle the character encoding for an SQL dump file. You should be able to just handle things as UTF-8 or something. I will do some searching when i have more time to find out in case you wish to go that route. Of course, if any of the other guys know how to handle it already, feel free to beat me to it.
 

Attachments

There appears to be a hashed password in the competitions table :eek:

The MySQL table definitions would be most useful for those of us importing back into MySQL!

EDIT: I've inferred some column types - perhaps they're not too far out -- e.g. : -
Code: 
CREATE TABLE /*!32312 IF NOT EXISTS*/ "formats" (
  "id" varchar(10) NOT NULL DEFAULT '',
  "description" varchar(50) DEFAULT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE /*!32312 IF NOT EXISTS*/ "competitions" (
  "id" varchar(50) NOT NULL DEFAULT '',
  "name" varchar(50) DEFAULT NULL,
  "cityName" varchar(50) DEFAULT NULL,
  "countryId" varchar(50) DEFAULT NULL,
  "information" varchar(2000) DEFAULT NULL,
  "year" varchar(50) DEFAULT NULL,
  "month" varchar(10) DEFAULT NULL,
  "day" varchar(10) DEFAULT NULL,
  "endMonth" varchar(10) DEFAULT NULL,
  "endDay" varchar(10) DEFAULT NULL,
  "eventSpecs" varchar(500) DEFAULT NULL,
  "wcaDelegate" varchar(500) DEFAULT NULL,
  "organiser" varchar(500) DEFAULT NULL,
  "venue" varchar(800) DEFAULT NULL,
  "venueAddress" varchar(800) DEFAULT NULL,
  "venueDetails" varchar(800) DEFAULT NULL,
  "website" varchar(500) DEFAULT NULL,
  "cellName" varchar(100) DEFAULT NULL,
  "showAtAll" varchar(10) DEFAULT NULL,
  "showResults" varchar(10) DEFAULT NULL,
  "password" varchar(100) DEFAULT NULL,
  "showPreregForm" varchar(10) DEFAULT NULL,
  "showPreregList" varchar(10) DEFAULT NULL,
  "latitude" varchar(50) DEFAULT NULL,
  "longitude" varchar(50) DEFAULT NULL,
  PRIMARY KEY ("id")
);

EDIT2: I've imported the data into a MySQL database (rough and ready, no foreign keys, but accommodates the available data) and I have a mysqldump file available for anyone interested (bz2 compressed sql text 1.3Mb).
 
Last edited:
Ron said:
About the Excel version: yes, I used Excel 2007 format, because that format includes compression. Otherwise the file size would be 18MB instead of 6.5MB.

Have fun,

Ron
Click to expand...
How about using http://us2.php.net/manual/en/book.zip.php or http://www.weberdev.com/get_example-4066.html on the XLS?
The .xlsx is 6.3 MB, and when I convert it to .xls using OpenOffice and zip it, it becomes 4.1 MB. And unzipping is much easier to do (say, automatically) than xlsx -> xls.

However, even if I need to convert, I'm happy with this. Mathematica imports it easily. :)
 
Lucas Garron said:
The .xlsx is 6.3 MB, and when I convert it to .xls using OpenOffice and zip it, it becomes 4.1 MB. And unzipping is much easier to do (say, automatically) than xlsx -> xls.
Click to expand...

Or to be more inclusive (OpenOffice is free and cross-platform) import into OpenOffice and keep in OASIS OpenDocument format (already compressed - 3Mb - copy available for anyone interested) :D
 
Last edited:
You must log in or register to reply here.

Similar threads

OtaMota
WCA website redesign Beta Testing
Replies
13
Views
702
Thomas.Caleb
Thomas.Caleb
ClockIt
FTO is now available in the WCA Website!!!
2
Replies
34
Views
1,986
mdcuber9
mdcuber9
ClockIt
Logo for FTO in WCA website and other website Proposal
Replies
9
Views
798
one_ace_of_cubes
one_ace_of_cubes
Maple Feng
A website to score a cuber's overall ability in 17 WCA official events
Replies
19
Views
3,767
Maple Feng
Maple Feng
R
  • Poll Poll
is it just me or does the wca website look like this
Replies
19
Views
2,017
OrangeCenterCap
OrangeCenterCap
Back
Top