SpicyNoodles: PostgreSQL Extension for Frontier

PostgreSQL Extension for Frontier

What is PostgreSQL?

PostgreSQL is an open-source database management system (DBMS). It started out as the POSTGRES database management system, a research project at the University of California, Berkeley, in 1994. When its custom PostQuel query language was replaced with an extended subset of the Structured Query Language (SQL) in 1995, the name first changed to Postgres95 and then to PostgreSQL in 1996. Nowadays, all development on PostgreSQL is performed by a team of Internet developers who coordinate their efforts through several mailing lists.

PostgreSQL is distributed under the BSD license, a classic open-source license that does not place any restrictions on how the source code may be used, as long as the copyright on it is not misrepresented.

The PostgreSQL database server runs natively on many modern Unix platforms, including Mac OS X. A port to Windows NT and Windows 2000 based on Cygwin, a Unix-to-Windows porting library, is also available.

What is the PostgreSQL Extension for Frontier?

The PostgreSQL extension for Frontier consists of two parts, a dynamically-linked library (DLL) implemented in the C programming language for connecting to a PostgreSQL database server and a set of glue scripts for accessing the DLL from UserTalk scripts running in UserLand Frontier or Radio UserLand.

As an example, here's what a demo script looks like that lists the firstname and lastname fields of those rows from the database table named clients where the age field has the value 41:

screenshot of workspace.pgdemo script

System Requirements

The extension is cross-platform. It should work with Radio UserLand or UserLand Frontier 6.0 or later running on Windows, Mac OS 8.x/9.x, or Mac OS X. Since Frontier and Radio do not implement the DLL interface on 68k Macs, these machines are not supported.

Status

The current version is 1.0a14, released on July 17th, 2002.

The extension is still undergoing active development and it may not yet be ready for deployment as a part of mission-critical applications. Most of the development work for the current release has been done on Mac OS X, so recently the Carbon version has received most of the time devoted to testing. I have only done some light testing on Windows, and almost none on classic Mac OS so far.

The C source code for the DLL is available on request. It currently requires Metrowerks Codewarrior Pro 7 to compile.

Download

postgreSQL-10a14.fttb.sit as a StuffIt 6.5 archive [241 kB]

postgreSQL-10a14.fttb.zip as a WinZip archive [255 kB]

The downloads are identical except for the compression method used.

Installation

Decompress the downloaded archive and open the resulting file with Frontier or Radio. A new table will be installed at system.extensions.postgreSQL, containing the glue scripts and the platform-specific versions of the DLL. If you have used previous versions of the extension, a restart of Frontier or Radio is required immediately after installing the new version.

Getting Started

The most important change since version 1.0a5 is the replacement of the very basic libpq interface with an easier-to-use higher-level interface for executing queries and retrieving query results, loosely based on the Python Database API Specification 2.0. If you have used version 1.0a5 or earlier, you should consult the change notes before upgrading.

To get a feeling for how the new query interface works, first check out the postgreSQL.examples.highlevel demo script, and then study the detailed descriptions of all the PostgreSQL verbs making up the new query interface.

Please send all feedback, feature requests, and the inevitable bug reports to me privately at or to the Script Meridian Community mailing list. Although I am monitoring most UserLand-operated mailing lists, I do not consider these to be the proper forum for discussing the PostgreSQL extension.

If you are feeling adventurous, you can also try the debug versions of the DLLs, available as separate downloads for Mac OS and Windows. To install, decompress the archive, move the respective DLL to the DLLs sub-folder of your Frontier or Radio application folder, and restart the application. The debug versions write a lot of information to a text file named "postgreSQL_dbg_log.txt" on your hard disk and as a result are a lot slower than the release versions. If you want to switch back to the release version of the DLL, delete the debug version of the DLL from the DLLs sub-folder and restart the application.

Things to Watch Out For

It's perfectly acceptable to share a pool of connections between several threads, in fact that is its sole reason for being. However, an individual connection and its associated cursors should only be accessed from a single thread.

When you open a new connection or grab one from a pool of connections, a new transaction will be started and the transaction isolation level will be set to serializable. If you execute any SQL commands that change the database, you have to call postgreSQL.connection.commit to make your changes permanent and visible to other users. Alternatively, you can call postgreSQL.connection.rollback to abort the current transaction and have all changes rolled back. Either way, a new transaction will be started automatically when you run the next SQL command.

The PostgreSQL extension keeps track internally of when you committed or rolled back transactions, so that it will only send the respective commands to the database server when neccessary. For this feature to work correctly, you should refrain from running BEGIN, COMMIT, END, or ABORT commands directly through the query interface and instead only call the postgreSQL.connection.commit and postgreSQL.connection.rollback verbs to manage transactions.

To turn off this default transaction management behaviour, you can switch to auto-commit mode by calling postgreSQL.connection.setAutoCommit. If you do, the postgreSQL.connection.commit and postgreSQL.connection.rollback verbs will no longer have any effect and you can then also safely run BEGIN, COMMIT, END, or ABORT commands directly through the query interface.

When you execute a query or call a procedure on the server, all parameters and the values from the result sets will be converted from UserTalk to PostgreSQL data types and vice versa. For the data type conversion to work correctly, the PostgreSQL extension automatically sets the date style for each connection to ISO, and you should refrain from changing it, e.g. by running a SET DATESTYLE command over the connection.

On Mac OS X, any domain name resolution requests might block all Frontier threads until the extension has received a response from your domain name server. You can avoid this behavior by specifying the database server by IP address rather than domain name, or even better, if the database runs on the local machine, use Unix domain sockets to communicate with it.

To Do List

The extension will have to go through more rigorous testing before the version number can be bumped to 1.0. Towards that end, I plan to implement a suite of regression tests next.

The documentation for the PostgreSQL extension certainly needs to be improved. For example, a table documenting the automatic conversion between UserTalk and PostgreSQL data types and a short tutorial geared towards beginners are definitely in order.

As a long-term goal, I would like to see a specification for a common interface for accessing relational database management systems from Frontier or Radio, preferably based on the high-level interface now implemented in the PostgreSQL extension.

License

PostgreSQL Front-End DLL for UserLand Frontier

PostgreSQL Data Base Management System
(formerly known as Postgres, then as Postgres95)

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" ASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.