Cacher is the code snippet organizer for pro developers

BEGIN;

DROP PROCEDURE IF EXISTS add_file_tag;

DELIMITER //
CREATE PROCEDURE add_file_tag(
    IN p_file_id int,
    IN p_tag varchar(128)
)
BEGIN
    DECLARE var_tag_id int;

    /* get tag_id, insert if not exists */
    SELECT id INTO var_tag_id FROM file_tag WHERE name=p_tag;
    IF var_tag_id IS NULL THEN
        INSERT INTO file_tag(name) VALUES(p_tag);
        SELECT id INTO var_tag_id FROM file_tag WHERE name=p_tag;
    END IF;

    /* insert data into file_file_tag */
    INSERT IGNORE INTO file_file_tag(file_id, tag_id) VALUES (p_file_id, var_tag_id);
END //
DELIMITER ;


DROP PROCEDURE IF EXISTS update_file_info;

DELIMITER //
CREATE PROCEDURE update_file_info(
    IN p_md5 char(32),
    IN p_sha256 char(64),
    IN p_file_type varchar(32),
    IN p_file_host varchar(128),
    IN p_path varchar(256),
    IN p_virus varchar(128),
    IN p_risk_score TINYINT
)
BEGIN
    DECLARE var_type_id int;
    DECLARE var_host_id int;

    SET p_md5 = UPPER(p_md5);
    SET p_sha256 = UPPER(p_sha256);

    /* get the type_id */
    IF p_file_type IS NOT NULL THEN
        SELECT id INTO var_type_id FROM file_type WHERE name=p_file_type;
        IF var_type_id IS NULL THEN
            INSERT INTO file_type(name) VALUES(p_file_type);
            SELECT id INTO var_type_id FROM file_type WHERE name=p_file_type;
        End IF;

    ELSE
        SET var_type_id = NULL;
    END IF;

    /* get host id */
    IF p_file_host IS NOT NULL THEN
        SELECT id INTO var_host_id FROM file_host WHERE name=p_file_host;
        IF var_host_id IS NULL THEN
            INSERT INTO file_host(name) VALUES(p_file_host);
            SELECT id INTO var_host_id FROM file_host WHERE name=p_file_host;
        END IF;

    ELSE
        SET var_host_id = NULL;
    END IF;

    /* insert data into file_info */

    INSERT INTO file_info(md5, sha256, type_id, host_id, path, virus, risk_score)
    VALUES (p_md5, p_sha256, var_type_id, var_host_id, p_path, p_virus, p_risk_score)
    ON duplicate key UPDATE
        id = LAST_INSERT_ID(id),
        md5=values(md5),
        sha256=values(sha256),
        type_id=values(type_id),
        host_id=values(host_id),
        path=values(path),
        virus=values(virus),
        risk_score=values(risk_score);

    SELECT LAST_INSERT_ID();
END //
DELIMITER ;

COMMIT;

DELIMITER //
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM file_info;
   END //
DELIMITER ;