updateinfo.pl

#!/usr/bin/perl -w

#/home/mirror/bin/updateinfo.pl -a i386 -H rhn.redhat.com -u xxx -p xxx -c rhel-i386-server-5 -x /home/mirror/db/rhel-i386-server-5.xml -d /home/mirror/db/rhel-i386-server-5.db
#/home/mirror/bin/updateinfo.pl -a x86_64 -H rhn.redhat.com -u xxx -p xxx -c rhel-x86_64-server-5 -x /home/mirror/db/rhel-x86_64-server-5.xml -d /home/mirror/db/rhel-x86_64-server-5.db
#/home/mirror/bin/updateinfo.pl -a i386 -H rhn.redhat.com -u xxx -p xxx -c rhel-i386-server-6 -x /home/mirror/db/rhel-i386-server-6.xml -d /home/mirror/db/rhel-i386-server-6.db
#/home/mirror/bin/updateinfo.pl -a x86_64 -H rhn.redhat.com -u xxx -p xxx -c rhel-x86_64-server-6 -x /home/mirror/db/rhel-x86_64-server-6.xml -d /home/mirror/db/rhel-x86_64-server-6.db

use strict;

$|=1;

#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.

#    For use of the RedHat Network Hosted API see https://rhn.redhat.com/rhn/apidoc/

# needed perl modules
my @modCheck = qw(
  Frontier::Client
  HTML::Entities
  Getopt::Long
  Crypt::SSLeay
  Time::HiRes
  Pod::Usage
  DBI
  DBD::SQLite
  XML::Writer
  IO::File
);

for (@modCheck) {
  eval "use $_"; if ($@) {
    die "$_ is not installed
CPAN install: perl -MCPAN -e 'install $_'\n";
  }
}

# code begin

my( $opt_h, $opt_l, $opt_a, $opt_c, $opt_u, $opt_p, $opt_H, $opt_x, $opt_d, $opt_f, $PROGNAME);
my $arch;
my $rhchannel;
my $rhusername;
my $rhpassword;
my $HOST;
my $client;
my $session;

$PROGNAME = "updateinfo.pl";

sub usage ($);
sub list_channels();
sub do_login();

Getopt::Long::Configure('bundling');
GetOptions
        ("h|help"          => \$opt_h,
         "l|list"          => \$opt_l,
         "a|arch=s"        => \$opt_a,
         "c|channel=s"     => \$opt_c,
         "u|rhnusername=s" => \$opt_u,
         "p|rhnpassword=s" => \$opt_p,
         "H|rhnhostname=s" => \$opt_H,
         "x|filename=s"    => \$opt_x,
         "d|database=s"    => \$opt_d,
         "f|fullupdate"    => \$opt_f);

pod2usage(2) if ($opt_h);

if ($opt_l) {
 ($opt_u) || usage("RedHat Network username not specified\n");
 $rhusername = $opt_u;
 ($opt_p) || usage("RedHat Network password not specified\n");
 $rhpassword = $opt_p;
 ($opt_H) || usage("RedHat Network hostname not specified\n");
 $HOST = $opt_H;
 list_channels();
 exit;
}

($opt_c) || usage("arch not specified\n");
$arch = $opt_a;

($opt_c) || usage("RedHat channel not specified\n");
$rhchannel = $opt_c;

($opt_u) || usage("RedHat Network username not specified\n");
$rhusername = $opt_u;

($opt_p) || usage("RedHat Network password not specified\n");
$rhpassword = $opt_p;

($opt_H) || usage("RedHat Network hostname not specified\n");
$HOST = $opt_H;

($opt_x) || usage("updateinfo.xml filename not specified\n");
my $updateinfofile = $opt_x;

($opt_d) || usage("sqlite file not specified\n");
my $updateinfodbfile = $opt_d;

# login and get session
do_login();

# start time measurement
my $t0 = [Time::HiRes::gettimeofday()];

my $dbh = DBI->connect( "dbi:SQLite:dbname=$updateinfodbfile", "", "" );
$dbh->{PrintError} = 0;
$dbh->{RaiseError} = 1;

