-- This file must exist, because its highest version number is taken as Perun code version, and is compared to
-- the current DB data version from table configurations, and if they do not match, Perun will refuse to start.

-- Changelog file should have the newest version at the top, the oldest at the bottom.
-- Versions must be separated by empty lines, version number should consist of three numbers with dots between, f.e. 3.0.1 is ok, 3.1 or 3.1.1.1 is not.
-- Directly under version number should be version commands. They will be executed in the order they are written here.
-- Comments are prefixed with -- and can be written only between version blocks, that means not in the lines with commands. They have to be at the start of the line.

3.1.92
ALTER TABLE services ADD COLUMN use_expired_members boolean default true not null;
UPDATE configurations SET value='3.1.92' WHERE property='DATABASE VERSION';

3.1.91
create table consent_hubs (id integer not null, name varchar not null, enforce_consents boolean not null, created_at timestamp default statement_timestamp() not null, created_by varchar default user not null, modified_at timestamp default statement_timestamp() not null, modified_by varchar default user not null, created_by_uid integer, modified_by_uid integer, constraint consent_hub_pk primary key (id), constraint consent_hub_name_u unique (name));
create table consent_hubs_facilities (consent_hub_id integer not null, facility_id integer not null, created_at timestamp default statement_timestamp() not null, created_by varchar default user not null,	modified_at timestamp default statement_timestamp() not null, modified_by varchar default user not null, created_by_uid integer, modified_by_uid integer, constraint conhubfac_ch_fk foreign key(consent_hub_id) references consent_hubs(id), constraint conhubfac_fac_fk foreign key(facility_id) references facilities(id), constraint facility_id_u unique (facility_id));
create sequence "consent_hubs_id_seq";
create index idx_fk_conhubfac_ch on consent_hubs_facilities(consent_hub_id);
create index idx_fk_conhubfac_fac on consent_hubs_facilities(facility_id);
grant all on consent_hubs to perun;
grant all on consent_hubs_facilities to perun;
insert into consent_hubs (id, name, enforce_consents) SELECT nextval('"consent_hubs_id_seq"'), name, true FROM facilities;
insert into consent_hubs_facilities (consent_hub_id, facility_id) SELECT consent_hubs.id, facilities.id FROM consent_hubs JOIN facilities ON consent_hubs.name = facilities.name;
create type consent_status as enum ('UNSIGNED', 'GRANTED', 'REVOKED');
create table consents (id integer not null, user_id integer not null, consent_hub_id integer not null, status consent_status not null default 'UNSIGNED', created_at timestamp default statement_timestamp() not null, created_by varchar default user not null, modified_at timestamp default statement_timestamp() not null, modified_by varchar default user not null, created_by_uid integer,modified_by_uid integer, constraint consents_pk primary key(id), constraint consents_user_fk foreign key(user_id) references users(id), constraint consents_cons_hub_fk foreign key(consent_hub_id) references consent_hubs(id), constraint consents_user_hub_status_u unique(user_id, consent_hub_id, status));
create table consent_attr_defs (consent_id  INT NOT NULL, attr_id  INT NOT NULL, constraint consentatt_pk primary key(consent_id, attr_id), constraint consentatt_nam_fk foreign key (attr_id) references attr_names(id), constraint consentatt_consent_fk foreign key (consent_id) references consents(id));
create sequence "consents_id_seq";
create index idx_fk_cons_usr ON consents(user_id);
create index idx_fk_cons_cons_hub ON consents(consent_hub_id);
create index idx_fk_attr_cons_cons ON consent_attr_defs(consent_id);
create index idx_fk_attr_cons_attr ON consent_attr_defs(attr_id);
grant all on consents to perun;
grant all on consent_attr_defs to perun;
UPDATE configurations SET value='3.1.91' WHERE property='DATABASE VERSION';

3.1.90
create table vos_vos (vo_id integer not null, member_vo_id integer not null, created_at timestamp default statement_timestamp() not null, created_by varchar default user not null, modified_at timestamp default statement_timestamp() not null, modified_by varchar default user not null, constraint vos_vos_pk primary key (vo_id,member_vo_id), constraint vos_vos_void_fk foreign key (vo_id) references vos(id), constraint vos_vos_memid_fk foreign key (member_vo_id) references vos(id));
grant all on vos_vos to perun;
UPDATE configurations SET value='3.1.90' WHERE property='DATABASE VERSION';

3.1.89
create type attribute_action as enum ('READ', 'WRITE');
create table attribute_policy_collections (id integer not null, attr_id integer not null, action attribute_action not null, constraint attrpolcol_pk primary key (id), constraint attrpolcol_attr_fk foreign key (attr_id) references attr_names (id) on delete cascade);
create type role_object as enum ('None', 'Group', 'Vo', 'Facility', 'Resource', 'User', 'Member');
create table attribute_policies (id integer not null, role_id integer not null, object role_object not null, policy_collection_id integer not null, constraint attrpol_pk primary key (id), constraint attrpol_attr_fk foreign key (policy_collection_id) references attribute_policy_collections (id) on delete cascade, constraint attrpol_role_fk foreign key (role_id) references roles (id));
grant all on attribute_policies to perun;
grant all on attribute_policy_collections to perun;
create index idx_fk_attrpol_role on attribute_policies(role_id);
create index idx_fk_attrpol_colid on attribute_policies(policy_collection_id);
create index idx_fk_attrpolcol_attr on attribute_policy_collections(attr_id);
create sequence "attribute_policies_id_seq";
create sequence "attribute_policy_collections_id_seq";
UPDATE configurations SET value='3.1.89' WHERE property='DATABASE VERSION';

