Let’s analyze the following perl scripta “cinema.pl”, to create and populate a simple database about movies, and “query_hollywood.pl” to execute a simple SELECT-FROM-WHERE query on it.
—————————————cinema.pl————————————————————
#!/usr/bin/perl -w
use DBI;
use strict;
# CONFIG VARIABLES
my $platform = “SQLite”;
my $database = “hollywood.db”;
my $host = “localhost”;
my $port = “3306”;
my $user = “username”;
my $pw = “password”;
# DATA SOURCE NAME
my $dsn = “dbi:$platform:$database:$host:$port”;
# PERL DBI CONNECT
my $dbh = DBI->connect($dsn, $user, $pw) or die “Cannot connect: $DBI::errstr”;
# creating the “hollywood” database
$dbh->do(“CREATE TABLE IF NOT EXISTS actors(aid integer primary key, name text)”);
$dbh->do(“CREATE TABLE IF NOT EXISTS movies(mid integer primary key, title text)”);
$dbh->do(“CREATE TABLE IF NOT EXISTS actors_movies(id integer primary key, mid integer, aid integer)”);
#populating “actors” table
$dbh->do(“INSERT INTO actors(name) VALUES(‘Philip Seymour Hofman’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Kate Shindle’)”);
$dbh->do(“INSERT INTO actors(name) VALUES (‘Kelci Stephenson’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Al Pacino’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Gabrielle Anwar’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Patricia Arquette’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Gabriel Byrne’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Max von Sydow’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Ellen Burstyn’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Jason Miller’)”);
#populating “movies” table
$dbh->do(“INSERT INTO movies VALUES(1,’Capote’)”);
$dbh->do(“INSERT INTO movies VALUES(2,’Scent of a woman’)”);
$dbh->do(“INSERT INTO movies VALUES(3,’Stigmata’)”);
$dbh->do(“INSERT INTO movies VALUES(4,’Exorcist’)”);
$dbh->do(“INSERT INTO movies VALUES(5,’Hamsun’)”);
#populating “actorsMovies” table
$dbh->do(“INSERT INTO actors_movies VALUES(1,1,1)”);
$dbh->do(“INSERT INTO actors_movies VALUES(2,2,1)”);
$dbh->do(“INSERT INTO actors_movies VALUES(3,3,1)”);
$dbh->do(“INSERT INTO actors_movies VALUES(4,4,2)”);
$dbh->do(“INSERT INTO actors_movies VALUES(5,5,2)”);
$dbh->do(“INSERT INTO actors_movies VALUES(6,6,3)”);
$dbh->do(“INSERT INTO actors_movies VALUES(7,7,3)”);
$dbh->do(“INSERT INTO actors_movies VALUES(8,8,4)”);
$dbh->do(“INSERT INTO actors_movies VALUES(9,9,4)”);
$dbh->do(“INSERT INTO actors_movies VALUES(10,10,4)”);
$dbh->do(“INSERT INTO actors_movies VALUES(11,8,5)”);
print qq{“Hollywood” database created! \n };
$dbh->disconnect;
—————————————————query_hollywood.pl———————————————
#!/usr/bin/perl -w
use DBI;
use strict;
# CONFIG VARIABLES
my $platform = “SQLite”;
my $database = “hollywood.db”;
my $host = “localhost”;
my $port = “3306”;
my $user = “username”;
my $pw = “password”;
# DATA SOURCE NAME
my $dsn = “dbi:$platform:$database:$host:$port”;
# PERL DBI CONNECT
my $dbh = DBI->connect($dsn, $user, $pw) or die “Cannot connect: $DBI::errstr”;
# EXECUTE THE QUERY
my $query = “SELECT actors.name , movies.title FROM actors,movies,actors_movies WHERE actors.aid=actors_movies.aid and
movies.mid=actors_movies.mid”;
my $sth=$dbh->selectall_arrayref($query);
print “Actor Movie \n” ;
print “====================== ====================\n”;
foreach my $row (@$sth) {
my ($name, $title) = @$row;
### Print out the table metadata…
printf “%-23s %-23s \n”, $name, $title;
}
$dbh->disconnect;
—————————————————————————————–
Make the perl scripts executable like:
$ sudo chmod +x script.pl
And run them liket:
$ ./script.pl
The result of the query is:
Actor Movie
==================== ====================
Philip Seymour Hofman Capote
Philip Seymour Hofman Scent of a woman
Philip Seymour Hofman Stigmata
Kate Shindle Exorcist
Kate Shindle Hamsun
Very nice script, isn’t it?
I guess the DBI deserves further attention…
