File: db-remove-orphaned-attachments

package info (click to toggle)
kopanocore 8.7.0-3
  • links: PTS, VCS
  • area: main
  • in suites: bullseye, buster, sid
  • size: 15,400 kB
  • sloc: cpp: 175,422; python: 24,623; perl: 7,319; php: 6,056; sh: 2,172; makefile: 1,294; xml: 45; ansic: 1
file content (96 lines) | stat: -rwxr-xr-x 2,336 bytes parent folder | download
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
#!/usr/bin/perl -w
# SPDX-License-Identifier: AGPL-3.0-only

use strict;
use DBI;

my $L1 = 10;
my $L2 = 20;

sub do_error($) {
   exit(1);
}

sub readconfig($) {
	my ($fn) = @_;
	my %options;

	open(CFG, $fn) or die("unable to open ".$fn." config file");
	while (<CFG>) {
		if ($_ =~ /^\s*[#!]/) {
			next;
		}
		if ($_ =~ /^\s*(\S+)\s*=\s*([^\r]+)\r?$/) {
			my $idx = $1;
			my $val = $2;
			chomp($val);
			$val =~ s/\s+$//;
			$options{$idx} = $val;
		}
	}
	close(CFG);
	return %options;
}

# TODO: parse config, and use settings
if(scalar(@ARGV) < 1) {
	print "Usage: $0 <server.cfg>\n";
	exit(1);
}

my $servercfg = $ARGV[0];
$servercfg = "/etc/kopano/server.cfg" if (!defined($servercfg));
my %serveropt = readconfig($servercfg);

my $basepath = $serveropt{attachment_path};

my $db = DBI->connect("dbi:mysql:database=".$serveropt{mysql_database}.";host=".$serveropt{mysql_host}, $serveropt{mysql_user}, $serveropt{mysql_password})
	or die "Database error: ".$DBI::errstr;

my $res; 
my $sth;
my $rows;
my @row;

if (!defined($db)) {
	print "did not connect to mysql\n";
	exit(1);
}

print "Finding all orphaned attachments...\n";
$sth = $db->prepare("SELECT DISTINCT(instanceid) FROM singleinstances LEFT JOIN hierarchy ON hierarchyid=hierarchy.id WHERE hierarchy.id IS NULL;");
$sth->execute() || die $DBI::errstr;;

if ($sth->rows == 0) {
	print "No orphaned attachments found.\n";
	exit(0);
}

if ($serveropt{attachment_storage} eq "files") {
	print "Processing ".$sth->rows." orphaned attachments\n";

	while(@row = $sth->fetchrow_array()) {
		my $filename = $basepath."/".($row[0] % $L1)."/".(($row[0] / $L1) % $L2)."/".$row[0];

		# can be either with or without compression
		system("rm -f ".$filename." ".$filename.".gz");
	}
}

$res = $db->do("begin;");
if(!$res) {
  do_error(1);
}

print "Cleaning singleinstances...\n";
$sth = $db->prepare("DELETE singleinstances.* FROM singleinstances LEFT JOIN hierarchy ON hierarchyid=hierarchy.id WHERE hierarchy.id IS NULL;");
$sth->execute() || die $DBI::errstr;;

print "Cleaning lob...\n";
$sth = $db->prepare("DELETE lob.* FROM lob LEFT JOIN singleinstances ON lob.instanceid = singleinstances.instanceid WHERE singleinstances.instanceid IS NULL;");
$sth->execute() || die $DBI::errstr;;

print "Flush database changes to disk...\n";
$db->do("commit;");

print "Done.\n";