<commit_before>-- Before being able to update, we need to know who to grant ownership of the
-- database views, i.e. the primary IWS user.

-- This is the 1.2.0 release script to update the database to newest state.
-- The changes makes the system incompatible with earlier versions of the IWS,
-- so let's add the information that this is the next DB version, and the IWS
-- version it is related to.
-- Commented out, as the current updates will not change the data model, only
-- correct some problems discovered with the Views & Data Content
insert into versions (db_version, iws_version) values (9, '1.2.0');


-- =============================================================================
-- Issue #2: Storage
-- =============================================================================
alter table users alter column private_date set default 'PROTECTED';
update grouptypes set folder_type = 'PROTECTED' where folder_type = 'PRIVATE';


-- =============================================================================
-- Issue #4 EULA corrections
-- =============================================================================
-- Considering the importance of Data Protection, it is necessary to frequently
-- update the EULA to reflect changes.
alter table users drop constraint user_notnull_eula_version;
alter table users drop column eula_version;
alter table users add column eula_version varchar(50) default '';
alter table users add constraint user_notnull_eula_version check (eula_version is not null);
update users set eula_version = '';


-- =============================================================================
-- Issue #6: Process Role
-- =============================================================================
insert into permissions (id, permission, restricted) values (205, 'PROCESS_ROLE', false);
insert into permission_to_grouptype (grouptype_id, permission_id) values (2, 205);
insert into permission_to_role (role_id, permission_id) values (1, 205);
insert into permission_to_role (role_id, permission_id) values (2, 205);
update permissions set restricted = false where id in (101, 111, 121, 202);


-- =============================================================================
-- Issue #11 Sequence Correction for Student Applications
-- =============================================================================
-- The Student Application Entity was incorrectly set to use the Offer Sequence,
-- which means that the changes to trust the DB identity generation will cause
-- a problem for the existing sequence as it was never used.
--   The following correction cannot be applied directly, but must be checked
-- against the latest information from the database, as an alter sequence
-- doesn't allow sub queries.

-- First the query to read the latest value to be used.
select max(id) + 1 as restart_value from student_applications;

-- The input from this, must be taken and used in the following, the default is
-- the value which was present at the latest available snapshot (2016-05-30):
alter sequence student_application_sequence restart with 52607;
-- =============================================================================


-- =============================================================================
-- Issue #18: Resolving Language & Language Level information
-- =============================================================================
-- There is a problem with some of the Language information for Offers in the
-- Database, below is the example of Offers where the information is set for the
-- third but not second, which must be corrected:
-- iws=> select ref_no, language_1, language_1_level, language_1_op, language_2, language_2_level, language_2_op, language_3, language_3_level from offers where language_2 is null and language_3 is not null;
--      ref_no     | language_1 | language_1_level | language_1_op | language_2 | language_2_level | language_2_op | language_3 | language_3_level
-- ----------------+------------+------------------+---------------+------------+------------------+---------------+------------+------------------
--  SI-2010-A00001 | ENGLISH    | E                | A             |            | F                | A             | ENGLISH    | F
--  CZ-2010-A00085 | ENGLISH    | G                | O             |            | F                | A             | ENGLISH    | E
--  CZ-2011-A00027 | ENGLISH    | G                | O             |            | F                | A             | ENGLISH    | E
--  KZ-2009-A00001 | ENGLISH    | E                | A             |            | G                | A             | ENGLISH    | F
--  KZ-2014-A00143 | ENGLISH    | G                | A             |            | F                | A             | ENGLISH    | F
--  TN-2014-A00625 | ENGLISH    | G                | A             |            |                  | A             | ENGLISH    | G
--  DE-2012-A03544 | ENGLISH    | G                | A             |            | G                | O             | ENGLISH    | G
--  DE-2012-A03545 | ENGLISH    | G                | A             |            | G                | O             | ENGLISH    | G
--  SI-2010-A00016 | ENGLISH    | E                | A             |            | F                | A             | ENGLISH    | E
--  CZ-2013-A00283 | ENGLISH    | G                | A             |            |                  | A             | ENGLISH    | G
--  SI-2010-A00015 | ENGLISH    | E                | A             |            | F                | A             | ENGLISH    | E
--  CZ-2010-A00060 | ENGLISH    | G                | O             |            | F                | A             | ENGLISH    | G<commit_msg>Corrected an alter command for the update script.<commit_after>-- Before being able to update, we need to know who to grant ownership of the
-- database views, i.e. the primary IWS user.

