
--------------------------------------------------
-- 07/10/2019
-- Paul 
-- Following changes are made for new "restart" feature. 
--------------------------------------------------

-- Give the name of the schema you are working on. 
SET @SCHEMA_NAME := 'cipres';

--------------------------------------------------
-- Create a new table `task_runs` 
--------------------------------------------------
CREATE TABLE task_runs 
(
    TASK_ID         BIGINT NOT NULL, 
    RUN_NUMBER      INTEGER NOT NULL,
    CREATION_DATE   DATETIME NOT NULL, 
    STATUS          INTEGER NOT NULL DEFAULT '1',
    STAGE           VARCHAR(20) NOT NULL,
    IS_TERMINAL     TINYINT(1) NOT NULL DEFAULT '0',
    IS_RESTART      TINYINT(1) NOT NULL DEFAULT '0',
    
    CONSTRAINT pk_task_runs_01 PRIMARY KEY (TASK_ID, RUN_NUMBER),

    CONSTRAINT fk_task_runs_01 FOREIGN KEY (TASK_ID) REFERENCES tasks (TASK_ID) 
        ON DELETE NO ACTION     ON UPDATE NO ACTION 
);

INSERT INTO task_runs (TASK_ID, RUN_NUMBER, CREATION_DATE, STATUS, STAGE, IS_TERMINAL) 
    SELECT TASK_ID, '1', CREATION_DATE, OK, STAGE, IS_TERMINAL 
    FROM tasks 
;


--------------------------------------------------
-- Modify `tasks` table. 
--------------------------------------------------
ALTER TABLE `tasks` DROP COLUMN `OK`;
ALTER TABLE `tasks` DROP COLUMN `STAGE`;
ALTER TABLE `tasks` DROP COLUMN `IS_TERMINAL`;


--------------------------------------------------
-- Modify `task_input_parameters` table. 
--------------------------------------------------

-- Find the FOREIGN KEY and ...
SET @KEY_NAME := 
(SELECT CONSTRAINT_NAME 
    FROM information_schema.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_SCHEMA = @SCHEMA_NAME 
        AND TABLE_NAME = 'task_input_parameters' 
        AND REFERENCED_TABLE_NAME = 'tasks')
;

SELECT @KEY_NAME;

-- Drop it here. 
ALTER TABLE `task_input_parameters` 
    DROP FOREIGN KEY `task_input_parameters_ibfk_1`
;

ALTER TABLE `task_input_parameters` 
    ADD COLUMN `RUN_NUMBER` INTEGER NOT NULL DEFAULT 1 
;

ALTER TABLE `task_input_parameters` 
    ADD FOREIGN KEY fk_task_input_parameters_01 (TASK_ID, RUN_NUMBER) 
        REFERENCES `task_runs` (TASK_ID, RUN_NUMBER) 
;

DELETE FROM task_input_parameters 
    WHERE TASK_ID NOT IN (SELECT TASK_ID FROM tasks)
;

ALTER TABLE `task_input_parameters`
    MODIFY COLUMN RUN_NUMBER INTEGER NOT NULL 
;


--------------------------------------------------
-- Modify `task_output_parameters` table. 
--------------------------------------------------
-- Give the name of the schema you are working on. 
SET @SCHEMA_NAME := 'cipres';

-- Find the FOREIGH KEY and ... 
SET @KEY_NAME := 
(SELECT CONSTRAINT_NAME 
    FROM information_schema.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_SCHEMA = @SCHEMA_NAME 
        AND TABLE_NAME = 'task_output_parameters' 
        AND REFERENCED_TABLE_NAME = 'tasks')
;

SELECT @KEY_NAME; 

-- Drop it here. 
ALTER TABLE `task_output_parameters` 
    DROP FOREIGN KEY `task_output_parameters_ibfk_1`
;

ALTER TABLE `task_output_parameters` 
    ADD COLUMN `RUN_NUMBER` INTEGER NOT NULL DEFAULT 1 
;

ALTER TABLE `task_output_parameters` 
    ADD FOREIGN KEY fk_task_output_parameters_01 (TASK_ID, RUN_NUMBER) 
        REFERENCES `task_runs` (TASK_ID, RUN_NUMBER) 
;

DELETE FROM `task_output_parameters` 
    WHERE TASK_ID NOT IN (SELECT TASK_ID FROM tasks)
;

ALTER TABLE `task_output_parameters`
    MODIFY COLUMN RUN_NUMBER INTEGER NOT NULL 
;


--------------------------------------------------
-- Modify `task_log_messages` table. 
--------------------------------------------------
-- Give the name of the schema you are working on. 
SET @SCHEMA_NAME := 'cipres';

