Normalize Table

• Tweet
• 

        // Normalize table by creating a child table with the unique field and each value of the comma separated column.

DROP TABLE IF EXISTS SplitValues;
DROP PROCEDURE IF EXISTS split_string;
DROP PROCEDURE IF EXISTS normalize_table;

CREATE TABLE `SplitValues` (
  `cid` varchar(40),
  `value` varchar(500)
) ENGINE=MyISAM;
 
DELIMITER //

CREATE PROCEDURE split_string (pcd TEXT, pinput TEXT, pdmtr VARCHAR(10))
DETERMINISTIC
BEGIN
    DECLARE cur_position INT DEFAULT 1;
    DECLARE remainder TEXT;
    DECLARE cur_string VARCHAR(1000);
    DECLARE delimiter_length TINYINT UNSIGNED;

    SET remainder = pinput;
    SET delimiter_length = CHAR_LENGTH(pdmtr);

    WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
        SET cur_position = INSTR(remainder, pdmtr);
        IF cur_position = 0 THEN
            SET cur_string = remainder;
        ELSE
            SET cur_string = LEFT(remainder, cur_position - 1);
        END IF;
        IF TRIM(cur_string) != '' THEN
            INSERT INTO SplitValues (cid, value) VALUES (pcd, cur_string);
        END IF;
        SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);
    END WHILE;
END
//

CREATE PROCEDURE normalize_table (ptable VARCHAR(64), pkey TEXT, pvalue TEXT, pdel VARCHAR(10))
DETERMINISTIC
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE vkey,vvalue TEXT;
    SET @vquery = CONCAT("CREATE VIEW tmpnormview AS SELECT ", pkey,",",pvalue," FROM ",ptable);

    DROP VIEW IF EXISTS tmpnormview;
    PREPARE stmt FROM @vquery;
    EXECUTE stmt;
   
    BEGIN
        DECLARE cur CURSOR FOR SELECT * FROM tmpnormview;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        OPEN cur;
        REPEAT
            FETCH cur INTO vkey,vvalue;
            IF NOT done THEN
                CALL split_string(vkey,vvalue,pdel);
            END IF;
        UNTIL done END REPEAT;
        CLOSE cur;
    END;
    DROP VIEW tmpnormview;
    DEALLOCATE PREPARE stmt;
END
//

DELIMITER ;

Tags:
• forge
• MySQL
• oksoft
• shell_script