# First, create tables if it doesn't exist yes or remove them

create table if not exists cormList (
    id int primary key,
    resourceKey int,
    european_data_only BOOL,
    index(resourceKey), index(european_data_only)
);

# Then, create a temp table with all resources according to corm_datasource
create table new_cormList like cormList;

insert into new_cormList
select id, data_resource_id, european_data_only
from corm_datasource
join GBIF.resource_access_point on uuid = binding_uuid
where data_resource_id > 0
group by data_resource_id;

# Now, create a table with a full outer join list of new and old cormList
create temporary table tmp

select new_cormList.resourceKey, new_cormList.european_data_only as flag_new, cormList.european_data_only as flag_old
from new_cormList left join  cormList using (resourceKey)

  union

select cormList.resourceKey, new_cormList.european_data_only as flag_new, cormList.european_data_only as flag_old
from new_cormList right join  cormList using (resourceKey);

# Then, insert these ids for records that lost the flag into the remove to-do-list
create table removedRecords
select id as brKey
from tmp
straight_join GBIF.occurrence_record on data_resource_id = resourceKey
where (flag_old is true and flag_new is not true);

# But: These records have to be looked at again (they could have european county code)
# -> insert into includeRecords table
# also include the records that have the flag now and didn't have it before
create table includeRecords
select id as brKey
from tmp
straight_join GBIF.occurrence_record on data_resource_id = resourceKey
where (flag_old is true and flag_new is not true)
   or (flag_new is true and flag_old is not true);

# Now, create indexes for these two tables
alter table removedRecords add primary key (brKey);
alter table includeRecords add primary key (brKey);

# Finally, update the cormList
delete cormList
from cormList
left join new_cormList using (resourceKey)
where new_cormList.resourceKey is null;

replace into cormList
select * from new_cormList;

# Last, remove temporary table we couldn't create as temp table
# (http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html)
