Mercurial > dive4elements > river
comparison flys-artifacts/doc/conf/datacage.sql @ 4901:1071aacd042c
Added view to datacage db to get location/distance information.
| author | Raimund Renkert <rrenkert@intevation.de> |
|---|---|
| date | Tue, 29 Jan 2013 17:06:50 +0100 |
| parents | a007a4368768 |
| children |
comparison
equal
deleted
inserted
replaced
| 4898:ad9f1bdd51ba | 4901:1071aacd042c |
|---|---|
| 83 ON ci.artifact_id = a.id | 83 ON ci.artifact_id = a.id |
| 84 GROUP BY ci.collection_id) o | 84 GROUP BY ci.collection_id) o |
| 85 ON o.c_id = ci2.collection_id | 85 ON o.c_id = ci2.collection_id |
| 86 WHERE a2.creation = o.oldest_a; | 86 WHERE a2.creation = o.oldest_a; |
| 87 | 87 |
| 88 CREATE VIEW master_artifacts_range AS | |
| 89 SELECT ma.id AS id, | |
| 90 ma.gid AS gid, | |
| 91 ma.state AS state, | |
| 92 ma.creation AS creation, | |
| 93 ma.collection_id AS collection_id, | |
| 94 mam.ld_mode AS ld_mode, | |
| 95 mal.ld_locations AS ld_locations, | |
| 96 maf.ld_from AS ld_from, | |
| 97 mat.ld_to AS ld_to | |
| 98 FROM master_artifacts ma | |
| 99 LEFT JOIN (SELECT ad.v AS ld_mode, | |
| 100 ad.artifact_id AS artifact_id | |
| 101 FROM artifact_data ad | |
| 102 WHERE ad.k = 'ld_mode') mam | |
| 103 ON mam.artifact_id = ma.id | |
| 104 LEFT JOIN (SELECT ad.v AS ld_locations, | |
| 105 ad.artifact_id AS artifact_id | |
| 106 FROM artifact_data ad | |
| 107 WHERE ad.k = 'ld_locations') mal | |
| 108 ON mal.artifact_id = ma.id | |
| 109 LEFT JOIN (SELECT ad.v AS ld_from, | |
| 110 ad.artifact_id AS artifact_id | |
| 111 FROM artifact_data ad | |
| 112 WHERE ad.k = 'ld_from') maf | |
| 113 ON maf.artifact_id = ma.id | |
| 114 LEFT JOIN (SELECT ad.v AS ld_to, | |
| 115 ad.artifact_id AS artifact_id | |
| 116 FROM artifact_data ad | |
| 117 WHERE ad.k = 'ld_to') mat | |
| 118 ON mat.artifact_id = ma.id; | |
| 119 | |
| 88 -- DROP VIEW master_artifacts; | 120 -- DROP VIEW master_artifacts; |
| 121 -- DROP VIEW master_artifacts_range; | |
| 89 -- DROP SEQUENCE USERS_ID_SEQ; | 122 -- DROP SEQUENCE USERS_ID_SEQ; |
| 90 -- DROP SEQUENCE COLLECTIONS_ID_SEQ; | 123 -- DROP SEQUENCE COLLECTIONS_ID_SEQ; |
| 91 -- DROP SEQUENCE ARTIFACTS_ID_SEQ; | 124 -- DROP SEQUENCE ARTIFACTS_ID_SEQ; |
| 92 -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ; | 125 -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ; |
| 93 -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ; | 126 -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ; |