[Mapbender-commits] r7017 -
trunk/mapbender/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Mon Oct 4 10:04:19 EDT 2010
Author: armin11
Date: 2010-10-04 14:04:19 +0000 (Mon, 04 Oct 2010)
New Revision: 7017
Modified:
trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql
Log:
add a new colum to mb_user_wmc which is unique!
Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql 2010-10-04 10:21:20 UTC (rev 7016)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql 2010-10-04 14:04:19 UTC (rev 7017)
@@ -594,18 +594,38 @@
WHERE var_name = 'backGroundHoverColor' AND fkey_e_id = 'navFrame');
-ALTER TABLE mb_user_wmc ADD COLUMN
-wmc_timestamp_create INTEGER;
+--*****wmc adoptions****
+DROP TABLE wmc_keyword CASCADE;
+DROP TABLE wmc_md_topic_category CASCADE;
-CREATE TABLE wmc_md_topic_category (
- fkey_wmc_id character varying(20) NOT NULL,
- fkey_md_topic_category_id integer NOT NULL
+--ALTER TABLE mb_user_wmc ADD COLUMN wmc_timestamp_create INTEGER;
+--adopt mb_user_wmc to store a serial column too!
+ALTER TABLE mb_user_wmc DROP CONSTRAINT pk_user_wmc;
+ALTER TABLE mb_user_wmc DROP CONSTRAINT mb_user_wmc_pkey; --for older implementations
+--DROP SEQUENCE mb_user_wmc_wmc_serial_id_seq cascade;
+CREATE SEQUENCE mb_user_wmc_wmc_serial_id_seq;
+ALTER TABLE mb_user_wmc ADD COLUMN wmc_serial_id INTEGER;
+ALTER TABLE mb_user_wmc ALTER COLUMN wmc_serial_id SET DEFAULT nextval('mb_user_wmc_wmc_serial_id_seq');
+UPDATE mb_user_wmc SET wmc_serial_id = NEXTVAL('mb_user_wmc_wmc_serial_id_seq');
+
+
+
+-- Constraint: pk_user_wmc
+
+ALTER TABLE mb_user_wmc
+ ADD CONSTRAINT pk_user_wmc PRIMARY KEY(wmc_serial_id);
+
+CREATE TABLE wmc_keyword (
+ fkey_keyword_id INTEGER REFERENCES keyword(keyword_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ fkey_wmc_serial_id INTEGER REFERENCES mb_user_wmc(wmc_serial_id) ON DELETE CASCADE ON UPDATE CASCADE
);
-ALTER TABLE ONLY wmc_md_topic_category
- ADD CONSTRAINT wmc_md_topic_category_fkey_wmc_id_fkey FOREIGN KEY (fkey_wmc_id) REFERENCES mb_user_wmc (wmc_id) ON UPDATE CASCADE ON DELETE CASCADE;
+ALTER TABLE ONLY wmc_keyword
+ ADD CONSTRAINT pk_wmc_keyword PRIMARY KEY (fkey_wmc_serial_id, fkey_keyword_id);
+--****wmc adoptions end *****
+
-- jQuery UI
INSERT INTO gui_element (fkey_gui_id, e_id, e_pos, e_public, e_comment, e_title, e_element, e_src, e_attributes, e_left, e_top, e_width, e_height, e_z_index, e_more_styles, e_content, e_closetag, e_js_file, e_mb_mod, e_target, e_requires, e_url)
SELECT gui.gui_id,'jq_ui',1,1,'The jQuery UI core','','','','',NULL ,NULL ,NULL ,NULL ,NULL ,'','','','','../extensions/jquery-ui-1.7.2.custom/development-bundle/ui/min.ui.core.js','','',''
@@ -686,12 +706,6 @@
ADD COLUMN maxx DOUBLE PRECISION DEFAULT 0,
ADD COLUMN maxy DOUBLE PRECISION DEFAULT 0;
-CREATE TABLE wmc_keyword (
- fkey_keyword_id INTEGER REFERENCES keyword(keyword_id) ON DELETE CASCADE ON UPDATE CASCADE,
- fkey_wmc_id CHARACTER VARYING(20) REFERENCES mb_user_wmc(wmc_id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-ALTER TABLE ONLY wmc_keyword
- ADD CONSTRAINT pk_wmc_keyword PRIMARY KEY (fkey_wmc_id, fkey_keyword_id);
-- disable publish WMC and delete WMC for gui1
INSERT INTO gui_element_vars(fkey_gui_id, fkey_e_id, var_name, var_value, context, var_type)
@@ -2309,34 +2323,22 @@
--function to collect the md categories for a single wmc into a column
-CREATE OR REPLACE FUNCTION f_collect_topic_cat_wmc(character varying)
- RETURNS text AS
- $BODY$DECLARE
- i_wmc_id ALIAS FOR $1;
- topic_cat_string TEXT;
- topic_cat_record RECORD;
-BEGIN
-topic_cat_string := '';
-FOR topic_cat_record IN SELECT wmc_md_topic_category.fkey_md_topic_category_id from wmc_md_topic_category WHERE wmc_md_topic_category.fkey_wmc_id=$1 LOOP
-topic_cat_string := topic_cat_string || '{' ||topic_cat_record.fkey_md_topic_category_id || '}';
-END LOOP ;
-
-RETURN topic_cat_string;
-END;
-$BODY$
- LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_topic_cat_wmc(character varying) OWNER TO postgres;
--- Function: f_collect_searchtext_wmc(varchar)
--- DROP FUNCTION f_collect_searchtext_wmc(varchar);
+--****wmc-begin****
+DROP FUNCTION f_collect_searchtext_wmc(character varying) CASCADE;
+DROP FUNCTION f_collect_topic_cat_wmc(character varying) CASCADE;
-CREATE OR REPLACE FUNCTION f_collect_searchtext_wmc(varchar)
+-- Function: f_collect_searchtext_wmc(integer)
+
+-- DROP FUNCTION f_collect_searchtext_wmc(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_searchtext_wmc(integer)
RETURNS text AS
$BODY$
DECLARE
@@ -2346,7 +2348,7 @@
l_result TEXT;
BEGIN
l_result := '';
- l_result := l_result || (SELECT COALESCE(wmc_title, '') || ' ' || COALESCE(abstract, '') FROM mb_user_wmc WHERE wmc_id = p_wmc_id);
+ l_result := l_result || (SELECT COALESCE(wmc_title, '') || ' ' || COALESCE(abstract, '') FROM mb_user_wmc WHERE wmc_serial_id = p_wmc_id);
FOR r_keywords IN SELECT DISTINCT keyword FROM
(SELECT keyword FROM wmc_keyword L JOIN keyword K ON (K.keyword_id = L.fkey_keyword_id )
) AS __keywords__ LOOP
@@ -2359,9 +2361,134 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-ALTER FUNCTION f_collect_searchtext_wmc(varchar) OWNER TO postgres;
+ALTER FUNCTION f_collect_searchtext_wmc(integer) OWNER TO postgres;
+
+
+
+
+
+
+--adoptions for wmc categories
+CREATE TABLE wmc_custom_category
+(
+ fkey_wmc_serial_id integer NOT NULL,
+ fkey_custom_category_id integer NOT NULL,
+ CONSTRAINT wmc_custom_category_fkey_wmc_serial_id_fkey FOREIGN KEY (fkey_wmc_serial_id)
+ REFERENCES mb_user_wmc (wmc_serial_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT wmc_custom_category_fkey_custom_category_id_fkey FOREIGN KEY (fkey_custom_category_id)
+ REFERENCES custom_category (custom_category_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+
+--adopt relation for old implementations:
+--create a new one and drop the old!
+
+--new categories for publishing -> custom , inspire ?
+-- Table: wmc_custom_category
+CREATE TABLE wmc_md_topic_category
+(
+ fkey_wmc_serial_id integer NOT NULL,
+ fkey_md_topic_category_id integer NOT NULL,
+ CONSTRAINT wmc_topic_category_fkey_wmc_serial_id_fkey FOREIGN KEY (fkey_wmc_serial_id)
+ REFERENCES mb_user_wmc (wmc_serial_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT wmc_topic_category_fkey_md_topic_category_id_fkey FOREIGN KEY (fkey_md_topic_category_id)
+ REFERENCES md_topic_category (md_topic_category_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+
+--new categories for publishing -> custom , inspire ?
+-- Table: wmc_custom_category
+CREATE TABLE wmc_custom_category
+(
+ fkey_wmc_serial_id integer NOT NULL,
+ fkey_custom_category_id integer NOT NULL,
+ CONSTRAINT wmc_custom_category_fkey_wmc_serial_id_fkey FOREIGN KEY (fkey_wmc_serial_id)
+ REFERENCES mb_user_wmc (wmc_serial_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT wmc_custom_category_fkey_custom_category_id_fkey FOREIGN KEY (fkey_custom_category_id)
+ REFERENCES custom_category (custom_category_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+-- Table: wmc_inspire_category
+CREATE TABLE wmc_inspire_category
+(
+ fkey_wmc_serial_id integer NOT NULL,
+ fkey_inspire_category_id integer NOT NULL,
+ CONSTRAINT wmc_inspire_category_fkey_wmc_serial_id_fkey FOREIGN KEY (fkey_wmc_serial_id)
+ REFERENCES mb_user_wmc (wmc_serial_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT wmc_inspire_category_fkey_inspire_category_id_fkey FOREIGN KEY (fkey_inspire_category_id)
+ REFERENCES inspire_category (inspire_category_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+--functions to collect the categories into strings
+CREATE OR REPLACE FUNCTION f_collect_custom_cat_wmc(integer)
+ RETURNS text AS
+ $BODY$DECLARE
+ i_wmc_serial_id ALIAS FOR $1;
+ custom_cat_string TEXT;
+ custom_cat_record RECORD;
+BEGIN
+custom_cat_string := '';
+FOR custom_cat_record IN SELECT wmc_custom_category.fkey_custom_category_id from wmc_custom_category WHERE wmc_custom_category.fkey_wmc_serial_id=$1 LOOP
+custom_cat_string := custom_cat_string || '{' ||custom_cat_record.fkey_custom_category_id || '}';
+END LOOP ;
+RETURN custom_cat_string;
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_custom_cat_wmc(integer) OWNER TO postgres;
+--functions to collect the categories into strings
+CREATE OR REPLACE FUNCTION f_collect_inspire_cat_wmc(integer)
+ RETURNS text AS
+ $BODY$DECLARE
+ i_wmc_serial_id ALIAS FOR $1;
+ inspire_cat_string TEXT;
+ inspire_cat_record RECORD;
+BEGIN
+inspire_cat_string := '';
+FOR inspire_cat_record IN SELECT wmc_inspire_category.fkey_inspire_category_id from wmc_inspire_category WHERE wmc_inspire_category.fkey_wmc_serial_id=$1 LOOP
+inspire_cat_string := inspire_cat_string || '{' ||inspire_cat_record.fkey_inspire_category_id || '}';
+END LOOP ;
+RETURN inspire_cat_string;
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_inspire_cat_wmc(integer) OWNER TO postgres;
+
+CREATE OR REPLACE FUNCTION f_collect_topic_cat_wmc(integer)
+ RETURNS text AS
+ $BODY$DECLARE
+ i_wmc_serial_id ALIAS FOR $1;
+ topic_cat_string TEXT;
+ topic_cat_record RECORD;
+
+BEGIN
+topic_cat_string := '';
+
+FOR topic_cat_record IN SELECT wmc_md_topic_category.fkey_md_topic_category_id from wmc_md_topic_category WHERE wmc_md_topic_category.fkey_wmc_serial_id=$1 LOOP
+topic_cat_string := topic_cat_string || '{' ||topic_cat_record.fkey_md_topic_category_id || '}';
+END LOOP ;
+
+RETURN topic_cat_string;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_topic_cat_wmc(integer) OWNER TO postgres;
+
+--*******wmc end*****
+
+
+
--set mb_user_mb_group_type
--of mb_user_mb_group to 1 for root!
@@ -2382,7 +2509,7 @@
CREATE OR REPLACE VIEW search_wmc_view AS
SELECT wmc_dep.fkey_user_id as user_id, wmc_dep.wmc_id , wmc_dep.srs as wmc_srs, wmc_dep.wmc_title , wmc_dep.abstract as wmc_abstract, f_collect_searchtext_wmc(wmc_dep.wmc_id) AS searchtext
, wmc_dep.wmc_timestamp, wmc_dep.department, wmc_dep.mb_group_name,wmc_dep.mb_group_title,wmc_dep.mb_group_country, wmc_dep.mb_group_stateorprovince ,f_collect_topic_cat_wmc(wmc_dep.wmc_id) as md_topic_cats,transform(geometryfromtext(((((((((((((((((((('POLYGON(('::text || wmc_dep.minx::text) || ' '::text) || wmc_dep.miny::text) || ','::text) || wmc_dep.minx::text) || ' '::text) || wmc_dep.maxy::text) || ','::text) || wmc_dep.maxx::text) || ' '::text) || wmc_dep.maxy::text) || ','::text) || wmc_dep.maxx::text) || ' '::text) || wmc_dep.miny::text) || ','::text) || wmc_dep.minx::text) || ' '::text) || wmc_dep.miny::text) || '))'::text, regexp_replace(UPPER(wmc_dep.srs), 'EPSG:', '')::INTEGER),4326) AS the_geom, (((((wmc_dep.minx::text || ','::text) || wmc_dep.miny::text) || ','::text) || wmc_dep.maxx::text) || ','::text) || wmc_dep.maxy::text AS bbox , wmc_dep.mb_group_logo_path
- FROM ( SELECT mb_user_wmc.wmc_public, mb_user_wmc.maxy ,mb_user_wmc.maxx ,mb_user_wmc.miny ,mb_user_wmc.minx ,mb_user_wmc.srs ,mb_user_wmc.wmc_id, mb_user_wmc.wmc_title, mb_user_wmc.abstract, mb_user_wmc.wmc_timestamp, mb_user_wmc.fkey_user_id, user_dep.mb_group_id AS department, user_dep.mb_group_name,
+ FROM ( SELECT mb_user_wmc.wmc_public, mb_user_wmc.maxy ,mb_user_wmc.maxx ,mb_user_wmc.miny ,mb_user_wmc.minx ,mb_user_wmc.srs ,mb_user_wmc.wmc_serial_id as wmc_id, mb_user_wmc.wmc_title, mb_user_wmc.abstract, mb_user_wmc.wmc_timestamp, mb_user_wmc.fkey_user_id, user_dep.mb_group_id AS department, user_dep.mb_group_name,
user_dep.mb_group_title,user_dep.mb_group_country, user_dep.mb_group_stateorprovince, user_dep.mb_group_logo_path
FROM ( SELECT registrating_groups.fkey_mb_user_id AS mb_user_id, mb_group.mb_group_id, mb_group.mb_group_name, mb_group.mb_group_title, mb_group.mb_group_country, mb_group.mb_group_stateorprovince, mb_group.mb_group_logo_path
FROM registrating_groups, mb_group WHERE registrating_groups.fkey_mb_group_id = mb_group.mb_group_id) as user_dep, mb_user_wmc
More information about the Mapbender_commits
mailing list