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