restoring documents on network edition

[hchan]

Does anyone have a pointer here on how to restore a document for an individual account? I don't want to restore a whole account, just a document under the documents tab.

Thanks in advance.

[Rich Graves]

There is no reasonable way to do that (the unreasonable way is below). The only supported answer is to perform a point-in-time restore of the whole account and then manually move the file. Relevant RFEs:

Bug 8849 - More Granular Restore: per folder & per-message Bug 15754 - Ability to backup and restore individual emails

Unless the account has so much mail that it would seriously impact operations, the supported brute-force restore-it-all approach is probably faster. Yes, restoring the whole account is wasteful and it takes time, but it's mostly just the computer's time, not yours. Your attention is probably more valuable than a few billion CPU cycles and a few hundred megabytes of disk.

That said, here's a totally unreasonable way to restore individual items. It only works from full backups, not incrementals.

Get the user's zimbraId.

[zimbra@zimbra5 ~]$ zmprov ga joeuser@zimbra5 | grep zimbraId
zimbraId: 86142e96-ded6-4837-abc2-b7cf5c4b3c00

cd into the blob area for a recent full backup of that user's account. Observe how the first 6 characters create a directory hash tree.

[zimbra@zimbra5 ~]$ cd /opt/zimbra/backup/sessions/full-20071201.025346.937/accounts/861/42e/86142e96-ded6-4837-abc2-b7cf5c4b3c00/

Grep the backup of that account's index db for the document name or the first line of document content. Yes, *everything* is in mail_item.dat, regardless of item type (contact, email, appointment, document).

[zimbra@zimbra5 86142e96-ded6-4837-abc2-b7cf5c4b3c00]$ grep Silly db/mail_item.dat
15,5901,14,\N,12,5901,5901,1196477596,19,1,"W561a6 xnv8tsy850D0aWwPk3bGw=",\N,0,0
,\N,"Silly","Silly","d2:cr28:joeuser@zimbra52:ct24 :text/html; charset
=utf-81:f14:This is a silly page.1:vi10ee",3538,1196477596,3538

The full content of the document can be found in the subdirectory blobs/1/1/ with a filename starting with W561a6xnv8tsy850D0aWwPk3bGw=. Tab completion is your friend.

[troy]

neat...but then, how does one get the "document under the documents tab"?

If you have the file content, how do you tell Zimbra what to do with it to get the user's document back?

Is there some kind of zmmailbox addMessage method for documents?

[Rich Graves]

The dumb way: Create a new doc, copy-paste stuff rescued from the blob.

ZCS 5 has trash and version control for documents, so this class of problem is somewhat less likely to appear in the future.

Bugzilla 8849 and 15754, or doing a point-in-time restore of the whole account, are the real answers. Unfortunately, there is no supported way to move a document from one user's wiki to another (appointment movement between calendars was recently added), so even then you'll be copy-pasting.

[troy]

I badly needed a solution to this so I read some zmmailbox code and thought I could hack my way through by using the zmmailbox addMessage and then perl DBI to the mysql database to hack the Zimbra meta data so it doesn't look like mail but wiki. I know it sounds awful, but is it really all that bad?

Also note, I built this on my ZCS 5 server and it wouldn't work on my 4.5.10 production.

Hear is the code for zimbracopydocs (please read this before trying to run it):

#!/usr/bin/perl


# Name:      zimbracopydocs
# Purpose:   a wiki document copy designed for copying wiki documents from
#            a users restored account to the users production account
# Version:   0.1 (alpha)
# Author:    Troy Adams (troy { at } troyadams { dot } ca)
#            http://blog.troyadams.ca/?p=3
# Date:      Thu Jan 24 11:09:18 MST 2008
# License:   GPL-3
# Usage:     zimbracopydocs --help
#            
# Warning:   No warranty!  This is program is for acedemic analysis only. 
#            Read the code yourself!
# 

use Env;

