Glorp SQLite on Pharo 7

23 October 2018

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.

Glorp Mapping Existing Schema - Part 2

20 October 2018

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

Glorp Mapping Existing Schema - Part 1

18 October 2018

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

Docker and Pharo

9 September 2018

Recently there were discussions and blog posts on Docker for Pharo and Gemstone/S. This is my report after spending an afternoon on the subject.

First, some links:

This blog is implemented in Pharo and is the natural choice for my Docker example application. I already have a Smalltalk snippet to load this blog's code and its dependencies into a pristine Pharo image, so I'll be using that. Also, as a matter of course, I build the Pharo VM from source, and my VM installation also contains self-built shared libraries like libsqlite.so and libshacrypt.so.

Outside of Docker, prepare a custom Pharo image:

% cp ../Pharo64-60543.image scms1.image
% cp ../Pharo64-60543.image scms1.image
% ~/pkg/pharo6vm64/gofaro scms1.image st loadSCMS1.st

gofaro is a simple shell script which purpose is to make sure the Pharo VM loads my custom shared libraries, co-located with the standard VM files, at run time:

#!/bin/sh
PHAROVMPATH=$(dirname `readlink -f "$0"`)
LD_LIBRARY_PATH="$PHAROVMPATH" exec "$PHAROVMPATH/pharo" $@

loadSCMS1.st looks like this:

"Load dependencies and then the blog code."
Gofer it ...
Gofer it ...
Metacello new ...
Metacello new ...

"Save the image for injection into Docker."
SmalltalkImage current snapshot: true andQuit: true

Before describing my Dockerfile, here are my conventions for inside the Docker container:

  • VM goes into /pkg/vm.
  • Application artifacts including the image and changes files go into /pkg/image.
  • For this blog application, the blog's content is in /pkg/cms.

Starting with Ubuntu 18.04, install libfreetype6. The other lines are copied from Torsten's tutorial.

