^^&&^^

Edit File: convert_roundcube_mysql2sqlite

#!/usr/local/cpanel/3rdparty/bin/perl
package Script::RCube::Mysql2Sqlite;

# cpanel - convert_roundcube_mysql2sqlite            Copyright 2016 cPanel, Inc.
#                                                           All rights Reserved.
# copyright@cpanel.net                                         http://cpanel.net
# This code is subject to the cPanel license. Unauthorized copying is prohibited

use strict;

use Try::Tiny;

use Cpanel::AccessIds::ReducedPrivileges ();
use Cpanel::Config::LoadCpConf           ();
use Cpanel::MysqlUtils                   ();
use Cpanel::DbUtils                      ();
use Cpanel::MysqlUtils::Connect          ();
use Cpanel::MysqlRun                     ();
use Cpanel::Config::LoadUserDomains      ();
use Cpanel::Email::RoundCube             ();
use Cpanel::Email::RoundCube::DBI        ();
use Cpanel::Quota::Temp                  ();

use Cpanel::PwCache ();
use Cpanel::Logger  ();
use Cpanel::Filesys ();

use File::Copy  ();
use File::Path  ();
use File::Slurp ();
use DBI;
use XML::Simple ();
use IPC::Open3  ();

$XML::Simple::PREFERRED_PARSER = "XML::SAX::PurePerl";

my $sqlite_table_file = '/usr/local/cpanel/base/3rdparty/roundcube/SQL/sqlite.initial.sql';

my $log_file = '/usr/local/cpanel/logs/roundcube_sqlite_convert_log';
my %opts     = ( 'alternate_logfile' => $log_file );
my $logger   = Cpanel::Logger->new( \%opts );

my $mysql_dbname = 'roundcube';
my $dbh;
my $cp_schema_version;
my @dumptables = qw(users identities contacts contactgroups contactgroupmembers);

my $time = time();

## if invoked as a script, there is nothing in the call stack
my $invoked_as_script = !caller();
__PACKAGE__->script(@ARGV) if ($invoked_as_script);

sub script {
    my ( $package, $opt_user, $opt_dbname ) = @_;
    my $cpconf = Cpanel::Config::LoadCpConf::loadcpconf();

my $db_type = 'mysql';
    if ( exists $cpconf->{'roundcube_db'} and ( $cpconf->{'roundcube_db'} eq 'sqlite' ) ) {
        $db_type = 'sqlite';
    }

if ( defined $opt_user ) {
        ## the optional user arg feature is currently *only* called as a script;
        ##   otherwise, all these exits would check $invoked_as_script

## Transfers.pm is the only user of $opt_user, which it uses in conjunction with $opt_user;
        ##   this changes the global variable $opt_dbname just in time to be used in &init_check
        if ( defined $opt_dbname ) {
            $mysql_dbname = $opt_dbname;
        }

if ( $db_type eq 'sqlite' && init_check($cpconf) ) {
            my $wasSuccess = convert_mysql_roundcube_to_sqlite($opt_user);
            if ($wasSuccess) {
                ## if the conversion was a complete success (as checked
                ##   above), we can safely archive and delete the MySQL
                ##   roundcube database. This will prevent future problems
                ##   with transfers, and in fact is the final solution for
                ##   the original Roundcube case 12162!
                ## $has_db already asserted via &init_check
                my $has_db = 1;

## Transfers.pm operates over a different dbname, and is responsible for the
                ##   removal of the temporary database
                unless ( defined $opt_dbname ) {
                    Cpanel::Email::RoundCube::archive_and_drop_mysql_roundcube( $has_db, $logger );
                }
                return 1;
            }
            die "Conversion for user '$opt_user' was not successful.\n";
        }

## SOMEDAY @GOLIVE: update-roundcube-sqlite-db gets moved to update-roundcube-db. Update this message and the conditional @GOIVE.
        die "The optional user argument is to be used only when Roundcube has been converted to sqlite. Use bin/update-roundcube-sqlite-db Exiting.\n";
    }

## Has the roundcube conversion already happened?
    ## note: this check is here, and not in init_check, because Transfers.pm calls into this
    ##   from a different context. The src server uses MySQL but the dest machine has done
    ##   the sqlite conversion.
    if ( $db_type eq 'sqlite' ) {
        $logger->info("Roundcube conversion already occurred, bailing out.");
        if ($invoked_as_script) {
            exit(0);
        }
        return 1;
    }

unless ( init_check($cpconf) ) {
        if ($invoked_as_script) {
            exit(0);
        }
        return 1;
    }

my %TRUEDOMAINS;
    Cpanel::Config::LoadUserDomains::loadtrueuserdomains( \%TRUEDOMAINS );
    my @users          = sort values %TRUEDOMAINS;
    my $success_cnt    = 0;
    my $success_verify = scalar @users;

for my $user (@users) {
        $success_cnt += convert_mysql_roundcube_to_sqlite($user);
    }

my $DIR = '/usr/local/cpanel/base/3rdparty';
    Cpanel::Email::RoundCube::generate_roundcube_config_sqlite( $DIR, $logger );

unless ( $success_cnt == $success_verify ) {
        $logger->warn("Roundcube Mysql to sqlite conversion was not completely successful. Please check $log_file for details.");

if ($invoked_as_script) {
            exit(1);
        }
        return;
    }

## if the conversion was a complete success (as checked above), we can safely archive and
    ##   delete the MySQL roundcube database. This will prevent future problems with transfers,
    ##   and in fact is the final solution for the original Roundcube case 12162!
    ## already asserted via &init_check
    my $has_db = 1;
    my $archive_success = Cpanel::Email::RoundCube::archive_and_drop_mysql_roundcube( $has_db, $logger );

require Cpanel::Config::CpConfGuard;
    my $cpconf_guard = Cpanel::Config::CpConfGuard->new();
    $cpconf->{'roundcube_db'} = $cpconf_guard->{'data'}->{'roundcube_db'} = 'sqlite';
    $cpconf_guard->save();

if ($invoked_as_script) {
        Cpanel::Email::RoundCube::restart_cpsrvd();
        exit(0);
    }

return 1;
}

