Merge branch 'beta'

Conflicts:
	lib/VNDB/Handler/Discussions.pm

6 files changed, 417 insertions, 263 deletions

diff --git a/util/jsgen.pl b/util/jsgen.pl
index 27cbc6da..03b79ab5 100755
--- a/util/jsgen.pl
+++ b/util/jsgen.pl
@@ -22,74 +22,111 @@ use LangFile;
 use VNDB::L10N;
 
 
-sub l10n {
-  # parse the .js code to find the l10n keys to use
-  my $js = shift;
-  my @keys;
-  push @keys, $1 ? quotemeta($1) : qr/$2/ while($js =~ m{(?:mt\('([a-z0-9_]+)'[,\)]|l10n /([^/]+)/)}g);
-  # also add the _lang_* for all languages for which we have a translation
-  my $jskeys_lang = join '|', VNDB::L10N::languages();
-  push @keys, qr/_lang_(?:$jskeys_lang)/;
 
-  # fetch the corresponding text from lang.txt
-  my %lang; # key1 => { lang1 => .., lang2 => .. }, key2 => { .. }
+my %lang; # lang1 => { key1 => .., key22 => .. }, lang2 => { .. }
+
+sub l10n_load {
+  # fetch all text from lang.txt
   my $lang = LangFile->new(read => "$ROOT/data/lang.txt");
-  my $cur; # 0 = none/excluded, 1 = TL lines
   my $key;
   while((my $l = $lang->read())) {
     my $type = shift @$l;
-    if($type eq 'key') {
-      my $k = shift @$l;
-      $cur = grep $k =~ /$_/, @keys;
-      $key = $k;
-    }
-    if($type eq 'tl' && $cur) {
-      my($lang, $sync, $val) = @$l;
-      next if !$val;
-      $lang{$key}{$lang} = $val;
-    }
+    $key = shift @$l if $type eq 'key';
+    $lang{$l->[0]}{$key} = $l->[2] if $type eq 'tl';
   }
+}
+
 
-  # generate JS code
+sub l10n {
+  my($lang, $js) = @_;
+
+  # parse the .js code and replace mt()'s that can be modified in-place, otherwise add to the @keys
+  my @keys;
+  $js =~ s{(?:mt\('([a-z0-9_]+)'([,\)])|l10n /([^/]+)/)}#
+    my($k, $s, $q) = ($1, $2, $3);
+    my $v = $k ? $lang{$lang}{$k} || $lang{'en'}{$k} : '';
+    if($q) { $q ne '<perl regex>' && push @keys, qr/$q/; '' }
+    elsif($s eq ')' && $v && $v !~ /[\~\[\]]/) {
+      $v =~ s/"/\\"/g;
+      $v =~ s/\n/\\n/g;
+      qq{"$v"}
+    } else {
+      push @keys, quotemeta($k);
+      "mt('$k'$s"
+    }
+  #eg;
+  # also add the _lang_* for all languages for which we have a translation
+  my $jskeys_lang = join '|', VNDB::L10N::languages();
+  push @keys, qr/_lang_(?:$jskeys_lang)/;
+
+  # generate header
   my $r = "L10N_STR = {\n";
   my $first = 1;
-  for my $key (sort keys %lang) {
+  for my $key (sort keys %{$lang{$lang}}) {
+    next if !grep $key =~ /$_/, @keys;
     $r .= ",\n" if !$first;
     $first = 0;
-    # let's assume all L10N keys are valid JS variable names
-    $r .= sprintf qq|  "%s": {\n|, $key;
-    my $firstk = 1;
-    for (sort keys %{$lang{$key}}) {
-      $r .= ",\n" if !$firstk;
-      $firstk = 0;
-      my $lang = $_;
-      $lang = qq{"$lang"} if $lang =~ /^(?:as|do|if|in|is)$/; # reserved two-char words
-      my $val = $lang{$key}{$_};
-      $val =~ s/"/\\"/g;
-      $val =~ s/\n/\\n/g;
-      $r .= sprintf qq|    %s: "%s"|, $lang, $val;
+    my $val = $lang{$lang}{$key} || $lang{'en'}{$key};
+    $val =~ s/"/\\"/g;
+    $val =~ s/\n/\\n/g;
+    $val =~ s/\[index,.+$// if $key =~ /^_vnlength_/; # special casing the VN lengths, since the JS mt() doesn't handle [index]
+    $r .= sprintf qq|  %s: "%s"|, $key !~ /^[a-z0-9_]+$/ ? "'$key'" : $key, $val;
+  }
+  $r .= "\n};";
+  return ("$r\n", $js);
+}
+
+
+# screen resolution information, suitable for usage in filFSelect()
+sub resolutions {
+  my $res_cat = '';
+  my $resolutions = '';
+  my $comma = 0;
+  for my $i (0..$#{$S{resolutions}}) {
+    my $r = $S{resolutions}[$i];
+    if($res_cat ne $r->[1]) {
+      $resolutions .= ']' if $res_cat;
+      $resolutions .= ",['$r->[1]',";
+      $res_cat = $r->[1];
+      $comma = 0;
     }
-    $r .= "\n  }";
+    $resolutions .= ($comma ? ',' : '')."[$i,'$r->[0]']";
+    $comma = 1;
   }
-  $r .= "\n};\n";
-  $r .= 'L10N_LANG = [ '.join(', ', map qq{"$_"}, VNDB::L10N::languages()).' ];';
-  return "$r\n";
+  $resolutions .= ']' if $res_cat;
+  return "resolutions = [ $resolutions ];\n";
 }
 
 
 sub jsgen {
-  # JavaScript::Minifier::XS doesn't correctly handle perl's unicode,
-  #  so just do everything in raw bytes instead.
-  open my $JS, '<', "$ROOT/data/script.js" or die $!;
+  l10n_load();
+  my $common = '';
+  $common .= resolutions();
+  $common .= sprintf "rlst_rstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_rstat}};
+  $common .= sprintf "rlst_vstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_vstat}};
+  $common .= sprintf "cookie_prefix = '%s';\n", $S{cookie_prefix};
+  $common .= sprintf "age_ratings = [ %s ];\n", join ',', map !defined $_ ? -1 : $_, @{$S{age_ratings}};
+  $common .= sprintf "languages = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{languages}};
+  $common .= sprintf "platforms = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{platforms}};
+  $common .= sprintf "media = [ %s ];\n", join ', ', map qq{"$_"}, sort keys %{$S{media}};
+  $common .= sprintf "release_types = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{release_types}};
+  $common .= sprintf "animated = [ %s ];\n", join ', ', @{$S{animated}};
+  $common .= sprintf "voiced = [ %s ];\n", join ', ', @{$S{voiced}};
+  $common .= sprintf "vn_lengths = [ %s ];\n", join ', ', @{$S{vn_lengths}};
+  $common .= sprintf "L10N_LANG = [ %s ];\n", join(', ', map qq{"$_"}, VNDB::L10N::languages());
+
+  open my $JS, '<:utf8', "$ROOT/data/script.js" or die $!;
   my $js .= join '', <$JS>;
   close $JS;
-  my $head = encode_utf8(l10n($js)) . "\n";
-  $head .= sprintf "rlst_rstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_rstat}};
-  $head .= sprintf "rlst_vstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_vstat}};
-  $head .= sprintf "cookie_prefix = '%s';\n", $S{cookie_prefix};
-  open my $NEWJS, '>', "$ROOT/static/f/script.js" or die $!;
-  print $NEWJS $JavaScript::Minifier::XS::VERSION ? JavaScript::Minifier::XS::minify($head.$js) : $head.$js;
-  close $NEWJS;
+
+  for my $l (VNDB::L10N::languages()) {
+    my($head, $body) = l10n($l, $js);
+    # JavaScript::Minifier::XS doesn't correctly handle perl's unicode, so manually encode
+    my $content = encode_utf8($head . $common . $body);
+    open my $NEWJS, '>', "$ROOT/static/f/js/$l.js" or die $!;
+    print $NEWJS $JavaScript::Minifier::XS::VERSION ? JavaScript::Minifier::XS::minify($content) : $content;
+    close $NEWJS;
+  }
 }
 
 jsgen;
diff --git a/util/sql/all.sql b/util/sql/all.sql
index fd1f0a73..4408c6df 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -1,8 +1,5 @@
 -- NOTE: Make sure you're cd'ed in the vndb root directory before running this script
 
--- plpgsql is required for our (trigger) functions
-CREATE LANGUAGE plpgsql;
-
 
 -- data types
 
@@ -30,52 +27,74 @@ CREATE TYPE vn_relation       AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid
 
 -- triggers
 
-CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON producers     FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON vn            FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON producers     FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON releases      FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) 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();
 
-CREATE TRIGGER vn_stats_update            AFTER  INSERT OR UPDATE ON vn            FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER producers_stats_update     AFTER  INSERT OR UPDATE ON producers     FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER releases_stats_update      AFTER  INSERT OR UPDATE ON releases      FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER threads_stats_update       AFTER  INSERT OR UPDATE ON threads       FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER threads_posts_stats_update AFTER  INSERT OR UPDATE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER users_stats_update         AFTER  INSERT OR DELETE ON users         FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON vn            FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON producers     FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON producers     FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON releases      FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON threads       FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON threads       FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache                AFTER  INSERT OR DELETE ON users         FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
 
-CREATE TRIGGER vn_anime_aid               BEFORE INSERT OR UPDATE ON vn_anime      FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
+CREATE TRIGGER vn_anime_aid_new           BEFORE INSERT           ON vn_anime      FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
+CREATE TRIGGER vn_anime_aid_edit          BEFORE UPDATE           ON vn_anime      FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) EXECUTE PROCEDURE vn_anime_aid();
 
-CREATE TRIGGER anime_fetch_notify         AFTER  INSERT OR UPDATE ON anime         FOR EACH ROW EXECUTE PROCEDURE anime_fetch_notify();
+CREATE TRIGGER anime_fetch_notify         AFTER  INSERT OR UPDATE ON anime         FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify();
 
-CREATE TRIGGER vn_rev_image_notify        AFTER  INSERT OR UPDATE ON vn_rev        FOR EACH ROW EXECUTE PROCEDURE vn_rev_image_notify();
+CREATE TRIGGER vn_rev_image_notify        AFTER  INSERT OR UPDATE ON vn_rev        FOR EACH ROW WHEN (NEW.image < 0) EXECUTE PROCEDURE vn_rev_image_notify();
 
-CREATE TRIGGER screenshot_process_notify  AFTER  INSERT OR UPDATE ON screenshots   FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify();
+CREATE TRIGGER screenshot_process_notify  AFTER  INSERT OR UPDATE ON screenshots   FOR EACH ROW WHEN (NEW.processed = FALSE) EXECUTE PROCEDURE screenshot_process_notify();
 
-CREATE TRIGGER vn_relgraph_notify         AFTER  UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
+CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW
+  WHEN (OLD.rgraph      IS DISTINCT FROM NEW.rgraph
+     OR OLD.latest      IS DISTINCT FROM NEW.latest
+     OR OLD.c_released  IS DISTINCT FROM NEW.c_released
+     OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
+  ) EXECUTE PROCEDURE vn_relgraph_notify();
 
-CREATE TRIGGER producer_relgraph_notify   AFTER  UPDATE           ON producers     FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
+CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW
+  WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph
+     OR OLD.latest IS DISTINCT FROM NEW.latest
+  ) EXECUTE PROCEDURE producer_relgraph_notify();
 
 CREATE TRIGGER insert_notify              AFTER  INSERT           ON changes       FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
 CREATE TRIGGER insert_notify              AFTER  INSERT           ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
 CREATE TRIGGER insert_notify              AFTER  INSERT           ON tags          FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
 
-CREATE TRIGGER release_vncache_update     AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE release_vncache_update();
+CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW
+  WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden)
+  EXECUTE PROCEDURE release_vncache_update();
 
 CREATE TRIGGER notify_pm                  AFTER  INSERT           ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
-CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON producers     FOR EACH ROW EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE notify_listdel();
-CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE notify_listdel();
-CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON producers     FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_announce            AFTER  INSERT           ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_announce();
-
-CREATE TRIGGER vn_vnsearch_notify         AFTER  UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
-CREATE TRIGGER vn_vnsearch_notify         AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
+CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON producers     FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
+CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
+CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON producers     FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_announce            AFTER  INSERT           ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
+
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW
+  WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL AND NOT NEW.hidden
+     OR NEW.hidden IS DISTINCT FROM OLD.hidden
+     OR NEW.latest IS DISTINCT FROM OLD.latest
+  ) EXECUTE PROCEDURE vn_vnsearch_notify();
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW
+  WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest)
+  EXECUTE PROCEDURE vn_vnsearch_notify();
 
 
 -- Sequences used for ID generation of items not in the DB
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 371e3ed0..53af121c 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -26,6 +26,21 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
       AND rr1.released <> 0
       GROUP BY rv1.vid
     ), 0),
