Results 1 to 7 of 7

Thread: restoring documents on network edition

  1. #1
    hchan is offline Active Member
    Join Date
    Sep 2007
    Posts
    37
    Rep Power
    7

    Default restoring documents on network edition

    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.

  2. #2
    Rich Graves is offline Outstanding Member
    Join Date
    Jan 2007
    Location
    Minnesota
    Posts
    718
    Rep Power
    9

    Default

    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.

  3. #3
    troy is offline Junior Member
    Join Date
    Jun 2007
    Location
    Athabasca, Alberta, Canada
    Posts
    8
    Rep Power
    7

    Default

    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?
    Last edited by troy; 01-16-2008 at 12:37 PM.

  4. #4
    Rich Graves is offline Outstanding Member
    Join Date
    Jan 2007
    Location
    Minnesota
    Posts
    718
    Rep Power
    9

    Default

    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.

  5. #5
    troy is offline Junior Member
    Join Date
    Jun 2007
    Location
    Athabasca, Alberta, Canada
    Posts
    8
    Rep Power
    7

    Default

    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):
    Code:
    #!/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";

  6. #6
    troy is offline Junior Member
    Join Date
    Jun 2007
    Location
    Athabasca, Alberta, Canada
    Posts
    8
    Rep Power
    7

    Default

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

    Here is '/root/bin/zimbramsgmeta':

    Code:
    #!/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"; }

  7. #7
    mmorse's Avatar
    mmorse is offline Moderator
    Join Date
    May 2006
    Location
    USA
    Posts
    6,242
    Rep Power
    20

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. moving from Network Edition to Open Source Edition
    By devasish2000 in forum Installation
    Replies: 1
    Last Post: 11-01-2007, 12:45 AM
  2. Replies: 1
    Last Post: 12-19-2006, 02:58 PM
  3. OS Requirements for Network Edition
    By mrichard in forum Installation
    Replies: 6
    Last Post: 11-22-2006, 02:52 PM
  4. Downgrading from Network Edition
    By tkrag@mostlyharmless.dk in forum Administrators
    Replies: 3
    Last Post: 07-03-2006, 01:11 PM
  5. Replies: 7
    Last Post: 04-21-2006, 09:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •