Software Specification

PostgreSQL & EnterpriseDB Application Installer

Date	Thursday, 29 March 2007	
Author	Dave Page	
Status	Draft	
Version	1.5	

1	
Table of contents

1	Table of contents	3
2	Introduction	4
2.1	Purpose of this document	4
2.2	Scope of the project	5
2.3	References	6
2.4	Overview	7
3	Application publishing	8
3.1	Overview	8
3.2	Online infrastructure	9
3.3	Published data	10
3.3.1	Mirror data	10
3.3.2	Application data	10
4	Application Installer	12
4.1	Overview	12
4.2	Description of operation	13
4.2.1	Startup checks	13
4.2.2	Application selection	13
4.2.3	Mirror selection	14
4.2.4	Download	14
4.2.5	Installation	14
5	Ruby on Rails	16
5.1	Overview	16
5.2	Application requirements	17
5.2.1	Components	17
5.2.2	Build	17

2	
Introduction

2.1	Purpose of this document

This document is intended to provide an outline specification for an ‘Application Installer’ for PostgreSQL and EnterpriseDB. In order to encourage further adoption of PostgreSQL, development of an application installer is considered to make it quick and painless for users to install applications to use with their DBMS. Unlike the software included with the PostgreSQL and EnterpriseDB installers, the target applications for the installer are far less tightly bound to the DBMS – for example, application development environments, web applications and fat-client type applications. In many cases the applications available may work well with other DBMSs, or even be traditionally used with other DBMSs rather than PostgreSQL. The aim of this project is to make those applications as easy as possible to install and run with PostgreSQL – ideally to the extent that PostgreSQL becomes the preferred DBMS across their userbase.

Note: Whilst every effort has been made to ensure that this document is a complete and accurate specification of the software, it is always possible that errors or omissions are present that will require resolution during the programming and testing phases of the project.

2.2	
Scope of the project

The initial project will focus on the development of the online infrastructure to support the installer, and to build a version of the installer to run on the Microsoft Windows platform for inclusion with PostgreSQL’s Windows installer (pgInstaller). The installer and supporting infrastructure shall be designed with support for additional platforms in mind.

A Ruby on Rails environment will be packaged as an initial application for deployment using the installer, preconfigured for use with PostgreSQL as much as is practical, without modification of the source code to ensure that support can still be provided through the normal channels of the upstream project.

2.3	
References

•	The PostgreSQL Installer
	http://pgfoundry.org/projects/pginstaller/

•	Ruby on Rails
	http://www.rubyonrails.org/

•	The Ruby One Click Installer
	http://rubyforge.org/projects/rubyinstaller/

•	Instant Rails
	http://instantrails.rubyforge.org/wiki/wiki.pl

•	WiX
	http://wix.sourceforge.net/

2.4	
Overview

In the following sections the technical details of the project are examined:

Chapter 2 describes the application publishing and online infrastructure support required.

Chapter 3 describes the installer program.

Chapter 4 describes the Ruby on Rails package.

3	
Application publishing
3.1	Overview

In order to support the installer online services must be provided to allow applications and their download locations to be advertised to end users. The information required consists of the following:

1.	A master list of available applications. 
2.	The servers that applications may be downloaded from.

3.2	
Online infrastructure

The master list of applications may simply be a manually maintained data file, with the primary copy being stored in the CVS repository of a PGFoundry project. This will provide version control, and the ability to allow multiple maintainers to be easily supported through the existing PGFoundry infrastructure.

The current PostgreSQL download network consists of a large number of FTP servers (expected to be enhanced to include HTTP servers in the future) which are monitored on a daily basis and automatically enabled and disabled based on the ‘freshness’ of their individual copies of the download tree. The mirror tracking data is held in a PostgreSQL database running on borg.postgresql.org. The download tree includes all files that are published on the GBorg and PGFoundry project sites, as well as the core PostgreSQL downloads.

The scripts used to generate the content on www.postgresql.org use data stored on remote CVS repositories and in the mirror database to do their job. It therefore seems logical to extend these scripts to publish the data required by the installer onto the PostgreSQL website, regenerating it periodically form the mirrors database and project CVS repository.

3.3	
Published data

Data will be published in the two parts detailed above, each of which will consist of a single data file downloaded from a known location under http://www.postgresql.org/. Data will be stored in an XML format for ease of processing and manual file maintenance.
3.3.1	Mirror data

The mirror data will be extracted from the mirrors database on borg.postgresql.org. The data required is simply the country that the mirror is physically located in, and the URL to the root of the download tree. The following XML format will be used:

