pysql/sql_procedures.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();