[Mapbender-commits] r4072 - branches/mifan_dev/mapbender/resources/db/pgsql

[svn_mapbender at osgeo.org]

Author: mifan
Date: 2009-06-22 12:01:10 -0400 (Mon, 22 Jun 2009)
New Revision: 4072

Added:
   branches/mifan_dev/mapbender/resources/db/pgsql/csw_schema_pgsql_2.8.sql
Log:
main schema for csw catalog

Added: branches/mifan_dev/mapbender/resources/db/pgsql/csw_schema_pgsql_2.8.sql
===================================================================
--- branches/mifan_dev/mapbender/resources/db/pgsql/csw_schema_pgsql_2.8.sql	                        (rev 0)
+++ branches/mifan_dev/mapbender/resources/db/pgsql/csw_schema_pgsql_2.8.sql	2009-06-22 16:01:10 UTC (rev 4072)
@@ -0,0 +1,91 @@
+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
+  --Relevant Operations Metadata Sections in XML
+  cat_op_getcapabilities text NOT NULL DEFAULT ''::text,
+  cat_op_describerecord text,
+  cat_op_getrecords text,
+  cat_op_getrecordbyid text,
+  --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
+);
+
+--
+-- Name: cat_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY cat
+    ADD CONSTRAINT cat_pkey PRIMARY KEY (cat_id);
+
+
+--
+-- Name: cat_keyword; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE cat_keyword (
+    fkey_cat_id integer NOT NULL,
+    fkey_keyword_id integer NOT NULL
+);
+
+--
+-- Name: pk_cat_keyword; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY cat_keyword
+    ADD CONSTRAINT pk_cat_keyword PRIMARY KEY (fkey_cat_id, fkey_keyword_id);
+
+--
+-- Name: fkey_keyword_id_fkey_cat_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+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;
+
+--
+-- Name: fkey_cat_id_fkey_keyword_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+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
+);