Mercurial > dive4elements > river
diff flys-artifacts/doc/conf/datacage.sql @ 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 | 02c327ffbad7 |
children | 1071aacd042c |
line wrap: on
line diff
--- 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;