PostgreSQL

[Iain Mott]

Hello

Have placed the Pd interface to PostgreSQL up on my site
http://soundart.tripod.com (follow Pd link). The file to download is called
'sqlsingle.tar.gz'.
There's a bit to do before you can use the sqlsingle external - so please
read the README reprinted below before you decide to proceed.

Cheers, Iain

'sqlsingle' allows you to send SQL messages to a PostgreSQL database from Pd
and retrieve the results. In order to use the 'sqlsingle' external you will
need to install PostgreSQL - available from http://www.postgresql.org.
There's a bit of work to do compiling the source, setting up environment
variables etc - but the package comes with good documentation. Read the
installation guide and do the introductory tutorial before trying
'sqlsingle'. See also 'some tips & notes' below before you decide to use
'sqlsingle'.

As with all applications that connect to a PostgreSQL database, 'sqlsingle'
requires the 'postmaster' daemon to be running. 'sqlsingle' may connect to a
database on a local host or on a remote machine via TCP/IP (handled by the
external).

If no arguments are specified in 'sqlsingle' - messages will be sent to a
postmaster via a local UNIX socket and will attempt to connect to the
default PostgreSQL 'template1' database. Such a connection may be used to
create new database by sending the SQL message: 'CREATE DATABASE
databasename'. Before this can be done, you will need to set your username
as a PostgreSQL user. This can be done from a PostgreSQL superuser account
(not root) generally called 'postgres' which handles database administrative
duties including access privileges (all this is covered in the turorial).

Note: all SQL messages sent to the 'sqlsingle' must start with 'sql' and
terminate with 'sqlend'. By convention, all PostgreSQL commands (some are
non-standard SQL) are written in allcaps. A commer between an 'sqlend' and a
'sql' in a Pd message, will execute a new query or command.

Once you have created a database you can send messages to it with a second
'sqlsingle' object with the argument 'databasename'. See the patch
'sqlsingle.pd' for an example of this.

If you wish to connect with a database on a networked machine - two
arguments are needed in 'sqlsingle' in addition to the name of the database.
The first is the name of the machine - the second is the port number. To
facilitate this, the server machine must run the 'postmaster' with a '-i'
flag to enable remote connections. The postmaster command line must also
specify the port number ('-p <num>'). See PostgreSQL documentation for
details. A startup script is available (in the Linux distribution of
PostgreSQL at least) somewhere to enable automated activation of the
postmaster (i think this is described in one of the install guides in the
distribution tarball?). For security reasons, the postmaster should not be
run as root. Rather, it should be run from the 'postgres' account.

Please note - At present I am unable to test the network facility of
'sqlsingle' - but I assume it works(!?)

sqlsingle outputs results of queries as lists. Often there will be more than
one instance for a given query (instances are referred to as 'tuples'). eg.
the query 'Select * FROM tablename' will return all rows from the specified
table in a database. 'sqlsingle' retrieves each row as a seperate message,
each preceded by an index starting at zero.

Some tips & notes...

'sqlsingle' is useful in runningle single SQL queries or commands - which
can in themselves be quite complex. If multiple queries are executed rapidly
however - or if large amounts of new data needs to be inserted into a
database in real-time, the system can become bogged down. This in part is
due to the fact that each message sent to 'sqlsingle' establishes and closes
a new connection to the database. The 'INSERT' command can also be time
consuming - as the system re-optimises the database on each insert. To avoid
this - it is best to create new externals specialised for the task at hand.
Such externals may limit the number of new connections made before 'committi
ng' new data to the database. Another useful technique is to first write
blocks of data to be inserted to file. This data can then be 'copied' to the
database avoiding the time consuming 'INSERT' command (see 'COPY' PostgreSQL
command). The entire database can then be re-optimised at some later
convenient time (at startup of Pd for example) using the command line
program 'vacuumdb' eg. 'vacuumdb --analyze databasename' (this can also be
done from Pd using the 'system' external in the motex collection). See also
the PostgreSQL specific command (non-standard SQL) 'VACUUM'.

At present 'sqlsingle' handles 6 PostgreSQL data types: PGINT4; PGFLOAT8;
PGDOUBLE; PGDATE; PGDATETIME and PGVARCHAR. If a query returns tuples of a
different data type, these will be compiled into a list (on output) as Pd
'symbols'. In contrast, PGINT4, PGFLOAT8 and PGDOUBLE are compiled into
lists as floats. Like non-specified data types, PGDATE, PGDATETIME and
PGVARCHAR are compiled into lists as 'symbols'.

Use the 'psql' command line program to monitor databases - and for manual
data updates and manipulations.

The makefile is configured for Linux. To compile type: make pd_linux
See example patch sqlsingle.pd.

___________________________
Iain Mott
phone: +61 3 9639 2959
email:   iain.mott at bigpond.com
http://soundart.tripod.com