From a0e870fd10215d7a4a84df15a825f639c77ec085 Mon Sep 17 00:00:00 2001 From: Banana Date: Wed, 15 Feb 2012 14:45:07 +0100 Subject: [PATCH] new tag list --- insipid.css | 365 ++++++++++++++++++++-------------------- lib/Insipid/Database.pm | 18 +- lib/Insipid/Tags.pm | 203 +++++++++++----------- 3 files changed, 296 insertions(+), 290 deletions(-) diff --git a/insipid.css b/insipid.css index f577643..09724c9 100755 --- a/insipid.css +++ b/insipid.css @@ -1,181 +1,190 @@ -/** - * Copyright (C) 2008 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 - */ - -* { - font-family:Arial, Helvetica, sans-serif; -} - -#leftside { - float:left; - padding-right:20; - padding-left:20; - padding-top:20; -} - -.bodyContent { - margin-right: 15px; - margin-top: 10px; -} - -.title { - padding-left:20px; - padding-top:10px; - color: blue; - font-size:medium; - font-weight:bold; - background-color: #CCCCCC; -} - -.tagsummarytable{ - border-color:#000000; - border-style:solid; - -moz-box-sizing: border-box; - -moz-border-radius:8px; - border-width: mid; - font-size: small; - margin-bottom: 15px; -} - -.tagtabletext{ - font-family:Arial, Helvetica, sans-serif; - text-decoration: none; - color:#000000; - -} - -.bodytext{ - font-family:Arial, Helvetica, sans-serif; - color: black; -} - -/* Used for "Most Recent Bookmarks" */ -.bodyTitle{ - color: black; - font-weight: bold; -} - -.inline_title{ - font-family:Arial, Helvetica, sans-serif; - color:#FFFFFF; - background:#000000; -} - -.search { - padding-left: 10px; - text-align: left; - background-color: #CCCCCC; -} - -.toolbar { - text-align: right; - padding-right: 10px; - background-color: #CCCCCC; - font-size: small; -} -h2 { - font-size: medium; - padding-top: 10px; -} - -h3 { - font-size: medium; - text-align: center; - padding-top: 10px; -} - -a.tools { - font-family:Arial, Helvetica, sans-serif; - color: black; - font-weight: bold; - line-height:2; -} - -a.tools:visited { - color: black; -} - -a.tools:hover { - font-family:Arial, Helvetica, sans-serif; - color: black; - font-weight: bold; - background:white; - text-decoration: none; - line-height:2; -} - - -.formtext { - font-family:Arial, Helvetica, sans-serif; - text-decoration: none; -} - - -A { - font-family:Arial, Helvetica, sans-serif; - text-decoration: none; -} - -A:hover { - font-family:Arial, Helvetica, sans-serif; - text-decoration: underline -} - -A:visited { - font-family:Arial, Helvetica, sans-serif; - text-decoration: none; - color:blue; -} - -.error { - font-family:Arial, Helvetica, sans-serif; - margin-top: 40px; - margin-bottom: 10px; - color: red; -} - -.bookmarkOperations { - font-family: Arial, Helvetica, sans-serif; - font-size: small; - margin-left: 20px; - color: #777777; -} - -li{ color:#8888ff; } -.bookmarklistitem{padding-bottom:8;} - -.bookmarklist{ - font-size: medium; - margin-left: 20px; - border-width:mid; - border-color:#CCCCCC; - border-style:dotted; - padding-right:10px; -} - -A.bookmarkTag:link { color: #8888ff; } -A.bookmarkTag:visited { color: #8888ff; } -A.bookmarkTag:hover { color: #8888ff; } - -A.bookmarkOp:link { color: #800000; } -A.bookmarkOp:visited { color: #800000; } -A.bookmarkOp:hover { color: #800000; } - +/** + * Copyright (C) 2008 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 + */ + +* { + font-family:Arial, Helvetica, sans-serif; +} + +#leftside { + float:left; + padding-right: 20px; + padding-left: 20px; + padding-top: 20px; +} + +.bodyContent { + margin-right: 15px; + margin-top: 10px; +} + +.title { + padding-left:20px; + padding-top:10px; + color: blue; + font-size:medium; + font-weight:bold; + background-color: #CCCCCC; +} + +.tagsummarytable { + border-color:#000000; + border-style:solid; + -moz-box-sizing: border-box; + -moz-border-radius:8px; + border-width: medium; + font-size: small; + margin-bottom: 15px; +} + +.tagsummarytable th { + background-color: #000; + color: #fff; +} +.tagsummarytable td { + padding: 1px 3px 0 1px; +} + +.tagtabletext { + font-family:Arial, Helvetica, sans-serif; + text-decoration: none; + color: #000000; +} + +.bodytext{ + font-family:Arial, Helvetica, sans-serif; + color: black; +} + +/* Used for "Most Recent Bookmarks" */ +.bodyTitle{ + color: black; + font-weight: bold; +} + +.inline_title{ + font-family:Arial, Helvetica, sans-serif; + color:#FFFFFF; + background:#000000; +} + +.search { + padding-left: 10px; + text-align: left; + background-color: #CCCCCC; +} + +.toolbar { + text-align: right; + padding-right: 10px; + background-color: #CCCCCC; + font-size: small; +} +h2 { + font-size: medium; + padding-top: 10px; +} + +h3 { + font-size: medium; + text-align: center; + padding-top: 10px; +} + +a.tools { + font-family:Arial, Helvetica, sans-serif; + color: black; + font-weight: bold; + line-height:2; +} + +a.tools:visited { + color: black; +} + +a.tools:hover { + font-family:Arial, Helvetica, sans-serif; + color: black; + font-weight: bold; + background:white; + text-decoration: none; + line-height:2; +} + + +.formtext { + font-family:Arial, Helvetica, sans-serif; + text-decoration: none; +} + + +A { + font-family:Arial, Helvetica, sans-serif; + text-decoration: none; +} + +A:hover { + font-family:Arial, Helvetica, sans-serif; + text-decoration: underline +} + +A:visited { + font-family:Arial, Helvetica, sans-serif; + text-decoration: none; + color:blue; +} + +.error { + font-family:Arial, Helvetica, sans-serif; + margin-top: 40px; + margin-bottom: 10px; + color: red; +} + +.bookmarkOperations { + font-family: Arial, Helvetica, sans-serif; + font-size: small; + margin-left: 20px; + color: #777777; +} + +li{ color:#8888ff; } +.bookmarklistitem{ + padding-bottom:8px; +} + +.bookmarklist{ + font-size: medium; + margin-left: 20px; + border-width: medium; + border-color:#CCCCCC; + border-style:dotted; + padding-right:10px; +} + +A.bookmarkTag:link { color: #8888ff; } +A.bookmarkTag:visited { color: #8888ff; } +A.bookmarkTag:hover { color: #8888ff; } + +A.bookmarkOp:link { color: #800000; } +A.bookmarkOp:visited { color: #800000; } +A.bookmarkOp:hover { color: #800000; } + #suggestlist { display: none; padding-bottom: 0.5em; diff --git a/lib/Insipid/Database.pm b/lib/Insipid/Database.pm index 10938e1..0815a7f 100755 --- a/lib/Insipid/Database.pm +++ b/lib/Insipid/Database.pm @@ -31,12 +31,12 @@ use vars qw($version); use Exporter (); our (@ISA, @EXPORT); - + @ISA = qw(Exporter); -@EXPORT = qw($dbname $dbuser $dbpass $dsn $dbh $dbtype get_option +@EXPORT = qw($dbname $dbuser $dbpass $dsn $dbh $dbtype get_option install $version $tag_url $feed_url $full_url $snapshot_url export_options $dbprefix); - + our ($dsn, $dbh, $dbname, $dbuser, $dbpass, $dbhost, $snapshot_url, $dbtype, $tag_url, $feed_url, $full_url, $dbprefix); @@ -48,25 +48,25 @@ $dbhost = getconfig('dbhost'); $dbtype = 'mysql'; $dsn = "DBI:$dbtype:dbname=$dbname;host=$dbhost"; -$dbh = DBI->connect($dsn, $dbuser, $dbpass, { 'RaiseError' => 0}) or die $DBI::errstr; +$dbh = DBI->connect($dsn, $dbuser, $dbpass, { 'RaiseError' => 1, 'PrintError' => 1}) or die $DBI::errstr; my %options; sub export_options { my ($writer) = (@_); my ($sth); - + $writer->startTag('options'); $sth = $dbh->prepare("select name, value from $tbl_options"); $sth->execute(); while(my $row = $sth->fetchrow_hashref) { if($row->{name} ne 'version') { - $writer->emptyTag('option', + $writer->emptyTag('option', 'name' => $row->{name}, 'value' => $row->{value}); } } - + $writer->endTag('options'); } @@ -75,7 +75,7 @@ sub dbupgrade { my $sth = $dbh->prepare($sql); $sth->execute($version, 'version'); - $sql = "insert into $tbl_options(name, value, description) + $sql = "insert into $tbl_options(name, value, description) values(?, ?, ?)"; $sth = $dbh->prepare($sql); $sth->execute('version', $version, 'Internal Insipid version'); @@ -96,7 +96,7 @@ sub dbupgrade { if($dbh->errstr) { print STDERR $dbh->errstr; } - + return; } diff --git a/lib/Insipid/Tags.pm b/lib/Insipid/Tags.pm index ad98d8f..6d6c25d 100755 --- a/lib/Insipid/Tags.pm +++ b/lib/Insipid/Tags.pm @@ -45,41 +45,41 @@ tag_operations ); sub tag_operations { - + check_access(); - + my ($sql,$sth,$sql1, $sth1, $sql2,$sth2); - + if(param('save') && param('newName')) { print '

