football.db is a "free open public domain football (soccer) database". Instructions to construct an SQLite data file from the data are on the website. The database consists of 40 tables; its schema looks normalized. With the World Cup 2014 and dependent data, the file weighs in at ~1MB.
Let's see what we can do with the data using ROE.
Each league/tournament is given an event ID; the World Cup's event_id is 8.
| conn groups teams wcTeams |
conn := NBSQLite3BaseConnection on: ('/home/pierce/data/openfootball/sport.db').
conn open.
groups := (((RASQLiteRelation name: 'groups' connection: conn)
where: #event_id equals: 8)
projectAll: #(id title))
renameAll: #(id title) to: #(gid gTitle).
teams := (RASQLiteRelation name: 'teams' connection: conn)
renameAll: #(id title) to: #(tid tTitle).
wcTeams := ((RASQLiteRelation name: 'groups_teams' connection: conn) * groups
whereEqual: #(group_id gid)).
((wcTeams * teams whereEqual: #(team_id tid))
projectAll: #(gTitle tTitle)) explore.
The above code snippet results in a RAProjection instance. We can see its content by the following (pretty-printed by hand):
(self collect: [ :ea | ea values asArray ]) asArray
#(#('Group A' 'Brazil')
#('Group A' 'Croatia')
#('Group A' 'Mexico')
#('Group A' 'Cameroon')
#('Group B' 'Spain')
#('Group B' 'Netherlands')
#('Group B' 'Chile')
#('Group B' 'Australia')
#('Group C' 'Colombia')
#('Group C' 'Greece')
#('Group C' 'Côte d''Ivoire')
#('Group C' 'Japan')
#('Group D' 'Uruguay')
#('Group D' 'Costa Rica')
#('Group D' 'England')
#('Group D' 'Italy')
#('Group E' 'Switzerland')
#('Group E' 'Ecuador')
#('Group E' 'France')
#('Group E' 'Honduras')
#('Group F' 'Argentina')
#('Group F' 'Bosnia-Herzegovina')
#('Group F' 'Iran')
#('Group F' 'Nigeria')
#('Group G' 'Germany')
#('Group G' 'Portugal')
#('Group G' 'Ghana')
#('Group G' 'United States')
#('Group H' 'Belgium')
#('Group H' 'Algeria')
#('Group H' 'Russia')
#('Group H' 'South Korea'))
(Ivory Coast's proper name in the output doesn't look right in Pharo. The SQLite database is correct though.)
Tags: ROE, SQLite