summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--ChangeLog1
-rw-r--r--Makefile14
-rw-r--r--data/lang.txt58
-rw-r--r--lib/VNDB/DB/Misc.pm24
-rw-r--r--lib/VNDB/DB/Producers.pm2
-rw-r--r--lib/VNDB/DB/Releases.pm2
-rw-r--r--lib/VNDB/DB/VN.pm2
-rw-r--r--lib/VNDB/Handler/Misc.pm16
-rw-r--r--lib/VNDB/Handler/Producers.pm7
-rw-r--r--lib/VNDB/Handler/Releases.pm8
-rw-r--r--lib/VNDB/Handler/VNEdit.pm9
-rw-r--r--lib/VNDB/Util/CommonHTML.pm26
-rw-r--r--lib/VNDB/Util/FormHTML.pm14
-rw-r--r--util/sql/all.sql4
-rw-r--r--util/sql/func.sql42
-rw-r--r--util/sql/schema.sql4
-rw-r--r--util/updates/update_2.11.sql54
17 files changed, 189 insertions, 98 deletions
diff --git a/ChangeLog b/ChangeLog
index c70bab5a..5148892e 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -3,6 +3,7 @@ git - ?
- Centered the thumbnails on the screenshots viewer
- Improved date selector
- Made the release date a required field
+ - Versioned the deleting and locking of database entries
2.10 - 2010-01-10
- VN score on tag pages use plain averages instead of bayesian rating
diff --git a/Makefile b/Makefile
index 873ae1f5..9ea395df 100644
--- a/Makefile
+++ b/Makefile
@@ -29,15 +29,15 @@
# sql-import
# Imports util/sql/all.sql into your (presumably empty) database
#
-# update-2.10
-# Updates all non-versioned items to 2.10
+# update-<version>
+# Updates all non-versioned items from the version before to <version>.
#
# NOTE: This Makefile has only been tested using a recent version of GNU make
# in a relatively up-to-date Arch Linux environment, and may not work in other
# environments. Patches to improve the portability are always welcome.
-.PHONY: all dirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart sql-import update-2.10
+.PHONY: all dirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart sql-import update-2.10 update-2.11
all: dirs js skins robots data/config.pl
@@ -109,7 +109,7 @@ multi-restart:
# Small perl script that tries to connect to the PostgreSQL database using 'psql', with the
# connection settings from data/config.pl. May not work in all configurations, though...
define runpsql
- perl -MDBI -e 'package VNDB;\
+ @perl -MDBI -e 'package VNDB;\
$$ROOT=".";\
require "data/global.pl";\
$$_=(DBI->parse_dsn($$VNDB::O{db_login}[0]))[4];\
@@ -133,7 +133,11 @@ sql-import:
update-2.10: all
$(multi-stop)
- @${runpsql} < util/updates/update_2.10.sql
+ ${runpsql} < util/updates/update_2.10.sql
$(multi-start)
+update-2.11: all
+ $(multi-stop)
+ ${runpsql} < util/updates/update_2.11.sql
+ $(multi-start)
diff --git a/data/lang.txt b/data/lang.txt
index 86110cab..624cb916 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -1208,6 +1208,24 @@ ru : Отправить
cs : Potvrdit
hu : Beküldés
+:_form_ihid
+en : Deleted
+ru*:
+cs*:
+hu*:
+
+:_form_ilock
+en : Locked
+ru*:
+cs*:
+hu*:
+
+:_form_hidlock_note
+en : Note: edit summary of the last edit should indicate the reason for the deletion or lock.
+ru*:
+cs*:
+hu*:
+
# Common javascript strings
@@ -1585,32 +1603,6 @@ ru : правка
cs : upravit
hu : szerkesztés
-# hide/unhide a DB item
-:_mtabs_hide
-en : hide
-ru : скрыть
-cs : skrýt
-hu : elrejtés
-
-:_mtabs_unhide
-en : unhide
-ru : показать
-cs : odkrýt
-hu : megjelenítés
-
-# lock/unlock for editing
-:_mtabs_lock
-en : lock
-ru : заблокировать
-cs : zamknout
-hu : bezárás
-
-:_mtabs_unlock
-en : unlock
-ru : разблокировать
-cs : odemknout
-hu : kinyitás
-
# delete
:_mtabs_del
en : del
@@ -1704,6 +1696,18 @@ ru : Нет
cs*:
hu : Nem
+:_revfield_ihid
+en : Deleted
+ru*:
+cs*:
+hu*:
+
+:_revfield_ilock
+en : Locked
+ru*:
+cs*:
+hu*:
+
# tabs above the search boxes
@@ -5718,7 +5722,7 @@ cs : Položka smazána
hu : Tárgy törölve
:_hiddenmsg_msg
-en : This item has been deleted from the database, File a request on the
+en : This item has been deleted from the database. File a request on the
[url,_1,discussion board] to undelete this page.
ru : Данная запись удалена из базы данных. Пожалуйста, подайте заявку на
[url,_1,форуме] для восстановления этой страницы.
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index 02df0a2b..e3ef69b1 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -6,7 +6,7 @@ use warnings;
use Exporter 'import';
our @EXPORT = qw|
- dbStats dbItemEdit dbRevisionGet dbItemMod dbRandomQuote
+ dbStats dbItemEdit dbRevisionGet dbRandomQuote
|;
@@ -21,7 +21,7 @@ sub dbStats {
# Inserts a new revision into the database
-# Arguments: type [vrp], revision id, %options->{ editsum uid + db[item]RevisionInsert }
+# Arguments: type [vrp], revision id, %options->{ editsum uid ihid ilock + db[item]RevisionInsert }
# revision id = changes.id of the revision this edit is based on, undef to create a new DB item
# Returns: { iid, cid, rev }
sub dbItemEdit {
@@ -29,8 +29,13 @@ sub dbItemEdit {
my $fun = {qw|v vn r release p producer|}->{$type};
$self->dbExec('SELECT edit_!s_init(?)', $fun, $oid);
- $self->dbExec('UPDATE edit_revision SET requester = ?, ip = ?, comments = ?',
- $o{uid}||$self->authInfo->{id}, $self->reqIP, $o{editsum});
+ $self->dbExec('UPDATE edit_revision !H', {
+ 'requester = ?' => $o{uid}||$self->authInfo->{id},
+ 'ip = ?' => $self->reqIP,
+ 'comments = ?' => $o{editsum},
+ exists($o{ihid}) ? ('ihid = ?' => $o{ihid} ?1:0) : (),
+ exists($o{ilock}) ? ('ilock = ?' => $o{ilock}?1:0) : (),
+ });
$self->dbVNRevisionInsert( \%o) if $type eq 'v';
$self->dbProducerRevisionInsert(\%o) if $type eq 'p';
@@ -112,17 +117,6 @@ sub dbRevisionGet {
}
-# Lock or hide a DB item
-# arguments: v/r/p, id, %options ->( hidden, locked )
-sub dbItemMod {
- my($self, $type, $id, %o) = @_;
- $self->dbExec('UPDATE !s !H WHERE id = ?',
- {qw|v vn r releases p producers|}->{$type},
- { map { ($_.' = ?', int $o{$_}) } keys %o }, $id
- );
-}
-
-
# Returns a random quote (hashref with keys = vid, quote)
sub dbRandomQuote {
return $_[0]->dbRow(q|
diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm
index f32f70db..5ec387cf 100644
--- a/lib/VNDB/DB/Producers.pm
+++ b/lib/VNDB/DB/Producers.pm
@@ -44,7 +44,7 @@ sub dbProducerGet {
my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang, pr.id AS cid, p.rgraph';
$select .= ', pr.desc, pr.alias, pr.website, pr.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/;
- $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev| if $o{what} =~ /changes/;
+ $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev, c.ihid, c.ilock| if $o{what} =~ /changes/;
$select .= ', pg.svg' if $o{what} =~ /relgraph/;
my($r, $np) = $self->dbPage(\%o, q|
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index a2f62a63..cb8ad006 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -74,7 +74,7 @@ sub dbReleaseGet {
'rr.id AS cid',
$o{what} =~ /extended/ ? qw|rr.notes rr.catalog rr.gtin rr.resolution rr.voiced rr.freeware rr.doujin rr.ani_story rr.ani_ero r.hidden r.locked| : (),
$o{what} =~ /changes/ ?
- (qw|c.requester c.comments r.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (),
+ (qw|c.requester c.comments r.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (),
);
my $order = sprintf {
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index b8cc0c42..166f4bbe 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -86,7 +86,7 @@ sub dbVNGet {
$o{what} =~ /extended/ ? (
qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (),
$o{what} =~ /changes/ ? (
- qw|c.requester c.comments v.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (),
+ qw|c.requester c.comments v.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (),
$o{what} =~ /relgraph/ ? 'vg.svg' : (),
$o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (),
$o{what} =~ /ranking/ ? (
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 625b0463..820f34cd 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -14,7 +14,6 @@ YAWF::register(
qr{(?:([upvr])([1-9]\d*)/)?hist}, \&history,
qr{d([1-9]\d*)}, \&docpage,
qr{nospam}, \&nospam,
- qr{([vrp])([1-9]\d*)/(lock|hide)}, \&itemmod,
qr{we-dont-like-ie6}, \&ie6message,
qr{opensearch\.xml}, \&opensearch,
@@ -336,21 +335,6 @@ sub nospam {
}
-# /hide and /lock for v/r/p+ pages
-sub itemmod {
- my($self, $type, $iid, $act) = @_;
- return $self->htmlDenied if !$self->authCan($act eq 'hide' ? 'del' : 'lock');
-
- my $obj = $type eq 'v' ? $self->dbVNGet(id => $iid)->[0] :
- $type eq 'r' ? $self->dbReleaseGet(id => $iid, what => 'extended')->[0] :
- $self->dbProducerGet(id => $iid, what => 'extended')->[0];
- return 404 if !$obj->{id};
-
- $self->dbItemMod($type, $iid, $act eq 'hide' ? (hidden => !$obj->{hidden}) : (locked => !$obj->{locked}));
- $self->resRedirect("/$type$iid", 'temp');
-}
-
-
sub ie6message {
my $self = shift;
diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm
index 064a06bc..b1203dd7 100644
--- a/lib/VNDB/Handler/Producers.pm
+++ b/lib/VNDB/Handler/Producers.pm
@@ -155,7 +155,7 @@ sub edit {
|| $pid && ($p->{locked} && !$self->authCan('lock') || $p->{hidden} && !$self->authCan('del'));
my %b4 = !$pid ? () : (
- (map { $_ => $p->{$_} } qw|type name original lang website desc alias|),
+ (map { $_ => $p->{$_} } qw|type name original lang website desc alias ihid ilock|),
l_wp => $p->{l_wp} || '',
prodrelations => join('|||', map $_->{relation}.','.$_->{id}.','.$_->{name}, sort { $a->{id} <=> $b->{id} } @{$p->{relations}}),
);
@@ -173,12 +173,17 @@ sub edit {
{ name => 'desc', required => 0, maxlength => 5000, default => '' },
{ name => 'prodrelations', required => 0, maxlength => 5000, default => '' },
{ name => 'editsum', maxlength => 5000 },
+ { name => 'ihid', required => 0 },
+ { name => 'ilock', required => 0 },
);
if(!$frm->{_err}) {
# parse
my $relations = [ map { /^([a-z]+),([0-9]+),(.+)$/ && (!$pid || $2 != $pid) ? [ $1, $2, $3 ] : () } split /\|\|\|/, $frm->{prodrelations} ];
# normalize
+ $frm->{ihid} = $frm->{ihid}?1:0;
+ $frm->{ilock} = $frm->{ilock}?1:0;
+ $relations = [] if $frm->{ihid};
$frm->{prodrelations} = join '|||', map $_->[0].','.$_->[1].','.$_->[2], sort { $a->[1] <=> $b->[1]} @{$relations};
return $self->resRedirect("/p$pid", 'post')
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index eb6ea531..55bc9778 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -287,7 +287,7 @@ sub edit {
my $vn = $rid ? $r->{vn} : [{ vid => $vid, title => $v->{title} }];
my %b4 = !$rid ? () : (
(map { $_ => $r->{$_} } qw|type title original gtin catalog languages website released
- notes platforms patch resolution voiced freeware doujin ani_story ani_ero|),
+ notes platforms patch resolution voiced freeware doujin ani_story ani_ero ihid ilock|),
minage => defined($r->{minage}) ? $r->{minage} : -1,
media => join(',', sort map "$_->{medium} $_->{qty}", @{$r->{media}}),
producers => join('|||', map
@@ -323,6 +323,8 @@ sub edit {
{ name => 'producers', required => 0, default => '' },
{ name => 'vn', maxlength => 5000 },
{ name => 'editsum', maxlength => 5000 },
+ { name => 'ihid', required => 0 },
+ { name => 'ilock', required => 0 },
);
push @{$frm->{_err}}, [ 'released', 'required', 1 ] if !$frm->{released};
@@ -334,7 +336,7 @@ sub edit {
$producers = [ map { /^([0-9]+),([1-3])/ ? [ $1, $2&1?1:0, $2&2?1:0] : () } split /\|\|\|/, $frm->{producers} ];
$new_vn = [ map { /^([0-9]+)/ ? $1 : () } split /\|\|\|/, $frm->{vn} ];
$frm->{platforms} = [ grep $_, @{$frm->{platforms}} ];
- $frm->{$_} = $frm->{$_} ? 1 : 0 for (qw|patch freeware doujin|);
+ $frm->{$_} = $frm->{$_} ? 1 : 0 for (qw|patch freeware doujin ihid ilock|);
# reset some fields when the patch flag is set
$frm->{doujin} = $frm->{resolution} = $frm->{voiced} = $frm->{ani_story} = $frm->{ani_ero} = 0 if $frm->{patch};
@@ -352,7 +354,7 @@ sub edit {
if(!$frm->{_err}) {
my $nrev = $self->dbItemEdit(r => !$copy && $rid ? $r->{cid} : undef,
(map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released
- notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|),
+ notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero ihid ilock|),
minage => $frm->{minage} < 0 ? undef : $frm->{minage},
vn => $new_vn,
producers => $producers,
diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm
index d5dfd878..60b24272 100644
--- a/lib/VNDB/Handler/VNEdit.pm
+++ b/lib/VNDB/Handler/VNEdit.pm
@@ -26,7 +26,7 @@ sub edit {
|| $vid && ($v->{locked} && !$self->authCan('lock') || $v->{hidden} && !$self->authCan('del'));
my %b4 = !$vid ? () : (
- (map { $_ => $v->{$_} } qw|title original desc alias length l_wp l_encubed l_renai l_vnn img_nsfw|),
+ (map { $_ => $v->{$_} } qw|title original desc alias length l_wp l_encubed l_renai l_vnn img_nsfw ihid ilock|),
anime => join(' ', sort { $a <=> $b } map $_->{id}, @{$v->{anime}}),
vnrelations => join('|||', map $_->{relation}.','.$_->{id}.','.$_->{title}, sort { $a->{id} <=> $b->{id} } @{$v->{relations}}),
screenshots => join(' ', map sprintf('%d,%d,%d', $_->{id}, $_->{nsfw}?1:0, $_->{rid}), @{$v->{screenshots}}),
@@ -49,6 +49,8 @@ sub edit {
{ name => 'vnrelations', required => 0, default => '', maxlength => 5000 },
{ name => 'screenshots', required => 0, default => '', maxlength => 1000 },
{ name => 'editsum', maxlength => 5000 },
+ { name => 'ihid', required => 0 },
+ { name => 'ilock', required => 0 },
);
# handle image upload
@@ -60,6 +62,9 @@ sub edit {
my $relations = [ map { /^([a-z]+),([0-9]+),(.+)$/ && (!$vid || $2 != $vid) ? [ $1, $2, $3 ] : () } split /\|\|\|/, $frm->{vnrelations} ];
my $screenshots = [ map /^[0-9]+,[01],[0-9]+$/ ? [split /,/] : (), split / +/, $frm->{screenshots} ];
+ $frm->{ihid} = $frm->{ihid}?1:0;
+ $frm->{ilock} = $frm->{ilock}?1:0;
+ $relations = [] if $frm->{ihid};
$frm->{anime} = join ' ', sort { $a <=> $b } keys %$anime;
$frm->{vnrelations} = join '|||', map $_->[0].','.$_->[1].','.$_->[2], sort { $a->[1] <=> $b->[1]} @{$relations};
$frm->{img_nsfw} = $frm->{img_nsfw} ? 1 : 0;
@@ -71,7 +76,7 @@ sub edit {
# perform the edit/add
my $nrev = $self->dbItemEdit(v => $vid ? $v->{cid} : undef,
- (map { $_ => $frm->{$_} } qw|title original alias desc length l_wp l_encubed l_renai l_vnn editsum img_nsfw|),
+ (map { $_ => $frm->{$_} } qw|title original alias desc length l_wp l_encubed l_renai l_vnn editsum img_nsfw ihid ilock|),
anime => [ keys %$anime ],
relations => $relations,
image => $image,
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index 79330272..924c91fd 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -83,18 +83,6 @@ sub htmlMainTabs {
end;
}
- if($type =~ /[vrp]/ && $self->authCan('del')) {
- li;
- a href => "/$id/hide", mt $obj->{hidden} ? '_mtabs_unhide' : '_mtabs_hide';
- end;
- }
-
- if($type =~ /[vrp]/ && $self->authCan('lock')) {
- li;
- a href => "/$id/lock", mt $obj->{locked} ? '_mtabs_unlock' : '_mtabs_lock';
- end;
- }
-
if($type eq 'u' && $self->authCan('usermod')) {
li $sel eq 'del' ? (class => 'tabselected') : ();
a href => "/$id/del", mt '_mtabs_del';
@@ -141,12 +129,18 @@ sub htmlHiddenMessage {
my($self, $type, $obj) = @_;
return 0 if !$obj->{hidden};
my $board = $type eq 'r' ? 'v'.$obj->{vn}[0]{vid} : $type.$obj->{id};
+ # fetch edit summary (not present in $obj because the changes aren't fetched)
+ my $editsum = $type eq 'v' ? $self->dbVNGet(id => $obj->{id}, what => 'changes')->[0]{comments}
+ : $type eq 'r' ? $self->dbReleaseGet(id => $obj->{id}, what => 'changes')->[0]{comments}
+ : $self->dbProducerGet(id => $obj->{id}, what => 'changes')->[0]{comments};
div class => 'mainbox';
h1 $obj->{title}||$obj->{name};
div class => 'warning';
h2 mt '_hiddenmsg_title';
p;
lit mt '_hiddenmsg_msg', "/t/$board";
+ br; br;
+ lit bb2html $editsum;
end;
end;
end;
@@ -209,7 +203,11 @@ sub htmlRevision {
end;
end;
my $i = 1;
- revdiff(\$i, $type, $old, $new, @$_) for (@fields);
+ revdiff(\$i, $type, $old, $new, @$_) for (
+ [ ihid => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ],
+ [ ilock => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ],
+ @fields
+ );
end;
}
end;
@@ -262,7 +260,7 @@ sub revdiff {
$ser2 = mt '_revision_empty' if !$ser2 && $ser2 ne '0';
Tr $$i++ % 2 ? (class => 'odd') : ();
- td mt "_revfield_${type}_$short";
+ td mt $short eq 'ihid' || $short eq 'ilock' ? "_revfield_$short" : "_revfield_${type}_$short";
td class => 'tcval'; lit $ser1; end;
td class => 'tcval'; lit $ser2; end;
end;
diff --git a/lib/VNDB/Util/FormHTML.pm b/lib/VNDB/Util/FormHTML.pm
index 2882b4c4..cfac77b9 100644
--- a/lib/VNDB/Util/FormHTML.pm
+++ b/lib/VNDB/Util/FormHTML.pm
@@ -209,11 +209,23 @@ sub htmlForm {
end;
}
- # edit summary / submit button
+ # db mod / edit summary / submit button
if(!$options->{nosubmit}) {
div class => 'mainbox';
fieldset class => 'submit';
if($options->{editsum}) {
+ # hidden / locked checkbox
+ if($self->authCan('del')) {
+ input type => 'checkbox', name => 'ihid', id => 'ihid', value => 1, $options->{frm}{ihid} ? (checked => 'checked') : ();
+ label for => 'ihid', mt '_form_ihid';
+ }
+ if($self->authCan('lock')) {
+ input type => 'checkbox', name => 'ilock', id => 'ilock', value => 1, $options->{frm}{ilock} ? (checked => 'checked') : ();
+ label for => 'ilock', mt '_form_ilock';
+ }
+ txt "\n".mt('_form_hidlock_note')."\n" if $self->authCan('lock') || $self->authCan('del');
+
+ # edit summary
(my $txt = $options->{frm}{editsum}||'') =~ s/&/&amp;/;
$txt =~ s/</&lt;/;
$txt =~ s/>/&gt;/;
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 04e1dbf9..b2e82bfb 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -27,6 +27,10 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid
-- triggers
+CREATE TRIGGER vn_hidlock_update BEFORE UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER producers_hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER releases_hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
+
CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
diff --git a/util/sql/func.sql b/util/sql/func.sql
index d87450a6..f32bd2ee 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -125,12 +125,20 @@ BEGIN
iid integer,
requester integer,
ip inet,
- comments text
+ comments text,
+ ihid boolean,
+ ilock boolean
);
EXCEPTION WHEN duplicate_table THEN
TRUNCATE edit_revision;
END;
- INSERT INTO edit_revision (type, iid) VALUES (t, i);
+ INSERT INTO edit_revision (type, iid, ihid, ilock) VALUES (t,
+ ( SELECT vid FROM vn_rev WHERE id = i
+ UNION SELECT rid FROM releases_rev WHERE id = i
+ UNION SELECT pid FROM producers_rev WHERE id = i),
+ COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE),
+ COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE)
+ );
END;
$$ LANGUAGE plpgsql;
@@ -159,8 +167,8 @@ BEGIN
LIMIT 1;
END IF;
-- insert change
- INSERT INTO changes (type, requester, ip, comments, rev)
- SELECT t, requester, ip, comments, r.rev
+ INSERT INTO changes (type, requester, ip, comments, ihid, ilock, rev)
+ SELECT t, requester, ip, comments, ihid, ilock, r.rev
FROM edit_revision
RETURNING id INTO r.cid;
-- insert DB item
@@ -196,13 +204,12 @@ BEGIN
EXCEPTION WHEN duplicate_table THEN
TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots;
END;
+ PERFORM edit_revtable('v', cid);
-- new VN, load defaults
IF cid IS NULL THEN
- PERFORM edit_revtable('v', NULL);
INSERT INTO edit_vn DEFAULT VALUES;
-- otherwise, load revision
ELSE
- PERFORM edit_revtable('v', (SELECT vid FROM vn_rev WHERE id = cid));
INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, l_vnn, image, l_encubed, l_renai, original FROM vn_rev WHERE id = cid;
INSERT INTO edit_vn_anime SELECT aid FROM vn_anime WHERE vid = cid;
INSERT INTO edit_vn_relations SELECT vid2, relation FROM vn_relations WHERE vid1 = cid;
@@ -252,13 +259,12 @@ BEGIN
EXCEPTION WHEN duplicate_table THEN
TRUNCATE edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn;
END;
+ PERFORM edit_revtable('r', cid);
-- new release
IF cid IS NULL THEN
- PERFORM edit_revtable('r', NULL);
INSERT INTO edit_release DEFAULT VALUES;
-- load revision
ELSE
- PERFORM edit_revtable('r', (SELECT rid FROM releases_rev WHERE id = cid));
INSERT INTO edit_release SELECT title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM releases_rev WHERE id = cid;
INSERT INTO edit_release_lang SELECT lang FROM releases_lang WHERE rid = cid;
INSERT INTO edit_release_media SELECT medium, qty FROM releases_media WHERE rid = cid;
@@ -306,13 +312,12 @@ BEGIN
EXCEPTION WHEN duplicate_table THEN
TRUNCATE edit_producer, edit_producer_relations;
END;
+ PERFORM edit_revtable('p', cid);
-- new producer
IF cid IS NULL THEN
- PERFORM edit_revtable('p', NULL);
INSERT INTO edit_producer DEFAULT VALUES;
-- load revision
ELSE
- PERFORM edit_revtable('p', (SELECT pid FROM producers_rev WHERE id = cid));
INSERT INTO edit_producer SELECT type, name, original, website, lang, "desc", alias, l_wp FROM producers_rev WHERE id = cid;
INSERT INTO edit_producer_relations SELECT pid2, relation FROM producers_relations WHERE pid1 = cid;
END IF;
@@ -553,3 +558,20 @@ BEGIN
END;
$$ LANGUAGE plpgsql;
+
+
+-- update (vn|release|producer).(hidden|locked) on a new revision
+-- NOTE: this is a /before/ trigger, it modifies NEW
+CREATE OR REPLACE FUNCTION update_hidlock() RETURNS trigger AS $$
+DECLARE
+ r record;
+BEGIN
+ IF OLD.latest IS DISTINCT FROM NEW.latest THEN
+ SELECT INTO r ihid, ilock FROM changes WHERE id = NEW.latest;
+ NEW.hidden := r.ihid;
+ NEW.locked := r.ilock;
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index d5286a94..cbd061c4 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -20,7 +20,9 @@ CREATE TABLE changes (
added timestamptz NOT NULL DEFAULT NOW(),
requester integer NOT NULL DEFAULT 0,
ip inet NOT NULL DEFAULT '0.0.0.0',
- comments text NOT NULL DEFAULT ''
+ comments text NOT NULL DEFAULT '',
+ ihid boolean NOT NULL DEFAULT FALSE,
+ ilock boolean NOT NULL DEFAULT FALSE
);
-- producers
diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql
new file mode 100644
index 00000000..d311165c
--- /dev/null
+++ b/util/updates/update_2.11.sql
@@ -0,0 +1,54 @@
+
+ALTER TABLE changes ADD COLUMN ihid boolean NOT NULL DEFAULT FALSE;
+ALTER TABLE changes ADD COLUMN ilock boolean NOT NULL DEFAULT FALSE;
+
+\i util/sql/func.sql
+
+CREATE TRIGGER vn_hidlock_update BEFORE UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER producers_hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER releases_hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
+
+
+CREATE OR REPLACE FUNCTION tmp_edit_hidlock(t text, iid integer) RETURNS void AS $$
+BEGIN
+ IF t = 'v' THEN
+ PERFORM edit_vn_init(latest) FROM vn WHERE id = iid;
+ IF EXISTS(SELECT 1 FROM vn WHERE id = iid AND hidden) THEN
+ UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1,
+ comments = 'This visual novel was deleted before the update to VNDB 2.11, no reason specified.';
+ ELSE
+ UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1,
+ comments = 'This visual novel was locked before the update to VNDB 2.11, no reason specified.';
+ END IF;
+ PERFORM edit_vn_commit();
+ ELSIF t = 'r' THEN
+ PERFORM edit_release_init(latest) FROM releases WHERE id = iid;
+ IF EXISTS(SELECT 1 FROM releases WHERE id = iid AND hidden) THEN
+ UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1,
+ comments = 'This release was deleted before the update to VNDB 2.11, no reason specified.';
+ ELSE
+ UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1,
+ comments = 'This release was locked before the update to VNDB 2.11, no reason specified.';
+ END IF;
+ PERFORM edit_release_commit();
+ ELSE
+ PERFORM edit_producer_init(latest) FROM producers WHERE id = iid;
+ IF EXISTS(SELECT 1 FROM producers WHERE id = iid AND hidden) THEN
+ UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1,
+ comments = 'This producer was deleted before the update to VNDB 2.11, no reason specified.';
+ ELSE
+ UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1,
+ comments = 'This producer was locked before the update to VNDB 2.11, no reason specified.';
+ END IF;
+ PERFORM edit_producer_commit();
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+ SELECT 'v', COUNT(*) FROM (SELECT tmp_edit_hidlock('v', id) FROM vn WHERE (hidden OR locked)) x
+UNION SELECT 'r', COUNT(*) FROM (SELECT tmp_edit_hidlock('r', id) FROM releases WHERE hidden OR locked) x
+UNION SELECT 'p', COUNT(*) FROM (SELECT tmp_edit_hidlock('p', id) FROM producers WHERE hidden OR locked) x;
+DROP FUNCTION tmp_edit_hidlock(text, integer);
+
+
+
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-19 21:59:45 +0000