Recalculate store-size per user

From Zarafa wiki

There is a script to recalculate the store sizes of ALL zarafa users which is installed when installing the zarafa package. The script can be found in:

/usr/share/doc/zarafa/db-calc-storesize

With this script however, it is NOT possible to recalculate store sizes for a single user. It is only possible for ALL users, which can take a long time on a very large database. It is also difficult sometimes to find out which username has which user_id in the database. When a store is created with a zarafa version before 6.11, the username is not stored in mailstore of the user. Also when the username is stored in the database (after 6.11) and if you rename a user in ldap, the username is NOT updated in the database.

Calculate script per user

Below you can find a php script which is able to recalculate the store size of a single user. Starting the script with the "-l" option also gives a list of all the Zarafa users and their corresponding user_id in the database.

Prerequisites for the script:

• Run the script on a server which has the php mapi extention installed.
• You also need to have the php-cli and php-mysql package installed.
• Modify the zarafa credentials (any zarafa user will do) and mysql credentials on top of the script.

Note: If you are using a multi company setup, the script works only within the company of the $zarafa_user.

Note 2: If you are running SLES11, you have to change the include to include('mapi.util.php');

The script:

#!/usr/bin/php
#

<?php

// Works in all PHP versions
ini_set('include_path', '/usr/share/php');

// Change zarafa credentials - ANY zarafa user will do
$zarafa_user="";
$zarafa_pass="";
$zarafa_sock="file:///var/run/zarafa";

// Change mysql credentials - NEEDS write access in DB to update store sizes
$mysqlhost="localhost";
$mysqlport="3306";
$mysqluser="root";
$mysqlpass="";
$mysqldb="zarafa";



if ($argc == 1) {
  show_help($argv[0]);
  exit;
}

if ($argv[1] == "-l") {
  show_gab($zarafa_user, $zarafa_pass, $zarafa_sock);
}

if ($argv[1] == "-r") {
  if (! isset($argv[2])) {
    show_help($argv[0]);
    exit;
  }
  $link = mysql_connect("$mysqlhost:$mysqlport", "$mysqluser", "$mysqlpass");
  if (!$link) {
    die('Could not connect: ' . mysql_error());
  }
  mysql_select_db($mysqldb, $link);

  $query = "select * from versions ORDER by major DESC LIMIT 1";
  $result = mysql_query($query) or die(mysql_error());

  $row=mysql_fetch_array($result);

  $major=$row[0];

  //$query = "SELECT hierarchy_id, users.id FROM stores JOIN users ON users.id = stores.user_id where user_id=$argv[2]";
  $query = "SELECT hierarchy_id, users.id, stores.user_name FROM stores JOIN users ON users.id = stores.user_id where users.externid='".mysql_real_escape_string(base64_decode($argv[2]))."'";

  $result = mysql_query($query) or die(mysql_error());

  if (mysql_num_rows($result)!=0) {

    $row=mysql_fetch_array($result);

    $size = getsize($row[0], $link);
    print "Size of store for user " . $row[2]. " = " . $size ."\n\n";

    $allowed_majors = array("6", "7");
    if (in_array($major, $allowed_majors)) {
      print "Storing size of user in database...\n";
      mysql_query("DELETE FROM properties WHERE hierarchyid = " . $row[0] . " AND tag=0x0e08 AND (type=0x0003 OR type=0x0014)") or die(mysql_error());
      if ($major == "6") {
        mysql_query("INSERT INTO properties (storeid, hierarchyid, tag, type, val_longint) VALUES(" . $row[0]. ", " . $row[0] . ", 0x0e08, 0x0014, $size)") or die(mysql_error());
      } elseif ($major == "7") {
        mysql_query("INSERT INTO properties (hierarchyid, tag, type, val_longint) VALUES(" . $row[0] . ", 0x0e08, 0x0014, $size)") or die(mysql_error());
      }
      print "Done.\n\n";
    }
  } else {
    print "User ". $row[1] . "cannot be found on this database. If you are running a multiserver setup please run the script on the zarafa server which is this user's home-server.\n\n";
  }

  mysql_close($link);
}