sub convert_mysql_roundcube_to_sqlite {
    my ($user) = @_;
    my @domains = Cpanel::Email::RoundCube::collect_domains($user);

my @uid_name_pairs = collect_roundcube_user_info( \@domains, $user );

## returning 1: no conversion necessary
    return 1 unless ( scalar @uid_name_pairs );

## e.g. '/home'
    my $best_mnt_point   = Cpanel::Filesys::get_homematch_with_most_free_space();
    my $tmpdir_root_base = "$best_mnt_point/roundcube_convert";

if ( -d $tmpdir_root_base ) {
        rename( $tmpdir_root_base, "$tmpdir_root_base.$time" );
    }
    mkdir($tmpdir_root_base);

my $tmpdir_root_sys = "$tmpdir_root_base/$user";
    mkdir($tmpdir_root_sys);

my @pwinfo = Cpanel::PwCache::getpwnam($user);
    my ( $mmuid, $mmgid, $homedir ) = @pwinfo[ 2, 3, 7 ];
    my $tmpdir_emailuser_base = "$homedir/tmp/roundcube_convert";

my $success_cnt = 0;

for my $email_user_info (@uid_name_pairs) {
        my ( $uid, $email_user ) = @$email_user_info;

my $tmpdir_root_sys_emailuser = "$tmpdir_root_sys/$email_user";
        mkdir($tmpdir_root_sys_emailuser);

do_mysqldumps_for_user( $uid, $tmpdir_root_sys_emailuser );
    }

## ensures that $best/rcube/$sysuser/$emailuser is readable by $sysuser,
    ##   so that $sysuser can move it to their $homedir after the setuid

my $rv_chmod  = system("chmod -R 700 $tmpdir_root_sys");
    my $rv_chown  = system("chown -Rf $mmuid:$mmgid $tmpdir_root_sys");
    my $tempquota = Cpanel::Quota::Temp->new( user => $user, log => 1 );
    $tempquota->disable();

for my $email_user_info (@uid_name_pairs) {
        my ( $uid, $email_user ) = @$email_user_info;

chdir($tmpdir_root_base);

my $tmpdir_root_sys_emailuser = "$tmpdir_root_sys/$email_user";
        my $rv_convert = xml_to_sqlite( $user, $tmpdir_root_sys_emailuser, $tmpdir_emailuser_base, $email_user, $homedir );

unless ($rv_convert) {
            $logger->warn("Conversion was not successful for user '$user'.");
        }
        $success_cnt += $rv_convert;

## note: no need to "rmdir($tmpdir_root_sys_emailuser)", as this dir was moved
        ##   to $homedir/tmp/rcube/$emailuser (during the setuid)

## SOMEDAY @GOLIVE: remove the mysql roundcube database (steal mysql/roundcube backup/archive
        ##   clauses from update-roundcube-db, the mysql version)
    }

$tempquota->restore();

rmdir($tmpdir_root_sys);
    rmdir($tmpdir_root_base);

return $success_cnt == scalar(@uid_name_pairs);
}

