Mercurial > dive4elements > river
changeset 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 |
| files | flys-artifacts/ChangeLog flys-artifacts/doc/conf/datacage.sql |
| diffstat | 2 files changed, 45 insertions(+), 0 deletions(-) [+] |
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.
--- 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;