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
|
#!/usr/bin/perl -w
use strict;
use warnings;
use DBI; #Database independent interface for Perl
use DBD::mysql; #MySQL driver for the Perl5 Database Interface (DBI)
use Getopt::Std;
my $db = "xxv";
my $host = "localhost";
my $port = "3306";
my $usr = "root";
my $pwd = "";
my $Usage = qq{
this script search and remove duplicates from CHRONICLE.
Duplicates should never occurrence, but it's some time possible.
e.g. after migration from mysql 4.0 to 5.0.
Usage: $0 [options]
Options:
-H <hostname> : Host name of the MySQL server (default: '$host')
-P <port> : Port number of MySQL server (default: '$port')
-d <database> : XXV database name (default: '$db')
-u <user> : Username (default: '$usr')
-p <password> : Password (default: '$pwd')
-q : Be quiet
-h : Help
};
use vars qw/ %opt /;
my $dbh;
die $Usage if (!getopts("d:H:P:u:p:hq", \%opt) || $opt{h} );
$db = $opt{d} if($opt{d});
$host = $opt{H} if($opt{H});
$port = $opt{P} if($opt{P});
$usr = $opt{u} if($opt{u});
$pwd = $opt{p} if($opt{p});
# ------------------
# dump_duplicates
# ------------------
sub dump_duplicates {
# my $sth = $dbh->prepare('select id,title,channel_id FROM CHRONICLE')
my $sth = $dbh->prepare('select id,title,channel_id FROM CHRONICLE WHERE id IN (SELECT id FROM TMPCHRONICLE)')
or die "Couldn't prepare dump statement: " . $dbh->errstr;
my @data;
$sth->execute()
or die "Couldn't execute dump statement: " . $sth->errstr;
while (@data = $sth->fetchrow_array()) {
print sprintf("%3d: %20s %s\n",$data[0],$data[2] || '',$data[1]);
}
if ($sth->rows == 0) {
print "There none duplicates found.\n";
}
}
# ------------------
# search and remove duplicates from CHRONICLE
# ------------------
sub remove_duplicates {
$dbh->do(qq|DROP TABLE IF EXISTS TMPCHRONICLE|)
|| die "Couldn't drop temporary table: ". $dbh->errstr;
$dbh->do(qq|
CREATE TEMPORARY TABLE TMPCHRONICLE
SELECT min(id) id
FROM CHRONICLE
GROUP BY title,channel_id,starttime,duration
HAVING count(*) > 1
|) || die "Couldn't create temporary table: ". $dbh->errstr;
&dump_duplicates() if(! $opt{q});
$dbh->do(qq|DELETE FROM CHRONICLE WHERE id IN (SELECT id FROM TMPCHRONICLE)|)
|| die "Couldn't delete duplicates from temporary table: ". $dbh->errstr;
$dbh->do(qq|DROP TABLE IF EXISTS TMPCHRONICLE|)
|| die "Couldn't drop temporary table: ". $dbh->errstr;
$dbh->disconnect;
return 0;
}
$dbh = DBI->connect("DBI:mysql:database=$db;host=$host;port=$port", $usr, $pwd,{
PrintError => 1,
AutoCommit => 1,
}) || die $DBI::errstr;
exit 1
unless($dbh);
exit &remove_duplicates();
|