+    c_olang = ARRAY(
+      SELECT lang
+        FROM releases_lang
+       WHERE rid = (
+        SELECT r.latest
+          FROM releases_vn rv
+          JOIN releases r ON rv.rid = r.latest
+          JOIN releases_rev rr ON rr.id = r.latest AND rr.rid = r.id
+         WHERE rr.released > 0
+           AND NOT r.hidden
+           AND rv.vid = $1
+         ORDER BY rr.released
+         LIMIT 1
+       )
+    ),
     c_languages = ARRAY(
       SELECT rl2.lang
       FROM releases_rev rr2
@@ -59,6 +74,8 @@ $$ LANGUAGE sql;
 -- recalculate vn.c_popularity
 CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$
 BEGIN
+  -- the following queries only update rows with popularity > 0, so make sure to reset all rows first
+  UPDATE vn SET c_popularity = NULL;
   CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS
       SELECT v.uid, v.vid, sqrt(count(*))::real
         FROM votes v
@@ -67,7 +84,7 @@ BEGIN
     GROUP BY v.vid, v.uid;
   CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS
     SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid;
-  UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id);
+  UPDATE vn SET c_popularity = s1.win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 s1 WHERE s1.vid = vn.id;
   RETURN;
 END;
 $$ LANGUAGE plpgsql;
