WCA website crawling

I added three new things to the Excel file (starting with the July 2009 version):
- Countries table
- Continents table
- SQL statements for creation of the tables

There are some additional tables that we use to generate the rankings on the pages without recalculating them all the time.
These tables are regenerated after adding new competition results.
 
Ron,

How would you feel about providing a "mysqldump --no-data" of the original schema? I imported several sheets of the workbook last night and it's not too hard to infer the types, but since it's only a single command, I thought you may not mind.

Thanks
 
Running the command "mysqldump --no-data schemaname" will generate a dump of all the SQL to create the schema without the data in it. That would be helpful to those of us who want to create a schema to import the data into.

I've also posted a more detailed request and an offer to assist over on the WCA site.

Thanks for all the work so far.
 
The new file for August 2009 is online now.
You need to refresh the whole database, because we needed to change some past results as well.

Brunson, I tried what you said but it does not give me more information than the table definitions I added to the first page of the file. What more information do you need?
 
Last edited:
Dave Campbell said:
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.
Click to expand...
I just noticed that the results tab has exceeded the 65535 row limit of OpenOffice Calc and gnumeric, so I can't open it and retrieve all the result data. Bummer.
 
brunson said:
I just noticed that the results tab has exceeded the 65535 row limit of OpenOffice Calc and gnumeric, so I can't open it and retrieve all the result data. Bummer.
Click to expand...
I shall create a sqldump version and an ODS with two results tabs when I get into the office tomorrow. There's no good reason to get locked into non-free software :D
 
That would be so awesome, Michael. I was thinking of writing a python macro for OOCalc to automatically save the tabs as CSV, that's the only part that's still manual for me. I was disappointed at the 65535 row limitation of OO and ended up finding this: http://www.openoffice.org/issues/show_bug.cgi?id=30215

Normally I'd be all over complaining about proprietary formats, but Ron is doing us a huge service so I kept my mouth shut. :)

If you can send it to me I can put it on my web server for anyone else that wants it.

Thanks again. And no end of thanks to Ron for making the data available in the first place.
 
Lucas Garron said:
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. :)
Click to expand...
Code: 
-rw-rw-r--. 1 brunson brunson  7144609 2009-09-03 08:41 wcadump200908.xlsx
-rw-rw-r--. 1 brunson brunson  3141192 2009-09-04 00:51 wcadump200908.ods
-rw-rw-r--. 1 brunson brunson  2438745 2009-09-05 13:19 wca200902-mysqldump.sql.gz
-rw-rw-r--. 1 brunson brunson  1694590 2009-09-05 13:15 wca200902-mysqldump.sql.bz2
-rw-rw-r--. 1 brunson brunson  1350371 2009-09-05 13:20 wca200902-csv.tar.bz2
In decreasing order of compactness: Excel, OOCalc, mysqldump gzip -9'd, mysqldump bzip2 -9'd, tar -cjf of the CSV files along with SQL scripts I made to create the schema and load it.
 
Do we have the new file for September?

msemtd or brunson, do either of you have scripts that automatically import this into MySQL? I'm looking at implementing State Records.
 
After I save each sheet to individual CSV files I use this script to load the data into the database.

Code: 
use wca;
source schema.sql;

load data infile '/home/brunson/wca/results.csv' 
replace into table Results 
columns terminated by ',' optionally enclosed by '"' 
ignore 1 lines 
( id, pos, personId, personName, countryId, competitionId, 
  eventId, roundId, formatId, value1, value2, value3, value4, 
  value5, best, average, regionalSingleRecord, 
  regionalAverageRecord );

load data infile '/home/brunson/wca/competitions.csv' 
replace into table Competitions 
columns terminated by ',' optionally enclosed by '"' 
ignore 1 lines 
( id, name, cityName, countryId, information, year, month, 
  day, endMonth, endDay, eventSpecs, wcaDelegate, organiser, 
  venue, venueAddress, venueDetails, website, cellName, showAtAll, 
  showResults, password, showPreregForm, showPreregList, 
  latitude, longitude );

load data infile '/home/brunson/wca/persons.csv' 
replace into table Persons 
columns terminated by ',' optionally enclosed by '"' 
ignore 1 lines 
( id, subid, name, countryId, gender );

load data infile '/home/brunson/wca/rounds.csv' 
replace into table Rounds 
columns terminated by ',' optionally enclosed by '"' 
ignore 1 lines 
( id, rank, name, cellName );

load data infile '/home/brunson/wca/events.csv' 
replace into table Events 
columns terminated by ',' optionally enclosed by '"' 
ignore 1 lines 
( id, name, rank, format, cellName );

load data infile '/home/brunson/wca/formats.csv' 
replace into table Formats 
columns terminated by ',' optionally enclosed by '"' 
ignore 1 lines ( id, name );

load data infile '/home/brunson/wca/countries.csv' 
replace into table Countries 
columns terminated by ',' optionally enclosed by '"' 
ignore 1 lines 
( id, name, continentId, latitude, longitude, zoom );

