Versioning with two tables

From ClassDBI

Contents 1 Summary 2 Schema 3 Page Edition Class 4 Page Class

Summary

Here's an approach to versioning that uses two tables and database rules (similar to triggers) along with locking and a custom update method. Perhaps CDBI triggers could be used instead of the rules.

The basic idea is that we have two objects, Page and PageEdition. Page is the current state of the page, and each PageEdition is the state of the page at some point in time. Whenever a Page is created or updated, a corresponding change is made to the PageEdition table. The custom update method is responsible for making sure that the version being updated is the latest version, and for incrementing the serial number.

This example uses RULEs and SELECT FOR UPDATE, which are Postgres-specific features and may not have analogues in other databases. This code was written for CDBI 0.96 and has never been tried with any more recent version. This code was edited for simplicity while writing this page and has never been tested in this exact form.

--Aneel Nazareth 07:19, 15 March 2006 (GMT)

Schema

We have two identical tables with different constraints. This one holds just the current state.

CREATE TABLE page (
	title		TEXT NOT NULL,
	serial		INT4 DEFAULT 1 NOT NULL, 
	modified	TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
	author		INT4 NOT NULL,
	body		TEXT
);
CREATE UNIQUE INDEX pk_page ON page (title);

This one holds the history. Notice the looser UNIQUE INDEX constraint.

CREATE TABLE page_edition (
	title		TEXT NOT NULL,
	serial		INT4 DEFAULT 1 NOT NULL, 
	modified	TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
	author		INT4 NOT NULL,
	body		TEXT
);
CREATE UNIQUE INDEX pk_page_edition ON page_edition (title, serial);

This rule creates an edition when a new page is created. This is a DO rule, not a DO INSTEAD rule, so the insert into the page table happens as well as the insert into the page_edition.

CREATE RULE page_insert AS ON INSERT TO page
    DO
    INSERT INTO page_edition (title, serial, modified, author, body)
         VALUES (NEW.title, NEW.serial, NEW.modified, NEW.author, NEW.body);

This rule creates an edition when a page is updated. This is also a DO rule, not a DO INSTEAD rule, so the update on the page table happens as well as the insert into the page_edition.

CREATE RULE page_update AS ON UPDATE TO page
    DO
    INSERT INTO page_edition (title, serial, modified, author, body)
         VALUES (NEW.title, NEW.serial, NEW.modified, NEW.author, NEW.body);

Page Edition Class

A simple class. It represents a single, dated, numbered edition of the page. There should perhaps be some mechanism that prevents these objects from being changed. A custom update method that just croaks?

package Loath::Wiki::PageEdition;

use base Loath::Wiki::CDBI;
use Loath::Date;

__PACKAGE__->table('page_edition');
__PACKAGE__->columns(Primary => qw/title serial/);
__PACKAGE__->columns(All => qw/modified author body/);

__PACKAGE__->has_a(modified => 'Loath::Date', deflate => 'as_string');

__PACKAGE__->has_a(author => 'Loath::Users::User');

1;

Page Class

This class is slightly more complex. It represents the edition of the page with the most recent changes. When we update, we use a set_sql query to get a lock on the most recent version of the row we're trying to modify, then we compare it with the row we've just tried to update. If there are no conflicts, we increment the serial number and let the update happen.

package Loath::Wiki::Page;

use base Loath::Wiki::CDBI;
use Loath::Date;

__PACKAGE__->table('page');
__PACKAGE__->columns(All => qw/title author body/);
__PACKAGE__->columns(Volatile => qw/serial modified/);

__PACKAGE__->has_a(modified => 'Loath::Date', deflate => 'as_string'); 

__PACKAGE__->add_trigger(before_update => 
			 sub { my $self = shift;
			       $self->modified("now") });

__PACKAGE__->has_a(author => 'Loath::Users::User');

__PACKAGE__->has_many(editions => [ 'Loath::Wiki::PageEdition' => 'title' ]);

__PACKAGE__->set_sql(lock => <<"");
    SELECT __ESSENTIAL__
      FROM __TABLE__
     WHERE __IDENTIFIER__
       FOR UPDATE

sub update {
    my ($self) = @_;
    
    # Start transaction
    local $self->db_Main->{ AutoCommit };
    
    # allow duplicates in memory
    $self->remove_from_object_index;
    
    my $latest = $self->search_lock($self->id)->first;
    
    my $nonvolatile_changed = 0;
    foreach my $column ($self->is_changed) {
	next if grep { $_ eq 'Volatile' } $self->find_column($column)->groups;
	# warn "nonvolatile change: $column";
	$nonvolatile_changed = 1;
	last;
    }
    
    if ($nonvolatile_changed) {
	
	if ($latest->serial > $self->serial) {
	    # Possible conflict. There's a version newer than the one we're updating
	    # warn "serial mismatch";
	    
	    my @conflicts;
	    foreach my $column ($self->columns) { # consider $self->is_changed instead
		next if grep { $_ eq 'Volatile' } $self->find_column($column)->groups;
		if ($self->get($column) ne $latest->get($column)) {
		    push(@conflicts, $column);
		}
	    }
	    
	    if (@conflicts) {
		# warn "conflict";

		# set this serial to the latest serial, since we've considered the changes from latest
		# if we call update a second time, the changes will go through.
		$self->serial($latest->serial);
		$self->_croak("Possible conflict. Object updated at " . 
			      $latest->modified .
			      ". Please compare values and resubmit.",
			      latest => $latest,
			      conflicts => \@conflicts
			     );
	    } else {
		# The latest version is the same as the one we're updating
		# warn "no conflicts";
	    }
	} else {
	    # The version we updated was the latest
	    # warn "serials match";
	    # Perhaps the SUPER::update should go here
	}
    
    } else {
	# warn "no nonvolatile changes";
    }
    
    # Having the SUPER::update here instead of above allows the creation
    # of new editions that only differ by their serial and modified date

    $self->serial($latest->serial + 1);
    # warn "serial: " . $self->serial;

    $self->SUPER::update(@_);
    
}

1;