FROM ubuntu:18.04
LABEL maintainer="Pierce Ng"
RUN apt-get update \
  && apt-get -y install libfreetype6 \
  && apt-get -y upgrade \
  && rm -rf /var/lib/apt/lists/* \
  && true

Next, install the Pharo VM.

RUN mkdir -p /pkg/vm
COPY pharo6vm64/ /pkg/vm
COPY pharolimits.conf /etc/security/limits.d/pharo.conf

Now copy over the prepared Pharo image.

RUN mkdir -p /pkg/image
WORKDIR /pkg/image
COPY PharoV60.sources PharoV60.sources
COPY scms1.image scms1.image
COPY scms1.changes scms1.changes
COPY runSCMS1.st runSCMS1.st

Finally, set the Docker container running. Here we create a UID/GID pair to run the application. Said UID owns the mutable Pharo files in /pkg/image and also the /pkg/image directory itself, in case the application needs to create other files such as SQLite databases.

RUN groupadd -g 1099 pharoapp && useradd -r -u 1099 -g pharoapp pharoapp
RUN chown -R pharoapp:pharoapp /pkg/image
RUN chown root:root /pkg/image/PharoV60.sources
RUN chown root:root /pkg/image/runSCMS1.st
EXPOSE 8081
USER pharoapp:pharoapp
CMD /pkg/vm/gofaro -vm-display-null -vm-sound-null scms1.image --no-quit st runSCMS1.st

runSCMS1.st runs the blog application. In my current non-Dockerized installation, the runSCMS1.st-equivalent snippet is in a workspace; for Docker, to become DevOps/agile/CI/CD buzzwords-compliant, this snippet is run from the command line. This is one Dockerization adaptation I had to make to my application.

Now we build the Docker image.

% sudo docker build -t samadhiweb/scms1:monolithic .
Sending build context to Docker daemon    299MB
Step 1/19 : FROM ubuntu:18.04
 ---> cd6d8154f1e1
Step 2/19 : LABEL maintainer="Pierce Ng"
 ---> Using cache
 ---> 1defb3ac00a8
Step 3/19 : RUN apt-get update   && apt-get -y install libfreetype6   && apt-get -y upgrade   && rm -rf /var/lib/apt/lists/*   && true
 ---> Running in b4e328138b50
<bunch of apt-get output>
Removing intermediate container b4e328138b50
 ---> 79e9d8ed7959
Step 4/19 : RUN mkdir -p /pkg/vm
 ---> Running in efb2b9b717fe
Removing intermediate container efb2b9b717fe
 ---> 0526cbc4c483
Step 5/19 : COPY pharo6vm64/ /pkg/vm
 ---> 2d751994c68c
Step 6/19 : COPY pharolimits.conf /etc/security/limits.d/pharo.conf
 ---> f442f475c568
Step 7/19 : RUN mkdir -p /pkg/image
 ---> Running in 143ebd54f243
Removing intermediate container 143ebd54f243
 ---> 6d1b99d30050
Step 8/19 : WORKDIR /pkg/image
 ---> Running in 45c76d8c08c0
Removing intermediate container 45c76d8c08c0
 ---> 57247408801b
Step 9/19 : COPY PharoV60.sources PharoV60.sources
 ---> 8802acc416f0
Step 10/19 : COPY scms1.image scms1.image
 ---> 3e2d62be5d00
Step 11/19 : COPY scms1.changes scms1.changes
 ---> dcbec7ebdda9
Step 12/19 : COPY runSCMS1.st runSCMS1.st
 ---> 72fa4efb33ff
Step 13/19 : RUN groupadd -g 1099 pharoapp && useradd -r -u 1099 -g pharoapp pharoapp
 ---> Running in e0af716c8db2
Removing intermediate container e0af716c8db2
 ---> 0a42beed8065
Step 14/19 : RUN chown -R pharoapp:pharoapp /pkg/image
 ---> Running in 2da21fefa399
Removing intermediate container 2da21fefa399
 ---> 0d808f48ae32
Step 15/19 : RUN chown root:root /pkg/image/PharoV60.sources
 ---> Running in 4ca0c6eb8301
Removing intermediate container 4ca0c6eb8301
 ---> 1426236b509c
Step 16/19 : RUN chown root:root /pkg/image/runSCMS1.st
 ---> Running in a942ecb8a155
Removing intermediate container a942ecb8a155
 ---> 1213e1647076
Step 17/19 : EXPOSE 8081
 ---> Running in 3b74e55b6394
Removing intermediate container 3b74e55b6394
 ---> a04593571d13
Step 18/19 : USER pharoapp:pharoapp
 ---> Running in 77ecde5a7ca7
Removing intermediate container 77ecde5a7ca7
 ---> 975b614d3a9f
Step 19/19 : CMD /pkg/vm/gofaro -vm-display-null -vm-sound-null scms1.image --no-quit st runSCMS1.st
 ---> Running in 2c6e7645da3d
Removing intermediate container 2c6e7645da3d
 ---> 65b4ca6cc5c5
Successfully built 65b4ca6cc5c5
Successfully tagged samadhiweb/scms1:monolithic
% sudo docker image ls
REPOSITORY              TAG                 IMAGE ID            CREATED             SIZE
samadhiweb/scms1        monolithic          65b4ca6cc5c5        2 minutes ago       402MB
...
% 

The Docker image has been created, but it is not ready to run yet, because the web content is not in the image. I'll put the content in a Docker volume. Below, the first -v mounts my host's content directory into /tmp/webcontent in the container; the second -v mounts the volume smdw-content into /pkg/cms in the container; I'm running the busybox image to get a shell prompt; and within the container I copy the web content from the source to the destination.

% sudo docker volume create smdw-content
% sudo docker run --rm -it \
  -v ~/work/webcms/samadhiweb:/tmp/webcontent \
  -v smdw-content:/pkg/cms \
  busybox sh
/ # cp -p -r /tmp/webcontent/* /pkg/cms/
/ # ^D
% 

Finally, run the Docker image, taking care to mount the volume smdw-content, now with this blog's content:

% sudo docker run --rm -d -p 8081:8081 \
  -v smdw-content:/pkg/cms \
  --name samadhiweb samadhiweb/scms1:monolithic
bfcc80b32f35b3979c5c8c1b28bd3464f79ebdae91f51d9422334b209678ab5c
% sudo docker ps
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS              PORTS                    NAMES
bfcc80b32f35        samadhiweb/scms1:monolithic   "/bin/sh -c '/pkg/vm."   4 seconds ago       Up 3 seconds        0.0.0.0:8081->8081/tcp   samadhiweb

Verified with a web browser. This works on my computer. :-)