Update: New Season Match Schedules - English Premier League 2017/18, Deutsche Bundesliga 2017/18, Italian Serie A 2017/18, Spanish Primera División 2017/18 and More

News and Updates

football.db - League Quick Starter Sample - Mauritius Premier League - Create Your Own Repo/League(s) from Scratch

Added a new quick starter sample using the Mauritius Premier League to get you started creating your own leagues/cups/etc. from scratch.

You can test drive the quick starter sample with a single command e.g.:

$ sportdb build 

To start from scratch follow these six steps:

  • Step 1: Add all leagues
  • Step 2: Add all clubs
  • Step 3: Add all match fixtures and results
  • Step 4: Add the league season “front matter” settings
  • Step 5: Add a setups file list (also known as manifest)
  • Step 6: Add a datafile build script - That’s it. Done.

Using a file structure like:

  ├── 2014-15              # 2014-15 season folder 
  |   ├── league-i.txt     #   match fixtures / results - matchdays  1-18 
  |   ├── league-ii.txt    #                            - matchdays 19-36 
  |   └── league.yml       #   "front matter" settings 
  ├── setups 
  |   └── all.txt          #   file list (manifest) 
  ├── leagues.txt          # all leagues 
  ├── clubs.txt            # all clubs 
  └── Datafile             # build script 

That’s it. Now try:

$ sqlite3 sport.db 

SQLite version 3.7.16 
Enter ".help" for instructions 
Enter SQL statements terminated with a ";" 

sqlite> select * from countries; 
1|Mauritius|mauritius|mu|1|MUS|||1261200|2040|||f|t|f|f| 

sqlite> select * from teams; 
1|joachim|Cercle de Joachim||CDJ|Cercle de Joachim SC|Joachim|1||t|||||f| 
2|chamarel|Chamarel SC||CHA|Chamarel|Chamarel Sport Club|1||t|||||f| 

and so on and so forth.

football.db - New 2015/16 Seasons - English Premier League, Bundesliga, And More

The plain text football fixtures for leagues, teams, match schedules, stadiums, and more that you can read with the sportdb command line tool into your SQL database of choice now includes the 2015/16 seasons for the English Premier League, the Deutsche Bundesliga, the Italian Serie A, the Spanish Primera División and more. Any league missing? Add your league.

Use Datafiles to build youself a copy e.g.

$ sportdb new en2015-16     # just the 2015-16 season, please
$ sportdb new en            # all seasons

All data, code and samples public domain. Enjoy the beautiful game. Cheers.

Added Open Football News & Updates Feed - `/atom.xml`

Updated the News & Updates section on the football.db - Open Football website. Now you can subscribe to the news feed. Thanks to news syndication via web feeds you can now find the football.db News on the Planet Open Data, for example.

Added `football.db` Quick Starter Datafile Templates - `$ sportdb new `

The sportdb command line tool now includes a new quick starter template command to (auto-)download Datafile scripts from the new openfootball/datafile registry.

For example, to build yourself a copy of the worldcup2014.db type:

$ sportdb new worldcup2014 

The new command will run these steps:

  • Step 1: Download worldcup2014.rb Datafile (from GitHub) to your working folder as ./Datafile
  • Step 2: Run the sportdb build command
    • Step 2.a: Download all datasets listed in the Datafile as zip archives (from GitHub) to ./tmp
    • Step 2.b: Create the “empty” database, that is, table structure, indexes, etc. (schema)
    • Step 2.c: Read in all datasets from the zip archives in ./tmp (no need to unpack)

That’s it. All done and setup with a single command. Still early and rough.

PS: The first quick starter Datafile templates include:

  • worldcup => All World Cups 1930-2014
  • en => English Premier Leagues
  • en2014-15 => English Premier League 2014/15
  • cl2014-15 => (European) Champions League 2014/15

New `football.db` Build System - Welcome `./Datafile`

The sportdb command line tool includes a new build system (as a new and easier all-in-one option).

To try it - use the new Datafile - a mini language a.k.a. domain-specific language (DSL) that lets you setup new football.dbs in minutes.

For example, to setup a football.db for the World Cup 2014 use:

./Datafile:

world 'openmundi/world.db', setup: 'countries' 

football 'openfootball/national-teams' 
football 'openfootball/world-cup', setup: '2014' 

Now run

$ sportdb build 

The new build command will look for the ./Datafile script in your working folder and

  • Step 1) Download all datasets as zip archives (from GitHub) to ./tmp
  • Step 2) Create all database tables
  • Step 3) Read/import all datasets from the zip archives in ./tmp (no need to unpack)

That’s it. Still early and rough.

New Tool - `sportweb` - Instant Open Sports Web Browser (Server) e.g. Use `$ sportweb`

