diff 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
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;

http://dive4elements.wald.intevation.org