Sven Van Caekenberghe has announced a new release of P3, which also implements parameter binding using the PostgreSQL extended query protocol.

Both P3 and PostgresV3 support TLS, the former's being Pharo-specific and the latter's being Squeak-specific.

Which to use? Simple - on Pharo, use P3; on Squeak, use PostgresV3.

PostgresV3 is a pure Smalltalk implementation of the PostgreSQL v3 wire protocol. I've modified PostgresV3-Core to do parameter binding and added PostgresV3-DBAPI, a very simple client API.

In PostgresV3-DBAPI, there are a number of examples in PG3ExampleClient. These examples require a PostgreSQL server loaded with the Sakila database. To set that up, firstly, download the Sakila database files. Place the PostgreSQL files in ~/data, say.

The quickest way to get PostgreSQL up and running is by Docker. Here's my docker-compose file that uses the official Postgres-11 image. Data is kept in a separate volume 'pgdata'.

version: '3.6'

services:
  postgres:
    image: postgres:11
    ports:
      - "5432:5432"
    volumes:
      - type: bind
        source: /home/pierce/data
        target: /tmp/data
      - type: volume
        source: pgdata
        target: /var/lib/postgresql/data/datavolume
    environment:
      PGDATA: /var/lib/postgresql/data/datavolume
    restart: unless-stopped

volumes:
  pgdata:
    name: pgdata
    external: true

Bring up PostgreSQL, create roles, load the Sakila data, assign permissions:

% sudo docker-compose up -d
Creating network "postgres-sakila_default" with the default driver
Creating postgres-sakila_postgres_1 ... done

% sudo docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
936cc31523e3        postgres:11         "docker-entrypoint.s…"   36 seconds ago      Up 34 seconds       0.0.0.0:5432->5432/tcp   postgres-sakila_postgres_1

% sudo docker exec -it 936cc31523e3 bash
root@936cc31523e3:/# su - postgres
postgres@936cc31523e3:~$ psql 
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.
postgres=# create role testuser login password 'secret';
CREATE ROLE
postgres=# create database sakila owner testuser;
CREATE DATABASE
postgres=# \c sakila
You are now connected to database "sakila" as user "postgres".
sakila=> \i /tmp/data/postgres-sakila-schema.sql 
...
GRANT
GRANT
sakila=> \i /tmp/data/postgres-sakila-data.sql 
...
COPY 2
ALTER TABLE
sakila=# grant all privileges on all tables in schema public to testuser;
GRANT
sakila=# grant all privileges on all sequences in schema public to testuser;
GRANT
sakila=# \q
postgres@936cc31523e3:~$ logout
root@936cc31523e3:/# exit
%

In a fresh Squeak image - I'm using 5.2 - evaluate in a workspace:

Installer monticello http: 'http://www.squeaksource.com/';
  project: 'PostgresV3';
  install: 'PostgresV3-Core';
  install: 'PostgresV3-Tests-Core';
  install: 'PostgresV3-DBAPI'.

PG3ExampleClient selectSakilaSimple.
PG3ExampleClient selectSakila.
PG3ExampleClient selectSakilaOneStringParam.
PG3ExampleClient selectSakilaOneStringParam2.
PG3ExampleClient selectSakilaOneStringParamNoResult.
PG3ExampleClient selectSakilaOneIntegerParam.
PG3ExampleClient selectSakilaTwoParams.
PG3ExampleClient insertThenSelectSakila.

In a fresh Pharo image - I'm using the latest 7.0 pre-release - evaluate in a playground:

Gofer it 	
  url: 'http://squeaksource.com/PostgresV3';
  package: 'PostgresV3-Core';
  package: 'PostgresV3-Tests-Core';
  package: 'PostgresV3-DBAPI';
  load. 

PG3ExampleClient selectSakilaSimple.
PG3ExampleClient selectSakila.
PG3ExampleClient selectSakilaOneStringParam.
PG3ExampleClient selectSakilaOneStringParam2.
PG3ExampleClient selectSakilaOneStringParamNoResult.
PG3ExampleClient selectSakilaOneIntegerParam.
PG3ExampleClient selectSakilaTwoParams.
PG3ExampleClient insertThenSelectSakila.

