diff options
-rw-r--r-- | ChangeLog | 1 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 31 | ||||
-rw-r--r-- | util/sql/schema.sql | 1 | ||||
-rw-r--r-- | util/updates/update_2.15.sql | 4 |
4 files changed, 33 insertions, 4 deletions
@@ -3,6 +3,7 @@ a combined view - Added a "general discussions" board - Added vote listings for VNs and users (/[uv]+/votes) + - Keep track of last modification date for tag<->vn links - API: Allow extra whitespace after "get .." command - API: Allow non-numbers as "clientver" for the login command - API: Added "image_nsfw" member to "get vn" diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index bfa56260..93023bc1 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -174,11 +174,34 @@ sub dbTagLinks { # Change a user's tags for a VN entry # Arguments: uid, vid, [ [ tag, vote, spoiler ], .. ] sub dbTagLinkEdit { - my($self, $uid, $vid, $tags) = @_; - $self->dbExec('DELETE FROM tags_vn WHERE vid = ? AND uid = ?', $vid, $uid); + my($self, $uid, $vid, $new) = @_; + + # compare with the old votes and determine which to delete, and/or insert + my %old = map +($_->{tag}, [ $_->{vote}, $_->{spoiler} // -1 ]), + @{$self->dbTagLinks(vid => $vid, uid => $self->authInfo->{id})}; + my %new = map +($_->[0], [ $_->[1], $_->[2] ]), @$new; + + my(%delete, %update, %insert); + for my $tag (keys %old, keys %new) { + if($old{$tag} && !$new{$tag}) { + $delete{$tag} = 1; + } elsif(!$old{$tag} && $new{$tag}) { + $insert{$tag} = $new{$tag}; + } elsif($old{$tag}[0] != $new{$tag}[0] || $old{$tag}[1] != $new{$tag}[1]) { + $update{$tag} = $new{$tag}; + } + } + + # spoiler '-1' -> NULL + $new{$_}[1] == -1 && ($new{$_}[1] = undef) for keys %new; + + # perform the changes + $self->dbExec('DELETE FROM tags_vn WHERE vid = ? AND uid = ? AND tag IN(!l)', + $vid, $uid, [ keys %delete ]) if keys %delete; $self->dbExec('INSERT INTO tags_vn (tag, vid, uid, vote, spoiler) VALUES (?, ?, ?, ?, ?)', - $_->[0], $vid, $uid, $_->[1], $_->[2] == -1 ? undef : $_->[2] - ) for (@$tags); + $_, $vid, $uid, $insert{$_}[0], $insert{$_}[1]) for (keys %insert); + $self->dbExec('UPDATE tags_vn SET vote = ?, spoiler = ?, date = NOW() WHERE tag = ? AND vid = ? AND uid = ?', + $update{$_}[0], $update{$_}[1], $_, $vid, $uid) for (keys %update); } diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 6911a40b..48367e4c 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -217,6 +217,7 @@ CREATE TABLE tags_vn ( uid integer NOT NULL, vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0), spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2), + date timestamptz NOT NULL DEFAULT NOW(), PRIMARY KEY(tag, vid, uid) ); diff --git a/util/updates/update_2.15.sql b/util/updates/update_2.15.sql new file mode 100644 index 00000000..b35b7636 --- /dev/null +++ b/util/updates/update_2.15.sql @@ -0,0 +1,4 @@ + + +ALTER TABLE tags_vn ADD COLUMN date timestamptz NOT NULL DEFAULT NOW(); + |