update-from-repos

#!/usr/bin/env perl

use 5.010;
use strict;
use warnings;

use autodie 'system';
use opts;

# FIXME
use FindBin;
use lib "$ENV{HOME}/gitdir/yum-repoquery/lib";
use lib "$FindBin::Bin/../lib";

use DateTime;
use DBI;
use File::Temp 'tempfile';
use JSON::XS;
use Path::Class;
use Scope::Guard;
use YUM::RepoQuery;
#use TryCatch;
use Try::Tiny;
use URI::Fetch;

use Fedora::App::Updates::Schema;

# debugging...
use Smart::Comments;

opts
    my $reload  => { isa => 'Bool', default => 0, alias => 'redo'         },
    my $do_only => { isa => 'Int',  default => 0, alias => 'do-only|only' },
    my $do_cpan => { isa => 'Bool', default => 0, }, # FIXME tmp anyways
    my $server  => { isa => 'Str',  default => '172.16.42.70' },
    my $stage   => { isa => 'Str',  default => 'fedora_stage' },

    my $do_pkgdb => { isa => 'Bool', default => 0 },
    ;

my ($TMPFH, $TMPFILE) = tempfile;

my @TABLES = qw{ packages conflicts provides requires files obsoletes };
my %TABLES = (
    conflicts => q{T.name, T.flags, T.epoch, 'T.version', 'T.release'},
    provides  => q{T.name, T.flags, T.epoch, 'T.version', 'T.release'},
    requires  => q{T.name, T.flags, T.epoch, 'T.version', 'T.release', T.pre},
    obsoletes => q{T.name, T.flags, T.epoch, 'T.version', 'T.release'},
    files     => q{T.name, T.type},
);

my %OTABLES = (
    changelog => 'T.author, from_unixtime(T.date), T.changelog',
);

my %FTABLES = (
    filelist => 'T.dirname, T.filenames, T.filetypes',
);

my @AUX_TABLES = qw{ packages rpmids rpmkeys id_to_id };

# all staging tables sans 'packages'
my %STAGE_TABLES = (%TABLES, %OTABLES, %FTABLES);

my $stage_dbh = DBI->connect(
    "dbi:mysql:$stage;hostname=$server;mysql_compression=1",
    @ARGV[0..1],
    { RaiseError => 1, PrintError => 1, },
);

my $fu = Fedora::App::Updates::Schema->connect(
    "dbi:mysql:fedora;hostname=$server;mysql_compression=1",
    @ARGV[0..1],
);

my $fu_dbh = $fu->storage->dbh;

if ($reload) {

    ### --reload passed, truncating logs, rpm_* tables...
    $fu_dbh->do("truncate rpm_$_") for ('packages', keys %STAGE_TABLES);
    $fu_dbh->do("truncate yum_repos_log");
}

my $repos;
if ($do_only) {

    $repos = $fu->resultset('YumRepositories')->search({ id => $do_only });
}
else {

    $repos = $fu->resultset('YumRepositories')->search({ active => 1 });
}

### temp load file is: $TMPFILE

if ($do_cpan) {

    ### doing cpan...

    Class::MOP::load_class('CPAN::SQLite');

    ### creating cpan/sqlite object...
    #my $obj = CPAN::SQLite->new(db_dir => dir->absolute);
    my $obj = CPAN::SQLite->new;

    ### creating db...
    $obj->index(setup => 1);

    ### load staging from update the cpan sqlite db...
    # hm.  for some reason db_dir doesn't seem to work as advertized...
    my $fn = file $ENV{HOME}, '.cpan', 'cpandb.sql';
    load_table_to_staging($fn, $_) for (qw{ auths chaps dists mods });

    ### copying cpan tables (staging) to master...
    $fu_dbh->do("replace into cpan_$_ select * from $stage.$_")
        for (qw{ auths chaps dists mods });

    exit;
}

# NOTE: a lot of this code is more or less lifted straight out of the old
# "check-upstream" script.  It, like these tables themselves, could use some
# serious attention.

if ($do_pkgdb) {

    my $url =
        'https://admin.fedoraproject.org/pkgdb/acls/bugzilla?tg_format=json';

    # load the classes we need
    #Class::MOP::load_class($_) for qw{ URI::Fetch JSON::XS };

    ### fetching the raw data...
    # FIXME I know, "not optimal"
    my $data =
        decode_json(URI::Fetch->fetch($url)->content)
        ->{bugzillaAcls}->{Fedora}
        ;

    ### now, load into db...
    my $user_rs = $fu->resultset('Users');
    my $pkg_rs  = $fu->resultset('Packages');
    my %seen;

    for my $name (keys %$data) {

        my $owner = $data->{$name}->{owner};
        $user_rs->find_or_create({ id => $owner }) unless $seen{$owner}++;
        $pkg_rs->update_or_create({ owner_id => $owner, name => $name });
    }

    exit;
}

my $try = 0;

MAIN_LOOP:
while (my $repo = $repos->next) {

    ### working on repo: $repo->name
    my $start = DateTime->now;
    my ($r, $sr) = ($repo->repo, $repo->srpm_repo);
    my $repo_ts = $r->mdinfo_primary->{timestamp};

    if ($repo->last_update_unixtime < $repo_ts) {

        try {
            pull_and_load_yum_repo($repo, $r, $sr);
        }
        catch {

            warn "Error: $_";

            do { warn "Retrying...\n"; redo MAIN_LOOP } if $try++ < 3;
            die;
        };

        $repo->create_related(logs => {
            repo_unixtime => $repo_ts,
            repo_date     => \"FROM_UNIXTIME($repo_ts)",
            start_time    => $start,
            stop_time     => \'NOW()',
            status        => 'SUCCESS',
        });
    }
    else {

        ### repo is up to date, skipping...
        $repo->create_related(logs => { status => 'UPTODATE' });
    }
}

