comparison flys-artifacts/doc/conf/datacage.sql @ 4904:aa67a88314f2 dc-km-filter

[branch: dc-km-filter]: Merge with tip.
author Felix Wolfsteller <felix.wolfsteller@intevation.de>
date Wed, 30 Jan 2013 08:26:54 +0100
parents 1071aacd042c
children
comparison
equal deleted inserted replaced
4900:137ff80f0a01 4904:aa67a88314f2
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;

http://dive4elements.wald.intevation.org