Keep track of users who requested tags

5 files changed, 24 insertions, 7 deletions

diff --git a/ChangeLog b/ChangeLog
index 4ccddca8..33ca8481 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -8,6 +8,7 @@
 	- Separated VN search filters from search box
 	- Tag filers on VN search
 	- Posts browser on user pages
+	- Keep track of the user who created a tag
 
 2.4 - 2009-06-07
 	- Release search + browser + filters
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index 280f3f3e..3e095d3c 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -9,7 +9,7 @@ our @EXPORT = qw|dbTagGet dbTagTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbT
 
 
 # %options->{ id noid name search state meta page results order what }
-# what: parents childs(n) aliases
+# what: parents childs(n) aliases addedby
 sub dbTagGet {
   my $self = shift;
   my %o = (
@@ -38,13 +38,19 @@ sub dbTagGet {
     defined $o{meta} ? (
       't.meta = ?' => $o{meta}?1:0 ) : (),
   );
+  my @select = (
+    qw|t.id t.meta t.name t.description t.added t.state t.c_vns|,
+    $o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (),
+  );
+  my @join = $o{what} =~ /addedby/ ? 'JOIN users u ON u.id = t.addedby' : ();
 
   my($r, $np) = $self->dbPage(\%o, q|
-    SELECT t.id, t.meta, t.name, t.description, t.added, t.state, t.c_vns
+    SELECT !s
       FROM tags t
+      !s
       !W
       ORDER BY !s|,
-    \%where, $o{order}
+    join(', ', @select), join(' ', @join), \%where, $o{order}
   );
 
   if(@$r && $o{what} =~ /aliases/) {
@@ -97,8 +103,8 @@ sub dbTagEdit {
 # returns the id of the new tag
 sub dbTagAdd {
   my($self, %o) = @_;
-  my $id = $self->dbRow('INSERT INTO tags (name, meta, description, state) VALUES (!l) RETURNING id',
-    [ map $o{$_}, qw|name meta description state| ]
+  my $id = $self->dbRow('INSERT INTO tags (name, meta, description, state, addedby) VALUES (!l, ?) RETURNING id',
+    [ map $o{$_}, qw|name meta description state| ], $o{addedby}||$self->authInfo->{id}
   )->{id};
   $self->dbExec('INSERT INTO tags_parents (tag, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
   $self->dbExec('INSERT INTO tags_aliases (tag, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}});
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
index a6d02698..6471d44f 100644
--- a/lib/VNDB/Handler/Tags.pm
+++ b/lib/VNDB/Handler/Tags.pm
@@ -238,7 +238,7 @@ sub tagedit {
 
   return $self->htmlDenied if !$self->authCan('tag') || $tag && !$self->authCan('tagmod');
 
-  my $t = $tag && $self->dbTagGet(id => $tag, what => 'parents(1) aliases')->[0];
+  my $t = $tag && $self->dbTagGet(id => $tag, what => 'parents(1) aliases addedby')->[0];
   return 404 if $tag && !$t;
 
   if($self->reqMethod eq 'POST') {
@@ -319,6 +319,8 @@ sub tagedit {
   $self->htmlForm({ frm => $frm, action => $par ? "/g$par->{id}/add" : $tag ? "/g$tag/edit" : '/g/new' }, $title => [
     [ input    => short => 'name',     name => 'Primary name' ],
     $self->authCan('tagmod') ? (
+      $tag ?
+        [ static   => label => 'Added by', content => sub { a href => "/u$t->{addedby}", $t->{username}; } ] : (),
       [ select   => short => 'state',    name => 'State', options => [
         [ 0, 'Awaiting moderation' ], [ 1, 'Deleted/hidden' ], [ 2, 'Approved' ] ] ],
       [ checkbox => short => 'meta',     name => 'This is a meta-tag (only to be used as parent for other tags, not for linking to VN entries)' ],
diff --git a/util/dump.sql b/util/dump.sql
index 6fe4bc08..22e80099 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -175,7 +175,8 @@ CREATE TABLE tags (
   meta boolean NOT NULL DEFAULT FALSE,
   added bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()),
   state smallint NOT NULL DEFAULT 0,
-  c_vns integer NOT NULL DEFAULT 0
+  c_vns integer NOT NULL DEFAULT 0,
+  addedby integer NOT NULL DEFAULT 1
 );
 
 -- tags_aliases
@@ -375,6 +376,7 @@ ALTER TABLE releases_vn        ADD FOREIGN KEY (rid)       REFERENCES releases_r
 ALTER TABLE releases_vn        ADD FOREIGN KEY (vid)       REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE rlists             ADD FOREIGN KEY (uid)       REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE rlists             ADD FOREIGN KEY (rid)       REFERENCES releases      (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags               ADD FOREIGN KEY (addedby)   REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE tags_aliases       ADD FOREIGN KEY (tag)       REFERENCES tags          (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE tags_parents       ADD FOREIGN KEY (tag)       REFERENCES tags          (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE tags_parents       ADD FOREIGN KEY (parent)    REFERENCES tags          (id) DEFERRABLE INITIALLY DEFERRED;
diff --git a/util/updates/update_2.5.sql b/util/updates/update_2.5.sql
index dab07608..b45da7b0 100644
--- a/util/updates/update_2.5.sql
+++ b/util/updates/update_2.5.sql
@@ -59,3 +59,9 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql;
 
+
+
+-- added by field for tags
+
+ALTER TABLE tags ADD COLUMN addedby integer NOT NULL DEFAULT 1 REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
+