CSCI 5733
XML Application Development
Spring 2006
Suggested Solution to Homework #1

(a) For example (not fully documented):

#   h1a.pl
#
use strict;
open DATA, "<$ARGV[0]" || die "Can't open input file $ARGV[0].\n";


print <<_HEADER;
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>
_HEADER

#   Discard first line.
$_ = <DATA>;
while ($_ = <DATA>) {
   chomp;
   my @field = split /\t/;
   my $description = "http://$field[13]";
   $field[5] =~ s/^(\d{5}).*$/\1/;
   my $address = "$field[2], $field[3], $field[4] $field[5]";
   my $name = $field[1];

   $description = xmlencode($description);
   $address = xmlencode($address);
   $name = xmlencode($name);

   print <<_FIELD;
<Placemark>
   <description>$description</description>
   <address>$address</address>
   <name>$name</name>
</Placemark>
_FIELD
}

print <<_FOOTER;
</Folder>
</Document>
</kml>
_FOOTER
exit 0;

sub xmlencode {
   my $s = shift;
   $s =~ s/&/&amp;/g;
   $s =~ s/</&lt;/g;
   $s =~ s/>/&gt;/g;
   $s =~ s/'/&apos;/g;
   $s =~ s/"/&quot;/g;
   $s;
}


Dr. Kwok-Bun Yue
Professor, Computer Science and Computer Information Systems
Chair, Division of Computing and Mathematics
University of Houston-Clear Lake
2700 Bay Area Boulevard
Houston, TX 77058
Yue's Home  Yue's home page     Yue's email  yue@uhcl.edu     phone  281-283-3864

(b) For example,

#
#   h1b.pl  Extracting data from an XML file and store it in a MySQL database.
#       See specification of HW #1 Question (b) of CSCI 5733, Spring 2006
#
#   By Kwok-Bun Yue, 1/14/2006
#

use strict;
use DBI;

open DATA, "<$ARGV[0]" || die "Can't open $ARGV[0]";
my @lines = <DATA>;
chomp @lines;
my $line = join "", @lines;

#   Create tables: assume not already exist.
my $dbh = DBI->connect( 'dbi:mysql:whatever',
                        'user',
                        'pass'
                      ) || die "Database connection not made: $DBI::errstr";


my $sth = $dbh->prepare("CREATE TABLE articles (
    aid INT,
   volume INT,
    number INT,
   title VARCHAR(200),
    initPage INT,
    endPage INT,
   KEY (aid));");
$sth->execute();

$sth = $dbh->prepare("CREATE TABLE authors (
    author VARCHAR(50),
   aid INT,
    position CHAR(2),
   KEY (author, aid),
   CONSTRAINT FOREIGN KEY (aid) REFERENCES articles(aid));");

$sth->execute();

my $artNum = 0;
while ($line =~/<issue>(.*?)<\/issue>/gsi) {
   my $issue = $1;
   my ($volume, $number) = $issue =~ /<volume>(.*?)<\/volume>.*?<number>(.*)<\/number>/;
   while ($issue =~ /<article>(.*?)<\/article>/gsi) {
      $artNum++;
      my $article = $1;
      my ($title) = $article =~ /<title>(.*?)<\/title>/;
      my ($initPage) = $article =~ /<initPage>(.*?)<\/initPage>/;
      my ($endPage) = $article =~ /<endPage>(.*?)<\/endPage>/;

      $article = xmlDecode($article);

      $dbh->do("INSERT INTO articles(aid, volume, number, title, initPage, endPage) VALUES(?,?,?,?,?,?)",
      {}, $artNum, $volume, $number, $title, $initPage, $endPage);

      while ($article =~ /<author\s+position="(.*?)">(.*?)<\/author>/gsi) {
         my ($position, $author) = ($1, $2);
         $author = xmlDecode($author);
         $position = xmlDecode($position);
         $dbh->do("INSERT INTO authors(author, aid, position) VALUES(?,?,?)",
         {}, $author, $artNum, $position) || print "error: ", $DBI::errstr , "\n";
      }
   }
}

$dbh->disconnect();

exit 0;

sub xmlDecode {
   my $s = shift;
   $s =~ s/&lt;/</g;
   $s =~ s/&gt;/>/g;
   $s =~ s/&quot;/"/g;
   $s =~ s/&apos;/'/g;
   $s =~ s/&amp;/&/g;
   $s;
}

(c) For example:

use CGI;
use DBI;
use strict;
$|++;

#
#   h1c.pl
#
#   By Kwok-Bun Yue   January 13, 2006
#
#   A simple CGI-Perl program to serve as a simple XML server.
#   See http://localhost/yue/courses/xml/spring2006/hw/h1.asp
#
#   Source Input:
#      MySQL Database
#
#   HTTP parameters:
#      authors=yes: returns a list of all authors in XML.
#      author=<author>: returns the articles published by the author in XML.
#      issues: returns a list of all issues (volume and number) in XML.
#      volume=<volume>&number=<number>,: returns all article titles in the given issue in XML.

