euler2/tk: store only labels where we dont need node objects (!114) · Merge requests · digibib / deich2

Petter Goksøyr Åsen requested to merge node-2-label-migration into master Nov 15, 2023
Many properties of service, library_event & audiovideo can be simpliy stored as a text field, we gain nothing by keeping the node object to be referenced by an id.
Note: branch and service references are still object ids, since they represent actuall rows in another table.
SQL migration script:
CREATE TABLE tmp_node_names (
  id    TEXT NOT NULL PRIMARY KEY,
  label TEXT NOT NULL
);

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/ageGroup/000f22b7-392f-4b32-98d7-683c98b6c0ef', '9-10 (Barn)'),
    ('https://tjenester.deichman.no/ageGroup/03413f0f-1b6f-407b-9867-7ede5a63de13', '6-8 (Barn)'),
    ('https://tjenester.deichman.no/ageGroup/85864e7d-680c-48b0-87da-86a63b97c3e7', '0-2 (Barn)'),
    ('https://tjenester.deichman.no/ageGroup/8aff96a6-3f5e-408f-a580-508e327930e0', '18+ (Voksen)'),
    ('https://tjenester.deichman.no/ageGroup/9f4e79df-7c99-4395-8768-bbba39578191', '3-5 (Barn)'),
    ('https://tjenester.deichman.no/ageGroup/aa0e7068-dd23-4210-9cf4-d58d4e785160', 'For alle'),
    ('https://tjenester.deichman.no/ageGroup/0900c0f7-6077-44a2-9987-a9501843d618', '13-15 (Ungdom)'),
    ('https://tjenester.deichman.no/ageGroup/d3bf924b-0d0b-4d77-aafe-29f363b5e75e', '16-18 (Ungdom)'),
    ('https://tjenester.deichman.no/ageGroup/dd8acc79-1ebe-4847-9ce9-6d8474f9d8da', '11-12 (Barn)')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/direction/3e2b95c2-9aff-46fd-8140-c7f07201e3e0', 'Eksternt'),
    ('https://tjenester.deichman.no/direction/52ad3514-3e25-4c9c-9799-015a99fcf38c', 'Internt'),
    ('https://tjenester.deichman.no/direction/b7176704-40f3-44be-a382-7f4ba737d4ed', 'Samarbeid')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/extendedUsage/107b92fe-6aa6-4eec-982c-33f7ac58bfd9', 'Helg'),
    ('https://tjenester.deichman.no/extendedUsage/9ac7a9a6-b8ff-450e-93d6-3c01e7281dc2', 'Ukedag'),
    ('https://tjenester.deichman.no/extendedUsage/ca6328b3-4192-4307-9f53-eec9abde3bc5', 'Meråpent'),
    ('https://tjenester.deichman.no/extendedUsage/f80dfeb2-6b98-4732-8874-ea05a229e735', 'Betjent tid')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/focusArea/11c9993e-df46-43fd-9903-ea0ab3a1e869', 'Temporære og faste programmer voksne'),
    ('https://tjenester.deichman.no/focusArea/40232c03-9fff-4745-b9da-801eeb2aa918', 'Temporære og faste programmer barn'),
    ('https://tjenester.deichman.no/focusArea/81562592-1550-437f-be46-f897fa4bc58b', 'Service og veiledning'),
    ('https://tjenester.deichman.no/focusArea/85f685a1-0425-42e4-9e82-3cfc602355d2', 'Rom, verksted og fasiliteter'),
    ('https://tjenester.deichman.no/focusArea/b725eec4-691b-421c-83a5-b346d4ebead9', 'Digital formidling'),
    ('https://tjenester.deichman.no/focusArea/c14c1a03-5fae-467f-b31d-fb70e0b148ba', 'Fysisk og digital samling'),
    ('https://tjenester.deichman.no/focusArea/c65ccfc8-c057-4d0a-8589-2eec3b473902', 'Temporære og faste programmer ung'),
    ('https://tjenester.deichman.no/focusArea/d827d593-82a4-4dd8-9f45-ec38e0335e3a', 'Utstillinger og romlig formidling')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/frequency/1130a064-7420-4849-b282-7ffe781d66fa', 'Daglig'),
    ('https://tjenester.deichman.no/frequency/2364a7a6-8f91-45fd-b38f-e59158e240ce', 'Annehver uke'),
    ('https://tjenester.deichman.no/frequency/5379417b-89bf-4878-9def-7cfbcd2fd570', 'Månedlig'),
    ('https://tjenester.deichman.no/frequency/742b294e-4708-485a-9a50-8dcf829eeb17', 'Ukentlig'),
    ('https://tjenester.deichman.no/frequency/922ee9f7-23a8-40b8-b3ae-930483720a62', 'Flere ganger i uken'),
    ('https://tjenester.deichman.no/frequency/c49ecc44-bddf-4dfc-8532-b0c28bc50c14', 'Halvårlig'),
    ('https://tjenester.deichman.no/frequency/f1d1e1eb-4892-49e5-88b4-26f8f7b2b1a8', 'Årlig')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/libraryEventPrice/6b050f5a-2ef5-479e-8ac9-449cd264101a', 'Gratis - ingen påmelding'),
    ('https://tjenester.deichman.no/libraryEventPrice/73ae44bb-bf58-403f-b5ac-d8af85ad6325', 'Gratis - påmelding'),
    ('https://tjenester.deichman.no/libraryEventPrice/7a153200-dfa0-4df9-a27c-243385674af5', 'Krever billett'),
    ('https://tjenester.deichman.no/libraryEventPrice/e4aa9535-ac66-49bd-bdbb-3bc77d4325a6', 'Påmelding')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/lifeCycle/040d8899-6c20-499e-a557-92a6510488cf', 'Lære'),
    ('https://tjenester.deichman.no/lifeCycle/18567932-e7e4-44c2-b8f9-a5767534a681', 'Skape'),
    ('https://tjenester.deichman.no/lifeCycle/2a10b669-3db9-4a8a-b13d-5847b6e2675d', 'Oppleve'),
    ('https://tjenester.deichman.no/lifeCycle/3c7e8745-09a2-4a6f-bed5-7a101fd9fb81', 'Være'),
    ('https://tjenester.deichman.no/lifeCycle/43d84e5d-e7e1-4607-9968-690bdc27509d', 'Mene'),
    ('https://tjenester.deichman.no/lifeCycle/76b480c7-53b7-44a2-a900-927f68cc28a3', 'Gjøre'),
    ('https://tjenester.deichman.no/lifeCycle/77de2451-dd51-4a8d-8f10-650d82cc4826', 'Dele'),
    ('https://tjenester.deichman.no/lifeCycle/964059d5-2257-46ad-9c67-1dd18d941be7', 'Oppdage'),
    ('https://tjenester.deichman.no/lifeCycle/ac299e61-54e5-497f-8014-a60e8bda9477', 'Hjelpe'),
    ('https://tjenester.deichman.no/lifeCycle/d7f815d0-e67d-4f28-9bd3-0710a814b260', 'Konsumere')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/reportingCategory/cdd2689c-52ab-11ec-940c-850b93c59754','Skoler og barnehager'),
    ('https://tjenester.deichman.no/reportingCategory/d9e905a0-52ab-11ec-940c-850b93c59754','Samtale og debatt'),
    ('https://tjenester.deichman.no/reportingCategory/e77dce26-52ab-11ec-940c-850b93c59754','Kultur'),
    ('https://tjenester.deichman.no/reportingCategory/f1f44ec0-52ab-11ec-940c-850b93c59754','Opplæring/kurs/verksted'),
    ('https://tjenester.deichman.no/reportingCategory/a0c3bd84-52ab-11ec-940c-850b93c59754','Bibliotekinformasjon/omvisning')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/season/00e40d68-18f3-48a4-99a0-dfdc44893e5b', 'Vinter'),
    ('https://tjenester.deichman.no/season/14d2337a-3dad-4f88-8bc1-54b4790e3098', 'Påske'),
    ('https://tjenester.deichman.no/season/6e64db87-0e5b-4f51-a43e-ba8f6f6c40c5', 'Høst'),
    ('https://tjenester.deichman.no/season/846607b7-282b-4fc9-84bd-93192742983b', 'Høytid'),
    ('https://tjenester.deichman.no/season/8fb57c46-4d4b-44f5-bd98-3328350e8310', 'Jul'),
    ('https://tjenester.deichman.no/season/98a8a409-9735-46b1-9a45-249c40993433', 'Sommer'),
    ('https://tjenester.deichman.no/season/aa37a331-5f98-41a3-8383-da4763166999', 'Ferie'),
    ('https://tjenester.deichman.no/season/d8a5ce0b-c882-42f2-a419-29f334f3323b', 'Vår')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/serviceForm/12730cb3-ae8c-429d-a17e-42375b4c769b', 'Kurs og læring'),
    ('https://tjenester.deichman.no/serviceForm/1638cf3e-e286-11e8-9f32-f2801f1b9fd1', 'Film og spill'),
    ('https://tjenester.deichman.no/serviceForm/18908cb7-01ef-4b25-8899-51683fe9ae4f', 'Festival'),
    ('https://tjenester.deichman.no/serviceForm/3d83b844-6c70-4439-b91b-1a5066766748', 'Verksted og kreativitet'),
    ('https://tjenester.deichman.no/serviceForm/53f2539b-3faf-4a4d-9dca-8951ddd14edd', 'Rom og utstyr'),
    ('https://tjenester.deichman.no/serviceForm/560106d6-45d0-4c02-a921-e830f12d2c39', 'Forestilling'),
    ('https://tjenester.deichman.no/serviceForm/569d6406-0eca-4e0e-a90a-09cf6e44c7aa', 'Lesesirkel'),
    ('https://tjenester.deichman.no/serviceForm/950a5592-d2f6-4619-8756-861597be0548', 'Digital tjeneste'),
    ('https://tjenester.deichman.no/serviceForm/ad522168-a4fb-465e-b025-4f98cb8972a2', 'Musikk og konsert'),
    ('https://tjenester.deichman.no/serviceForm/bb41eec6-78e5-4a62-af42-5b6b69d036bc', 'Samlingen vår'),
    ('https://tjenester.deichman.no/serviceForm/bb91e74f-06d6-47ac-b1bf-77e7ffae613c', 'Godt og blandet'),
    ('https://tjenester.deichman.no/serviceForm/cb77753b-adeb-4759-a966-d28289ad896f', 'Formidling og fortelling'),
    ('https://tjenester.deichman.no/serviceForm/e01b1b1b-d478-4b52-becc-f888207486e9', 'Samtale og debatt'),
    ('https://tjenester.deichman.no/serviceForm/ec8b1821-4847-4fbf-970a-15db2c839ee8', 'Hjelp og veiledning'),
    ('https://tjenester.deichman.no/serviceForm/f6748f50-e566-4478-bf0b-e413f741597d', 'Sosial møteplass')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/serviceType/358566fb-a9a8-44e1-9afc-8149966df6fa', 'Fast program'),
    ('https://tjenester.deichman.no/serviceType/49a3a31d-83bb-4bcb-839b-e65d49095eb9', 'Temporært program'),
    ('https://tjenester.deichman.no/serviceType/f26a783c-fa0a-488e-9a33-ff00b72bec48', 'Fast tjeneste')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/status/36b2f7de-46ac-441e-9bca-c7a65f08b106', 'Utvidet tilbud'),
    ('https://tjenester.deichman.no/status/4dc34455-89ec-4755-83cd-15c9bd58eba4', 'Arkiverte tjenester'),
    ('https://tjenester.deichman.no/status/61602978-f7a0-4fe9-9dff-9107b0c1761d', 'Spesialtilbud'),
    ('https://tjenester.deichman.no/status/68694233-bdfa-4388-a6f1-fef781da5a2d', 'Basistilbud'),
    ('https://tjenester.deichman.no/status/76d3f5b6-1590-4c04-9e34-f29121db20d0', 'Testtjenester')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/targetArea/442f3605-dc48-4cd4-939e-5eb70760077d', 'Kultur og debattprogram'),
    ('https://tjenester.deichman.no/targetArea/b07acf96-9a4c-4644-a24b-7715886ed261', 'Digitale tjenester'),
    ('https://tjenester.deichman.no/targetArea/b16ca1c0-f55a-4208-9777-2860a4c3e598', 'Den nye samlingen'),
    ('https://tjenester.deichman.no/targetArea/f5775154-195d-41f2-ad22-444b65c92d61', 'Rom for inspirasjon og medskaping'),
    ('https://tjenester.deichman.no/targetArea/fc36ef85-b3ee-4a20-ba5c-69318f540f5d', 'Livslang læring')
