Below I will describe a set or procedures to generate your own database from scratch. I do not believe this is better than the openfootball/build script but I believe it will help clarify any confusion for people that are unfamiliar with ruby and rake.
- Clone this repository
- run the following script
sh downloadAndBuild.sh - the sqlite db will be built at ``/temp/football.db`
- to repeat/refresh db just delete temp folder and run script again.
Requirements:
- git
- Ruby and RubyGems
- SQLite3
The new folder will hold the numerous open football repositories. For this example we will use "openfootball." Other instructions use a slightly different folder format. We will execute all commands below inside this directory.
mkdir openfootball
cd openfootball
We need to install the sportdb command line tool. We can easily do this with the Ruby package manager RubyGems. We need to install the gem sportdb. You can view source code on github.com/sportdb/sport.db.ruby Run the following:
gem install sportdb
Expected Ouput:
Successfully installed sportdb-1.9.2
Parsing documentation for sportdb-1.9.2
Done installing documentation for sportdb after 1 seconds
1 gem installed
If you already have sportdb installed you may want to ensure you have the latest with gem update sportdb Whenever the parser is changed (last change was for the addition of goals to the data template) you will need to update sportdb.
Now run the following to see your options:
sportdb --help
Run:
sportdb create
You will now see a new sqlite database file in your new directory called sport.db. sport.db is default filename. I will show how to specify a different name elsewhere. You can connect to the database with sqlite3.exe and browse the schema and tables.
From our openfootball dir run:
sqlite3 sport.db
// then in the sqlite prompt run:
sqlite> .tables
// the output will look something like this:
lltime_standing_entries games props
alltime_standings goals races
assocs grounds records
assocs_assocs group_standing_entries regions
badges group_standings rosters
cities groups rounds
continents groups_teams runs
countries langs seasons
event_standing_entries leagues taggings
event_standings logs tags
events names teams
events_grounds persons tracks
events_teams places usages
However, the tables are still empty. We can get a quick snapshot of the database by running sportdb stats too see a quick summary of table counts.
sportdb stat
// example output
0 leagues / 56 seasons
0 events (league+season recs) / 0 rounds / 0 groups
0 teams
0 games
0 badges
0 tracks / 0 races (track+event recs) / 0 runs
0 records (race|run+person recs)
0 rosters (person+team+event recs)
0 goals (person+game recs)
0 assocs|orgs
0 grounds|stadiums
Stats:
0 continents
0 countries (0 supras, 0 deps)
0 regions
0 metros
0 cities (0 metros)
0 districts
0 places
0 names
0 langs
0 usages
At this point we only have the ruby parser. We need to download the raw data stored in various github repos. It important where you put the repos and how you refrence them in the scripts. In this example I will be putting everything in the openfootball folder. However, if you are using the official rake file to build you will need to have the expected folder structure as described in this thread.
Our first required dependency is github.com/openmundi/world.db. It contains geographic information including country names and codes.
From inside the /openfootball folder run the following:
git clone git://github.com/openmundi/world.db.git
This creates a new folder in our repo called world.db. Now if we run the setup command we can setup the database with our world.db data.
sportdb setup --worldinclude world.db
This time in the output we can see our sportdb parser doing actual work. Here is a snippet of the logs
[info] parsing data 'pacific/nz-new-zealand/cities' (world.db/pacific/nz-new-zealand/cities.txt)...
[info] parsing data 'south-america/1-codes/fifa' (world.db/south-america/1-codes/fifa.yml)...
[info] parsing data 'south-america/1-codes/internet' (world.db/south-america/1-codes/internet.yml)...
Now run sportdb stat and you will see the new data included in the summary. I prefer to delete the existing database and start over. You will need to do this is you are using the create or setup commands. If you use the update command this is not required.
When including files you can use relative paths. Since I am using one folder I can keep this simple, otherwise I would need to have something like --worldinclude ./world.db
The below download.sh script will clone all the current data repos. If the repos already exist you will see errors. You can only clone a repo once. I usually delete the folders and re-download them when I want updated data.
Create download.sh and run sh download.sh
git clone https://github.com/openmundi/world.db.git
git clone https://github.com/openfootball/stadiums.git
git clone https://github.com/openfootball/players.git
git clone https://github.com/openfootball/national-teams
git clone https://github.com/openfootball/world-cup.git
git clone https://github.com/openfootball/euro-cup.git
git clone https://github.com/openfootball/africa-cup.git
git clone https://github.com/openfootball/north-america-gold-cup.git
git clone https://github.com/openfootball/copa-america.git
git clone https://github.com/openfootball/at-austria.git
git clone https://github.com/openfootball/de-deutschland.git
git clone https://github.com/openfootball/en-england.git
git clone https://github.com/openfootball/es-espana.git
git clone https://github.com/openfootball/it-italy.git
git clone https://github.com/openfootball/europe-champions-league.git
git clone https://github.com/openfootball/mx-mexico.git
git clone https://github.com/openfootball/br-brazil.git
git clone https://github.com/openfootball/north-america-champions-league.git
git clone https://github.com/openfootball/copa-libertadores.git
git clone https://github.com/openfootball/copa-sudamericana.git
Create build.sh and run sh build.sh
# delete the existing sqlite db
rm football.db
# Setup the database with world data
sportdb --dbname football.db setup --worldinclude world.db
sportdb --dbname football.db update --include 'stadiums'
sportdb --dbname football.db update --include 'players'
# World Cup is dependent on national-teams
sportdb --dbname football.db update --include 'national-teams'
sportdb --dbname football.db update --include 'world-cup'
sportdb --dbname football.db update --include 'euro-cup'
sportdb --dbname football.db update --include 'africa-cup'
sportdb --dbname football.db update --include 'north-america-goldf-cup'
sportdb --dbname football.db update --include 'copa-america'
sportdb --dbname football.db update --include 'world'
sportdb --dbname football.db update --include 'at-austria'
sportdb --dbname football.db update --include 'de-deutschland'
sportdb --dbname football.db update --include 'en-england'
sportdb --dbname football.db update --include 'es-espana'
sportdb --dbname football.db update --include 'it-italy'
sportdb --dbname football.db update --include 'europe-champions-league'
sportdb --dbname football.db update --include 'mx-mexico'
sportdb --dbname football.db update --include 'br-brazil'
sportdb --dbname football.db update --include 'north-america-champions-league'
sportdb --dbname football.db update --include 'copa-libertadores'
sportdb --dbname football.db update --include 'copa-sudamericana'
The order of updates can be important. See the rake build script for recommended order. https://github.com/openfootball/build/blob/master/settings.rb
Now check the stats for your football.db with
sportdb --dbname football.db stat
Example Output
41 leagues / 56 seasons
35 events (league+season recs) / 531 rounds / 63 groups
505 teams
3886 games
0 badges
0 tracks / 0 races (track+event recs) / 0 runs
0 records (race|run+person recs)
0 rosters (person+team+event recs)
136 goals (person+game recs)
0 assocs|orgs
200 grounds|stadiums
Stats:
10 continents
245 countries (1 supras, 48 deps)
446 regions
6 metros
1073 cities (365 metros)
0 districts
1780 places
0 names
0 langs
Discuss and Ask questions for project at Google Groups