I've started to update Glorp and GlorpSQLite for Pharo 8. This post lists the stuff to be handled.

Changes in Pharo

First, changes in Pharo from version to version. Glorp's TimedProxyReaper uses a weak-valued dictionary to hold TimedProxy instances. In Pharo 6, WeakValueDictionary>>at:put: essentially does the following:

WeakValueAssociation key: key value: anObject

In Pharo 7, that became:

WeakValueAssociation key: key value: anObject asSetElement

This required TimedProxy to implement #asSetElement.

In Pharo 8, #asSetElement is deprecated in favour of #asCollectionElement.

WeakValueAssociation key: key value: anObject asCollectionElement

So TimedProxy now also needs #asCollectionElement.

New SQLite Binding

The Pharo community has consolidated around Pharo-SQLite3 as the definitive SQLite binding going forward. GlorpSQLite uses the now-legacy UDBC-SQLite binding currently. This change should be straightforward.

Glorp Changes

Todd Blanchard has been working on Ruby on Rails-style ActiveRecord for Glorp, and testing the changes with PostgreSQL.

CI

With independently evolving drivers for SQLite, PostgreSQL and MySQL, and the ActiveRecord work changing Glorp itself, the time has come to set up CI for Glorp.

GlorpSQLite works on Pharo 7!

Take a fresh Pharo 7 alpha image; as of yesterday's download that is 5f13ae8. Launch it and run the following snippet in a Playground:

Metacello new
  baseline: 'GlorpSQLite';
  repository: 'github://PierceNg/glorp-sqlite3:pharo7dev';
  load.

Run the Glorp tests in TestRunner. The result should be green, with all 891 tests passed and 12 tests skipped. The database file is sodbxtestu.db in your image directory. Tested on 32- and 64-bit Ubuntu 18.04.

This is the second post in a short series on the topic. The last post looked at the tables GROUPS and TEAMS in the OpenFootball relational database schema. There is also the table GROUPS_TEAMS, usually known as a link table, which, ahem, "relates" the GROUPS and TEAMS table. GROUPS_TEAMS has the following schema:

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

A row in GROUPS_TEAMS with group_id of XXX and team_id of YYY means that the team represented by team_id YYY is in the group with group_id XXX.

Let's modify the Smalltalk class OFGroup to handle the linkage, by adding the inst-var 'teams' and creating accessors for it.

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

Next, modify the mapping for OFGroup in OFDescriptorSystem:

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

It is now necessary to add the table GROUPS_TEAMS to OFDescriptorSystem:

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

Now let's fetch the OFGroup instances with their linked OFTeam instances.

| 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

The above snippet produces the following output:

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

In the snippet, logging is enabled, and the SQL generated by Glorp is displayed in the Transcript (with whitespace inserted for readability). What we see is the infamous "N+1 selects problem" in action - the first SELECT fetches the GROUPS rows, then, for each group_id, there is a corresponding SELECT to fetch the TEAMS rows.

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)

Fortunately Glorp is cleverer than this, and provides a way to avoid the N+1 problem, by using the message #alsoFetch:.

| 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

Same output as before, but this time the SQL (pretty-printed by hand for readability) is much shorter and properly takes advantage of the SQL language.

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

Using OpenFootball-Glorp for illustration, this post is the first in a series on mapping an existing normalized database schema and other fun Glorp stuff. As usual, I'm using SQLite for the database.

Consider the tables GROUPS and TEAMS.

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

As it happens, every table in OpenFootball has columns "id", "created_at" and "updated_at", where "id" is that table's primary key. Let's take advantage of Smalltalk's inheritance and class hierarchy to map these columns and tables:

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'

By convention, the Glorp mapping is encapsulated in the class OFDescriptor, which has these supporting methods:

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.

The mapping for OFGroup is as follows:

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.

The mapping for OFTeam is similar and I've not shown it here for brevity.

To round out the scene setting, OFDatabase, the "database interface" class, has class-side convenience methods to run snippets like so:

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