;

INSERT INTO tmp_node_names (id, label) VALUES
    ('https://tjenester.deichman.no/targetAudience/08330eaf-a9ca-4f14-905c-084236954fab', 'Barn'),
    ('https://tjenester.deichman.no/targetAudience/29104cf3-b7b4-41af-a235-59b4ecb44d0f', 'Barnehager'),
    ('https://tjenester.deichman.no/targetAudience/4d4a5afb-a07b-4e3f-971e-9866046e5d8b', 'Voksne'),
    ('https://tjenester.deichman.no/targetAudience/4d743a4f-c35d-4e4c-b7a3-2ffdc5c4922f', 'Ungdom'),
    ('https://tjenester.deichman.no/targetAudience/542dfdbd-205e-431b-934a-1de23f25e01e', 'Foreldre'),
    ('https://tjenester.deichman.no/targetAudience/5cc5998c-38f7-422c-9df8-ef967be066cb', 'Familie'),
    ('https://tjenester.deichman.no/targetAudience/69D5BB08-B903-11E9-93E4-A540C2478E97', 'For alle'),
    ('https://tjenester.deichman.no/targetAudience/70efde38-647c-438e-a438-54ee9573121f', 'Senior'),
    ('https://tjenester.deichman.no/targetAudience/72363299-3b97-4b6a-b9d4-9ef3a456c854', 'Elever'),
    ('https://tjenester.deichman.no/targetAudience/7559ccca-8864-464a-8e5d-2ac828d6d3ab', 'Asylsøkere og nyankomne'),
    ('https://tjenester.deichman.no/targetAudience/78c97315-8c8d-4564-9560-10a4a13d2469', 'Arbeidssøkere'),
    ('https://tjenester.deichman.no/targetAudience/92e81908-b099-4407-8245-6a41abd98606', 'Skoler og skoleklasser'),
    ('https://tjenester.deichman.no/targetAudience/bbfdf1b4-a2c4-4ad0-b24c-a76629fd0df6', 'Studenter')