$dbh->do( "CREATE TABLE IF NOT EXISTS channelerrata ( advisory VARCHAR ( 255 ) PRIMARY KEY, synopsis VARCHAR ( 255 ), issue_date VARCHAR ( 255 ), update_date VARCHAR ( 255 ));" );
$dbh->do( "CREATE TABLE IF NOT EXISTS errata ( advisory VARCHAR ( 255 ) PRIMARY KEY, type VARCHAR ( 255 ) , description VARCHAR ( 255 ) );" );
$dbh->do( "CREATE TABLE IF NOT EXISTS misc ( id INTEGER PRIMARY KEY, lastrundate DATE, channelname VARCHAR ( 255 ) );");
$dbh->do( "CREATE TABLE IF NOT EXISTS erratabugzilla(id INTEGER PRIMARY KEY, advisory VARCHAR ( 255 ), bugzilla_id VARCHAR ( 255 ), summary VARCHAR ( 255 ));" );
$dbh->do( "CREATE TABLE IF NOT EXISTS erratacves( id INTEGER PRIMARY KEY, advisory VARCHAR ( 255 ), cve_id VARCHAR ( 255 ) );" );
$dbh->do( "CREATE TABLE IF NOT EXISTS erratapackages( id INTEGER PRIMARY KEY, advisory VARCHAR ( 255 ), name VARCHAR ( 255 ), version VARCHAR ( 255 ), release VARCHAR ( 255 ), epoch VARCHAR ( 255 ), arch_label VARCHAR ( 255 ), file VARCHAR ( 255 ), md5sum VARCHAR ( 255 ));" );

my $lastrundate;
my $lastrundatevalue;

unless ($opt_f) {
  $lastrundate = $dbh->prepare( "SELECT lastrundate FROM misc  WHERE id=1;");
  undef $lastrundatevalue;
  eval {
    $lastrundatevalue = $dbh->selectrow_array( $lastrundate,{}, );
  };
  print "Getting erratas from after $lastrundatevalue\n" if $lastrundatevalue;
}
else
{
  print "Performing a full update.\n";
}