Bundled up the sport.db.admin web app into an easy-to-use command line tool for (local) use on your own machine. How does it work?

Let’s say you have an SQLite database - e.g. football.db - in your folder, than just type

$ sportweb 

and that’s it - open your browser (e.g. use localhost:3000) and start browsing your local SQLite database.

How to install the machinery?

It’s a Ruby gem (Source), thus, type:

$ gem install sportweb 

That’s it.

`football.db` SQL Queries Made Easy (Thanks to Views) e.g. `select * from games_list;`

First thanks to Joe Kampschmidt for getting started w/ some SQL queries examples.

Added some SQL views to make it easier to query the football.db. Example - List World Cup Brazil 2014 Games:

Before (Table Edition):

SELECT t1.title, 
       t2.title, 
       g.* 
  FROM games g 
       LEFT OUTER JOIN teams t1 ON t1.id = g.team1_id 
       LEFT OUTER JOIN teams t2 ON t2.id = g.team2_id 
       INNER JOIN rounds r ON r.id = g.round_id 
       INNER JOIN events e ON e.id = r.event_id 
 WHERE e.key = 'world.2014' 

After (View Edition):

SELECT * 
  FROM events_games_list 
 WHERE e.key = 'world.2014'; 

More views include:

  • teams_list
  • games_list
  • events_list
  • events_teams_list
  • events_games_list
  • events_rounds_list
  • events_groups_list

New Repo - `/docs` - for Documentation (Articles, Tips & Tricks, Notes, Examples, etc.)

Added a new repo, that is, /docs - to collect all the documentation in one place and make it easier to contribute. Still in an early stage the first pages include:

  • Intro - football.db - What? Why?
  • SQL Queries Examples
  • HTTP JSON API Intro

Thanks again to Joe Kampschmidt for the first docu contribution, that is, notes on a couple of SQL query examples for the world cup.

sportdb Update v1.9.4 - Adds `clubs.txt`, Week 1 Round Headers, `en/2013-14/pl.txt`, etc.

What’s news? sportdb command line tool updates:

  • Added clubs.txt as an alternative for teams.txt (recommended for football clubs otherwise clubs might get flagged as national teams and not as clubs).
  • Added 2013-14 as an alternative for 2013_14 in your fixture path (e.g. 2013-14/1-premierleague-i.txt now works).
  • Added Week 1 or Week 5 and so on in your match schedule (in English/en) for matchday/round headers.

New Org - `football.csv` - Football Match Data in CSV (Comma-Separated Values) Format e.g. `Arsenal, Liverpool, 2-0, 1-0`

Added a new GitHub org, that is, football.csv, that collects football match data in the CSV (comma-separated values) format for easy (re)use and started adding all seasons for the English Premier League, the Football League (Championship, League One, League Two), etc. to the new /en-england repo.

New Country Repo - `/ch-confoederatio-helvetica` - for Switzerland incl. Super League 2014/15 etc.

Added a new country repo, that is, /ch-confoederatio-helvetica - for Switzerland (Schweiz, Suisse, Svizzera, Svizra) incl. Lichtenstein. Added the Super League Match Schedule for 2014/15. Example:

Spieltag 1 

