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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.squeakfoundation.org/pipermail/squeak-dev/attachments/20140926/73ea5ec3/attachment.htm