load data infile '/home/brunson/wca/continents.csv' 
replace into table Continents 
columns terminated by ',' optionally enclosed by '"' 
ignore 1 lines 
( id, name, recordName, latitude, longitude, zoom );
The script references another script called schema.sql. It's just the creation statements from Ron's spreadsheet put into a single file:
Code: 
CREATE TABLE `Competitions` (
  `id` varchar(32) NOT NULL default '',
  `name` varchar(50) NOT NULL default '',
  `cityName` varchar(50) NOT NULL default '',
  `countryId` varchar(50) NOT NULL default '',
  `information` mediumtext,
  `year` smallint(5) unsigned NOT NULL default '0',
  `month` smallint(5) unsigned NOT NULL default '0',
  `day` smallint(5) unsigned NOT NULL default '0',
  `endMonth` smallint(5) unsigned NOT NULL default '0',
  `endDay` smallint(5) unsigned NOT NULL default '0',
  `eventSpecs` text NOT NULL,
  `wcaDelegate` varchar(240) NOT NULL default '',
  `organiser` varchar(200) NOT NULL default '',
  `venue` varchar(240) NOT NULL default '',
  `venueAddress` varchar(120) default NULL,
  `venueDetails` varchar(120) default NULL,
  `website` varchar(200) default NULL,
  `cellName` varchar(45) NOT NULL default '',
  `showAtAll` tinyint(1) NOT NULL default '1',
  `showResults` tinyint(1) NOT NULL default '1',
  `password` varchar(45) NOT NULL default '',
  `showPreregForm` tinyint(1) NOT NULL default '0',
  `showPreregList` tinyint(1) NOT NULL default '0',
  `latitude` int(11) NOT NULL default '0',
  `longitude` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Continents` (
  `id` varchar(50) NOT NULL default '',
  `name` varchar(50) NOT NULL default '',
  `recordName` char(3) NOT NULL default '',
  `latitude` int(11) NOT NULL default '0',
  `longitude` int(11) NOT NULL default '0',
  `zoom` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Countries` (
  `id` varchar(50) NOT NULL default '',
  `name` varchar(50) NOT NULL default '',
  `continentId` varchar(50) NOT NULL default '',
  `latitude` int(11) NOT NULL default '0',
  `longitude` int(11) NOT NULL default '0',
  `zoom` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `fk_continents` (`continentId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Events` (
  `id` varchar(6) NOT NULL default '',
  `name` varchar(54) NOT NULL default '',
  `rank` int(11) NOT NULL default '0',
  `format` varchar(10) NOT NULL default '',
  `cellName` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0;
CREATE TABLE `Formats` (
  `id` char(1) NOT NULL default '',
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Persons` (
  `id` varchar(10) NOT NULL default '',
  `subId` tinyint(6) NOT NULL default '1',
  `name` varchar(80) NOT NULL default '',
  `countryId` varchar(50) NOT NULL default '',
  `gender` char(1) NOT NULL default '',
  `year` smallint(6) NOT NULL default '0',
  `month` tinyint(4) NOT NULL default '0',
  `day` tinyint(4) NOT NULL default '0',
  `comments` varchar(40) NOT NULL default '',
  KEY `fk_country` (`countryId`),
  KEY `id` (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Results` (
  `id` int(11) NOT NULL auto_increment,
  `pos` smallint(6) NOT NULL default '0',
  `personId` varchar(10) NOT NULL default '',
  `personName` varchar(80) NOT NULL default '',
  `countryId` varchar(50) default NULL,
  `competitionId` varchar(32) NOT NULL default '',
  `eventId` varchar(6) NOT NULL default '',
  `roundId` char(1) NOT NULL default '',
  `formatId` char(1) NOT NULL default '',
  `value1` int(11) NOT NULL default '0',
  `value2` int(11) NOT NULL default '0',
  `value3` int(11) NOT NULL default '0',
  `value4` int(11) NOT NULL default '0',
  `value5` int(11) NOT NULL default '0',
  `best` int(11) NOT NULL default '0',
  `average` int(11) NOT NULL default '0',
  `regionalSingleRecord` char(3) default NULL,
  `regionalAverageRecord` char(3) default NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_tournament` (`competitionId`),
  KEY `fk_event` (`eventId`),
  KEY `fk_round` (`roundId`),
  KEY `fk_format` (`formatId`),
  KEY `fk_competitor` USING BTREE (`personId`)
) ENGINE=MyISAM AUTO_INCREMENT=66847 DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=66847 ;
CREATE TABLE `Rounds` (
  `id` char(1) NOT NULL default '',
  `rank` int(11) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `cellName` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
So is there really not just a table with severyone's best? I know we can calculate it on our own, but it seems like that could cut down on a lot of bandwidth, since many people don't need all the results.

The other thing it does is for situations where the average may be different from the calculated average. There's a case where the Square-1 scramble was incorrect, so the solve was replaced with a DNF, but the mean was the mean of the remaining two. I believe this has only happened once though.
 
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,985
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