Mercurial > dive4elements > river
changeset 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 | ad9f1bdd51ba |
| children | e1566938d04c |
| files | flys-artifacts/doc/conf/datacage.sql |
| diffstat | 1 files changed, 33 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-artifacts/doc/conf/datacage.sql Tue Jan 29 11:49:55 2013 +0100 +++ b/flys-artifacts/doc/conf/datacage.sql Tue Jan 29 17:06:50 2013 +0100 @@ -85,7 +85,40 @@ ON o.c_id = ci2.collection_id WHERE a2.creation = o.oldest_a; +CREATE VIEW master_artifacts_range AS + SELECT ma.id AS id, + ma.gid AS gid, + ma.state AS state, + ma.creation AS creation, + ma.collection_id AS collection_id, + mam.ld_mode AS ld_mode, + mal.ld_locations AS ld_locations, + maf.ld_from AS ld_from, + mat.ld_to AS ld_to + FROM master_artifacts ma + LEFT JOIN (SELECT ad.v AS ld_mode, + ad.artifact_id AS artifact_id + FROM artifact_data ad + WHERE ad.k = 'ld_mode') mam + ON mam.artifact_id = ma.id + LEFT JOIN (SELECT ad.v AS ld_locations, + ad.artifact_id AS artifact_id + FROM artifact_data ad + WHERE ad.k = 'ld_locations') mal + ON mal.artifact_id = ma.id + LEFT JOIN (SELECT ad.v AS ld_from, + ad.artifact_id AS artifact_id + FROM artifact_data ad + WHERE ad.k = 'ld_from') maf + ON maf.artifact_id = ma.id + LEFT JOIN (SELECT ad.v AS ld_to, + ad.artifact_id AS artifact_id + FROM artifact_data ad + WHERE ad.k = 'ld_to') mat + ON mat.artifact_id = ma.id; + -- DROP VIEW master_artifacts; +-- DROP VIEW master_artifacts_range; -- DROP SEQUENCE USERS_ID_SEQ; -- DROP SEQUENCE COLLECTIONS_ID_SEQ; -- DROP SEQUENCE ARTIFACTS_ID_SEQ;