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@bigpond.com http://soundart.tripod.com