Reanming...

'; - + my $newTag = param('newName'); my $oldTagName = param('rename'); my $oldTagId = get_tag_id_by_name($oldTagName); - + # is the new name already a tag ? # if check then check is the new tagId my $check = get_tag_id_by_name($newTag); if($check && ($check != $oldTagId)) { - $sql = "SELECT bookmark_id, tag_id + $sql = "SELECT bookmark_id, tag_id FROM `$tbl_bookmark_tags` WHERE tag_id = ?"; $sth = $dbh->prepare($sql); $sth->execute($oldTagId); while(my ($bid, $tid) = $sth->fetchrow_array) { - $sql1 = "DELETE FROM `$tbl_bookmark_tags` + $sql1 = "DELETE FROM `$tbl_bookmark_tags` WHERE bookmark_id = ? AND tag_id = ?"; $sth1 = $dbh->prepare($sql1); $sth1->execute($bid,$tid); - + $sql2 = "INSERT IGNORE INTO `$tbl_bookmark_tags` SET bookmark_id = ?, tag_id = ?"; $sth2 = $dbh->prepare($sql2); $sth2->execute($bid,$check); - } - + } + # remove the old one $sql = "DELETE FROM `$tbl_tags` WHERE id = ?"; $sth = $dbh->prepare($sql); @@ -91,17 +91,17 @@ sub tag_operations { $sth = $dbh->prepare($sql); $sth->execute($newTag,$oldTagId); } - + print 'Done !'; } elsif(param("doDelete") && param("deleteTag") && param("moveTo")) { print '

