The Plan

• A story
• Problems of schema updates
• Downtime and zero downtime
• More complex example

A Story

• The CPAN Pull Request Challenge followed by the Pull Request Club
• In the first month, I was assigned PRC, the project itself!

A Story (2)

A Story (3)

The issue I selected.

Indeed, loading the page took several seconds.

A Story (4)

Implementation

DBIx::Class::Core

__PACKAGE__->add_columns(
  "user_id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
  "create_time",
  {
    data_type     => "datetime",
    default_value => \"current_timestamp",
    is_nullable   => 0,
    set_on_create => 1,
  },
  "update_time",
  {
    data_type     => "datetime",
    default_value => \"current_timestamp",
    is_nullable   => 0,
    set_on_update => 1,
  },
  "last_login_time",
  {
    data_type     => "datetime",
    default_value => \"current_timestamp",
    is_nullable   => 0,
  },
  "tos_agree_time",
  { data_type => "datetime", default_value => \"null", is_nullable => 1 },
  "tos_agreed_version",
  { data_type => "datetime", default_value => \"null", is_nullable => 1 },
  "scheduled_delete_time",
  { data_type => "datetime", default_value => \"null", is_nullable => 1 },
  "is_deactivated",
  { data_type => "boolean", default_value => 0, is_nullable => 0 },
  "assignment_level",
  { data_type => "integer", default_value => 0, is_nullable => 0 },
  "assignee_level",
  { data_type => "integer", default_value => 0, is_nullable => 0 },
  "github_id",
  { data_type => "integer", is_nullable => 0 },
  "github_login",
  { data_type => "varchar", is_nullable => 0, size => 128 },
  "github_email",
  { data_type => "varchar", is_nullable => 0, size => 256 },
  "github_profile",
  { data_type => "varchar", is_nullable => 0, size => 256 },
  "github_token",
  {
    data_type => "varchar",
    default_value => \"null",
    is_nullable => 1,
    size => 256,
  },
);

A Story (4)

The Patch

DBIx::Class::Core

diff --git a/lib/PRC/Schema/Result/User.pm b/lib/PRC/Schema/Result/User.pm
index a28dfa0..cc4311b 100644
--- a/lib/PRC/Schema/Result/User.pm
+++ b/lib/PRC/Schema/Result/User.pm
@@ -72,6 +72,8 @@ __PACKAGE__->add_columns(
     is_nullable => 1,
     size => 256,
   },
+  "last_repos_sync",
+  { data_type => "datetime", default_value => \"null", is_nullable => 1 },
 );
 
 __PACKAGE__->set_primary_key("user_id");
@@ -309,6 +311,9 @@ Returns undef if something went wrong.
 sub fetch_repos {
   my ($user) = @_;
 
+  return 1 if $user->last_repos_sync
+    && $user->last_repos_sync > DateTime->now->add(days=>-1);
+
   my @existing_repos = $user->repos;
   my $fetched_repos  = PRC::GitHub->get_repos($user->github_token);
   return undef unless defined $fetched_repos;
@@ -333,6 +338,8 @@ sub fetch_repos {
     }
   }
 
+  $user->update({ last_repos_sync => DateTime->now->datetime });
+
   return 1;
 }
 
