#!/usr/bin/perl -w
# Filename:      dpkg-to-db
# Purpose:       add grml build information into a sqlite database
# Authors:       grml-team (grml.org)
# Bug-Reports:   see http://grml.org/bugs/
# License:       This file is licensed under the GPL v2 or any later version.
################################################################################
# Requires the following Debian packages (handled via grml-live-db depends):
# libdbd-sqlite3-perl libdbi-perl libtimedate-perl perl-doc sqlite3
################################################################################

use strict;

use warnings;
use Getopt::Long;
use Pod::Usage;
use DBI;
use Date::Format;


my ($db, $logfile, $flavour, $help);
my $rc = GetOptions (
		'db|d=s' => \$db,
		'logfile|l=s' => \$logfile,
		'flavour|f=s' => \$flavour,
		'help|h' => \$help,
	);

pod2usage(1) if $help;

pod2usage(-message => "$0: Need a sqlite database.\n") unless $db;
pod2usage(-message => "$0: Need a logfile to insert.\n") unless $logfile;
pod2usage(-message => "$0: Need the flavour information\n") unless $flavour;

my $dbh = DBI->connect("dbi:SQLite:dbname=$db","","") or die "Could not connect to database: " . $DBI::err;

# We use foreign key - beware this needs sqlite > 3.6.19
$dbh->do("PRAGMA foreign_keys = ON");

open (my $fh, '<', $logfile) or die "Could not open $logfile: $!";

# read content of log file - please do not try this at home :)
my $log = do { local $/; <$fh> };

my $identifier = "$flavour-". time2str('%Y%m%d%H%M%S', time());

# Prepare tables if not yet present {{{
my $create_table_build = $dbh->prepare("
CREATE TABLE if not exists build ( id integer primary key autoincrement,
identifier varchar(30),
flavour varchar(30),
date varchar(30),
logfile blob);
")
  or die "Could not create tables: " . $dbh->errstr."\n";

$create_table_build->execute()
  or die "Can't execute SQL statement: " . $dbh->errstr."\n";

my $create_table_packages = $dbh->prepare("
CREATE TABLE if not exists packages (  id integer primary key autoincrement,
package varchar(30),
status varchar(2),
version varchar(30),
build integer,
FOREIGN KEY(build) REFERENCES build(id));
")
  or die "Could not create tables: " . $dbh->errstr."\n";

$create_table_packages->execute()
  or die "Can't execute SQL statement: " . $dbh->errstr."\n";
# }}}


# Write information to database {{{
my $sth = $dbh->prepare("INSERT into build ('identifier','flavour','date','logfile') VALUES (?,?,?,?)")
	or die "Could not prepare db statement: " . $dbh->errstr;

# Execute the query
$sth->execute($identifier, $flavour, time(), $log)
	or die "Could not add build to db: " . $sth->errstr;

$sth = $dbh->prepare("SELECT id from build where identifier = ?");
$sth->execute($identifier) or die "Couldn't execute statement: " . $sth->errstr;
my $row = $sth->fetch;
my $id = $row->[0];

die "No id?" unless $id;

$sth = $dbh->prepare("INSERT into packages (package, status, version, build) VALUES (?,?,?,?)")
	or die "Could not prepare db statement: " . $dbh->errstr;

while (my $line = <>) {
	next unless $line =~ /^[a-z]{2} /;
	# remove new lines
	my ($status, $package, $version, $desc) = split (/\s+/, $line, 4);
	$sth->execute($package, $status, $version, $id)
		or die "Couldn't execute statement: " . $sth->errstr;

}
# }}}

print "recorded buildinformation with identifier $identifier as id $id\n";

# perldoc -F ./dpkg-to-db

__END__

=head1 dpkg-to-db

dpkg-to-db - add grml build information into a sqlite database

=head1 SYNOPSIS

dpkg-to-db [options]

  Options:
   --help            	brief help message
   --db <database>	database file
   --logfile <logfile>  logfile which should be added
   --flavour <flavour>  which flavour the build is

=head1 OPTIONS

=over 8

=item B<-help>

Print a brief help message and exits.

=back

=head1 DESCRIPTION

B<dpkg-to-db> will read the given input file(s) which holds output of
`dpkg --list' and writes the information to the specified database.

=head1 USAGE EXAMPLES

Please see B<man 8 grml-live-db> for further information.

=cut