I have three tables: folder, folder_group, and folder_group_permission.
These are source tables from which I need to unload data to a new schema in new tables where the logic is slightly different in the tables access, access_group, and access_group_permission.
The relations between the tables are as follows:
Table access is linked to table access_group through the ID: access.id = access_group.access_id.
Table access_group is linked to table access_group_permission through the relationship: access_group.id = access_group_permission.access_group_id.
Table folder is linked to table folder_group: folder.id = folder_group.folder_id.
Table folder_group is linked to table folder_group_permission: folder_group.id = folder_group_permission.folder_group_id.
access folder
+----+-----------------+-----------+ +----+--------------+
| id | access_type_id | id_entity | | id | folder_name |
+----+-----------------+-----------+ +----+--------------+
| 1 | 3 | 1 | | 1 | 3 |
| 2 | 3 | 2 | +----+-------- -----+
| 3 | 3 | 3 |
+----+-----------------+-----------+
access_group folder_group
+----+-----------------+-----------+ +----+-----------------+-----------+
| id | group_id | access_id | | id | group_id | folder_id |
+----+-----------------+-----------+ +----+-----------------+-----------+
| 1 | 2 | 1 | | 1 | 2 | 1 |
| 2 | 2 | 1 | | 2 | 2 | 1 |
| 3 | 2 | 3 | | 3 | 2 | 3 |
+----+-----------------+-----------+ +----+-----------------+-----------+
access_group_permission folder_group_permission
+----+-----------------+-----------------+ +----+-----------------+-----------------+
| id | access_group_id | permission_code | | id | folder_group_id | permission_code |
+----+-----------------+-----------------+ +----+-----------------+-----------------+
| 1 | 1 | view | | 1 | 1 | view |
| 2 | 1 | edit | | 2 | 1 | edit |
| 3 | 2 | view | | 3 | 2 | view |
+----+-----------------+-----------------+ +----+-----------------+-----------------+
Here you can see the tables. Id_entity in access table will have all folder_ids so the table can give permissions for all entites even if it is not folder. So access table has all enities and their access types, access_group table has all access_ids and group_id, access_group_permission connects into access table througth access_group table and gives permissions for each entity. And on this relation i want to input folder, folder_group and folder_group_permission.
So here is my script for the whole migration. I understand that my solution is bad, can someone help me how can i do that. I am completely confused =(. Give some ideas how it could be done
DO $$
DECLARE
conn text := 'dbname= host= user= password=';
max_access_id INT;
max_access_group_id INT;
BEGIN
PERFORM dblink_connect('db_connection', conn);
SELECT COALESCE(MAX(id), 0) INTO max_access_id FROM access_control.public.access;
SELECT COALESCE(MAX(id), 0) INTO max_access_group_id FROM access_control.public.access_group_permission;
INSERT INTO access_control.public.access (id, access_type_id, id_entity)
SELECT
max_access_id + ROW_NUMBER() OVER () AS id,
(SELECT id FROM access_type WHERE type_name = 'folder') AS access_type_id,
f.id AS id_entity
FROM
dblink('db_connection',
'SELECT id
FROM folder') AS f(
id BIGINT
)
ON CONFLICT (access_type_id, id_entity) DO NOTHING;
INSERT INTO access_control.public.access_group (id, group_id, access_id)
SELECT
(SELECT COALESCE(MAX(id), 0) FROM access_control.public.access_group) + ROW_NUMBER() OVER () AS id,
fg.group_id,
max_access_id + ROW_NUMBER() OVER () AS access_id
FROM
dblink('db_connection',
'SELECT group_id, folder_id
FROM folder_group') AS fg(
group_id BIGINT,
folder_id BIGINT
);
INSERT INTO access_control.public.access_group_permission (id, access_group_id, permission_code)
SELECT
(SELECT COALESCE(MAX(id), 0) FROM access_control.public.access_group_permission) + ROW_NUMBER() OVER () AS id,
max_access_group_id + ROW_NUMBER() OVER () AS access_group_id,
fgp.permission_code
FROM
dblink('db_connection',
'SELECT folder_group_id, permission_code
FROM folder_group_permission') AS fgp(
folder_group_id BIGINT,
permission_code VARCHAR(128)
)
JOIN access_control.public.access_group ag ON ag.id = fgp.folder_group_id
ON CONFLICT (id) DO NOTHING;
PERFORM dblink_disconnect('db_connection');
END $$;
Here is how i made it.
CREATE EXTENSION IF NOT EXISTS dblink;
INSERT into access_type(type_name, date_from, author_keycloak_uuid, update_date, update_keycloak_uuid)
VALUES ('supeditor.folder', NOW(), 'system', NOW(), 'system')
ON CONFLICT (type_name) DO NOTHING;
DO $$
DECLARE
conn text := 'dbname= host= user= password=';
f_access_id INT;
fg_access_group_id INT;
fgp_access_group_permission_id INT;
folder_row RECORD;
folder_group_row RECORD;
folder_group_permission_row RECORD;
BEGIN
PERFORM dblink_connect('db_connection', conn);
FOR folder_row IN
SELECT id, owner_keycloak_uuid, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid
FROM dblink('db_connection',
'SELECT id,
owner_keycloak_uuid,
date_from,
date_to,
author_keycloak_uuid,
update_date,
update_keycloak_uuid FROM folder')
AS folder_remote(
id INT,
owner_keycloak_uuid varchar(128),
date_from TIMESTAMP,
date_to TIMESTAMP,
author_keycloak_uuid VARCHAR(128),
update_date TIMESTAMP,
update_keycloak_uuid VARCHAR(128)
)
LOOP
INSERT INTO access (access_type_id, id_entity, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid, owner_keycloak_uuid)
VALUES (
(SELECT id FROM access_type WHERE type_name = 'supeditor.folder'),
folder_row.id,
folder_row.date_from,
folder_row.date_to,
folder_row.author_keycloak_uuid,
folder_row.update_date,
folder_row.update_keycloak_uuid,
folder_row.owner_keycloak_uuid
)
RETURNING id INTO f_access_id;
RAISE NOTICE 'Inserted into access, access_id: %', f_access_id;
FOR folder_group_row IN
SELECT id, folder_id, group_id, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid
FROM dblink('db_connection',
'SELECT id,folder_id, group_id, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid FROM folder_group WHERE folder_id = ' || quote_literal(folder_row.id))
AS folder_group_remote(
id BIGINT,
folder_id INT,
group_id INT,
date_from TIMESTAMP,
date_to TIMESTAMP,
author_keycloak_uuid VARCHAR(128),
update_date TIMESTAMP,
update_keycloak_uuid VARCHAR(128)
)
LOOP
INSERT INTO access_group (access_id, group_id, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid)
VALUES (
f_access_id,
folder_group_row.group_id,
folder_group_row.date_from,
folder_group_row.date_to,
folder_group_row.author_keycloak_uuid,
folder_group_row.update_date,
folder_group_row.update_keycloak_uuid
)
RETURNING id INTO fg_access_group_id;
RAISE NOTICE 'Inserted into access_group, access_group_id: %', fg_access_group_id;
FOR folder_group_permission_row IN
SELECT permission_code, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid
FROM dblink('db_connection',
'SELECT permission_code, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid FROM folder_group_permission WHERE folder_group_id = ' || quote_literal(folder_group_row.id))
AS folder_group_permission_remote(
permission_code VARCHAR(128),
date_from TIMESTAMP,
date_to TIMESTAMP,
author_keycloak_uuid VARCHAR(128),
update_date TIMESTAMP,
update_keycloak_uuid VARCHAR(128)
)
LOOP
INSERT INTO access_group_permission (permission_code, access_group_id, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid)
VALUES (
REGEXP_REPLACE(folder_group_permission_row.permission_code, 'folder', 'access', 'g'),
fg_access_group_id,
folder_group_permission_row.date_from,
folder_group_permission_row.date_to,
folder_group_permission_row.author_keycloak_uuid,
folder_group_permission_row.update_date,
folder_group_permission_row.update_keycloak_uuid
)
RETURNING id INTO fgp_access_group_permission_id;
RAISE NOTICE 'Inserted into access_group_permission, access_group_permission_id: %', fgp_access_group_permission_id;
END LOOP;
END LOOP;
END LOOP;
PERFORM dblink_disconnect('db_connection');
END $$