From 9aa6f31f9a800157dd6899e3b237316890e2872f Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sat, 5 Dec 2009 18:29:53 +0100
Subject: SQL: Call update_vncache() in a trigger

This removes the need for the dbVNCache() function in perl.
---
 lib/VNDB/DB/VN.pm            |  9 +--------
 lib/VNDB/Handler/Misc.pm     |  7 +------
 lib/VNDB/Handler/Releases.pm |  2 --
 util/dump.sql                | 17 ++++++++++++++++-
 util/updates/update_2.10.sql | 17 ++++++++++++++++-
 5 files changed, 34 insertions(+), 18 deletions(-)

diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index a5df65f2..bc99f17c 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -7,7 +7,7 @@ use Exporter 'import';
 use VNDB::Func 'gtintype';
 use Encode 'decode_utf8';
 
-our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbVNCache dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;
+our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;
 
 
 # Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, what, sort, reverse
@@ -211,13 +211,6 @@ sub dbVNImageId {
 }
 
 
-# Updates the vn.c_ columns
-sub dbVNCache {
-  my($self, @vn) = @_;
-  $self->dbExec('SELECT update_vncache(?)', $_) for (@vn);
-}
-
-
 # insert a new screenshot and return it's ID
 # (no arguments required, as Multi is responsible for filling the entry with information)
 sub dbScreenshotAdd {
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 3bcce26b..625b0463 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -342,16 +342,11 @@ sub itemmod {
   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 => 'vn extended')->[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}));
-
-  # update cached vn info when hiding an r+ page
-  $self->dbVNCache(map $_->{vid}, @{$obj->{vn}})
-    if $type eq 'r' && $act eq 'hide';
-
   $self->resRedirect("/$type$iid", 'temp');
 }
 
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index 1097f43b..724ecf4e 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -361,8 +361,6 @@ sub edit {
       ($rev) = $self->dbItemEdit(r => $rid, %opts) if !$copy && $rid;
       ($rid) = $self->dbItemAdd(r => %opts) if $copy || !$rid;
 
-      $self->dbVNCache(@$new_vn, map $_->{vid}, @$vn);
-
       return $self->resRedirect("/r$rid.$rev", 'post');
     }
   }
diff --git a/util/dump.sql b/util/dump.sql
index 70f79b2d..2e2b6cf7 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -701,7 +701,7 @@ CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCED
 -- Same as above for producers, with slight differences in the steps:
 -- There is no 2, and
 -- 3 = Producer edit of which the name, language or type differs from the previous revision
-CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
+CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
 BEGIN
   -- 1.
   IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN
@@ -751,6 +751,21 @@ CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EX
 CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
 
 
+-- 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;
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update();
+
+
 
 
 
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql
index c2437136..e034b6e8 100644
--- a/util/updates/update_2.10.sql
+++ b/util/updates/update_2.10.sql
@@ -92,7 +92,7 @@ CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCED
 
 
 DROP TRIGGER vn_relgraph_notify ON producers;
-CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
+CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
 BEGIN
   -- 1.
   IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN
@@ -167,3 +167,18 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
 $$ LANGUAGE sql;
 
 
+-- 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;
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update();
+
+
-- 
cgit v1.2.3