my $TEMP_FILE = "/tmp/zmcopyfoldertmp.$$";
my $TEMP_FILE2 = "/tmp/zmcopyfoldertmp-metadata.$$";
my $EXEC_FILE = "/tmp/zmcopyfolder.execute.$$";

my $zmMAILBOX = "/opt/zimbra/bin/zmmailbox";
my $zimbraMSGMETA="/root/bin/zimbramsgmeta"; 
my $MAX_SEARCH=1000;


#getFolderDocumentIDs
#addDocumentFolder
#foreach documentID
#  getDocumentMetaData
#  addMessage filename
#done

#zmmailbox -z -m pmdftest addMessage /HR /opt/zimbra/store/0/155/msg/1/4485-16915.msg

sub quit
{
  if (-f $TEMP_FILE) { unlink $TEMP_FILE or die "ERROR: cannot remove '$TEMP_FILE'.\n" }
  if (-f $EXEC_FILE) { unlink $EXEC_FILE or die "ERROR: cannot remove '$EXEC_FILE'.\n" }
  exit shift;
}

sub giveup
{
  print STDERR "@_\n";
  quit 1
}


sub execute
{
  my @exec_cmd = @_;
  push @exec_cmd, ">$EXEC_FILE";
  my $x = "@exec_cmd";
  my @system = ($x);
  my @output = ();

  print "executing '$x'\n" unless $DEBUG<2;
  if ($commandlog)
  {
    open  CMDLOG, ">>$commandlog";
    print CMDLOG  "$x\n";
    close CMDLOG;
  }

  system($x) == 0 or return 1;
  open EXEC_TMP, "$EXEC_FILE" or giveup "ERROR: failed to retrieve execution output\n";
  while (<EXEC_TMP>)
  {
    push @output, $_;
  }
  close EXEC_TMP;

  system("cat $EXEC_FILE") unless $DEBUG<3;

  unlink "$EXEC_FILE" or giveup "ERROR: failed to cleanup execution output\n";

  return (0, @output);
}


sub getFolderDocumentIDs
{
  my $user   = shift;
  my $path   = shift;
  my $type   = shift;
  my $code   = "";
  my @output = ();
  my @idlist = ();
  my $i      = 0;

  print STDERR "DEBUG: username: $user, path: $path, type: $type\n" unless !$DEBUG;

  # enumerate source target contents for $user:$path of $type
  ($code, @output) = execute "$zmMAILBOX -z -m $user search -l $MAX_SEARCH -t $type 'in:\"$path\"' 2>&1";
  $code == 0 or giveup "ERROR: enumerate source target contents failure";
  $i = 0; # source target message counter
  foreach (@output)
  {
    next unless (/  $type   /);
    $i++;
    #//s/^.*\. //;
    s/^.{1,4}\.\s+([\d ]{1,6}) /$1 /;
    push @idlist, (split / +/)[0];
  }
  print "zimbracopydocs: source target contains $i ";
  print "or more " unless $i <1000;
  print "messages\n";
  if ($i == $MAX_SEARCH)
  {
    print "WARNING: It seems that, due to a limitation of zmmailbox, this program can\n";
    print "WARNING: only handle folders with a maximum of 1000 messages.\n"
  }
  
  return @idlist;
}

sub addFolder
{
  my $user   = shift;
  my $path   = shift;
  my $type   = shift;
  my $msgid  = 0;

  # zmmailbox -z -m pmdftest createFolder -V wiki /HR  
  ($code, @output) = execute "$zmMAILBOX -z -m $user createFolder -V $type '$path' 2>&1";
  $code == 0 or giveup "ERROR: create $type folder failure ($code)" unless $DEBUG>5;
  $msgid = $output[0];
  print STDERR "DEBUG[$DEBUG]: new folder msgid: $msgid\n" unless $DEBUG<2;

  return $msgid;
}

sub addMessage
{
  my $user   = shift;
  my $path   = shift;
  my $file   = shift;

  # zmmailbox -z -m troy addMessage /folder /path/textfile
  ($code, @output) = execute "$zmMAILBOX -z -m $user addMessage '$path' '$file'";
  $code == 0 or giveup "ERROR: add message failure ($code)";
  ($msgid,$junk) = split / /,$output[0];
  print STDERR "DEBUG[$DEBUG]: new message msgid: $msgid\n" unless $DEBUG<2;

  return $msgid;
}

