1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374 |
- 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();
|