3.1.88
create or replace function relation_group_resource_exist(integer, integer) returns integer as 'select count(1)::integer from (SELECT group_id, resource_id FROM perun.groups_resources UNION SELECT group_id, resource_id FROM perun.groups_resources_automatic) gr_res where group_id=$1 and resource_id=$2;' language sql;
UPDATE configurations SET value='3.1.88' WHERE property='DATABASE VERSION';

3.1.87
ALTER TABLE groups_resources ADD COLUMN auto_assign_subgroups boolean default false not null;
UPDATE configurations SET value='3.1.87' WHERE property='DATABASE VERSION';

3.1.86
create table groups_resources_automatic(group_id integer not null, resource_id integer not null,source_group_id integer not null,created_at timestamp default statement_timestamp() not null,created_by varchar default user not null,modified_at timestamp default statement_timestamp() not null,modified_by varchar default user not null,created_by_uid integer,modified_by_uid integer,constraint grresaut_grp_res_sgrp_u unique (group_id,resource_id,source_group_id),constraint grresaut_gr_fk foreign key (group_id) references groups(id),constraint grresaut_res_fk foreign key (resource_id) references resources(id),constraint grresaut_sgr_fk foreign key (source_group_id) references groups(id));
grant all on groups_resources_automatic to perun;
create index idx_fk_grres_a_gr on groups_resources_automatic(group_id);
create index idx_fk_grres_a_res on groups_resources_automatic(resource_id);
create index idx_fk_grres_a_sgr on groups_resources_automatic(source_group_id);
create or replace function relation_group_resource_exist(integer, integer) returns integer as 'select count(1)::integer from (SELECT group_id, resource_id FROM groups_resources UNION SELECT group_id, resource_id FROM groups_resources_automatic) gr_res where group_id=$1 and resource_id=$2;' language sql;
create or replace function delete_group_resource_status() returns trigger as 'begin if relation_group_resource_exist(OLD.group_id, OLD.resource_id) = 0 then delete from groups_resources_state where group_id=OLD.group_id and resource_id=OLD.resource_id; end if; return OLD; end;' language plpgsql;
create trigger after_deleting_from_groups_resources_automatic after delete on groups_resources_automatic for each row execute procedure delete_group_resource_status();
UPDATE configurations SET value='3.1.86' WHERE property='DATABASE VERSION';

3.1.85
create function relation_group_resource_exist(integer, integer) returns integer as 'select count(1)::integer from groups_resources where group_id=$1 and resource_id=$2;' language sql;
create table groups_resources_state (group_id integer not null, resource_id integer not null, failure_cause varchar default null, status group_resource_status not null default 'PROCESSING', check (relation_group_resource_exist(group_id, resource_id) != 0));
create function delete_group_resource_status() returns trigger as 'begin delete from groups_resources_state where group_id=OLD.group_id and resource_id=OLD.resource_id; return OLD; end;' language plpgsql;
create trigger after_deleting_from_groups_resources after delete on groups_resources for each row execute procedure delete_group_resource_status();
create index idx_fk_grres_s_gr on groups_resources_state(group_id);
create index idx_fk_grres_s_res on groups_resources_state(resource_id);
grant all on groups_resources_state to perun;
insert into groups_resources_state (group_id, resource_id, failure_cause, status) select group_id, resource_id, failure_cause, status from groups_resources;
alter table groups_resources drop column status, drop column failure_cause;
UPDATE configurations SET value='3.1.85' WHERE property='DATABASE VERSION';

3.1.84
ALTER TABLE groups_resources ADD COLUMN failure_cause varchar default null;
UPDATE configurations SET value='3.1.84' WHERE property='DATABASE VERSION';

3.1.83
DROP TABLE facility_contacts;
UPDATE configurations SET value='3.1.83' WHERE property='DATABASE VERSION';

3.1.82
create type group_resource_status as enum ('ACTIVE','INACTIVE','FAILED','PROCESSING');
ALTER TABLE groups_resources ADD COLUMN status group_resource_status not null default 'PROCESSING';
UPDATE groups_resources SET status = 'ACTIVE';
UPDATE configurations SET value='3.1.82' WHERE property='DATABASE VERSION';

3.1.81
ALTER TABLE mailchange ADD COLUMN uu_id uuid not null default gen_random_uuid();
UPDATE configurations SET value='3.1.81' WHERE property='DATABASE VERSION';

3.1.80
alter table groups_to_register drop constraint grpreg_group_fk;
alter table groups_to_register add constraint grpreg_group_fk foreign key (group_id) references groups(id) on delete cascade;
UPDATE configurations SET value='3.1.80' WHERE property='DATABASE VERSION';

