#!/usr/bin/perl
#    Purpose:   This script converts an old imp preferences database contents
#               to the newer horde table format, it was written for
#               mysql, but could be easily ported to other databases
#
#    Usage:     Modify the variables at the beginning of the script
#               so that your database user/password, location and imp
#               database and horde database are correct. 
#

 
 use DBI;
  
  my $location       = 'localhost';
  my $port_num       = '3306';
  my $username       = '';
  my $password       = '';
   
  my $IMP_DATABASE   = 'horde';
  my $IMP_TABLE      = 'imp_pref';
  my $HORDE_DATABASE = 'horde2';
  my $HORDE_TABLE    = 'horde_prefs';
  my $domain	      = 'example.com';
  my $identity_name  = 'Default Identity';
    
  my $dbi_options = {RaiseError => 1, ChopBlanks => 1, AutoCommit => 1};
     
  $db_imphandle = DBI->connect
                       ("DBI:mysql:$IMP_DATABASE:$location:$port_num",
                         $username, $password, $dbi_options )
                         || die ("Connection error: $DBI::errstr");
									     
  $db_horde = DBI->connect
                       ("DBI:mysql:$HORDE_DATABASE:$location:$port_num",
                         $username, $password, $dbi_options )
                         || die ("Connection error: $DBI::errstr");

  $imp_statement = $db_imphandle->prepare("SELECT username,fullname,replyto,lang,sig FROM $IMP_TABLE");
  $imp_statement->execute();
   
   $horde_statement = "DELETE FROM $HORDE_TABLE";
    $horde_statement = $db_horde->prepare($horde_statement)
       || die "prepare: $$stmt: $DBI::errstr";
    $horde_statement->execute || die "execute: $$stmt: $DBI::errstr";
													  
  while (my ($owner, $fullname, $replyto, $lang, $sig) =
         $imp_statement->fetchrow_array())
											    
  {
													      
    #print "$owner, $fullname, $replyto, $lang, $sig\n";

    #@chars     = ( "A" .. "Z", "a" .. "z", 0 .. 9, qw(! @ $ % ^ & *) );
    #$unique_key = join("", @chars[ map { rand @chars } ( 1 .. 31 ) ]);
														     
# Remove the @domain.com part from the $owner, doesn't work in Turba
    $owner =~ s/\@.*$//;
    $owner .= "\@$domain";
															 
# Quote the strings appropriately for the database
    my $quoted_owner = $db_imphandle->quote($owner);
    my $quoted_sig = $db_imphandle->quote($sig);
    my $quoted_lang = $db_imphandle->quote($lang);
    my $quoted_fullname = $db_imphandle->quote($fullname);
    my $value = "a:1:{i:0;a:12:{s:2:\"id\";s:19:\"$owner\";s:8:\"fullname\";s:".length($fullname).":\"$fullname\";s:9:\"from_addr\";s:0:\"\";s:12:\"replyto_addr\";s:".length($replyto).":\"$replyto\";s:9:\"signature\";s:".length($sig).":\"$sig\";s:9:\"sig_first\";i:0;s:10:\"sig_dashes\";s:1:\"1\";s:14:\"save_sent-mail\";N;s:16:\"sent_mail_folder\";s:15:\"INBOX.sent-mail\";s:14:\"save_sent_mail\";s:1:\"1\";s:11:\"private_key\";N;s:16:\"private_key_type\";N;}}";
    my $quoted_value = $db_imphandle->quote($value);
#print "$value\n"; 

    $horde_statement = "INSERT INTO $HORDE_TABLE VALUES
                        ($quoted_owner, 'horde', 'identities', $quoted_value)";
    $horde_statement = $db_horde->prepare($horde_statement)
       || die "prepare: $$stmt: $DBI::errstr";
    $horde_statement->execute || die "execute: $$stmt: $DBI::errstr";
    # from_addr
    $horde_statement = "INSERT INTO $HORDE_TABLE VALUES
                        ($quoted_owner, 'horde', 'from_addr', $quoted_owner)";
    $horde_statement = $db_horde->prepare($horde_statement)
       || die "prepare: $$stmt: $DBI::errstr";
    $horde_statement->execute || die "execute: $$stmt: $DBI::errstr";
    # fullname
    if ($fullname) {
       $horde_statement = "INSERT INTO $HORDE_TABLE VALUES
                           ($quoted_owner, 'horde', 'fullname', $quoted_fullname)";
       $horde_statement = $db_horde->prepare($horde_statement)
          || die "prepare: $$stmt: $DBI::errstr";
       $horde_statement->execute || die "execute: $$stmt: $DBI::errstr";
     }
       # signature
     if ($sig) {
       $horde_statement = "INSERT INTO $HORDE_TABLE VALUES
                           ($quoted_owner, 'horde', 'signature', $quoted_sig)";
       $horde_statement = $db_horde->prepare($horde_statement)
          || die "prepare: $$stmt: $DBI::errstr";
       $horde_statement->execute || die "execute: $$stmt: $DBI::errstr";
     }
       # lang
     if ($lang) {
       $horde_statement = "INSERT INTO $HORDE_TABLE VALUES
                           ($quoted_owner, 'horde', 'language', $quoted_lang)";
       $horde_statement = $db_horde->prepare($horde_statement)
          || die "prepare: $$stmt: $DBI::errstr";
       $horde_statement->execute || die "execute: $$stmt: $DBI::errstr";
     }
       $horde_statement->finish();
															        
 }
																										  
 $imp_statement->finish();
 $horde_statement->finish();
 $db_imphandle->disconnect;
 $db_horde->disconnect;
