Planet Smalltalk

CREATE TABLE IF NOT EXISTS "groups_teams" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, 
  "group_id" integer NOT NULL, 
  "team_id" integer NOT NULL, 
  "created_at" datetime NOT NULL,
  "updated_at" datetime NOT NULL
);

OFObject subclass: #OFGroup
  instanceVariableNames: 'eventId title pos teams'
  classVariableNames: ''
  package: 'OpenFootball'

classModelForOFGroup: aClassModel
  self virtualClassModelForOFObject: aClassModel.
  aClassModel newAttributeNamed: #eventId type: Integer.
  aClassModel newAttributeNamed: #title type: String.
  aClassModel newAttributeNamed: #pos type: Integer.
  "Next item is for linking OFGroup with OFTeam."
  aClassModel newAttributeNamed: #teams collectionOf: OFTeam.

descriptorForOFGroup: aDescriptor
  | t | 
  t := self tableNamed: 'GROUPS'.
  aDescriptor table: t.
  self virtualDescriptorForOFObject: aDescriptor with: t.
  (aDescriptor newMapping: DirectMapping)
    from: #eventId
    type: Integer
    to: (t fieldNamed: 'event_id').
  (aDescriptor newMapping: DirectMapping)
    from: #title
    type: String
    to: (t fieldNamed: 'title').
  (aDescriptor newMapping: DirectMapping)
    from: #pos
    type: Integer
    to: (t fieldNamed: 'pos'.
  "Next item is for linking OFGroup with OFTeam."
  (aDescriptor newMapping: ManyToManyMapping)
    attributeName: #teams.

"No change to #tableForGROUPS:."

tableForGROUPS_TEAMS: aTable
  | gid tid |
  self virtualTableForOFObject: aTable.
  gid := aTable createFieldNamed: 'group_id' type: platform integer.
  aTable addForeignKeyFrom: gid to: ((self tableNamed: 'GROUPS') fieldNamed: 'id').
  tid := aTable createFieldNamed: 'team_id' type: platform integer.
  aTable addForeignKeyFrom: tid to: ((self tableNamed: 'TEAMS') fieldNamed: 'id').

| vh |
Transcript clear.
OFDatabase dbFileName: 'wc2018.db'
  evaluate: [ :db |
    db session accessor logging: true. "This shows the generated SQL."
    vh := String streamContents: [ :str | 
      (db session read: OFGroup) do: [ :ea | 
        str nextPutAll: ea title; nextPut: Character cr.
        ea teams do: [ :team | 
          str nextPutAll: '- ', team title; nextPut: Character cr ]]]].
vh

Group A
- Egypt
- Russia
- Saudi Arabia
- Uruguay
<some output omitted>
Group H
- Senegal
- Japan
- Poland
- Colombia

SELECT t1.id, t1.created_at, t1.updated_at, t1.event_id, t1.title, t1.pos
 FROM GROUPS t1  an OrderedCollection()

SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
 FROM TEAMS t1, GROUPS_TEAMS t2
 WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?))  an OrderedCollection(1)

SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
 FROM TEAMS t1, GROUPS_TEAMS t2
 WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?))  an OrderedCollection(2)

SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
 FROM TEAMS t1, GROUPS_TEAMS t2
 WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?))  an OrderedCollection(3)

SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
 FROM TEAMS t1, GROUPS_TEAMS t2
 WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?))  an OrderedCollection(4)

SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
 FROM TEAMS t1, GROUPS_TEAMS t2
 WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?))  an OrderedCollection(5)

SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
 FROM TEAMS t1, GROUPS_TEAMS t2
 WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?))  an OrderedCollection(6)

SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
 FROM TEAMS t1, GROUPS_TEAMS t2
 WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?))  an OrderedCollection(7)

SELECT t1.id, t1.created_at, t1.updated_at, t1.key, t1.title
 FROM TEAMS t1, GROUPS_TEAMS t2
 WHERE ((t2.team_id = t1.id) AND (t2.group_id = ?))  an OrderedCollection(8)

