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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2017-07-27 09:14:27 -0700 (Thu, 27 Jul 2017)
New Revision: 9756

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
New table to store json schemata in the mapbender database

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-07-27 16:13:51 UTC (rev 9755)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2017-07-27 16:14:27 UTC (rev 9756)
@@ -1939,5 +1939,67 @@
 
 UPDATE wms SET wms_proxy_exchange_external_urls = 1 WHERE wms_proxy_exchange_external_urls IS NULL;
 
+ -- Table: json_schema
 
+-- DROP TABLE json_schema;
 
+CREATE TABLE json_schema
+(
+  id serial NOT NULL,
+  uuid uuid,
+  version character varying(10) NOT NULL DEFAULT 'draft-04'::character varying, --draft-04, draft-05, draft-06
+  title character varying(255),
+  abstract text,
+  usecase integer, -- 1 - digitize, 2 - ...
+  geomtype integer, -- 1 - point, 2 - linestring, 3 - polygon
+  schema text,
+  created integer, --timestamp
+  updated integer, --timestamp
+  deleted integer, --timestamp
+  fkey_mb_user_id integer,
+  fkey_mb_group_id integer,
+  public boolean DEFAULT TRUE,
+  CONSTRAINT pk_json_schema_id PRIMARY KEY (id)
+)
+WITH (
+  OIDS=FALSE
+);
+
+ALTER TABLE json_schema
+  OWNER TO postgres;
+
+-- Index: idx_json_schema_id
+
+-- DROP INDEX idx_json_schema_id;
+
+CREATE INDEX idx_json_schema_id
+  ON json_schema
+  USING btree
+  (id);
+
+-- Function: update_json_schema_updated_column()
+
+-- DROP FUNCTION update_json_schema_updated_column();
+
+CREATE OR REPLACE FUNCTION update_json_schema_updated_column()
+  RETURNS trigger AS
+$BODY$
+BEGIN
+   NEW.updated = EXTRACT(EPOCH FROM NOW())::INTEGER; 
+   RETURN NEW;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+ALTER FUNCTION update_json_schema_updated_column()
+  OWNER TO postgres;
+
+-- Trigger: update_updated on json_schema
+
+-- DROP TRIGGER update_updated ON json_schema;
+
+CREATE TRIGGER update_updated
+  BEFORE UPDATE
+  ON json_schema
+  FOR EACH ROW
+  EXECUTE PROCEDURE update_json_schema_updated_column();