sub putDocumentMetaData
{
  my $user   = shift;
  my $msgid  = shift;
  my $meta   = shift;

  #foreach (keys (%meta)) { print "$_ = '$meta{$_}'\n"; }
  #print "query: ";
  open SQLSETPIPE, "|$zimbraMSGMETA --msgid $msgid --user $user --updaterow";
  print SQLSETPIPE "$meta\n";
  close SQLSETPIPE;
}

sub buildsqlset
{
  my $field  = shift;
  my $meta  = shift;

  #foreach (keys (%$meta)) { print "$_ = '$$meta{$_}'\n"; }
  return qq|$field="$$meta{$field}"|;
}

sub usage
{
  print "$0 --source username\@host:folderpath --destination username\@host:folderpath\n";
  print "\n";
  print "$0 does not currently support copying across hosts but we can do that later.\n";
  print "\n";
  print "example usage:\n";
  print "  $0 --source restored_username\@mydomain.com:/Notebook --destination username\@mydomain.com:/Notebook\n";
  print "\n";
  print "Warning:   No warranty!  Read the code yourself!\n";
  print "           This code implements a mean hack by calling zmmailbox\n";
  print "           addMessage to add the document and then running back\n";
  print "           to the database to scribble on the meta data to make\n";
  print "           it look like a wiki document instead of a mail message.\n";
  print "           Is this bad?\n";
  print "\n";
  print "\n";
}



#foreach documentID
#  getDocumentMetaData
#  getDocumentFilename
#  addMessage filename
#  putDocumentMetaData
#done
my %source_metadata=();

# (1) aquire and verify command-line options
use Getopt::Long;
GetOptions("verbose!"=>\$verbose,            # optional flag verbose
           "source=s"=>\$source,             # source is mandatory
           "destination=s"=>\$destination,   # destination is mandatory
           "help!"=>\$help);                 # option help

if ($ARGV[0])
{
  print "I didn't understand the following command-line options:\n";
  foreach (@ARGV)
  {
    print "  $_\n";
  }

  quit 1;
}

if ($help or !($source and $destination)) { usage ; quit 0 }


print "source:      $source\n" unless !$verbose;
print "destination: $destination\n" unless !$verbose;

# (2) split out and verify src/dst components
(my $source_user, my $source_host, my $source_path) = split /[\@\:]/, $source;
(my $destination_user, my $destination_host, my $destination_path) = split /[\@\:]/, $destination;
if ($source_user eq $destination_user) { $preservetags=1; }

print STDERR "DEBUG: $source_user, $source_host, $source_path\n" unless !$DEBUG;
print STDERR "DEBUG: $destination_user, $destination_host, $destination_path\n" unless !$DEBUG;

my @document_ids=getFolderDocumentIDs($source_user, $source_path, "wiki");
addFolder($destination_user, "$destination_path", "wiki");

