Миграция с OpenWeatherMap на OpenMeteo в связи с отключением бесплатного API с 26...

[weathermon.git] / mysql / meteo_routines.sql

diff --git a/mysql/meteo_routines.sql b/mysql/meteo_routines.sql
index bdfa3ccf6190a1bb227fb81c4b82c1f4d09507c9..5f819b3f90afaf6db21623827ca65cf3c97b9267 100644 (file)
--- 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 */;
 
 /*!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 ;;
 /*!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;
 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
   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;
       end if;

+    else
+      insert into error_log(timestamp,text)
+      values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));

     end if;
     end if;

+  else
+    insert into error_log(timestamp,text)
+    values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));

   end if;
 END ;;
 DELIMITER ;
   end if;
 END ;;
 DELIMITER ;


@@ -90,4 +128,55 @@ DELIMITER ;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
 /*!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<pUpTo and reduced=pRID
+    group by sensor_id,parameter_id,floor(unix_timestamp(timestamp)/lSeconds);
+
+  select date(min(timestamp)),date(max(timestamp)) into lStart,lEnd from tmp_sensor_values;
+
+  delete from sensor_values where timestamp<pUpTo and reduced=pRID;
+
+  insert into sensor_values(sensor_id,parameter_id,timestamp,value,reduced)
+  select sensor_id,parameter_id,timestamp,value,lNewRID from tmp_sensor_values;
+
+  delete from sensors_ranges
+  where day>=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<date_add(lEnd, interval 1 day)
+  group by date(timestamp),sensor_id,parameter_id;
+
+  delete from tmp_sensor_values; 
+
+END$$
+DELIMITER ;
+
+DELIMITER $$
+CREATE DEFINER=`admin`@`%` PROCEDURE `Cron_Reduce`(pMonths integer)
+BEGIN
+  declare lTime datetime;
+  select date_sub(date_format(now(),'%Y-%m-01'), interval pMonths month) into lTime;
+  call meteo.reduce(lTime,0);
+END$$
+DELIMITER ;
+
+
+
+-- Dump completed on 2014-09-25 14:45:45