# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1313677170 0
# Node ID a007a4368768a473c247855de3c4e654763588db
# Parent  9c0f981cd22d4c793e2b755e1c13f600f873ab8a
Datacage: Added view to schema to select master artifacts.

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

diff -r 9c0f981cd22d -r a007a4368768 flys-artifacts/ChangeLog
--- 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.
diff -r 9c0f981cd22d -r a007a4368768 flys-artifacts/doc/conf/datacage.sql
--- a/flys-artifacts/doc/conf/datacage.sql	Thu Aug 18 14:13:35 2011 +0000
+++ b/flys-artifacts/doc/conf/datacage.sql	Thu Aug 18 14:19:30 2011 +0000
@@ -67,6 +67,25 @@
     UNIQUE (out_id, num, name)
 );
 
+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;
+
+-- DROP VIEW master_artifacts;
 -- DROP SEQUENCE USERS_ID_SEQ;
 -- DROP SEQUENCE COLLECTIONS_ID_SEQ;
 -- DROP SEQUENCE ARTIFACTS_ID_SEQ;