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;

Universität Fakultät SAI

Ingo Melzer, 09. Dezember 1999