mysql/meteo_routines.sql

   1 -- MySQL dump 10.13  Distrib 5.5.38, for debian-linux-gnu (x86_64)
   2 --
   3 -- Host: localhost    Database: meteo
   4 -- ------------------------------------------------------
   5 -- Server version       5.5.38-0ubuntu0.14.04.1
   6
   7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
   8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
   9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  10 /*!40101 SET NAMES utf8 */;
  11 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  12 /*!40103 SET TIME_ZONE='+00:00' */;
  13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  17
  18 --
  19 -- Dumping routines for database 'meteo'
  20 --
  21 /*!50003 DROP FUNCTION IF EXISTS `unitconv` */;
  22 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  23 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  24 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  25 /*!50003 SET character_set_client  = utf8 */ ;
  26 /*!50003 SET character_set_results = utf8 */ ;
  27 /*!50003 SET collation_connection  = utf8_general_ci */ ;
  28 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  29 /*!50003 SET sql_mode              = '' */ ;
  30 DELIMITER ;;
  31 CREATE DEFINER=`admin`@`%` FUNCTION `unitconv`(pValue float, pFromUnit integer, pToUnit integer) RETURNS float
  32 BEGIN
  33   declare result float;
  34   declare a,b,c float;
  35   if pFromUnit=pToUnit then
  36     set result = pValue;
  37   else
  38     select u.a,u.b,u.c into a,b,c from unit_conv u where from_unit=pFromUnit and to_unit=pToUnit;
  39     set result=b;
  40     set result = (pValue+a)*b+c;
  41   end if;
  42   return result;
  43 END ;;
  44 DELIMITER ;
  45 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  46 /*!50003 SET character_set_client  = @saved_cs_client */ ;
  47 /*!50003 SET character_set_results = @saved_cs_results */ ;
  48 /*!50003 SET collation_connection  = @saved_col_connection */ ;
  49 /*!50003 DROP PROCEDURE IF EXISTS `submit_value` */;
  50 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  51 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  52 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  53 /*!50003 SET character_set_client  = utf8 */ ;
  54 /*!50003 SET character_set_results = utf8 */ ;
  55 /*!50003 SET collation_connection  = utf8_general_ci */ ;
  56 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  57 /*!50003 SET sql_mode              = '' */ ;
  58 DELIMITER ;;
  59 CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float,pTimestamp datetime)
  60 BEGIN
  61   declare lSTID int;
  62   declare lSID int;
  63   declare lSTPID int;
  64   declare lTime DATETIME;
  65   declare lDay DATE;
  66   declare lCnt integer;
  67   select max(id) into lSTID from sensor_types where st_name=pSType;
  68   if lSTID is not null then
  69     select max(id) into lSID from sensors where st_id=lSTID and s_id=pSID;
  70     if lSID is not null then
  71           select max(id) into lSTPID from st_parameters where st_id=lSTID and st_name=pParam;
  72       if lSTPID is not null then
  73
  74         if lSTPID>=0 then
  75
  76           if pTimestamp is null then
  77                     set lTime:=current_timestamp();
  78                   else
  79             set lTime:=pTimestamp;
  80           end if;
  81
  82           insert into sensor_values(sensor_id,parameter_id,timestamp,value)
  83             values(lSID,lSTPID,lTime,pValue);
  84
  85           set lDay:=DATE(lTime);
  86           select count(*) into lCnt from calendar where sensor=lSID and day=lDay;
  87           if lCnt=0 then
  88             insert into calendar(day,sensor) values(lDay,lSID);
  89           end if;
  90
  91           select count(*) into lCnt from sensors_ranges where sensor=lSID and day=lDay and parameter=lSTPID;
  92           if lCnt=0 then
  93             insert into sensors_ranges(day,sensor,parameter,min,max) values (lDay,lSID,lSTPID,pValue,pValue);
  94           else
  95             update sensors_ranges
  96             set
  97               min=LEAST(min,pValue),
  98               max=GREATEST(max,pValue)
  99             where
 100               day=lDay and sensor=lSID and parameter=lSTPID;
 101           end if;
 102         end if;
 103       else
 104         insert into error_log(timestamp,text)
 105         values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
 106       end if;
 107     else
 108       insert into error_log(timestamp,text)
 109       values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
 110     end if;
 111   else
 112     insert into error_log(timestamp,text)
 113     values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
 114   end if;
 115 END ;;
 116 DELIMITER ;
 117 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 118 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 119 /*!50003 SET character_set_results = @saved_cs_results */ ;
 120 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 121 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 122
 123 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 124 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 125 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 126 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 127 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 128 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 129 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 130
 131 DELIMITER $$
 132 CREATE DEFINER=`admin`@`%` PROCEDURE `Reduce`(pUpTo datetime,pRID integer)
 133 BEGIN
 134   declare lNewRID integer;
 135   declare lSeconds integer;
 136
 137   declare lStart,lEnd timestamp;
 138
 139   set lNewRID:=pRID+1;
 140   select seconds into lSeconds from reduce_ranges where id=lNewRID;
 141
 142   delete from tmp_sensor_values;
 143
 144   insert into tmp_sensor_values(sensor_id,parameter_id,timestamp,value)
 145   select sensor_id,parameter_id,from_unixtime(avg(unix_timestamp(timestamp))),avg(value)
 146     from sensor_values
 147     where timestamp<pUpTo and reduced=pRID
 148     group by sensor_id,parameter_id,floor(unix_timestamp(timestamp)/lSeconds);
 149
 150   select date(min(timestamp)),date(max(timestamp)) into lStart,lEnd from tmp_sensor_values;
 151
 152   delete from sensor_values where timestamp<pUpTo and reduced=pRID;
 153
 154   insert into sensor_values(sensor_id,parameter_id,timestamp,value,reduced)
 155   select sensor_id,parameter_id,timestamp,value,lNewRID from tmp_sensor_values;
 156
 157   delete from sensors_ranges
 158   where day>=lStart and day<=lEnd;
 159
 160   insert into sensors_ranges(day,sensor,parameter,timestamp,min,max)
 161   select date(timestamp),sensor_id,parameter_id,max(timestamp),min(value),max(value)
 162   from sensor_values
 163   where timestamp>=lStart and timestamp<date_add(lEnd, interval 1 day)
 164   group by date(timestamp),sensor_id,parameter_id;
 165
 166   delete from tmp_sensor_values;
 167
 168 END$$
 169 DELIMITER ;
 170
 171 DELIMITER $$
 172 CREATE DEFINER=`admin`@`%` PROCEDURE `Cron_Reduce`(pMonths integer)
 173 BEGIN
 174   declare lTime datetime;
 175   select date_sub(date_format(now(),'%Y-%m-01'), interval pMonths month) into lTime;
 176   call meteo.reduce(lTime,0);
 177 END$$
 178 DELIMITER ;
 179
 180
 181
 182 -- Dump completed on 2014-09-25 14:45:45