
-- Database update August 29, 2018


-- Updates Probe's codeClass and analyteType for old analysis according to controlValue (NA ignored)
-- If codeClass or analyteType can't be determined and it is null then the rows also effected
UPDATE Probe SET codeClass = 'Positive', analyteType = 'SpikeIn' WHERE controlValue = 'POS';
UPDATE Probe SET codeClass = 'Negative', analyteType = 'SpikeIn' WHERE controlValue = 'NEG';
UPDATE Probe SET codeClass = 'Control', analyteType = 'Protein' WHERE controlValue = 'HK';
UPDATE Probe SET codeClass = 'Positive', analyteType = 'SpikeIn' WHERE name LIKE 'POS%' AND (codeClass IS NULL OR analyteType IS NULL);
UPDATE Probe SET codeClass = 'Negative', analyteType = 'SpikeIn' WHERE name LIKE 'NEG%' AND (codeClass IS NULL OR analyteType IS NULL);
UPDATE Probe SET codeClass = 'Endogenous' WHERE codeClass IS NULL;
UPDATE Probe SET analyteType = 'Protein' WHERE analyteType IS NULL;

-- Makes controlValue column's value of Probe table optional
ALTER TABLE Probe ALTER COLUMN controlValue DROP NOT NULL;
ALTER TABLE Probe ALTER COLUMN controlValue set DEFAULT NULL;