[2] | 1 | package Bookmarks; |
---|
| 2 | |
---|
[15] | 3 | use Moose; |
---|
[9] | 4 | use SQL::Interp qw{:all}; |
---|
[24] | 5 | use URI; |
---|
[2] | 6 | use Bookmark; |
---|
| 7 | |
---|
[15] | 8 | has dbh => ( is => 'rw' ); |
---|
[24] | 9 | has base_uri => ( is => 'ro', isa => 'URI' ); |
---|
[2] | 10 | |
---|
[15] | 11 | sub BUILD { |
---|
| 12 | my $self = shift; |
---|
| 13 | my $args = shift; |
---|
| 14 | |
---|
| 15 | if (!$self->dbh) { |
---|
| 16 | if ($args->{dbname}) { |
---|
| 17 | require DBI; |
---|
| 18 | $self->dbh(DBI->connect("dbi:SQLite:dbname=$$args{dbname}", "", "", { RaiseError => 1, PrintError => 0 })); |
---|
| 19 | # enable foreign key support (requires DBD::SQLite 1.26_05 or above (sqlite 3.6.19 or above)) |
---|
| 20 | $self->dbh->do('pragma foreign_keys = on;'); |
---|
| 21 | } else { |
---|
| 22 | #TODO: figure out how to make croak play nice with Moose to get the user-visible caller line |
---|
| 23 | die "No dbh or dbname specified in the constructor"; |
---|
| 24 | } |
---|
| 25 | } |
---|
| 26 | } |
---|
| 27 | |
---|
[2] | 28 | sub get_bookmark { |
---|
| 29 | my $self = shift; |
---|
| 30 | my $params = shift; |
---|
| 31 | my $sth; |
---|
| 32 | if ($params->{id}) { |
---|
| 33 | $sth = $self->dbh->prepare('select id,resources.uri,title,ctime,mtime from bookmarks join resources on bookmarks.uri=resources.uri where id=?'); |
---|
| 34 | $sth->execute($params->{id}); |
---|
| 35 | } elsif ($params->{uri}) { |
---|
| 36 | $sth = $self->dbh->prepare('select id,resources.uri,title,ctime,mtime from bookmarks join resources on bookmarks.uri=resources.uri where resources.uri=?'); |
---|
| 37 | $sth->execute($params->{uri}); |
---|
| 38 | } else { |
---|
| 39 | die "Must specify either id or uri"; |
---|
| 40 | } |
---|
| 41 | my $bookmark = $sth->fetchrow_hashref; |
---|
| 42 | if ($bookmark) { |
---|
| 43 | my $sth_tag = $self->dbh->prepare('select tag from tags where uri = ? order by tag'); |
---|
| 44 | $sth_tag->execute($bookmark->{uri}); |
---|
| 45 | $bookmark->{tags} = [ map { $$_[0] } @{ $sth_tag->fetchall_arrayref } ]; |
---|
[7] | 46 | if ($self->base_uri) { |
---|
[24] | 47 | $bookmark->{bookmark_uri} = URI->new_abs($bookmark->{id}, $self->base_uri); |
---|
[7] | 48 | } |
---|
[2] | 49 | } |
---|
| 50 | return $bookmark; |
---|
| 51 | } |
---|
| 52 | |
---|
| 53 | sub get_resources { |
---|
| 54 | my $self = shift; |
---|
| 55 | my $params = shift; |
---|
[20] | 56 | my $tags = $params->{tag} || []; |
---|
[9] | 57 | my $limit = $params->{limit}; |
---|
[13] | 58 | my $offset = $params->{offset}; |
---|
[2] | 59 | |
---|
[20] | 60 | # build the query |
---|
| 61 | my @sql; |
---|
[9] | 62 | |
---|
[20] | 63 | if (!ref $tags) { |
---|
| 64 | $tags = [ $tags ]; |
---|
| 65 | } |
---|
| 66 | if (@$tags) { |
---|
| 67 | my $intersect = 0; |
---|
| 68 | for my $tag (@{ $tags }) { |
---|
| 69 | push @sql, 'intersect' if $intersect; |
---|
| 70 | push @sql, 'select resources.*, bookmarks.* from resources join bookmarks on resources.uri = bookmarks.uri'; |
---|
| 71 | push @sql, 'join tags on resources.uri = tags.uri where tags.tag =', \$tag; |
---|
| 72 | $intersect++; |
---|
| 73 | } |
---|
| 74 | } else { |
---|
| 75 | push @sql, 'select * from resources join bookmarks on resources.uri = bookmarks.uri'; |
---|
| 76 | } |
---|
| 77 | push @sql, 'order by ctime desc'; |
---|
| 78 | push @sql, ('limit', \$limit) if $limit; |
---|
| 79 | # an offset is only allowed if we have a limit clause |
---|
| 80 | push @sql, ('offset', \$offset) if $limit && $offset; |
---|
| 81 | |
---|
| 82 | my ($sql, @bind) = sql_interp(@sql); |
---|
| 83 | |
---|
[9] | 84 | my $sth_resource = $self->dbh->prepare($sql); |
---|
| 85 | $sth_resource->execute(@bind); |
---|
| 86 | |
---|
[2] | 87 | my $sth_tag = $self->dbh->prepare('select tag from tags where uri = ? order by tag'); |
---|
| 88 | my @resources; |
---|
| 89 | while (my $resource = $sth_resource->fetchrow_hashref) { |
---|
| 90 | $sth_tag->execute($resource->{uri}); |
---|
| 91 | $resource->{tags} = [ map { $$_[0] } @{ $sth_tag->fetchall_arrayref } ]; |
---|
[7] | 92 | if ($self->base_uri) { |
---|
[24] | 93 | $resource->{bookmark_uri} = URI->new_abs($resource->{id}, $self->base_uri); |
---|
[7] | 94 | } |
---|
[2] | 95 | push @resources, $resource; |
---|
| 96 | } |
---|
| 97 | return @resources; |
---|
| 98 | } |
---|
| 99 | |
---|
| 100 | sub get_tags { |
---|
| 101 | my $self = shift; |
---|
| 102 | my $params = shift; |
---|
| 103 | my $tag = $params->{selected}; |
---|
| 104 | my $sth_all_tags = $self->dbh->prepare('select tag, count(tag) as count, tag = ? as selected from tags group by tag order by tag'); |
---|
| 105 | $sth_all_tags->execute($tag); |
---|
| 106 | my $all_tags = $sth_all_tags->fetchall_arrayref({}); |
---|
| 107 | return @{ $all_tags }; |
---|
| 108 | } |
---|
| 109 | |
---|
| 110 | sub get_cotags { |
---|
| 111 | my $self = shift; |
---|
| 112 | my $params = shift; |
---|
[20] | 113 | my $tags = $params->{tag} || []; |
---|
| 114 | if (!ref $tags) { |
---|
| 115 | $tags = [ $tags ]; |
---|
| 116 | } |
---|
| 117 | my @sql; |
---|
| 118 | |
---|
| 119 | push @sql, 'select tag, count(tag) as count from tags'; |
---|
| 120 | if (@$tags) { |
---|
| 121 | push @sql, 'where uri in ('; |
---|
| 122 | my $intersect = 0; |
---|
| 123 | for my $tag (@{ $tags }) { |
---|
| 124 | push @sql, 'intersect' if $intersect; |
---|
| 125 | push @sql, 'select uri from tags where tag = ', \$tag; |
---|
| 126 | $intersect++; |
---|
| 127 | } |
---|
| 128 | push @sql, ') and tag not in ', $tags, ''; |
---|
| 129 | } |
---|
| 130 | push @sql, 'group by tag order by tag'; |
---|
| 131 | |
---|
| 132 | my ($sql, @bind) = sql_interp(@sql); |
---|
| 133 | my $sth = $self->dbh->prepare($sql); |
---|
| 134 | $sth->execute(@bind); |
---|
[2] | 135 | return @{ $sth->fetchall_arrayref({}) }; |
---|
| 136 | } |
---|
| 137 | |
---|
| 138 | sub add { |
---|
| 139 | my $self = shift; |
---|
| 140 | my $bookmark = shift; |
---|
| 141 | |
---|
| 142 | my $uri = $bookmark->{uri}; |
---|
| 143 | my $title = $bookmark->{title}; |
---|
| 144 | #TODO: accept a ctime or mtime |
---|
[15] | 145 | my $ctime = $bookmark->{ctime} || time; |
---|
| 146 | my $mtime = $bookmark->{mtime} || $ctime; |
---|
| 147 | my $id = $bookmark->{id}; |
---|
[2] | 148 | |
---|
| 149 | # create an entry for the resource |
---|
| 150 | my $sth_resource = $self->dbh->prepare('insert into resources (uri, title) values (?, ?)'); |
---|
| 151 | eval { |
---|
| 152 | $sth_resource->execute($uri, $title); |
---|
| 153 | }; |
---|
| 154 | if ($@) { |
---|
| 155 | if ($@ =~ /column uri is not unique/) { |
---|
| 156 | # this is not truly an error condition; the resource is already listed |
---|
| 157 | # update the title instead |
---|
| 158 | my $sth_update = $self->dbh->prepare('update resources set title = ? where uri = ?'); |
---|
| 159 | $sth_update->execute($title, $uri); |
---|
| 160 | } else { |
---|
| 161 | die $@; |
---|
| 162 | } |
---|
| 163 | } |
---|
| 164 | |
---|
| 165 | # create the bookmark |
---|
[15] | 166 | my ($sql_bookmark, @bind_bookmark) = sql_interp( |
---|
| 167 | 'insert into bookmarks', { ($id ? (id => $id) : ()), uri => $uri, ctime => $ctime, mtime => $mtime } |
---|
| 168 | ); |
---|
| 169 | my $sth_bookmark = $self->dbh->prepare($sql_bookmark); |
---|
[2] | 170 | eval { |
---|
[15] | 171 | $sth_bookmark->execute(@bind_bookmark); |
---|
[2] | 172 | }; |
---|
| 173 | if ($@) { |
---|
| 174 | if ($@ =~ /column uri is not unique/) { |
---|
| 175 | # this is not truly an error condition; the bookmark was already there |
---|
| 176 | # update the mtime instead |
---|
| 177 | # TODO: only update mtime if the tag list is changed? |
---|
| 178 | my $sth_update = $self->dbh->prepare('update bookmarks set mtime = ? where uri = ?'); |
---|
| 179 | $sth_update->execute($mtime, $uri); |
---|
| 180 | } else { |
---|
| 181 | die $@; |
---|
| 182 | } |
---|
| 183 | } |
---|
| 184 | |
---|
| 185 | my %new_tags = map { $_ => 1 } @{ $bookmark->{tags} }; |
---|
| 186 | my $sth_delete_tag = $self->dbh->prepare('delete from tags where uri = ? and tag = ?'); |
---|
| 187 | my $sth_insert_tag = $self->dbh->prepare('insert into tags (uri, tag) values (?, ?)'); |
---|
| 188 | my $sth_current_tags = $self->dbh->prepare('select tag from tags where uri = ?'); |
---|
| 189 | $sth_current_tags->execute($uri); |
---|
| 190 | while (my ($tag) = $sth_current_tags->fetchrow_array) { |
---|
| 191 | if (!$new_tags{$tag}) { |
---|
| 192 | # if a current tag is not in the new tags, remove it from the database |
---|
| 193 | $sth_delete_tag->execute($uri, $tag); |
---|
| 194 | } else { |
---|
| 195 | # if a new tag is already in the database, remove it from the list of tags to add |
---|
| 196 | delete $new_tags{$tag}; |
---|
| 197 | } |
---|
| 198 | } |
---|
| 199 | for my $tag (keys %new_tags) { |
---|
| 200 | $sth_insert_tag->execute($uri, $tag); |
---|
| 201 | } |
---|
| 202 | |
---|
| 203 | =begin |
---|
| 204 | |
---|
| 205 | # clear all tags |
---|
| 206 | my $sth_delete_tag = $self->dbh->prepare('delete from tags where uri = ?'); |
---|
| 207 | $sth_delete_tag->execute($uri); |
---|
| 208 | my $sth_tag = $self->dbh->prepare('insert into tags (uri, tag) values (?, ?)'); |
---|
| 209 | for my $tag (@{ $bookmark->{tags} }) { |
---|
| 210 | #print $tag, "\n"; |
---|
| 211 | # prevent duplicate (uri,tag) pairs in the database |
---|
| 212 | # TODO: should POST with a set of tags ever remove tags? |
---|
| 213 | eval { |
---|
| 214 | $sth_tag->execute($uri, $tag); |
---|
| 215 | }; |
---|
| 216 | if ($@) { |
---|
| 217 | if ($@ =~ /columns uri, tag are not unique/) { |
---|
| 218 | # this is not truly an error condition; the tag was already there |
---|
| 219 | } else { |
---|
| 220 | die $@; |
---|
| 221 | } |
---|
| 222 | } |
---|
| 223 | } |
---|
| 224 | |
---|
| 225 | =cut |
---|
| 226 | |
---|
| 227 | # return the newly created or updated bookmark |
---|
| 228 | return $self->get_bookmark({ uri => $uri }); |
---|
| 229 | } |
---|
| 230 | |
---|
| 231 | # module returns true |
---|
| 232 | 1; |
---|