summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-03-14 13:33:45 +0100
committerYorhel <git@yorhel.nl>2009-03-14 13:33:45 +0100
commitf7dc3ee5d6673250304de925088f29da11708b8b (patch)
tree315479c0ef47999785185d2e863a462ba3ed539d
parent351a48f316e5c25a2b6da03c5890c29db11f89ad (diff)
Added tag states for pending/deleted/accepted
-rw-r--r--data/global.pl2
-rw-r--r--data/style.css6
-rw-r--r--lib/VNDB/DB/Tags.pm15
-rw-r--r--lib/VNDB/Handler/Tags.pm37
-rw-r--r--static/f/forms.js6
-rw-r--r--util/updates/update_2.3.sql20
6 files changed, 57 insertions, 29 deletions
diff --git a/data/global.pl b/data/global.pl
index 823d2426..0a67bef8 100644
--- a/data/global.pl
+++ b/data/global.pl
@@ -29,7 +29,7 @@ our %S = (%S,
[qw| visitor hist |], # 0
[qw| banned hist |], # 1
[qw| loser hist board |], # 2
- [qw| user hist board edit tag tagmod |], # 3
+ [qw| user hist board edit tag |], # 3
[qw| mod hist board boardmod edit tag mod lock del tagmod |], # 4
[qw| admin hist board boardmod edit tag mod lock del tagmod usermod |], # 5
],
diff --git a/data/style.css b/data/style.css
index 9386b0fa..a95696ba 100644
--- a/data/style.css
+++ b/data/style.css
@@ -987,8 +987,10 @@ ul#catselect li li.exc { background-position: 0px -33px; color: $statnok$; }
#tagtable .tc3_1 { border-left: 1px solid $border$; width: 150px; text-align: center }
#tagtable .tc1 { min-width: 200px; border-right: 1px solid $border$ }
#tagtable .tc2 i { font-style: normal; font-size: 8px }
-#tagtable .tc3 { border-right: 1px solid $border$ }
-#tagtable .tc5 { text-align: right }
+#tagtable .tc2 { padding-left: 30px!important }
+#tagtable .tc3 { border-right: 1px solid $border$; padding-right: 30px!important; text-align: right }
+#tagtable .tc4 { padding-left: 30px!important; }
+#tagtable .tc5 { text-align: right; padding-right: 30px!important; padding-left: 10px!important }
.taglvl { display: block; float: left; width: 8px; height: 12px; border: 1px solid $border$; font-size: 1px; color: $maintext$!important }
.taglvl0 { width: 15px; border: none!important; font-size: 10px; text-align: center; }
div.taglvl0 { font-size: 8px; width: 20px!important }
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index ff3e57be..4213fd84 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -24,7 +24,10 @@ sub dbTagGet {
my %where = (
$o{id} ? (
- 't.id = ?' => $o{id} ) : (),
+ 't.id = ?' => $o{id}
+ ) : (
+ 't.state <> 1' => 1
+ ),
$o{name} ? (
'lower(t.name) = ?' => lc $o{name} ) : (),
$o{search} ? (
@@ -32,7 +35,7 @@ sub dbTagGet {
);
my($r, $np) = $self->dbPage(\%o, q|
- SELECT t.id, t.meta, t.name, t.alias, t.description, t.c_vns
+ SELECT t.id, t.meta, t.name, t.alias, t.description, t.added, t.state, t.c_vns
FROM tags t
!W
ORDER BY !s|,
@@ -62,11 +65,13 @@ sub dbTagTree {
sub dbTagEdit {
my($self, $id, %o) = @_;
- $self->dbExec('UPDATE tags !H WHERE id = ?',
- { map { +"$_ = ?" => $o{$_} } qw|name meta alias description| }, $id);
+ $self->dbExec('UPDATE tags !H WHERE id = ?', {
+ $o{upddate} ? ('added = ?' => time) : (),
+ map { +"$_ = ?" => $o{$_} } qw|name meta alias description state|
+ }, $id);
$self->dbExec('DELETE FROM tags_parents WHERE tag = ?', $id);
$self->dbExec('INSERT INTO tags_parents (tag, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
- $self->dbExec('DELETE FROM tags_vn WHERE tag = ?', $id) if $o{meta};
+ $self->dbExec('DELETE FROM tags_vn WHERE tag = ?', $id) if $o{meta} || $o{state} == 1;
}
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
index 3dcf80f8..8e4ada7a 100644
--- a/lib/VNDB/Handler/Tags.pm
+++ b/lib/VNDB/Handler/Tags.pm
@@ -35,7 +35,7 @@ sub tagpage {
);
return 404 if $f->{_err};
- my($list, $np) = $t->{meta} ? ([],0) : $self->dbTagVNs(
+ my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->dbTagVNs(
tag => $tag,
order => {score=>'tb.rating',title=>'vr.title',rel=>'v.c_released',pop=>'v.c_popularity'}->{$f->{s}}.($f->{o}eq'a'?' ASC':' DESC'),
page => $f->{p},
@@ -46,6 +46,28 @@ sub tagpage {
my $title = ($t->{meta} ? 'Meta tag: ' : 'Tag: ').$t->{name};
$self->htmlHeader(title => $title);
$self->htmlMainTabs('g', $t);
+
+ if($t->{state} != 2) {
+ div class => 'mainbox';
+ h1 "Tag: $t->{name}";
+ if($t->{state} == 1) {
+ div class => 'warning';
+ h2 'Tag deleted';
+ p;
+ lit qq|This tag has been removed from the database, and cannot be used or re-added.|.
+ qq| File a request on the <a href="/t/db">discussion board</a> if you disagree with this.|;
+ end;
+ end;
+ } else {
+ div class => 'notice';
+ h2 'Waiting for approval';
+ p 'This tag is waiting for a moderator to approve it. You can still use it to tag VNs as you would with a normal tag.';
+ end;
+ }
+ end;
+ return $self->htmlFooter if $t->{state} == 1 && !$self->authCan('tagmod');
+ }
+
div class => 'mainbox';
a class => 'addnew', href => "/g$tag/add", 'Create child tag' if $self->authCan('tagmod');
h1 $title;
@@ -83,7 +105,7 @@ sub tagpage {
end;
_childtags($self, $t) if @{$t->{childs}};
- _vnlist($self, $t, $f, $list, $np) if !$t->{meta};
+ _vnlist($self, $t, $f, $list, $np) if !$t->{meta} && $t->{state} == 2;
$self->htmlFooter;
}
@@ -215,6 +237,7 @@ sub tagedit {
if($self->reqMethod eq 'POST') {
$frm = $self->formValidate(
{ name => 'name', required => 1, maxlength => 250 },
+ { name => 'state', required => 1, enum => [ 0..2 ] },
{ name => 'meta', required => 0, default => 0 },
{ name => 'alias', required => 0, maxlength => 1024, default => '' },
{ name => 'description', required => 0, maxlength => 1024, default => '' },
@@ -227,14 +250,14 @@ sub tagedit {
if(!$frm->{_err}) {
$frm->{meta} = $frm->{meta} ? 1 : 0;
$frm->{parents} = [ split / /, $frm->{parents} ];
- $self->dbTagEdit($tag, %$frm) if $tag;
+ $self->dbTagEdit($tag, %$frm, upddate => $frm->{state} == 2 && $t->{state} != 2) if $tag;
$tag = $self->dbTagAdd(%$frm) if !$tag;
$self->resRedirect("/g$tag", 'post');
}
}
if($tag) {
- $frm->{$_} ||= $t->{$_} for (qw|name meta alias description|);
+ $frm->{$_} ||= $t->{$_} for (qw|name meta alias description state|);
$frm->{parents} ||= join ' ', map $_->{tag}, @{$t->{parents}};
}
@@ -243,9 +266,11 @@ sub tagedit {
$self->htmlMainTabs('g', $par || $t, 'edit') if $t || $par;
$self->htmlForm({ frm => $frm, action => $par ? "/g$par->{id}/add" : $tag ? "/g$tag/edit" : '/g/new' }, $title => [
[ input => short => 'name', name => 'Primary name' ],
+ [ 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)' ],
$tag ?
- [ static => content => 'WARNING: Checking this option will permanently delete all existing VN relations!' ] : (),
+ [ static => content => 'WARNING: Checking this option or selecting "Deleted" as state will permanently delete all existing VN relations!' ] : (),
[ textarea => short => 'alias', name => "Aliases\n(separated by newlines)", cols => 30, rows => 4 ],
[ textarea => short => 'description', name => 'Description' ],
[ static => content => 'What should the tag be used for? Having a good description helps users choose which tags to link to a VN.' ],
@@ -473,7 +498,7 @@ sub tagxml {
xml;
tag 'tags', more => $np ? 'yes' : 'no', query => $q;
for(@$list) {
- tag 'item', id => $_->{id}, meta => $_->{meta} ? 'yes' : 'no', $_->{name};
+ tag 'item', id => $_->{id}, meta => $_->{meta} ? 'yes' : 'no', state => $_->{state}, $_->{name};
}
end;
}
diff --git a/static/f/forms.js b/static/f/forms.js
index e0ec7084..9e795ba6 100644
--- a/static/f/forms.js
+++ b/static/f/forms.js
@@ -876,6 +876,8 @@ function tglLoad() {
td.innerHTML = shorten(item.firstChild.nodeValue, 40);
if(item.getAttribute('meta') == 'yes')
td.innerHTML = '<b class="grayedout">'+td.innerHTML+'</b> (meta)';
+ else if(item.getAttribute('state') == 0)
+ td.innerHTML += ' <b class="grayedout">awaiting moderation</b>';
tr.appendChild(td);
}, function(item) {
return item.firstChild.nodeValue;
@@ -957,10 +959,10 @@ function tglAdd() {
tr.appendChild(td);
td = document.createElement('td');
td.className = 'tc2';
- td.innerHTML = '0.00 (0)';
+ td.innerHTML = '-';
tr.appendChild(td);
td = document.createElement('td');
- td.innerHTML = '0';
+ td.innerHTML = '-';
td.className = 'tc3';
tr.appendChild(td);
td = document.createElement('td');
diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql
index b0662302..46e2f241 100644
--- a/util/updates/update_2.3.sql
+++ b/util/updates/update_2.3.sql
@@ -21,6 +21,8 @@ CREATE TABLE tags (
alias text NOT NULL DEFAULT '',
description text NOT NULL DEFAULT '',
meta boolean NOT NULL DEFAULT FALSE,
+ added bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()),
+ state smallint NOT NULL DEFAULT 0, -- 0: awaiting moderation, 1: deleted, 2: accepted
c_vns integer NOT NULL DEFAULT 0
) WITHOUT OIDS;
@@ -54,7 +56,7 @@ BEGIN
FOR r IN
SELECT lvl, t.id, t.name, t.c_vns
FROM tags t
- WHERE NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id)
+ WHERE state = 2 AND NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id)
ORDER BY t.name
LOOP
RETURN NEXT r;
@@ -70,6 +72,7 @@ BEGIN
FROM tags_parents tp
JOIN tags t ON t.id = tp.tag
WHERE tp.parent = tag
+ AND state = 2
ORDER BY t.name
LOOP
RETURN NEXT r;
@@ -85,6 +88,7 @@ BEGIN
FROM tags_parents tp
JOIN tags t ON t.id = tp.parent
WHERE tp.tag = tag
+ AND state = 2
ORDER BY t.name
LOOP
RETURN NEXT r;
@@ -106,7 +110,7 @@ DECLARE
i RECORD;
l RECORD;
BEGIN
- FOR l IN SElECT id FROM tags WHERE meta = FALSE AND EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
+ FOR l IN SElECT id FROM tags WHERE meta = FALSE AND AND state = 2 EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP
FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP
RETURN NEXT r;
@@ -137,17 +141,7 @@ CREATE OR REPLACE VIEW tags_vn_inherited AS
FROM tags_vn_grouped
GROUP BY tag, vid;
--- bayesian average on the above view, to provide better rankings as to how much a tag applies to a VN
--- details of the calculation @ http://www.thebroth.com/blog/118/bayesian-rating
---CREATE OR REPLACE VIEW tags_vn_bayesian AS
--- SELECT tag, vid, users,
--- ( (SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users*rating )
--- / ( (SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users )::real AS rating,
--- spoiler
--- FROM tags_vn_inherited tvi;
-
-
--- creates/updates a table eqvuivalent to tags_vn_bayesian
+-- creates/updates a table with bayesian rankings of tags
CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
BEGIN
DROP TABLE IF EXISTS tags_vn_stored;