Delete...

'; - + # this moves the selected tag and its bookmarks to the new one # the tag is still availbale in the DB - + my($delTagName,$moveToTagName) = (param('deleteTag'), param('moveTo')); - + if($delTagName && $moveToTagName && ($delTagName ne $moveToTagName)) { # get the ones with the old tag my $oldTagId = get_tag_id_by_name($delTagName); @@ -114,10 +114,10 @@ sub tag_operations { $sth->execute($moveToTagId,$oldTagId); } } - + print 'Done !'; } - + print '

Rename Tag

'; print '
'; print ''; print ''; print '
'; - + print '

Delete Tag

'; print '
'; print "Delete Tag"; @@ -143,7 +143,7 @@ sub tag_operations { print ''; print ''; print '
'; - + print '

Show bookmarks without a tag

'; print '

Show me the bookmarks

'; } @@ -155,93 +155,90 @@ sub tag_operations { sub show_tags { my ($mode) = shift; if(!defined($mode)) { $mode = 0; } - + my ($sql, $sth); if($mode eq 0) { print "
"; } - # If the user has already chosen a tag, get the intersection list - if(defined(url_param('tag')) && (logged_in() eq 1)) { - $sql = "select $tbl_tags.name,count(*) from $tbl_bookmarks - inner join $tbl_bookmark_tags as bt1 on - ($tbl_bookmarks.id = bt1.bookmark_id) - inner join $tbl_tags on - ($tbl_tags.id = bt1.tag_id) - inner join $tbl_bookmark_tags as bt2 on - ($tbl_bookmarks.id = bt2.bookmark_id) - inner join $tbl_tags as t2 on - (t2.id = bt2.tag_id and t2.name = ?) - where ($tbl_tags.name != ?) - group by $tbl_tags.name"; + my $tag = url_param('tag'); + + #if()) { + # find the tags which have been used with this tags too + my $tagstring = $tag; + chomp($tagstring); + $tagstring =~ s/ /','/g; + + # get the bookmarks first which have those tags + $sql = "SELECT bm.id FROM $tbl_bookmarks as bm + INNER JOIN $tbl_bookmark_tags AS bt ON bm.id = bt.bookmark_id + INNER JOIN $tbl_tags AS t ON t.id = bt.tag_id + WHERE 1"; + $sql .= " AND t.name IN ('$tagstring')" if $tag; + + $sql .= " AND (bm.access_level = 1) " if(logged_in() eq 0); + $sth = $dbh->prepare($sql); - $sth->execute(url_param('tag'), url_param('tag')); + $sth->execute; if($sth->rows ne 0) { - print "
"; - print "'; - print ""; - - while(my @rs = $sth->fetchrow_array()) { - my $tt = url_param('tag'); - my $link = "$tag_url$tt+$rs[0]"; - $tt =~ s/ /\+/g; - - print "\n"; + my @bids; + while(my($id) = $sth->fetchrow_array()) { + push(@bids,$id); } - print "
"; - print "
Add Tag
 ($rs[1]) $rs[0] "; - print "
