
-- 08/13/2018
-- Tony
-- Following changes are made to update SU calculations. 

CREATE TABLE resource_conversion 
(
    ID                              BIGINT AUTO_INCREMENT,
    RESOURCE                        VARCHAR(100) NOT NULL,
    CONVERSION                      INT NOT NULL DEFAULT '1',
    
    CONSTRAINT pk_resource_conversion_01 PRIMARY KEY (ID),
    CONSTRAINT uq_resource_conversion_01 UNIQUE (RESOURCE) 
);


ALTER TABLE `job_stats` 
  ADD COLUMN `RESRC_CONVRTN_ID` BIGINT NULL AFTER `APPNAME`
;


-- Fill in the newly created table, 'resource_conversion.' 
-- All resources have the conversion ratio of 1 (except comet-gpu). 
INSERT INTO resource_conversion (RESOURCE, CONVERSION) 
  SELECT JS.RESOURCE, '1'
    FROM job_stats AS JS 
    GROUP BY JS.RESOURCE 
    ORDER BY JS.RESOURCE ASC 
;

-- Resource 'comet-gpu' has conversion reatio of 14. 
INSERT INTO resource_conversion (RESOURCE, CONVERSION)
  VALUES ('comet-gpu', '14')
;


-- Change all non utf8_unicode charset to be utf8_unicode. 
ALTER TABLE tgusage CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE job_stats CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;


-- Update the 'job_stats.resrc_convrtn_id.'
-- *** WARNING *** 
-- This update will be very very slow depending on the number of rows in 
-- job_stats and tgusage tables. 
UPDATE job_stats, tgusage, resource_conversion SET
    job_stats.tg_charge_number = tgusage.charge_number,
    job_stats.resource = tgusage.resource, 
    job_stats.resrc_convrtn_id = resource_conversion.ID 
    WHERE job_stats.tg_charge_number IS NULL AND 
        job_stats.remote_job_id = tgusage.jobid AND 
        tgusage.resource IS NOT NULL AND 
        ((tgusage.resource =  job_stats.resource) OR 
        tgusage.resource = 'comet-gpu' AND job_stats.resource = 'comet') AND 
        tgusage.resource = resource_conversion.resource 
;