# get list of Erratas for a given channel
my $channelerrata = $client->call('channel.software.listErrata', $session, $rhchannel, $lastrundatevalue);
print "Processing erratas:\n";
for my $errata (@$channelerrata) {
  eval {
        $dbh->do( "INSERT INTO channelerrata(advisory, synopsis, issue_date, update_date) VALUES( '$errata->{errata_advisory}','$errata->{errata_synopsis}', '$errata->{errata_issue_date}', '$errata->{errata_update_date}');" );
    };
  if ($@) {
      print "Updating errata $errata->{errata_advisory} in local database\n";
      $dbh->do( "UPDATE channelerrata SET advisory='$errata->{errata_advisory}', synopsis='$errata->{errata_synopsis}', issue_date='$errata->{errata_issue_date}', update_date='$errata->{errata_update_date}' WHERE advisory='$errata->{errata_advisory}';" );
  }
  else {
      print "Inserting errata $errata->{errata_advisory} in local database\n";
  }
  # get extra details about an errata
  my $package;
  my $sql;
  eval {
  	 $package = $client->call('errata.getDetails', $session, $errata->{errata_advisory}) || die;
  };
  if ($@) {
      print "WARNING: cannot get $errata->{errata_advisory} details\n";
  } else {
  $sql = "INSERT INTO errata(advisory,type, description) VALUES('$errata->{errata_advisory}', '$package->{errata_type}', '" . HTML::Entities::encode_entities_numeric($package->{errata_description}) . "');";
  eval {
         $dbh->do( $sql);
  };
  if ($@) {
      print "Updating errata $errata->{errata_advisory} details in local database\n";
      $dbh->do( "UPDATE errata SET advisory='$errata->{errata_advisory}',type='$package->{errata_type}', description='" . HTML::Entities::encode_entities_numeric($package->{errata_description}) . "' WHERE advisory='$errata->{errata_advisory}';");
  }
  else {
      print "Inserting errata $errata->{errata_advisory} details in local database\n";
  }
  }
  # get a list (if any) of bugzilla references for a given errata
  my $erratabugzillaFixes;
  my $erratabugzillaFixeserr=0;
  eval {
	   $erratabugzillaFixes = $client->call('errata.bugzillaFixes', $session, $errata->{errata_advisory});
  };
  if ($@) {
      print "WARNING: cannot get $errata->{errata_advisory} bugzilla\n";
      $erratabugzillaFixeserr=1;
  }
  # get a list (if any) of CVE references for a given errata
  my $erratacves;
  my $erratacveserr=0;
  eval {
  	 $erratacves = $client->call('errata.listCves', $session, $errata->{errata_advisory});
         $erratacveserr=1;
  };
  if ($@) {
      print "WARNING: cannot get $errata->{errata_advisory} CVEs\n";
  }
  if (! $erratabugzillaFixeserr) {
    while ((my $bugzilla_id, my $bug_summary) = each(%$erratabugzillaFixes)){
      my $myid;
      eval {
        $myid = $dbh->selectall_arrayref( "SELECT id FROM erratabugzilla WHERE advisory='$errata->{errata_advisory}' AND bugzilla_id='$bugzilla_id';");
      };
      if (@$myid == 0) {
        print "Inserting bugzilla $bugzilla_id in local database\n";
        $sql = "INSERT INTO erratabugzilla(advisory, bugzilla_id, summary) VALUES ('$errata->{errata_advisory}', '$bugzilla_id', '". HTML::Entities::encode_entities_numeric($bug_summary) . "');";
        $dbh->do( $sql );
      }
      else {
        print "Updating bugzilla $bugzilla_id in local database\n";
        $dbh->do( "UPDATE erratabugzilla SET advisory='$errata->{errata_advisory}', bugzilla_id='$bugzilla_id', summary='" . HTML::Entities::encode_entities_numeric($bug_summary) . "' WHERE advisory='$errata->{errata_advisory}' AND bugzilla_id='$bugzilla_id';");
      }
    }
  } elsif (! $erratacveserr) {
    for my $erratacve (@$erratacves) {
      my $mycveid;
      eval {
        $mycveid = $dbh->selectall_arrayref( "SELECT id FROM erratacves  WHERE advisory='$errata->{errata_advisory}' AND cve_id='$erratacve';");
      };
      if (@$mycveid == 0) {
        print "Inserting $erratacve in local database\n";
        $sql = "INSERT INTO erratacves(advisory,cve_id) VALUES ('$errata->{errata_advisory}', '$erratacve');";
        $dbh->do( $sql );
      }
      else {
        print "Updating cves $erratacve in local database\n";
        $dbh->do( "UPDATE erratacves SET advisory='$errata->{errata_advisory}',cve_id='$erratacve' WHERE advisory='$errata->{errata_advisory}' AND cve_id='$erratacve';" );
      }
    }
  }
  # get the list of program packages for the errata
  my $erratapackages;
  eval {
	   $erratapackages = $client->call('errata.listPackages', $session, $errata->{errata_advisory});
  };
  if ($@) {
      print "WARNING: cannot get $errata->{errata_advisory} packages\n";
  } else {
  $sql = "DELETE FROM erratapackages WHERE advisory='$errata->{errata_advisory}';";
      eval {
          $dbh->do( $sql );
      };
  for my $erratapackage (@$erratapackages) {
    # only select channel architecture and noarch packages
    if($erratapackage->{package_arch_label} =~ m/$arch|noarch/i) {
      $sql = "INSERT INTO erratapackages(advisory, name, version, release, epoch, arch_label, file, md5sum) VALUES ('$errata->{errata_advisory}', '$erratapackage->{package_name}', '$erratapackage->{package_version}', '$erratapackage->{package_release}', '$erratapackage->{package_epoch}', '$erratapackage->{package_arch_label}', '$erratapackage->{package_file}', '$erratapackage->{package_md5sum}');";
      eval {
          $dbh->do( $sql );
      };
      print "Inserting erratapackages $erratapackage->{package_name} in local database\n";
    }
  }
  }
}

