1.39.0 - Asta 7 Core

Highlights:

Bug fixes:

https://stiftelsenasta.atlassian.net/browse/AAM-1074

Stories:

https://stiftelsenasta.atlassian.net/browse/AAM-1069

https://stiftelsenasta.atlassian.net/browse/AAM-1078

Tasks:

https://stiftelsenasta.atlassian.net/browse/AAM-1085

https://stiftelsenasta.atlassian.net/browse/AAM-1048

https://stiftelsenasta.atlassian.net/browse/AAM-1075

https://stiftelsenasta.atlassian.net/browse/AAM-1083

https://stiftelsenasta.atlassian.net/browse/AAM-1071

SQL Fixes:

AAM-1069: As a project admin I want to shield archive entities in Asta 7 to only give access to the correct users

alter table field add if not exists create_index bool;
update field set create_index = false;
alter table field alter column create_index set not null;

alter table entity_structure add if not exists create_index bool;
update entity_structure set create_index = true;
alter table entity_structure alter column create_index set not null;

do $$
    declare
        r record;
        ps   varchar[] := array ['READ', 'WRITE'];
        p    varchar;
        role varchar;
    begin
        for r in select id FROM project where archive_initialized = true
            loop
                execute 'create table if not exists ' || r.id || '.content_access_group
(
    _amid               uuid default uuid_generate_v4()   not null
        unique,
    created             timestamp,
    created_by          varchar,
    updated             timestamp,
    updated_by          varchar,
    security_model_name varchar,
    id                  uuid default uuid_generate_v1mc() not null
        primary key,
    name                varchar(36)                       not null
        unique,
    description         varchar
)';
                execute 'alter table ' || r.id || '.content_access_group owner to "archive-manager"';

                execute 'create table if not exists ' || r.id || '.content_screening
(
    _amid                uuid default uuid_generate_v4()   not null
        unique,
    created              timestamp,
    created_by           varchar,
    updated              timestamp,
    updated_by           varchar,
    id                   uuid default uuid_generate_v1mc() not null
        primary key,
    cag_id               uuid                              not null
        constraint fk_ccb0a488_a9a9_4162_a149_494ab3ce7dd9
            references ' || r.id || '.content_access_group
            deferrable initially deferred,
    object_id            uuid                              not null,
    table_name_reference varchar                           not null,
    scope                varchar                           not null,
    level                integer                           not null,
    parent_screening_id  uuid
        constraint fk_9a653aaa_aa8c_4bf9_a69a_9452f876aa75
            references ' || r.id || '.content_screening
            deferrable initially deferred,
    field_names          varchar(2000)
)';
                execute 'alter table ' || r.id || '.content_screening owner to "archive-manager"';
                execute 'create index if not exists index_b49d524e_8148_43aa_9615_b653e50bb633 on ' || r.id ||
                        '.content_screening (object_id)';            
            end loop;
    end;
$$

