main.pm

来自「Insipid 是一款基于Web书签仓库。很方面的记录下各种输入输出信息。」· PM 代码 · 共 1,216 行 · 第 1/3 页

    xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
    xmlns:content="http://purl.org/rss/1.0/modules/content/">
    
<channel>
  <title>$title</title>
  <link>$site_url/bookmarks</link>
  <description>Aggregated links</description>
  <dc:language>en-us</dc:language>
  <dc:creator>Insipid</dc:creator>
  <dc:rights>Copyright 2005</dc:rights>
RDFHEADER

	while(my @hr = $sth->fetchrow_array) {
	  my $url = sanitize_html($hr[2]);
	  my $title = sanitize_html($hr[1]);
	  print <<ITEM;
<item>
  <title>$title</title>
  <link>$url</link>
  <guid isPermaLink="false">$hr[0]_$site_url/feeds/bookmarks</guid>
  <content:encoded><![CDATA[<a href="$url">$hr[1]</a>]]></content:encoded>
</item>
ITEM
	}

	print "</channel></rss>\n\n";
}
sub show_bookmarks {
	my ($subquery, $sql, $sth, @parms, @wheres, @hr);
	
	# this first query will be used to select from a set, like when a user 
	# drills in on a specific tag or to get a smaller view of the entire
	# dataset (for paging purposes).

	# MySQL and postgres have slightly different syntax here...
	if ($dbtype eq 'mysql') {
	    $sql = "select distinct bookmarks.id from bookmarks";
	} elsif ($dbtype eq 'Pg') {
	    $sql = "select distinct bookmarks.id, bookmarks.date from bookmarks";
	}

	# Limit to tags
	if(defined(url_param('tag'))) {
		# Join the tag tables only when necessary
	
		if(url_param('tag') =~ / /) {
			my @tags = split(/ /, url_param('tag'));
			my $icount = 1;
			
			foreach(@tags) {		
				push(@parms, $_);
				$sql = "$sql join bookmark_tags as bt$icount on 
					  (bookmarks.id = bt$icount.bookmark_id)
					join tags as t$icount on 
					   (t$icount.id = bt$icount.tag_id and t$icount.name = ?) ";
				$icount++;
			}
		} else {
			$sql = "$sql 
				left join bookmark_tags on 
				  (bookmarks.id = bookmark_tags.bookmark_id)	
				join tags on 
			  	  (tags.id = bookmark_tags.tag_id) where (tags.name = ?)";
			push(@parms, url_param('tag'));
		}

	}

	# Search
	if($query ne "") {
  		if((get_option("public_searches") eq "yes") || ($logged_in eq 1)) {
			my $sparm = $query;
			if(length($sparm) > 2) {
				$sql = "$sql where (bookmarks.title like ?)";
				$sparm =~ s/\%//;
				$sparm = "\%$sparm\%";
				push(@parms, $sparm);
			}
		}
	}

	# order
	$sql = "$sql order by bookmarks.date desc";

	# paging functionality
	$sql = "$sql limit 50";
	
	if(defined(url_param('page'))) {
	    my $offset = ((url_param('page') - 1) * 50);
	    $sql = "$sql offset $offset";
	}

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

	$subquery = "";
	if($sth->rows > 0) {
		if($sth->rows ne 50) { $last_page = 1; }
	
		$subquery = " bookmarks.id in (";
		
		while(@hr = $sth->fetchrow_array) {
			$subquery = $subquery . "$hr[0],";
		}
		chop($subquery); # Strip off the last delimiter
		
		$subquery = $subquery . ")";
	} else {
		print "<p>No bookmarks found.</p>";
		return;
	}

	@parms = ();
	@wheres = ();
	
        $sql = "select 
  		  bookmarks.id, 
		  bookmarks.title, 
		  bookmarks.description, 
		  access_level,
		  bookmarks.url,
		  tags.name,
		  bookmarks.date,
		  pagecache.date as cache_date,
		  bookmarks.md5
		from bookmarks 
		left join bookmark_tags on 
		  (bookmarks.id = bookmark_tags.bookmark_id)
		left join tags on 
		  (tags.id = bookmark_tags.tag_id)
		left join pagecache on
		  (bookmarks.md5 = pagecache.md5)";
	
	# Don't show private marks for non-logged in users
	if($logged_in eq 0) {
	  push(@wheres, "bookmarks.access_level");
	  push(@parms, "1");
	}

	my $max = @wheres;
	if($max ne 0) {
	  $sql = "$sql where (";
	  my $count = 1;
	  
  	  foreach (@wheres) {
	    $sql = "$sql $_ = ?";
	    if($count < $max) {
	      $sql = "$sql and ";
	    }
	    $count++;
	  }

	  $sql = "$sql )";
	  if($subquery ne "") { $sql = "$sql and $subquery"; }
	} else {
	  if($subquery ne "") { $sql = "$sql where $subquery "; }
	}

        # append sort order.
	$sql = "$sql order by bookmarks.date desc";

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

	my %last;
	$last{id} = -1;

	print "<ul><br>";

	my $title = "";
	if(defined(url_param('tag'))) {
		my $temp = url_param('tag');
		if($temp =~ / /) {
			my $count = 0;
			foreach(split(/ /, $temp)) {
				if($count++ ne 0) { $title = "$title +"; }
				$title = "$title <a class=\"bodyTitle\" href=\"$site_url/bookmarks/$_\">$_</a>";
			}
		} else {
			$title = "<a class=\"bodyTitle\" href=\"$site_url/bookmarks/$temp\">$temp</a>";
		}
	} else {
		$title = "Most Recent Bookmarks";
	}

	if($query ne "") { 
		$title = sprintf("Search results for \"%s\"", $query); 
	}
	  
	print "<span class=\"bodyTitle\">$title</span>";
	show_footer();
	print "<br>";
	print "<br>";

	print "<table class=\"bookmarklist\">";
	print "<tr><td>";
	print "<ul type=\"circle\">\n";
	print "<!-- bookmark item-->";
	while(@hr = $sth->fetchrow_array) {
		if($last{id} eq -1) {
		  $last{id} = $hr[0];
		  $last{title} = $hr[1];
		  $last{description} = $hr[2];
		  $last{access_level} = $hr[3];
		  $last{url} = $hr[4];
		  $last{tags} = "";
		  $last{timestamp} = $hr[6];
		  $last{cachetime} = $hr[7];
		  $last{md5} = $hr[8];
		} 
		
		if($hr[0] ne $last{id}) {
		  # the id changed, so show the last mark.
		  show_bookmark($last{id}, $last{title}, $last{description}, $last{access_level}, $last{url}, $last{tags}, $last{timestamp}, $last{cachetime}, $last{md5});	

		  # Swap the new one in.
		  $last{id} = $hr[0];
		  $last{title} = $hr[1];
		  $last{description} = $hr[2];
		  $last{access_level} = $hr[3];
		  $last{url} = $hr[4];
		  $last{tags} = $hr[5];
		  $last{timestamp} = $hr[6];
		  $last{cachetime} = $hr[7];
		  $last{md5} = $hr[8];
		} else {
		  # Add tag to the current bookmark
		  if(defined($hr[5])) {
		    $last{tags} = "$last{tags} $hr[5]";
		  }
		}
	}

	if($last{id} ne -1) {
		show_bookmark($last{id}, $last{title}, $last{description}, $last{access_level}, $last{url}, $last{tags}, $last{timestamp}, $last{cachetime}, $last{md5});
	}

	print "</ul></td></tr></table>";
}

