1 -- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64)
3 -- Host: localhost Database: meteo
4 -- ------------------------------------------------------
5 -- Server version 5.5.38-0ubuntu0.14.04.1
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 */;
19 -- Dumping routines for database 'meteo'
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 = '' */ ;
31 CREATE DEFINER=`admin`@`%` FUNCTION `unitconv`(pValue float, pFromUnit integer, pToUnit integer) RETURNS float
35 if pFromUnit=pToUnit then
38 select u.a,u.b,u.c into a,b,c from unit_conv u where from_unit=pFromUnit and to_unit=pToUnit;
40 set result = (pValue+a)*b+c;
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 = '' */ ;
59 CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float,pTimestamp datetime)
64 declare lTime DATETIME;
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
76 if pTimestamp is null then
77 set lTime:=current_timestamp();
79 set lTime:=pTimestamp;
82 insert into sensor_values(sensor_id,parameter_id,timestamp,value)
83 values(lSID,lSTPID,lTime,pValue);
85 set lDay:=DATE(lTime);
86 select count(*) into lCnt from calendar where sensor=lSID and day=lDay;
88 insert into calendar(day,sensor) values(lDay,lSID);
91 select count(*) into lCnt from sensors_ranges where sensor=lSID and day=lDay and parameter=lSTPID;
93 insert into sensors_ranges(day,sensor,parameter,min,max) values (lDay,lSID,lSTPID,pValue,pValue);
97 min=LEAST(min,pValue),
98 max=GREATEST(max,pValue)
100 day=lDay and sensor=lSID and parameter=lSTPID;
104 insert into error_log(timestamp,text)
105 values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
108 insert into error_log(timestamp,text)
109 values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
112 insert into error_log(timestamp,text)
113 values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
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 */;
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 */;
132 CREATE DEFINER=`admin`@`%` PROCEDURE `Reduce`(pUpTo datetime,pRID integer)
134 declare lNewRID integer;
135 declare lSeconds integer;
137 declare lStart,lEnd timestamp;
140 select seconds into lSeconds from reduce_ranges where id=lNewRID;
142 delete from tmp_sensor_values;
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)
147 where timestamp<pUpTo and reduced=pRID
148 group by sensor_id,parameter_id,floor(unix_timestamp(timestamp)/lSeconds);
150 select date(min(timestamp)),date(max(timestamp)) into lStart,lEnd from tmp_sensor_values;
152 delete from sensor_values where timestamp<pUpTo and reduced=pRID;
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;
157 delete from sensors_ranges
158 where day>=lStart and day<=lEnd;
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)
163 where timestamp>=lStart and timestamp<date_add(lEnd, interval 1 day)
164 group by date(timestamp),sensor_id,parameter_id;
166 delete from tmp_sensor_values;
172 CREATE DEFINER=`admin`@`%` PROCEDURE `Cron_Reduce`(pMonths integer)
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);
182 -- Dump completed on 2014-09-25 14:45:45