-- This is the 1.2.0 release script to update the database to newest state.
-- The changes makes the system incompatible with earlier versions of the IWS,
-- so let's add the information that this is the next DB version, and the IWS
-- version it is related to.
-- Commented out, as the current updates will not change the data model, only
-- correct some problems discovered with the Views & Data Content
insert into versions (db_version, iws_version) values (9, '1.2.0');


-- =============================================================================
-- Issue #2: Storage
-- =============================================================================
alter table users alter column private_data set default 'PROTECTED';
update users set private_data = 'PROTECTED';
update grouptypes set folder_type = 'PROTECTED' where folder_type = 'PRIVATE';


-- =============================================================================
-- Issue #4 EULA corrections
-- =============================================================================
-- Considering the importance of Data Protection, it is necessary to frequently
-- update the EULA to reflect changes.
alter table users drop constraint user_notnull_eula_version;
alter table users drop column eula_version;
alter table users add column eula_version varchar(50) default '';
alter table users add constraint user_notnull_eula_version check (eula_version is not null);
update users set eula_version = '';


-- =============================================================================
-- Issue #6: Process Role
-- =============================================================================
insert into permissions (id, permission, restricted) values (205, 'PROCESS_ROLE', false);
insert into permission_to_grouptype (grouptype_id, permission_id) values (2, 205);
insert into permission_to_role (role_id, permission_id) values (1, 205);
insert into permission_to_role (role_id, permission_id) values (2, 205);
update permissions set restricted = false where id in (101, 111, 121, 202);


-- =============================================================================
-- Issue #11 Sequence Correction for Student Applications
-- =============================================================================
-- The Student Application Entity was incorrectly set to use the Offer Sequence,
-- which means that the changes to trust the DB identity generation will cause
-- a problem for the existing sequence as it was never used.
--   The following correction cannot be applied directly, but must be checked
-- against the latest information from the database, as an alter sequence
-- doesn't allow sub queries.

-- First the query to read the latest value to be used.
select max(id) + 1 as restart_value from student_applications;

-- The input from this, must be taken and used in the following, the default is
-- the value which was present at the latest available snapshot (2016-07-18):
alter sequence student_application_sequence restart with 52607;
-- =============================================================================


-- =============================================================================
-- Issue #18: Resolving Language & Language Level information
-- =============================================================================
-- There is a problem with some of the Language information for Offers in the
-- Database, below is the example of Offers where the information is set for the
-- third but not second, which must be corrected:
-- iws=> select ref_no, language_1, language_1_level, language_1_op, language_2, language_2_level, language_2_op, language_3, language_3_level from offers where language_2 is null and language_3 is not null;
--      ref_no     | language_1 | language_1_level | language_1_op | language_2 | language_2_level | language_2_op | language_3 | language_3_level
-- ----------------+------------+------------------+---------------+------------+------------------+---------------+------------+------------------
--  SI-2010-A00001 | ENGLISH    | E                | A             |            | F                | A             | ENGLISH    | F
--  CZ-2010-A00085 | ENGLISH    | G                | O             |            | F                | A             | ENGLISH    | E
--  CZ-2011-A00027 | ENGLISH    | G                | O             |            | F                | A             | ENGLISH    | E
--  KZ-2009-A00001 | ENGLISH    | E                | A             |            | G                | A             | ENGLISH    | F
--  KZ-2014-A00143 | ENGLISH    | G                | A             |            | F                | A             | ENGLISH    | F
--  TN-2014-A00625 | ENGLISH    | G                | A             |            |                  | A             | ENGLISH    | G
--  DE-2012-A03544 | ENGLISH    | G                | A             |            | G                | O             | ENGLISH    | G
--  DE-2012-A03545 | ENGLISH    | G                | A             |            | G                | O             | ENGLISH    | G
--  SI-2010-A00016 | ENGLISH    | E                | A             |            | F                | A             | ENGLISH    | E
--  CZ-2013-A00283 | ENGLISH    | G                | A             |            |                  | A             | ENGLISH    | G
--  SI-2010-A00015 | ENGLISH    | E                | A             |            | F                | A             | ENGLISH    | E
--  CZ-2010-A00060 | ENGLISH    | G                | O             |            | F                | A             | ENGLISH    | G
{'fraction_non_alphanumeric': 0.2364519205757392, 'fraction_numerical': 0.03173502124338854, 'mean_word_length': 2.0880856760374833, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 72, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1378728', 'n_tokens_mistral': 3004, 'n_tokens_neox': 2599, 'n_words': 1035}