[squeak-dev] PostgresV3 with parameter binding
Pierce Ng
pierce at samadhiweb.com
Tue Dec 25 03:23:59 UTC 2018
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
More information about the Squeak-dev
mailing list
|