As this is a pure Smalltalk implementation, it should also work in earlier versions of Squeak and Pharo.

Testing and comments welcome.

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.

Konstantin Gredeskoul, CTO of Wanelo, gave a great talk entitled "12-Step Program for Scaling Web Applications on PostgreSQL"; slides here. Wanelo uses Rails, and as the title says, the talk is heavily focused on PostgreSQL, but many of the points should be applicable to Smalltalk web programming.

Hat tip to Josh Berkus.

I'm reading the book Seven Databases in Seven Weeks by Eric Redmond and Jim Wilson. The databases covered are PostgreSQL, Riak, HBase, MongoDB, CouchDB, Neo4J and Redis.

There are several Pharo/Squeak libraries for PostgreSQL:

• PostgresV3 on SqueakSource.
• PostgresV2 on SqueakSource.
• Squeak_PostgreSQL by Jim Menard. Last updated in 2006.
• DBXTalk, using OpenDBX, which wraps libpq. Also has GlorpDBX, which is an ORM that uses DBXTalk or the PostgresV2 driver.

Riak:

• Runar Jordahl's EpigentRiakInterface.
• Goran Krampe's fork, Phriak.

HBase runs on JVM. It supports a RESTful HTTP API, Thrift, and a Java API. The first is probably the easiest way to write a Smalltalk interface.

MongoDB:

• Estaban Lorenzano's Voyage is an object persistence layer that works with MongoDB.
• MongoTalk, by Kent Beck and others.

CouchDB:

• SCouchDB by Igor Stasenko.
• SCouchDBViewServer, by Jesus Mari Aguirre, a fork and superset that includes other functionality.

Neo4J is a graph database. It is provides a RESTful API. I've not played with Neo4J, but I'd imagine the Smalltalk environment, and by extension any Smalltalk object persistence mechanism, make up a graph database. Probably speaking from ignorance here, but I'm not sure what interest a Smalltalk programmer will have in a graph database written in Java. :-)

Finally, there is Redis Client by Mike Hale and others.

I haven't finished the book, but so far I haven't seen any discussion on authentication or security of these HTTP-speaking NoSQL databases. If the database is lacking authentication or SSL, and if your threat model covers that, probably the easiest is to put these behind a proxy. And, for database and other such connectivity from the Smalltalk client, I suggest SpsSplitPasswordStore.

In the previous post I wrote about visualizing the PostgresV3 pure Smalltalk implementation of the PostgreSQL v3 wire protocol. That "visualization" was in text, which is not nearly as visual as seeing the protocol state machine graphically.

In this post, let's use GraphViz to do that. Building on the code previously written:

| skip states statesMap gv |

skip := #('PG3NoticeResponse' 'PG3NotificationResponse' 'PG3ParameterStatus').
states := OrderedCollection new.

PG3ServerState createStateGraph valuesDo: [ :inst |
  inst transitions keysAndValuesDo: [ :k :v |
    (skip includes: k asString) ifFalse: [
      states add: (Array with: inst with: k with: v first with: v second) ]]].

states do: [ :ea |
  Transcript show: 'From ', ea first name; 
    show: ' on ', ea second asString.
  ea fourth ifNotNil: [ Transcript show: ' perform #', ea fourth ].
  Transcript show: ' goto ', ea third name; cr; flush ].

statesMap := Dictionary new.
states do: [ :ea |
  (statesMap includesKey: ea first name) ifFalse: [
    statesMap at: ea first name put: OrderedCollection new ].
  (statesMap at: ea first name) add: ea ].

gv := GraphViz new.
gv beDirected;
  name: 'PostgresV3 Protocol State Machine';
  add: #graph with: { #overlap -> #scale. #concentrate -> #true };
  add: #edge with: { #arrowsize -> 0.5 }.
	