sub collect_roundcube_user_info {
    my ( $ar_domains, $user ) = @_;
    my $regexp = '@(' . join( '|', map { s/\./\\./gr } @$ar_domains ) . ')$';
    my @ids;

try {
        @ids = $dbh->selectall_array( "SELECT user_id, username FROM users WHERE username REGEXP ? or username = ?", {}, $regexp, $user );
    };

return @ids;
}

## Stolen from pkgacct; needs to be modularized!
## SOMEDAY: this version has converged; move pkgacct version to module, and get rid of below
sub mysqldumpdb {
    my ($args) = @_;

my @options   = @{ $args->{'options'} };
    my $db        = $args->{'db'};
    my $table     = $args->{'table'};
    my $file      = $args->{'file'};
    my $file_mode = $args->{'append'} ? '>>' : '>';

# for testing
    my @extra_options = _get_extra_options();

my $mysqldump = Cpanel::DbUtils::find_mysqldump();
    my @db        = ($db);
    if ($table) {
        push @db, $table;
    }

#print join( ' ', ( @extra_options, @options, @db ) ) . $/;
    my $pid = IPC::Open3::open3( my $w, my $r, '', $mysqldump, @extra_options, @options, @db );

my $first_line = 1;
    if ( open( my $fh, $file_mode, $file ) ) {
        while (<$r>) {
            if ( $first_line && ( !$_ || m/^mysqldump:/ ) ) {
                warn join( '.', @db ) . ': ' . $_;
                close $w;
                close $r;
                waitpid( $pid, 0 );
                $first_line = 0;
                my $mysqlcheck = Cpanel::DbUtils::find_mysqlcheck();
                system( $mysqlcheck, '--repair', @extra_options, @db );
                $pid = IPC::Open3::open3( $w, $r, '', $mysqldump, @extra_options, @options, @db );
            }
            else {
                print {$fh} $_;
            }
        }
    }
    close $w;
    close $r;
    waitpid( $pid, 0 );
    return;
}

sub do_mysqldumps_for_user {
    my ( $uid, $tmp_convertdir ) = @_;

# for testing
    my $extra_options = _get_extra_options_string();

## XML output, not suppressing table structure, with a where clause on user_id list
    my @options = ('--xml');

for my $tbl (@dumptables) {
        ## these two tables do not key off user_id
        next if ( 'contactgroupmembers' eq $tbl );
        my @opts = @options;
        push @opts, '-w', qq{user_id = $uid};
        mysqldumpdb(
            {
                'options' => [@opts],
                'db'      => $mysql_dbname,
                'file'    => "$tmp_convertdir/rcube.$tbl.xml",
                'table'   => $tbl
            }
        );
    }

## contactgroupmembers is gathered in two steps, otherwise would be a LEFT JOIN that
    ##   &mysqldumpdb can not currently support

my @contacts;

try {
        @contacts = map { $_->[0] } $dbh->selectall_array( "SELECT contactgroup_id FROM contactgroups WHERE user_id = ?", {}, $uid );
    };

if (@contacts) {
        my $contacts_csv = join( ',', @contacts );
        mysqldumpdb(
            {
                'options' => [ @options, '-w', qq{contactgroup_id IN ($contacts_csv)} ],
                'db'      => $mysql_dbname,
                'file'    => "$tmp_convertdir/rcube.contactgroupmembers.xml",
                'table'   => 'contactgroupmembers',
            }
        );
    }

return undef;
}

