Sometimes, during the changes inevitable in the progress of a PostgreSQL-based project, the ownership of a database or the tables in a database must change.

Should I need to create a new user, I can simply write:

CREATE USER mynewuser WITH PASSWORD 'mypassword';

Once the user is created, I am free to issue a series of SQL statements similar to this:

ALTER TABLE myschema.mytablename OWNER TO mynewuser;

For a small database, this will not take long. But for a large application with hundreds of tables, this could be tedious (at best). Fortunately, PostgreSQL has very nice introspection tools to make this process much easier.

CREATE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION change_ownership(text, text, text)
RETURNS TEXT AS $$
BEGIN
execute 'ALTER TABLE ' || $1 || '.' || $2 || ' OWNER TO ' || $3 || ';';
RETURN 'OWNER CHANGED TO ' || $3 || ' FOR ' || $1 || '.' || $2;
END;
$$ LANGUAGE 'plpgsql';

SELECT
change_ownership('myschema', table_name, 'mynewuser')
FROM information_schema.tables WHERE table_schema='myschema';

Creating a developer-focused blog application turned out to be a great learning experience. I am generally happy with the design and layout, but there seems to be a glaring omission: the urls to specific articles are not terribly search-engine friendly. So, I want to add a database table that properly maps the public url to the private application module invocation. Clearly, I already have several articles written, for which there is no search-engine friendly url. That means I also need to populate that new database table with appropriate information for existing articles. I can use the patch system to deploy these changes once they are ready.

SQL Patch

The first part of the patch is the creation of a redirect table to handle URL mapping and some hand-coded entries.

Partial Listing: /dblog/shell/sql/2.2.sql

SET client_min_messages = warning;

CREATE SEQUENCE redirect_seq START 1;
CREATE TABLE redirect (
    redirect_id integer default nextval('redirect_seq'),
    public_path text not null,
    application_module varchar(96) not null,
    identifier varchar(4),
    identifier_value varchar(32) 
);
ALTER TABLE redirect ADD CONSTRAINT pkey_redirect PRIMARY KEY (redirect_id);

INSERT INTO redirect VALUES (DEFAULT, 'dblog', 'BlogViewer', NULL, NULL);

PHP Patch

I have modified the save-related methods in several model objects to add entries to the redirect table when appropriate (i.e., when the applicable entry does not yet exist). I have also updated ApplicationDelegate to handle redirects. With that code written (and which will be updated via subversion as the patch executes), I can now create my PHP patch.

Listing: /dblog/shell/php/2.2.php

<?php

require "constants.php";

// Update redirect with project entries
$select = new DBOMSelect('project');
$projects = DBOMFactory::instanceArray('Project', $select);
foreach ($projects as $project)
{
    $project->save();
}

// Update redirect with category entries
$select = new DBOMSelect('category');
$categories = DBOMFactory::instanceArray('Category', $select);
foreach ($categories as $category)
{
    $category->save();
}

// Update redirect with article entries
$select = new DBOMSelect('article');
$articles = DBOMFactory::instanceArray('Article', $select);
foreach ($articles as $article)
{
    $article->save();
}

?>

Executing the Patch

With the SQL and PHP patch files ready, I can now execute the shell script that applies the patch.

markf$ cd ~/Sites/dblog/shell
markf$ sh apply_patch.sh 2.2
Updating framework via subversion...
Updating application via subversion...
Applying PostgreSQL patch component...
Applying PHP patch component...

Another Approach

Notice that in the SQL patch, I directly inserted a record. In many cases, I prefer to instead use the database object architecture. In that case, I would avoid INSERT INTO statements in the SQL patch file and instead create (and save) redirect instances in the PHP patch file.

Imaginary Partial Listing: /dblog/shell/php/2.2.php

<?php

...

// Create specific Redirect entries
$redirect = new Redirect;
$redirect->setPublicPath('dblog')
         ->setApplicationModule('BlogViewer');
$redirect->save();

?>

One inevitable activity in any web application is updates. I am always refactoring code, adding new features, and improving existing features. I get everything working the way I want in my development environment and then need to deploy the changes. Typically, that deployment has three components: update the scripts via subversion, modify the database, and use php to update database records.

To address this problem, I want to create a lightweight patch system for my applications, and the developerBlog is a good target for experimenting with this idea. Below is a skeleton of the directory strucuture I want.

Directory Listing: /dblog/shell

/dblog/shell
    apply_patch.sh
    conf/
        sh.conf.dist
    php/
        constants.php.dist
    sql/

The patch components will reside in an applicable directory. For example, a patch (that I will arbitrarily name 2.1) has only an SQL component. In that case, there should be a file called 2.1.sql in the sql directory.