my $channelname;
my $channellist = $client->call('channel.listSoftwareChannels', $session);
for my $channel (@$channellist) {
  if($channel->{channel_label} =~ m/^$rhchannel$/i) {
    $channelname = $channel->{channel_name};
  }
}

eval {
    $dbh->do( "INSERT INTO misc VALUES (1, date('now'), '$channelname'); ");
};
if ($@) {
    $dbh->do( "UPDATE misc SET ID=1, lastrundate=date('now'), channelname='$channelname' WHERE ID=1; ");
}

print "Generating xml file.\n";

my $xmloutput = new IO::File(">$updateinfofile");
my $writer = new XML::Writer(OUTPUT => $xmloutput);
$writer->xmlDecl("UTF-8");
$writer->startTag("updates");
 my $erratarows = $dbh->selectall_arrayref("SELECT advisory,type,description FROM errata ORDER BY advisory ASC;");
 foreach my $erratarow (@$erratarows) {
   my ($advisory,$type,$description) = @$erratarow;
   my $xml_update_type;
   if($type =~ m/bug fix/i)     { $xml_update_type = "bugfix";      }
   if($type =~ m/security/i)    { $xml_update_type = "security";    }
   if($type =~ m/enhancement/i) { $xml_update_type = "enhancement"; }
   $writer->startTag( "update", from => 'security@redhat.com', status => 'final', type => $xml_update_type, version => '1');
   $writer->dataElement( 'id', $advisory);
   my $channelerratarows = $dbh->selectall_arrayref( "SELECT synopsis, issue_date, update_date FROM channelerrata WHERE advisory='$advisory';");
   foreach my $channelerratarow (@$channelerratarows) {
     my ($synopsis, $issue_date, $update_date) = @$channelerratarow;
     $writer->dataElement( 'title', $synopsis);
     $writer->emptyTag( 'issued', 'date' => $issue_date);
     $writer->emptyTag( 'updated', 'date' => $update_date);
   }
   my $erratarows = $dbh->selectall_arrayref( "SELECT type, description FROM errata where advisory='$advisory';" );
   foreach my $erratarow (@$erratarows) {
     my ($type, $description) = @$erratarow;
     $writer->dataElement( 'description', $description);
   }
   my $erratabugzillaFixesrows = $dbh->selectall_arrayref( "SELECT bugzilla_id, summary FROM erratabugzilla WHERE advisory='$advisory';" );
   my $erratabugzillaCount = scalar(@$erratabugzillaFixesrows);
   my $erratacvesrows = $dbh->selectall_arrayref( "SELECT cve_id FROM erratacves WHERE advisory='$advisory';" );
   my $erratacvesCount = scalar(@$erratacvesrows);
   if ($erratabugzillaCount > 0 || $erratacvesCount > 0) {
     $writer->startTag( "references" );
     foreach my $erratabugzillaFixesrow (@$erratabugzillaFixesrows) {
       my ($bugzilla_id, $summary) = @$erratabugzillaFixesrow;
       $writer->dataElement( 'reference', $summary, 'href' => "http://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=$bugzilla_id", 'id' => $bugzilla_id, 'type' => 'bugzilla');  
     }
     foreach my $erratacvesrow (@$erratacvesrows) {
       my ($cve_id) = @$erratacvesrow;
       $writer->emptyTag( 'reference', 'href' => "http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=$cve_id", 'id' => $cve_id, 'type' => 'cve');
     }
     $writer->endTag("references");
   }
   else {
     $writer->emptyTag( 'references');
   }
   $writer->startTag( "pkglist");
   $writer->startTag( "collection", short => $rhchannel);

   my $xmlchannelname = $dbh->prepare( "SELECT channelname FROM misc  WHERE id=1;");
   my $xmlchannelnamevalue = $dbh->selectrow_array( $xmlchannelname,{}, );
   $writer->dataElement( 'name', $xmlchannelnamevalue);

   my $erratapackagesrows = $dbh->selectall_arrayref( "SELECT name, version, release, epoch, arch_label, file, md5sum FROM erratapackages WHERE advisory='$advisory';" );
   foreach my $erratapackagesrow (@$erratapackagesrows) {
     my ($name, $version, $release, $epoch, $arch_label, $file, $md5sum) = @$erratapackagesrow;
     $writer->startTag( "package", name => $name, version => $version, release => $release, epoch => $epoch, arch => $arch_label, src => "");
     $writer->dataElement( 'filename', $file);
     $writer->dataElement( 'sum', $md5sum, type => "md5");
     $writer->endTag("package");
   }
   $writer->endTag("collection");
   $writer->endTag("pkglist");
   $writer->endTag;
 }

