File: db-convert-attachments-to-files

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 (138 lines) | stat: -rwxr-xr-x 3,107 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
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
#!/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 readable {
	my $size = shift;
	my @add = qw( B KB MB GB TB );
	my $i ;
	for ($i = 0; $i < 5;$i++) { 
		if ( int($size / 1024) > 0 ) {
			$size = $size / 1024;
		} else {
			$size = 0.01*int(0.5+ $size/0.01) . " " . $add[$i];
			last;
		}
	}
	return $size;
}

if(scalar(@ARGV) < 4) {
	print "Usage: $0 <mysqluser> <mysqlpass> <mysqldb> <destination path> [delete]\n";
	printf"   if [delete] is given, attachments will be removed from the database, to free storage\n";
	exit(1);
}

my ($user,$pass,$dbname,$basepath,$delete) = @ARGV;

my $db = DBI->connect("dbi:mysql:$dbname", $user, $pass);

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

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

$res = $db->do("set character_set_client=utf8;");
if(!$res) {
  do_error(1);
}
$res = $db->do("set character_set_connection=utf8;");
if(!$res) {
  do_error(1);
}
$res = $db->do("set character_set_results=utf8;");
if(!$res) {
  do_error(1);
}
$res = $db->do("begin;");
if(!$res) {
  do_error(1);
}

$sth = $db->prepare("SHOW TABLE STATUS WHERE name = 'lob'");
$sth->execute() || die $DBI::errstr;
if ($sth->rows == 0) {
	print "Can't get attachment size.\n";
	exit(0);
}

@row = $sth->fetchrow_array();
if (! defined ( $row[0] ) ) {
	print "Table status failed.\n";
	exit(0);
}
my $dbsize = $row[6];
print "The size of all attachments in the database is: ". $dbsize." Bytes (". readable($dbsize) . ")\n";

my $free = `df -P -B1 $basepath | tail -1 | awk '{print \$4}'`;
chomp($free);
print "Available space is: " .  $free . " Bytes (" . readable($free) . ")\n";

if ( $dbsize >= $free ) {
	print "Not enough space left on device.\n";
	exit(0);
}


print "Finding all attachments...\n";
$sth = $db->prepare("SELECT distinct(instanceid) FROM lob");
$sth->execute() || die $DBI::errstr;;

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

print "Processing ".$sth->rows." attachments\n";

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

	system("mkdir -p ".$path) == 0 or die("Unable to create attachment directories");

	if ( -s $filename ) {
		next;
	}
	open(AFILE, ">".$filename) or die("Unable to open new attachment file");

	my $sth2 = $db->prepare("SELECT val_binary FROM lob WHERE instanceid=".$row[0]." ORDER BY chunkid");
	$res = $sth2->execute();
	if(!$res) {
		print "  Unable to extract attachment ".$row[0]."\n";
		next;
	}

	while (@data = $sth2->fetchrow_array()) {
		print AFILE $data[0] or die("Not all data could be retrieved from attachment ".$row[0]);
	}
	close(AFILE);
}

print "Done.\n";

if (defined($delete) && $delete) {
	print "Deleting attachments from database...\n";
	$sth = $db->prepare("DELETE FROM lob");
	$sth->execute() || die $DBI::errstr;
	print "Done.\n";
}

print "Remember to correct the ownership of the files for kopano to access, when Kopano is not running as root\n";

$db->do("commit;");