ru.so/778781/ - full DB script

CREATE SCHEMA `test` DEFAULT COLLATE utf8_bin;


USE `test`;


CREATE TABLE `genes-g38-201505`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `Complement` int(11) NOT NULL,
  `Name` tinytext NOT NULL,
  `source` tinytext NOT NULL,
  `ENSEMBL` tinytext NOT NULL,
  `gene_version` tinytext NOT NULL,
  `gene_name` tinytext NOT NULL,
  `gene_source` tinytext NOT NULL,
  `gene_biotypeid` tinytext NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `ix_chr_left` (`chr`, `left`)
) ENGINE=MyISAM;


CREATE TABLE `repeats-g38-201505`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) DEFAULT NULL,
  `left` int(11) DEFAULT NULL,
  `right` int(11) DEFAULT NULL,
  `name` tinytext,
  PRIMARY KEY (`id`),
  INDEX `ix_chr_left` (`chr`, `left`)
) ENGINE=MyISAM;


CREATE TABLE `47k-80-80-ignore-random-noreverse`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `chr_left` (`chr`, `left`)
) ENGINE=MyISAM;


CREATE TABLE `51k-80-80-ignore-random-noreverse`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `chr_left` (`chr`, `left`)
) ENGINE=MyISAM;


CREATE TABLE `pk47-pk51-gene-repeat`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `count_k51` int(11) DEFAULT '0',
  `count_p51` int(11) DEFAULT '0',
  `count_p47` int(11) DEFAULT '0',
  `count_k47` int(11) DEFAULT '0',
  `name_left` varchar(30) NOT NULL,
  `name_right` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `pos_name` (`chr`, `left`, `right`, `name_left`, `name_right`),
  INDEX `ix_compare` (`chr`, `left`, `right`, `count_k47`, `count_k51`, `name_left`, `name_right`)
) ENGINE=MyISAM;


CREATE TABLE `pk47-pk51-gene-repeat-compare`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `count_k51` int(11) DEFAULT '0',
  `count_p51` int(11) DEFAULT '0',
  `count_p47` int(11) DEFAULT '0',
  `count_k47` int(11) DEFAULT '0',
  `name_left` varchar(30) NOT NULL,
  `name_right` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `pos_name` (`chr`, `left`, `right`, `name_left`, `name_right`),
  INDEX `ix_compare` (`chr`, `left`, `right`, `count_k47`, `count_k51`, `name_left`, `name_right`)
) ENGINE=MyISAM;


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 ;


DELIMITER $$
CREATE PROCEDURE `run_query_authors`()
BEGIN
    TRUNCATE TABLE `pk47-pk51-gene-repeat`;

    INSERT INTO `pk47-pk51-gene-repeat`
    ( `chr`, `left`, `right`, `count_k47`, `name_left`, `name_right` )
    SELECT
        a.`chr`, a.`left`, a.`right`, a.`count` as `count_k47`,
        g.`name` as `name_left`,
        r.`name` as `name_right`
    FROM
        `47k-80-80-ignore-random-noreverse` a,
        `genes-g38-201505` g,
        `repeats-g38-201505` r
    WHERE
        a.`chr`=g.`chr` and a.`chr`=r.`chr` and
        a.`left` < g.`right` and a.`left` > g.`left` and
        a.`right` < r.`right` and a.`right` > r.`left` and
        ( a.`id` between 10000 and 10100 )
    ON DUPLICATE KEY
    UPDATE `pk47-pk51-gene-repeat`.`count_k47`=a.`count`;

    INSERT INTO `pk47-pk51-gene-repeat`
    ( `chr`, `left`, `right`, `count_k51`, `name_left`, `name_right` )
    SELECT
        a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
        g.`name` as `name_left`,
        r.`name` as `name_right`
    FROM
        `51k-80-80-ignore-random-noreverse` a,
        `genes-g38-201505` g,
        `repeats-g38-201505` r
    WHERE
        a.`chr`=g.`chr` and a.`chr`=r.`chr` and
        a.`left` < g.`right` and a.`left` > g.`left` and
        a.`right` < r.`right` and a.`right` > r.`left` and
        ( a.`id` between 10000 and 10100 )
    ON DUPLICATE KEY
    UPDATE `pk47-pk51-gene-repeat`.`count_k51`=a.`count`;
END$$
DELIMITER ;


