File: res_fk.pm

package info (click to toggle)
percona-toolkit 3.2.1-1
  • links: PTS, VCS
  • area: main
  • in suites: bookworm, bullseye, forky, sid, trixie
  • size: 68,916 kB
  • sloc: perl: 241,287; sql: 22,868; sh: 19,746; javascript: 6,799; makefile: 353; awk: 38; python: 30; sed: 1
file content (184 lines) | stat: -rw-r--r-- 6,815 bytes parent folder | download | duplicates (2)
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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
# This program is copyright 2009 Percona Inc.
# Feedback and improvements are welcome.
#
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation, version 2; OR the Perl Artistic License.  On UNIX and similar
# systems, you can issue `man perlgpl' or `man perlartistic' to read these
# licenses.
#
# You should have received a copy of the GNU General Public License along with
# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
# Place, Suite 330, Boston, MA  02111-1307  USA.

package res_fk;

# This mk-archiver plugin demonstrates how to archive a table which several
# other tables reference directly and indirectly with foreign keys.  The
# tables are provided in samples/res_fk.sql.  The picutre is:
#
#   comp <---- user
#   ^          |
#   |          /
#   prod <---+
#   ^
#   |
#   prod_details
#
# prod_details references prod.  user references both prod and comp.  comp is
# the table we want to archive.  Therefore, before we can remove rows from
# comp, we must remove rows in user, prod_details then prod, else we'll
# violate a foreign key constraint:
#
#   mysql> DELETE FROM comp WHERE id=2;
#   ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
#   constraint fails (`test/prod`, CONSTRAINT `prod_comp_id` FOREIGN KEY
#   (`comp_id`) REFERENCES `comp` (`id`) ...
#
# If we were just deleteing the archived rows, things would be simple: just
# delete rows in the child tables then delete the row in the parent table,
# comp.  Instead, we'll do something slightly more complex: we'll archive
# the rows into another database with the same foreign key dependencies.
# Thus, we'll need to do special work in before_delete().

use strict;
use English qw(-no_match_vars);
use constant PTDEBUG  => $ENV{PTDEBUG};
use Data::Dumper;
$Data::Dumper::Indent    = 1;
$Data::Dumper::Sortkeys  = 1;
$Data::Dumper::Quotekeys = 0;

sub new {
   my ( $class, %args ) = @_;
   my $dbh = $args{dbh};
   my $src_db = "`$args{db}`";
   my $dst_db = '`test_archived`'; 

   # Prepare statements for user table.
   my $sql = "INSERT INTO $dst_db.`user` "
        . "SELECT * FROM $src_db.`user` "
        . 'WHERE comp_id=?';
   PTDEBUG && _d($sql);
   my $archive_users_sth = $dbh->prepare($sql);

   $sql = "DELETE FROM $src_db.`user` WHERE comp_id=?";
   PTDEBUG && _d($sql);
   my $delete_users_sth = $dbh->prepare($sql);

   # Prepare statements for prod table.
   $sql = "INSERT INTO $dst_db.`prod` "
        . "SELECT * FROM $src_db.`prod` "
        . 'WHERE comp_id=?';
   PTDEBUG && _d($sql);
   my $archive_prods_sth = $dbh->prepare($sql);

   $sql = "SELECT DISTINCT `id` FROM $src_db.`prod` WHERE comp_id=?";
   PTDEBUG && _d($sql);
   my $get_prods_sth = $dbh->prepare($sql);

   $sql = "DELETE FROM $src_db.`prod` WHERE comp_id=?";
   PTDEBUG && _d($sql);
   my $delete_prods_sth = $dbh->prepare($sql);

   my $self = {
      dbh               => $args{dbh},
      src_db            => $src_db,
      dst_db            => $dst_db,
      archive_users_sth => $archive_users_sth,
      delete_users_sth  => $delete_users_sth,
      archive_prods_sth => $archive_prods_sth,
      get_prods_sth     => $get_prods_sth,
      delete_prods_sth  => $delete_prods_sth,
   };

   return bless $self, $class;
}

sub before_begin {
   my ( $self, %args ) = @_;
   return;
}

sub is_archivable {
   my ( $self, %args ) = @_;
   # Use --where to select the rows you want and/or do special checks here.
   return 1;  # Archive the row.
}

# before_delete() is called after the row is inserted via the --dest dbh.
# However, we normally cannot see the inserted comp row because these are
# InnoDB tables and we're using transactions and the transactions are committed
# after the whole insert and delete operation is completed, not to mention
# that the comp row is inserted via the --dest dbh so it's visible in that
# connection's transaction before commit but not in our connection, the
# --src dbh.  There's a few ways around this.  We could use --txn-size 0
# to disable transactions, or use --skip-foreign-key-checks, or use this
# plugin with the --src dbh.  This last option would be ideal but it's not
# possible because only before_insert() is available to a --src plugin;
# we would need "after_insert()" which does not exist.  before_delete() is
# not called for the --src plugin either, else that would work since
# before_delete() is called after before_insert().  Using
# --skip-foreign-key-checks works, too, but to be safe we should not do this.
# So the solution is to use --txn-size 0.  This enables autocommit so the
# INSERT into the dest comp is visible to us.  Then we can archive the other
# tables with INSERT SELECT ($archive_*_sth).
sub before_delete {
   my ( $self, %args ) = @_;
   PTDEBUG && _d('before delete');
   my $dbh     = $self->{dbh};
   my $src_db  = $self->{src_db};
   my $dst_db  = $self->{dst_db};
   my $comp_id = $args{row}->[0];  # id is first column
   my $sql;
   PTDEBUG && _d('row:', Dumper($args{row}));

   # Archive rows from prod then user, in that order because
   # user referenes prod.
   $self->{archive_prods_sth}->execute($comp_id);
   $self->{archive_users_sth}->execute($comp_id);

   # Archiving the prod details requires a little extra work
   # because prod_details only references prod and each comp
   # may have multiple prod.  So we need to get all the prod
   # details for all the comp's prods.
   $self->{get_prods_sth}->execute($comp_id);
   my $prod_ids     = $self->{get_prods_sth}->fetchall_arrayref();
   my $all_prod_ids = join(',', map { $_->[0]; } @$prod_ids);
   PTDEBUG && _d('prod ids:', $all_prod_ids);
   my $sql = "INSERT INTO $dst_db.`prod_details` "
           . "SELECT * FROM $src_db.`prod_details` "
           . "WHERE prod_id IN ($all_prod_ids)";
   PTDEBUG && _d($sql);
   $dbh->do($sql);

   # Now we can delete the rows from user, prod_details then prod
   # on the source.  This allows mk-archiver to delete the comp row.
   $self->{delete_users_sth}->execute($comp_id);
   $sql = "DELETE FROM $src_db.`prod_details` "
        . "WHERE prod_id IN ($all_prod_ids)";
   PTDEBUG && _d($sql);
   $dbh->do($sql);
   $self->{delete_prods_sth}->execute($comp_id);

   return;
}

sub after_finish {
   my ( $self ) = @_;
   return;
}

sub _d {
   my ($package, undef, $line) = caller 0;
   @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
        map { defined $_ ? $_ : 'undef' }
        @_;
   print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
}

1;