This is the library database search script that is destined for
authorMatthew Thiffault <mthiffau@csclub.uwaterloo.ca>
Wed, 11 Mar 2009 03:19:56 +0000 (23:19 -0400)
committerMatthew Thiffault <mthiffau@csclub.uwaterloo.ca>
Wed, 11 Mar 2009 03:19:56 +0000 (23:19 -0400)
csclub.uwaterloo.ca/library. Along with it goes a .htaccess file
to make sure that index.cgi works. There may be some updates to the
script in the near future.

library/.htaccess [new file with mode: 0755]
library/index.cgi [new file with mode: 0755]

diff --git a/library/.htaccess b/library/.htaccess
new file mode 100755 (executable)
index 0000000..d54535d
--- /dev/null
@@ -0,0 +1,3 @@
+AddHandler cgi-script .cgi
+Options +Indexes +ExecCGI
+DirectoryIndex index.cgi
diff --git a/library/index.cgi b/library/index.cgi
new file mode 100755 (executable)
index 0000000..1609553
--- /dev/null
@@ -0,0 +1,374 @@
+#!/usr/bin/perl
+
+###############################################
+# Postgres SQL Library Database Web Interface #
+# Matt Thiffault (January 22,2009)            #
+# matt.thiffault@gmail.com                    #
+###############################################
+
+
+use DBI;
+
+print "Content-type: text/html\n\n";
+
+$in = $ENV{'QUERY_STRING'};
+
+@pairs = split(/[&=]/,$in);
+%fields = @pairs;
+chomp(%fields);
+$webroot = "../../..";
+
+print <<END;
+
+<html xmlns="http://www.w3.org/1999/xhtml">
+<head><link rel="shortcut icon" href="/favicon.ico"/>
+<link rel="alternate" title="CSC Events Feed" href="$webroot/events.atom" type="application/rss+xml"/>
+<link rel="alternate" title="CSC News Feed" href="$webroot/news.atom" type="application/rss+xml"/>
+<title>Computer Science Club of the University of Waterloo: Home</title>
+<link rel="stylesheet" href="$webroot/default.css" type="text/css"/></head>
+<body>
+<div class="content">
+<a id="pagetop"/>
+<div class="biglogo">
+<div class="uwlogo">
+<a href="http://www.uwaterloo.ca/"><img src="$webroot/logos/uw_logo.png" alt="University of Waterloo" width="105" height="70"/></a>
+</div><div class="csclogo">
+<a href="./"><img src="$webroot/logos/csc_logo.png" alt="Computer Science Club" width="420" height="79"/></a>
+</div></div>
+<div class="dirheader">
+<a class="diritem" href="$webroot/"><b>HOME</b></a>
+<a class="diritem" href="$webroot/services/">Services</a>
+<a class="diritem" href="$webroot/office/">Office</a>
+<a class="diritem" href="$webroot/events/">Events</a>
+<a class="diritem" href="$webroot/media/">Media</a>
+<a class="diritem" href="http://wiki.csclub.uwaterloo.ca/">Wiki</a>
+<a class="diritem" href="https://mail.csclub.uwaterloo.ca/">Webmail</a>
+<a class="diritem" href="$webroot/gallery2/">Gallery</a>
+<a class="diritem" href="$webroot/stats">Stats</a>
+<br><br><br>
+
+END
+
+#display the search form
+if($fields{'do'} eq "home" || $fields{'do'} eq ""){
+
+#isbn,title,authors,year,edition,publisher 
+
+    print <<END;
+    <center>
+    <br>
+    <h3>Computer Science Library Collection Search</h3>
+    <br>
+    <br>
+
+    <table>
+
+    <form name="search" action="library.cgi" method="get">
+       <input type="hidden" name="do" value="query" />
+       
+       <tr><td>Title:</td> <td><input type="text" name="title" /></td></tr>
+       
+        <tr><td>Publisher:</td> <td><input type="text" name="publisher" /><td></tr>
+       
+        <tr><td>Edition:</td> <td><input type="text" name="edition" /></td></tr>
+
+        <tr><td>Year:</td> <td><input type="text" name="year" /></td></tr>
+       
+        <tr><td>ISBN:</td> <td><input type="text" name="isbn" /></td></tr>
+
+    <tr>
+    <td></td>
+    <td><input type="submit" value="Search" /><br>
+    </td>
+    </tr>
+    </form>
+    </table>
+    </center>
+
+END
+
+}
+
+#query the database
+if($fields{'do'} eq "query"){
+
+    print "<h3>Search Results</h3>";
+
+    #build query string here, query and display.
+    my $dbh = DBI->connect("dbi:Pg:dbname=library;host=localhost", 
+                          'librarian', 
+                          'KJI3RVgszrxrxe6W4gX3', 
+                          {AutoCommit => 1})
+       or print "Couldn't connect to database: " . DBI->errstr;
+
+    $searchterm = undef;
+    $orterm = undef;
+    @results;
+    @oresults;
+    $title = $fields{'title'};
+    $title =~ s/[';:"]/ /g;
+    $publisher = $fields{'publisher'};
+    $publisher =~ s/[';:"]/ /g;
+    $edition = $fields{'edition'};
+    $edition =~ s/[';:"]/ /g;
+    $year = $fields{'year'};
+    $year =~ s/[';:"]/ /g;
+    $isbn = $fields{'isbn'};
+    $isbn =~ s/[';:"]/ /g;
+
+    if($title){
+
+       @tmp = split(/\+/,$title);
+       my @keywords;
+       my @orwords;
+       push @keywords,"'%$tmp[0]%'";
+       push @orwords,"'%tmp[0]%'";
+       shift @tmp;
+       
+       foreach $keyword (@tmp){
+           push @keywords,"AND (title ILIKE '%$keyword%')";
+           push @orwords,"OR (title ILIKE '%$keyword%')";
+       }
+       
+       $searchterm = "(title ILIKE @keywords)";
+       $orterm = "(title ILIKE @orwords)";
+
+    }
+
+    if($publisher){
+       
+       @tmp = split(/\+/,$publisher);
+       my @keywords;
+       my @orwords;
+       push @keywords,"'%$tmp[0]%'";
+       shift @tmp;
+
+       foreach $keyword (@tmp){
+            push @keywords,"AND (publisher ILIKE '%$keyword%')";
+           push @orwords,"OR (publisher ILIKE '%$keyword%')";
+        }
+       
+        if($searchterm eq undef){
+            $searchterm = "(publisher ILIKE @keywords)";
+           $orterm = "(publisher ILIKE @orwords)";
+        }else{
+            $temp = $searchterm;
+            $searchterm = $temp." AND (publisher ILIKE @keywords)";
+           $temp = $orterm;
+           $orterm = $temp." OR (publisher ILIKE @keywords)";
+        }
+       
+    }
+
+    if($edition){
+       
+       @tmp = split(/\+/,$edition);
+       my @keywords;
+       my @orwords;
+       push @keywords,"'%$tmp[0]%'";
+       push @orwords,"'%$tmp[0]%'";
+       shift @tmp;
+
+       foreach $keyword (@tmp){
+            push @keywords,"AND (edition ILIKE '%$keyword%')";
+           push @keywords,"OR (edition ILIKE '%$keyword%')";
+        }
+
+        if($searchterm eq undef){
+            $searchterm = "(edition ILIKE @keywords)";
+           $orterm = "(edition ILIKE @keywords)"
+        }else{
+            $temp = $searchterm;
+            $searchterm = $temp." AND (edition ILIKE @keywords)";
+           $temp = $orterm;
+           $orterm = $temp." OR (edition ILIKE @keywords)";                                                                                                                                                                               
+        }
+
+    }
+
+    if($year){
+       
+       @tmp = split(/\+/,$year);
+       my @keywords;
+       my @orwords;
+       push @keywords,"'%$tmp[0]%'";
+       push @orwords,"'%$tmp[0]%'";
+       shift @tmp;
+
+       foreach $keyword (@tmp){
+            push @keywords,"AND (year ILIKE '%$keyword%')";
+           push @orwords,"OR (year ILIKE '%$keyword%')";
+        }
+
+        if($searchterm eq undef){
+            $searchterm = "(year ILIKE @keywords)";
+           $orterm = "(year ILIKE $orwords)";
+        }else{
+            $temp = $searchterm;
+            $searchterm = $temp." AND (year ILIKE @keywords)";
+           $temp = $orterm;
+           $orterm = $temp." OR (year ILIKE @orwords)";
+           
+        }
+
+    }
+
+    if($isbn){
+       
+       @tmp = split(/\+/,$isbn);
+       my @keywords;
+       my @orwords;
+       push @keywords,"'%$tmp[0]%'";
+       push @orwords,"'%$tmp[0]%'";
+       shift @tmp;
+
+       foreach $keyword (@tmp){
+            push @keywords,"AND (isbn ILIKE '%$keyword%')";
+           push @orwords,"OR (isbn ILIKE '%keyword%')";
+        }
+
+        if($searchterm eq undef){
+            $searchterm = "(isbn ILIKE @keywords)";
+           $orterm = "(isbn ILIKE @keywords)";
+        }else{
+            $temp = $searchterm;
+            $searchterm = $temp." AND (isbn ILIKE @keywords)";
+           $temp = $orterm;
+           $orterm = $temp." OR (year ILIKE @orwords)";
+        }
+
+    }
+
+    if($searchterm){
+
+       my $sth = $dbh->prepare("SELECT title,isbn,year,publisher,edition FROM book WHERE $searchterm;")
+           or print "Couldn't prepare statement: " . $dbh->errstr;
+
+       my $oth = $dbh->prepare("SELECT title,isbn,year,publisher,edition FROM book WHERE $orterm;")
+           or print "Couldn't prepare statement: " . $dbh->errstr;
+       
+       $sth->execute()             # Execute the query
+           or print "Couldn't execute statement: " . $sth->errstr;
+       
+       while($data = $sth->fetchrow_arrayref()){
+           push @results, [@{$data}];
+       }
+
+       $oth->execute()
+           or print "Couldn't execute statement: " . $oth->errstr;
+
+       while($ordata = $oth->fetchrow_arrayref()){
+           push @oresults, [@{$ordata}];
+       }
+
+       $sth->finish;
+       $oth->finish;
+
+       
+       if ($sth->rows == 0 && $oth->rows == 0) {
+           print "<center>No names matched</center></br>";
+       }else{
+           print "<center><table border>";
+           print "<tr> <td>Title</td> <td>ISBN</td> <td>Year</td> <td>Publisher</td> <td>Edition</td> </tr>";
+           
+           for my $book (@results){
+               print "<tr>";
+               for my $field (@{$book}){
+                   print "<td> $field </td>\n";
+               }
+               print "</tr>";
+           }
+
+           for my $book (@oresults){
+               foreach $andbook (@results){
+                   if(@{$book}[0] eq @{$andbook}[0]){
+                       $book = undef;
+                   }
+               }
+               
+               if($book){
+                   print "<tr>";
+                   for my $field (@{$book}){
+                       print "<td> $field </td>\n";
+                   }
+                   print "</tr>";
+               }
+           }
+
+           print "</table></center>";
+       }
+       
+    }else{
+       print "<br><center>You did not enter any search terms.</center></br>";
+    }
+}
+
+print <<END;
+
+<br><br>
+
+<div class="footer">
+<div class="menubar">
+END
+
+open(MENU,"/users/www/www/menu.xml") or print "Couldn't open file\n";
+@items = <MENU>;
+close(MENU);
+
+$items[0] = undef;
+$items[-1] = undef;
+
+foreach $item (@items){
+    if($item){
+       $item =~ s/[<>]//g;
+       $item =~ s/\/$//g;
+       $item =~ s/menuitem//g;
+       $item =~ s/ //;
+       @pairs = split(/[ =]/,$item);
+       shift @pairs;
+       %fields = @pairs;
+       $fields{'href'} =~ s/"//g;
+       chomp($fields{'href'});
+       $fields{'icon'} =~ s/"//g;
+
+       print "<div class=\"menuitem\">\n";
+
+       if($fields{'absolute'} =~ /true/){
+           print "<a href=\"$fields{'href'}\">\n";
+       }else{
+           print "<a href=\"$webroot/$fields{'href'}\">\n"
+       }
+
+       print "<img src=\"$webroot/buttons/$fields{'icon'}.png\" width=\"40\" height=\"40\" alt=$fields{'title'}/>\n";
+
+       $fields{'title'} =~ s/"//g;
+
+       print "<br/>$fields{'title'}</a></div>\n\n";
+
+    }
+}
+
+print <<END;
+
+</div>
+
+<a href="#pagetop" class="pagetop">Go to top</a>
+
+<div class="infobox">
+
+<p>
+This site supports TLS/SSL using a certificate signed by the
+"CSC Certificate Authority". Click <a href="$webroot/csclub.pe\
+m">
+here</a> (PEM format) or <a href="$webroot/csclub.der">here</a\
+> (DER
+format) to install the certificate authority into your browser\
+.
+</p>
+
+</div>
+
+</body>
+</html>
+
+END