DELIMITER $$
CREATE PROCEDURE `run_query_hkeys`()
BEGIN
    TRUNCATE TABLE `pk47-pk51-gene-repeat-compare`;

    INSERT INTO `pk47-pk51-gene-repeat-compare`
    ( `chr`, `left`, `right`, `count_k47`, `name_left`, `name_right` )
    SELECT
        a.`chr`, a.`left`, a.`right`, a.`count` as `count_k47`,
        sg.`name` as `name_left`,
        sr.`name` as `name_right`
    FROM
        `47k-80-80-ignore-random-noreverse` a
        INNER JOIN `unwind-genes` g ON
            ( a.`chr` = g.`chr` ) and
            ( a.`left` div 10000 = g.`hkey` ) and
            ( g.`left` < a.`left` ) and ( a.`left` < g.`right` )
        INNER JOIN `unwind-repeats` r ON
            ( a.`chr` = r.`chr` ) and
            ( a.`right` div 10000 = r.`hkey` ) and
            ( r.`left` < a.`right` ) and ( a.`right` < r.`right` )
        INNER JOIN `genes-g38-201505` sg ON
            ( g.`gene_id` = sg.`id` )
        INNER JOIN `repeats-g38-201505` sr ON
            ( r.`repeat_id` = sr.`id` )
    WHERE
        ( a.`id` between 10000 and 10100 )
    ON DUPLICATE KEY
    UPDATE `pk47-pk51-gene-repeat-compare`.`count_k47` = a.`count`;

    INSERT INTO `pk47-pk51-gene-repeat-compare`
    ( `chr`, `left`, `right`, `count_k51`, `name_left`, `name_right` )
    SELECT
        a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
        sg.`name` as `name_left`,
        sr.`name` as `name_right`
    FROM
        `51k-80-80-ignore-random-noreverse` a
        INNER JOIN `unwind-genes` g ON
            ( a.`chr` = g.`chr` ) and
            ( a.`left` div 10000 = g.`hkey` ) and
            ( g.`left` < a.`left` ) and ( a.`left` < g.`right` )
        INNER JOIN `unwind-repeats` r ON
            ( a.`chr` = r.`chr` ) and
            ( a.`right` div 10000 = r.`hkey` ) and
            ( r.`left` < a.`right` ) and ( a.`right` < r.`right` )
        INNER JOIN `genes-g38-201505` sg ON
            ( g.`gene_id` = sg.`id` )
        INNER JOIN `repeats-g38-201505` sr ON
            ( r.`repeat_id` = sr.`id` )
    WHERE
        ( a.`id` between 10000 and 10100 )
    ON DUPLICATE KEY
    UPDATE `pk47-pk51-gene-repeat-compare`.`count_k51` = a.`count`;
END$$
DELIMITER ;


DELIMITER $$
CREATE FUNCTION `check_validity`() RETURNS varchar(50) CHARSET utf8
BEGIN
    DECLARE cnt_1 INT;
    DECLARE cnt_2 INT;
    DECLARE cnt_3 INT;
    DECLARE cnt_4 INT;
    DECLARE cnt_5 INT;

    SET cnt_1 = (SELECT COUNT(*) FROM `pk47-pk51-gene-repeat`);
    SET cnt_2 = (SELECT COUNT(*) FROM `pk47-pk51-gene-repeat-compare`);

    IF (cnt_1 != cnt_2) THEN
        RETURN 'Error 1';
    END IF;

    SET cnt_3 =
    (
        SELECT COUNT(*)
        FROM
            `pk47-pk51-gene-repeat` a
            LEFT JOIN `pk47-pk51-gene-repeat-compare` b ON
                ( a.`chr` = b.`chr` ) AND
                ( a.`left` = b.`left` ) AND
                ( a.`right` = b.`right` ) AND
                ( a.`count_k47` = b.`count_k47` ) AND
                ( a.`count_k51` = b.`count_k51` ) AND
                ( a.`name_left` = b.`name_left` ) AND
                ( a.`name_right` = b.`name_right` )
        WHERE
            ( b.`chr` IS NULL )
    );

    IF (cnt_3 > 0) THEN
        RETURN 'Error 2';
    END IF;

    SET cnt_4 =
    (
        SELECT COUNT(*)
        FROM
            `pk47-pk51-gene-repeat-compare` a
            LEFT JOIN `pk47-pk51-gene-repeat` b ON
                ( a.`chr` = b.`chr` ) AND
                ( a.`left` = b.`left` ) AND
                ( a.`right` = b.`right` ) AND
                ( a.`count_k47` = b.`count_k47` ) AND
                ( a.`count_k51` = b.`count_k51` ) AND
                ( a.`name_left` = b.`name_left` ) AND
                ( a.`name_right` = b.`name_right` )
        WHERE
            ( b.`chr` IS NULL )
    );

    IF (cnt_4 > 0) THEN
        RETURN 'Error 3';
    END IF;

    SET cnt_5 =
    (
        SELECT COUNT(*)
        FROM
            `pk47-pk51-gene-repeat-compare` a
            INNER JOIN `pk47-pk51-gene-repeat` b ON
                ( a.`chr` = b.`chr` ) AND
                ( a.`left` = b.`left` ) AND
                ( a.`right` = b.`right` ) AND
                ( a.`count_k47` = b.`count_k47` ) AND
                ( a.`count_k51` = b.`count_k51` ) AND
                ( a.`name_left` = b.`name_left` ) AND
                ( a.`name_right` = b.`name_right` )
    );

    IF (cnt_5 != cnt_1) THEN
        RETURN 'Error 4';
    END IF;

    RETURN Concat('Success! (',
        convert(cnt_1, char(10)), ';',
        convert(cnt_2, char(10)), ';',
        convert(cnt_3, char(10)), ';',
        convert(cnt_4, char(10)), ';',
        convert(cnt_5, char(10)), ')');
END$$
DELIMITER ;