;


UPDATE library_service
    SET data=json_set(data, '$.serviceType', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.serviceType.id') ;

UPDATE library_service
    SET data=json_set(data, '$.targetArea', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.targetArea.id') ;

UPDATE library_service
    SET data=json_set(data, '$.targetAudience', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.targetAudience.id') ;

UPDATE library_service
    SET data=json_set(data, '$.direction', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.direction.id') ;

UPDATE library_service
    SET data=json_set(data, '$.focusArea', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.focusArea.id') ;

UPDATE library_service
    SET data=json_set(data, '$.frequency', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.frequency.id') ;

UPDATE library_service
    SET data=json_set(data, '$.reportingCategory', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.reportingCategory.id') ;

UPDATE library_service
    SET data=json_set(data, '$.serviceForm', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.serviceForm.id') ;

UPDATE library_service
    SET data=json_set(data, '$.status', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.status.id') ;

WITH ids AS (
SELECT
    library_service.id AS library_service_id,
    json_group_array(json_extract(obj.value, '$.id')) AS v
FROM library_service,
    json_each(json_extract(data, '$.ageGroup')) obj
    GROUP BY library_service.id
),
result AS (
SELECT library_service_id, json_group_array(tmp_node_names.label) AS array FROM ids,
    json_each(ids.v)
    JOIN tmp_node_names ON tmp_node_names.id = value
GROUP BY library_service_id
)
UPDATE library_service
    SET data=json_set(data, '$.ageGroup', json(result.array))
FROM (SELECT * FROM result) AS result
WHERE result.library_service_id=library_service.id ;

WITH ids AS (
SELECT
    library_service.id AS library_service_id,
    json_group_array(json_extract(obj.value, '$.id')) AS v
FROM library_service,
    json_each(json_extract(data, '$.lifeCycle')) obj
    GROUP BY library_service.id
),
result AS (
SELECT library_service_id, json_group_array(tmp_node_names.label) AS array FROM ids,
    json_each(ids.v)
    JOIN tmp_node_names ON tmp_node_names.id = value
GROUP BY library_service_id
)
UPDATE library_service
    SET data=json_set(data, '$.lifeCycle', json(result.array))
FROM (SELECT * FROM result) AS result
WHERE result.library_service_id=library_service.id ;

WITH ids AS (
SELECT
    library_service.id AS library_service_id,
    json_group_array(json_extract(obj.value, '$.id')) AS v
FROM library_service,
    json_each(json_extract(data, '$.season')) obj
    GROUP BY library_service.id
),
result AS (
SELECT library_service_id, json_group_array(tmp_node_names.label) AS array FROM ids,
    json_each(ids.v)
    JOIN tmp_node_names ON tmp_node_names.id = value
GROUP BY library_service_id
)
UPDATE library_service
    SET data=json_set(data, '$.season', json(result.array))
FROM (SELECT * FROM result) AS result
WHERE result.library_service_id=library_service.id ;

UPDATE library_event
    SET data=json_set(data, '$.libraryEventPrice', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.libraryEventPrice.id') ;

UPDATE library_event
    SET data=json_set(data, '$.serviceForm', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.serviceForm.id') ;

UPDATE library_event
    SET data=json_set(data, '$.targetAudience', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.targetAudience.id') ;

UPDATE audiovideo
    SET data=json_set(data, '$.targetAudience', node.label)
FROM (SELECT * FROM tmp_node_names) AS node
WHERE node.id=json_extract(data, '$.targetAudience.id') ;

UPDATE library_service
    SET data=json_set(data, '$.frequency', json(null))
WHERE json_extract(data, '$.frequency.id') = '';

UPDATE library_service
    SET data=json_set(data, '$.reportingCategory', json(null))
WHERE json_extract(data, '$.reportingCategory.id') = '';

DROP TABLE tmp_node_names;
Edited Nov 15, 2023 by Petter Goksøyr Åsen
euler2/tk: store only labels where we dont need node objects

Merge request reports
