Mercurial > dive4elements > river
diff flys-artifacts/doc/conf/datacage.sql @ 5086:4f65d833680f dami
Merge Default onto Dami branch.
Dami is now temporarily a public merge branch to fix problems
created by the merge.
author | Andre Heinecke <aheinecke@intevation.de> |
---|---|
date | Mon, 25 Feb 2013 11:50:13 +0100 |
parents | 1071aacd042c |
children |
line wrap: on
line diff
--- a/flys-artifacts/doc/conf/datacage.sql Mon Feb 25 11:46:36 2013 +0100 +++ b/flys-artifacts/doc/conf/datacage.sql Mon Feb 25 11:50:13 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;