#!./perl -w #!/usr/local/bin/perl5 -w ########################################################################### ### $Id: //depot/personal/ryan/optimism.cc/music.3/search.cgi#2 $ ########################################################################### ### search DB for matches to specified parameters ########################################################################### ### Unbuffer stdout to make life faster $|=1; ### Need to be able to see errors open(STDERR, ">&STDOUT"); ### Print out header before bad things happen print "Content-type: text/html\n\n"; ### Load in modules use strict; ### prevent typos use CGI; use music::db; ### DB routines use music::display; ### HTML routines ### Global variables my( $dbh, $cgi); ### Get the CGI data $cgi = new CGI; ### Log in to the server $dbh=&Connect(); ### Do we need to display search results? if( defined( $cgi->param('search')) && $cgi->param('search') eq "CD") { ### print out the nifty header &DisplayHeader( "Matching", "CDs"); ### Search for _cds_ &ShowCDSearch( $dbh, $cgi); } elsif( defined( $cgi->param('search')) && $cgi->param('search') eq "work") { ### print out the apropriate header if( defined( $cgi->param('composer_sel')) && $cgi->param('composer_sel') ne "") { my( $artist); $artist = &GetArtistInfo( $dbh, $cgi->param('composer_sel'), "name"); &DisplayHeader( "Works of $artist",""); } else { &DisplayHeader( "Search", "Matching Works", ""); } ### Search for _works_ &ShowWorkSearch( $dbh, $cgi); } elsif( defined( $cgi->param('search')) && $cgi->param('search') eq "any") { ### print out the nifty header if( defined( $cgi->param('find')) && $cgi->param('find') eq "cd") { &DisplayHeader( "Matching CDs", ""); } else { &DisplayHeader( "Matching Performances", ""); } ### Search for possibly anything (really performances) &ShowAnySearch( $dbh, $cgi); } else { ### print out the nifty header &DisplayHeader( "Search", ""); } &DisplaySearchBox( $dbh); ### Log off from the server $dbh->disconnect; ########################################################################### ### &ShowCDSearch( $dbh, $cgi); ########################################################################### ### ########################################################################### sub ShowCDSearch { my( $dbh, $cgi) = @_; my( $query, $key, @row, $rec, $sth, $sth2, $title, $count); print < Matching CDs EOF $query = "SELECT cd.cd_id,cd.title\ FROM cd_info cd\ WHERE "; if( defined( $cgi->param('title_reg')) && $cgi->param('title_reg') ne "") { $key = $dbh->quote($WILD . $cgi->param('title_reg') . $WILD); $key = uc($key); $key =~ s/\s+/$WILD/g; ### Need to put up a list of composers $query .= " UPPER(cd.title) $REGEXP $key"; } else { $key = $dbh->quote($cgi->param('title_sel')); # $key = uc($key); $query .= " cd.title=$key"; } # print "$query\n"; $sth=$dbh->prepare( $query); $sth->execute || die $dbh->errstr; ### Fetch the results $title = ""; for( $count=0;defined((@row = $sth->fetchrow_array)[0]);$count++) { if( $title ne $row[1]) { # print "\n" if( $count !=0); print "$row[1]"; $title = $row[1]; } # print ""; # print "$row[2]
" if( $row[2] ne ""); # print "$row[3]
" if( $row[3] ne ""); # print "$row[4]
" if( $row[4] ne ""); # print "$row[5]" if( $row[5] ne ""); # print "\n"; } if( $count==0) { print " 

Doh! Your search matched no CDs.

 \n"; } print "\n"; print "

