ALTER TABLE .`sequenceaccession` 
ADD COLUMN `boldnumber` VARCHAR(45) NULL ;
ALTER TABLE .`sha1responses` 
ADD COLUMN `datasetdir` VARCHAR(255) NULL ;
ALTER TABLE `preparation` 
ADD COLUMN `preparationMaterials` VARCHAR(455) NULL ;
ALTER TABLE `preparation` 
CHANGE COLUMN `preparationMethod` `preparationType` VARCHAR(255) NULL DEFAULT NULL ;
ALTER TABLE `loan` 
ADD COLUMN `blockedUntilDate` DATE ;
ALTER TABLE `loan` 
ADD COLUMN `disposition_clean` VARCHAR(45);
--changes november 2015
ALTER TABLE `multimedia` 
ADD COLUMN `relatedResource` VARCHAR(355) NULL DEFAULT NULL;
--march 2016
ALTER TABLE `measurementorfacts` 
CHANGE COLUMN `parameter` `parameter` VARCHAR(455) NULL DEFAULT NULL ;

-- changes for revision 327
create table namedarea(
	namedareaid int AUTO_INCREMENT primary key,
	fk_occurrenceid int,
	sequence tinyint,
	class varchar(255),
	name varchar(255),
	constraint fk_namedareas1 FOREIGN KEY (fk_occurrenceid) REFERENCES rawoccurrence (occurrenceid) ON DELETE CASCADE ON UPDATE CASCADE
);
-- changes for revision 330 (guid moved from rawoccurrence/occurrence to tripleidstore)
alter table tripleidstore
add column guid varchar(255) after tripleidstoreid,
add index idxTripleidstoreGUID(guid); 

update tripleidstore
join rawoccurrence on fk_tripleidstoreid=tripleidstoreid
set tripleidstore.guid = rawoccurrence.guid
where rawoccurrence.guid is not null

alter table rawoccurrence drop column guid;
alter table occurrence drop column guid;

-- changes for revision 355 (ipen, hardiness and establishmentMeans added to harvesting)
alter table rawoccurrence
	add column ipen varchar(255),
	add column hardiness char(1);

alter table occurrence
	add column ipen varchar(255),
	add column hardiness char(1);

-- changes for revision 360 (comment, context, creator and created date for multimedia added to harvesting)
alter table multimedia
	add column comment varchar(512),
	add column context varchar(512),
	add column creator varchar(255),
	add createdDate varchar(50);
	
-- changes for revision 370 (harvesting of biotope/habitat added)
ALTER TABLE rawoccurrence
	ADD COLUMN biotope TEXT NULL AFTER locality;
	
-- changes in rev372 (column comment in multimedia was too small)
ALTER TABLE multimedia
	CHANGE COLUMN comment comment VARCHAR(4096) NULL DEFAULT NULL;

-- update of old higher taxon ranks/names for rev373 (whith lowercase letters)
update rawhigher
set higherrank=CONCAT(UCASE(LEFT(higherrank, 1)), LCASE(SUBSTRING(higherrank, 2))),
	highertaxon=CONCAT(UCASE(LEFT(highertaxon, 1)), LCASE(SUBSTRING(highertaxon, 2)))

-- changes in rev374 (harvesting of abcd:acquiredFrom, see tic.bgbm.org/redmine/issues/551)
ALTER TABLE rawoccurrence
	ADD COLUMN acquiredFrom VARCHAR(255) NULL AFTER hardiness;

-- changes in rev376 (harvesting of abcd:AccessionStatus, see http://tic.bgbm.org/redmine/issues/568)
ALTER TABLE rawoccurrence
	ADD COLUMN accessionStatus VARCHAR(255) NULL AFTER acquiredFrom;

-- changes in rev389, (deprecation of Netherland Antilles, see http://tic.bgbm.org/redmine/issues/613)
INSERT IGNORE INTO `country` (`countryid`, `country`, `iso`) VALUES
	(267, 'Bonaire, Sint Eustatius and Saba', 'BQ'),
	(268, 'Sint Maarten', 'SX'),
	(269, 'Curaçao', 'CW');
UPDATE country SET country='Netherlands Antilles (no longer exists)' WHERE iso='AN';

INSERT IGNORE INTO `countryToOceanOrContinent` (`fk_countryid`, `fk_oceanOrContinentid`) VALUES
	(267, 15),
	(268, 15),
	(269, 15);

-- changes in rev390 (harvesting of abcd:DonorCategory, see http://tic.bgbm.org/redmine/issues/619)
ALTER TABLE rawoccurrence
	ADD COLUMN donorCategory CHAR(1) NULL AFTER accessionStatus;

-- restrict length of accessionStatus to char(1), since it's just a single character
ALTER TABLE rawoccurrence
	MODIFY accessionStatus CHAR(1) NULL;
	
-- rev 399: New column in basket
ALTER TABLE user_shopping_cart
      ADD COLUMN tripleidstoreid INT NOT NULL DEFAULT NULL AFTER institution_code;

-- Swasiland renamed to Eswatini
UPDATE country SET country='Eswatini' WHERE iso='SZ'

-- changes for revision 408 (harvesting of biotope/habitat added to occurrence table)
ALTER TABLE occurrence
	ADD COLUMN biotope TEXT NULL AFTER locality;

UPDATE occurrence o
JOIN rawoccurrence r ON r.occurrenceid=o.occurrenceid
SET o.biotope=r.biotope
WHERE r.biotope IS NOT NULL
