Otogiri-Plugin-DeleteCascade

 view release on metacpan or  search on metacpan

README.md  view on Meta::CPAN

    use Otogiri;
    use Otogiri::Plugin;

    Otogiri->load_plugin('DeleteCascade');

    my $db = Otogiri->new( connect_info => $connect_info );
    $db->insert('parent_table', { id => 123, value => 'aaa' });
    $db->insert('child_table',  { parent_id => 123, value => 'bbb'}); # child.parent_id referes parent_table.id(FK)

    $db->delete_cascade('parent_table', { id => 123 }); # both parent_table and child_table are deleted.

# DESCRIPTION

Otogiri::Plugin::DeleteCascade is plugin for [Otogiri](https://metacpan.org/pod/Otogiri) which provides cascading delete feature.
loading this plugin, `delete_cascade` method is exported. `delete_cascade` follows Foreign Keys(FK) and
delete data referred in these key.

# NOTICE

Please DO NOT USE this module in production code and data. This module is intended to be used for data maintenance
in development environment or cleanup data for test code.

This module does not support multiple foreign key. It causes unexpected data lost if you delete data in
multiple foreign key table.

This module uses [DBIx::Inspector](https://metacpan.org/pod/DBIx::Inspector) to access metadata(foreign keys). In some environment, database administrator
does not allow to access these metadata, In this case this module can't be used.

# METHOD

## $self->delete\_cascade($table\_name, $cond\_href);

Delete rows that matched to $cond\_href and child table rows that can be followed by Foreign Keys.

# LICENSE

Copyright (C) Takuya Tsuchida.

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.

lib/Otogiri/Plugin/DeleteCascade.pm  view on Meta::CPAN

use 5.008005;
use strict;
use warnings;

our $VERSION = "0.04";

use Otogiri;
use Otogiri::Plugin;
use DBIx::Inspector;

our @EXPORT = qw(delete_cascade);

sub delete_cascade {
    my ($self, $table_name, $cond_href) = @_;

    my @parent_rows = $self->select($table_name, $cond_href);
    my $inspector = DBIx::Inspector->new(dbh => $self->dbh);
    my $iter = $inspector->table($table_name)->pk_foreign_keys();
    my $affected_rows = 0;
    while( my $child_table_fk_info = $iter->next ) {
        $affected_rows += _delete_child($self, $child_table_fk_info, @parent_rows);
    }
    $affected_rows += $self->delete($table_name, $cond_href);

lib/Otogiri/Plugin/DeleteCascade.pm  view on Meta::CPAN

    my ($db, $child_table_fk_info, @parent_rows) = @_;
    my $affected_rows = 0;
    for my $parent_row ( @parent_rows ) {
        my $child_table_name   = $child_table_fk_info->fktable_name;
        my $parent_column_name = $child_table_fk_info->pkcolumn_name;
        my $child_column_name  = $child_table_fk_info->fkcolumn_name;

        my $child_delete_condition = {
            $child_column_name => $parent_row->{$parent_column_name},
        };
        $affected_rows += $db->delete_cascade($child_table_name, $child_delete_condition);
    }
    return $affected_rows;
}


1;
__END__

=encoding utf-8

lib/Otogiri/Plugin/DeleteCascade.pm  view on Meta::CPAN

    use Otogiri;
    use Otogiri::Plugin;

    Otogiri->load_plugin('DeleteCascade');

    my $db = Otogiri->new( connect_info => $connect_info );
    $db->insert('parent_table', { id => 123, value => 'aaa' });
    $db->insert('child_table',  { parent_id => 123, value => 'bbb'}); # child.parent_id referes parent_table.id(FK)

    $db->delete_cascade('parent_table', { id => 123 }); # both parent_table and child_table are deleted.

=head1 DESCRIPTION

Otogiri::Plugin::DeleteCascade is plugin for L<Otogiri> which provides cascading delete feature.
loading this plugin, C<delete_cascade> method is exported. C<delete_cascade> follows Foreign Keys(FK) and
delete data referred in these key.

=head1 NOTICE

Please DO NOT USE this module in production code and data. This module is intended to be used for data maintenance
in development environment or cleanup data for test code.

This module does not support multiple foreign key. It causes unexpected data lost if you delete data in
multiple foreign key table.

This module uses L<DBIx::Inspector> to access metadata(foreign keys). In some environment, database administrator
does not allow to access these metadata, In this case this module can't be used.

=head1 METHOD

=head2 $self->delete_cascade($table_name, $cond_href);

Delete rows that matched to $cond_href and child table rows that can be followed by Foreign Keys.

=head1 LICENSE

Copyright (C) Takuya Tsuchida.

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.

t/01_sqlite.t  view on Meta::CPAN

use Test::More;
use Otogiri;
use Otogiri::Plugin;
#use DBIx::QueryLog;

my $dbfile  = ':memory:';

my $db = Otogiri->new( connect_info => ["dbi:SQLite:dbname=$dbfile", '', '', { RaiseError => 1, PrintError => 0 }] );
$db->load_plugin('DeleteCascade');

ok( $db->can('delete_cascade') );

my @sql_statements = split /\n\n/, <<EOSQL;
PRAGMA foreign_keys = ON;

CREATE TABLE person (
  id   INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT    NOT NULL,
  age  INTEGER NOT NULL DEFAULT 20
);

t/01_sqlite.t  view on Meta::CPAN

$db->fast_insert('person', {
    name => 'Sherlock Shellingford',
    age  => 15,
});
my $person_id = $db->last_insert_id();
$db->fast_insert('detective', {
    person_id => $person_id,
    toys      => 'psychokinesis',
});

my $affected_rows = $db->delete_cascade('person', { id => $person_id });
ok( !defined $db->single('person',    { id => $person_id }) );
ok( !defined $db->single('detective', { person_id => $person_id }) );
is( $affected_rows, 2 );

done_testing;