"; + if(@bids) { + # there are results + # get the tags for the tag list + my $bids = join(',',@bids); - return; - } - } + $sql = "SELECT t.name, count(*) FROM $tbl_bookmarks AS bm + INNER JOIN $tbl_bookmark_tags AS bt + ON (bm.id = bt.bookmark_id) + INNER JOIN $tbl_tags AS t + ON (t.id = bt.tag_id) + WHERE 1"; - # 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 ($tbl_bookmarks.access_level = 1) "; - } + $sql .= " AND bm.id IN ($bids) AND t.name NOT IN ('$tagstring')" if $tag; + $sql .= " AND (bm.access_level = 1) " if(logged_in() eq 0); - my $order_clause; - $order_clause = "order by $tbl_tags.name"; - - $sql = "select $tbl_tags.name, count(*) - from $tbl_bookmarks - inner join $tbl_bookmark_tags on - ($tbl_bookmarks.id = $tbl_bookmark_tags.bookmark_id) - inner join $tbl_tags on - ($tbl_tags.id = $tbl_bookmark_tags.tag_id) - $access_where - group by $tbl_tags.name - $order_clause"; - - $sth = $dbh->prepare($sql); - $sth->execute; - - if($mode eq 0) { - print '
'; - print ''; - print ''; - } - - while(my @r = $sth->fetchrow_array) { - if($mode eq 0) { - print "\n"; - } else { - print ""; + $sql .= " GROUP BY t.name + ORDER BY t.name"; + #print $sql; + $sth = $dbh->prepare($sql); + $sth->execute; + if($sth->rows ne 0) { + + if($mode eq 0) { + print '
'; + print '
'; - print 'Tag List
 ($r[1])"; - print " $r[0] "; - print "
'; + print ''; + } + + + while(my @rs = $sth->fetchrow_array()) { + my $link = $tag_url.$rs[0]; + + if($mode eq 0) { + print ""; + print "\n"; + } else { + print ""; + } + } + + if($mode eq 0) { + print "
Tags
($rs[1])$rs[0]
"; + print ""; + } + + return; + } + } } - } + #} + + - if($mode eq 0) { - print ""; - print ""; - } } # Get a string representing a URLs tags @@ -262,13 +259,13 @@ sub get_tags { # Get a list of the tags for a given URL id sub get_tags_list { my ($url) = (@_); - my $sql = "select $tbl_tags.name from $tbl_tags - inner join $tbl_bookmark_tags on - ($tbl_tags.id = $tbl_bookmark_tags.tag_id) + my $sql = "select $tbl_tags.name from $tbl_tags + inner join $tbl_bookmark_tags on + ($tbl_tags.id = $tbl_bookmark_tags.tag_id) inner join $tbl_bookmarks on ($tbl_bookmark_tags.bookmark_id = $tbl_bookmarks.id) where ($tbl_bookmarks.url = ?)"; - + my $sth = $dbh->prepare($sql); $sth->execute($url); @@ -286,14 +283,14 @@ sub set_tags { my ($bookmark_id, $tag_string) = (@_); check_access(); - + my @tags = split(" ", $tag_string); - + # Clear old tags first. my $sql = "delete from $tbl_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 $tbl_tags where (name = ?)"; @@ -315,7 +312,7 @@ sub set_tags { $sth->execute($cur); my $tid = $sth->fetchrow_array; - $sql = "insert into $tbl_bookmark_tags(bookmark_id, tag_id) + $sql = "insert into $tbl_bookmark_tags(bookmark_id, tag_id) values( ? , ? )"; $sth = $dbh->prepare($sql); $sth->execute($bookmark_id, $tid); @@ -326,9 +323,9 @@ sub set_tags { # if so return the tag id sub get_tag_id_by_name { my ($tag) = (@_); - + my($tagId, $sql, $sth, @result); - + if($tag) { $sql = "select id from `$tbl_tags` where (name = ?)"; $sth = $dbh->prepare($sql); @@ -336,7 +333,7 @@ sub get_tag_id_by_name { @result = $sth->fetchrow_array(); $tagId = $result[0]; } - + return $tagId; } -- 2.39.5