Cool service of the day: Web Sequence Diagrams

Enforcing strict database DDL upgrade version requirements in Postgresql

When delivering a upgrade version of a web-based product, you often wish to provide your clients with database upgrade scripts.

As the data in the database is the most critical part of the installation, both the upgrader and the developer want to be absolutely certain that upgrades are pushed into the database in the correct order.

This is an example of how to achieve this objective using Postgresql.

BEGIN;

CREATE FUNCTION require_database_version(IN p_version INT) RETURNS boolean AS $$
DECLARE
  dv INT;
BEGIN
  IF p_version IS NULL THEN
    RAISE EXCEPTION 'require_database_version called with NULL input';
  END IF;
  SELECT version INTO STRICT dv FROM database_version;
  IF dv <> p_version THEN
    RAISE EXCEPTION 'Database version required: %. Found %.', p_version, dv;
  END IF;
  RETURN true;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE FUNCTION set_database_version(IN p_version INT) RETURNS boolean AS $$
BEGIN
  IF p_version IS NULL THEN
    RAISE EXCEPTION 'set_database_version called with NULL input';
  END IF;
  EXECUTE 'CREATE OR REPLACE VIEW database_version AS SELECT ' || p_version || ' AS version';
  RETURN true;
END;
$$ LANGUAGE plpgsql VOLATILE;

SAVEPOINT s1;
SELECT require_database_version(4);
ROLLBACK TO s1;
SAVEPOINT s2;
SELECT set_database_version(6);
SAVEPOINT s3;
SELECT require_database_version(4);
ROLLBACK TO s3;
SELECT require_database_version(6);
ROLLBACK TO s3;
SELECT require_database_version(NULL);
ROLLBACK;

Setting up a Flash testing environment with several versions of the Flash 9 and 10 plugins

When you're developing a Flash application, you usually want to be reasonably certain that it'll work with the various versions of the Flash plugin which your users might have. The best way to be certain is to see for yourself.

Alas, the Flash plugin was designed to be installed only once per PC, and Adobe wants you as a consumer to always have only the newest version of the plugin installed.

Adobe, however, have provided us with all the tools we need to build a Firefox installation which contains all of the Flash 9 and Flash 10 plugins in separate profiles, so that we can even run the various versions simultaneously.

IE, as usual, is a different kettle of fish altogether, and generates extra customer costs even here.

Let's start by visiting Adobe's page "Archived Flash Players available for testing purposes" at http://www.adobe.com/go/tn_14266.

From there, download the files http://fpdownload.macromedia.com/get/flashplayer/installers/archive/fp10_archive.zip and http://fpdownload.macromedia.com/get/flashplayer/installers/archive/fp9_archive.zip. If the filenames have changed since writing this blog post, you'll probably be able to find the correct files from the Adobe KB page referenced earlier.

These .ZIP files contain all of the .EXE installation files needed to get to the actual plugin files we'll be needing. Create a working directory, and unzip the fp*_archive.zip-files there.

Here's a small shell script you can run under Linux to extract the plugin files from the installation executables, using Wine:

export WINEPREFIX=$PWD/wine
mkdir $WINEPREFIX
mkdir flash_plugins

for file in fp*archive/*/flashplayer*_win.exe
do
  version=`echo $file | awk -F/ '{print $2}'`
  echo $file $version
  wineconsole $file /S /D=C:\\flash\\$version
  cp -r wine/drive_c/flash/$version flash_plugins/
done

... to be continued!