
-- 02/08/2019 
-- Author: Tony Chen 
-- Following changes are made for validating and metadata parsing of user input data files. 

-- ************************************************************
-- As of Feb. 08, 2019: 
-- ONLY IN BETA INSTANCE WORK IN PROGRESS, NOT A FINALIZED VERSION. 

-- The corresponding Java codes are ONLY in Tony's branch,  
-- they are NOT in 'trunk' nor any other branches. 

-- Contact Tony if you have any questions. 
-- ************************************************************

-- Move some columns to place realted information together for better visibility. 
ALTER TABLE `userdata` 
  MODIFY COLUMN `LABEL` VARCHAR(1023) AFTER `SOURCE_DOCUMENT_ID`
;

ALTER TABLE `userdata` 
  MODIFY COLUMN `COMMENT` VARCHAR(255) AFTER `VALIDATED`
;

ALTER TABLE `userdata` 
  MODIFY COLUMN `DATATYPE` VARCHAR(255) AFTER `CREATION_DATE`
;

ALTER TABLE `userdata` 
  MODIFY COLUMN `ENTITYTYPE` VARCHAR(255) AFTER `DATATYPE`
;
-- --------------------------------------------------

-- New columns 
-- Modifiying of UserDataItem.java is also required.
ALTER TABLE `userdata` 
  ADD COLUMN `USER_ASSIGNED_DATA_FORMAT` VARCHAR(255) NULL AFTER `DATAFORMAT`
;

ALTER TABLE `userdata` 
  ADD COLUMN `DATA_FORMAT_SET_DATE` DATETIME NULL AFTER `USER_ASSIGNED_DATA_FORMAT`
;

ALTER TABLE `userdata` 
  ADD COLUMN `VALIDATED_DATE` DATETIME NULL AFTER `VALIDATED`
;

ALTER TABLE `userdata` 
  ADD COLUMN `PARSE_START_DATE` DATETIME NULL AFTER `VALIDATED_DATE`
;

ALTER TABLE `userdata` 
  ADD COLUMN `PARSE_END_DATE` DATETIME NULL AFTER `PARSE_START_DATE`
;

ALTER TABLE `userdata` 
  ADD COLUMN `PARSE_SUCCESS` BIT DEFAULT FALSE AFTER `PARSE_END_DATE`
;
-- --------------------------------------------------


-- ***** Change corresponding column names in UserDataItem.java. 

-- Rename of some columns for better visibility. 
ALTER TABLE `userdata` 
  CHANGE COLUMN `DATATYPE` `DATA_TYPE` VARCHAR(255) NOT NULL 
;

ALTER TABLE `userdata` 
  CHANGE COLUMN `ENTITYTYPE` `ENTITY_TYPE` VARCHAR(255) NOT NULL 
;

ALTER TABLE `userdata` 
  CHANGE COLUMN `DATAFORMAT` `DATA_FORMAT` VARCHAR(255) NOT NULL 
;
-- --------------------------------------------------

ALTER TABLE `item_metadata` 
  MODIFY COLUMN `FIELD` VARCHAR(100) AFTER `USERDATA_ID`
;
-- --------------------------------------------------
