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,
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
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,
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
squeak-dev@lists.squeakfoundation.org
-
Levente Uzonyi -
Pierce Ng