[Sa 19.7.] 
  17.45   FC Aarau       1:2 (0:2)  FC Basel             @ Brügglifeld, Aarau
            [Schultz 85'; Embolo 15' Aliji 38'] 
  20.00   FC St. Gallen  2:2 (1:1)  BSC Young Boys       @ AFG Arena, St. Gallen
              [Cavusevic 14' Gajic  52' (ET); Kubo 23' Afum 90' ] 
...

New Season - English Premier League 2014/15 Schedule (and Some Club Squads e.g. `arsenal.txt`) Added

Added the new 2014/15 season for the English Premier League plus started adding the squad players for Arsenal, as an example:

(1)     Wojciech Szczęsny (POL)    GK  2007-

(3)     Kieran Gibbs               DF  2007-
(4)     Per Mertesacker (GER)      DF  2011-
(5)  (c) Thomas Vermaelen (BEL)    DF  2009-
(6)     Laurent Koscielny (FRA)    DF  2010- 
(18)    Nacho Monreal (ESP)        DF  2013- 
(25)    Carl Jenkinson             DF  2011- 

(7)     Tomáš Rosický (CZE)        MF  2006-  
(8)     Mikel Arteta (ESP)         MF  2011-
(10)    Jack Wilshere (ENG)        MF  2008-
(11)    Mesut Özil (GER)           MF  2013-
(16)    Aaron Ramsey (WAL)         MF  2008-
(19)    Santi Cazorla (ESP)        MF  2012-
(20)    Mathieu Flamini (FRA)      MF  2013-
(24)    Abou Diaby (FRA)           MF  2006-
(35)    Gedion Zelalem (GER)       MF  2013-
        Francis Coquelin (FRA)     MF  2008-

(9)     Lukas Podolski (GER)       FW  2012-
(12)    Olivier Giroud (FRA)       FW  2012-
(14)    Theo Walcott               FW  2006-
(15)    Alex Oxlade-Chamberlain    FW  2011-
(17)    Alexis Sánchez (CHI)       FW  2014-
...

sportdb Update v1.9.3 - Added Better Squads Reader (Auto-Creates Missing Players)

What’s news? sportdb command line updates:

The squads (rosters)reader will now auto-add missing players. Try it, for example, using the World Cup 1930 squads e.g.:

$ rake update DATA=history     # reads all world cups from 1930 to 2010 

Squads get added to the rosters join table (one record per player) and missing players get auto-added to the persons table. Still a little rough and early stage.

New Repo - `/build-shell` - for Building your own `football.db` Copy using Shell Scripts

Added new repo, that is, /build-shell for building your own up-to-date football.db copy from the latest datasets using shell scripts. Thanks to Joe Kampschmidt to get it all started and the write-up and the shell script.

New Command Line Tool - `ojogo` gem - Who's Playing Today?

Added new command line tool, that is, ojogo that prints today’s matches for the World Cup 2014 in Brazil using the football.db HTTP JSON API service. For example, typing on Jun/29

$ ojogo    # defaults to today's world cup 2014 matches 

will print

Netherlands (NED) vs Mexico (MEX) 
Costa Rica (CRC) vs Greece (GRE) 

sportdb Update v1.9.2 - Added Match Goals Reader e.g. `[Messi 3', 45+1']` etc.

What’s news? sportdb command line updates:

The match schedule reader now will add goals (plus auto-add missing players). All still a little rough but working without any (extra) configuration. Example:

(1) Thu Jun/12 17:00   Brazil 3-1 (1-1) Croatia       @ Arena de São Paulo, São Paulo
        [Neymar 29', 71' (pen.) Oscar 90+1';  Marcelo 11' (o.g.)] 

The reader will add four goal records (see the table goals w/ fields such as player_id, team_id, minute, offset, score1, score2 etc.):

1 |   Marcelo  11'   (o.g.)   1-0   Croatia (Team 2) 
2 |   Neymar   29'            1-1   Brazil  (Team 1) 
3 |   Neymar   71'   (pen.)   2-1   Brazil  (Team 1) 
4 |   Oscar    90'+1'         3-1   Brazil  (Team 1)

New Org - `wikiscript` - Open Data Scripts for Wikipedia (e.g `football.squads.ruby` - World Cup 2014 Squads Update)

Added a new GitHub org, that is, wikiscript that collects scripts that let you turn free-style or semi-structured wiki text into open structured data.

A first example is the new football squads reader that reads in a wikitext page e.g


{{nat fs player|no=1|pos=GK|name=[[Jefferson de Oliveira 
Galvão|Jefferson]]|age={{Birth date and 
age2|2014|6|12|1983|1|2|df=y}}|caps=9|club=[[Botafogo de Futebol e 
Regatas|Botafogo]]|clubnat=BRA}} 
{{nat fs player|no=2|pos=DF|name=[[Dani Alves]]|age={{Birth date and 
age2|2014|6|12|1983|5|6|df=y}}|caps=74|club=[[FC 
Barcelona|Barcelona]]|clubnat=ESP}} 
...

and outputs all squads files in the football.db format:

(1)  GK   Jefferson        #   9, Botafogo (BRA)
(2)  DF   Dani Alves       #  74, Barcelona (ESP)
... 

New Org - `rsssf` - RSSSF (Rec.Sport.Soccer Statistics Foundation) Archive Data Imports for `football.db`

Added a new GitHub org, that is, rsssf - hosting RSSSF archive data that you can read into your SQL database of choice (SQLite, PostgreSQL, etc.). Still early and a little rough - as always expect surprises.

What’s the RSSSF (Rec.Sport.Soccer Statistics Foundation)?

The RSSSF collects and offers football league tables from all over the world in plain text. Example:

Round 1
[May 25]
Vasco da Gama   1-0 Portuguesa
 [Carlos Tenório 47']
Vitória         2-2 Internacional
 [Maxi Biancucchi 2', Gabriel Paulista 11'; Diego Forlán 29', Fred 63']
Corinthians     1-1 Botafogo
 [Paulinho 73'; Rafael Marques 24']
... 

Edit Me! This website is a public GitHub repo. Please help by forking the project and adding to it.