3.1.79
ALTER TABLE application_form ADD COLUMN automatic_approval_embedded boolean default false not null;
UPDATE configurations SET value='3.1.79' WHERE property='DATABASE VERSION';

3.1.78
create table groups_to_register (group_id integer, constraint grpreg_group_fk foreign key (group_id) references groups(id));
grant all on groups_to_register to perun;
UPDATE configurations SET value='3.1.78' WHERE property='DATABASE VERSION';

3.1.77
create type app_item_disabled as enum ('NEVER','ALWAYS','IF_PREFILLED','IF_EMPTY');
create type app_item_hidden as enum ('NEVER','ALWAYS','IF_PREFILLED','IF_EMPTY');
ALTER TABLE application_form_items ADD COLUMN hidden app_item_hidden not null default 'NEVER';
ALTER TABLE application_form_items ADD COLUMN disabled app_item_disabled not null default 'NEVER';
ALTER TABLE application_form_items ADD COLUMN hidden_dependency_item_id integer;
ALTER TABLE application_form_items ADD COLUMN disabled_dependency_item_id integer;
ALTER TABLE application_form_items ADD COLUMN updatable boolean default true not null;
ALTER TABLE application_form_items ADD CONSTRAINT applfrmit_hd foreign key (hidden_dependency_item_id) references application_form_items(id) ON DELETE SET NULL;
ALTER TABLE application_form_items ADD CONSTRAINT applfrmit_dd foreign key (disabled_dependency_item_id) references application_form_items(id) ON DELETE SET NULL;
UPDATE application_form_items SET hidden = 'ALWAYS' where type = 'FROM_FEDERATION_HIDDEN';
UPDATE application_form_items SET disabled = 'ALWAYS' where type = 'FROM_FEDERATION_SHOW';
UPDATE application_form_items SET updatable = false, type = 'TEXTFIELD' where type = 'FROM_FEDERATION_SHOW' OR type = 'FROM_FEDERATION_HIDDEN';
UPDATE configurations SET value='3.1.77' WHERE property='DATABASE VERSION';

3.1.76
ALTER TABLE pwdreset ADD COLUMN uu_id uuid not null default gen_random_uuid();
UPDATE configurations SET value='3.1.76' WHERE property='DATABASE VERSION';

3.1.75
ALTER TABLE users ADD COLUMN anonymized boolean default false not null;
UPDATE configurations SET value='3.1.75' WHERE property='DATABASE VERSION';

3.1.74
UPDATE attr_names SET type='java.lang.String' WHERE type='java.lang.LargeString';
UPDATE attr_names SET type='java.util.ArrayList' WHERE type='java.util.LargeArrayList';
UPDATE configurations SET value='3.1.74' WHERE property='DATABASE VERSION';

3.1.73
ALTER TABLE pwdreset ADD COLUMN validity_to timestamp;
UPDATE pwdreset SET validity_to = created_at + interval '1 days';
ALTER TABLE pwdreset ALTER COLUMN validity_to SET not null;
UPDATE configurations SET value='3.1.73' WHERE property='DATABASE VERSION';

3.1.72
alter table attr_names drop constraint attnam_attnam_fk;
drop index idx_fk_attnam_attnam;
alter table attr_names drop column default_attr_id;
UPDATE configurations SET value='3.1.72' WHERE property='DATABASE VERSION';

3.1.71
alter table groups drop constraint grp_nam_vo_parentg_u;
create unique index idx_grp_nam_vo_parentg_u on groups (name,vo_id,coalesce(parent_group_id,'0'));
UPDATE configurations SET value='3.1.71' WHERE property='DATABASE VERSION';

3.1.70
ALTER TABLE members_sponsored ADD CONSTRAINT memspons_mem_usr_u unique (sponsored_id, sponsor_id);
UPDATE configurations SET value='3.1.70' WHERE property='DATABASE VERSION';

3.1.69
alter table members_sponsored ADD COLUMN validity_to timestamp default null;
UPDATE configurations SET value='3.1.69' WHERE property='DATABASE VERSION';

3.1.68
CREATE TABLE vos_bans (id integer not null, member_id integer not null, vo_id integer not null, description varchar, banned_to timestamp default '2999-01-01 00:00:00' not null, created_at timestamp default statement_timestamp() not null, created_by varchar default user not null, modified_at timestamp default statement_timestamp() not null, modified_by varchar default user not null, created_by_uid integer, modified_by_uid integer, constraint vos_bans_pk primary key (id), constraint vos_bans_u unique (member_id), constraint vos_bans_mem_fk foreign key (member_id) references members (id), constraint vos_bans_vo_fk foreign key (vo_id) references vos (id));
CREATE SEQUENCE "vos_bans_id_seq";
CREATE INDEX idx_fk_vos_ban_member ON vos_bans (member_id);
CREATE INDEX idx_fk_vos_ban_vos ON vos_bans (vo_id);
GRANT ALL ON vos_bans TO perun;
INSERT INTO vos_bans (id, description, banned_to, member_id, vo_id) SELECT nextval('vos_bans_id_seq'), 'AUTO CREATED FROM SUSPEND STATUS', members.suspended_to, members.id, members.vo_id FROM members WHERE status = 2 AND members.suspended_to IS NOT NULL;
INSERT INTO vos_bans (id, description, member_id, vo_id) SELECT nextval('vos_bans_id_seq'), 'AUTO CREATED FROM SUSPEND STATUS', members.id, members.vo_id FROM members WHERE status = 2 AND members.suspended_to IS NULL;
UPDATE members SET status=0 WHERE status = 2;
ALTER TABLE members DROP COLUMN suspended_to;
UPDATE configurations SET value='3.1.68' WHERE property='DATABASE VERSION';

