1.39.0 - Asta 7 Core

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;
$$

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; $$

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; $$

do $$
    declare
        r record;
        sql varchar;
    begin
        for r in SELECT
                     tc.table_schema,
                     tc.constraint_name,
                     tc.table_name,
                     kcu.column_name,
                     ccu.table_schema AS foreign_table_schema,
                     ccu.table_name AS foreign_table_name,
                     ccu.column_name AS foreign_column_name
                 FROM
                     information_schema.table_constraints AS tc
                         JOIN information_schema.key_column_usage AS kcu
                              ON tc.constraint_name = kcu.constraint_name
                                  AND tc.table_schema = kcu.table_schema
                         JOIN information_schema.constraint_column_usage AS ccu
                              ON ccu.constraint_name = tc.constraint_name
                                  AND ccu.table_schema = tc.table_schema
                 WHERE tc.constraint_type = 'FOREIGN KEY' and tc.table_schema in (select id from project where archive_initialized = true)
            loop
                sql := 'alter table ' || r.table_schema || '.' || r.table_name ||' drop constraint if exists '|| r.constraint_name;
                -- raise notice '%', sql;
                execute sql;
            end loop;

        for r in select schemaname, tablename, indexname from pg_indexes where schemaname in (select id from project where archive_initialized = true) and indexname like 'index%'
            loop
                sql := 'drop index if exists ' || r.schemaname || '.' || r.indexname;
                execute sql;
                -- raise notice '%', sql;
            end loop;
    end;
$$;