PostgresV3 with parameter binding

Hi Levente, Balázs, and all,

I've modified PostgresV3 to support parameter binding so that parsing
SQL with placeholders and binding/executing with data are done
separately. So far tested inserts and selects successfully.

I've created an account on Squeaksource called PierceNg.  Happy to
contribute my changes to the Squeaksource repo if you grant me commit
access.

I'm more of a Pharo user, and intend to make PostgresV3's TLS work with
Pharo, and also to develop a more friendly API for it. Of course I will
keep protocol state machine changes in sync between the Squeak and Pharo
versions.

Screenshots:

1 - Wireshark capture of a select protocol exchange.

2 - Pharo inspector of a PG3ResultSet. More work needs to be done - the
left-most column should not be large negative numbers.

Cheers,

Pierce
https://www.samadhiweb.com/blog

Hi Pierce,

On Sun, 23 Dec 2018, Pierce Ng wrote:

> Hi Levente, Balázs, and all,
>
> I've modified PostgresV3 to support parameter binding so that parsing
> SQL with placeholders and binding/executing with data are done
> separately. So far tested inserts and selects successfully.

That's great news.

I didn't finish the implementation of the extended query protocol, because
it didn't really give us any benefit. I expected better parsing
performance from binary encoded column values, but that only applies to
certain types, its use is discouraged by the Postgres devs (it relies on
internal representation which may change between versions) and you have to
specify it before you execute the query for each response column.
But if you write SQL queries in Smalltalk (something we avoid to do), then
there are cases when it is better than the simple query protocol.

>
> I've created an account on Squeaksource called PierceNg.  Happy to
> contribute my changes to the Squeaksource repo if you grant me commit
> access.

I have added you as developer.

>
> I'm more of a Pharo user, and intend to make PostgresV3's TLS work with
> Pharo, and also to develop a more friendly API for it. Of course I will
> keep protocol state machine changes in sync between the Squeak and Pharo
> versions.

The last time I checked, many things worked out of the box in Pharo. I'm
sure there will be problems with text encoding and the time primitives
(methods have different names for no reason).

Levente

On Sun, Dec 23, 2018 at 01:50:45PM +0100, Levente Uzonyi wrote:
> I didn't finish the implementation of the extended query protocol

Turns out the modifications required to support parameter binding are
minimal.

> it didn't really give us any benefit. I expected better parsing performance
> from binary encoded column values, but that only applies to certain types,

For me the first consideration is security. I avoid libraries that do
SQL string construction.

> I have added you as developer.

Thank you. I've committed my changes to PostgresV3-Core and added
PostgresV3-DBAPI, a very simple client API. Tested select, insert and
delete successfully.

In PostgresV3-DBAPI, examples are in PG3ExampleClient class-side. Here's
one:

  insertThenSelectSakila
    self run: 'insert into actor (first_name, last_name) values ($1, $2)'
        parameters: #('JACKIE' 'CHAN').
    (self run: 'select actor_id, first_name, last_name from actor where last_name = $1'
        parameters: #('CHAN'))
      first inspect.
    self run: 'delete from actor where first_name = $1 and last_name = $2' parameters: #('JACKIE' 'CHAN').
               
   "
   Expected result:
   <actor_id> JACKIE CHAN
   "

These examples require a running PostgreSQL server with the Sakila database
loaded and appropriate permissions for the user 'testuser'. Setting that up
is left as an exercise for the reader. :-)

All existing tests pass, and the simple query protocol should continue to
work as seen in PG3ExampleClient class>>selectSakilaSimple.

Some points about my changes:

- The states DescribeCompleted and GotRowDescription overlap. I imagine one
  of them is redundant, although I didn't look at the former very hard.
  I've gone for the latter.

- For binding string parameters, printing Smalltalk strings literally as
  done by String>>pg3PrintAsLiteralOn: doesn't work, because the
  parameter that is transmitted is then a string in single quotes which
  is usually not what is wanted. I've left the method alone and modified
  PG3BinaryWriteStream>>writeObject:formatCode: instead.

- Only tested with string and integer parameters.

- The DB API does query-based parse-bind-execute. It doesn't currently
  do repeatable portal-based execution. Something to add if needed.

Season's greetings.

Pierce