SET @CKEY_NAME := 
(SELECT CONSTRAINT_NAME 
    FROM information_schema.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_SCHEMA = @SCHEMA_NAME 
        AND TABLE_NAME = 'task_log_messages' 
        AND REFERENCED_TABLE_NAME = 'tasks')
;

SELECT @KEY_NAME; 

ALTER TABLE `task_log_messages` 
    DROP FOREIGN KEY `task_output_parameters_ibfk_1`
;

ALTER TABLE `task_log_messages` 
    ADD COLUMN RUN_NUMBER integer NOT NULL DEFAULT 1, 
    DROP PRIMARY KEY, 
    ADD PRIMARY KEY (TASK_ID, RUN_NUMBER, MESSAGE_INDEX)
;

DELETE FROM `task_log_messages` 
    WHERE TASK_ID NOT IN (SELECT TASK_ID FROM tasks)
;

ALTER TABLE `task_log_messages` 
    ADD FOREIGN KEY fk_task_log_messages_01 (TASK_ID, RUN_NUMBER) REFERENCES `task_runs` (TASK_ID, RUN_NUMBER)
;

ALTER TABLE `task_log_messages`
    MODIFY COLUMN RUN_NUMBER INTEGER NOT NULL 
;


--------------------------------------------------
-- Modify `running_tasks` table.
--------------------------------------------------
-- DELETE FROM `running_tasks` 
--     WHERE JOBHANDLE NOT IN (SELECT JOBHANDLE FROM tasks)
-- ;

ALTER TABLE `running_tasks` 
    ADD COLUMN RUN_NUMBER INTEGER NOT NULL DEFAULT 1, 
    DROP PRIMARY KEY, 
    ADD PRIMARY KEY (JOBHANDLE, RUN_NUMBER)
;

ALTER TABLE `running_tasks` 
    MODIFY COLUMN RUN_NUMBER INTEGER NOT NULL
;

-- ALTER TABLE cipres_tmp.running_tasks 
-- 	DROP PRIMARY KEY
-- ;


--------------------------------------------------
-- Modify `running_tasks_parameters` table.
--------------------------------------------------
-- Give the name of the schema you are working on. 
SET @SCHEMA_NAME := 'cipres';

SET @KEY_NAME := 
(SELECT CONSTRAINT_NAME 
    FROM information_schema.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_SCHEMA = @SCHEMA_NAME 
        AND TABLE_NAME = 'running_tasks_parameters' 
        AND REFERENCED_TABLE_NAME = 'running_tasks')
;

SELECT @KEY_NAME; 

ALTER TABLE `running_tasks_parameters` 
    DROP FOREIGN KEY ``
;

-- DELETE FROM `running_tasks_parameters`  
--     WHERE JOBHANDLE NOT IN (SELECT JOBHANDLE FROM tasks)
-- ;

ALTER TABLE `running_tasks_parameters`
    ADD COLUMN RUN_NUMBER INTEGER NOT NULL DEFAULT 1, 
    DROP PRIMARY KEY, 
    ADD PRIMARY KEY (JOBHANDLE, RUN_NUMBER, NAME)
;

ALTER TABLE `running_tasks_parameters`
    ADD FOREIGN KEY fk_running_tasks_parameters_01 (JOBHANDLE, RUN_NUMBER) 
		REFERENCES `running_tasks` (JOBHANDLE, RUN_NUMBER)
;

ALTER TABLE `running_tasks_parameters`
    MODIFY COLUMN RUN_NUMBER INTEGER NOT NULL
;


--------------------------------------------------
-- Modify `job_events` table. 
--------------------------------------------------

-- DELETE FROM `job_events`
--     WHERE JOBHANDLE NOT IN (SELECT JOBHANDLE FROM tasks)
-- ;

ALTER TABLE job_events 
    ADD COLUMN RUN_NUMBER INTEGER NOT NULL DEFAULT 1
;

-- ALTER TABLE job_events 
--     MODIFY COLUMN RUN_NUMBER INTEGER NOT NULL
-- ;

DROP INDEX JOBHANDLE ON job_events;

CREATE INDEX JOBHANDLE ON job_events (JOBHANDLE, RUN_NUMBER);


--------------------------------------------------
-- Modify `job_stats` table. 
--------------------------------------------------
-- DELETE FROM job_stats 
--     WHERE JOBHANDLE NOT IN (SELECT JOBHANDLE FROM tasks)
-- ;

ALTER TABLE job_stats 
    ADD COLUMN RUN_NUMBER INTEGER NOT NULL DEFAULT 1, 
    DROP PRIMARY KEY, 
    ADD PRIMARY KEY (JOBHANDLE, RUN_NUMBER)
;
            
ALTER TABLE job_stats 
    MODIFY COLUMN RUN_NUMBER INTEGER NOT NULL
;