foreach $document_id (@document_ids) 
{ 
  print "processing $source_user,$document_id-";
  print STDERR "-source-$source_user,$document_id-----------\n" unless !$DEBUG;
  foreach (execute "$zimbraMSGMETA --msgid $document_id --user $source_user --allmeta")  
    {
       # execute will return a list starting with the shell return code
       #   we will chose to not even handle the return code
       print STDERR unless !$DEBUG;
       chomp;
       s/\s*$//;
       s/=/===/;
       s/,$//;
       ($field_name, $field_value) = split /===/;
       $source_metadata{$field_name}=$field_value;
    }
  print "'$source_metadata{\"subject\"}' => ";
  #$source_metadata{"name"} .= "new";
  $filename = (execute "$zimbraMSGMETA --msgid $document_id --user $source_user --filename")[1];
  chomp $filename;
  $dest_id=addMessage($destination_user, $destination_path, $filename);
  $sqlset=buildsqlset("flags",       \%source_metadata) . ","
        . buildsqlset("type",        \%source_metadata) . ","
        . buildsqlset("unread",      \%source_metadata) . ","
        . buildsqlset("name",        \%source_metadata) . ","
        . buildsqlset("sender",      \%source_metadata) . ","
        . buildsqlset("subject",     \%source_metadata) . ","
        . buildsqlset("size",        \%source_metadata) . ","
        . buildsqlset("date",        \%source_metadata) . ","
        . buildsqlset("blob_digest", \%source_metadata) . ","
        . buildsqlset("metadata",    \%source_metadata);
  #DEBUG:$sqlset =~ s/($source_metadata{"name"})/$1-x/;
#  print "$sqlset\npushing to db:";
  print "$destination_user,$dest_id - ";
  putDocumentMetaData($destination_user, $dest_id, $sqlset);
  print STDERR "-destination-$destination_user,$dest_id-----\n" unless !$DEBUG;
  print STDERR execute "$zimbraMSGMETA --msgid $dest_id --user $destination_user --allmeta" unless !$DEBUG;  
  print "done\n";
}

#print getFolderDocumentIDs("troy", "Notebook_x3", "wiki");

print "\n\n\n";

[troy]

oops, I forgot to post the other code that is required for this to run.

Here is '/root/bin/zimbramsgmeta':

#!/usr/bin/perl

use DBI; #load DBI module

# PURPOSE: to provide answers to simple metadata queries about Zimbra messages
# REFERENCES: http://wiki.zimbra.com/index.php?title=Account_mailbox_database_structure


sub failed { return "     [FAILED]\n"; }
sub succeeded { return "     [  OK  ]\n"; }
sub echo { print @_ unless !$verbose }

sub getPassword
{
  my $password="";

  echo "Retreiving mysql root password: ";
  $password = (split "\s*=\s*", `/opt/zimbra/bin/zmlocalconfig -s | grep mysql_root_password`)[1];
  $password =~ s/\s//g;

  if ($password) { echo succeeded() }
  else           { echo failed() }

  return $password;
}

sub getMailboxID
{
  my $username=shift;
  my $mailboxid=0;

  echo "Retreiving mailbox id for '$username': ";
  $mailboxid = (split "\s*:\s*", `zmprov getMailboxInfo $username | grep mailboxId`)[1];
  $mailboxid =~ s/\s//g;

  if ($mailboxid) { echo succeeded() }
  else            { echo failed() }

  return $mailboxid;

}

sub usage()
{
  print "Query mode:\n";
  print "  zmmsgquery --user username --msgid somenumber --query field\n";
  print "Filename mode:\n";
  print "  zmmsgquery --user username --msgid somenumber --filename\n";
  exit;
}

# (-) DBI connect
sub dbiConnect
{
  my $database = shift;
  my $password = shift;
  echo "DBI:mysql connection: ";
  if ($handle = DBI->connect("DBI:mysql:$database:localhost;mysql_socket=/opt/zimbra/db/mysql.sock", "root", $password)) 
    {
       echo succeeded();
    }
  else
    {
       echo failed();
       die "fatal error";
    }
    return $handle;
}

# (-) DBI mysql: get mail_item table description
sub dbiAvailableFields
{
  my $table=shift;
  my %fields=();
  my $i=0;
  $sth= $dbh->prepare("DESCRIBE $table;") or die "Cannot prepare query: $DBI::errstr\n", $DBI::errstr;
  $sth->execute or die "Cannot execute query: $DBI::errstr\n", $DBI::errstr;
  while (@x=$sth->fetchrow())
  { $fields{@x[0]}=$i++; }
  return %fields;
}

