24 lines
1013 B
SQL
24 lines
1013 B
SQL
DELIMITER |
|
|
ALTER EVENT cleandatain
|
|
ON SCHEDULE EVERY 2 MINUTE
|
|
DO
|
|
BEGIN
|
|
create temporary table tdi select * from datain where time < (select max(time) from datain);
|
|
insert into sensor_id (sensor) select distinct sensor from tdi where sensor not in (select sensor from sensor_id where sensor is not null);
|
|
insert into device_id (device) select distinct device from tdi where device not in (select device from device_id where device is not null);
|
|
insert into var_id (var) select distinct var from tdi where var not in (select var from var_id);
|
|
insert into data_storage (time,device_id,var_id,sensor_id,i2c,value) select time,did.id,vid.id,sens.id,i2c,value from tdi join var_id vid on vid.var = tdi.var join device_id did on did.device=tdi.device join sensor_id sens on sens.sensor=tdi.sensor;
|
|
delete from datain where time <= (select max(time) from tdi);
|
|
drop table tdi;
|
|
END |
|
|
DELIMITER ;
|
|
|
|
DELIMITER |
|
|
CREATE EVENT opti_datain
|
|
ON SCHEDULE EVERY 1 DAY_HOUR
|
|
DO
|
|
BEGIN
|
|
OPTIMIZE TABLE datain;
|
|
END |
|
|
DELIMITER ;
|