sub create_rcube_sqlite_tables {
    my ($dbh)          = @_;
    my $sql            = File::Slurp::slurp($sqlite_table_file);
    my $statements_ref = Cpanel::Email::RoundCube::DBI::_yield_statements($sql);

for my $create_statement (@$statements_ref) {
        my $sth = $dbh->prepare($create_statement);
        my $rv  = $sth->execute();
    }
    return undef;
}

## SOMEDAY: the "UID conflict resolver" does a much better job of introspecting the table definition
##   (the column names, the columns that require quoting, and could also do nullables via
##   IS_NULLABLE); I attempted to abstract those ideas for sqlite, but the DBD::SQLite2 version
##   of &column_info is not up to the task
sub do_all_rcube_xml_to_db {
    my ( $dbh, $xmldir ) = @_;

my $success_cnt = 0;
    ## verify the number of XML docs to convert to SQL
    my $success_verify = scalar(@dumptables);
    {
        ## note: there is a disparity in the sqlite.initial.sql, and the "table_structure" for "users";
        ##   preferences is not NULLABLE.
        my @_cols        = qw(user_id username mail_host created last_login language preferences);
        my @_needs_quote = qw(username mail_host created last_login language preferences);
        $success_cnt += rcube_xml_to_db( $xmldir, 'users', $dbh, \@_cols, \@_needs_quote, );
    }

{
        my @_cols = qw( identity_id user_id changed del standard name organization email
          reply-to bcc signature html_signature);
        my @_needs_quote = qw( changed name organization email
          reply-to bcc signature);
        my @_nullables = qw(organization);
        $success_cnt += rcube_xml_to_db( $xmldir, 'identities', $dbh, \@_cols, \@_needs_quote, \@_nullables );
    }

{
        my @_cols        = qw(contact_id user_id changed del name email firstname surname vcard);
        my @_needs_quote = qw(changed name email firstname surname vcard);
        $success_cnt += rcube_xml_to_db( $xmldir, 'contacts', $dbh, \@_cols, \@_needs_quote );
    }

## new contactgroups table
    {
        my @_cols        = qw( contactgroup_id user_id changed del name );
        my @_needs_quote = qw( changed name );
        $success_cnt += rcube_xml_to_db( $xmldir, 'contactgroups', $dbh, \@_cols, \@_needs_quote );
    }

## new contactgroupmembers table
    ## guard clause: contactgroupmembers is gathered conditionally, so it is the only file which
    ##   may not exist
    if ( -e _xml_file( $xmldir, 'contactgroupmembers' ) ) {
        my @_cols        = qw( contactgroup_id contact_id created );
        my @_needs_quote = qw( created );
        $success_cnt += rcube_xml_to_db( $xmldir, 'contactgroupmembers', $dbh, \@_cols, \@_needs_quote );
    }
    else {
        $success_cnt++;
    }

return $success_cnt == $success_verify;
}

sub _xml_file {
    my ( $xmldir, $tbl ) = @_;
    return "$xmldir/rcube.$tbl.xml";
}

sub rcube_xml_to_db {
    my ( $xmldir, $tbl, $dbh, $ar_cols, $ar_needs_quote, $ar_nullables ) = @_;

my $xml_fname = _xml_file( $xmldir, $tbl );

my %common_opts = ( ForceArray => 1, KeyAttr => [], ContentKey => '__content' );

## $ref->{database}->[0]->{table_data}->[0]->{row}->[$x]->{field}->[$x]->{__content}
    my $ref;
    my $err;
    try {
        $ref = XML::Simple::XMLin( $xml_fname, %common_opts );
    }
    catch {
        $err = $_;
    };

if ($err) {
        $logger->warn("Failed to load xml for $tbl: $err");
        return;
    }

my $rows = $ref->{'database'}->[0]->{'table_data'}->[0]->{'row'};
    ## FWIW, Perl does not complain if $rows is undef, when called in a for loop context.
    for my $row (@$rows) {
        my $hr_vals = process_row( $row, $dbh, $ar_needs_quote, $ar_nullables );
        my $sql = rcube_make_sql( $tbl, $ar_cols, $hr_vals );

$sql =~ s/^INSERT INTO system/REPLACE INTO system/;

my $sth = $dbh->prepare($sql);
        my $rv  = $sth->execute();
        unless ($rv) {
            $logger->info("$sql");
            $logger->warn( "Conversion was not successful: " . $dbh->errstr() );
            return;
        }
    }

return 1;
}

