Universität Ulm,
Fakultät für Mathematik und Wirtschaftswissenschaften,
SAI
Lösung zu Blatt 6 --- UNIX Datenbanken II (WS 1999/2000)
7. Persönliche Abfragen der Filmdatenbank
#!/usr/local/bin/perl -w
use DBI;
use Getopt::Std;
use Mysql::Admin qw(dbi_connect);
use strict;
my $dbdir = "/home/thales/sai/lehre/ws99/uxdb2/6/mysql/data";
my $cmdname = $0; $cmdname =~ s{.*/}{};
my $usage = "Usage: $cmdname -c Country [-a Actor] [-d Director] [-g Genre]";
$usage .= " [-t Title]\n";
my %opts = (); getopts('a:c:d:g:t:', \%opts);
die "$usage" unless defined $opts{'c'};
my $db = DBI->connect(dbi_connect($dbdir));
die "$cmdname: unable to connect to db: $DBI::errstr\n"
unless defined $db;
$db->{RaiseError} = 1;
my $dbq = "select distinct Movies.title, Movies.director, ";
$dbq .= "Movies.year, Length.length from Movies, Length";
my $where = "where Movies.movieid = Length.movieid and Length.Country = '";
$where .= $opts{'c'}."'";
if (defined $opts{'g'}) {
$where .= " and Movies.movieid = MtoG.movieid and Genre.genre = '";
$where .= $opts{'g'}."' and Genre.genreid = MtoG.genreid";
$dbq .= ", Genre, MtoG";
}
if (defined $opts{'a'}) {
$where .= " and Movies.movieid = MtoA.movieid and Actors.actor = '";
$where .= $opts{'a'}."' and Actors.actorid = MtoA.actorid";
$dbq .= ", Actors, MtoA";
}
$where .= " and Movies.title = '".$opts{'t'}."'"
if defined $opts{'t'};
$where .= " and Movies.director = '".$opts{'d'}."'"
if defined $opts{'d'};
$where .= " order by Movies.title;";
my $st = $db->prepare("$dbq $where");
$st->execute();
my $record;
while (defined($record = $st->fetch())) {
print join("\t", @{$record}), "\n";
}
$st->finish;
$db->disconnect;
Ingo Melzer, 09. Dezember 1999