pysql/clean_data_in.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 ;