sub rcube_make_sql {
    my ( $table, $ar_cols, $hr_vals ) = @_;

my @values;
    for my $col (@$ar_cols) {
        push( @values, $hr_vals->{$col} );
    }

## put the column names in single quotes. only really needed for 'reply-to'.
    my @quoted_cols = map { "'$_'" } @$ar_cols;

my $sql = "INSERT INTO $table (" . join( ', ', @quoted_cols ) . ")\nVALUES (" . join( ', ', @values ) . ")\n";

return $sql;
}

## Each field is given a default value of empty string or NULL (if nullable), then overriden
##   by its existing value of an integer or a DBI-quoted string.
sub process_row {
    my ( $row, $dbh, $ar_needs_quote, $ar_nullables ) = @_;
    my %f;

my %needs_quote = map { $_ => undef } @$ar_needs_quote;
    my %nullables;
    if ( defined $ar_nullables ) {
        %nullables = map { $_ => undef } @$ar_nullables;
    }

for my $field ( @{ $row->{'field'} } ) {
        my $fname = $field->{'name'};

my $content = "''";
        if ( exists( $nullables{$fname} ) ) {
            $content = 'NULL';
        }

if ( defined $field->{'__content'} ) {
            my $tmp = $field->{'__content'};
            if ( exists $needs_quote{$fname} ) {
                $tmp = $dbh->quote($tmp);
            }
            $content = $tmp;
        }
        $f{$fname} = $content;
    }
    return \%f;
}

sub check_mysqlup {
    ## note: this call is cached with a ttl of 600
    my $isrunning = Cpanel::MysqlRun::running();
    if ($isrunning) {
        return 1;
    }

return undef;
}

sub ensure_roundcube_tables {
    my ($dbh) = @_;

my %tables = map { $_ => undef } $dbh->tables();

# system table is created during a migration
    my @expected = qw(session users cache contacts identities
      contactgroups contactgroupmembers system
      cp_schema_version
    );

for my $exp (@expected) {
        if ( !exists $tables{$exp} && !exists $tables{qq{"main"."$exp"}} ) {
            $logger->warn("ERROR: missing $exp table");
            return;
        }
    }

## SOMEDAY: add logic that detects new tables (so that devel can be alerted)
    ## All of the expected table names were found in the database
    return 1;
}

sub init_check {
    my ($cpconf) = @_;
    ## Ensure root
    return unless ( 0 == $> );

unless ( -e $sqlite_table_file ) {
        $logger->info("Roundcube is not installed; conversion is irrelevant.");
        return;
    }

if ( exists $cpconf->{'skiproundcube'} and $cpconf->{'skiproundcube'} ) {
        $logger->info("Roundcube should be skipped, bailing out.");
        return;
    }

my $mysqlup = check_mysqlup();
    unless ($mysqlup) {
        $logger->warn("Mysql not currently running, bailing out.");
        return;
    }

## Does this installation use Roundcube/MySQL?
    unless ( Cpanel::MysqlUtils::db_exists($mysql_dbname) ) {
        $logger->warn("mysql database $mysql_dbname is missing");
        return;
    }

my $error;

try {
        my $dbc = Cpanel::MysqlUtils::Connect->new( database => $mysql_dbname );
        $dbh = $dbc->db_handle();
    }
    catch {
        $error = $_;
    };

if ($error) {
        $logger->warn("Failed to connect to MySQL database '$mysql_dbname'.");
        return;
    }

try {
        my $rows_hr = $dbh->selectrow_hashref( Cpanel::Email::RoundCube::DBI::get_sql_cp_schema_version() );
        $cp_schema_version = $rows_hr->{version};
    }
    catch {
        $error = $_;
    };

if ($error) {
        $logger->info("Roundcube cp_schema_version table missing. Please run '/usr/local/cpanel/bin/update-roundcube-db --force', and try again.");
        return;
    }

return 1;
}