do $$
    declare
        r    record;
        ps   varchar[] := array ['READ', 'WRITE'];
        p    varchar;
        role varchar;
        grp varchar;
        i int;
    begin
        for r in select id FROM project where archive_initialized = true
            loop
                foreach p in array ps
                    loop
                        role := format('am|project|%s|CONTENT_SCREENING_%s', r.id, p);
                        execute format('insert into keycloak.keycloak_role values (%L, ''archive-manager'', false, null,
                            %L, ''archive-manager'', null, null) on conflict do nothing', uuid_generate_v4(), role);

                        grp := format('am|project|%s|ADMIN', r.id);
                        execute format('select count(*) from keycloak.keycloak_group where name = %L', grp) into i;
                        if i > 0 then
                            execute format('insert into keycloak.group_role_mapping
                    values ((select id from keycloak.keycloak_role where name = %L),
                            (select id from keycloak.keycloak_group where name = %L)) on conflict do nothing', role, grp);
                        end if;
                    end loop;

                execute format('select count(*) from %s.coding_scheme where name = %L', r.id, 'security') into i;
                if i > 0 then
                    execute format('insert into %s.code_table ("_amid", name, cs_name, created)
                values (''c20f0ff2-8119-4f3a-b350-fe81a44ccb92'', ''contentScreeningScope'', ''security'', now())
                on conflict do nothing', r.id);
                execute format('INSERT INTO %s.code_value (_amid, code, value, ct_name, uid)
                VALUES (''347e584a-a9d1-4766-9147-70e307360213'', ''100'', ''Kan ikke gjøre noe'', ''contentScreeningScope'',
                        ''c68170c4-c231-47a6-a57d-a0f05d7a9390'')
                on conflict do nothing', r.id);
                execute format('INSERT INTO %s.code_value (_amid, code, value, ct_name, uid)
                VALUES (''5a114ba7-a31a-4a42-aa8b-7d532904ad0f'', ''90'', ''Kan lese felt'', ''contentScreeningScope'',
                        ''d58a5738-a033-4323-8034-620ba3cf3040'')
                on conflict do nothing', r.id);
                execute format('INSERT INTO %s.code_value (_amid, code, value, ct_name, uid)
                VALUES (''38b23289-7dcf-4a76-bce5-69a2dde2c910'', ''80'', ''Kan lese'', ''contentScreeningScope'',
                        ''b6b0b2f4-d7cd-4a55-9b5f-66d5f0eef624'')
                on conflict do nothing', r.id);
                execute format('INSERT INTO %s.code_value (_amid, code, value, ct_name, uid)
                VALUES (''76677f95-5107-471f-9ba9-6b71d912b204'', ''70'', ''Kan lese felt og skrive felt'', ''contentScreeningScope'',
                        ''c77a1264-2879-482f-84c2-afe2f04d7e11'')
                on conflict do nothing', r.id);
                execute format('INSERT INTO %s.code_value (_amid, code, value, ct_name, uid)
                VALUES (''cbef8ba9-44ff-4c99-897f-64f8661e7aaa'', ''60'', ''Kan lese og skrive felt'', ''contentScreeningScope'',
                        ''e2f44a13-55cd-4c84-9fd7-7f53f3e22911'')
                on conflict do nothing', r.id);
                execute format('INSERT INTO %s.code_value (_amid, code, value, ct_name, uid)
                VALUES (''b8d03172-c516-4b89-84dc-1e29313e69c0'', ''50'', ''Kan lese og skrive'', ''contentScreeningScope'',
                        ''a3675c2b-6e66-46cf-af34-e84e23b79aca'')
                on conflict do nothing', r.id);
                end if;
            end loop;
    end;
$$

2. AAM-1078: As an Asta 7 user I want a set of predefined restrictions to select from when setting up a new restriction

update system_template set template_id = 'd10f1600-00db-4733-b4f5-858feb69103f' where name = 'SECURITY_MODULE';
do $$
    declare
        r record;
    begin
        for r in select id FROM project where archive_initialized = true
            loop
                execute 'alter table if exists ' || r.id || '.restriction add column if not exists is_predefined_for_suggestion boolean null';
                execute 'alter table if exists ' || r.id || '.restriction alter column name type varchar(1000)';
            end loop;
    end; $$

3. AAM-1085: Add startdato & sluttdato on PARTICIPANT_MODULE template AKTOR_RELASJON entity

update system_template set template_id = 'ccff22f4-d532-4d1b-add5-5427d1044e09' where name = 'PARTICIPANT_MODULE';
do $$
    declare
        r record;
    begin
        for r in select id FROM project where archive_initialized = true
            loop
                execute 'alter table if exists ' || r.id || '.aktor_relasjon add column if not exists startdato varchar null';
                execute 'alter table if exists ' || r.id || '.aktor_relasjon add column if not exists sluttdato varchar null';
                execute 'alter table if exists ' || r.id || '.aktor_relasjon drop column if exists object_reference';
            end loop;
    end; $$

4. AAM-1074: DROP CONSTRAINT PKEY error during import when using two child entities with generated PK's in template

How to Upgrade

Upload the latest SECURITY_MODULE and PARTICIPANT_MODULE templates in your current version (most likely 1.38) of Asta7.
Upgrade to 1.39.1
Go to the asta7 directory and run the following script (NB: Script written for old docker compose syntax: “docker-compose”, not “docker compose”). For large archives, you might need to increase max_locks_per_transaction in postgres (64 by default). Use ALTER SYSTEM set max_locks_per_transaction = 512

After Asta7 is up and running import an empty file in all the initialized projects. You should also delete the old SECURITY_MODULE and PARTICIPANT_MODULE templates.

How to Run SQL Fixes Manually

Copy the SQL fixes to a file, let’s assume the file name is upgrade.sql.
Copy the upgrade.sql file to the Postgres container
Run the upgrade.sql file using the Postgres shell, with the default credentials the command would be

Asta Documentation