diff flys-artifacts/ChangeLog @ 1044:a007a4368768

Datacage: Added view to schema to select master artifacts. flys-artifacts/trunk@2505 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Thu, 18 Aug 2011 14:19:30 +0000
parents 9c0f981cd22d
children ba840385ad2e
line wrap: on
line diff
--- a/flys-artifacts/ChangeLog	Thu Aug 18 14:13:35 2011 +0000
+++ b/flys-artifacts/ChangeLog	Thu Aug 18 14:19:30 2011 +0000
@@ -1,3 +1,29 @@
+2011-08-18  Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+	* doc/conf/datacage.sql: Added view master_artifacts to select
+	  the master artifacts of the collections more easily.
+	  To upgrade existing database:
+
+	    CREATE VIEW master_artifacts AS
+	        SELECT a2.id             AS id,
+	               a2.gid            AS gid,
+	               a2.state          AS state,
+	               a2.creation       AS creation,
+	               ci2.collection_id AS collection_id
+	        FROM   collection_items ci2 
+	               JOIN artifacts a2 
+	                 ON ci2.artifact_id = a2.id 
+	               JOIN (SELECT ci.collection_id AS c_id, 
+	                            MIN(a.creation)  AS oldest_a 
+	                     FROM   collection_items ci 
+	                            JOIN artifacts a 
+	                              ON ci.artifact_id = a.id 
+	                     GROUP  BY ci.collection_id) o 
+	                 ON o.c_id = ci2.collection_id 
+	        WHERE  a2.creation = o.oldest_a;
+
+	 TODO: Use the view in the templates.
+
 2011-08-18  Sascha L. Teichmann <sascha.teichmann@intevation.de>
 
 	* doc/conf/meta-data-user.xml: Removed state filter because it was broken.

http://dive4elements.wald.intevation.org