File: db-calc-storesize

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 (102 lines) | stat: -rwxr-xr-x 2,673 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
#!/usr/bin/perl -w
# SPDX-License-Identifier: AGPL-3.0-only

use strict;
use DBI;

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

if(@ARGV != 4) {
	print "Usage: $0 <mysqluser> <mysqlpass> <mysqldb> <db-hostname>\n";
	exit(1);
}

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

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

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

sub getsize {
    my ($hierarchyid) = @_;
    
    my $sth = $db->prepare("SELECT hierarchy.id, properties.val_ulong, hierarchy.type FROM hierarchy LEFT JOIN properties ON properties.hierarchyid=hierarchy.id AND properties.tag=3592 AND properties.type=3 WHERE hierarchy.parent=". $hierarchyid . " AND hierarchy.flags & 0x400 = 0");

    $sth->execute();
    
    my @row;
    
    my $totalsize = 0;

    while(@row = $sth->fetchrow_array()) {
        if($row[2] == 5 && defined($row[1])) {
            $totalsize += $row[1];
        } elsif($row[2] == 3) {
            $totalsize += getsize($row[0]);
        }
    }
    
    return $totalsize;
}

my $res; 
my $sth;
my $rows;

$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);
}

my @row;

@row = $db->selectrow_array("SELECT major FROM versions ORDER BY databaserevision DESC LIMIT 1")
	or die $DBI::errstr;

if (scalar(@row) == 0) {
	print "Unable to determain version of database\n";
	exit(1);
}
my $version = $row[0];

print "Calculating store sizes..\n";


# Add PR_SEARCH_KEY for all objects
$sth = $db->prepare("SELECT hierarchy_id, users.id, user_name, val_longint FROM stores JOIN users ON users.id = stores.user_id JOIN properties ON hierarchy_id=properties.hierarchyid WHERE properties.tag=3592 AND properties.type=20 order by val_longint");
if(!$sth) {
  do_error(1);
}
$sth->execute();

while(@row = $sth->fetchrow_array()) {
	my $size = getsize($row[0]);
    
	if($row[3] == $size) {
	print "Size of store for user " . $row[1] ."(".$row[2].") is correct at " . $size . "\n";
	} else {
	print "Size of store for user " . $row[1] ."(".$row[2].") was ".$row[3].", now is " . $size . "\n";
	$db->do("DELETE FROM properties WHERE hierarchyid = " . $row[0] . " AND tag=3592 AND (type=3 OR type=20)");
		if ($version < 7) {
			$db->do("INSERT INTO properties (storeid, hierarchyid, tag, type, val_longint) VALUES(" . $row[0]. ", " . $row[0] . ", 3592, 20, $size)");
		} else {
			$db->do("INSERT INTO properties (hierarchyid, tag, type, val_longint) VALUES(" . $row[0] . ", 3592, 20, $size)");
		}
	}
}

print "done.\n";