sub show_bookmark {
	my($id, $title, $description, $access_level, $url, 
		$tags, $timestamp, $cachetime, $md5) = (@_);

	print "<div class=\"bookmarklistitem\">";
	print "<li>";
	if($access_level eq 0) { 
	  print "<a href=\"$site_url/insipid.cgi?go=$id\">";
	  print "<i>";
	  print "$title";
	  print "</i>";
	} else {
	  print "<a href=\"$url\">";
	  print $title;
	}

	if($logged_in eq 1) {
	  if(defined($cachetime)) {
		my $slink = "$site_url/snapshot/$md5";
		print "</a> - <a href=\"$slink\">view snapshot";
	  }
	}
	
	print "</a><br /><div class=\"bookmarkOperations\">";

	
	my $timestr = "";
	if($logged_in eq 1) {
	  $timestr = time2str("%Y-%m-%d %T EST", $timestamp, "EST");
	} else {
	  $timestr = time2str("%Y-%m-%d", $timestamp, "EST");
	}

	print "posted on $timestr ";
	    
	if(defined($tags)) {
	  print "to ";
	  my $cur;
	    
	  foreach $cur (split(/\ /, $tags)) {
	    print "<a class=\"bookmarkTag\" href=\"$site_url/bookmarks/$cur\">$cur</a> ";
	  }
	}

	if($logged_in eq 1) {
	  my $ex = "";

	  if(url_param('tag')) { $ex = "$ex&tag=" . url_param('tag'); }
	  if(url_param('page')) { $ex = "$ex&page=" . url_param('page'); }
	  if($query ne "") { $ex = "$ex&q=" . $query; }
	  
	  print "<span class=\"bodytext\">&nbsp;&mdash;&nbsp;";
	  print "(<a class=\"bookmarkOp\" href=\"$site_url/insipid.cgi?op=delete_bookmark&id=$id$ex\">delete</a>,&nbsp;";
	  print "<a class=\"bookmarkOp\" href=\"$site_url/insipid.cgi?op=edit_bookmark&id=$id$ex\">edit</a>";
	  if(!defined($cachetime)) {
	    print ",&nbsp;<a class=\"bookmarkOp\" href=\"$site_url/insipid.cgi?op=snapshot&id=$id$ex\">snapshot</a>";
	  }
	  print ")<div class=\"bookmarkDescription\">$description</div></span></div></li>\n";
	}
	
	print "</div>\n";
}