@@ -393,7 +410,7 @@ BEGIN
   IF TG_OP = 'INSERT' THEN
     IF TG_TABLE_NAME = 'users' THEN
       UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
-    ELSIF NEW.hidden = FALSE THEN
+    ELSE
       IF TG_TABLE_NAME = 'threads_posts' THEN
         IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN
           UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
@@ -404,12 +421,12 @@ BEGIN
     END IF;
 
   ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN
-    IF OLD.hidden = TRUE AND NEW.hidden = FALSE THEN
+    IF OLD.hidden = TRUE THEN
       IF TG_TABLE_NAME = 'threads' THEN
         UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts';
       END IF;
       UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
-    ELSIF OLD.hidden = FALSE AND NEW.hidden = TRUE THEN
+    ELSIF OLD.hidden = FALSE THEN
       IF TG_TABLE_NAME = 'threads' THEN
         UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts';
       END IF;
@@ -426,6 +443,7 @@ $$ LANGUAGE 'plpgsql';
 
 
 -- insert rows into anime for new vn_anime.aid items
+-- (this is a BEFORE trigger)
 CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$
 BEGIN
   IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN
@@ -439,36 +457,21 @@ $$ LANGUAGE plpgsql;
 
 -- Send a notify whenever anime info should be fetched
 CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
