summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--ChangeLog1
-rw-r--r--lib/VNDB/DB/Tags.pm31
-rw-r--r--util/sql/schema.sql1
-rw-r--r--util/updates/update_2.15.sql4
4 files changed, 33 insertions, 4 deletions
diff --git a/ChangeLog b/ChangeLog
index de37331d..7008fb77 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -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();
+