3.1.67
ALTER TABLE users ADD COLUMN uu_id UUID not null default gen_random_uuid();
ALTER TABLE groups ADD COLUMN uu_id UUID not null default gen_random_uuid();
ALTER TABLE resources ADD COLUMN uu_id UUID not null default gen_random_uuid();
update configurations set value='3.1.67' where property='DATABASE VERSION';

3.1.66
ALTER TABLE tasks DROP CONSTRAINT task_stat_chk;
ALTER TABLE tasks ADD CONSTRAINT task_stat_chk check (status in ('WAITING', 'PLANNED', 'GENERATING', 'GENERROR', 'GENERATED', 'SENDING', 'DONE', 'SENDERROR', 'ERROR', 'WARNING'));
ALTER TABLE tasks_results DROP CONSTRAINT taskres_stat_chk;
ALTER TABLE tasks_results ADD CONSTRAINT taskres_stat_chk check (status in ('DONE','ERROR','DENIED', 'WARNING'));
update configurations set value='3.1.66' where property='DATABASE VERSION';

3.1.65
ALTER TABLE host_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE host_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE host_attr_values DROP COLUMN attr_value_text;
ALTER TABLE host_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE entityless_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE entityless_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE entityless_attr_values DROP COLUMN attr_value_text;
ALTER TABLE facility_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE facility_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE facility_attr_values DROP COLUMN attr_value_text;
ALTER TABLE facility_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE group_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE group_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE group_attr_values DROP COLUMN attr_value_text;
ALTER TABLE group_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE resource_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE resource_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE resource_attr_values DROP COLUMN attr_value_text;
ALTER TABLE resource_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE group_resource_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE group_resource_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE group_resource_attr_values DROP COLUMN attr_value_text;
ALTER TABLE group_resource_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE member_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE member_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE member_attr_values DROP COLUMN attr_value_text;
ALTER TABLE member_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE member_group_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE member_group_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE member_group_attr_values DROP COLUMN attr_value_text;
ALTER TABLE member_group_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE member_resource_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE member_resource_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE member_resource_attr_values DROP COLUMN attr_value_text;
ALTER TABLE member_resource_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE user_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE user_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE user_attr_values DROP COLUMN attr_value_text;
ALTER TABLE user_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE user_facility_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE user_facility_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE user_facility_attr_values DROP COLUMN attr_value_text;
ALTER TABLE user_facility_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE vo_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE vo_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE vo_attr_values DROP COLUMN attr_value_text;
ALTER TABLE vo_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE user_ext_source_attr_values ALTER COLUMN attr_value TYPE text;
UPDATE user_ext_source_attr_values set attr_value=attr_value_text where attr_value is null and attr_value_text is not null;
ALTER TABLE user_ext_source_attr_values DROP COLUMN attr_value_text;
ALTER TABLE user_ext_source_attr_u_values ALTER COLUMN attr_value TYPE text;
ALTER TABLE ext_sources_attributes ALTER COLUMN attr_value TYPE text;
update configurations set value='3.1.65' where property='DATABASE VERSION';

3.1.64
ALTER TABLE vos DROP COLUMN status;
ALTER TABLE users DROP COLUMN status;
ALTER TABLE owners DROP COLUMN status;
ALTER TABLE facilities DROP COLUMN status;
ALTER TABLE resources DROP COLUMN status;
ALTER TABLE destinations DROP COLUMN status;
ALTER TABLE facility_owners DROP COLUMN status;
ALTER TABLE groups DROP COLUMN status;
ALTER TABLE attr_names DROP COLUMN status;
ALTER TABLE hosts DROP COLUMN status;
ALTER TABLE host_attr_values DROP COLUMN status;
ALTER TABLE services DROP COLUMN status;
ALTER TABLE service_required_attrs DROP COLUMN status;
ALTER TABLE service_denials DROP COLUMN status;
ALTER TABLE resource_services DROP COLUMN status;
ALTER TABLE facility_service_destinations DROP COLUMN status;
ALTER TABLE entityless_attr_values DROP COLUMN status;
ALTER TABLE facility_attr_values DROP COLUMN status;
ALTER TABLE group_attr_values DROP COLUMN status;
ALTER TABLE resource_attr_values DROP COLUMN status;
ALTER TABLE group_resource_attr_values DROP COLUMN status;
ALTER TABLE groups_members DROP COLUMN status;
ALTER TABLE groups_resources DROP COLUMN status;
ALTER TABLE member_attr_values DROP COLUMN status;
ALTER TABLE member_group_attr_values DROP COLUMN status;
ALTER TABLE member_resource_attr_values DROP COLUMN status;
ALTER TABLE user_attr_values DROP COLUMN status;
ALTER TABLE user_facility_attr_values DROP COLUMN status;
ALTER TABLE vo_attr_values DROP COLUMN status;
ALTER TABLE ext_sources DROP COLUMN status;
ALTER TABLE ext_sources_attributes DROP COLUMN status;
ALTER TABLE vo_ext_sources DROP COLUMN status;
ALTER TABLE user_ext_sources DROP COLUMN status;
ALTER TABLE service_packages DROP COLUMN status;
ALTER TABLE service_service_packages DROP COLUMN status;
ALTER TABLE user_ext_source_attr_values DROP COLUMN status;
ALTER TABLE specific_user_users DROP CONSTRAINT specifu_u_status_chk;
ALTER TABLE specific_user_users ALTER COLUMN status drop DEFAULT;
ALTER TABLE specific_user_users ALTER COLUMN status TYPE integer USING status::int;
ALTER TABLE specific_user_users ALTER COLUMN status SET DEFAULT 0;
ALTER TABLE specific_user_users ADD CONSTRAINT specifu_u_status_chk check (status in (0,1));
update configurations set value='3.1.64' where property='DATABASE VERSION';