diff --git a/prc.sql b/prc.sql
index 26b4556..d3a8874 100644
--- a/prc.sql
+++ b/prc.sql
@@ -15,7 +15,8 @@ CREATE TABLE IF NOT EXISTS user (
   github_login           VARCHAR(128) NOT NULL,
   github_email           VARCHAR(256) NOT NULL,
   github_profile         VARCHAR(256) NOT NULL,
-  github_token           VARCHAR(256) DEFAULT NULL
+  github_token           VARCHAR(256) DEFAULT NULL,
+  last_repos_sync        DATETIME DEFAULT NULL
 );
 
 CREATE TABLE IF NOT EXISTS repo (

A Story (5)

ALTER TABLE

• For an already deployed app, we need

  ALTER TABLE user
  ADD COLUMN last_repos_sync DATETIME DEFAULT NULL;
  

• But there was no place to insert it in!
• So I put it into the commit message.

A Story (6)

# The implementation of the "Reload" button isn’t important for this talk

Existing Tools

sqitch

• Written in Perl
• Would store just the ALTER TABLE
• PRC was migrated to it 4 commits later
• (Question for sqitch users)
  • Do you keep the initial state forever, accumulating the changes, or can you tell sqitch to reset to the actual state and start recording changes again?

… and probably many more

Changing a Running App

What happens when we alter a table while the app is running?

• The application might crash, returning a 500 back to the user
• The data might get corrupt
• The data might get lost without notice
• Or, if we were lucky, everything could just work

  

General Problem

• There might be schema changes other than ALTER TABLE
• A database doesn’t have to be involved at all

Downtime

• Make sure the old and new code never meets.
• Before applying the change, make sure all the systems are switched off.
  

Downtime Not Possible

There are services that can’t be switched off easily.

• They’re critical
• The customer pays for that

Zero Downtime

• Backward compatible changes
• Proceed in steps

The Steps

1. Accept data conforming to both the old and new schema
2. Start producing the data conforming to the new schema
3. Stop supporting the old schema
   ??

A More Complex Case

We store our customers’ data.

• Several different VMs
• 3 components of an update:
  • Code change
  • Offline update
  • Live update

A More Complex Case

We store our customers’ data.

1. Actual change in the data schema (backward compatibility!)

A More Complex Case

We store our customers’ data.

1. Actual change in the data schema (backward compatibility)
2. Start accepting the metadata conforming to the new schema

A More Complex Case

We store our customers’ data.

1. Actual change in the data schema (backward compatibility)
2. Start accepting the metadata conforming to the new schema
3. Treat the new schema as valid (integration tests)

A More Complex Case

We store our customers’ data.

1. Actual change in the data schema (backward compatibility)
2. Start accepting the metadata conforming to the new schema
3. Treat the new schema as valid
4. Start producing the metadata conforming to the new schema

A More Complex Case

We store our customers’ data.

1. Actual change in the data schema (backward compatibility)
2. Start accepting the metadata conforming to the new schema
3. Treat the new schema as valid
4. Start producing the metadata conforming to the new schema
5. Convert stored data to the new schema (no code change)
6. Cleanup

Metadata Object

• Moose
• Methods to serialise and deserialise

package MD::Column;
use Moose;

sub serialise   { ... }
sub deserialise { ... }

no Moose;
__PACKAGE__->meta->make_immutable

Metadata Update Phase 1

• Add the role for the update phase
• Declare both the new and old methods

package MD::Column;
use Moose;

with 'MD::Update::Phase1';

sub serialise_original   { ... }
sub serialise_updated    { ... }

sub deserialise_original { ... }
sub deserialise_updated  { ... }

no Moose;
__PACKAGE__->meta->make_immutable

Metadata Update Phase 2

• Just change the role
• The rest of the code remains the same

package MD::Column;
use Moose;

with 'MD::Update::Phase2';

sub serialise_original   { ... }
sub serialise_updated    { ... }

sub deserialise_original { ... }
sub deserialise_updated  { ... }

no Moose;
__PACKAGE__->meta->make_immutable

The Update Role

package MD::Update::Phase1;
use Moose::Role;

requires qw{ serialise_original   serialise_updated
             deserialise_original deserialise_updated
             is_updated };

has should_use_updated => ( is => 'rw', isa => 'Bool', default => 0 );

sub serialise {
    my ($self) = @_;
    return $self->should_use_updated ? $self->serialise_updated
                                     : $self->serialise_original
}

sub deserialise {
    my ($self, $structure) = @_;
    my $is_updated = $self->is_updated($structure);
    $self->should_use_updated($is_updated);
    return $is_updated ? $self->deserialise_updated($structure)
                       : $self->deserialise_original($structure)
}

__PACKAGE__

The Update Role (2)

package MD::Update::Phase2;
use Moose::Role;

requires qw{ serialise_original   serialise_updated
             deserialise_original deserialise_updated
             is_updated };



sub serialise {
    my ($self) = @_;
    return $self->serialise_updated
}


sub deserialise {
    my ($self, $structure) = @_;
    return $self->is_updated($structure)
        ? $self->deserialise_updated($structure)
        : $self->deserialise_original($structure)
}

__PACKAGE__

The Cleanup

• In the cleanup phase, the Metadata Objects drops the role and returns to only one serialisation and one deserialisation method.

Release Procedure

• 5 releases needed to deliver a single change
• “Feature Hotfix”