3 # Purpose: add grml build information into a sqlite database
4 # Authors: grml-team (grml.org)
5 # Bug-Reports: see http://grml.org/bugs/
6 # License: This file is licensed under the GPL v2 or any later version.
7 ################################################################################
8 # Requires the following Debian packages (handled via grml-live-db depends):
9 # libdbd-sqlite3-perl libdbi-perl libtimedate-perl perl-doc sqlite3
10 ################################################################################
21 my ($db, $logfile, $flavour, $help);
24 'logfile|l=s' => \$logfile,
25 'flavour|f=s' => \$flavour,
29 pod2usage(1) if $help;
31 pod2usage(-message => "$0: Need a sqlite database.\n") unless $db;
32 pod2usage(-message => "$0: Need a logfile to insert.\n") unless $logfile;
33 pod2usage(-message => "$0: Need the flavour information\n") unless $flavour;
35 my $dbh = DBI->connect("dbi:SQLite:dbname=$db","","") or die "Could not connect to database: " . $DBI::err;
37 # We use foreign key - beware this needs sqlite > 3.6.19
38 $dbh->do("PRAGMA foreign_keys = ON");
40 open (my $fh, '<', $logfile) or die "Could not open $logfile: $!";
42 # read content of log file - please do not try this at home :)
43 my $log = do { local $/; <$fh> };
45 my $identifier = "$flavour-". time2str('%Y%m%d%H%M%S', time());
47 # Prepare tables if not yet present {{{
48 my $create_table_build = $dbh->prepare("
49 CREATE TABLE if not exists build ( id integer primary key autoincrement,
50 identifier varchar(30),
55 or die "Could not create tables: " . $dbh->errstr."\n";
57 $create_table_build->execute()
58 or die "Can't execute SQL statement: " . $dbh->errstr."\n";
60 my $create_table_packages = $dbh->prepare("
61 CREATE TABLE if not exists packages ( id integer primary key autoincrement,
66 FOREIGN KEY(build) REFERENCES build(id));
68 or die "Could not create tables: " . $dbh->errstr."\n";
70 $create_table_packages->execute()
71 or die "Can't execute SQL statement: " . $dbh->errstr."\n";
75 # Write information to database {{{
76 my $sth = $dbh->prepare("INSERT into build ('identifier','flavour','date','logfile') VALUES (?,?,?,?)")
77 or die "Could not prepare db statement: " . $dbh->errstr;
80 $sth->execute($identifier, $flavour, time(), $log)
81 or die "Could not add build to db: " . $sth->errstr;
83 $sth = $dbh->prepare("SELECT id from build where identifier = ?");
84 $sth->execute($identifier) or die "Couldn't execute statement: " . $sth->errstr;
85 my $row = $sth->fetch;
88 die "No id?" unless $id;
90 $sth = $dbh->prepare("INSERT into packages (package, status, version, build) VALUES (?,?,?,?)")
91 or die "Could not prepare db statement: " . $dbh->errstr;
93 while (my $line = <>) {
94 next unless $line =~ /^[a-z]{2} /;
96 my ($status, $package, $version, $desc) = split (/\s+/, $line, 4);
97 $sth->execute($package, $status, $version, $id)
98 or die "Couldn't execute statement: " . $sth->errstr;
103 print "recorded buildinformation with identifier $identifier as id $id\n";
105 # perldoc -F ./dpkg-to-db
111 dpkg-to-db - add grml build information into a sqlite database
118 --help brief help message
119 --db <database> database file
120 --logfile <logfile> logfile which should be added
121 --flavour <flavour> which flavour the build is
129 Print a brief help message and exits.
135 B<dpkg-to-db> will read the given input file(s) which holds output of
136 `dpkg --list' and writes the information to the specified database.
138 =head1 USAGE EXAMPLES
140 Please see B<man 8 grml-live-db> for further information.