/* Remove existing procedures */
DROP PROCEDURE IF EXISTS deleteDataSet;
DROP PROCEDURE IF EXISTS createDataSetByMetaInformation;
DROP FUNCTION IF EXISTS createDataSetByMetaInformation;
DROP PROCEDURE IF EXISTS createSubDataSetByMetaInformation;
DROP FUNCTION IF EXISTS createSubDataSetByMetaInformation;
DROP PROCEDURE IF EXISTS createDataSetByMetaInformations;
DROP FUNCTION IF EXISTS createDataSetByMetaInformations;
DROP PROCEDURE IF EXISTS createSubDataSetByMetaInformations;
DROP FUNCTION IF EXISTS createSubDataSetByMetaInformations;
DROP PROCEDURE IF EXISTS createDataSetByName;
DROP FUNCTION IF EXISTS createDataSetByName;
DROP PROCEDURE IF EXISTS createSubDataSetByName;
DROP FUNCTION IF EXISTS createSubDataSetByName;
DROP PROCEDURE IF EXISTS createDataSetBySourceDatabase;
DROP FUNCTION IF EXISTS createDataSetBySourceDatabase;
DROP PROCEDURE IF EXISTS createSubDataSetBySourceDatabase;
DROP FUNCTION IF EXISTS createSubDataSetBySourceDatabase;
DROP PROCEDURE IF EXISTS createDataSetByType;
DROP FUNCTION IF EXISTS createDataSetByType;
DROP PROCEDURE IF EXISTS createSubDataSetByType;
DROP FUNCTION IF EXISTS createSubDataSetByType;
DROP PROCEDURE IF EXISTS createDataSetByMetaInformationWithThreshold;
DROP FUNCTION IF EXISTS createDataSetByMetaInformationWithThreshold;
DROP PROCEDURE IF EXISTS createSubDataSetByMetaInformationWithThreshold;
DROP FUNCTION IF EXISTS createSubDataSetByMetaInformationWithThreshold;
DROP PROCEDURE IF EXISTS createDataSetByMetaInformationsWithThreshold;
DROP FUNCTION IF EXISTS createDataSetByMetaInformationsWithThreshold;
DROP PROCEDURE IF EXISTS createSubDataSetByMetaInformationsWithThreshold;
DROP FUNCTION IF EXISTS createSubDataSetByMetaInformationsWithThreshold;
DROP PROCEDURE IF EXISTS createDataSetByNameWithThreshold;
DROP FUNCTION IF EXISTS createDataSetByNameWithThreshold;
DROP PROCEDURE IF EXISTS createSubDataSetByNameWithThreshold;
DROP FUNCTION IF EXISTS createSubDataSetByNameWithThreshold;
DROP PROCEDURE IF EXISTS createDataSetBySourceDatabaseWithThreshold;
DROP FUNCTION IF EXISTS createDataSetBySourceDatabaseWithThreshold;
DROP PROCEDURE IF EXISTS createSubDataSetBySourceDatabaseWithThreshold;
DROP FUNCTION IF EXISTS createSubDataSetBySourceDatabaseWithThreshold;
DROP PROCEDURE IF EXISTS createDataSetByTypeWithThreshold;
DROP FUNCTION IF EXISTS createDataSetByTypeWithThreshold;
DROP PROCEDURE IF EXISTS createSubDataSetByTypeWithThreshold;
DROP FUNCTION IF EXISTS createSubDataSetByTypeWithThreshold;


/* Set the delimiter to "|", so the code in our procedures can use the normal delimiter ";" */
DELIMITER |


CREATE PROCEDURE deleteDataSet(dataSet_id VARCHAR(127) ) 
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  proc: BEGIN
        DELETE FROM Thing WHERE id = dataSet_id AND classname_id = '400';
        DELETE FROM DataSet WHERE id = dataSet_id;
        DELETE FROM DataSet_Thing WHERE dataset = dataSet_id;
  END |