-BEGIN
-  IF NEW.lastfetch IS NULL THEN
-    NOTIFY anime;
-  END IF;
-  RETURN NULL;
-END;
+  BEGIN NOTIFY anime; RETURN NULL; END;
 $$ LANGUAGE plpgsql;
 
 
 
 -- Send a notify when a new cover image is uploaded
 CREATE OR REPLACE FUNCTION vn_rev_image_notify() RETURNS trigger AS $$
-BEGIN
-  IF NEW.image < 0 THEN
-    NOTIFY coverimage;
-  END IF;
-  RETURN NULL;
-END;
+  BEGIN NOTIFY coverimage; RETURN NULL; END;
 $$ LANGUAGE plpgsql;
 
 
 
 -- Send a notify when a screenshot needs to be processed
 CREATE OR REPLACE FUNCTION screenshot_process_notify() RETURNS trigger AS $$
-BEGIN
-  IF NEW.processed = FALSE THEN
-    NOTIFY screenshot;
-  END IF;
-  RETURN NULL;
-END;
+  BEGIN NOTIFY screenshot; RETURN NULL; END;
 $$ LANGUAGE plpgsql;
 
 
@@ -558,11 +561,9 @@ $$ LANGUAGE plpgsql;
 -- call update_vncache() when a release is added, edited, hidden or unhidden
 CREATE OR REPLACE FUNCTION release_vncache_update() RETURNS trigger AS $$
 BEGIN
-  IF OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden THEN
-    PERFORM update_vncache(vid) FROM (
-      SELECT DISTINCT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest
-    ) AS v(vid);
-  END IF;
+  PERFORM update_vncache(vid) FROM (
+    SELECT DISTINCT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest
+  ) AS v(vid);
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
@@ -575,11 +576,9 @@ 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;
+  SELECT INTO r ihid, ilock FROM changes WHERE id = NEW.latest;
+  NEW.hidden := r.ihid;
+  NEW.locked := r.ilock;
   RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;
@@ -663,123 +662,111 @@ END;
 $$ LANGUAGE plpgsql;
 
 
--- called on UPDATE vn / producers / releases
+-- called on UPDATE vn / producers / releases when (NOT OLD.hidden AND NEW.hidden)
 CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$
 BEGIN
