-
Notifications
You must be signed in to change notification settings - Fork 0
/
DeleteCascade.pm
129 lines (96 loc) · 4.28 KB
/
DeleteCascade.pm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
package Otogiri::Plugin::DeleteCascade;
use 5.008005;
use strict;
use warnings;
our $VERSION = "0.01";
use Otogiri;
use Otogiri::Plugin;
our @EXPORT = qw(delete_cascade);
sub delete_cascade {
my ($self, $table_name, $cond_href) = @_;
$cond_href = $self->_deflate_param($table_name, $cond_href);
my @child_table_names = _fetch_child_table_names($self, $table_name);
my @parent_rows = $self->select($table_name, $cond_href);
for my $child_table_name ( @child_table_names ) {
_delete_child_tables($self, $table_name, $child_table_name, @parent_rows);
}
$self->delete($table_name, $cond_href);
}
sub _fetch_child_table_names {
my ($db, $table_name) = @_;
my $sql = <<"EOSQL";
SELECT DISTINCT table_constraints.table_name
FROM information_schema.table_constraints
JOIN information_schema.constraint_column_usage
ON constraint_column_usage.table_catalog = table_constraints.table_catalog
AND constraint_column_usage.table_schema = table_constraints.table_schema
AND constraint_column_usage.constraint_catalog = table_constraints.constraint_catalog
AND constraint_column_usage.constraint_name = table_constraints.constraint_name
WHERE table_constraints.constraint_type ='FOREIGN KEY'
AND constraint_column_usage.table_name = ?
;
EOSQL
my @result = map { $_->{table_name} } $db->search_by_sql($sql, [$table_name]);
return @result;
}
sub _delete_child_tables {
my ($db, $parent_table_name, $child_table_name, @parent_rows) = @_;
my @foreign_column_info = _fetch_foreign_column_info($db, $parent_table_name, $child_table_name);
for my $foreign_column_info ( @foreign_column_info ) {
for my $parent_row ( @parent_rows ) {
my $child_delete_condition = {
$foreign_column_info->{column_name} => $parent_row->{$foreign_column_info->{foreign_column_name}},
};
$db->delete_cascade($child_table_name, $child_delete_condition);
}
}
}
sub _fetch_foreign_column_info {
my ($db, $parent_table_name, $child_table_name) = @_;
my $sql = <<"EOSQL";
SELECT key_column_usage.column_name
, constraint_column_usage.column_name AS foreign_column_name
FROM information_schema.table_constraints
JOIN information_schema.key_column_usage
ON table_constraints.constraint_name = key_column_usage.constraint_name
JOIN information_schema.constraint_column_usage
ON constraint_column_usage.table_catalog = table_constraints.table_catalog
AND constraint_column_usage.table_schema = table_constraints.table_schema
AND constraint_column_usage.constraint_catalog = table_constraints.constraint_catalog
AND constraint_column_usage.constraint_name = table_constraints.constraint_name
WHERE table_constraints.constraint_type = 'FOREIGN KEY'
AND table_constraints.table_name = ?
AND constraint_column_usage.table_name = ?
;
EOSQL
return $db->search_by_sql($sql, [$child_table_name, $parent_table_name]);
}
1;
__END__
=encoding utf-8
=head1 NAME
Otogiri::Plugin::DeleteCascade - Otogiri Plugin for cascading delete by following FK columns
=head1 SYNOPSIS
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 NOTICE
This module works only PostgreSQL.
=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 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.
=head1 AUTHOR
Takuya Tsuchida E<lt>tsucchi@cpan.orgE<gt>
=cut