News and Updates

Inspired by the datasette {db,dbf,markdown,geojson,shapefile}-to-sqlite series added the missing football-to-sqlite tool that - surprise, surprise - lets you load / read football.txt match datafiles (e.g. English Premier League, German Bundesliga, Spanish La Liga 2020/21, etc.) into a sqlite database.

Usage:

Run this tool against match files in the Football.TXT format like so:

$ football-to-sqlite england.db 2020-21\1-premierleague.txt

or pass in more than one match file:

$ football-to-sqlite england.db 2020-21\1-premierleague.txt \
                                2020-21\2-championship.txt \
                                2020-21\3-league1.txt \
                                2020-21\4-league2.txt \
                                2020-21\5-nationalleague.txt

Note: If the single-file SQLite database (and its tables, views & indices) do not (yet) exist, they get auto-created on the first run.

Note: You can use football2sqlite as an alias / alternate name.

Pipes & Standard Input (STDIN)

You can use any command line tool to download match files and pipe (via stdin) into this tool like so:

$ curl https://raw.githubusercontent.com/openfootball/world-cup/master/2018--russia/cup.txt
| football-to-sqlite worldcup.db

Enjoy the beautiful game.

Reorganized the football.db datasets a little. The /brazil country repo is now /south-america and includes datasets from Argentina and more countries upcoming.

The /france country repo is now /europe and includes all datasets from Europe (e.g. Belgium, Netherlands, Ireland, Iceland, Scotland, etc.) from the /world repo.

Enjoy the beautiful game.

A first little experiment using GitHub Actions for more automation. The football.csv datasets for

• /england
• /deutschland
• /espana
• /austria
• /belgium
• /mexico

now get auto-updated every 24 hours at 5 am (UTC) thanks to GitHub Actions from the (single-source) datasets in the (structured) text format also known as Football.TXT.

Find the source for the workflow update/build machinery in the yorobot/workflow repo or see the actions in action.

Enjoy the beautiful game.

Added the match schedules for the top 5 leagues for the new 2020/21 season:

• English Premier League 2020/21
• Deutsche Bundesliga 2020/21
• Spanish Primera División 2020/21
• Italian Serie A 2020/21
• French Ligue 2020/21

See the /2020-21 folder in the country repos / datasets. Enjoy the beautiful game.

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.

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.

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.

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

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.

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.

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

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.

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.

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.

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' ] 
...

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-
...

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.

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.

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) 

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)

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)
... 

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']
...