Whitebeam Postgres API

Subject

Postgres has been an integral part of a whitebeam installation since its open source debut back in June 2001. Postgres provides the high performance data storage and access underlying the whole Whitebeam architecture. Postgres has however been hidden behind 'templates', which abstract away the complexities of dealing with complex database structures. The templates are very flexible in there own right and have been the basis of a wide range of applications from online surveys to content management systems (CMS) to sho[p front stores.

For some developers though it can be frustrating to be restricted to the limitations of the template interfaces, as good as they are. This is especially true where a developer has experience of SQL from other environments such as PHP.

Whitebeam now includes native access to the backend Postgres database engine. This interface provides sophisticated database access over and above that provided, for example, by the PHP library. and allows applications to go far beyond the already powerful functionality provided by the Whitebeam templates. The result is full access to high-performance SQL database technology combined with Whitebeams XML structured page generation.

Building Whitebeam with Postgres API support

Consistent with Whitebeams aim to provide an ISP friendly application development environment, Whitebeams Postgres interface can be customised through directives in the Apache configuration file (httpd.conf) allowing the ISP to specify which users can access which databases without having to configure the database for each new user.

Click here for full details of how to build the native Postgres interface into Whitebeam

The Postgres JavaScript API

With the servers configured you can access Postgres databases through Presentation Page JavaScript using a couple of classes. The primary class is PgsqlConnection. Executing SQL operations on an active connection will return instances of PgsqlResult

Database connection

Connection to any database is via the PgsqlConnection class. To connect, create an instance of this object and then invoke *either* the connect or pconnect methods.

Both of these methods take the same parameter, a connection description object. This takes a number of mandatory attributes.

PgsqlConnection.connect creates a connection to a database for the duration of this page generation. At the end of generating the current page the Presentation Engine will close down the Postgres connection.

Connecting to a database is quite a costly (slow) operation and so if every page has to connect to the database, performance is likely to be poor. Instead Whitebeam provides a persistent database connection in the form of the pconnect method. Identical in every way to 'connect' except that the connection is *not* closed at the end of a page generation.

If you use 'pconnect' and then in a subsequent page call 'connect' or 'pconnect' with the same connection description object then you will be given the existing (persistent) connection.

If you get to the point in a Presentation Page where you no longer need a connection you can call close. This is good practice, but not absolutely necessary. Non-persistent connections are closed automatically at the end of each page generation.

connect and pconnect both return a boolean value representing the success (true) or failure (false) to connect to the database.

Note that prior to version 0.9.33, the connect methods always return true. This is a known issue and is fixed in CVS and will be relased in 0.9.33.

Example

    var connectDesc = {
      dbname:'MySampleDBase',
      password:'Password',     // Note password is required!
      user:'postgres',
      host:'localhost'
    }
    var pgconn = new PgsqlConnection;

    if (pgconn.connect(connectDesc))
      rb.debug.write("DATABASE open OK<br>");
    else
      rb.debug.write("FAILED to open DATABASE<br>");
  

Executing Commands

With an established Postgres connection you can use 'exec' to run SQL commands on the database. The PgsqlConnection.exec method takes a single parameter, a string containing one or more SQL statements to execute. The method returns an instance of PgsqlResult class, which can be used to interrogate the results of the operation.

Generally SQL statements fall into two categories - command and query. Examples of query statements include 'SELECT', and an example of a command would be an 'INSERT'.

Query statements are the most common, and the ones that return the most data. Generally a SELECT will return a set of rows of data derived from one or more tables in the underlying database schema. Whitebeam provides access to these rows through the PgsqlResult object, which has a number of attributes describing the result and a couple of methods for extracting row information from the result set.

First of all to check whether a SQL statement succeeded, test the value of PgsqlResult.success. This is a boolean. If it is 'true' then the operation completed with no problems. If there were database errors then check the values of PgsqlResult.error.

For SELECT operations the PgsqlResult class provides two methods for extracting row information : PgsqlResult.fetchRowObject() and PgsqlResult.fetchRowArray(). These two methods return the same information, but in different formats. 'fetchRowObject' returns a JavaScript object containing named properties for each of the columns in the result set. 'fetchRowArray' returns a numerically indexed array of values for columns in the result set.

Note Whitebeam applies a very simple mapping of SQL type to Javascript type as follows:

• Numeric values: Integers, floats etc are converted to JavaScript numbers
• BYTEA: Binary byte arrays are converted to Whitebeam Binary objects
• Everything else: Converted to JavaScript strings

Clearing results

Although there is no strong requirement, it is good practice to call clear() on a result set when you've finished with the results. clear is automatically called at the end of each page generation. For database intensive pages where potentially multiple querries are being performed, clearing each result set when you've finished with the data will prevent the postgres server and the Whitebeam instance being memory bloated with data that is no longer required.

Escaping data

Generally you will need to build up SQL strings from information presented on a page. It is important that this data is safe and so the PgsqlConnection class provides a static (class) method called escape(str) that will return a safely escaped string of data suitable for inclusion in a constructed SQL query.

Escaping binary data

Binary data to be stored in BYTEA strings in Postgres require another level of escaping, and for this the PgsqlConnection class provides a different method : binaryEscape. Generally you will need to build up SQL strings from information presented on a page. It is important that this data is safe and so the PgsqlConnection class provides a static (class) method called escape(str) that will return a safely escaped string of data suitable for inclusion in a constructed SQL query.

Example

The following simple example reads the contents of an arbitrary SQL table called 'TestTable' and outputs all the columns to the output page.

  <rb:script>
    var pgconn = new PgsqlConnection;
    var pgres;
    if (pgconn.connect({dbname:'mydb', user:'me', password:'mydbpwd', host:localhost'})) {
      // Connected to the database - read in the contents of the table.
      pgres = pgconn.exec('SELECT * FROM TestTable');
    }
  </rb:script>
  <!-- If there are any results then output the table. -->
  <rb:if expr="pgres!=null && pgres.success && pgres.numRows>0">
    <table>
    <rb:repeatfor expr="var r=0;r<pgres.numRows && r<20;r++">
      <rb:script>
        // Get this row
        var row = pgres.fetchRowObject(r);
      <rb:script>
      <rb:if expr="r==0">
        <!-- This is the first row so output row titles -->
        <tr>
          <rb:repeatfor expr="var ch in row">
            <td><?= ch ?></td>
          </rb:repeatfor>
        </tr>
      </rb:if>
      <tr>
        <rb:repeatfor expr="var ch in row">
          <td><?= row[ch] ?></td>
        </rb:repeatfor>
      </tr>
    </rb:repeatfor>
  </table>

At the end of a page generation all connections to Postgres openned with this (connect) method are closed. Note that if the same database is openned very frequently this will be slow. See instead the pconnect method for creating persistent database connections.