# Gets the ID for a bookmark if it already exists in the DB. Otherwise, -1.
sub get_bookmark_id {
	my ($url) = (@_);
	
	# Lookup the URL id first.
	my $sql = "select bookmarks.id from bookmarks where (bookmarks.md5 = ?)";
	my $sth = $dbh->prepare($sql);
	$sth->execute(md5_hex("$url"));

	if($sth->rows ne 0) {
		my @r = $sth->fetchrow_array;
		return $r[0];
	}
	
	return -1;
}

sub get_bookmark {
	my ($id) = (@_);

	my $sql = <<SQL;
select 
  bookmarks.title, 
  bookmarks.description, 
  bookmarks.url,
  bookmarks.access_level 
from bookmarks 
where (bookmarks.id = ?)

SQL
	my $sth = $dbh->prepare($sql);
	$sth->execute($id);
	my @r = $sth->fetchrow_array;
	return ($r[2], $r[0], $r[1], $r[3]);
}

sub update_bookmark {
	my ($id, $url, $title, $description, $access_level, $tags) = (@_);

	if($logged_in ne 1) {
	  push(@errors, "You have to be logged in to perform that operation.");
	  return;
	}
	
	my $sql = "update bookmarks 
			set url = ?, md5 = ?, title = ?, description = ?, 
			access_level = ? where (id = ?)";
	my $sth = $dbh->prepare($sql);
	$sth->execute($url, md5_hex("$url"), $title, $description, 
			$access_level, $id);

	set_tags($id, $tags);
}

sub add_bookmark {
	my ($url, $title, $description, $access_level, $epoch, $tags) = (@_);
	my ($sql, $sth);
	
	if($logged_in ne 1) {
	  push(@errors, "You have to be logged in to perform that operation.");
	  return;
	}
	
	# Check for duplicate
	$sql = "SELECT * FROM bookmarks WHERE
		(md5 = ?)";
	$sth = $dbh->prepare($sql);
	$sth->execute(md5_hex("$url"));
	
	if($sth->rows ne 0) {
		$duplicates++;
		return;
	}

	$sql = "INSERT INTO bookmarks 
		(url, md5, title, description, access_level, date) 
		VALUES (?, ?, ?, ?, ?, ?)";

	if($epoch eq 0) { $epoch = time; }
	$sth = $dbh->prepare($sql);

	$sth->execute($url, md5_hex("$url"), $title, $description, $access_level, $epoch);
	$icount++;

	set_tags(get_bookmark_id_by_url($url), $tags);
}

sub get_bookmark_id_by_url {
	my ($url) = (@_);
	my $sql = "select bookmarks.id from bookmarks where (bookmarks.url = ?)";
	my $sth = $dbh->prepare($sql);
	$sth->execute($url);

	my @r = $sth->fetchrow_array;
	return $r[0];
}


1;
__END__