-  -- item is deleted?
-  IF NOT OLD.hidden AND NEW.hidden THEN
-    INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
-      SELECT DISTINCT 'dbdel'::notification_ntype,
-             (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype,
-             c.requester, NEW.id, c2.rev, x.title, c2.requester
-        -- look for changes of the deleted entry
-        -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
-        FROM changes c
-        JOIN (  SELECT vr.id, vr2.title FROM vn_rev vr
-                  JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest
-                 WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id
-          UNION SELECT rr.id, rr2.title FROM releases_rev rr
-                  JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest
-                 WHERE TG_TABLE_NAME = 'releases'  AND rr.rid = NEW.id
-          UNION SELECT pr.id, pr2.name FROM producers_rev pr
-                  JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest
-                 WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id
-        ) x(id, title) ON c.id = x.id
-        -- join info about the deletion itself
-        JOIN changes c2 ON c2.id = NEW.latest
-       WHERE c.requester <> 1 -- exclude Multi
-         -- exclude the user who deleted the entry
-         AND c.requester <> c2.requester;
-  END IF;
+  INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
+    SELECT DISTINCT 'dbdel'::notification_ntype,
+           (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype,
+           c.requester, NEW.id, c2.rev, x.title, c2.requester
+      -- look for changes of the deleted entry
+      -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
+      FROM changes c
+      JOIN (  SELECT vr.id, vr2.title FROM vn_rev vr
+                JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest
+               WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id
+        UNION SELECT rr.id, rr2.title FROM releases_rev rr
+                JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest
+               WHERE TG_TABLE_NAME = 'releases'  AND rr.rid = NEW.id
+        UNION SELECT pr.id, pr2.name FROM producers_rev pr
+                JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest
+               WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id
+      ) x(id, title) ON c.id = x.id
+      -- join info about the deletion itself
+      JOIN changes c2 ON c2.id = NEW.latest
+     WHERE c.requester <> 1 -- exclude Multi
+       -- exclude the user who deleted the entry
+       AND c.requester <> c2.requester;
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
 
 
--- called on UPDATE vn / releases
+-- called on UPDATE vn / releases when (NOT OLD.hidden AND NEW.hidden)
 CREATE OR REPLACE FUNCTION notify_listdel() RETURNS trigger AS $$
 BEGIN
-  -- item is deleted?
-  IF NOT OLD.hidden AND NEW.hidden THEN
-    INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
-      SELECT DISTINCT 'listdel'::notification_ntype,
-             (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' ELSE 'r' END)::notification_ltype,
-             u.uid, NEW.id, c.rev, x.title, c.requester
-        -- look for users who should get this notify
-        FROM (
-          -- voted on the VN
-                SELECT uid FROM votes  WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
-          -- VN in wishlist
-          UNION SELECT uid FROM wlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
-          -- release in release list
-          UNION SELECT uid FROM rlists WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id
-          -- there's also a special case which we're ignoring here:
-          --  when a VN linked to a release in a user's release list is deleted
-          -- normally, the releases are also deleted, so a notify is generated anyway
-        ) u
-        -- fetch info about this edit
-        JOIN changes c ON c.id = NEW.latest
-        JOIN (
-                SELECT id, title FROM vn_rev       WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
-          UNION SELECT id, title FROM releases_rev WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id
-        ) x ON c.id = x.id;
-  END IF;
+  INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
+    SELECT DISTINCT 'listdel'::notification_ntype,
+           (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' ELSE 'r' END)::notification_ltype,
+           u.uid, NEW.id, c.rev, x.title, c.requester
+      -- look for users who should get this notify
+      FROM (
+        -- voted on the VN
+              SELECT uid FROM votes  WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
+        -- VN in wishlist
+        UNION SELECT uid FROM wlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
+        -- release in release list
+        UNION SELECT uid FROM rlists WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id
+        -- there's also a special case which we're ignoring here:
+        --  when a VN linked to a release in a user's release list is deleted
+        -- normally, the releases are also deleted, so a notify is generated anyway
+      ) u
+      -- fetch info about this edit
+      JOIN changes c ON c.id = NEW.latest
+      JOIN (
+              SELECT id, title FROM vn_rev       WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
+        UNION SELECT id, title FROM releases_rev WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id
+      ) x ON c.id = x.id;
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
 
 
--- called on UPDATE vn / producers / releases
+-- called on UPDATE vn / producers / releases when (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden)
 -- this trigger is very similar to notify_dbdel()
 CREATE OR REPLACE FUNCTION notify_dbedit() RETURNS trigger AS $$
 BEGIN
-  -- item is edited but not deleted? (deleted items are handled by the dbdel notify)
-  IF OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden THEN
-    INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
-      SELECT DISTINCT 'dbedit'::notification_ntype,
-             (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype,
-             c.requester, NEW.id, c2.rev, x.title, c2.requester
-        -- look for changes of the edited entry
-        FROM changes c
-        JOIN (  SELECT vr.id, vr2.title FROM vn_rev vr
-                  JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest
-                 WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id
-          UNION SELECT rr.id, rr2.title FROM releases_rev rr
-                  JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest
-                 WHERE TG_TABLE_NAME = 'releases'  AND rr.rid = NEW.id
-          UNION SELECT pr.id, pr2.name FROM producers_rev pr
-                  JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest
-                 WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id
-        ) x(id, title) ON c.id = x.id
-        -- join info about the deletion itself
-        JOIN changes c2 ON c2.id = NEW.latest
-        -- join info about the user who should get this notification
-        JOIN users u ON u.id = c.requester
-        -- exclude the user who edited the entry
-       WHERE c.requester <> c2.requester
-         -- exclude users who don't want this notify
-         AND u.notify_dbedit;
-  END IF;
+  INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
+    SELECT DISTINCT 'dbedit'::notification_ntype,
+           (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype,
+           c.requester, NEW.id, c2.rev, x.title, c2.requester
+      -- look for changes of the edited entry
+      FROM changes c
+      JOIN (  SELECT vr.id, vr2.title FROM vn_rev vr
+                JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest
+               WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id
+        UNION SELECT rr.id, rr2.title FROM releases_rev rr
+                JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest
+               WHERE TG_TABLE_NAME = 'releases'  AND rr.rid = NEW.id
+        UNION SELECT pr.id, pr2.name FROM producers_rev pr
+                JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest
+               WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id
+      ) x(id, title) ON c.id = x.id
+      -- join info about the deletion itself
+      JOIN changes c2 ON c2.id = NEW.latest
+      -- join info about the user who should get this notification
+      JOIN users u ON u.id = c.requester
+      -- exclude the user who edited the entry
+     WHERE c.requester <> c2.requester
+       -- exclude users who don't want this notify
+       AND u.notify_dbedit;
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
 
 
--- called on INSERT INTO threads_posts
+-- called on INSERT INTO threads_posts when (NEW.num = 1)
 CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$
 BEGIN
-  -- new thread?
-  IF NEW.num = 1 THEN
-    INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
-      SELECT 'announce', 't', u.id, t.id, 1, t.title, NEw.uid
-        FROM threads t
-        JOIN threads_boards tb ON tb.tid = t.id
-        -- get the users who want this announcement
-        JOIN users u ON u.notify_announce
-       WHERE t.id = NEW.tid
-         AND tb.type = 'an' -- announcement board
-         AND NOT t.hidden;
-  END IF;
+  INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
+    SELECT 'announce', 't', u.id, t.id, 1, t.title, NEw.uid
+      FROM threads t
+      JOIN threads_boards tb ON tb.tid = t.id
+      -- get the users who want this announcement
+      JOIN users u ON u.notify_announce
+     WHERE t.id = NEW.tid
+       AND tb.type = 'an' -- announcement board
+       AND NOT t.hidden;
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 72ecd9cc..6911a40b 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -36,7 +36,7 @@ CREATE TABLE notifications (
   iid integer NOT NULL,
   subid integer,
   c_title text NOT NULL,
-  c_byuser integer
+  c_byuser integer NOT NULL DEFAULT 0
 );
 
 -- producers
@@ -194,7 +194,7 @@ CREATE TABLE tags (
   added timestamptz NOT NULL DEFAULT NOW(),
   state smallint NOT NULL DEFAULT 0,
   c_vns integer NOT NULL DEFAULT 0,
-  addedby integer NOT NULL DEFAULT 1
+  addedby integer NOT NULL DEFAULT 0
 );
 
 -- tags_aliases
@@ -293,7 +293,8 @@ CREATE TABLE vn (
   c_popularity real,
   c_rating real,
   c_votecount integer NOT NULL DEFAULT 0,
-  c_search text
+  c_search text,
+  c_olang language[] NOT NULL DEFAULT '{}'
 );
 
 -- vn_anime
@@ -358,9 +359,9 @@ CREATE TABLE wlists (
 
 
 
-ALTER TABLE changes             ADD FOREIGN KEY (requester) REFERENCES users         (id);
-ALTER TABLE notifications       ADD FOREIGN KEY (uid)       REFERENCES users         (id);
-ALTER TABLE notifications       ADD FOREIGN KEY (c_byuser)  REFERENCES users         (id);
+ALTER TABLE changes             ADD FOREIGN KEY (requester) REFERENCES users         (id) ON DELETE SET DEFAULT;
+ALTER TABLE notifications       ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+ALTER TABLE notifications       ADD FOREIGN KEY (c_byuser)  REFERENCES users         (id) ON DELETE SET DEFAULT;
 ALTER TABLE producers           ADD FOREIGN KEY (latest)    REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE producers           ADD FOREIGN KEY (rgraph)    REFERENCES relgraphs     (id);
 ALTER TABLE producers_relations ADD FOREIGN KEY (pid1)      REFERENCES producers_rev (id);
@@ -378,19 +379,19 @@ ALTER TABLE releases_rev        ADD FOREIGN KEY (id)        REFERENCES changes
 ALTER TABLE releases_rev        ADD FOREIGN KEY (rid)       REFERENCES releases      (id);
 ALTER TABLE releases_vn         ADD FOREIGN KEY (rid)       REFERENCES releases_rev  (id);
 ALTER TABLE releases_vn         ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
-ALTER TABLE rlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id);
+ALTER TABLE rlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE rlists              ADD FOREIGN KEY (rid)       REFERENCES releases      (id);
-ALTER TABLE sessions            ADD FOREIGN KEY (uid)       REFERENCES users         (id);
-ALTER TABLE tags                ADD FOREIGN KEY (addedby)   REFERENCES users         (id);
+ALTER TABLE sessions            ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+ALTER TABLE tags                ADD FOREIGN KEY (addedby)   REFERENCES users         (id) ON DELETE SET DEFAULT;
 ALTER TABLE tags_aliases        ADD FOREIGN KEY (tag)       REFERENCES tags          (id);
 ALTER TABLE tags_parents        ADD FOREIGN KEY (tag)       REFERENCES tags          (id);
 ALTER TABLE tags_parents        ADD FOREIGN KEY (parent)    REFERENCES tags          (id);
 ALTER TABLE tags_vn             ADD FOREIGN KEY (tag)       REFERENCES tags          (id);
 ALTER TABLE tags_vn             ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
-ALTER TABLE tags_vn             ADD FOREIGN KEY (uid)       REFERENCES users         (id);
+ALTER TABLE tags_vn             ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE threads             ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE threads_posts       ADD FOREIGN KEY (tid)       REFERENCES threads       (id);
-ALTER TABLE threads_posts       ADD FOREIGN KEY (uid)       REFERENCES users         (id);
+ALTER TABLE threads_posts       ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
 ALTER TABLE threads_boards      ADD FOREIGN KEY (tid)       REFERENCES threads       (id);
 ALTER TABLE vn                  ADD FOREIGN KEY (latest)    REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE vn                  ADD FOREIGN KEY (rgraph)    REFERENCES relgraphs     (id);
@@ -403,8 +404,8 @@ ALTER TABLE vn_rev              ADD FOREIGN KEY (vid)       REFERENCES vn
 ALTER TABLE vn_screenshots      ADD FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
 ALTER TABLE vn_screenshots      ADD FOREIGN KEY (scr)       REFERENCES screenshots   (id);
 ALTER TABLE vn_screenshots      ADD FOREIGN KEY (rid)       REFERENCES releases      (id);
-ALTER TABLE votes               ADD FOREIGN KEY (uid)       REFERENCES users         (id);
+ALTER TABLE votes               ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE votes               ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
-ALTER TABLE wlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id);
+ALTER TABLE wlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE wlists              ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
 
diff --git a/util/updates/update_2.14.sql b/util/updates/update_2.14.sql
new file mode 100644
index 00000000..ea06e689
--- /dev/null
+++ b/util/updates/update_2.14.sql
@@ -0,0 +1,145 @@
+
+-- add vn.c_olang
+ALTER TABLE vn ADD COLUMN c_olang language[] NOT NULL DEFAULT '{}';
+
+
+-- reload functions
+\i util/sql/func.sql
+
+
+-- regenerate vn.c_* columns
+SELECT COUNT(*) FROM (SELECT update_vncache(id) FROM vn WHERE NOT hidden) s;
+
+
+-- redefine the triggers to use the new conditional triggers in PostgreSQL 9.0
+
+DROP TRIGGER hidlock_update ON vn;
+DROP TRIGGER hidlock_update ON producers;
+DROP TRIGGER hidlock_update ON releases;
+CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON vn            FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON producers     FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON releases      FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+
+
+DROP TRIGGER vn_stats_update            ON vn;
+DROP TRIGGER producers_stats_update     ON producers;
+DROP TRIGGER releases_stats_update      ON releases;
+DROP TRIGGER threads_stats_update       ON threads;
+DROP TRIGGER threads_posts_stats_update ON threads_posts;
+DROP TRIGGER users_stats_update         ON users;
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON vn            FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON producers     FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON producers     FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON releases      FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON threads       FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON threads       FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache                AFTER  INSERT OR DELETE ON users         FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+
+DROP TRIGGER vn_anime_aid ON vn_anime;
+CREATE TRIGGER vn_anime_aid_new           BEFORE INSERT           ON vn_anime      FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
+CREATE TRIGGER vn_anime_aid_edit          BEFORE UPDATE           ON vn_anime      FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) EXECUTE PROCEDURE vn_anime_aid();
+
+DROP TRIGGER anime_fetch_notify ON anime;
+CREATE TRIGGER anime_fetch_notify         AFTER  INSERT OR UPDATE ON anime         FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify();
+
+DROP TRIGGER vn_rev_image_notify ON vn_rev;
+CREATE TRIGGER vn_rev_image_notify        AFTER  INSERT OR UPDATE ON vn_rev        FOR EACH ROW WHEN (NEW.image < 0) EXECUTE PROCEDURE vn_rev_image_notify();
+
+DROP TRIGGER screenshot_process_notify ON screenshots;
+CREATE TRIGGER screenshot_process_notify  AFTER  INSERT OR UPDATE ON screenshots   FOR EACH ROW WHEN (NEW.processed = FALSE) EXECUTE PROCEDURE screenshot_process_notify();
+
+DROP TRIGGER vn_relgraph_notify ON vn;
+CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW
+  WHEN (OLD.rgraph      IS DISTINCT FROM NEW.rgraph
+     OR OLD.latest      IS DISTINCT FROM NEW.latest
+     OR OLD.c_released  IS DISTINCT FROM NEW.c_released
+     OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
+  ) EXECUTE PROCEDURE vn_relgraph_notify();
+
+DROP TRIGGER producer_relgraph_notify ON producers;
+CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW
+  WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph
+     OR OLD.latest IS DISTINCT FROM NEW.latest
+  ) EXECUTE PROCEDURE producer_relgraph_notify();
+
+DROP TRIGGER release_vncache_update ON releases;
+CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW
+  WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden)
+  EXECUTE PROCEDURE release_vncache_update();
+
+DROP TRIGGER notify_dbdel ON vn;
+DROP TRIGGER notify_dbdel ON producers;
+DROP TRIGGER notify_dbdel ON releases;
+CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON producers     FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+
+DROP TRIGGER notify_listdel ON vn;
+DROP TRIGGER notify_listdel ON releases;
+CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
+CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
+
+DROP TRIGGER notify_dbedit ON vn;
+DROP TRIGGER notify_dbedit ON producers;
+DROP TRIGGER notify_dbedit ON releases;
+CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON producers     FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+
+DROP TRIGGER notify_announce ON threads_posts;
+CREATE TRIGGER notify_announce            AFTER  INSERT           ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
+
+DROP TRIGGER vn_vnsearch_notify ON vn;
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW
+  WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL AND NOT NEW.hidden
+     OR NEW.hidden IS DISTINCT FROM OLD.hidden
+     OR NEW.latest IS DISTINCT FROM OLD.latest
+  ) EXECUTE PROCEDURE vn_vnsearch_notify();
+
+DROP TRIGGER vn_vnsearch_notify ON releases;
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW
+  WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest)
+  EXECUTE PROCEDURE vn_vnsearch_notify();
+
+
+
+-- add ON DELETE clause to all foreign keys referencing users (id)
+-- and change some defaults/constraints to make sure it'll actually work
+
+ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey;
+ALTER TABLE changes             ADD FOREIGN KEY (requester) REFERENCES users         (id) ON DELETE SET DEFAULT;
+
+UPDATE notifications SET c_byuser = 0 WHERE c_byuser IS NULL;
+ALTER TABLE notifications ALTER COLUMN c_byuser SET DEFAULT 0;
+ALTER TABLE notifications ALTER COLUMN c_byuser SET NOT NULL;
+ALTER TABLE notifications DROP CONSTRAINT notifications_uid_fkey;
+ALTER TABLE notifications DROP CONSTRAINT notifications_c_byuser_fkey;
+ALTER TABLE notifications       ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+ALTER TABLE notifications       ADD FOREIGN KEY (c_byuser)  REFERENCES users         (id) ON DELETE SET DEFAULT;
+
+ALTER TABLE rlists DROP CONSTRAINT rlists_uid_fkey;
+ALTER TABLE rlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+
+ALTER TABLE sessions DROP CONSTRAINT sessions_uid_fkey;
+ALTER TABLE sessions            ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+
+ALTER TABLE tags ALTER COLUMN addedby SET DEFAULT 0;
+ALTER TABLE tags DROP CONSTRAINT tags_addedby_fkey;
+ALTER TABLE tags                ADD FOREIGN KEY (addedby)   REFERENCES users         (id) ON DELETE SET DEFAULT;
+
+ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_uid_fkey;
+ALTER TABLE tags_vn             ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+
+ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_uid_fkey;
+ALTER TABLE threads_posts       ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
+
+ALTER TABLE votes DROP CONSTRAINT votes_uid_fkey;
+ALTER TABLE votes               ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+
+ALTER TABLE wlists DROP CONSTRAINT wlists_uid_fkey;
+ALTER TABLE wlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+
diff --git a/util/vndb.pl b/util/vndb.pl
index 29c2648c..183cea4d 100755
--- a/util/vndb.pl
+++ b/util/vndb.pl
@@ -24,13 +24,10 @@ use SkinFile;
 our(%O, %S);
 
 