To be continued...

I last wrote about football.db in a Jul 2014 blog post. Four years have gone by, and the World Cup is here again. This time around, I've started building a Glorp descriptor system for the World Cup data.

football.db's data is described in YAML files which are assembled into SQLite databases using tools written in Ruby. From the constructed 2014 and 2018 World Cup databases, I've created SQL dumps and placed them in the repo. To get an SQLite database from the 2018 SQL dump file:

% sqlite3 wc2018.db < wc2018.sql

In its current state, footballdb-Glorp allows querying the initial World Cup first round group membership.

| login sess |
login := Login new
  database: UDBCSQLite3Platform new;
  host: '';
  port: '';
  username: '';
  password: '';
  databaseName: '/tmp/wc2018.db';
  yourself.
sess := OFDescriptor sessionForLogin: login.
sess login.
[   Transcript clear.	
    (sess read: OFTournamentTeam) do: [ :ea |
      Transcript show: ea group title , ' - ', ea team title; cr ]
] ensure: [ sess logout ].

This Glorp descriptor may not be completed in time for this World Cup which is starting in a few days, but will be in time for the next one for sure! :-) Load it thusly:

Metacello new 
  repository: 'github://PierceNg/footballdb-Glorp:master/repo';
  baseline: 'OpenFootball';
  load.

From its documentation, SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

I've added Boolean handling to UDBC-SQLite. When writing to an SQLite database from Pharo, true is written as integer 1 and false as integer 0. SQLite uses dynamic typing, and any column in an SQLite database, except an INTEGER PRIMARY KEY column, may be used to store a value of any type, irrespective of the column's type declaration. As such, when writing Boolean values to a database, UDBC-SQLite does not check the database's type declaration.

When reading an SQLite database, UDBC-SQLite does check a column's type declaration: If a column is Boolean, UDBC-SQLite reads 1 as true, 0 as false, NULL as nil, and any other integer values raises an exception. I've encountered real world data where the string "t" means true and "f" means false for a Boolean column, so UDBC-SQLite handles these cases too.

Glorp has been similarly updated. Loading GlorpSQLite, from my development fork for now, installs both UDBC-SQLite and Glorp:

Metacello new
  repository: 'github://PierceNg/glorp-sqlite3';
  baseline: 'GlorpSQLite';
  load.

All Glorp unit tests should pass. Tested on Linux using fresh 32- and 64-bit 60540 images.

I have started a booklet on Pharo, hopefully the first of, um, more than one. It is entitled RedditSt20, on my fork and extension of Sven Van Caekenberghe's excellent "Reddit.st in 10 elegant classes", to cover the following in another 10 or so classes:

• GlorpSQLite
• Seaside-MDL
• username/password authentication
• logging
• 2-factor authentication

The book is hosted on Github. Source code is on Smalltalkhub.

The book is being written using Pillar, of course. Note that the Pharo 5 version of Pillar that I downloaded from InriaCI doesn't work - the supporting makefiles aren't able to obtain the output of "./pillar introspect ". Use the Pharo 6 version.

I've updated ConfigurationOfGlorp for Pharo 6 and added catalog methods to ConfigurationOfGlorpSQLite.

Take a fresh Pharo 60365 image, the latest as of yesterday's download. Launch it, open the Catalog Browser, and install GlorpSQLite from there.

Run the Glorp tests in TestRunner. The result should be green, with all 889 tests passed and 12 tests skipped. The database file is sodbxtestu.db in your image directory.

(On Pharo 5, Glorp runs and passes total of 953 tests. Something to look into.)

I've created ConfigurationOfGlorpSQLite on STH.

Take a fresh Pharo 5 image. Make a script like the following and run it on the image:

% cat loadGlorpSQLite.sh
#!/bin/sh
MCREPO=http://www.smalltalkhub.com/mc/DBXTalk/Glorp/main/
pharo $1.image config $MCREPO ConfigurationOfGlorpSQLite --install=stable

