74 lines
2.3 KiB
SQL
74 lines
2.3 KiB
SQL
DROP PROCEDURE IF EXISTS insert_topic;
|
|
DELIMITER //
|
|
CREATE PROCEDURE insert_topic(vtopic varchar(64),newid smallint unsigned)
|
|
begin
|
|
declare tpos tinyint;
|
|
declare feldname varchar(64);
|
|
DECLARE bcfgDone INT;
|
|
DECLARE cfg_curs CURSOR FOR SELECT cfg_topic.feld,cfg_topic.pos FROM cfg_topic where cfg_topic.minlength is null or cfg_topic.minlength <= (select (length(vtopic) - length(replace(vtopic,'/','')) +1));
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bcfgDone = 1;
|
|
OPEN cfg_curs;
|
|
REPEAT
|
|
fetch cfg_curs into feldname,tpos;
|
|
if (tpos < 0) then
|
|
select substring_index(substring_index(vtopic,'/',tpos),'/',1) into @feldinhalt;
|
|
else
|
|
select substring_index(substring_index(vtopic,'/',tpos),'/',-1) into @feldinhalt;
|
|
end if;
|
|
if (select count(*) from topic where topic.tid = newid and topic.feld=feldname) = 0 then
|
|
insert into topic (tid,feld,inhalt) values (newid,feldname,@feldinhalt);
|
|
end if;
|
|
UNTIL bcfgDone END REPEAT;
|
|
close cfg_curs;
|
|
end
|
|
//
|
|
DELIMITER ;
|
|
|
|
|
|
|
|
DROP PROCEDURE IF EXISTS import_data;
|
|
DELIMITER //
|
|
CREATE PROCEDURE import_data()
|
|
BEGIN
|
|
DECLARE bDone INT;
|
|
DECLARE vtopic VARCHAR(64); -- or approriate type
|
|
DECLARE curs CURSOR FOR SELECT DISTINCT topic FROM datatmp;
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
|
|
OPEN curs;
|
|
SET bDone = 0;
|
|
SELECT if(max(tid) is null,1,max(tid)+1) into @newid from topic;
|
|
SELECT @newid;
|
|
REPEAT
|
|
FETCH curs INTO vtopic;
|
|
select vtopic;
|
|
IF (select max(topic.tid) from topic where feld = 'topic' and inhalt = vtopic) is null THEN
|
|
call insert_topic(vtopic,@newid);
|
|
INSERT INTO topic (tid,feld,inhalt) VALUES (@newid,'topic',vtopic);
|
|
select @newid+1 into @newid;
|
|
ELSE
|
|
select max(topic.tid) into @actid from topic where feld='topic' and inhalt = vtopic;
|
|
END IF;
|
|
COMMIT;
|
|
insert into data_storage (time,sensortime,tid,value) select tmp.time,tmp.sensortime,@actid as tid,tmp.value from datatmp tmp where tmp.topic=vtopic;
|
|
COMMIT;
|
|
select @actid;
|
|
UNTIL bDone END REPEAT;
|
|
CLOSE curs;
|
|
END
|
|
//
|
|
DELIMITER ;
|
|
|
|
DROP PROCEDURE IF EXISTS imd;
|
|
DELIMITER //
|
|
CREATE PROCEDURE imd()
|
|
BEGIN
|
|
create table datatmp as select * from datain;
|
|
call import_data();
|
|
delete from datain where datain.time <= (select max(dt.time) from datatmp dt);
|
|
drop table datatmp;
|
|
END
|
|
//
|
|
DELIMITER ;
|
|
|
|
CREATE EVENT import_data ON SCHEDULE EVERY 1 MINUTE DO CALL imd();
|