<mirrors>
	<mirror>
		<country>UK</country>
		<protocol>ftp</protocol>
		<hostname>ftp.uk.postgresql.org</hostname>
		<path>/pub/mirrors/postgresql/</path>
		<port>21</port>
	</mirror>
</mirrors>

Note that the port element is optional, and will only be included if the port used is not the default for the protocol used.

The data file will include only those mirrors known to have synchronised with the master site within the last 48 hours. 

This data file is already available at http://www.postgresql.org/mirrors.xml.
3.3.2	Application data

The application data is more complex but will also be maintained as an XML format file. The following fields will be required:

Field name	Description	Required?	
id	This is a shortname, uniquely identifying the application series. All versions with the same ID must be upgrade compatible.	Yes	
name	The friendly name of the application.	Yes	
description	A description of the application	No	
version	The application version	Yes	
category	The category in which the application should be grouped	Yes	
pgversion	The PostgreSQL version required (if any)	No	
edbversion	The EnterpriseDB version required (if any)	No	
format	If set to ‘exe’, the downloaded file will be executed directly. If set to ‘msi’, the installation will be started by calling msiexec. Other values may be used to handle different packaging systems in the future, e.g. ‘rpm’, ‘deb’.	Yes	
installoptions	Installation command options. These will be passed as options to the setup program by the Application Installer.	No	
upgradeoptions	Upgrade command options. These will be passed as options to the setup program by the Application Installer.	No	
checksum	MD5 checksum of the installation file.	Yes	
mirrorpath	Download path and file on the mirror network. This will have the root path to the selected mirror prepended to it. If omitted, custom download URLs must be specified.	Yes	
dependency	The ID of any applications that this application is dependent upon. This element may appear more than once.	No	
versionkey	The path to the registry key containing the package version number. Must be under HKEY_LOCAL_MACHINE.	Yes	

An XML file describing a single application might look something like:

<applications>
	<application>
		<id>pgrails</id>
		<name>Ruby on Rails</name>
		<description>The Ruby on Rails web development framework, preconfigured for use with PostgreSQL and EnterpriseDB</description>
		<version>1.85.22</version>
		<category>Web development</category>
<format>msi</format>
		<upgradeoptions>REINSTALLMODE=vamus REINSTALL=all</upgradeoptions>
		<checksum>e98018a6b562f1fa454e705dde2269de</checksum>
		<mirrorpath>/projects/pgfoundry/pgrails/pgrails-1.85.22.msi</mirrorpath>
		<versionkey>Software\pgRails\Version</versionkey>
	</application>
</applications>

4	
Application Installer
4.1	Overview

The Application Installer will be written as a wizard-style program using C++ and the wxWidgets framework to enable easy porting to non-Windows platforms in the future. Java will not be used as the PostgreSQL Installer is specifically designed not to require the user to install a Java Virtual Machine unless they wish to use pl/Java.

It is envisaged that the Application Installer will be included as a default installation option within the PostgreSQL Installer, that when installed, will automatically be run when the installer completes an installation. An icon will be included on the Start Menu to allow the user to run the installer as desired.

4.2	
Description of operation

When run, the Application Installer will behave as a ‘wizard’, guiding the user through the application selection and download process. Each step is detailed below.
4.2.1	Startup checks

When the program starts it will examine the system its running on to discover what installations of PostgreSQL or EnterpriseDB are present. PostgreSQL stores the following registry keys during installation, specifically to allow other applications to discover what was installed:

HKLM\Software\PostgreSQL\<Product Code>\Base Directory
HKLM\Software\PostgreSQL\<Product Code>\Data Directory
HKLM\Software\PostgreSQL\<Product Code>\Service ID
HKLM\Software\PostgreSQL\<Product Code>\Version

HKLM\Software\PostgreSQL\Services\<Service ID>\Data Directory
HKLM\Software\PostgreSQL\Services\<Service ID>\Database Superuser
HKLM\Software\PostgreSQL\Services\<Service ID>\Display Name
HKLM\Software\PostgreSQL\Services\<Service ID>\Encoding
HKLM\Software\PostgreSQL\Services\<Service ID>\Locale
HKLM\Software\PostgreSQL\Services\<Service ID>\Port
HKLM\Software\PostgreSQL\Services\<Service ID>\Product Code
HKLM\Software\PostgreSQL\Services\<Service ID>\Service Account

The two sets of registry keys are cross referenced to each other by Product Code and Service ID.

Note: It is recommended that EnterpriseDB’s Installer be modified to write the same information to the registry under HKLM\Software\EnterpriseDB. 