I envision a couple configuration files. One (conf/sh.conf) helps apply_patch.sh execute the right versions of PostgreSQL and PHP, and identifies the paths to key directories. The second configuration file (php/constants.php) bootstraps the Istarel Workshop Frameworksto to any PHP script so that I have complete access to the framework and application class hierarchies.

Subversion

Notice that the configuration files both end in .dist. The idea here is that I want to be able to run the patches in my development, staging, or production environment. Clearly, the path and program names and paths are likely to be different in each environment, but I want to have these distributed versions available as guides. Thus, the skeleton as shown is made part of the subversion repository for the project.

markf$ svn add shell
markf$ cp shell/conf/sh.conf.dist shell/conf/sh.conf
markf$ cp shell/php/constants.php.dist shell/php/constants.php
markf$ svn propset svn:ignore sh.conf shell/conf
markf$ svn propset svn:ignore constants.php shell/php

In order for the local environment constants to be respected, I make sure that sh.conf and constants.php are not part of the subversion repository.

Patch Application

The name of the patch will be passed as an argument to apply_patch.sh. The shell script then executes a subversion update for the frameworks and the application. If there is an appropriate SQL or PHP patch, those are applied as well.

Listing: /dblog/shell/apply_patch.sh

#!/bin/bash
. conf/sh.conf

# identify the patch being applied
if [ ! $1 ]
then
    echo "Patch not defined"
    exit
fi

# apply subversion update to framework
echo "Updating framework via subversion..."
cd $FRAMEWORK_DIR
svn up

# apply subversion update to application
echo "Updating application via subversion..."
cd $PROJECT_DIR
svn up
cd shell

# apply database patch
if [ ! -f sql/$1.sql ]; then
    echo "No database patch component"
else
    echo "Applying PostgreSQL patch component..."
    $PSQL -q -U $DATABASE_USER -f sql/$1.sql -d $DATABASE_NAME
fi

# apply PHP patch
if [ ! -f php/$1.php ]; then
    echo "No PHP patch component"
else
    echo "Applying PHP patch component..."
    $PHP $PROJECT_DIR/shell/php/$1.php
fi

This is a bare bones approach to the problem, and future work may require me to chance its structure. For example, I may want to add a switch that tells the script to ignore the step for updating the framework. A subsequent blog entry will demonstrate how to apply an actual patch to the developerBlog.

IWDatabase UML diagram

One of the most common (and useful) design patterns is the Singleton. The idea is simple: you often only want one instance of a particular resource, and the Singleton pattern makes that possible. For the frameworks, we want to ensure that there is always only one active connection to our PostgreSQL database (for any given client).

PHP Singletons

In an object-oriented PHP application, a singleton is accomplished by making the constructor function a private method while providing a public class method to access the desired resource. The trick is for this public class method to have a static variable that represents the singleton. (Remember, in PHP, a static variable only exists in the function where it is declared, but it keeps its value even after the program execution leaves the function.)

IWDatabase

The root database class in the IWFrameworks is IWDatabase: it is responsible for managing the connection to the back-end database (using the Singleton Design Pattern) and for handling specific types of data requests. Note that each database type (e.g., PostgreSQL or MySQL) has its own adapter, which is responsible for the low-level PHP database calls.

/fw/database/IWDatabase.php

<?php

class IWDatabase
{
    protected $database_connection;
    
    private function __construct() { }
    
    public static function databaseConnection($type, $hostname, $database,
                                              $username, $password)
    {
        static $singleton;
        
        if (! isset($singleton[$database]))
        {
            $adapter_class = IWDatabase::databaseAdapterClass($type);
            $singleton[$database] = new $adapter_class;
            $singleton[$database]->initialize($hostname, $database, 
                                              $username, $password);
        }
        return $singleton[$database];
    }
    
    public static function databaseAdapterClass($type)
    {
        switch ($type)
        {
            case 'pgsql':    return 'DBOMPostgreSQLAdapter';
            case 'mysql':    return 'DBOMMySQLAdapter';
        }
    }
}

?>

Singleton Subclass

One feature of the IWFrameworks is a series of application helpers: classes and objects that act as gateways to the underlying framework classes and objects. These helpers exist mostly for convenience, implementing methods that nearly any application utilizing the frameworks will need. ApplicationDatabase is an example of such a helper: while IWDatabase is versatile enough, it requires the database information to be passed as arguments each time. ApplicationDatabase works by expecting the presence of four application constants (representing the database host, name, username, and password). How this integration happens in my particular frameworks isn't important: what is important is that this approach demonstrates how you can subclass a Singleton implementation.

Listing: /fw/database/ApplicationDatabase.php

<?php

class ApplicationDatabase extends IWDatabase
{
    public static function databaseConnection()
    {
        return parent::databaseConnection(DB_TYPE, DB_HOST, DB_NAME, 
                                          DB_USERNAME, DB_PASSWORD);
    }
}

?>