3.1.63
ALTER TABLE members ALTER COLUMN status drop DEFAULT;
ALTER TABLE members ALTER COLUMN status TYPE integer USING status::int;
ALTER TABLE members ALTER COLUMN status SET DEFAULT 0;
update configurations set value='3.1.63' where property='DATABASE VERSION';

3.1.62
ALTER TABLE application_form ALTER COLUMN automatic_approval TYPE boolean USING automatic_approval::boolean;
ALTER TABLE application_form ALTER COLUMN automatic_approval SET default false;
UPDATE application_form SET automatic_approval=false where automatic_approval is null;
ALTER TABLE application_form ALTER COLUMN automatic_approval SET not null;
ALTER TABLE application_form ALTER COLUMN automatic_approval_extension TYPE boolean USING automatic_approval_extension::boolean;
ALTER TABLE application_form ALTER COLUMN automatic_approval_extension SET default false;
UPDATE application_form SET automatic_approval_extension=false where automatic_approval_extension is null;
ALTER TABLE application_form ALTER COLUMN automatic_approval_extension SET not null;
ALTER TABLE application_form_items ALTER COLUMN required TYPE boolean USING required::boolean;
ALTER TABLE application_form_items ALTER COLUMN required SET default false;
UPDATE application_form_items SET required=false where required is null;
ALTER TABLE application_form_items ALTER COLUMN required SET not null;
ALTER TABLE application_mails ALTER COLUMN send TYPE boolean USING send::boolean;
ALTER TABLE application_mails ALTER COLUMN send SET default false;
ALTER TABLE application_mails ALTER COLUMN send SET not null;
ALTER TABLE cabinet_publications ALTER COLUMN locked drop DEFAULT;
ALTER TABLE cabinet_publications ALTER COLUMN locked TYPE boolean USING locked::boolean;
ALTER TABLE cabinet_publications ALTER COLUMN locked SET default false;
ALTER TABLE cabinet_publications ALTER COLUMN locked SET not null;
ALTER TABLE members_sponsored ALTER COLUMN active drop DEFAULT;
ALTER TABLE members_sponsored ALTER COLUMN active TYPE boolean USING active::boolean;
ALTER TABLE members_sponsored ALTER COLUMN active SET default true;
ALTER TABLE members_sponsored ALTER COLUMN active SET not null;
ALTER TABLE services ALTER COLUMN enabled drop DEFAULT;
ALTER TABLE services ALTER COLUMN enabled TYPE boolean USING enabled::boolean;
ALTER TABLE services ALTER COLUMN enabled SET default true;
ALTER TABLE services ALTER COLUMN enabled SET not null;
ALTER TABLE users DROP CONSTRAINT usr_srvacc_chk;
ALTER TABLE users ALTER COLUMN service_acc drop DEFAULT;
ALTER TABLE users ALTER COLUMN service_acc TYPE boolean USING service_acc::boolean;
ALTER TABLE users ALTER COLUMN service_acc SET default false;
UPDATE users SET service_acc=false where service_acc is null;
ALTER TABLE users ALTER COLUMN service_acc SET not null;
ALTER TABLE users ALTER COLUMN sponsored_acc drop DEFAULT;
ALTER TABLE users ALTER COLUMN sponsored_acc TYPE boolean USING sponsored_acc::boolean;
ALTER TABLE users ALTER COLUMN sponsored_acc SET default false;
UPDATE users SET sponsored_acc=false where sponsored_acc is null;
ALTER TABLE users ALTER COLUMN sponsored_acc SET not null;
update configurations set value='3.1.62' where property='DATABASE VERSION';