-# load and (if required) regenerate the skins
+# load the skins
 # NOTE: $S{skins} can be modified in data/config.pl, allowing deletion of skins or forcing only one skin
-$S{skins} = readskins();
-
-
-# automatically regenerate script.js when required and possible
-checkjs();
+my $skin = SkinFile->new("$ROOT/static/s");
+$S{skins} = { map +($_ => [ $skin->get($_, 'name'), $skin->get($_, 'userid') ]), $skin->list };
 
 
 # load lang.dat
@@ -41,6 +38,10 @@ VNDB::L10N::loadfile();
 require $ROOT.'/data/global.pl';
 
 
+# automatically regenerate the skins and script.js and whatever else should be done
+system "make -sC $ROOT" if $S{regen_static};
+
+
 YAWF::init(
   %O,
   namespace => 'VNDB',
@@ -69,11 +70,11 @@ sub reqinit {
   $self->authInit;
 
   # check for IE6
-  if($self->reqHeader('User-Agent') && $self->reqHeader('User-Agent') =~ /MSIE 6/
-    && !$self->reqCookie('ie-sucks') && $self->reqPath ne 'we-dont-like-ie6') {
+  if($self->reqHeader('User-Agent') && $self->reqHeader('User-Agent') =~ /MSIE [67]/
+    && !$self->reqCookie('ie-sucks') && $self->reqPath ne 'we-dont-like-ie') {
     # act as if we're opening /we-dont-like-ie6 (ugly hack, until YAWF supports preventing URL handlers from firing)
     $ENV{HTTP_REFERER} = $ENV{REQUEST_URI};
-    $ENV{REQUEST_URI} = '/we-dont-like-ie6';
+    $ENV{REQUEST_URI} = '/we-dont-like-ie';
   }
 
   # load some stats (used for about all pageviews, anyway)
@@ -96,39 +97,3 @@ sub handle404 {
   $self->htmlFooter;
 }
 
-
-sub readskins {
-  my %skins; # dirname => skin name
-  my @regen;
-  my $lasttemplate = [stat "$ROOT/data/style.css"]->[9];
-  my $skin = SkinFile->new("$ROOT/static/s");
-  for my $n ($skin->list) {
-    $skins{$n} = [ $skin->get($n, 'name'), $skin->get($n, 'userid') ];
-    next if !$skins{$n}[0];
-
-    my $f = "$ROOT/static/s/$n";
-    my $css = -f "$f/style.css" && [stat "$f/style.css"]->[9] || 0;
-    my $boxbg = -f "$f/boxbg.png" && [stat "$f/boxbg.png"]->[9] || 0;
-    my $lastgen = $css < $boxbg ? $css : $boxbg;
-    push @regen, $n if (!$lastgen && -x $f && (!$css && !$boxbg || $css && -w "$f/style.css" || $boxbg && -w "$f/boxbg.png"))
-      || ([stat "$f/conf"]->[9] > $lastgen || $lasttemplate > $lastgen) && -w "$f/style.css" && -w "$f/boxbg.png";
-  }
-  system "$ROOT/util/skingen.pl", @regen if @regen;
-  return \%skins;
-}
-
-
-sub checkjs {
-  my $script = "$ROOT/static/f/script.js";
-  my $lastmod = [stat $script]->[9];
-  system "$ROOT/util/jsgen.pl" if
-       (!-e $script && -x "$ROOT/static/f")
-    || (-e $script && -w $script && (
-           $lastmod < [stat "$ROOT/data/script.js"]->[9]
-        || $lastmod < [stat "$ROOT/data/lang.txt"]->[9]
-        || (-e "$ROOT/data/config.pl" && $lastmod < [stat "$ROOT/data/config.pl"]->[9])
-        || $lastmod < [stat "$ROOT/data/global.pl"]->[9]
-        || $lastmod < [stat "$ROOT/util/jsgen.pl"]->[9]
-       ));
-}
-