##############################################################
# These functions allow tests to use a temporary MySQL server

sub _get_extra_options_string {

if ( scalar _get_extra_options() ) {
        return join( ' ', _get_extra_options() );
    }

return;
}

sub _get_extra_options {
    return @Cpanel::MysqlUtils::_EXTRA_MYSQL_ARGS;
}

#
##############################################################

sub xml_to_sqlite {
    my ( $user, $tmpdir_root, $tmpdir_convert_base, $email_user, $homedir ) = @_;
    my $code_ref = _closure_maker( $user, $tmpdir_root, $tmpdir_convert_base, $email_user, $homedir );
    my $rv = Cpanel::AccessIds::ReducedPrivileges::call_as_user( $code_ref, $user );
    return $rv;
}

## defining as a closure, as ::run_as_user does not currently handle sub args
sub _closure_maker {
    my ( $system_user, $tmpdir_root, $tmpdir_convert_base, $email_user, $homedir ) = @_;

my $code_ref = sub {
        my $tmp_convertdir = "$tmpdir_convert_base/$email_user";

if ( -d $tmp_convertdir ) {
            rename( $tmp_convertdir, "$tmp_convertdir.$time" );
        }

# Copy the XML files over to the user's homedir.
        # Hopefully these are simple renames. But directories may be on different
        # mount points requiring a copy.

File::Path::make_path($tmp_convertdir);

for my $table (qw{ users identities contacts contactgroups contactgroupmembers }) {
            my $src  = _xml_file( $tmpdir_root,    $table );
            my $dest = _xml_file( $tmp_convertdir, $table );
            if ( -e $src ) {
                File::Copy::move( $src, $dest );
            }
        }

# Clean up empty source directory.
        # This will fail if the above moves failed.

rmdir($tmpdir_root);

## note: similar clause in cpsrvd to set up $ENV{'_RCUBE'}
        my ( $mail_user, $mail_domain ) = split( '@', $email_user );

my $rcube_sqlite_loc8;
        if ( defined $mail_domain ) {
            File::Path::make_path( "$homedir/etc/$mail_domain", { owner => "$system_user", group => 'mail', chmod => 0750 } );
            $rcube_sqlite_loc8 = "$homedir/etc/$mail_domain/$mail_user.rcube.db";
        }
        else {
            File::Path::make_path( "$homedir/etc", { owner => "$system_user", group => 'mail', chmod => 0750 } );
            $rcube_sqlite_loc8 = "$homedir/etc/$mail_user.rcube.db";
        }

if ( -e $rcube_sqlite_loc8 ) {
            rename( $rcube_sqlite_loc8, "$rcube_sqlite_loc8.$time" );
        }

my $dbh = DBI->connect( "dbi:SQLite:dbname=$rcube_sqlite_loc8", "", "" ) or do {
            $logger->info("Connection error to $rcube_sqlite_loc8: $!");
            return;
        };
        ## note: $dbh->{sqlite_version} is confirmed 2.8.15
        create_rcube_sqlite_tables($dbh);
        unless ( Cpanel::Email::RoundCube::DBI::create_cp_schema_version_table( $dbh, 'sqlite', { logger => $logger } ) ) {
            $logger->info("Unable to create cp_schema_version table");
            return;
        }

return unless ensure_roundcube_tables($dbh);

unless ( do_all_rcube_xml_to_db( $dbh, $tmp_convertdir ) ) {
            $logger->info("Conversion of roundcube XML dump to sqlite database failed");
            return;
        }
        unless (
            Cpanel::Email::RoundCube::DBI::save_cp_schema_version(
                $dbh, 'sqlite',
                $cp_schema_version,
                { logger => $logger }
            )
          ) {
            $logger->info("Unable to save schema version");
            return;
        }

$dbh->disconnect();

chmod( 0600, $rcube_sqlite_loc8 );

## the return values on these rm calls are not critical
        ## only remove the /tmp directory if the XML converion has been successfull thus far

try {
            File::Path::remove_tree($tmp_convertdir);
        };

## attempt to rmdir, which will intentionally fail if any of the converts did not succeed
        rmdir($tmpdir_convert_base);

return 1;
    };
    return $code_ref;
}