Liquibase: How to store PostgreSQL procedures and triggers

Liquibase is an open source, database-independent library for tracking, managing and applying database changes written in Java and distributed as JAR (Java archive). There are many tools to version code, most popular: SVN, GIT, Mercurial.

Many product managers faced the problem with versioning the database during application development. The solution is Liquibase.

Although tool is very usefull (it can track changes related with tables, views, columnt, indexes, foreigns), there are few limitations. One of these there is stored procedures. Liquibase cannot track stored procedures depends on database. The solution is to use custom query executon avaliable in liquibase.

To create custom stored procedure in Liquibase, just make a simple XML file (testChanges.xml):

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
    <preConditions>
      <dbms type="postgresql" />
    </preConditions>
    
    <changeSet author="Athan (generated)" id="1360329703893-1-1">
        <createProcedure>
CREATE OR REPLACE FUNCTION TestFunction() RETURNS trigger AS $proc$
BEGIN
  ...
END
$proc$ LANGUAGE plpgsql;
        </createProcedure>
    </changeSet>
</databaseChangeLog>

And type:

liquibase --changeLogFile testChanges.xml --url=jdbc:postgresql://localhost:5432/dbname --username=postgres --password=root update

Your procedure will appear in database.

WARNING! You should remember that Liquibase provides rollback to version, tag or count of changes. For custom SQL there are no rollback actions (such us for create table – drop table). You have to provide a rollback SQL.

So just add a <rollback> tag to yout changeSet:

        <rollback>
DROP FUNCTION TableOffersStateRealisedUpdate();
        </rollback>

Whole XML should look like:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
    <preConditions>
      <dbms type="postgresql" />
    </preConditions>
    
    <changeSet author="Athan (generated)" id="1360329703893-1-1">
        <createProcedure>
CREATE OR REPLACE FUNCTION TestFunction() RETURNS trigger AS $proc$
BEGIN
  ...
END
$proc$ LANGUAGE plpgsql;
        </createProcedure>
        <rollback>
DROP FUNCTION TableOffersStateRealisedUpdate();
        </rollback>
    </changeSet>
</databaseChangeLog>

Same case with triggers:

<changeSet author="Athan (generated)" id="1360329703893-2-1">
    <sql>
CREATE TRIGGER "tableoffersstaterealisedupdate" AFTER INSERT OR UPDATE ON "public"."offers"
FOR EACH ROW
EXECUTE PROCEDURE "tableoffersstaterealisedupdate"();
    </sql>
    <rollback>
DROP TRIGGER "tableoffersstaterealisedupdate" ON "public"."offers"
    </rollback>
</changeSet>

Hope this helped you.

 

Double-checked locking with Singleton pattern in Java

I just faced problem with synchronization many threads starting at the same time (with microseconds difference) and creating single object instance of connection to the database using a Singleton Pattern in Java. As a result I had many connections except one. The sent queries counter has been set to smaller value as excepted in simulations.

I have just Google’d the IBM article by Peter Haggar, Senior Software Engineer “Double-checked locking and the Singleton pattern”.

Problem overview

Creating an singleton in Java is simple to implement. There are two common ways to create singleton:

  1. Lazy loaded with create an private static field _instance filled by null (by default Java object initialization). The instance is created, when the static method getInstance() is called.
  2. Create an class instance in advance, just before class is loaded to memory by declaring a value of priate static field _instance by calling the private constructor new SingletonClass();

1st implementation with lazy initialization

package pl.athlan.examples.singleton;
 
public class Singleton {
	private static Singleton _instance; // null by default
 
	private Singleton() {
	}
 
	public static Singleton getInstance() {
		if(_instance == null) {
			_instance = new Singleton();
		}
 
		return _instance;
	}
}

2nd implementation with eager initialization

package pl.athlan.examples.singleton;
 
public class Singleton {
	private static Singleton _instance = new Singleton(); // object is created just after class is loaded into memory
 
	private Singleton() {
	}
 
	public static Singleton getInstance() {
		return _instance;
	}
}

Motivation.

Imagine two separated threads with is delegated to call getInstance() method at the same time.