#   Porting variables:
#   My SQL credential.
my $user = '';  
my $pass = '';

#   Variables.
my $dbh;      #   database handle

my $q = new CGI;

#   HTTP parameters
my $authors = $q->param("authors");
my $author = $q->param("author");
my $issues = $q->param("issues");
my $volume = $q->param("volume");
my $number = $q->param("number");

if ($authors) {
   &returnAuthors();
   exit 0;
}

if ($author) {
   &returnAuthor($author);
   exit 0;
}

if ($issues) {
   &returnIssues();
   exit 0;
}

if ($volume || $number) {
   &returnIssue($volume, $number);
   exit 0;
}

print <<_EMPTY_XML;
Content-Type: text/xml; charset=ISO-8859-1

<?xml version='1.0' encoding='ISO-8859-1' ?>
<noResult />
_EMPTY_XML

exit 0;   #   main

sub returnAuthors {
  
print <<_HEAD;
Content-Type: text/xml; charset=ISO-8859-1

<?xml version='1.0' encoding='ISO-8859-1' ?>
<authors>
_HEAD

my $dbh = DBI->connect("DBI:mysql:database=database;host=localhost;port=3306", $user, $pass)
   ||  die "Can't connect to the database";

my @row;
my $sth=$dbh->prepare("SELECT DISTINCT author FROM authors");
$sth->execute();
while(@row=$sth->fetchrow_array()){
   my $author = xmlEncode(${row[0]});
    print "    <author>$author</author>\n";
}
print "</authors>";
$sth->finish();
}   #   returnAuthors

sub returnAuthor {
   my $author = shift;

print <<_HEAD;
Content-Type: text/xml; charset=ISO-8859-1

<?xml version='1.0' encoding='ISO-8859-1' ?>
<articles author="$author">
_HEAD

my $dbh = DBI->connect("DBI:mysql:database=database;host=localhost;port=3306", $user, $pass)
   ||  die "Can't connect to the database";

my @row;
my $authorStr = $author;
$authorStr =~ s/'/''/g;
my $sth=$dbh->prepare("SELECT DISTINCT title, volume, number FROM articles, authors where articles.aid = authors.aid and authors.author = '$authorStr'");
$sth->execute();
while(@row=$sth->fetchrow_array()){
   my $author = xmlEncode(${row[0]});
    print "    <article volume=\"${row[1]}\" number=\"${row[2]}\">$author</article>\n";
}
print "</articles>";
$sth->finish();

}   #   returnAuthor

sub returnIssues {

print <<_HEAD;
Content-Type: text/xml; charset=ISO-8859-1

<?xml version='1.0' encoding='ISO-8859-1' ?>
<issues>
_HEAD

my $dbh = DBI->connect("DBI:mysql:database=database;host=localhost;port=3306", $user, $pass)
   ||  die "Can't connect to the database";

my @row;
my $sth=$dbh->prepare("SELECT DISTINCT volume, number FROM articles");
$sth->execute();
while(@row=$sth->fetchrow_array()){
    print "    <issue volume=\"${row[0]}\" number=\"${row[1]}\" />\n";
}
print "</issues>";
$sth->finish();

}   #   returnIssues


sub returnIssue {
   my ($volume, $number) = @_;

print <<_HEAD;
Content-Type: text/xml; charset=ISO-8859-1

<?xml version='1.0' encoding='ISO-8859-1' ?>
<issue volume="$volume" number="$number">
_HEAD

my $dbh = DBI->connect("DBI:mysql:database=database;host=localhost;port=3306", $user, $pass)
   ||  die "Can't connect to the database";

my @row;
$volume = xmlencode($volume);
$number = xmlencode($number);
my $sth=$dbh->prepare("SELECT DISTINCT title FROM articles where volume = '$volume' and number = '$number'");
$sth->execute();
while(@row=$sth->fetchrow_array()){
   my $title = xmlEncode($row[0]);
    print "    <article>$title</article>\n";
}
print "</issue>";
$sth->finish();


}   #   returnAuthor

sub xmlEncode {
   my $s = shift;
   $s =~ s/&/&amp;/g;
   $s =~ s/</&lt;/g;
   $s =~ s/>/&gt;/g;
   $s =~ s/'/&apos;/g;
   $s =~ s/"/&quot;/g;
   $s;
}

sub xmlDecode {
   my $s = shift;
   $s =~ s/&lt;/</g;
   $s =~ s/&gt;/>/g;
   $s =~ s/&quot;/"/g;
   $s =~ s/&apos;/'/g;
   $s =~ s/&amp;/&/g;
   $s;
}