Mercurial > dive4elements > river
comparison 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 |
comparison
equal
deleted
inserted
replaced
| 1043:9c0f981cd22d | 1044:a007a4368768 |
|---|---|
| 65 state VARCHAR(256) NOT NULL, | 65 state VARCHAR(256) NOT NULL, |
| 66 description VARCHAR(256), | 66 description VARCHAR(256), |
| 67 UNIQUE (out_id, num, name) | 67 UNIQUE (out_id, num, name) |
| 68 ); | 68 ); |
| 69 | 69 |
| 70 CREATE VIEW master_artifacts AS | |
| 71 SELECT a2.id AS id, | |
| 72 a2.gid AS gid, | |
| 73 a2.state AS state, | |
| 74 a2.creation AS creation, | |
| 75 ci2.collection_id AS collection_id | |
| 76 FROM collection_items ci2 | |
| 77 JOIN artifacts a2 | |
| 78 ON ci2.artifact_id = a2.id | |
| 79 JOIN (SELECT ci.collection_id AS c_id, | |
| 80 MIN(a.creation) AS oldest_a | |
| 81 FROM collection_items ci | |
| 82 JOIN artifacts a | |
| 83 ON ci.artifact_id = a.id | |
| 84 GROUP BY ci.collection_id) o | |
| 85 ON o.c_id = ci2.collection_id | |
| 86 WHERE a2.creation = o.oldest_a; | |
| 87 | |
| 88 -- DROP VIEW master_artifacts; | |
| 70 -- DROP SEQUENCE USERS_ID_SEQ; | 89 -- DROP SEQUENCE USERS_ID_SEQ; |
| 71 -- DROP SEQUENCE COLLECTIONS_ID_SEQ; | 90 -- DROP SEQUENCE COLLECTIONS_ID_SEQ; |
| 72 -- DROP SEQUENCE ARTIFACTS_ID_SEQ; | 91 -- DROP SEQUENCE ARTIFACTS_ID_SEQ; |
| 73 -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ; | 92 -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ; |
| 74 -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ; | 93 -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ; |