UpdateJobTag.sql

CREATE PROCEDURE UpdateJobTag(IN tag_id INT)
BEGIN

DECLARE cur CURSOR FOR SELECT id FROM job WHERE job_desc LIKE CONCAT('%', @tag_name, '%');
DECLARE job_id INT;

OPEN cur;

BEGIN
    DECLARE exit_flag BOOLEAN DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_flag=True;

    cloop: LOOP    
    IF exit_flag THEN
        LEAVE cloop;
    END IF;
    FETCH cur INTO job_id;

    INSERT IGNORE INTO job_tag VALUES (job_id, tag_id);
    END LOOP cloop;
END;
CLOSE cur;

END

UpdateJobTag

When inserting new TAG, we'll search JOB descriptions and add matching JOB_TAG.

BEGIN

DECLARE job_id INT;
DECLARE tag_id_var INT;
DECLARE cur CURSOR FOR SELECT id FROM JOB WHERE LOWER(text_description) LIKE LOWER(CONCAT('%', tag_name, '%'));

OPEN cur;

BEGIN
    DECLARE exit_flag BOOLEAN DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_flag=True;
    INSERT IGNORE INTO TAG (tag, tag_type) VALUES (tag_name, "skills");
    SELECT id INTO tag_id_var FROM TAG WHERE tag = tag_name;
    DELETE FROM JOB_TAG WHERE tag_id=tag_id_var;

    cloop: LOOP    
    IF exit_flag THEN
        LEAVE cloop;
    END IF;
    FETCH cur INTO job_id;
    INSERT IGNORE INTO JOB_TAG (job_id, tag_id) VALUES (job_id, tag_id_var);
    END LOOP cloop;
END;
CLOSE cur;

END

JobDescriptionHook

When JOB description is updated, we will update the JOB_TAGS based on new description

1. delete unmatched tags
2. TODO: add new matching tags

BEGIN

DECLARE tag_id_var INT;
DECLARE tag_name_var VARCHAR(250);
DECLARE tag_exist_in_jd INT;
DECLARE cur CURSOR FOR SELECT jt.tag_id as tag_id, t.tag as tag_name FROM TAG as t JOIN JOB_TAG as jt ON t.id = jt.tag_id
        WHERE jt.job_id = job_id_var;

OPEN cur;

BEGIN
    DECLARE exit_flag BOOLEAN DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_flag=True;

    cloop: LOOP    
    IF exit_flag THEN
        LEAVE cloop;
    END IF;
    FETCH cur INTO tag_id_var, tag_name_var;

    SELECT count(1) INTO tag_exist_in_jd FROM JOB WHERE LOWER(text_description) LIKE LOWER(CONCAT('%', tag_name_var, '%')) AND id = job_id_var;

    IF tag_exist_in_jd < 1 THEN
        DELETE FROM JOB_TAG WHERE job_id=job_id_var AND tag_id=tag_id_var;
    END IF;    

    END LOOP cloop;
END;
CLOSE cur;

END