CREATE PROCEDURE createDataSetByMetaInformations (meta_id VARCHAR(127), meta_id2 VARCHAR(127))
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id or meta_id2'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* DECLARE cur01 CURSOR FOR SELECT next_high FROM ID_GENERATOR; */
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByMetaInformations(',meta_id,',',meta_id2,')');


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByMetaInformations(',meta_id,',',meta_id2,')') GROUP BY dataset;
        LEAVE proc;
    END IF;

    
    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByMetaInformations(',meta_id,',',meta_id2,')'), current_id );

    INSERT INTO DataSet_Thing (dataset, thing) SELECT current_id, mit.thing FROM MetaInformation_Thing mit WHERE (metainformation = meta_id OR metainformation = meta_id2);
    
     SELECT current_id;

  END |


CREATE FUNCTION createDataSetByMetaInformations (meta_id VARCHAR(127), meta_id2 VARCHAR(127)) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id or meta_id2'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* DECLARE cur01 CURSOR FOR SELECT next_high FROM ID_GENERATOR; */
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByMetaInformations(',meta_id,',',meta_id2,')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByMetaInformations(',meta_id,',',meta_id2,')') GROUP BY dataset;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;
    
    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByMetaInformations(',meta_id,',',meta_id2,')'), current_id );
    
    
    INSERT INTO DataSet_Thing (dataset, thing) SELECT current_id, mit.thing FROM MetaInformation_Thing mit WHERE (metainformation = meta_id OR metainformation = meta_id2);
    
     RETURN current_id;

  END |


CREATE PROCEDURE createDataSetByMetaInformation (meta_id VARCHAR(127))
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* DECLARE cur01 CURSOR FOR SELECT next_high FROM ID_GENERATOR; */
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByMetaInformation(',meta_id,')');

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByMetaInformation(',meta_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;
    
    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByMetaInformation(',meta_id,')'), current_id );

    
    
    INSERT INTO DataSet_Thing (dataset, thing) SELECT current_id, mit.thing FROM MetaInformation_Thing mit WHERE metainformation = meta_id;
    
    
     /*
       BUG: wenn DataSet leer ist, wird nix zurück gegeben!
       SELECT current_id, COUNT(thing) FROM DataSet_Thing WHERE dataset = current_id GROUP BY dataset;
     */
     SELECT current_id;

  END |

  
CREATE FUNCTION createDataSetByMetaInformation (meta_id VARCHAR(127)) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* DECLARE cur01 CURSOR FOR SELECT next_high FROM ID_GENERATOR; */
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByMetaInformation(',meta_id,')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByMetaInformation(',meta_id,')') GROUP BY dataset;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByMetaInformation(',meta_id,')'), current_id );

    
    
    INSERT INTO DataSet_Thing (dataset, thing) SELECT current_id, mit.thing FROM MetaInformation_Thing mit WHERE metainformation = meta_id;
    
    
     /*
       BUG: wenn DataSet leer ist, wird nix zurück gegeben!
       SELECT current_id, COUNT(thing) FROM DataSet_Thing WHERE dataset = current_id GROUP BY dataset;
     */
     RETURN current_id;

  END |


CREATE PROCEDURE createDataSetByName (name_value VARCHAR(255))
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with the given name_value'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByName(',name_value,')');


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    
    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByName(',name_value,')') GROUP BY dataset;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
         SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByName(',name_value,')'), current_id);


    
    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(tn.thing), current_id FROM Thing_Name tn, Name n WHERE tn.name = n.id AND n.name LIKE name_value;
    
    SELECT current_id;

  END |


CREATE FUNCTION createDataSetByName (name_value VARCHAR(255)) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with the given name_value'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByName(',name_value,')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByName(',name_value,')') GROUP BY dataset;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    
    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
         SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByName(',name_value,')'), current_id);


    
    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(tn.thing), current_id FROM Thing_Name tn, Name n WHERE tn.name = n.id AND n.name LIKE name_value;
    
    RETURN current_id;

  END |