function show_help($arg) {
  print "Usage: $arg [option] <argument>\n";
  print "\n";
  print "For recalculation the storesize you need to know the extern id (ext_id)\n";
  print "of the user. You can find this with the list option (-l).\n\n";
  print "The following options are available:\n";
  print "\t-l\tShows all users in Zarafa with the corresponding ext_id from the database\n";
  print "\t-r\t[ext_id]\tRe-calculates storesize of user with ext_id [ext_id]\n";
  print "\n";
}

function show_gab($zarafa_user, $zarafa_pass, $zarafa_sock) {
  include('mapi/mapi.util.php');
  include('mapi/mapidefs.php');
  include('mapi/mapicode.php');
  include('mapi/mapitags.php');
  include('mapi/mapiguid.php');

  include('mapi/class.recurrence.php');
  include('mapi/class.freebusypublish.php');

  $session = mapi_logon_zarafa("$zarafa_user","$zarafa_pass", "$zarafa_sock");
  if(!$session) { print "Unable to open session\n"; exit(1); }

  $msgstorestable = mapi_getmsgstorestable($session);
  if(!$msgstorestable) { print "Unable to open message stores table\n"; exit(1); }

  $msgstores = mapi_table_queryallrows($msgstorestable, array(PR_DEFAULT_STORE, PR_ENTRYID));

  foreach ($msgstores as $row) {
    if($row[PR_DEFAULT_STORE]) {
        $storeentryid = $row[PR_ENTRYID];
    }
  }  

  if(!$storeentryid) { print "Can't find default store\n"; exit(1); }

  $store = mapi_openmsgstore($session, $storeentryid);
  if(!$store) { print "Unable to open default store\n"; exit(1); }

  $ab = mapi_openaddressbook($session);
  if(!$ab) { print "Unable to open addressbook\n"; exit(1); }

  $gabid = mapi_ab_getdefaultdir($ab);
  if(!$gabid) { print "Unable to get default dir\n"; exit(1); }

  $gab = mapi_ab_openentry($ab, $gabid);
  if(!$gab) { print "Unable to open GAB $gabid\n"; exit(1); }

  $table = mapi_folder_getcontentstable($gab);
  if(!$table) { print "Unable to get GAB table\n"; exit(1); }

  $rows = mapi_table_queryallrows($table);

  $columns = Array();
  // Find all the used 'string' type columns first
  foreach($rows as $row) {
    foreach($row as $proptag => $propval) {
     if(mapi_prop_type($proptag) == PT_STRING8)
       $columns[$proptag] = 1; 
    }  
  }

  // Dump all the data
  $i=0;
  $pad=20;
  $delimiter="  - ";
  print str_pad("EXT ID", 20 + strlen($delimiter), " ");
  print str_pad("NAME", $pad + strlen($delimiter), " ");
  print str_pad("TYPE", $pad + strlen($delimiter), " ");
  print str_pad("LOGIN NAME", $pad + strlen($delimiter), " ");
  print str_pad("EMAIL", $pad, " ");
  print "\n";
  print str_pad("-", (4*$pad) + 15, "-");
  print "\n";
  foreach($rows as $row) {
    $array = unpack("lunk/lunk/lunk/lunk/lunk/lunk/Ltype/Lid/a*exid",$row[PR_ENTRYID]);
    print str_pad($array["exid"],20, " ");
    foreach($columns as $proptag => $dummy) {
      print $delimiter;
      if(isset($row[$proptag])) {
        //print $row[$proptag];
        print str_pad($row[$proptag], $pad, " ");
      } else {
        print "[EMPTY]";
      }
    }
    print "\n";
    $i++;
  }
  print "\n";
}  // End function show_gab



function getsize($hierarchyid, $link) {

    $query="SELECT hierarchy.id, properties.val_ulong, hierarchy.type FROM hierarchy LEFT JOIN properties ON properties.hierarchyid=hierarchy.id AND properties.tag=0x0e08 AND properties.type=0x0003 WHERE hierarchy.parent=". $hierarchyid . " AND hierarchy.flags & 0x400 = 0";

    $result = mysql_query($query, $link) or die(mysql_error());

    $totalsize = 0;

    while($row=mysql_fetch_array($result)) {
      if($row[2] == 5 && isset($row[1])) {
        $totalsize = $totalsize + $row[1]; 
      } elseif ($row[2] == 3) {
        $totalsize = $totalsize + getsize($row[0], $link);
      }
    }

    return $totalsize;
} // End function getsize

?>