Update usernames in Database

From Zarafa wiki

(Difference between revisions)
Jump to: navigation, search
(fixed error Undefined offset: 1)
 
Line 7: Line 7:
* Run the script on a server which has the php mapi extention installed.
* Run the script on a server which has the php mapi extention installed.
* You need to have the php-cli package installed.
* You need to have the php-cli package installed.
 +
* You need to have the php-mysql package installed.
* Modify the zarafa credentials (any zarafa user will do) and mysql credentials on top of the script.
* Modify the zarafa credentials (any zarafa user will do) and mysql credentials on top of the script.

Latest revision as of 09:38, 3 November 2010

When a store is created with a zarafa version before 6.11, the username is not stored in mailstore of the user. When you upgrade to 6.30 or later, and one of these stores becomes orphaned, it will be difficult to see who the orphaned store belonged to (you will see some hexadecimal string as username). Also when you update a username e.g. in ldap, the username in the database will NOT be updated.

Below is a script which will update all the usernames in the database, so that normal (or updated) usernames are shown when the stores get orphaned.


Prerequisites for the script:

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

Without any options the script will only output data. With the option "-u" the script will also update the database.


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


The script:

#!/usr/bin/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";


$update_flag=0;

if (count($argv) > 1 ){
  if ($argv[1] == "-h") {
    show_help($argv[0]);
    exit;
  }

  if ($argv[1] == "-u") {
    $update_flag=1;
  }
}

$link = mysql_connect("$mysqlhost:$mysqlport", "$mysqluser", "$mysqlpass");
if (!$link) {
  die('Could not connect: ' . mysql_error());
}
mysql_select_db($mysqldb, $link);

show_gab($zarafa_user, $zarafa_pass, $zarafa_sock, $link, $update_flag);

mysql_close($link);


function show_help($arg) {
  print "Usage: $arg [option]\n";
  print "\n";
  print "Default only a listing is shown with loginname from zarafa and the loginname which is stored in the database.\n";
  print "\n";
  print "The following options are available:\n";
  print "\t-u\tUpdate the username in the database if it does not match the username in Zarafa.\n";
  print "\n\n";
  print "The output shows several columns, the user id, the loginname as known to Zarafa, the loginname as known to the database and the column EQ.\n";
  print "\n";
  print "Explanation of the EQ column:\n";
  print "\t=\tZarafa loginname equals Database loginname.\n";
  print "\t!\tZarafa loginname does NOT equal Database loginname.\n";
  print "\t!U\tDatabase loginname updated.\n";
  print "\t0\tLoginname cannot be found in this database (probably multiserver setup).\n";
  print "\n";
}

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

  include('/usr/share/php/mapi/class.recurrence.php');
  include('/usr/share/php/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=30;
  $delimiter=" - ";
  print str_pad("USER ID", 12, " ");
  print str_pad("ZARAFA LOGINNAME", $pad, " ");
  print str_pad("DB LOGINNAME", $pad, " ");
  print "EQ";
  print "\n";
  print str_pad("-", (2*$pad) + 15, "-");
  print "\n";
  foreach($rows as $row) {
    $array = unpack("lunk/lunk/lunk/lunk/lunk/lunk/Ltype/Lid",$row[PR_ENTRYID]);
    $userid=$array["id"];
    $username=$row[805371934];
    $usertype=$row[805437470];
    $userlogin=$row[805503006];
    $useremail=$row[972947486];
    
    if ($useremail!="" && $usertype=="ZARAFA" && $userlogin!="SYSTEM") {
      //echo "$userid\n";
      $db_array=get_from_db($userid, $link);
      print str_pad($userid, 12, " ");
      print str_pad($userlogin, $pad, " ");
      print str_pad($db_array[1], $pad, " ");
      if ($db_array != 0) {
        if ($userlogin==$db_array[1]) {
          print "=";
        } else {
          print "!";
          if ($update_flag==1) {
            update_db($userid, $userlogin, $link);
          }
        }
      } else {
        print "0";
      }
      print "\n";
    }
  }

}  // End function show_gab


function get_from_db($userid, $link) {
  $query="SELECT user_id,user_name FROM stores where user_id=$userid";  
    
  $result = mysql_query($query, $link) or die(mysql_error());

  $row=mysql_fetch_array($result);  

  if (mysql_num_rows($result)==0) {
    return 0;
  } else {
    return $row;
  }
}

function update_db($userid, $userlogin, $link) {
  $query="UPDATE stores SET user_name='$userlogin' where user_id=$userid";
  
  $result = mysql_query($query, $link) or die(mysql_error());
  
  echo "U";
}


?>

Personal tools