The user will be presented with the first screen of the wizard containing a brief introduction. The user will be asked to select the PostgreSQL or EnterpriseDB installation for which they wish to download an application, or if none were found, advised of that. Options will also be provided for non-installed versions of PostgreSQL to be used in the event that the user has a server installed on another machine.

The user will click Next to continue.
4.2.2	Application selection

The Application Installer will download the list of published applications from the PostgreSQL website. The applications appropriate for the version of PostgreSQL selected on the previous screen will be displayed, along with those that are version independent. The available options will be displayed in a tree control, e.g.

Development environments
	Ruby on Rails
	Apache & PHP
Content Management
	Drupal
	MySource Matrix

Upon selection of an application, the description will be displayed in a text label and the Next button will be enabled, allowing the user to continue.

4.2.3	Mirror selection

If the application selected is hosted on the PostgreSQL mirror network, a tree control will be displayed allowing the user to select a download site, eg:

UK
	ftp2.uk.postgresql.org (FTP)
USA
	ftp.us.postgresql.org (FTP)
	ftp2.us.postgresql.org (FTP)
	ftp2.us.postgresql.org (HTTP)

Following selection of a mirror, the Next button will be enabled, allowing the user to continue.
4.2.4	Download

The installer will display an introduction and allow the user to select a download directory if required. Upon clicking the Next the installer will begin to download the selected applications, giving the user visual feedback that the download is proceeding. When complete, the MD5 checksum of each downloaded file will be generated, and compared to that specified in the application data record. If it does not match, an error is raised and the installer returns the user to the Download screen after deleting the corrupt file. If the checksum matches, the wizard will continue to the Installation screen.
4.2.5	Installation

The installer will notify the user that the installation will begin once the Finish button is clicked. It will also inform the user where the download has been saved so they can archive or delete it once the installation has completed. 

When the user clicks the Finish button, the installer checks the MSI flags of the application data record and executes msiexec.exe using the following command line if it is set to ‘1’:

msiexec /i <downloaded filename> <installation command options>

Or if set to ‘0’, will execute the downloaded package using the command line:

<downloaded filename> <installation command options>

The installation command options are taken from the application data record downloaded from the PostgreSQL website. The following placeholders are used and replaced with values based on the users’ PostgreSQL/EnterpriseDB installation selection:

Placeholder	Will be replaced with	
$LOCAL	Set to 1 if the user selected an existing, local installation, else 0.	
$PATH	Installation path*	
$DATA	Data directory path*	
$VERSION	Server version	
$PORT	Server listen port number*	
$SERVICE	Service ID*	
$ACCOUNT	Service user account*	
$SUPER	Superuser name*	
$ENCODING	Default database encoding*	
$LOCALE	Locale setting*	

* Placeholders marked with an asterisk may be empty is $LOCAL is set to 0.

The installation command options allow application packagers to tailor their installations based on the local installations of PostgreSQL or EnterpriseDB to enable maximum ‘out of the box’ functionality and integration.

5	
Ruby on Rails
5.1	Overview

A Ruby on Rails development environment will be packaged as the first application to be distributed by the Application Installer. Ruby on Rails is a web application framework based on the Ruby language. It is designed to work with a number of DBMSs, including PostgreSQL and Oracle, however the default is MySQL. 

Ruby on Rails is an ideal package to use as a test application in this project for a number of reasons:

•	It is simple to setup and install, and doesn’t require a complex installer mechanism.

•	The existing Ruby on Rails installer (Instant Rails) is little more than a zip archive of Ruby, Rails, Apache and MySQL, without production quality integration.

•	Whilst the default DBMS is MySQL, the DBMS and the framework are not yet generally considered to be tied together in the way that, for example, PHP and MySQL are.

The package will be built as a Windows Installer MSI database using WiX.

5.2	
Application requirements

The Ruby on Rails application will be a properly packaged development environment, unlike Instant Rails. It will be streamlined to remove unnecessary components – for example, Rails includes a built in web server for development and testing, so there is no need to include Apache, which would only be needed on a production system (which Instant Rails certainly does not provide!).

5.2.1	Components

The components included will be:

•	Ruby

•	RubyGems

•	Rails

•	postgres-pr (the pure-Ruby PostgreSQL interface library).

5.2.2	Build

The Ruby, RubyGems, Rails and postgres-pr packages will be installed onto the build machine into a working PostgreSQL environment.

A rails_apps directory will be added, and a simple demonstration Rails application will be created within it, preconfigured for use with PostgreSQL.

The batch script used as a wrapper for the rails command will be modified to add the options –d postgresql to ensure that applications are created to run with PostgreSQL by default.

A Start Menu group will be added including shortcuts to the included documentation, the Ruby and Ruby on Rails websites, and a command prompt.