ru.so/778781/ - tables and procedures

CREATE TABLE `unwind-genes`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `hkey` int(11) NOT NULL,
  `gene_id` int(11) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX (`chr`, `hkey`, `gene_id`)
) ENGINE=MyISAM;


CREATE TABLE `unwind-repeats`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `hkey` int(11) NOT NULL,
  `repeat_id` int(11) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX (`chr`, `hkey`, `repeat_id`)
) ENGINE=MyISAM;


DELIMITER $$
CREATE PROCEDURE `proc_unwind_genes`()
BEGIN
    DECLARE fetched INT DEFAULT TRUE;
    DECLARE v_chr VARCHAR(2);
    DECLARE v_left INT;
    DECLARE v_right INT;
    DECLARE v_id INT;
    DECLARE hkey INT;
    DECLARE hkey_max INT;
    DECLARE cur_genes CURSOR FOR
        SELECT `chr`, `left`, `right`, `id`
        FROM `genes-g38-201505`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = FALSE;

    TRUNCATE TABLE `unwind-genes`;

    OPEN cur_genes;
    FETCH cur_genes INTO v_chr, v_left, v_right, v_id;

    WHILE (fetched) DO
        SET hkey = v_left div 10000;
        SET hkey_max = v_right div 10000;

        WHILE (hkey <= hkey_max) DO
            INSERT INTO `unwind-genes`
            ( `chr`, `hkey`, `left`, `right`, `gene_id` )
            VALUES
            ( v_chr, hkey, v_left, v_right, v_id );

            SET hkey = hkey + 1;
        END WHILE;

        FETCH cur_genes INTO v_chr, v_left, v_right, v_id;
    END WHILE;

    CLOSE cur_genes;
END$$
DELIMITER ;


DELIMITER $$
CREATE PROCEDURE `proc_unwind_repeats`()
BEGIN
    DECLARE fetched INT DEFAULT TRUE;
    DECLARE v_chr VARCHAR(2);
    DECLARE v_left INT;
    DECLARE v_right INT;
    DECLARE v_id INT;
    DECLARE hkey INT;
    DECLARE hkey_max INT;
    DECLARE cur_repeats CURSOR FOR
        SELECT `chr`, `left`, `right`, `id`
        FROM `repeats-g38-201505`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = FALSE;

    TRUNCATE TABLE `unwind-repeats`;

    OPEN cur_repeats;
    FETCH cur_repeats INTO v_chr, v_left, v_right, v_id;

    WHILE (fetched) DO
        SET hkey = v_left div 10000;
        SET hkey_max = v_right div 10000;

        WHILE (hkey <= hkey_max) DO
            INSERT INTO `unwind-repeats`
            ( `chr`, `hkey`, `left`, `right`, `repeat_id` )
            VALUES
            ( v_chr, hkey, v_left, v_right, v_id );

            SET hkey = hkey + 1;
        END WHILE;

        FETCH cur_repeats INTO v_chr, v_left, v_right, v_id;
    END WHILE;

    CLOSE cur_repeats;
END$$
DELIMITER ;