tags.pm

Insipid 是一款基于Web书签仓库。很方面的记录下各种输入输出信息。

#!/usr/bin/perl -w
#
# Copyright (C) 2005 Luke Reeves
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307
# USA
#

package Insipid::Tags;

use strict;
use vars qw(@ISA @EXPORT @EXPORT_OK);
use CGI qw/:standard/;
use CGI::Carp qw(fatalsToBrowser);
use Insipid::Config;
use Insipid::Database;
require Exporter;

@ISA = qw(Exporter);

@EXPORT = qw(
show_tags
get_tags
get_tags_list
set_tags
);

# display the tag list as an HTML list.
sub show_tags {
	my ($sql, $sth);
	print "<div id=\"leftside\">";

	# If the user has already chosen a tag, get the intersection list
	if(defined(url_param('tag')) && ($logged_in eq 1)) {
		$sql = "select tags.name,count(*) from bookmarks 
			join bookmark_tags as bt1 on 
				(bookmarks.id = bt1.bookmark_id) 
			join tags on 
				(tags.id = bt1.tag_id)
			join bookmark_tags as bt2 on 
				(bookmarks.id = bt2.bookmark_id) 
			join tags as t2 on (t2.id = bt2.tag_id and t2.name = ?)
			where (tags.name != ?)
			group by tags.name";
		$sth = $dbh->prepare($sql);
		$sth->execute(url_param('tag'), url_param('tag'));

		if($sth->rows ne 0) {
			print "<div id=\"taglist\" style=\"\">";
			print "<table cellpadding =\"0\" cellspacing=\"0\" class=\"tagsummarytable\"><tbody>";
			print "<tr><td align=\"center\"><div class=\"inline_title\">Add Tag</div></td>";
		
			while(my @rs = $sth->fetchrow_array()) {
				my $tt = url_param('tag');
				my $link = "$site_url/bookmarks/$tt+$rs[0]";
				$tt =~ s/ /\+/g;
				print "<tr><td>&nbsp;<span class=\"tagtabletext\">($rs[1])&nbsp;</span><a href=\"$link\">$rs[0]</a>&nbsp;";
				print "</tr></td>\n";
			}

			print "</tbody></table></div></div>";
			return;
		}
	}

	# Access_spec contains a where clause to count only public bookmarks 
	# if the user is not logged in
	my $access_where = "";
	if($logged_in eq 0) {
		$access_where = " where (bookmarks.access_level = 1) ";
	}

	my $order_clause;
	if($dbtype eq "Pg") {
		$order_clause = "order by upper(tags.name)";
	} else {
		$order_clause = "order by tags.name";
	}

	$sql = "select tags.name, count(*) 
		   from bookmarks  
		   inner join bookmark_tags on
			(bookmarks.id = bookmark_tags.bookmark_id)
		   inner join tags on
			(tags.id = bookmark_tags.tag_id)
		   $access_where
		   group by tags.name
		   $order_clause"; 

	$sth = $dbh->prepare($sql);
	$sth->execute;

	print "<div id=\"taglist\" style=\"\">";
	print "<table cellpadding =\"0\" cellspacing=\"0\" class=\"tagsummarytable\"><tbody>";
	print "<tr><td align=\"center\"><div class=\"inline_title\">Tag List</div></td>";

	while(my @r = $sth->fetchrow_array) {
		my $link = sprintf("%s/bookmarks/%s", $site_url, $r[0]);
		
		print "<tr><td>&nbsp;<span class=\"tagtabletext\">($r[1])&nbsp;</span><a href=\"$link\">$r[0]</a>&nbsp;";
	#	if(defined(url_param('tag'))) {
			# check the intersection
	#		if($intersections{$r[0]} eq 1) {
	#			my $tt = url_param('tag');
	#			$tt =~ s/ /\+/g;
	#			print "<a href=\"$site_url/bookmarks/$tt+$r[0]\">+</a>";
	#		}
	#	}
		print "</td></tr>\n";
	}
	print "</tbody></table></div>";
	print "</div>";
}

# Get a string representing a URLs tags
sub get_tags {
	my ($url) = (@_);
	my @tags = get_tags_list($url);

	my $rv = "";
	foreach (@tags) {
		$rv = "$rv $_";
	}

	# Trim leading whitespace
	$rv =~ s/^\s+//;
	return $rv;
}

# Get a list of the tags for a given URL id
sub get_tags_list {
	my ($url) = (@_);
	my $sql = <<SQL;
select tags.name 
from tags 
join bookmark_tags on 
  (tags.id = bookmark_tags.tag_id) 
join bookmarks on
  (bookmark_tags.bookmark_id = bookmarks.id)
where 
  (bookmarks.url = ?)
SQL
  
	my $sth = $dbh->prepare($sql);
	$sth->execute($url);

	my @tags;
	while(my @r = $sth->fetchrow_array) {
		push(@tags, $r[0]);
	}

	return @tags;
}

sub set_tags {
	my ($bookmark_id, $tag_string) = (@_);

	if($logged_in ne 1) {
	  push(@errors, "You have to be logged in to perform that operation.");
	  return;
	}
	
	my @tags = split(" ", $tag_string);
	
	# Clear old tags first.
	my $sql = "DELETE FROM bookmark_tags WHERE (bookmark_id = ?)";
	my $sth = $dbh->prepare($sql);
	$sth->execute($bookmark_id);
	
	foreach my $cur (@tags) {
	    # check if this tag exists in tags table
	    my $sql = "SELECT COUNT(id) FROM tags WHERE name=?";
	    my $sth = $dbh->prepare($sql);
	    $sth->execute($cur);
	    my $tagcount = $sth->fetchrow_array();

	    # or create a new tag
	    if ($tagcount < 1) {
		my $sql = "INSERT INTO tags (name) VALUES(?)";
		my $sth = $dbh->prepare($sql);
		$sth->execute($cur);
	    }

	    # and fetch the tag ID
	    $sql = "SELECT id FROM tags WHERE name=?";
	    $sth = $dbh->prepare($sql);
	    $sth->execute($cur);
	    my $tid = $sth->fetchrow_array;

	    $sql = "INSERT INTO bookmark_tags(bookmark_id, tag_id) 
		  VALUES( ? , ? )";
	    $sth = $dbh->prepare($sql);
	    $sth->execute($bookmark_id, $tid);
	}

	
}


1;
__END__