X-Git-Url: https://git.rvb.name/weathermon.git/blobdiff_plain/b9870da67ab44b1dbba4a9f5467b0aeaa2fb3e4f..39baae7114fe1f630ced6750e9648728c4a2d525:/mysql/meteo_routines.sql?ds=sidebyside diff --git a/mysql/meteo_routines.sql b/mysql/meteo_routines.sql index bdfa3cc..5f819b3 100644 --- a/mysql/meteo_routines.sql +++ b/mysql/meteo_routines.sql @@ -1,10 +1,8 @@ -CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; -USE `meteo`; --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) -- --- Host: estia Database: meteo +-- Host: localhost Database: meteo -- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 +-- Server version 5.5.38-0ubuntu0.14.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -58,21 +56,61 @@ DELIMITER ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; -CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float) +CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float,pTimestamp datetime) BEGIN declare lSTID int; declare lSID int; declare lSTPID int; + declare lTime DATETIME; + declare lDay DATE; + declare lCnt integer; select max(id) into lSTID from sensor_types where st_name=pSType; if lSTID is not null then select max(id) into lSID from sensors where st_id=lSTID and s_id=pSID; if lSID is not null then select max(id) into lSTPID from st_parameters where st_id=lSTID and st_name=pParam; if lSTPID is not null then - insert into sensor_values(sensor_id,parameter_id,timestamp,value) - values(lSID,lSTPID,current_timestamp(),pValue); + + if lSTPID>=0 then + + if pTimestamp is null then + set lTime:=current_timestamp(); + else + set lTime:=pTimestamp; + end if; + + insert into sensor_values(sensor_id,parameter_id,timestamp,value) + values(lSID,lSTPID,lTime,pValue); + + set lDay:=DATE(lTime); + select count(*) into lCnt from calendar where sensor=lSID and day=lDay; + if lCnt=0 then + insert into calendar(day,sensor) values(lDay,lSID); + end if; + + select count(*) into lCnt from sensors_ranges where sensor=lSID and day=lDay and parameter=lSTPID; + if lCnt=0 then + insert into sensors_ranges(day,sensor,parameter,min,max) values (lDay,lSID,lSTPID,pValue,pValue); + else + update sensors_ranges + set + min=LEAST(min,pValue), + max=GREATEST(max,pValue) + where + day=lDay and sensor=lSID and parameter=lSTPID; + end if; + end if; + else + insert into error_log(timestamp,text) + values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue)); end if; + else + insert into error_log(timestamp,text) + values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue)); end if; + else + insert into error_log(timestamp,text) + values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue)); end if; END ;; DELIMITER ; @@ -90,4 +128,55 @@ DELIMITER ; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-03-17 20:31:48 +DELIMITER $$ +CREATE DEFINER=`admin`@`%` PROCEDURE `Reduce`(pUpTo datetime,pRID integer) +BEGIN + declare lNewRID integer; + declare lSeconds integer; + + declare lStart,lEnd timestamp; + + set lNewRID:=pRID+1; + select seconds into lSeconds from reduce_ranges where id=lNewRID; + + delete from tmp_sensor_values; + + insert into tmp_sensor_values(sensor_id,parameter_id,timestamp,value) + select sensor_id,parameter_id,from_unixtime(avg(unix_timestamp(timestamp))),avg(value) + from sensor_values + where timestamp=lStart and day<=lEnd; + + insert into sensors_ranges(day,sensor,parameter,timestamp,min,max) + select date(timestamp),sensor_id,parameter_id,max(timestamp),min(value),max(value) + from sensor_values + where timestamp>=lStart and timestamp