You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
PostgresV3 [PostgresV3Help]
** Overview
#+BEGIN_EXAMPLE
We’ve uploaded the latest version to the squeaksource repository in two forms: as a single package, and as multiple separate packages.
Links:
http://www.squeaksource.com/PostgresV3.htmlhttp://www.squeaksource.com/PostgresV3
The single package version will not be updated anymore. The multi-package version 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.
We use it daily in our projects, and I would say it’s pretty stable.
#+END_EXAMPLE
** PostgresV3 Core
#+BEGIN_EXAMPLE
Core is basically the implementation of the protocol. It enables one to create connections and execute text queries.
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.
#+END_EXAMPLE
** PostgresV3 Pool
#+BEGIN_EXAMPLE
This is an implementation of a connection pool. We always use it when we connect to a database. It also gives support for easy transaction handling.
#+END_EXAMPLE
** PostgresV3 CodeMirror
#+BEGIN_EXAMPLE
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 cannot be specified (e.g. security definer),
no support for inout/out parameters, the return type has to be refcursor, or a basic type.
#+END_EXAMPLE
** PostgresV3 Objects
#+BEGIN_EXAMPLE
Deprecated: This is the old way to map rows to objects and cache them. We dont use it anymore in new projects.
#+END_EXAMPLE
** Standard Usage Pattern
#+BEGIN_EXAMPLE
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:
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 ” ]
#+END_EXAMPLE
** Examples [PostgresV3HelpExamples]
*** Example Database [PostgresV3ExampleDB]
**** Overview
#+BEGIN_EXAMPLE
TODO Write an example postgres database and tailor examples to it.
#+END_EXAMPLE
*** Step by Step Example One [PostgresV3ExampleOne]
**** Subclass ConnectionPool
#+BEGIN_EXAMPLE
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
TODO: Make this talk to the example database.
#+END_EXAMPLE
**** Execute Queries
#+BEGIN_EXAMPLE
(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 ”
at: 1 ” and the first column, this returns the number 7 ”
Or:
#rowsWithColumnNameDictionary will only work if you name your columns and
ensure that all of them have different names. Here is an example:
(YourConnectionPool default executeQuery: ‘select 3 + 4 as “theResult”’)
first
rowsWithColumnNameDictionary
first
theResult
TODO: Make this talk to the example database.
#+END_EXAMPLE
**** Create a Schema Mirror
#+BEGIN_EXAMPLE
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’
TODO: Make this talk to the example database.
#+END_EXAMPLE
**** Schema Mirror browseChanges
#+BEGIN_EXAMPLE
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.
TODO: Make this talk to the example database.
#+END_EXAMPLE
**** Schema Mirror Auto commit
#+BEGIN_EXAMPLE
You can set the autocommit behavior with:
YourSchemaMirror commitOnAccept: true
TODO: Make this talk to the example database.
#+END_EXAMPLE
**** Refactor Me
#+BEGIN_EXAMPLE
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;
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 ] ] ]
#+END_EXAMPLE
** Bibliography [PostgresV3HelpForumPosts]
*** Jun 17 2011
#+BEGIN_EXAMPLE
This content copied and pasted from: http://forum.world.st/status-of-PostgresV3-package-td3600415.html
On Wed, 15 Jun 2011, marcelo Cortez wrote:
> hi all
i would like to know the status of the proyect
“A client for PostgreSQL using the V3 protocol”
i donwnloaded from squeaksource this proyect
but the few tests that contain the downloaded project
don’t help me to understand the use of the framework
any help would be appreciated
best
We’re using it in production, though we’re a few versions ahead of the
public repository. For general consumption I’d say it’s usable, but the
API may change and there may be bugs (so it’s alpha). Some features are
not implemented yet, some will never be. Also there’s no user
documentation yet, just some API docs and only a small, but critical part
of the package has tests.
So here’s some ad-hoc user’s guide:
The package provides different ways to access the database. The simplest
thing you can do is to create a PG3Connection object which represents a
connection to the database. The easiest way to do this is to create a
PG3ConnectionArguments object, initialize it and request a new connection
from it. Here’s an example:
connectionArguments := PG3ConnectionArguments new
hostname: ‘127.0.0.1’;
port: 5432;
username: ‘user1’;
password: ‘123’;
databaseName: ‘foo’
yourself.
connection := connectionArguments newConnection.
then you can activate the connection:
connection startup.
and execute a query:
resultSets := connection execute: ‘select 3 + 4, now()’. “an OrderedCollection of PG3ResultSets”
resultSet := resultSets first. “a PG3ResultSet”
rows := resultSet rows. “an OrderedCollection of PG3Rows”
firstRow := rows first. “a PG3Row”
firstRow at: 1. “7”
firstRow at: 2. “2011-06-16T23:21:36.358833+02:00”
finally close the connection:
connection terminate.
If you ask #rowsWithColumnNameDictionary instead of #rows from the result
set, then the PG3Row objects will understand the names of the columns as
messages. For example:
resultSets := connection execute: ‘select 3 + 4 as result, now() as “currentDateAndTime”.
resultSet := resultSets first.
rows := resultSet rowsWithColumnNameDictionary.
firstRow := rows first.
firstRow result. “7”
firstRow currentDateAndTime. “2011-06-16T23:21:36.358833+02:00”
Frequently creating connections is not cheap, so there’s a connection
pool, which besides storing connections also provides some cool stuff,
like transactions. Here’s how to create a new connection pool:
A connection pool is a singleton, so create a subclass of
PG3ConnectionPool:
PG3ConnectionPool subclass: #PG3ExampleConnectionPool
instanceVariableNames: ’
classVariableNames: ’
poolDictionaries: ’
category: ‘Postgres-Example’
implement #defaultConnectionArguments on the class side:
defaultConnectionArguments
^PG3ConnectionArguments new
hostname: ‘127.0.0.1’;
port: 5432;
username: ‘user1’;
password: ‘123’;
databaseName: ‘foo’;
yourself
then you can execute queries:
PG3ExampleConnectionPool default withConnectionDo: [ :connection |
connection execute: ‘select 3 + 4’.
connection execute: ‘select now()’ ].
the latest version (which is not available yet) can also execute
transactions:
PG3ExampleConnectionPool default executeTransation: [ :connection |
connection execute: ‘insert into foo values(1, 2, 3)’.
connection savepoint: ‘foo1’.
…
connection execute: ‘insert into baz values(‘abc’)’.
connection rollbackTo: ‘foo1’ ].
Passing the connection around in smalltalk is a bit tedious, so you can
use the pool again, which will be able execute your query in the same
transation:
PG3ExampleConnectionPool default executeTransation: [
PG3ExampleConnectionPool default executeQuery: ‘insert into foo values(1, 2, 3)’.
… ].
Managing queries in smalltalk is not easy, so instead of string queries
you can use functions to access postgres. I won’t write about this now,
because it’d be a bit too long and the API on squeaksource is obsolete now
(PG3FunctionClient). In the new API you can edit, save, debug,
synchronize, etc. the (plpgsql) functions from the Browser in Squeak.
Levente
mdc
>
>
marcelo Cortez Reply | Threaded | More
#+END_EXAMPLE
*** Sep 25 2014
#+BEGIN_EXAMPLE
This content copied and pasted from http://forum.world.st/Status-of-PostgresV3-td4780110.html
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.
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.
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:
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;
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, Balazs
Hi Balazs,
Thanks for the clear and extensive response. I’ll take a look
at the new packages and see how far I get. So if I understand it
correctly, your code is more tightly coupled to the PostgreSQL
database, can get to better performance by heavy reliance
on stored procedures, and gives up some abstraction to get there?
And of course there are the results of using v3 vs v2 and
non-blocking calls to the database?
Cheers,
Stephan
Stephan Eggermont-3
>(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 ”
I can get the results back and inspect them.
Pg3Row doesn’t understand first.
If I use a rowsWithColumnNameDictionary,
I get an interesting column name:
#’?column?’
Is that default postgres behavior, or a textconverter issue?
Stephan
>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
The default schema that was created is ‘public’.
When trying to browseChanges on that, I get back
a ‘function array_accum(text) does not exist’ in a
Pg3ErrorResponse. (Postgres 9.3.5 on Mac 10.7.5)
Stephan
Hello Stephan,
On Wed, 1 Oct 2014, Stephan Eggermont wrote:
> >(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 ”
>
> I can get the results back and inspect them.
>
> Pg3Row doesn’t understand first.
That’s a bug in the example. You have to use ‘at: 1’ instead of ‘first’.
It should be:
(YourConnectionPool default executeQuery: ‘select 3 + 4’)
first
rows
first
at: 1
>
> If I use a rowsWithColumnNameDictionary,
> I get an interesting column name:
> #’?column?’
#rowsWithColumnNameDictionary will only work if you name your columns and
ensure that all of them have different names. Here’s an example:
(YourConnectionPool default executeQuery: ‘select 3 + 4 as “theResult”’)
first
rowsWithColumnNameDictionary
first
theResult
Levente
>
> Is that default postgres behavior, or a textconverter issue?
>
> Stephan
>
>
On Wed, 1 Oct 2014, Stephan Eggermont wrote:
> >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
>
> The default schema that was created is ‘public’.
>
> When trying to browseChanges on that, I get back
> a ‘function array_accum(text) does not exist’ in a
> Pg3ErrorResponse. (Postgres 9.3.5 on Mac 10.7.5)
That’s right. The query we use to get the defined functions in a schema
uses that aggregate function. We always add it to our databases, because
it’s really useful. It’s from
http://www.postgresql.org/docs/9.3/static/xaggr.html and the definition
is
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = ‘{}’
);
But I’ve uploaded a new version of PostgresV3-CodeMirror, which doesn’t
use that function anymore, so it should work with the default Postgres
installation.
Levente
#+END_EXAMPLE