# FIXME cleanups...
# unlink $TMPFILE;


sub pull_and_load_yum_repo {
    my ($repo, $r, $sr) = @_;

    # ## working on repo: $repo->name
    #my ($r, $sr) = ($repo->repo, $repo->srpm_repo);

    truncate_staging_tables();

    load_staging_from_repo($r);
    load_staging_from_repo($sr) if $sr;

    load_master_from_stage($repo);
}

sub truncate_staging_tables {

    ### truncating staging tables...
    $stage_dbh->do("truncate $_")
        for (@AUX_TABLES, keys %STAGE_TABLES);
}

sub load_staging_from_repo {
    my $r = shift @_;

    my $fn = $r->primary_db_file;
    load_table_to_staging($fn, $_) for ('packages', keys %TABLES);

    $fn = $r->other_db_file;
    load_table_to_staging($fn, $_) for (keys %OTABLES);

    $fn = $r->filelists_db_file;
    load_table_to_staging($fn, $_) for (keys %FTABLES);

    return;
}

sub load_table_to_staging {
    my ($fn, $table) = @_;

    my $cmd = qq{(echo ".mode csv"; echo "select * from $table;")};
    $cmd .= " | sqlite3 $fn > $TMPFILE";

    ### dumping from sqlite: $table
    system $cmd;

    my $sg = _disable_keys("$stage.$table");

    #### loading to stage: $table
    $stage_dbh->do(qq{
        load data local infile '$TMPFILE' ignore
        into table $table
        fields terminated by ',' optionally enclosed by '"'
    });

    return;
}

sub load_master_from_stage {
    my $repo = shift @_;

    my $repo_id = $repo->id;

    ### trim rpm_packages...
    # I'm told this is faster under mysql than a simple subquery...
    $stage_dbh->do("insert into rpmkeys select pkgid from packages");
    $fu_dbh->do("
        delete from rpm_packages
        where repo_id = $repo_id and not exists
            (select 1 from $stage.rpmkeys where `key` = rpmkey)
    ");

    ### trim stage.packages to the set of new packages not in rpm_packages...
    $stage_dbh->do("
        insert into rpmids
        select pkgkey from packages as p
            inner join fedora.rpm_packages as rp
            on p.pkgid = rp.rpmkey
        where rp.repo_id = $repo_id
    ");

    $stage_dbh->do("
        delete quick from packages
        where exists
            (select 1 from rpmids where id = packages.pkgkey)
    ");
        #where pkgid in (select `key` from rpmkeys)

    #$stage_dbh->do("delete from $_ where pkgkey not in (select pkgkey from packages)")
    $stage_dbh->do("delete quick from $_ where exists (select 1 from rpmids where id = $_.pkgkey)")
        for (keys %STAGE_TABLES);

    ### insert the new packages into rpm_packages...
    $fu_dbh->do("
        insert into rpm_packages
        select
            NULL, $repo_id,
            pkgid, name, arch, `version`, `epoch`, `release`, `summary`,
            description, url,
            from_unixtime(time_file), from_unixtime(time_build),
            rpm_license, rpm_vendor, rpm_group,
            rpm_buildhost, rpm_sourcerpm, rpm_header_start, rpm_header_end,
            rpm_packager, size_package, size_installed, size_archive,
            location_href, location_base, checksum_type
        from $stage.packages
    ");

    ### creating id_to_id mapping...
    $stage_dbh->do("
        insert into id_to_id
        select p.pkgkey, rp.id
        from packages as p inner join fedora.rpm_packages as rp
            on p.pkgid = rp.rpmkey
        where rp.repo_id = $repo_id
    ");

    ### update stage tables with new package keys (rpm_packages.id)...
    $stage_dbh->do("update $_ set pkgkey = (select new_id from id_to_id where old_id = $_.pkgkey)")
        for keys %STAGE_TABLES;

    for my $table (keys %STAGE_TABLES) {

        my $cols = $STAGE_TABLES{$table};

        ### updating: $table
        $fu_dbh->do("
            insert into rpm_$table
            select null, pkgkey, $cols
            from $stage.$table as T
        ");
    }

    return;
}

#sub _disable_keys{ $fu_dbh->do("ALTER TABLE $table DISABLE KEYS") }
#sub _enable_keys  { $fu_dbh->do("ALTER TABLE $table ENABLE KEYS")  }

sub _disable_keys {
    my $table = shift @_;

    $fu_dbh->do("ALTER TABLE $table DISABLE KEYS");
    return Scope::Guard->new(
        sub { $fu_dbh->do("ALTER TABLE $table ENABLE KEYS") }
    );
}

sub load_master_table_from_stage {
    my ($repo_id, $table) = @_;

    # disable keys, and set up a Scope::Guard object to handle reenabling
    # them, even if we bail for some reason
    my $sg = _disable_keys("$stage.$table");
    #my $sg = Scope::Guard->new(sub { _enable_keys("$stage.$table") });

    my $cols = $STAGE_TABLES{$table};

    ### updating: $table
    $fu_dbh->do("
        insert into fedora.rpm_$table
        select null, PKGID($repo_id, P.pkgid), $cols
        from $stage.$table as T inner join $stage.packages as P
            on T.pkgkey = P.pkgkey
    ");

    return;
}

### fini...