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)';
foreach p in array ps end loop;
end;
$$
do $$
declare
loop r record;
ps varchar[] := role := 'am|project|' || r.id || '|CONTENT_SCREENING_' || p;array ['READ', 'WRITE'];
p varchar;
role varchar;
grp varchar;
execute 'insert into keycloak.keycloak_role i int;
begin
values (''' || uuid_generate_v4() || ''', ''archive-manager'', false, null,
for r in select id FROM project where archive_initialized = true
loop
''' || role || ''', ''archive-manager'', null, null) on conflict doforeach nothing';p in array ps
loop
execute 'insert into keycloak.group_role_mapping values ((select id from keycloak.keycloak_role where name := format(''' am|project| role || '''),%s|CONTENT_SCREENING_%s', r.id, p);
execute format(select id from'insert into keycloak.keycloak_grouprole wherevalues name = ''am|project|' || r.id ||(%L, ''archive-manager'', false, null,
'|ADMIN'')%L, ''archive-manager'', null, null) on conflict do nothing', uuid_generate_v4(), role);
end loop; grp := format('am|project|%s|ADMIN', r.id);
execute format('select count(*) from keycloak.keycloak_group where name = execute 'insert%L', grp) into 'i;
|| r.id || '.code_table ("_amid", name, cs_name, created) if values (''c20f0ff2-8119-4f3a-b350-fe81a44ccb92'', ''contentScreeningScope'', ''security'', now())i > 0 then
on conflict do nothing'; execute format('insert into keycloak.group_role_mapping
execute 'INSERT INTO ' || r.id || '.code_value (_amid, code, value, ct_name, uid) values ((select id from keycloak.keycloak_role where name = %L),
VALUES (''347e584a-a9d1-4766-9147-70e307360213'', ''100'', ''Kan ikke gjøre noe'', ''contentScreeningScope'',
(select id from keycloak.keycloak_group where name = %L)) on conflict do nothing', role, grp);
''c68170c4-c231-47a6-a57d-a0f05d7a9390'')
end if;
on conflict do nothing'; end loop;
execute 'INSERT INTO ' || r.id || '.code_value (_amid, code, value, ct_name, uid)
execute format('select count(*) from %s.coding_scheme where name = %L', r.id, 'security') into i;
VALUES (''5a114ba7-a31a-4a42-aa8b-7d532904ad0f'', ''90'', ''Kan lese felt'', ''contentScreeningScope'', if i > 0 then
execute format('insert into %s.code_table ''d58a5738-a033-4323-8034-620ba3cf3040'')
("_amid", name, cs_name, created)
on conflict do nothing';
values (''c20f0ff2-8119-4f3a-b350-fe81a44ccb92'', ''contentScreeningScope'', ''security'', now())
execute 'INSERT INTO ' || r.id || ' on conflict do nothing', r.id);
execute format('INSERT INTO %s.code_value (_amid, code, value, ct_name, uid)
VALUES (''38b23289347e584a-7dcfa9d1-4a764766-bce59147-69a2dde2c91070e307360213'', ''80100'', ''Kan leseikke gjøre noe'', ''contentScreeningScope'',
''b6b0b2f4c68170c4-d7cdc231-4a5547a6-9b5fa57d-66d5f0eef624a0f05d7a9390'')
on conflict do nothing', r.id);
execute format('INSERT INTO ' || r.id || '.%s.code_value (_amid, code, value, ct_name, uid)
VALUES (''76677f955a114ba7-5107a31a-471f4a42-9ba9aa8b-6b71d912b2047d532904ad0f'', ''7090'', ''Kan lese felt og skrive felt'', ''contentScreeningScope'',
''c77a1264d58a5738-2879a033-482f4323-84c28034-afe2f04d7e11620ba3cf3040'')
on conflict do nothing', r.id);
execute format('INSERT INTO ' || r.id || '.code_value %s.code_value (_amid, code, value, ct_name, uid)
VALUES (''cbef8ba938b23289-44ff7dcf-4c994a76-897fbce5-64f8661e7aaa69a2dde2c910'', ''6080'', ''Kan lese og skrive felt'', ''contentScreeningScope'',
''e2f44a13b6b0b2f4-55cdd7cd-4c844a55-9fd79b5f-7f53f3e2291166d5f0eef624'')
on conflict do nothing', r.id);
execute format('INSERT INTO ' || r.id || '%s.code_value (_amid, code, value, ct_name, uid)
VALUES (''b8d0317276677f95-c5165107-4b89471f-84dc9ba9-1e29313e69c06b71d912b204'', ''5070'', ''Kan lese felt og skrive felt'', ''contentScreeningScope'',
''a3675c2bc77a1264-6e662879-46cf482f-af3484c2-e84e23b79acaafe2f04d7e11'')
on conflict do nothing', r.id);
end loop; execute end;
$$ |