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:
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:
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.
Next, modify the mapping for OFGroup in OFDescriptorSystem:
It is now necessary to add the table GROUPS_TEAMS to OFDescriptorSystem:
Now let's fetch the OFGroup instances with their linked OFTeam instances.
The above snippet produces the following output:
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.
Fortunately Glorp is cleverer than this, and provides a way to avoid the
N+1 problem, by using the message #alsoFetch:.
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
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.
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:
By convention, the Glorp mapping is encapsulated in the class OFDescriptor,
which has these supporting methods:
The mapping for OFGroup is as follows:
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:
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:
In its current state, footballdb-Glorp allows querying the initial World Cup
first round group membership.
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:
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
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:
All Glorp unit tests should pass. Tested on Linux using fresh 32- and 64-bit
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:
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 <something>". Use the Pharo 6 version.
Take a fresh Pharo 5 image. Make a script like the following and run
it on the image:
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.
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':
In Smalltalk, firstly, install PostgresV2:
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
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:
This is because GlorpDatabaseLoginResource class>defaultPostgreSQLLocalLogin
does not specify encodingStrategy, meaning it is nil and will respond to #asSymbol
I've integrated NBSQLite3 into Glorp on the current Pharo 4.0 v40592 beta
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
In a workspace/playground, execute the following:
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.
Made really good progress with NBSQLite3 for Glorp.
On the failed tests:
#testReadTimestamp - SQLite3 does not
support date/time/timestamp types natively. More investigation needed.
#testDateCast - The test itself says, "This test will fail on SQLite which
has no DB Date type; see test comment."
#testDeleteAndReinsertTwiceWhenRemovingFromAnExclusiveCollection - This test
is expected to result in a primary key conflict. Without studying the code,
my conjecture is that the test's failing has to do with SQLite's primary
#testInt8 - Integer overflow on 32-bit SQLite, I think.
#testPreparedStatementsAreReused - PostgresV2 doesn't support prepared
statements. NBSQLite3 does. Mayhaps this is why? More investigation needed.
#testUpdatingComment - Test passes on OSX Mavericks but fails on Linux Mint
17. No idea why.
#testUpdatingOtherThing - Test passes on OSX Mavericks but fails on Linux
Mint 17. No idea why.
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.
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.
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