[Mapbender-commits] r9690 - trunk/mapbender/resources/db/pgsql/UTF-8/update

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2017-02-20 23:34:26 -0800 (Mon, 20 Feb 2017)
New Revision: 9690

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
Add required views

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2017-02-20 15:43:49 UTC (rev 9689)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2017-02-21 07:34:26 UTC (rev 9690)
@@ -1626,6 +1626,38 @@
 
 ALTER TABLE search_wfs_view
   OWNER TO postgres;
+
+-- View: groups_for_publishing
+
+-- DROP VIEW groups_for_publishing;
+
+CREATE OR REPLACE VIEW groups_for_publishing AS 
+
+SELECT mb_group_id AS fkey_mb_group_id, mb_group_name, mb_group_title, mb_group_country, mb_group_stateorprovince, mb_group_logo_path, mb_group_email FROM mb_group WHERE mb_group_id IN (
+
+SELECT DISTINCT f.fkey_mb_group_id
+   FROM mb_user_mb_group f, mb_user_mb_group s
+  WHERE f.mb_user_mb_group_type IN (2,3)  AND s.fkey_mb_group_id = 36 AND f.fkey_mb_user_id = s.fkey_mb_user_id
+
+);
+
+ALTER TABLE groups_for_publishing
+  OWNER TO postgres;
+
+-- View: users_for_publishing
+
+-- DROP VIEW users_for_publishing;
+
+CREATE OR REPLACE VIEW users_for_publishing AS 
+
+SELECT DISTINCT f.fkey_mb_user_id, f.fkey_mb_group_id AS primary_group_id
+   FROM mb_user_mb_group f, mb_user_mb_group s
+  WHERE f.mb_user_mb_group_type =2 AND s.fkey_mb_group_id = 36 AND f.fkey_mb_user_id = s.fkey_mb_user_id
+  ORDER BY f.fkey_mb_user_id;
+
+ALTER TABLE users_for_publishing
+  OWNER TO postgres;
+
 --f_get_responsible_organization(integer, integer)
 
 -- DROP FUNCTION f_get_responsible_organization(integer, integer);