X-Git-Url: http://git.grml.org/?p=grml-live.git;a=blobdiff_plain;f=db%2Fdpkg-to-db;fp=db%2Fdpkg-to-db;h=6510d1218a12f6739a5ed409c5b24ad868752b11;hp=0000000000000000000000000000000000000000;hb=d761bd6cdc6b54b67883e295a0dd1694e492bd7b;hpb=e56cc393d3419a72c74aa5084525b88c6a7cf854

diff --git a/db/dpkg-to-db b/db/dpkg-to-db
new file mode 100755
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