diff options
author | Yorhel <git@yorhel.nl> | 2009-03-14 13:33:45 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-03-14 13:33:45 +0100 |
commit | f7dc3ee5d6673250304de925088f29da11708b8b (patch) | |
tree | 315479c0ef47999785185d2e863a462ba3ed539d | |
parent | 351a48f316e5c25a2b6da03c5890c29db11f89ad (diff) |
Added tag states for pending/deleted/accepted
-rw-r--r-- | data/global.pl | 2 | ||||
-rw-r--r-- | data/style.css | 6 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 15 | ||||
-rw-r--r-- | lib/VNDB/Handler/Tags.pm | 37 | ||||
-rw-r--r-- | static/f/forms.js | 6 | ||||
-rw-r--r-- | util/updates/update_2.3.sql | 20 |
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; |