CREATE PROCEDURE createDataSetBySourceDatabase (db_name VARCHAR(127))
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with a SourceDatabase with the given db_name'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR  SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetBySourceDatabase(',db_name,')');

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetBySourceDatabase(',db_name,')') GROUP BY dataset;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetBySourceDatabase(',db_name,')') );

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;

    SELECT current_id;

  END |

CREATE FUNCTION createDataSetBySourceDatabase (db_name VARCHAR(127)) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with a SourceDatabase with the given db_name'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR  SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetBySourceDatabase(',db_name,')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetBySourceDatabase(',db_name,')') GROUP BY dataset;



    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetBySourceDatabase(',db_name,')') );

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;

    RETURN current_id;

  END |

  
  
CREATE PROCEDURE createDataSetByType (type_id MEDIUMINT(7))
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all of the given type_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByType(',type_id,')');


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    
    
    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByType(',type_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    
    SELECT current_id;
            
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByType(',type_id,')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(id), current_id FROM Thing WHERE classname_id = type_id;

    SELECT current_id;

  END |
  
  
CREATE FUNCTION createDataSetByType (type_id MEDIUMINT(7)) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all of the given type_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByType(',type_id,')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByType(',type_id,')') GROUP BY dataset;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    
    
    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
            
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByType(',type_id,')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(id), current_id FROM Thing WHERE classname_id = type_id;

    RETURN current_id;

  END |
  
  
CREATE PROCEDURE createSubDataSetByMetaInformations (meta_id VARCHAR(127), meta_id2 VARCHAR(127), dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id and a subset of the given dataset_id or meta_id2'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id  VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByMetaInformations(',meta_id,',',meta_id2,',',dataset_id,')');

    IF (dataset_id < 0) THEN
        SELECT 'Empty DataSet found';
        LEAVE proc;
    END IF;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByMetaInformations(',meta_id,',',meta_id2,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
        
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;

    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByMetaInformations(',meta_id,',',meta_id2,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE (metainformation = meta_id OR metainformation = meta_id2 ) AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    SELECT current_id;

  END |

  
CREATE FUNCTION createSubDataSetByMetaInformations (meta_id VARCHAR(127), meta_id2 VARCHAR(127), dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id and a subset of the given dataset_id or meta_id2'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByMetaInformations(',meta_id,',',meta_id2,',',dataset_id,')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByMetaInformations(',meta_id,',',meta_id2,',',dataset_id,')') GROUP BY dataset;

    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
        
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByMetaInformations(',meta_id,',',meta_id2,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE (metainformation = meta_id OR metainformation = meta_id2 ) AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    RETURN current_id;

  END |


CREATE PROCEDURE createSubDataSetByMetaInformation (meta_id VARCHAR(127), dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByMetaInformation(',meta_id,',',dataset_id,')');

    IF (dataset_id < 0) THEN
        SELECT 'Empty DataSet found';
        LEAVE proc;
    END IF;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByMetaInformation(',meta_id,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
        
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByMetaInformation(',meta_id,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE metainformation = meta_id AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    SELECT current_id;

  END |


CREATE FUNCTION createSubDataSetByMetaInformation (meta_id VARCHAR(127), dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByMetaInformation(',meta_id,',',dataset_id,')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByMetaInformation(',meta_id,',',dataset_id,')') GROUP BY dataset;

    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
        
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByMetaInformation(',meta_id,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE metainformation = meta_id AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    RETURN current_id;

  END |



CREATE PROCEDURE createSubDataSetByName (name_value VARCHAR(127), dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with the given name_value and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByName(',name_value,',',dataset_id+')');

    IF (dataset_id < 0) THEN
        SELECT 'Empty DataSet found';
        LEAVE proc;
    END IF;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByName(',name_value,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByName(',name_value,',',dataset_id+')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(tn.thing), current_id FROM Thing_Name tn, Name n, DataSet_Thing dt WHERE tn.name = n.id AND n.name LIKE name_value AND dt.thing = tn.thing AND dt.dataset = dataset_id;

    SELECT current_id;

  END |


CREATE FUNCTION createSubDataSetByName (name_value VARCHAR(127), dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with the given name_value and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByName(',name_value,',',dataset_id+')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByName(',name_value,',',dataset_id,')') GROUP BY dataset;


    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByName(',name_value,',',dataset_id+')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(tn.thing), current_id FROM Thing_Name tn, Name n, DataSet_Thing dt WHERE tn.name = n.id AND n.name LIKE name_value AND dt.thing = tn.thing AND dt.dataset = dataset_id;

    RETURN current_id;

  END |


CREATE PROCEDURE createSubDataSetBySourceDatabase (db_name VARCHAR(127), dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with a SourceDatabase with the given db_name and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR  SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetBySourceDatabase(',db_name,',',dataset_id,')');


    IF (dataset_id < 0) THEN
        SELECT 'Empty DataSet found';
        LEAVE proc;
    END IF;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetBySourceDatabase(',db_name,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetBySourceDatabase(',db_name,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND dt.thing = mt.thing AND dt.dataset = dataset_id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;

    SELECT current_id;

  END |


CREATE FUNCTION createSubDataSetBySourceDatabase (db_name VARCHAR(127), dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with a SourceDatabase with the given db_name and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR  SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetBySourceDatabase(',db_name,',',dataset_id,')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetBySourceDatabase(',db_name,',',dataset_id,')') GROUP BY dataset;


    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetBySourceDatabase(',db_name,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND dt.thing = mt.thing AND dt.dataset = dataset_id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;

    RETURN current_id;

  END |



CREATE PROCEDURE createSubDataSetByType (type_id MEDIUMINT(7), dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all of the given type_id and a subset of the given dataset_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByType(',type_id,',',dataset_id,')');
    
    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByType(',type_id,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
                
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByType(',type_id,',',dataset_id,')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(t.id), current_id FROM Thing t, DataSet_Thing dt WHERE t.classname_id = type_id AND dt.thing = t.id AND dt.dataset = dataset_id ;

    SELECT current_id;

  END |


CREATE FUNCTION createSubDataSetByType (type_id MEDIUMINT(7), dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all of the given type_id and a subset of the given dataset_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByType(',type_id,',',dataset_id,')');
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByType(',type_id,',',dataset_id,')') GROUP BY dataset;

    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;
    
    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
                
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByType(',type_id,',',dataset_id,')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(t.id), current_id FROM Thing t, DataSet_Thing dt WHERE t.classname_id = type_id AND dt.thing = t.id AND dt.dataset = dataset_id ;

    RETURN current_id;

  END |



CREATE PROCEDURE createDataSetByMetaInformationsWithThreshold (meta_id VARCHAR(127), meta_id2 VARCHAR(127), threshold INT)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* DECLARE cur01 CURSOR FOR SELECT next_high FROM ID_GENERATOR; */
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(*) FROM MetaInformation_Thing WHERE (metainformation = meta_id OR metainformation = meta_id2);


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc; 
    END IF;
    
    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,')'), current_id );

    
    
    INSERT INTO DataSet_Thing (dataset, thing) SELECT current_id, mit.thing FROM MetaInformation_Thing mit WHERE (metainformation = meta_id OR metainformation = meta_id2);
    
     SELECT current_id;

  END |


CREATE FUNCTION createDataSetByMetaInformationsWithThreshold (meta_id VARCHAR(127), meta_id2 VARCHAR(127), threshold INT) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* DECLARE cur01 CURSOR FOR SELECT next_high FROM ID_GENERATOR; */
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(*) FROM MetaInformation_Thing WHERE (metainformation = meta_id OR metainformation = meta_id2);
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,')') GROUP BY dataset;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        RETURN -1;
        LEAVE proc; 
    END IF;
    
    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,')'), current_id );

    
    
    INSERT INTO DataSet_Thing (dataset, thing) SELECT current_id, mit.thing FROM MetaInformation_Thing mit WHERE (metainformation = meta_id OR metainformation = meta_id2);
    
     RETURN current_id;

  END |


CREATE PROCEDURE createDataSetByMetaInformationWithThreshold (meta_id VARCHAR(127), threshold  INT)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* DECLARE cur01 CURSOR FOR SELECT next_high FROM ID_GENERATOR; */
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByMetaInformationWithThreshold(',meta_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(*) FROM MetaInformation_Thing WHERE metainformation = meta_id;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByMetaInformationWithThreshold(',meta_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc; 
    END IF;
    
    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByMetaInformationWithThreshold(',meta_id,')'), current_id );

    
    
    INSERT INTO DataSet_Thing (dataset, thing) SELECT current_id, mit.thing FROM MetaInformation_Thing mit WHERE metainformation = meta_id;
    
    
     /*
       BUG: wenn DataSet leer ist, wird nix zurück gegeben!
       SELECT current_id, COUNT(thing) FROM DataSet_Thing WHERE dataset = current_id GROUP BY dataset;
     */
     SELECT current_id;

  END |

  
CREATE FUNCTION createDataSetByMetaInformationWithThreshold (meta_id VARCHAR(127), threshold  INT) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* DECLARE cur01 CURSOR FOR SELECT next_high FROM ID_GENERATOR; */
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByMetaInformationWithThreshold(',meta_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(*) FROM MetaInformation_Thing WHERE metainformation = meta_id;
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByMetaInformationWithThreshold(',meta_id,')') GROUP BY dataset;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        RETURN -1;
        LEAVE proc; 
    END IF;
    
    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByMetaInformationWithThreshold(',meta_id,')'), current_id );

    
    
    INSERT INTO DataSet_Thing (dataset, thing) SELECT current_id, mit.thing FROM MetaInformation_Thing mit WHERE metainformation = meta_id;
    
    
     /*
       BUG: wenn DataSet leer ist, wird nix zurück gegeben!
       SELECT current_id, COUNT(thing) FROM DataSet_Thing WHERE dataset = current_id GROUP BY dataset;
     */
     RETURN current_id;

  END |


CREATE PROCEDURE createDataSetByNameWithThreshold (name_value VARCHAR(255), threshold  INT)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with the given name_value'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByNameWithThreshold(',name_value,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(tn.thing) FROM Thing_Name tn, Name n WHERE tn.name = n.id AND n.name LIKE name_value;



    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    
    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByNameWithThreshold(',name_value,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
         SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByNameWithThreshold(',name_value,')'), current_id);


    
    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(tn.thing), current_id FROM Thing_Name tn, Name n WHERE tn.name = n.id AND n.name LIKE name_value;
    
    SELECT current_id;

  END |


CREATE FUNCTION createDataSetByNameWithThreshold (name_value VARCHAR(255), threshold  INT) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with the given name_value'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByNameWithThreshold(',name_value,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(tn.thing) FROM Thing_Name tn, Name n WHERE tn.name = n.id AND n.name LIKE name_value;
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByNameWithThreshold(',name_value,')') GROUP BY dataset;



    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    
    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
         SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByNameWithThreshold(',name_value,')'), current_id);


    
    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(tn.thing), current_id FROM Thing_Name tn, Name n WHERE tn.name = n.id AND n.name LIKE name_value;
    
    RETURN current_id;

  END |


CREATE PROCEDURE createDataSetBySourceDatabaseWithThreshold (db_name VARCHAR(127), threshold  INT)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with a SourceDatabase with the given db_name'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR  SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetBySourceDatabaseWithThreshold(',db_name,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(mt.thing) FROM MetaInformation_Thing mt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;



    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetBySourceDatabaseWithThreshold(',db_name,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetBySourceDatabaseWithThreshold(',db_name,')') );

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;

    SELECT current_id;

  END |


CREATE FUNCTION createDataSetBySourceDatabaseWithThreshold (db_name VARCHAR(127), threshold  INT) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with a SourceDatabase with the given db_name'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR  SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetBySourceDatabaseWithThreshold(',db_name,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(mt.thing) FROM MetaInformation_Thing mt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetBySourceDatabaseWithThreshold(',db_name,')') GROUP BY dataset;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetBySourceDatabaseWithThreshold(',db_name,')') );

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;

    RETURN current_id;

  END |

  
  
CREATE PROCEDURE createDataSetByTypeWithThreshold (type_id MEDIUMINT(7), threshold  INT)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all of the given type_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByTypeWithThreshold(',type_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(id) FROM Thing WHERE classname_id = type_id;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    
    
    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByTypeWithThreshold(',type_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    
            
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByTypeWithThreshold(',type_id,')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(id), current_id FROM Thing WHERE classname_id = type_id;

    SELECT current_id;

  END |
  
  
CREATE FUNCTION createDataSetByTypeWithThreshold (type_id MEDIUMINT(7), threshold  INT) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all of the given type_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createDataSetByTypeWithThreshold(',type_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(id) FROM Thing WHERE classname_id = type_id;
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createDataSetByTypeWithThreshold(',type_id,')') GROUP BY dataset;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;
    
    
    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    
               
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createDataSetByTypeWithThreshold(',type_id,')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(id), current_id FROM Thing WHERE classname_id = type_id;

    RETURN current_id;

  END |
  
  
CREATE PROCEDURE createSubDataSetByMetaInformationsWithThreshold (meta_id VARCHAR(127), meta_id2 VARCHAR(127), threshold  INT, dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,',',dataset_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(mt.thing) FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE ( metainformation = meta_id OR metainformation = meta_id2 ) AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    IF (dataset_id < 0) THEN
        SELECT 'Empty DataSet found';
        LEAVE proc;
    END IF;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
        
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE (metainformation = meta_id OR metainformation = meta_id2 ) AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    SELECT current_id;

  END |

  
CREATE FUNCTION createSubDataSetByMetaInformationsWithThreshold (meta_id VARCHAR(127), meta_id2 VARCHAR(127), threshold  INT, dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,',',dataset_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(mt.thing) FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE ( metainformation = meta_id OR metainformation = meta_id2 ) AND dt.thing = mt.thing AND dt.dataset = dataset_id;
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,',',dataset_id,')') GROUP BY dataset;



    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
	RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
        
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByMetaInformationsWithThreshold(',meta_id,',',meta_id2,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE (metainformation = meta_id OR metainformation = meta_id2 ) AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    RETURN current_id;

  END |


CREATE PROCEDURE createSubDataSetByMetaInformationWithThreshold (meta_id VARCHAR(127), threshold  INT, dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByMetaInformationWithThreshold(',meta_id,',',dataset_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(mt.thing) FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE metainformation = meta_id AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    IF (dataset_id < 0) THEN
        SELECT 'Empty DataSet found';
        LEAVE proc;
    END IF;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByMetaInformationWithThreshold(',meta_id,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
        
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByMetaInformationWithThreshold(',meta_id,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE metainformation = meta_id AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    SELECT current_id;

  END |


CREATE FUNCTION createSubDataSetByMetaInformationWithThreshold (meta_id VARCHAR(127), threshold  INT, dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected to a organism with the given meta_id and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByMetaInformationWithThreshold(',meta_id,',',dataset_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(mt.thing) FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE metainformation = meta_id AND dt.thing = mt.thing AND dt.dataset = dataset_id;
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByMetaInformationWithThreshold(',meta_id,',',dataset_id,')') GROUP BY dataset;


    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
        
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByMetaInformationWithThreshold(',meta_id,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt WHERE metainformation = meta_id AND dt.thing = mt.thing AND dt.dataset = dataset_id;


    RETURN current_id;

  END |



CREATE PROCEDURE createSubDataSetByNameWithThreshold (name_value VARCHAR(127), threshold  INT, dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with the given name_value and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByNameWithThreshold(',name_value,',',dataset_id+')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(tn.thing) FROM Thing_Name tn, Name n, DataSet_Thing dt WHERE tn.name = n.id AND n.name LIKE name_value AND dt.thing = tn.thing AND dt.dataset = dataset_id;

    IF (dataset_id < 0) THEN
        SELECT 'Empty DataSet found';
        LEAVE proc;
    END IF;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByNameWithThreshold(',name_value,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc;
    END IF;


    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByNameWithThreshold(',name_value,',',dataset_id+')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(tn.thing), current_id FROM Thing_Name tn, Name n, DataSet_Thing dt WHERE tn.name = n.id AND n.name LIKE name_value AND dt.thing = tn.thing AND dt.dataset = dataset_id;

    SELECT current_id;

  END |


CREATE FUNCTION createSubDataSetByNameWithThreshold (name_value VARCHAR(127), threshold  INT, dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with the given name_value and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByNameWithThreshold(',name_value,',',dataset_id+')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(tn.thing) FROM Thing_Name tn, Name n, DataSet_Thing dt WHERE tn.name = n.id AND n.name LIKE name_value AND dt.thing = tn.thing AND dt.dataset = dataset_id;
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByNameWithThreshold(',name_value,',',dataset_id,')') GROUP BY dataset;

    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        RETURN -1;
        LEAVE proc;
    END IF;


    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
        
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByNameWithThreshold(',name_value,',',dataset_id+')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(tn.thing), current_id FROM Thing_Name tn, Name n, DataSet_Thing dt WHERE tn.name = n.id AND n.name LIKE name_value AND dt.thing = tn.thing AND dt.dataset = dataset_id;

    RETURN current_id;

  END |


CREATE PROCEDURE createSubDataSetBySourceDatabaseWithThreshold (db_name VARCHAR(127), threshold  INT, dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with a SourceDatabase with the given db_name and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR  SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetBySourceDatabaseWithThreshold(',db_name,',',dataset_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(mt.thing) FROM MetaInformation_Thing mt, DataSet_Thing dt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND dt.thing = mt.thing AND dt.dataset = dataset_id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;


    IF (dataset_id < 0) THEN
        SELECT 'Empty DataSet found';
        LEAVE proc;
    END IF;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetBySourceDatabaseWithThreshold(',db_name,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetBySourceDatabaseWithThreshold(',db_name,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND dt.thing = mt.thing AND dt.dataset = dataset_id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;

    SELECT current_id;

  END |


CREATE FUNCTION createSubDataSetBySourceDatabaseWithThreshold (db_name VARCHAR(127), threshold  INT, dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all connected with a SourceDatabase with the given db_name and a subset of the given dataset_id'
  proc:BEGIN
        /* temporary variable */
    DECLARE current_id INT DEFAULT 1;
    DECLARE current_size INT DEFAULT 0;

    DECLARE cur0 CURSOR FOR  SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetBySourceDatabaseWithThreshold(',db_name,',',dataset_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(mt.thing) FROM MetaInformation_Thing mt, DataSet_Thing dt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND dt.thing = mt.thing AND dt.dataset = dataset_id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetBySourceDatabaseWithThreshold(',db_name,',',dataset_id,')') GROUP BY dataset;


    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;


    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_id;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetBySourceDatabaseWithThreshold(',db_name,',',dataset_id,')'), current_id);


    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(mt.thing), current_id FROM MetaInformation_Thing mt, DataSet_Thing dt, SourceDatabase s, Thing_Name tn, Name n WHERE mt.metainformation = s.id AND dt.thing = mt.thing AND dt.dataset = dataset_id AND s.id = tn.thing AND tn.name = n.id AND n.name LIKE db_name;

    RETURN current_id;

  END |



CREATE PROCEDURE createSubDataSetByTypeWithThreshold (type_id MEDIUMINT(7), threshold  INT, dataset_id VARCHAR(127) )
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all of the given type_id and a subset of the given dataset_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByTypeWithThreshold(',type_id,',',dataset_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(t.id) FROM Thing t, DataSet_Thing dt WHERE t.classname_id = type_id AND dt.thing = t.id AND dt.dataset = dataset_id ;
    
    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        SELECT dt.dataset, COUNT(dt.thing) FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByTypeWithThreshold(',type_id,',',dataset_id,')') GROUP BY dataset;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        SELECT CONCAT('Size is bigger then threshold: ',current_size);
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
                
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByTypeWithThreshold(',type_id,',',dataset_id,')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(t.id), current_id FROM Thing t, DataSet_Thing dt WHERE t.classname_id = type_id AND dt.thing = t.id AND dt.dataset = dataset_id ;

    SELECT current_id;

  END |


CREATE FUNCTION createSubDataSetByTypeWithThreshold (type_id MEDIUMINT(7), threshold  INT, dataset_id VARCHAR(127) ) RETURNS VARCHAR(127)
  LANGUAGE SQL 
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT 'Create a DataSet of Thing object, which are all of the given type_id and a subset of the given dataset_id'
  proc:BEGIN
    /* temporary variable */
    DECLARE current_id VARCHAR(127);
    DECLARE current_size INT DEFAULT 0;


    DECLARE cur0 CURSOR FOR SELECT MAX(max_value)+1 FROM DataSet WHERE 1;
    
    /* result set cursor, will be initialized later */
    DECLARE cur2 CURSOR FOR SELECT COUNT(id) FROM DataSet WHERE description = CONCAT('createSubDataSetByTypeWithThreshold(',type_id,',',dataset_id,')');
    DECLARE cur3 CURSOR FOR SELECT DISTINCT COUNT(t.id) FROM Thing t, DataSet_Thing dt WHERE t.classname_id = type_id AND dt.thing = t.id AND dt.dataset = dataset_id ;
    DECLARE cur4 CURSOR FOR SELECT dt.dataset FROM DataSet_Thing dt, DataSet d WHERE dt.dataset = d.id AND d.description = CONCAT('createSubDataSetByTypeWithThreshold(',type_id,',',dataset_id,')') GROUP BY dataset;
    
    IF (dataset_id < 0) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur2;
    FETCH cur2 INTO current_size;
    CLOSE cur2;

    IF (current_size > 0) THEN
        OPEN cur4;
        FETCH cur4 INTO current_id;
        CLOSE cur4;
        RETURN current_size;
        LEAVE proc;
    END IF;


    OPEN cur3;
    FETCH cur3 INTO current_size;
    CLOSE cur3;

    IF (current_size > threshold) THEN
        RETURN -1;
        LEAVE proc;
    END IF;

    OPEN cur0;
    FETCH cur0 INTO current_id;
    CLOSE cur0;
                
    
    IF (current_id IS NULL) THEN
        SET current_id = "1";
    END IF;
    
    INSERT INTO Thing (id, classname_id, accessionnumber, timestamp) VALUES (current_id, '400', current_id, NOW());  
    INSERT INTO DataSet (id, description, max_value) VALUES (current_id, CONCAT('createSubDataSetByTypeWithThreshold(',type_id,',',dataset_id,')'), current_id);

    INSERT INTO DataSet_Thing (thing, dataset) SELECT DISTINCT(t.id), current_id FROM Thing t, DataSet_Thing dt WHERE t.classname_id = type_id AND dt.thing = t.id AND dt.dataset = dataset_id ;

    RETURN current_id;

  END |


/* Reset the delimiter to ";" */
DELIMITER ;