\n"; } ########################################################################### ### &ShowWorkSearch( $dbh, $cgi); ########################################################################### ### ########################################################################### sub ShowWorkSearch { my( $dbh, $cgi) = @_; my( $query, $key, @row, $rec, $sth, $sth2, $title, $count); my( %seen, $wid, @parent_id, $parent, $i, %works, $work, @works); my( $sth3, @row3, $indent); print < EOF $query = "SELECT distinct work_id,title,name,p_work_id,mov_num FROM works,artist"; $query .= " WHERE works.comp_id=artist.art_id"; # $query .= " AND works.p_work_id=0"; if( defined( $cgi->param('composer')) && $cgi->param('composer') ne "") { $key = $dbh->quote($WILD . $cgi->param('composer') . $WILD); $key = uc($key); $key =~ s/\s+/$WILD/g; # $query .= " AND works.comp_id=composer.comp_id AND UPPER(composer.name) $REGEXP $key AND works.p_work_id=0"; $query .= " AND works.comp_id=composer.comp_id AND UPPER(composer.name) $REGEXP $key"; } elsif (defined($cgi->param('composer_sel')) && $cgi->param('composer_sel') ne "") { # $query .= " AND works.comp_id=" . $cgi->param('composer_sel') . " AND works.p_work_id=0"; $query .= " AND works.comp_id=" . $cgi->param('composer_sel'); } if( defined( $cgi->param('title_reg')) && $cgi->param('title_reg') ne "") { $key = $dbh->quote( $WILD . $cgi->param('title_reg') . $WILD); $key = uc($key); $key =~ s/\s+/$WILD/g; $query .= " AND UPPER(works.title) $REGEXP $key"; } elsif( defined($cgi->param('title_sel')) && $cgi->param('title_sel') ne "") { $key = $dbh->quote($cgi->param('title_sel')); $query .= " AND works.work_id=$key"; } if( defined($cgi->param('work_sel')) && $cgi->param('work_sel') ne "") { $key = $dbh->quote($cgi->param('work_sel')); $query .= " AND works.work_id=$key"; } ### We order by p_work_id so that as we're going through the ### results, we'll find the parent before the child and an skip it $query .= " ORDER BY p_work_id, title"; ### Need to put up a list of composers # print "$query\n"; $sth = $dbh->prepare( $query) || die $dbh->errstr; $sth->execute || die $dbh->errstr; ### Fetch the results ### The way the search is formed, we might find a work, and ### a child that both match. In this case, we need to ### make sure that we skip the child for( $count=0;defined((@row = $sth->fetchrow_array)[0]);$count++) { next if( defined( $seen{$row[0]})); $seen{$row[0]} = 1; ### Skip and work for which we have seen the parent next if( defined( $seen{$row[3]})); $works{$row[1]} = $row[0]; push( @works, $row[1]); } ### empty the hash so that we can use it below %seen = (); ### Then go ahead and print them out in order # @works = (sort {&WorkCmp($a,$b)} ( keys( %works))); # @works = keys( %works); for( $count=0;$count<=$#works;$count++) { $wid = $works{$works[$count]}; if( defined( $seen{$wid})) { # $count--; # next; } if( $count==1+int($#works/2)) { print " \n"; } if( $count==0 || $count==1+int($#works/2)) { print "\n"; print "\n"; } my($bgcolor) = ($count%2==0) ? "#d5d5d0" : "#eeeeee"; $bgcolor="#FFFFFF"; print "\n"; } else { print "
\n"; ### First thing we need to do is to show where ### we came from, by showing out parents @parent_id = GetWorkParents( $dbh, $wid); # print "$#parent_id\n"; for( $i=$#parent_id; $i>= 0; $i--) { &DisplayWork( $dbh, $parent_id[$i], undef, $#parent_id-$i); ### Indent our chidren # print "
\n"; print "
    "; } ### Now show our info (and sub works) &ShowWork( $dbh, $wid, \%seen, $indent); ### Close off the parent indenting for( $i=$#parent_id; $i>= 0; $i--) { # print "
\n"; print "\n"; } next; } if( $count==0) { print "
 

Doh! Your search matched no works.

 

\n"; } print "\n"; print "

\n"; } ########################################################################### ### &ShowWork( $dbh, $work_id, %seen, $indent); ########################################################################### ### ########################################################################### sub ShowWork { my( $dbh, $work_id, $seen_ref, $indent) = @_; my( $query, @row, $sth, $count); my( %sub, $sub); return if( defined( $$seen_ref{$work_id})); $$seen_ref{$work_id} = 1; ### Extract the info about this work $query = "SELECT work_id, title, p_work_id FROM works WHERE work_id=$work_id"; $sth=$dbh->prepare( $query); $sth->execute || die $dbh->errstr; for( $count=0;defined((@row = $sth->fetchrow_array)[0]);$count++) { &DisplayWork( $dbh, $row[0], $row[1], $row[2]); # print "
foom\n"; } $sth->finish; &ShowMatchingCDs( $dbh, $work_id, $indent) if( defined( $cgi->param('show'))); return if( defined( $cgi->param('nochildren'))); ### Grab our children $query = "SELECT work_id, title, p_work_id FROM works WHERE p_work_id=$work_id ORDER BY mov_num,title"; # print "$query\n"; $sth=$dbh->prepare( $query); $sth->execute || die $dbh->errstr; # print "

