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

[svn_mapbender at osgeo.org]

Author: astrid_emde
Date: 2009-09-27 13:21:44 -0400 (Sun, 27 Sep 2009)
New Revision: 4757

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6_to_2.7rc1_pgsql_UTF-8.sql
Log:
new tables for catalog handling

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6_to_2.7rc1_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6_to_2.7rc1_pgsql_UTF-8.sql	2009-09-27 09:37:56 UTC (rev 4756)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6_to_2.7rc1_pgsql_UTF-8.sql	2009-09-27 17:21:44 UTC (rev 4757)
@@ -359,3 +359,87 @@
 
 -- remove event handlers, are now in the script
 UPDATE gui_element SET e_attributes = '' WHERE e_id = 'resizeMapsize';
+
+
+--
+-- CSW Support - new Tables to build up CSW support
+-- 
+CREATE TABLE cat
+(
+  cat_id serial NOT NULL, -- auto generated
+  cat_version character varying(50) NOT NULL DEFAULT ''::character varying, -- get from cat version from xml - service_type_version
+  --cat_name character varying(255), -- ows-service-id:title
+  cat_title character varying(255) NOT NULL DEFAULT ''::character varying, --ows-service-id:title
+  cat_abstract text, --ows-service-id:abstract
+  --cat_keywords do we need them? There maybe a special table? - cs:keywords - another table to hold kw
+  --UploadUrl
+  cat_upload_url character varying(255),
+  fees character varying(50), --servicetype-fees
+  accessconstraints text, -- servicetype-accessconstraints
+  --ServiceProvider 
+  providername character varying(255),
+  providersite character varying(255),
+  --ServiceContact
+  individualname character varying(255),
+  positionname character varying(255),
+  --ContactInfo
+  voice character varying(255), -- Phone
+  facsimile character varying(255),
+  --Address
+  deliverypoint character varying(255),
+  city character varying(255),
+  administrativearea character varying(255),
+  postalcode character varying(255),
+  country character varying(255),
+  electronicmailaddress character varying(255),
+  --Whole Cap-doc
+  cat_getcapabilities_doc text,
+  --Information about Owner
+  cat_owner integer,
+  --Actuality
+  cat_timestamp integer
+);
+
+
+ALTER TABLE ONLY cat
+    ADD CONSTRAINT cat_pkey PRIMARY KEY (cat_id);
+
+CREATE TABLE cat_keyword (
+    fkey_cat_id integer NOT NULL,
+    fkey_keyword_id integer NOT NULL
+);
+
+ALTER TABLE ONLY cat_keyword
+    ADD CONSTRAINT pk_cat_keyword PRIMARY KEY (fkey_cat_id, fkey_keyword_id);
+
+ALTER TABLE ONLY cat_keyword
+    ADD CONSTRAINT fkey_keyword_id_fkey_cat_id FOREIGN KEY (fkey_keyword_id) REFERENCES keyword(keyword_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE ONLY cat_keyword
+    ADD CONSTRAINT fkey_cat_id_fkey_keyword_id FOREIGN KEY (fkey_cat_id) REFERENCES cat(cat_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+CREATE TABLE gui_cat
+(
+  fkey_gui_id character varying(50) NOT NULL DEFAULT ''::character varying,
+  fkey_cat_id integer NOT NULL DEFAULT 0,
+  CONSTRAINT fkey_cat_gui_id FOREIGN KEY (fkey_gui_id)
+      REFERENCES gui (gui_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE,
+  CONSTRAINT fkey_cat_cat_id FOREIGN KEY (fkey_cat_id)
+      REFERENCES cat (cat_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+CREATE TABLE cat_op_conf
+(
+  fk_cat_id integer NOT NULL,
+  param_name character varying(255) NOT NULL,
+  param_value text NOT NULL,
+  param_type character varying(255) NOT NULL,
+  CONSTRAINT pk_con_cat_op PRIMARY KEY (fk_cat_id, param_type, param_name, param_value),
+  CONSTRAINT fk_cat_conf_to_cat FOREIGN KEY (fk_cat_id)
+      REFERENCES cat (cat_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE
+);
+