Versioned the deleting and locking of database entries

This is implemented by adding ihid (item hidden) and ilock (item
locked) columns to the changes table,
The (vn|release|producer).(hidden|locked) columns now work as a
cache, refering to the changes.(ihid|ilock) columns with
  changes.id = (vn|release|producer).latest.

The cached columns are updated automatically each time a new revision is
inserted.

This is a pretty large change, bugs are quite likely.

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);
+
+
+