# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1301561922 0
# Node ID 328ef982d7680598ac3a7c65bdbefdcaad018fc8
# Parent  dd977fb7552e9b42208cbb8ef120c91875f109cf
When an artifact is touched the collections which contain the artifact are touched, too.


artifacts/trunk@1631 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r dd977fb7552e -r 328ef982d768 ChangeLog
--- a/ChangeLog	Thu Mar 31 08:42:53 2011 +0000
+++ b/ChangeLog	Thu Mar 31 08:58:42 2011 +0000
@@ -1,3 +1,22 @@
+2011-03-31	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* artifact-database/doc/schema-h2.sql, artifact-database/doc/schema-pg.sql:
+	  Added a trigger on artifacts. If an artifact is changed the last_access
+	  timestamp of the collections in which the artifact is in are updated, too.
+	  Needs testing!
+
+	  - In PostgreSQL it is done by a trigger written in plpgsql. So don't
+	    forget to the language to the database!
+
+	  - In H2 it is done by a trigger written in Java, because H2 does not
+	    offer a script level trigger support.
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/h2/CollectionAccessUpdateTrigger.java:
+	  The H2 trigger.
+
+	* artifact-database/src/main/resources/sql/org-h2-driver.properties:
+	  Added the statement which is executed if the trigger fires.
+
 2011-03-31  Ingo Weinzierl <ingo@intevation.de>
 
 	* artifact-database/src/main/java/de/intevation/artifactdatabase/ArtifactDatabaseImpl.java:
diff -r dd977fb7552e -r 328ef982d768 artifact-database/doc/schema-h2.sql
--- a/artifact-database/doc/schema-h2.sql	Thu Mar 31 08:42:53 2011 +0000
+++ b/artifact-database/doc/schema-h2.sql	Thu Mar 31 08:58:42 2011 +0000
@@ -50,4 +50,8 @@
     UNIQUE (collection_id, artifact_id)
 );
 
+CREATE TRIGGER collections_access_update_trigger AFTER UPDATE
+    ON TABLE artifacts FOR EACH ROW 
+    CALL "de.intevation.artifactdatabase.h2.CollectionAccessUpdateTrigger";
+
 COMMIT;
diff -r dd977fb7552e -r 328ef982d768 artifact-database/doc/schema-pg.sql
--- a/artifact-database/doc/schema-pg.sql	Thu Mar 31 08:42:53 2011 +0000
+++ b/artifact-database/doc/schema-pg.sql	Thu Mar 31 08:58:42 2011 +0000
@@ -50,4 +50,23 @@
     UNIQUE (collection_id, artifact_id)
 );
 
+CREATE FUNCTION collections_access_update() RETURNS trigger AS
+$$
+BEGIN
+    UPDATE collections SET last_access = current_timestamp 
+    WHERE id IN 
+        (SELECT c.id FROM collections c 
+         INNER JOIN collection_items ci ON c.id = ci.collection_id  
+         INNER JOIN artifacts a         ON a.id = ci.artifact_id 
+         WHERE a.id = NEW.id);
+    RETURN NEW;
+END;
+$$
+LANGUAGE 'plpgsql';
+
+
+CREATE TRIGGER collections_access_update_trigger AFTER UPDATE
+    ON artifacts FOR EACH ROW 
+    EXECUTE PROCEDURE collections_access_update();
+
 COMMIT;
diff -r dd977fb7552e -r 328ef982d768 artifact-database/src/main/java/de/intevation/artifactdatabase/h2/CollectionAccessUpdateTrigger.java
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/h2/CollectionAccessUpdateTrigger.java	Thu Mar 31 08:58:42 2011 +0000
@@ -0,0 +1,56 @@
+package de.intevation.artifactdatabase.h2;
+
+import org.h2.api.Trigger;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.PreparedStatement;
+
+import de.intevation.artifactdatabase.SQL;
+
+import org.apache.log4j.Logger;
+
+public class CollectionAccessUpdateTrigger
+implements   Trigger
+{
+    private static Logger logger =
+        Logger.getLogger(CollectionAccessUpdateTrigger.class);
+
+    public static final String COLLECTIONS_TOUCH_TRIGGER_FUNCTION =
+        SQL.get("collections.touch.trigger.function");
+
+    public void init(
+        Connection conn,
+        String     schemaName,
+        String     triggerName,
+        String     tableName,
+        boolean    before,
+        int        type
+    ) 
+    throws SQLException {
+        logger.debug("CollectionAccessUpdateTrigger.init");
+    }
+
+    public void fire(
+        Connection conn, 
+        Object []  oldRow,
+        Object []  newRow
+    )
+    throws SQLException {
+        logger.debug("CollectionAccessUpdateTrigger.fire");
+        PreparedStatement stmnt = conn.prepareStatement(
+            COLLECTIONS_TOUCH_TRIGGER_FUNCTION);
+        stmnt.setObject(1, newRow[0]);
+        stmnt.execute();
+        stmnt.close();
+    }
+
+    public void close() throws SQLException {
+        logger.debug("CollectionAccessUpdateTrigger.close");
+    }
+
+    public void remove() throws SQLException {
+        logger.debug("CollectionAccessUpdateTrigger.remove");
+    }
+}
+// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
diff -r dd977fb7552e -r 328ef982d768 artifact-database/src/main/resources/sql/org-h2-driver.properties
--- a/artifact-database/src/main/resources/sql/org-h2-driver.properties	Thu Mar 31 08:42:53 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-h2-driver.properties	Thu Mar 31 08:58:42 2011 +0000
@@ -84,6 +84,16 @@
     WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?)
 
 # COLLECTIONS
+
+collections.touch.trigger.function = \
+   UPDATE collections SET last_access = current_timestamp \
+   WHERE id IN \
+        (SELECT c.id FROM collections c \
+         INNER JOIN collection_items ci ON c.id = ci.collection_id  \
+         INNER JOIN artifacts a         ON a.id = ci.artifact_id \
+         WHERE a.id = ?)
+
+
 collections.touch.by.gid =\
     UPDATE collections SET last_access = CURRENT_TIMESTAMP \
         WHERE gid = ?