« Previous  |  Next »

PostgresV3 with Parameter Binding

25 December 2018

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.

Blog comments powered by Disqus