3.1.61
ALTER TABLE vos ALTER COLUMN name TYPE varchar;
ALTER TABLE vos ALTER COLUMN short_name TYPE varchar;
ALTER TABLE vos ALTER COLUMN created_by TYPE varchar;
ALTER TABLE vos ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE users ALTER COLUMN first_name TYPE varchar;
ALTER TABLE users ALTER COLUMN last_name TYPE varchar;
ALTER TABLE users ALTER COLUMN middle_name TYPE varchar;
ALTER TABLE users ALTER COLUMN title_before TYPE varchar;
ALTER TABLE users ALTER COLUMN title_after TYPE varchar;
ALTER TABLE users ALTER COLUMN created_by TYPE varchar;
ALTER TABLE users ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE owners ALTER COLUMN name TYPE varchar;
ALTER TABLE owners ALTER COLUMN contact TYPE varchar;
ALTER TABLE owners ALTER COLUMN created_by TYPE varchar;
ALTER TABLE owners ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE owners ALTER COLUMN type TYPE varchar;
ALTER TABLE cabinet_categories ALTER COLUMN name TYPE varchar;
ALTER TABLE cabinet_publication_systems ALTER COLUMN friendlyName TYPE varchar;
ALTER TABLE cabinet_publication_systems ALTER COLUMN url TYPE varchar;
ALTER TABLE cabinet_publication_systems ALTER COLUMN username TYPE varchar;
ALTER TABLE cabinet_publication_systems ALTER COLUMN password TYPE varchar;
ALTER TABLE cabinet_publication_systems ALTER COLUMN loginNamespace TYPE varchar;
ALTER TABLE cabinet_publication_systems ALTER COLUMN type TYPE varchar;
ALTER TABLE cabinet_publications ALTER COLUMN title TYPE varchar;
ALTER TABLE cabinet_publications ALTER COLUMN main TYPE varchar;
ALTER TABLE cabinet_publications ALTER COLUMN isbn TYPE varchar;
ALTER TABLE cabinet_publications ALTER COLUMN createdBy TYPE varchar;
ALTER TABLE cabinet_publications ALTER COLUMN doi TYPE varchar;
ALTER TABLE cabinet_authorships ALTER COLUMN createdBy TYPE varchar;
ALTER TABLE cabinet_thanks ALTER COLUMN createdBy TYPE varchar;
ALTER TABLE facilities ALTER COLUMN name TYPE varchar;
ALTER TABLE facilities ALTER COLUMN dsc TYPE varchar;
ALTER TABLE facilities ALTER COLUMN created_by TYPE varchar;
ALTER TABLE facilities ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE resources ALTER COLUMN name TYPE varchar;
ALTER TABLE resources ALTER COLUMN dsc TYPE varchar;
ALTER TABLE resources ALTER COLUMN created_by TYPE varchar;
ALTER TABLE resources ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE destinations ALTER COLUMN destination TYPE varchar;
ALTER TABLE destinations ALTER COLUMN type TYPE varchar;
ALTER TABLE destinations ALTER COLUMN created_by TYPE varchar;
ALTER TABLE destinations ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE facility_owners ALTER COLUMN created_by TYPE varchar;
ALTER TABLE facility_owners ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE groups ALTER COLUMN dsc TYPE varchar;
ALTER TABLE groups ALTER COLUMN created_by TYPE varchar;
ALTER TABLE groups ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE facility_contacts ALTER COLUMN name TYPE varchar;
ALTER TABLE members ALTER COLUMN created_by TYPE varchar;
ALTER TABLE members ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE roles ALTER COLUMN name TYPE varchar;
ALTER TABLE action_types ALTER COLUMN action_type TYPE varchar;
ALTER TABLE action_types ALTER COLUMN description TYPE varchar;
ALTER TABLE membership_types ALTER COLUMN membership_type TYPE varchar;
ALTER TABLE membership_types ALTER COLUMN description TYPE varchar;
ALTER TABLE attr_names ALTER COLUMN attr_name TYPE varchar;
ALTER TABLE attr_names ALTER COLUMN friendly_name TYPE varchar;
ALTER TABLE attr_names ALTER COLUMN namespace TYPE varchar;
ALTER TABLE attr_names ALTER COLUMN type TYPE varchar;
ALTER TABLE attr_names ALTER COLUMN dsc TYPE varchar;
ALTER TABLE attr_names ALTER COLUMN created_by TYPE varchar;
ALTER TABLE attr_names ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE attr_names ALTER COLUMN display_name TYPE varchar;
ALTER TABLE hosts ALTER COLUMN hostname TYPE varchar;
ALTER TABLE hosts ALTER COLUMN dsc TYPE varchar;
ALTER TABLE hosts ALTER COLUMN created_by TYPE varchar;
ALTER TABLE hosts ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE host_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE host_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE host_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE host_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE auditer_consumers ALTER COLUMN name TYPE varchar;
ALTER TABLE auditer_consumers ALTER COLUMN created_by TYPE varchar;
ALTER TABLE auditer_consumers ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE services ALTER COLUMN name TYPE varchar;
ALTER TABLE services ALTER COLUMN description TYPE varchar;
ALTER TABLE services ALTER COLUMN script TYPE varchar;
ALTER TABLE services ALTER COLUMN created_by TYPE varchar;
ALTER TABLE services ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE service_required_attrs ALTER COLUMN created_by TYPE varchar;
ALTER TABLE service_required_attrs ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE specific_user_users ALTER COLUMN type TYPE varchar;
ALTER TABLE service_denials ALTER COLUMN created_by TYPE varchar;
ALTER TABLE service_denials ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE resource_services ALTER COLUMN created_by TYPE varchar;
ALTER TABLE resource_services ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE application ALTER COLUMN apptype TYPE varchar;
ALTER TABLE application ALTER COLUMN extSourceName TYPE varchar;
ALTER TABLE application ALTER COLUMN extSourceType TYPE varchar;
ALTER TABLE application ALTER COLUMN state TYPE varchar;
ALTER TABLE application ALTER COLUMN created_by TYPE varchar;
ALTER TABLE application ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE application_form ALTER COLUMN module_name TYPE varchar;
ALTER TABLE application_form_items ALTER COLUMN shortname TYPE varchar;
ALTER TABLE application_form_items ALTER COLUMN type TYPE varchar;
ALTER TABLE application_form_items ALTER COLUMN fed_attr TYPE varchar;
ALTER TABLE application_form_items ALTER COLUMN src_attr TYPE varchar;
ALTER TABLE application_form_items ALTER COLUMN dst_attr TYPE varchar;
ALTER TABLE application_form_items ALTER COLUMN regex TYPE varchar;
ALTER TABLE application_form_item_apptypes ALTER COLUMN apptype TYPE varchar;
ALTER TABLE application_form_item_texts ALTER COLUMN locale TYPE varchar;
ALTER TABLE application_form_item_texts ALTER COLUMN help TYPE varchar;
ALTER TABLE application_form_item_texts ALTER COLUMN error_message TYPE varchar;
ALTER TABLE application_data ALTER COLUMN shortname TYPE varchar;
ALTER TABLE application_data ALTER COLUMN value TYPE varchar;
ALTER TABLE application_data ALTER COLUMN assurance_level TYPE varchar;
ALTER TABLE application_mails ALTER COLUMN app_type TYPE varchar;
ALTER TABLE application_mails ALTER COLUMN mail_type TYPE varchar;
ALTER TABLE application_mail_texts ALTER COLUMN locale TYPE varchar;
ALTER TABLE application_mail_texts ALTER COLUMN subject TYPE varchar;
ALTER TABLE application_mail_texts ALTER COLUMN text TYPE varchar;
ALTER TABLE application_reserved_logins ALTER COLUMN login TYPE varchar;
ALTER TABLE application_reserved_logins ALTER COLUMN namespace TYPE varchar;
ALTER TABLE application_reserved_logins ALTER COLUMN created_by TYPE varchar;
ALTER TABLE facility_service_destinations ALTER COLUMN created_by TYPE varchar;
ALTER TABLE facility_service_destinations ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE facility_service_destinations ALTER COLUMN propagation_type TYPE varchar;
ALTER TABLE entityless_attr_values ALTER COLUMN subject TYPE varchar;
ALTER TABLE entityless_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE entityless_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE entityless_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE facility_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE facility_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE facility_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE facility_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE group_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE group_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE group_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE group_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE resource_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE resource_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE resource_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE resource_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE group_resource_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE group_resource_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE group_resource_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE group_resource_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE groups_members ALTER COLUMN created_by TYPE varchar;
ALTER TABLE groups_members ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE groups_resources ALTER COLUMN created_by TYPE varchar;
ALTER TABLE groups_resources ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE member_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE member_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE member_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE member_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE member_group_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE member_group_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE member_group_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE member_group_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE member_resource_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE member_resource_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE member_resource_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE member_resource_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE user_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE user_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE user_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE user_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE user_facility_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE user_facility_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE user_facility_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE user_facility_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE vo_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE vo_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE vo_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE vo_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE ext_sources ALTER COLUMN name TYPE varchar;
ALTER TABLE ext_sources ALTER COLUMN type TYPE varchar;
ALTER TABLE ext_sources ALTER COLUMN created_by TYPE varchar;
ALTER TABLE ext_sources ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE ext_sources_attributes ALTER COLUMN attr_name TYPE varchar;
ALTER TABLE ext_sources_attributes ALTER COLUMN created_by TYPE varchar;
ALTER TABLE ext_sources_attributes ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE vo_ext_sources ALTER COLUMN created_by TYPE varchar;
ALTER TABLE vo_ext_sources ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE group_ext_sources ALTER COLUMN created_by TYPE varchar;
ALTER TABLE group_ext_sources ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE user_ext_sources ALTER COLUMN login_ext TYPE varchar;
ALTER TABLE user_ext_sources ALTER COLUMN created_by TYPE varchar;
ALTER TABLE user_ext_sources ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE service_packages ALTER COLUMN name TYPE varchar;
ALTER TABLE service_packages ALTER COLUMN description TYPE varchar;
ALTER TABLE service_packages ALTER COLUMN created_by TYPE varchar;
ALTER TABLE service_packages ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE service_service_packages ALTER COLUMN created_by TYPE varchar;
ALTER TABLE service_service_packages ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE tasks ALTER COLUMN status TYPE varchar;
ALTER TABLE tasks ALTER COLUMN err_message TYPE varchar;
ALTER TABLE tasks_results ALTER COLUMN status TYPE varchar;
ALTER TABLE tasks_results ALTER COLUMN err_message TYPE varchar;
ALTER TABLE tasks_results ALTER COLUMN std_message TYPE varchar;
ALTER TABLE tasks_results ALTER COLUMN created_by TYPE varchar;
ALTER TABLE tasks_results ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE auditer_log ALTER COLUMN actor TYPE varchar;
ALTER TABLE reserved_logins ALTER COLUMN login TYPE varchar;
ALTER TABLE reserved_logins ALTER COLUMN namespace TYPE varchar;
ALTER TABLE reserved_logins ALTER COLUMN application TYPE varchar;
ALTER TABLE reserved_logins ALTER COLUMN id TYPE varchar;
ALTER TABLE pn_object ALTER COLUMN name TYPE varchar;
ALTER TABLE pn_object ALTER COLUMN properties TYPE varchar;
ALTER TABLE pn_object ALTER COLUMN class_name TYPE varchar;
ALTER TABLE pn_template ALTER COLUMN primary_properties TYPE varchar;
ALTER TABLE pn_template ALTER COLUMN notify_trigger TYPE varchar;
ALTER TABLE pn_template ALTER COLUMN name TYPE varchar;
ALTER TABLE pn_template ALTER COLUMN sender TYPE varchar;
ALTER TABLE pn_pool_message ALTER COLUMN key_attributes TYPE varchar;
ALTER TABLE pn_receiver ALTER COLUMN target TYPE varchar;
ALTER TABLE pn_receiver ALTER COLUMN type_of_receiver TYPE varchar;
ALTER TABLE pn_receiver ALTER COLUMN locale TYPE varchar;
ALTER TABLE pn_regex ALTER COLUMN note TYPE varchar;
ALTER TABLE pn_regex ALTER COLUMN regex TYPE varchar;
ALTER TABLE pn_template_message ALTER COLUMN locale TYPE varchar;
ALTER TABLE pn_template_message ALTER COLUMN message TYPE varchar;
ALTER TABLE pn_template_message ALTER COLUMN subject TYPE varchar;
ALTER TABLE groups_groups ALTER COLUMN created_by TYPE varchar;
ALTER TABLE groups_groups ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE res_tags ALTER COLUMN tag_name TYPE varchar;
ALTER TABLE res_tags ALTER COLUMN created_by TYPE varchar;
ALTER TABLE res_tags ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE configurations ALTER COLUMN property TYPE varchar;
ALTER TABLE configurations ALTER COLUMN value TYPE varchar;
ALTER TABLE mailchange ALTER COLUMN created_by TYPE varchar;
ALTER TABLE pwdreset ALTER COLUMN created_by TYPE varchar;
ALTER TABLE security_teams ALTER COLUMN name TYPE varchar;
ALTER TABLE security_teams ALTER COLUMN description TYPE varchar;
ALTER TABLE security_teams ALTER COLUMN created_by TYPE varchar;
ALTER TABLE security_teams ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE security_teams_facilities ALTER COLUMN created_by TYPE varchar;
ALTER TABLE security_teams_facilities ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE blacklists ALTER COLUMN description TYPE varchar;
ALTER TABLE blacklists ALTER COLUMN created_by TYPE varchar;
ALTER TABLE blacklists ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE resources_bans ALTER COLUMN description TYPE varchar;
ALTER TABLE resources_bans ALTER COLUMN created_by TYPE varchar;
ALTER TABLE resources_bans ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE facilities_bans ALTER COLUMN description TYPE varchar;
ALTER TABLE facilities_bans ALTER COLUMN created_by TYPE varchar;
ALTER TABLE facilities_bans ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE user_ext_source_attr_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE user_ext_source_attr_values ALTER COLUMN created_by TYPE varchar;
ALTER TABLE user_ext_source_attr_values ALTER COLUMN modified_by TYPE varchar;
ALTER TABLE user_ext_source_attr_u_values ALTER COLUMN attr_value TYPE varchar;
ALTER TABLE members_sponsored ALTER COLUMN created_by TYPE varchar;
ALTER TABLE members_sponsored ALTER COLUMN modified_by TYPE varchar;
update configurations set value='3.1.61' where property='DATABASE VERSION';

3.1.60
alter table tasks alter column engine_id set default 1;
alter table tasks_results alter column engine_id set default 1;
alter table tasks drop constraint task_eng_fk;
alter table tasks_results drop constraint taskres_eng_fk;
drop index idx_fk_task_eng;
drop index idx_fk_taskres_eng;
drop sequence routing_rules_id_seq;
drop sequence processing_rules_id_seq;
drop table engine_routing_rule;
drop table routing_rules;
drop table service_processing_rule;
drop table processing_rules;
drop table engines;
update configurations set value='3.1.60' where property='DATABASE VERSION';

3.1.59
ALTER TABLE ext_sources_attributes ALTER COLUMN attr_value TYPE varchar;
update configurations set value='3.1.59' where property='DATABASE VERSION';
