Support for logging to database via grml-live-db. Changed logic of ZERO_LOGFILE to...

[grml-live.git] / db / dpkg-to-db

diff --git a/db/dpkg-to-db b/db/dpkg-to-db
new file mode 100755 (executable)
index 0000000..6510d12
--- /dev/null
+++ b/db/dpkg-to-db
@@ -0,0 +1,142 @@
+#!/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