Status of PostgresV3

Hi Stephan,

On Thu, 25 Sep 2014, Stephan Eggermont wrote:
> What is the actual status of the PostgresV3 driver? In the last mailing list
> discussions about it there was a reference to newer features not yet
> on squeaksource?

We use it daily in our projects, and I would say it’s pretty stable.

We've uploaded the latest version to the squeaksource repository in two forms: as a single package, and as multiple separate packages. The former version will not be updated anymore. The latter consists of the following:

PostgresV3-Core:
This is basically the implementation of the protocol. It enables one to create connections and execute text queries.

PostgresV3-Pool (depends on Core):
This is an implementation of a connection pool. We always use it when we're connecting to a database. It also gives support for easy transaction handling.

PostgresV3-CodeMirror (depends on Core and Pool):
This is a set of high level tools which we use most of the time. It allows one to map Postgresql functions to Smalltalk methods. One can load, edit and save selected functions of a schema from a class browser. Invoking the smalltalk methods will result in calling the Postgresql functions. This tool has some limitations: only plpgsql functions are supported, some flags can't be specified (e.g. security definer), no support for inout/out parameters, the return type has to be refcursor, or a basic type.

PostgresV3-Objects:
This is the old way to map rows to objects and cache them. We don't use it anymore in new projects. The Core has support for custom rowClass per query (you can define what class should be instantiated for the rows returned by the query), which provides better row-object mapping.

> What kind of advantages should I expect when compared to the dbx stuff? Is there a Glorp driver for it?

We are not familiar with the Glorp and DBXTalk project, so can’t comment on that.

As mentioned by Levente in a previous mail we have a rather specific usage pattern:
We use stored procedures (written in plpgsql) to communicate with the database.

There is a class PG3SchemaMirror to aid you with that. You subclass it, and this subclass corresponds to a schema in your database. Its methods correspond to the database functions you want to call from Squeak.

For example you may have a “users” schema in your database, and a UsersSchemaMirror class in your image, with a method like this:

authenticate: username password: password

    <pg3Function: ‘authenticate’
        arguments: #(‘_username’ text ‘_password’ text)
        returns: #boolean
        volatility: #volatile>

begin
    return exists (
        select 1
        from users.user
        where
           username = _username and
           password_hash = crypt(_password, password_hash));
end;

and then from your code you can call it like:

(UsersSchemaMirror default authenticate: username password: password)
    ifTrue: [ “ authenticated successfully “ ]
    ifFalse: [ “authentication failed ” ]


Here is a step-by-step guide:

First create a subclass of PG3ConnectionPool and implement its defaultConnectionArgument class side method:

YourConnectionPool class >> defaultConnectionArguments

   ^PG3ConnectionArguments new
      hostname: '127.0.0.1';
      port: 5432;
      username: ‘username’;
      password: ‘password’;
      databaseName: ‘dbname’;
      yourself

now you can execute queries like this:

(YourConnectionPool default executeQuery: ‘select 3 + 4’)
    first “ a query may returns multiple result sets, now we select the first “
    rows “ we request the rows of the result set “
    first “ then the first row “
    first “ and the first column, this returns the number 7 ”

To create a schema mirror, subclass PG3SchemaMirror and implement its pool and schema class side methods.

YourSchemaMirror class >> pool

    ^YourConnectionPool default


YourSchemaMirror class >> schema

    ^’schema_name’


Schema mirrors mirror the functions in your database and provide an interface to call them. There is a little tool to inspect the differences between the methods of the schema mirror and the functions in the database, which can be invoked by:

YourSchemaMirror browseChanges

You can up and download functions via this interface. In earlier times we would write the database functions in pgAdmin3 and download them into the image. Nowadays we use the smalltalk browser to write the plpgsql code. You can set the autocommit behavior with:

YourSchemaMirror commitOnAccept: true


The method mirroring a database function has three parts:
 - the method signature,
 - the pragma describing the database function’s signature,
 - and the body of the database function.

There are two types of these functions, one that return simple types (text, numbers, booleans, arrays, …) and the other that return a collection of rows. Let’s take a look at some examples.

Suppose that you have a table in your schema that lists your plonks, and you want a method that count all the plonks of a specific color, then you may write something like this:

YourSchemaMirror >> numberOfPlonksColored: color

    <pg3Function ‘number_of_plonks_colored’
        arguments: #(‘_color’ text)
        returns: #integer
        volatility: #volatile>

begin
    return (
        select count(1)
        from your.plonk
        where color = _color);
end;

The method signature and the arguments specified in the pragma must correspond to each other. The arguments are described with a literal array of even elements. The odd ones are the parameter names, the even ones are the argument types.

Now you may want to list all your plonks of a color:

YourSchemaMirror >> plonksColored: color

    <pg3Function: ‘plonks_colored’
        arguments: #(‘ref’ refcursor ‘_color’ text)
        returns: #refcursor
        volatility: #volatile
        rowClass: #Plonk>

begin
    open ref for
        select
           p.id,
           (select count(1)
            from your.griffle g
            where g.plonk_id = p.id) as “griffleCount”
        from your.plonk p
        where color = _color
        order by age desc;
    return ref;
end;

Methods returning rows must have the return type refcursor, and also their corresponding database function’s first argument is a refcursor (which you omit from the method signature).

You may optionally specify a row class name (in our case Plonk) which must be a subclass of PG3Row. The returned objects understand the column names. So you can say:

(YourSchemaMirror default plonksColored: ‘red’) select: [ :each | each griffleCount > 3 ]

Using row classes has the benefit that you can implement some behaviour on the returned objects. Eg. you may create a method in Plonk to return all the griffles of a plonk:


Plonk >> griffles

    ^YourSchemaMirror default grifflesOfPlonk: self id

Usually we don’t keep these objects around and don’t share them. Most of the time we try to encapsulate database changes into single functions, so consistency is not an issue. Besides plpgsql is a really good language to manipulate a relational database. But using database transactions are straightforward too, just call the pool’s executeTransaction: method with a block. The block may have an argument where it receives the connection, which can be used to rollback the transaction. Eg.:


    YourConnectionPool default executeTransaction: [ :connection |
        | redPlonks |
        redPlonks := YourSchemaMirror default plonksColored: ‘red’.
        redPlonks size > 1 ifTrue: [
            | griffle |
            griffle := redPlonks first removeGriffle.
            redPlonks last addGriffle: griffle.
            (redPlonks last griffles count: [ :each | each smell = ‘stinky’ ]) > 2 ifTrue: [
               “ the last red plonk has too many stinky griffles. rollback! ”
               connection rollback ] ] ]

That’s it for now. If you have any questions, we are here to help.

Cheers, Balázs