I've been playing with the OpenFootball data using ROE. ROE provides RAArrayRelation, which shares a common superclass with RASQLRelation. As its name implies, RAArrayRelation uses an in-memory array object and does not involve any underlying SQL database.

In my OpenFootball data crunching, I tried JOINing instances of both. When the left side of the JOIN is the RASQLiteRelation instance, the operation threw an SQLite exception "no such table" because ROE expected the right side to also be an RASQLiteRelation on the same underlying database. When the left side is the RAArrayRelation instance, the operation sends #tuples to the right side, which resulted in a DNU. I've tried a few implementations of RASQLiteRelation>>tuples, but so far no success. Even if I get a working implementation, the implication of #tuples is that all matching rows from the SQLite database are loaded into memory, which is not always performant or feasible.

The reason for using an RAArrayRelation is to create a new relation unlike any existing one. The alternative is to create an RASQLiteRelation instance on a new, empty table. However, ROE does not support creating tables in the underlying database. Of course, I can use the given database connection to CREATE TABLE via direct SQL, but that doesn't feel satisfying.

I've committed my integration of ROE with NBSQLite3 to SmalltalkHub.

Tested on Pharo 3 on OSX Mountain Lion and Linux Mint 17 based on Ubuntu 14.04. All 23 tests of RATestSQLiteSemantics pass.

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

ROE is "a library for manipulating relational queries as first class Smalltalk expressions, and generating SQL from them."

Not bad for an afternoon's hacking, now ROE works with NBSQLite3 and all tests pass.

(Yes, I notice these screenshots are of rather poor quality.)