$writer->endTag;
$writer->end();
$xmloutput->close();

$dbh->disconnect();

print "Finished:\n";
printf ("run time was: %d seconds.\n", Time::HiRes::tv_interval($t0));
exit;

# usage help text
sub usage ($) {
  print @_;
  print "Usage: $PROGNAME -a <arch> -c <rhn channel> -u <rhn username> -p <rhn password> -H <rhn hostname> -x <filename> -d <sqlitedbfilename>\n";
  print "Example: $PROGNAME -a i386 -c rhel-i386-server-5 -u myuser\@domain.com -p secret -H rhn.redhat.com -x updateinfo.xml -d updateinfo.db\n";
  exit
}

# list channels
sub list_channels() {
  # login and get a session key
  do_login();
  # get the list of available software channels for the user
  my $channellist = $client->call('channel.listSoftwareChannels', $session);
  for my $channel (@$channellist) {
    printf ("%-40s, %s\n",$channel->{channel_label}, $channel->{channel_name});
  }
}

# login routine
sub do_login() {
  # create a new XML RPC client
  $client = new Frontier::Client(url => "https://$HOST/rpc/api");
  # login and get a valid session key
  $session = $client->call('auth.login', $rhusername, $rhpassword);
}

__END__

=head1 NAME

updateinfo.pl - Generating the updateinfo.xml

=head1 SYNOPSIS

updateinfo.pl [options]

 Options:
   -h or --help           brief help message
   --man                  full documentation
   -l or --list           List available channels for you
   -a or --arch           Platform architecture
   -c or --channel        RHN Channel
   -u or --rhnusername    Your RHN username
   -p or --rhnpassword    Your RHN password
   -H or --rhnhost        RedHat Network host address (or a RHN Satelite host)
   -x or --filename       output xml filename
   -d or --database       output sqlite database filename
   -f or --fullupdate     Perform a full update

=head1 OPTIONS

=over 8

=item B<--help>

Print a brief help message and exits.

=item B<--man>

Prints the manual page and exits.

=item B<--list>

List available channels for you.
Usefull for new users of mrepo and you do not know the exact channel name.

=item B<--arch>

Platform architecture.
Ie. i386, x86_64, ppc, ia64, s390, s390x, alpha, sparc, iSeries, pSeries.

=item B<--channel>

RedHat Network software channel
Ie. rhel-i386-server-5
Use --list to get a list of available channel names.

=item B<--rhnusername>

Your RedHat Network username.
You must have a valid RedHat subscription.

=item B<--rhnpassword>

Your valid Redhat Network password.
You must have a valid RedHat subscription.

=item B<--rhnhost>

Target RedHat Network hostname.
A RedHat Satelite server can also be used.

=item B<--filename>

Output xml file.
Ie. updateinfo.xml.

=item B<--database>

Output sqlite file
Ie. updateinfo.db

=item B<--fullupdate>

Perform a full update.
This will download all erratas from RedHat.
If omitted the script will only download erratas
released after the last time the script has been run.

=back

=head1 DESCRIPTION

B<This program> will generate a updateinfo.xml file
for mrepo for use of the yum-security yum plugin.

=head1 AUTHOR

Bjarne Saltbaek <arnebjarne72@hotmail.com>

=cut