Thread #1 Thread #2 value of _instance
Singleton.getInstance() null
Singleton.getInstance() null
if(_instance == null) null
if(_instance == null) null
_instance = new Singleton() [object #1]
_instance = new Singleton() [object #2]

As a result, two object has been created, because thread #2 hasn’t noticed the object creation.

If your object stores common data like a (in my case) database queries counter or the creation of the object is time-expensive when the system just hang out for many threads – this situation have not to occur.

Sloving the problem.

The problem slove is to synchronize the threads while accessing getInstace method. You can simply write:

public static synchronized Singleton getInstance()

but this solution produces an huge overhead to synchronize all threads calling this method. The better solution is to synchronize the fragment of code which checks an existance and creates the object in fact, except of returing if it already exists.

Finally solution:

package pl.athlan.examples.singleton;
 
public class Singleton {
	private volatile static Singleton _instance;
 
	private Singleton() {
	}
 
	public static Singleton getInstance() {
		if(_instance == null) {
 
			// causes that this block will be processed in sequence in parallel computing mode
			synchronized(Singleton.class) {
 
				// if previous sequence created the instance, just omit object creation
				if(_instance == null) {
					_instance = new Singleton();
				}
			}
		}
 
		return _instance;
	}
}

The volatile keyword assigned to _instance field provides the synchronization.

If there is no instance of the object, the synchronized block will begin. It means that all processes are queued to access that block. After access just ensure one more time, if the single object is not exists in fact, because the process doesn’t know what happened before it has rached the queue. If any process before queueing has created the object, just ommit the creation.

Hope it helped!

NOTE: Note that implementing Singleton by an ENUM is thread-safe and reflection-safe.

 

How to trim QUERY_STRING from mod_rewrite redirect

Quick issue = quick solution.

How to trim out a QUERY_STRING from mod_rewrite redirect?

RewriteEngine On
RewriteCond %{QUERY_STRING} z=([0-9]*)&x=([0-9]*)&y=([0-9]*)&r=mapnik
RewriteRule tiles.php http://a.tile.openstreetmap.org/%1/%2/%3.png [NC,L,R=301]

After accessing file:
tiles.php?z=6&x=35&y=21&r=mapink

you will be redirected to
http://a.tile.openstreetmap.org/6/35/21.png?z=6&x=35&y=21&r=mapink

The query string params are appended by default. To trim out the parameters, just place an ending ? (question mark) to redirect rule. That means, that there should be no parameters. Finally:

RewriteEngine On
RewriteCond %{QUERY_STRING} z=([0-9]*)&x=([0-9]*)&y=([0-9]*)&r=mapnik
RewriteRule tiles.php http://a.tile.openstreetmap.org/%1/%2/%3.png? [NC,L,R=301]
 

How to disable trash wordpress feature plugin

Howdy! Recently, I have been working a lot with WordPress engine, modyfying behind the site by writing plugins. I wonder to share my knowledge I collected under The Wordrpess Optimalizations tag, where a lot of simple tricks will be published. I also know that in the web there are a few of advises related with turning WordPress optimized and combination with your’s flair, the WordPress can become a powerfull, huge toll able to build simply sites as well as advanced solutions. The main advantage of WordPress is well developed backend, so you don’t have to care about beautiful and simply way to publish content and concentrate in frontent, striving to make a website much friendly to end user.

Unfortunately, the WordPress has many features that in common are not required in your project. Obviously, it generates relatively huge overhead to server, database and so one.

This post describes how to disable the “Trash” feature, which has been introduced in version 2.9 aimed to keep posts until permament deletion, such as deleting files in operating system. Deleted items are not deleted permanently from the database in fact, instead they appears in “Trash” by marking them as deleted, setting the flag.

Disabling trash manually

To disable trash in wordpress, you can simply define the constant EMPTY_TRASH_DAYS, for example in your wp-config.php.

define('EMPTY_TRASH_DAYS', 0);

From now, all options “Move to trash” will no longer apperar in admin pages, the functionality is just disabled. Just… but copies of posts marked as deleted are still in the database. To truncate them, just execute the sql statement:

DELETE p, p_rel, p_meta
 FROM wp_posts p
 LEFT JOIN wp_term_relationships p_rel ON (p.ID = p_rel.object_id)
 LEFT JOIN wp_postmeta p_meta ON (p.ID = p_meta.post_id)
 WHERE p.post_status = 'trash'

All posts marked as “Move to trash” will be deleted, and theirs post meta (custom fields) and taxonomy relations will be truncated too. The database now is clean.

Writing simple plugin.

We will write a simple plugin. In the /wp-content/plugins/MyPlugin/ create a file plugin.php, and the code within:

define('EMPTY_TRASH_DAYS', 0);
 
register_activation_hook(__FILE__, function() {
 global $wpdb;
 
$query = "DELETE p, p_rel, p_meta
 FROM " . $wpdb->posts . " p
 LEFT JOIN " . $wpdb->term_relationships . " p_rel ON (p.ID = p_rel.object_id)
 LEFT JOIN " . $wpdb->postmeta . " p_meta ON (p.ID = p_meta.post_id)
 WHERE p.post_status = 'trash'";
 
$wpdb->query($query);
});

The Trash feature is disabled by setting the EMPTY_TRASH_DAYS constant, and posts moved to trash will be deleted while plugin activation. We used register_activation_hook function with anonymous function javascript-like callback function (PHP 5.3).

Download the plugin

The plugin compiled to be ready to install is available here:

Hope the post will be helpfull.

 

How to send template and layout mail with Zend_Mail

Zend_Mail provides a great and simple in use and configuration mechanizm to send emails. The problem begins when you would like to specify fully templated and layouted messages.

In my current project I have several kinds of mails: customer invoices and messages, users notifications, admin notifications, webmaster email about critical errors in scheduled system tasks. In this case the Zend_Layout fits perfectly to redner rich text content by Zend_View, but it is implemented in Zend_Mail, wchih provies simply setBodyText() and setBodyHTML() methods.

This inconvenience is understandable by the way, mainly in context simpe, clear and flexible extendable code of Zend Framework. We will strive to extend the functionality od Zend_Mail following ZF developers concepts.

Overview

Writing class extending Zend_Mail I kept a several concepts:

  • Messages should be both templated and layouted using Zend_View and Zend_Layout.
  • The email view scripts (templates) there are in main view scripts directory nested in subdirectory (as deep as you want).
  • … The same path story with layouts.
  • You can use this object excatly the same way as Zend_Mail. It behaviour the same way as parent until you set special options (like point to view script path or file to render in body).
  • … and object should keep Zend_Mail fluent interface (returning $this in setters) to provide method chaining fluent interface.
  • Pointed view file is rendered as a mail body.
  • You can use this object excatly the same way as Zend_Mail. It behaviour the same way as parent until you set special options (like point to view script path or file to render in body).

Zend_Mail application.ini configuration and extending application

Simply paste several lines to application.ini configuration, theare are self-commented, description is not neccessary at this point. We will use SMTP transport:

resources.mail.transport.type = smtp
resources.mail.transport.host = YOUR_HOSTNAME
resources.mail.transport.auth = login
resources.mail.transport.username = "YOUR_ACCOUNT"
resources.mail.transport.password = "YOUR_PASSWORD"
resources.mail.transport.register = true
 
resources.mail.defaultFrom.email = YOUR_ACCOUNT
resources.mail.defaultFrom.name = "MyService.com"
resources.mail.defaultReplyTo.email = YOUR_ACCOUNT
resources.mail.defaultReplyTo.name = "MyService.com"

In addition we will create tho additional directories and files:

  • /application/views/scripts/email/–  just add a subdirectory /email to existing view scripts directory.
  • /application/layouts/scripts/email/ – the same story as above
TIP: I have moved default configured layouts direcotry to /application/views/layouts/ to unify structure of application. Just change in application.ini this line:
resources.layout.layoutPath = APPLICATION_PATH "/modules/default/views/layouts/scripts/"

To test our class let’s create additional two files:

/application/layouts/scripts/email/html.phtml

< ?php echo $this->layout()->content ?>
<p>--<br />Best Regards,<br />MyService.com</p>

and scond one, the information about successfull account register with your own content:

/application/views/scripts/email/AccountRegister.phtml

<p>Thanks for register.</p>

ZentUtil_Mail class usage

Before we will write a code, let’s think abous its usage, wchih should be the same as in Zend_Mail documentation witch additional methods:

$mail = new ZendUtil_Mail('utf-8');
$mail->addTo('athlanster@gmail.com', 'Piotr Pelczar');
$mail->setSubject('Testowy mail z zenda');
$mail->setBodyView('AccountRegister.phtml');
$mail->send();

Above code should send mail from AccountRegister.phtml view script nested in html.phtml layout.

If you want to change layout simply call setViewLayoutScript($script) method with string or set false to disable layouts. For change paths setViewPathDirectory($path), setViewLayoutPathDirectory($path) are available.

ZendUtil_Mail extends Zend_Mail

ZendUtil_Mail has been extended by Zend_Mail and _prepareBody() method has been added. It is called just before parent::send() method.

NOTE: I have added ZendUtil_ namespace.

I hope it will help.

 

How to get single error message with Zend_Validate_EmailAddress validation

I have just started introducing Zend Framework when I had to face the problem with output multiple error messages in form while email address validation. Checking domain (whith is enabled by default) causes additional error messages indicates anomalies in hostname segment of provided by user email address. In result, we receive several errors assigned to one email field.

The problem is easy to slove by overriding default behaviour of Zend_Validate_EmailAddress clearing all messages generating while validation and setup a new single error message.

Simply add namespace MyOwn_ for own needs and provide class in file /libraries/MyOwn/Validate/EmailAddess.php

class MyOwn_Validate_EmailAddress extends Zend_Validate_EmailAddress
{
  const INVALID = 'emailAddressInvalid';
 
  protected $_messageTemplates = array(
    self::INVALID => "Invalid Email Address."
  );
 
  public function isValid($value)
  {
    parent::setOptions(array(
      'allow' => Zend_Validate_Hostname::ALLOW_DNS,
      'domain' => true,
      'mx' => true,
      'deep' => true)
    );
 
    if(!parent::isValid($value)) {
      $this->_messages = array(); // clear all previous messages
      $this->_error(self::INVALID);
      return false;
    }
 
    return true;
  }
}

And provide above custom validator to form element in /application/forms/AccountRegister.php:

class Form_AccountRegister extends Zend_Form
{
  public function init()
  {
    $this->setMethod('post')
         ->setName('Account_Register');
 
    $email = new Zend_Form_Element_Text('email');
    $email
      ->setLabel('Email address')
      ->addValidator(new ZendUtil_Validate_EmailAddress)
      ->setRequired(true);
 
    $this->addElement($email);
 
  }
}

NOTE:

  • In addition, you can simply translate the emailAddressInvalid message.
  • For sticklers, setting options in isValid method is hardcoded with look like a messy code, but it is quick-fix

Hope it will help.