statesMap keysAndValuesDo: [ :k :v |
  gv add: k with: { #shape -> #box. #fontsize -> 10 }.
  v do: [ :ea |
    gv add: ea third name with: { #shape -> #box. #fontsize -> 10 }.
    gv add: k -> ea third name with: { #label -> ea second asString. #fontsize -> 8 } ]].

gv openInWindow.

PostgresV3 is a pure Smalltalk implementation of the PostgreSQL v3 wire protocol (and a database access API) by Levente Uzonyi and Balazs Kosi.

In code, the protocol is implemented as a state machine, created by "PG3ServerState createStateGraph", which invokes methods such as this:

initializingBackendStateDescription

(self state: #InitializingBackend)
  on: PG3BackendKeyData
  connectionDo: #registerBackendKeyData:
  goto: #InitializingBackend;

  on: PG3ReadyForQuery
  connectionDo: #readyForQuery:
  goto: #WaitingForQuery

"on:connectionDo:goto:" looks like this:

on: aPG3MessageClass connectionDo: selector goto: aSymbol
  transitions at: aPG3MessageClass put: { (self state: aSymbol). selector }

I wanted to map out all state transitions, to better visualize the protocol flow. This is done by walking through the "transitions" inst-var of each instance of PG3ServerState:

| skip states |

skip := #('PG3NoticeResponse' 'PG3NotificationResponse' 'PG3ParameterStatus').
states := OrderedCollection new.

PG3ServerState createStateGraph valuesDo: [ :inst |
  inst transitions keysAndValuesDo: [ :k :v |
    (skip includes: k asString) ifFalse: [
      states add: (Array with: inst with: k with: v first with: v second) ]]].

states do: [ :ea |
  Transcript show: 'From ', ea first name; 
    show: ' on ', ea second asString.
  ea fourth ifNotNil: [ Transcript show: ' perform #', ea fourth ].
  Transcript show: ' goto ', ea third name; cr; flush ].

Here's the output:

From InitializingBackend on PG3ReadyForQuery perform #readyForQuery: goto WaitingForQuery
From InitializingBackend on PG3BackendKeyData perform #registerBackendKeyData: goto InitializingBackend
From GotDataRow on PG3ErrorResponse perform #handleError: goto GotErrorResponseDuringSimpleQuery
From GotDataRow on PG3CommandComplete perform #commandComplete: goto GotCommandComplete
From GotDataRow on PG3DataRow perform #dataRow: goto GotDataRow
From GotErrorResponseDuringSimpleQuery on PG3ReadyForQuery perform #readyForQuery: goto WaitingForQuery
From GotEmptyQueryResponse on PG3ReadyForQuery perform #readyForQuery: goto WaitingForQuery
From GotEmptyQueryResponse on PG3ErrorResponse perform #handleError: goto GotErrorResponseDuringSimpleQuery
From Querying on PG3ErrorResponse perform #handleError: goto GotErrorResponseDuringSimpleQuery
From Querying on PG3CommandComplete perform #commandComplete: goto GotCommandComplete
From Querying on PG3RowDescription perform #rowDescription: goto GotRowDescription
From Querying on PG3EmptyQueryResponse goto GotEmptyQueryResponse
From Authenticating on PG3AuthenticationMD5Password perform #respondToAuthenticationMD5PasswordRequest: goto AuthenticatingWithMD5
From Authenticating on PG3AuthenticationOkMessage goto InitializingBackend
From AuthenticatingWithMD5 on PG3AuthenticationOkMessage goto InitializingBackend
From GotCommandComplete on PG3ReadyForQuery perform #readyForQuery: goto WaitingForQuery
From GotCommandComplete on PG3ErrorResponse perform #handleError: goto GotErrorResponseDuringSimpleQuery
From GotCommandComplete on PG3CommandComplete perform #commandComplete: goto GotCommandComplete
From GotCommandComplete on PG3RowDescription perform #rowDescription: goto GotRowDescription
From GotRowDescription on PG3ErrorResponse perform #handleError: goto GotErrorResponseDuringSimpleQuery
From GotRowDescription on PG3CommandComplete perform #commandComplete: goto GotCommandComplete
From GotRowDescription on PG3DataRow perform #dataRow: goto GotDataRow