| vh |
Transcript clear.
OFDatabase dbFileName: 'wc2018.db'
  evaluate: [ :db |
    | query |
    db session accessor logging: true.
    query := Query read: OFGroup.
    query alsoFetch: [ :ea | ea teams ]. " <== See me. "
    vh := String streamContents: [ :str | 
      (db session execute: query) do: [ :ea | 
        str nextPutAll: ea title; nextPut: Character cr.
        ea teams do: [ :team | 
          str nextPutAll: '- ', team title; nextPut: Character cr ]]]].
vh

SELECT t1.id, t1.created_at, t1.updated_at, t1.event_id, t1.title, t1.pos, 
       t2.id, t2.created_at, t2.updated_at, t2.key, t2.title
FROM GROUPS t1 
INNER JOIN GROUPS_TEAMS t3 ON (t1.id = t3.group_id) 
INNER JOIN TEAMS t2 ON (t3.team_id = t2.id) 
ORDER BY t1.id  an OrderedCollection()

CREATE TABLE IF NOT EXISTS "groups" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, 
  "event_id" integer NOT NULL, 
  "title" varchar NOT NULL, 
  "pos" integer NOT NULL, 
  "created_at" datetime NOT NULL, 
  "updated_at" datetime NOT NULL
);

CREATE TABLE IF NOT EXISTS "teams" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, 
  "key" varchar NOT NULL, 
  "title" varchar NOT NULL, 
-- many other columns omitted for now --
  "created_at" datetime NOT NULL, 
  "updated_at" datetime NOT NULL
);

Object subclass: #OFObject
  instanceVariableNames: 'pid createdAt updatedAt'
  classVariableNames: ''
  package: 'OpenFootball'

"Maps to GROUPS."
OFObject subclass: #OFGroup
  instanceVariableNames: 'eventId title pos'
  classVariableNames: ''
  package: 'OpenFootball'

"Maps to TEAMS."
OFObject subclass: #OFTeam
  instanceVariableNames: 'key title'
  classVariableNames: ''
  package: 'OpenFootball'

virtualClassModelForOFObject: aClassModel
  aClassModel newAttributeNamed: #pid type: Integer.
  aClassModel newAttributeNamed: #createdAt type: DateAndTime.
  aClassModel newAttributeNamed: #updatedAt type: DateAndTime.

virtualDescriptorForOFObject: aDescriptor with: aTable
  (aDescriptor newMapping: DirectMapping)
    from: #pid
    to: (aTable fieldNamed: 'id'). "This is the primary key mapping."
  (aDescriptor newMapping: DirectMapping)
    from: #createdAt
    type: DateAndTime
    to: (aTable fieldNamed: 'created_at').
  (aDescriptor newMapping: DirectMapping)
    from: #updatedAt
    type: DateAndTime
    to: (aTable fieldNamed: 'updated_at').

virtualTableForOFObject: aTable
  (aTable createFieldNamed: 'id' type: platform serial) bePrimaryKey.
  aTable createFieldNamed: 'created_at' type: platform datetime.
  aTable createFieldNamed: 'updated_at' type: platform datetime.

classModelForOFGroup: aClassModel
  self virtualClassModelForOFObject: aClassModel.
  aClassModel newAttributeNamed: #eventId type: Integer.
  aClassModel newAttributeNamed: #title type: String.
  aClassModel newAttributeNamed: #pos type: Integer.

descriptorForOFGroup: aDescriptor
  | t | 
  t := self tableNamed: 'GROUPS'.
  aDescriptor table: t.
  self virtualDescriptorForOFObject: aDescriptor with: t.
  (aDescriptor newMapping: DirectMapping)
    from: #eventId
    type: Integer
    to: (t fieldNamed: 'event_id').
  (aDescriptor newMapping: DirectMapping)
    from: #title
    type: String
    to: (t fieldNamed: 'title').
  (aDescriptor newMapping: DirectMapping)
    from: #pos
    type: Integer
    to: (t fieldNamed: 'pos'.

tableForGROUPS: aTable
  self virtualTableForOFObject: aTable.
  aTable createFieldNamed: 'event_id' type: platform integer.
  aTable createFieldNamed: 'title' type: platform varchar.
  aTable createFieldNamed: 'pos' type: platform integer.

OFDatabase 
  dbFileName: 'wc2018.db'
  evaluate: [ :db |
    db session read: OFGroup ]