+#!/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
projects / grml-live.git / commitdiff