# (-) DBI mysql: specified table/row
sub dbiUpdateRow
{
  my $table = shift;
  my $msgid = shift;
  my $mailboxid = shift;
  my @row=();

  my $sqlquery="update $table set ";
  while (<STDIN>)
  {
    chomp; $sqlquery .= $_;
  }
  $sqlquery .= " where id=$msgid and mailbox_id=$mailboxid;\n";
  #print "DEBUG sqlquery: $sqlquery\n";


  my $sth= $dbh->prepare($sqlquery);
  die "Cannot prepare table update: $DBI::errstr\n", $DBI::errstr unless($sth);
  $sth->execute;
  die "Cannot execute table update: $DBI::errstr\n", $DBI::errstr unless($sth);
  #foreach $field ($sth->fetchrow())
  #{
  #  push @row, $field;
  #}
  #  return @row;
}

# (-) DBI mysql: pull a query from the specified table
sub dbiQueryRow
{
  my $table = shift;
  my $msgid = shift;
  my $mailboxid = shift;
  my $sqlquery="select * from $table where id=$msgid and mailbox_id=$mailboxid;";
  my @row=();
  my $sth= $dbh->prepare($sqlquery);
  die "Cannot prepare query: $DBI::errstr\n", $DBI::errstr unless($sth);
  $sth->execute;
  die "Cannot execute query: $DBI::errstr\n", $DBI::errstr unless($sth);
  foreach $field ($sth->fetchrow())
  {
    push @row, $field;
  }
    return @row;
}



# (1) aquire and parse command-line options
use Getopt::Long;
GetOptions("verbose!"=>\$verbose,            # optional flag verbose
           "user:s"=>\$user,                 # optional string
           "quiet!"=>\$quiet,                # optional flag
           "allmeta!"=>\$allmeta,            # optional flag
           "updaterow!"=>\$updaterow,        # optional flag
           "filename!"=>\$filename,          # optional flag
           "help!"=>\$help,                  # optional flag
           "mailboxid:s"=>\$mailboxid,       # optional string
           "query:s"=>\$query,               # optional string
           "msgid=s"=>\$msgid);              # mandatory string
usage unless (!$help);
if (!$mailboxid && !$user)
  { print "You must specify either --mailboxid or --user\n"; die }
$mailboxid = getMailboxID($user) unless $mailboxid;

echo "user:  $user\n";
echo "msgid: $msgid\n";

# (2) set some variables
my $mboxgroup = $mailboxid % 100;
my $database = "mboxgroup$mboxgroup";
my $store_sub = $mboxgroup >> 12;
my $password  = getPassword();
my $msgid_hash = $msgid >> 12;

# (3) connect to the mysql server
$dbh = dbiConnect $database,$password;

# (4) query available fields in the "mail_item" table
my %mail_item=dbiAvailableFields "mail_item";

# (5) show the query options if the user doesn't seem to understand
if ($query eq "" && !$filename && !$allmeta && !$updaterow)
{
  print "query options:\n";
  foreach $field (keys %mail_item) { print "  $field\n" }
  exit;
}

if ($updaterow)
{
  dbiUpdateRow "mail_item", $msgid, $mailboxid;
  exit;
}

# (6) pull in the metadata from table "mail_item"
my @msg_meta = dbiQueryRow "mail_item", $msgid, $mailboxid;

# (7) display all the metadata
if ($allmeta)
{
  foreach $field (sort keys %mail_item) 
    {
      print "$field=$msg_meta[$mail_item{$field}], \n";
    }
  print "filename=/opt/zimbra/store/$store_sub/$mailboxid/msg/$msgid_hash/$msgid-$msg_meta[$mail_item{'mod_content'}].msg\n";
  exit;
}

# (8) show the filename associated with the given msgid and quit
if ($filename)
{
  if ($verbose) { print "MSG:$msgid file: " }
  print "/opt/zimbra/store/$store_sub/$mailboxid/msg/$msgid_hash/$msgid-", $msg_meta[$mail_item{'mod_content'}], ".msg\n";
  exit;
}

# (9) print out the query result
if ($quiet) { print "$msg_meta[$mail_item{$query}]\n" }
else { print "MSG:$msgid\[$query\] = $msg_meta[$mail_item{$query}]\n"; }

[mmorse]

Looks cool