Aside: CRAZY SQL

This is the sql that gets run before deleting a single row

INSERT INTO shadow_tracks( rel_shadow_cds_lifecycle, shadow_stage, shadow_timestamp, shadow_val_position, shadow_val_title, shadowed_curpk_cd_id, shadowed_curpk_position, shadowed_lifecycle )
  VALUES( (
        SELECT me.rel_shadow_cds_lifecycle
          FROM shadow_tracks me
        WHERE shadowed_lifecycle = (
            SELECT shadows.shadowed_lifecycle
              FROM tracks me
              JOIN shadow_tracks shadows
                ON shadows.shadowed_curpk_cd_id = me.cd_id AND shadows.shadowed_curpk_position = me.position
            WHERE cd_id = '4' AND position = '1'
              LIMIT '1'
           )
          LIMIT '1'
       ), '0', '13086697951239', '1', 'dear_mr_president', '4', '1', (
        SELECT shadows.shadowed_lifecycle
          FROM tracks me
          JOIN shadow_tracks shadows
            ON shadows.shadowed_curpk_cd_id = me.cd_id AND shadows.shadowed_curpk_position = me.position
        WHERE cd_id = '4' AND position = '1'
          LIMIT '1'
       ) )

Can't I just use DBIx::Class::Journal?

Can't I just use DBIx::Class::Journal?

• Can only version single PK tables (no join tables)
• Uses separate schema and connection (good and bad)
• All or none (columns)
• Everything must be in a transaction
• Awkward to set up
• Not very helpful API for real usage
• Update/Delete cascades not supported

Can't I use Database Triggers?

Can't I just use Database Triggers?

• Not cross DB
• Not nearly as powerful
• But you can (soon?) anyway

Can't I log business level transactions?

Can't I log business level transactions?

• Short sighted
• Lots of work
• You'll probably do it wrong
• Create your own API

So this is better?

• MultiPK works
• Only audit what you care about
• Use transactions/changesets when they make sense
• Inflate your actual results from shadows
• Cascades are supported*
• Cross DB
• Proven API
• Versioned Relationships

So this is better?

• Just go to the pub!

So this is better?

• ... and do whatever else you love

Terminology

• Shadow - a specific version for a given row object
• Changeset - a transaction of changes optionally tied to a user (or anything else), a db snapshot
• Shadow Stage - insert | update | delete (and rekey)
• Shadow Lifecycle - persistent across inserts, updates, and deletes
• Phantom - Rehydrated Result (zombie?)

Shadow

Lifecycle

Phantom

Terminology (Shadow)

• my $tea = $rs->create({ name => 'sencha' });

• $tea->update({ name => 'Sencha' });

• $tea->delete

• Three shadows
• Three stages (2, 1, 0)
• One lifecycle

Setup (schema)

package MyApp::Schema;

use parent 'DBIx::Class::Schema';
my $p = __PACKAGE__;
$p->load_components(qw/Schema::Shadow/);

$p->shadow_result_base_class( '::BaseShadowResult' );
$p->shadow_changeset_result( '::Changeset' );

$p->load_namespaces;

1;

Setup (result)

package MyApp::Schema::Result::Artist;

use parent 'MyApp::Schema::Result';

my $p = __PACKAGE__;
# defaults to all columns
$p->load_components('Shadow');
$p->shadow_columns([qw(id name)]);

Setup (result) (cont.)

$p->table('artists');

$p->add_columns(
  id => { data_type => 'int' },
  name => { data_type => 'varchar', size => 30 },
  notes => { data_type => 'text' },
);

$p->set_primary_key('id');

$p->has_many(cds => 'MyApp::Schema::Result::CD', 'artist_id');

1;

Setup (Changeset Result)

package MyApp::Schema::Result::Changeset;

use parent 'MyApp::Schema::Result';

my $p = __PACKAGE__;
# defaults to all columns

$p->table('changeset');

$p->add_columns(
  id => { data_type => 'int' },
  timestamp => { data_type => 'bigint' },
  user_id => { data_type => 'int' },
);

Setup (Changeset Result cont.)

$p->set_primary_key('id');

$p->belongs_to(user => 'MyApp::Schema::Result::User', 'user_id');

sub new_changeset {
   my ($class, $rsrc, $params) = @_;

   $rsrc->resultset->new_result($params);
}

1;

USAGE

USAGE

• Changeset Creation
• Resultset
• Result
• Changeset

Changeset Usage

$schema->changeset_do({
   system     => undef,
   user_id    => 5,
   session_id => 76
}, sub {
   ...
})

Shadow ResultSet methods

• shadow_version
• shadow_inserts
• shadow_updates
• shadow_deletes
• shadows_before
• shadows_after
• changeset
• change

Shadow ResultSet methods (shadow_version)

 $rs->shadow_version(5) 

Shadow ResultSet methods (shadows_before/shadows_after)

• $rs->shadows_before( revision  => 5   ); 

• $rs->shadows_before( datetime  => $dt );

• $rs->shadows_before( changeset => 5   );

Shadow ResultSet methods (change)

$rs->change(
   last_name => 'Cantrell', 'Schmidt'
);

Shadow ResultSet methods (chain)

$rs->shadows_after(
   datetime => DateTime->now->truncate(to => 'month')
)->shadows_before(
   datetime => DateTime->now->add(days => 7)
)->change(
   log_location => '/var/log', '/home/frew/log'
)

Shadow Result methods

• as_result
• as_diff
• previous_shadow
• next_shadow

Shadow Result methods (as_result)

my $user_phantom = $shadow->as_result
$user_phantom->roles->related_resultset('permissions')

Shadow Result methods (as_diff)

my ($type, $from, $to) = $shadow->as_diff

$type =~ /insert|update|delete/

Shadow Result methods (next_shadow/previous_shadow)

while (my $row = $row->next_shadow) {
 ...;
}

"FAQ"

What about schema changes?

• As long as your shadow columns are all nullable, that's totally fine

What about my database getting huge?

• time/revision based cutoffs

Applications

• Database Changelog For Debugging
• Database Changelog For Users (See JIRA)
• Reverting Changes

Applications (show log)

Applications (Naïve Reverting)

• Possible to revert
• Version 1: (a, b, c)
• Version 2: (a, b, g)
• Current version: (x, y, g)
• Version 2 can be reverted
• because the delta would have the same "from"

Applications (Naïve Reverting)

• Impossible to revert
• Version 1: (a, b, c)
• Version 2: (a, b, g)
• Current version: (x, y, d)
• Version 2 cannot be reverted
• because the delta does not have the same "from"

Questions?

THE END