Alternate way to build your own football.db sqlite database
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.
The Short Answer
- Clone this repository
- run the following script
- the sqlite db will be built at ``/temp/football.db`
- to repeat/refresh db just delete temp folder and run script again.
The Long Answer (how it works)
- Ruby and RubyGems
1) Create a new folder
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
2) Install sportdb tool
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
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.
3) Working with the sportdb command line tool
Now run the following to see your options:
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
4) Downloading the raw data from github
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)...
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
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
5) Putting it all together. Download and build the entire football.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
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
# 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
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