[Mapbender-commits] r4458 - trunk/mapbender/resources/db/update

[svn_mapbender at osgeo.org]

Author: christoph
Date: 2009-07-30 11:04:27 -0400 (Thu, 30 Jul 2009)
New Revision: 4458

Modified:
   trunk/mapbender/resources/db/update/update_2.7.sql
Log:
http://trac.osgeo.org/mapbender/ticket/506

Modified: trunk/mapbender/resources/db/update/update_2.7.sql
===================================================================
--- trunk/mapbender/resources/db/update/update_2.7.sql	2009-07-29 15:54:51 UTC (rev 4457)
+++ trunk/mapbender/resources/db/update/update_2.7.sql	2009-07-30 15:04:27 UTC (rev 4458)
@@ -123,3 +123,97 @@
 INSERT INTO gui_element_vars(fkey_gui_id, fkey_e_id, var_name, var_value, context, var_type) VALUES( 'gui2', 'overview', 'skipWmsIfSrsNotSupported', '0', 'if set to 1, it skips the WMS request if the current SRS is not supported by the WMS; if set to 0, the WMS is always queried. Default is 0, because of backwards compatibility' ,'var');
 INSERT INTO gui_element_vars(fkey_gui_id, fkey_e_id, var_name, var_value, context, var_type) VALUES( 'gui_digitize', 'overview', 'skipWmsIfSrsNotSupported', '0', 'if set to 1, it skips the WMS request if the current SRS is not supported by the WMS; if set to 0, the WMS is always queried. Default is 0, because of backwards compatibility' ,'var');
 
+
+---------------------------------------------MONITORING BEGIN
+-- Index: idx_mb_monitor_status
+
+-- DROP INDEX idx_mb_monitor_status;
+
+CREATE INDEX idx_mb_monitor_status
+  ON mb_monitor
+  USING btree
+  (status);
+
+-- Index: idx_mb_monitor_upload_id
+
+-- DROP INDEX idx_mb_monitor_upload_id;
+
+CREATE INDEX idx_mb_monitor_upload_id
+  ON mb_monitor
+  USING btree
+  (upload_id);
+
+
+-- Table: mb_wms_availability
+
+-- DROP TABLE mb_wms_availability;
+
+CREATE TABLE mb_wms_availability
+(
+  fkey_wms_id integer,
+  fkey_upload_id character varying,
+  last_status integer,
+  availability real,
+  image integer,
+  status_comment character varying,
+  average_resp_time real,
+  upload_url character varying,
+  map_url character varying,
+  CONSTRAINT mb_wms_availability_fkey_wms_id_wms_id FOREIGN KEY (fkey_wms_id)
+      REFERENCES wms (wms_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE
+)
+WITH OIDS;
+
+-- Function: mb_monitor_after()
+
+-- DROP FUNCTION mb_monitor_after();
+
+CREATE OR REPLACE FUNCTION mb_monitor_after()
+  RETURNS "trigger" AS
+$BODY$DECLARE
+   availability_new REAL;
+   average_res_cap REAL;
+   count_monitors REAL;
+    BEGIN
+     IF TG_OP = 'UPDATE' THEN
+     
+     count_monitors := count(fkey_wms_id) from mb_monitor where fkey_wms_id=NEW.fkey_wms_id;
+      --the following should be adopted if the duration of storing is changed!!!
+      average_res_cap := ((select average_resp_time from mb_wms_availability where fkey_wms_id=NEW.fkey_wms_id)*count_monitors+(NEW.timestamp_end-NEW.timestamp_begin))/(count_monitors+1);
+
+     IF NEW.status > -1 THEN --service gives caps
+      availability_new := round(cast(((select availability from mb_wms_availability where fkey_wms_id=NEW.fkey_wms_id)*count_monitors + 100)/(count_monitors+1) as numeric),2);
+     ELSE --service has problems with caps
+      availability_new := round(cast(((select availability from mb_wms_availability where fkey_wms_id=NEW.fkey_wms_id)*count_monitors)/(count_monitors+1) as numeric),2);
+     END IF;
+
+      UPDATE mb_wms_availability SET average_resp_time=average_res_cap,last_status=NEW.status, availability=availability_new, image=NEW.image, status_comment=NEW.status_comment,upload_url=NEW.upload_url,map_url=NEW.map_url WHERE mb_wms_availability.fkey_wms_id=NEW.fkey_wms_id;
+      RETURN NEW;
+     END IF;
+     IF TG_OP = 'INSERT' THEN
+
+	IF (select count(fkey_wms_id) from mb_wms_availability where fkey_wms_id=NEW.fkey_wms_id) > 0  then -- service is not new
+			UPDATE mb_wms_availability set fkey_upload_id=NEW.upload_id,last_status=NEW.status,status_comment=NEW.status_comment,upload_url=NEW.upload_url where fkey_wms_id=NEW.fkey_wms_id;
+		else --service has not yet been monitored
+			INSERT INTO mb_wms_availability (fkey_upload_id,fkey_wms_id,last_status,status_comment,upload_url,map_url,average_resp_time,availability) VALUES (NEW.upload_id,NEW.fkey_wms_id,NEW.status,NEW.status_comment,NEW.upload_url::text,NEW.map_url,0,100);
+		end if;
+
+      RETURN NEW;
+     END IF;
+    END;
+$BODY$
+  LANGUAGE 'plpgsql' VOLATILE;
+
+
+-- Trigger: mb_monitor_after on mb_monitor
+
+-- DROP TRIGGER mb_monitor_after ON mb_monitor;
+
+CREATE TRIGGER mb_monitor_after
+  AFTER INSERT OR UPDATE
+  ON mb_monitor
+  FOR EACH ROW
+  EXECUTE PROCEDURE mb_monitor_after();
+
+---------------------------------------------MONITORING END