% ./loadGlorpSQLite.sh Pharo-50757

When done, fire up the image again, and run the Glorp tests in TestRunner. The result should be green, with all 953 tests passed and 12 tests skipped. The database file is sodbxtestu.db in your image directory.

I'm pleased to announce the release of Glorp-SQLite3 for Pharo 5.

Developed and tested on Linux. Known working on Windows 7. Your Pharo 5 VM needs to be able to find libsqlite3.so or the Windows equivalent.

Take a fresh Pharo 5 image. Run the following:

Gofer it
    smalltalkhubUser: 'TorstenBergmann' project: 'UDBC';
    configuration;
    load.
(Smalltalk at: #ConfigurationOfUDBC) loadBleedingEdge.

Gofer it
    smalltalkhubUser: 'DBXTalk' project: 'Glorp';
    configurationOf: 'Glorp';
    load.
#ConfigurationOfGlorp asClass project stableVersion load.

Gofer it
    smalltalkhubUser: 'DBXTalk' project: 'Glorp';
    package: 'Glorp-SQLite3';
    load.

GlorpSQLite3CIConfiguration new configureSqlite3.
GlorpDemoTablePopulatorResource invalidateSetup.

Run the Glorp tests in TestRunner. All tests should pass, with 12 tests skipped. The database file is sodbxtestu.db in your image directory.

Using the Pharo v40592 image with which I had verified NBSQLite3 for Glorp, in this blog post I go through doing the same with the PostgresV2 pure-Smalltalk database driver.

Outside of Smalltalk, create the database 'sodbxtest', user 'sodbxtest' with password 'sodbxtest':

# su postgres -c psql
postgres=# create role sodbxtest with password 'sodbxtest' login;
CREATE ROLE 
postgres=# create database sodbxtest;
CREATE DATABASE
postgres=# \q
#

In Smalltalk, firstly, install PostgresV2:

Gofer it
    smalltalkhubUser: 'PharoExtras' 
    project: 'PostgresV2';
    package: 'ConfigurationOfPostgresV2';
    load.
((Smalltalk at: #ConfigurationOfPostgresV2) project version: '2.4') load.

Open Test Runner and runs the PostgresV2 tests. On my Linux Mint machine, using a vanilla PostgreSQL 9.3 installation, 23 of 24 tests passed, and TestPGConnection>>#testNotify2 erred.

Now that we know the PostgresV2 driver can talk to our database, using the Monticello browser, open the PostgresV2 repository and load the package GlorpDriverPostgreSQL. Here I had to edit NativePostgresDriver>>connectionArgsFromCurrentLogin: to comment out the second last line:

connectionArgs clientEncoding: aLogin encodingStrategy asSymbol

This is because GlorpDatabaseLoginResource class>defaultPostgreSQLLocalLogin does not specify encodingStrategy, meaning it is nil and will respond to #asSymbol with DNU.

Next, in a playground, execute the following:

GlorpDemoTablePopulatorResource invalidateSetup.
GlorpDatabaseLoginResource
    defaultLogin: GlorpDatabaseLoginResource defaultPostgreSQLLocalLogin

Open Test Runner and run the Glorp tests.

Tested on Linux Mint 17.

I've integrated NBSQLite3 into Glorp on the current Pharo 4.0 v40592 beta image.

Firstly, install NBSQLite3 (TorstenBergmann.7) and then Glorp (TorstenBergmann.42) from the config browser.

Then, using the Monticello browser, open the NBSQLite3 repository and load the packages NBSQLite3-Glorp and NBSQLite3-Test-Glorp. Next, open the Glorp repository and load the packages Glorp-PierceNg.97.mcz and GlorpTests-PierceNg.44.mcz.

In a workspace/playground, execute the following:

GlorpDemoTablePopulatorResource invalidateSetup.
GlorpDatabaseLoginResource
    defaultLogin: GlorpDatabaseLoginResource defaultNBSQLite3LocalLogin

Open Test Runner and run the Glorp tests.

Tested on Linux Mint 17 and OSX Mavericks. 2 fewer tests passed on Linux.

Curiously, #testLargeBlob, #testLargeClob and #testLargeText passed on the Pharo 3 image that I wrote this code on.

The database file created by the tests is sodbxtest.db.

% sqlite3 sodbxtest.db
sqlite> .tables
AIRLINE                  GALLERY_LINK             PERISHABLE_ITEM        
AIRLINE_MEAL             GLORP_IMAGE              PERSON                 
ATTACHMENT               GLORP_IMAGE_FILE         POULTRY                
ATTACHMENTBYTES          GLORP_JOB                PUBLISHER_EMP          
BANK_ACCT                GLORP_OWNER              PUBLISHER_TITLE        
BANK_TRANS               GLORP_SLAVE              PUBLISHER_TITLE2       
BOOK                     GLORP_TAG                PUB_EMP_LINK           
BOOK_CUSTOMER            GLORP_TAGS               PUB_TITLES_STOCK       
COMPRESSED_MONEY_TABLE   GLORP_WORKER             PUB_TITLE_LINK         
CUSTOMER_ACCT_LINK       GLORP_WORKER_JOB_LINK    RECORD_WITH_UPDATE     
CUSTOMER_BOOK_LINK       GR_ADDRESS               RESERVATION            
DEFAULTABLE_THING        GR_CUSTOMER              STUFF                  
DOCUMENT                 GR_FOLDER                TAX                    
EMAIL_ADDRESS            GR_MESSAGE               TRANSFORMED_TIME       
EMPLOYEE                 GR_PUBLISHER             TREE_NODE              
ENCYC                    GR_THINGONE              TREE_NODE_LINK         
ENCYC_BIO                GR_THINGWITHCOLLECTIONS  UNASSEMBLED_ITEM       
ENCYC_ENTRY              GR_THING_LINK            VIDEO_CREDIT_STATUS    
ENCYC_ENTRY_LINK         GR_TRAVEL_AGENT          VIDEO_PURCHASE         
FKADDRESS                GR_USER                  VIDEO_PURCHASE_LINK    
FKCONTACT                IMAGETAGS                VIDEO_RENTAL           
FLIGHT                   ITINERARY                VIDEO_STORE            
FLIGHT_PASS              NONPERISHABLE_ITEM       WAREHOUSE              
FREQUENT_FLYER           OFFICE                   WAREHOUSE_ITEM_LINK    
GALLERY                  PASSENGER                WORKING_STIFF          
sqlite> 

Made really good progress with NBSQLite3 for Glorp.

On the failed tests:

The entire GlorpOptimisticLockingTest and GlorpTimestampTest suites are skipped, because some of the tests fail, and foobars the Pharo-SQLite interface, causing many subsequent tests to fail, requiring restart of the Pharo image. Still need to look into these.

Making good progress with NBSQLite3 for Glorp.

Sven van Caekenberghe has written a very nice tutorial implementing a Reddit clone in Pharo using Seaside, Glorp and PostgreSQL. Sven also makes available a prebuilt image containing the application.

Seeing that the image contains Glorp working with the PostgresV2 driver, I set about integrating NBSQLite3 with Glorp. After about an afternoon's work, I now have Reddit.st working with Glorp+NBSQLite3.

$ sqlite3 reddit.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from reddit_links;
1|http://www.pharo.org|Pharo Smalltalk|2014-09-22 22:46:53|1
2|http://planet.smalltalk.org|Planet Smalltalk|2014-09-22 22:47:18|1
6|http://www.world.st/|The World of Smalltalk|2014-09-22 22:58:50|0
sqlite> 

There is still much to be done to get Glorp fully working with NBSQLite3: Some tests apparently expect Glorp proxies, but are getting OrderedCollections, and one particular test rendered my Linux X session non-responsive to mouse and keyboard, except for screen brightness key chords!