\n" if( $sth->rows>0); print "
\n" if( $sth->rows>0); print "" if( $sth->rows>0); } ########################################################################### ### ########################################################################### ### ########################################################################### sub WorkCmp { my( $work_a, $work_b) = @_; my( $i, $j, $x, $y); if( $work_a =~ /^(\D*)(\d+)/) { $x=$1; $i=$2; if( $work_b =~ /^(\D*)(\d+)/) { $y=$1; $j=$2; if( $x eq $y) { return $i<=>$j; } } } return $work_a cmp $work_b; } ########################################################################### ### &ShowMatchinCDs( $dbh, $work_id, $indent); ########################################################################### ### Display all CD titles containing this work ########################################################################### sub ShowMatchingCDs { my( $dbh, $work_id, $indent) = @_; my( $query, @row, $sth, $count); $indent = "" if( !defined( $indent)); $indent .= "     "; #print "C: $work_id $indent
\n"; $sth = $dbh->prepare("SELECT distinct cd_info.cd_id,cd_info.title\ FROM cd_info, cd_track, performance\ WHERE performance.work_id=$work_id\ AND performance.perf_id=cd_track.perf_id\ AND cd_track.cd_id=cd_info.cd_id\ ORDER BY cd_info.title") || die $dbh->errstr; $sth->execute || die $dbh->errstr; for( $count=0;( defined((@row = $sth->fetchrow_array)[0]));$count++) { print "\n" if( $count>0); $sth->finish; } ########################################################################### ### &ShowAnySearch( $dbh, $cgi); ########################################################################### ### search for performances and display results ########################################################################### sub ShowAnySearch { my( $dbh, $cgi) = @_; my( $query, $key, @row, $rec, $sth, $sth2, $title, $count); print < EOF $query = "SELECT DISTINCT cd.cd_id,cd.title,w.title,cm.name,cn.name,p.ens_id,w.work_id \ FROM cd_info cd,cd_track ct,performance p,works w,artist cm,\ artist cn, ensemble e, artist m \ WHERE cd.cd_id=ct.cd_id AND ct.perf_id=p.perf_id AND \ p.work_id=w.work_id AND w.comp_id=cm.art_id AND \ p.cond_id=cn.art_id AND p.ens_id=e.ens_id AND\ e.mus_id=m.art_id"; if( defined( $cgi->param('title_reg')) && $cgi->param('title_reg') ne "") { $key = $dbh->quote( $WILD . $cgi->param('title_reg') . $WILD); $key = uc($key); $key =~ s/\s+/$WILD/g; ### Need to put up a list of composers $query .= " AND UPPER(cd.title) $REGEXP $key"; } elsif( defined( $cgi->param('title_sel')) && $cgi->param('title_sel') ne "") { $key = $dbh->quote($cgi->param('title_sel')); $key = uc($key); $query .= " AND cd.title=$key"; } if( defined( $cgi->param('work_reg')) && $cgi->param('work_reg') ne "") { $query =~ s/works w,/works w, works w1, works w2,/; $key = $dbh->quote( $WILD . $cgi->param('work_reg') . $WILD); $key = uc($key); $key =~ s/\s+/$WILD/g; ### Need to put up a list of composers $query .= " AND (UPPER(w.title) $REGEXP $key"; $query .= " OR (w.work_id = w1.p_work_id AND UPPER(w1.title) $REGEXP $key)"; $query .= " OR (w1.work_id = w2.p_work_id AND UPPER(w2.title) $REGEXP $key))"; } elsif( defined( $cgi->param('work_sel')) && $cgi->param('work_sel') ne "") { $key = $dbh->quote($cgi->param('work_sel')); $key = uc($key); $query .= " AND w.title=$key"; } if( defined( $cgi->param('conductor_reg')) && $cgi->param('conductor_reg') ne "") { $key = $dbh->quote( $WILD . $cgi->param('conductor_reg') . $WILD); $key = uc($key); $key =~ s/\s+/$WILD/g; $query .= " AND UPPER(cn.name) $REGEXP $key"; } elsif( defined( $cgi->param('conductor_sel')) && $cgi->param('conductor_sel') ne "") { $key = $dbh->quote($cgi->param('conductor_sel')); $key = uc($key); $query .= " AND cn.art_id=$key"; } if( defined( $cgi->param('composer_reg')) && $cgi->param('composer_reg') ne "") { $key = $dbh->quote( $WILD . $cgi->param('composer_reg') . $WILD); $key = uc($key); $key =~ s/\s+/$WILD/g; $query .= " AND UPPER(cm.name) $REGEXP $key"; } elsif( defined( $cgi->param('composer_sel')) && $cgi->param('composer_sel') ne "") { $key = $dbh->quote($cgi->param('composer_sel')); $key = uc($key); $query .= " AND cm.art_id=$key"; } if( defined( $cgi->param('musician_reg')) && $cgi->param('musician_reg') ne "") { $key = $dbh->quote( $WILD . $cgi->param('musician_reg') . $WILD); $key = uc($key); $key =~ s/\s+/$WILD/g; $query .= " AND UPPER(m.name) $REGEXP $key"; } elsif( defined( $cgi->param('musician_sel')) && $cgi->param('musician_sel') ne "") { $key = $dbh->quote($cgi->param('musician_sel')); $key = uc($key); $query .= " AND m.art_id=$key"; } if( defined($cgi->param('find')) && $cgi->param('find') eq "cd") { $query .= " ORDER BY cd.title"; } # print "$query\n"; $sth=$dbh->prepare( $query); $sth->execute || die $dbh->errstr; ### Fetch the results $title = ""; for( $count=0;defined((@row = $sth->fetchrow_array)[0]);$count++) { if( $title ne $row[1]) { # print "\n" if( $count !=0); print "\n"; print "$row[1]
"; &DisplayDiscInfo( $dbh, $row[0], 0, 1) if( defined( $cgi->param('find')) && $cgi->param('find') eq "cd"); $title = $row[1]; } next if( defined($cgi->param('find')) && $cgi->param('find') eq "cd"); # print "   "; my( @parent_id, $i, $indent); @parent_id = GetWorkParents( $dbh, $row[6]); # print "$#parent_id\n"; $indent=""; for( $i=$#parent_id; $i>= 0; $i--) { # print "