This does not cover data migration from a previous system into FOLIO!
Requirements:
MODORDSTOR-89
-
Getting issue details...
STATUS
Once our first customers "go-live" and load data into the system, breaking schema changes will need to be handled w/o "starting fresh" or manually intervention.
Considered changes should be made by executing SQL queries directly to the PostgreSQL DB. For most modification scenarious there is no RMB functionality at the moment. Possible test scenarios are presented in drop-down lists.
1. Fields1.1 Field has been renamed in a schema*
RMB doesn't support field renaming existing field, but the following SQL query allows to rename JSONB field:
UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${existed_field}}' || jsonb_build_object('${new_field}', jsonb->'${existed_field}');
* Here and below placeholder ${name} should be changed to the actual name in query.
Example of usage
1) Select one existing record from DB
SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;
{
"id": "d50a347c-ab10-413e-a1c5-3c28e5e3d876",
"name": null,
"notes": [],
"vendor": "f0fb6130-cdf1-11e8-a8d5-f2801f1b9fd1",
"metadata": {
"createdDate": "2019-05-31T17:19:39.602",
"updatedDate": "2019-05-31T17:19:39.855+00",
"createdByUserId": "030d554a-2c41-5a9a-85b9-afafaff88abc",
"updatedByUserId": "030d554a-2c41-5a9a-85b9-afafaff88abc"
},
"poNumber": "10375",
"orderType": "One-Time",
"dateOrdered": "2019-05-31T17:19:39.752+0000",
"workflowStatus": "Open"
}
2) Rename field 'poNumber' by executing query from Snippet 1.1
UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb #- '{poNumber}' || jsonb_build_object('poNumberChanged', jsonb->'poNumber');
3) Select one existing record from DB to be sure that field "poNumber" is changed to "poNumberChanged"
SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;
{
"id": "8e3f16fa-d9bb-4117-8dec-55e0b58ebc79",
"name": null,
"notes": [],
"vendor": "f0fb6130-cdf1-11e8-a8d5-f2801f1b9fd1",
"metadata": {
"createdDate": "2019-05-31T17:19:43.793",
"updatedDate": "2019-05-31T17:19:44.213+00",
"createdByUserId": "030d554a-2c41-5a9a-85b9-afafaff88abc",
"updatedByUserId": "030d554a-2c41-5a9a-85b9-afafaff88abc"
},
"orderType": "One-Time",
"workflowStatus": "Open",
"poNumberChanged": "23421"
}
1.2 Field has been removed from a schema
RMB supports field removing by adding deleteFields
element in the table schema:
"deleteFields": [{
"fieldName": "${existed_field}"
}
]
This corresponds to the following SQL query:
UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${existed_field}}';
Example of usage
1) Select one existing record from DB
SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;
{
"id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
"notes": [
"Cataloging record service"
],
"owner": "Monographs",
"vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
"renewal": {
"cycle": "6 Months",
"interval": 182,
"renewalDate": "2019-04-09T00:00:00.000+0000",
"reviewPeriod": 30,
"manualRenewal": true
},
"approved": true,
"manualPo": true,
"poNumber": "38434",
"orderType": "Ongoing",
"reEncumber": false,
"workflowStatus": "Pending"
}
2) Remove field by executing query from Snippet 1.2 for field workflowStatus
UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb #- '{workflowStatus}';
3) Select one existing record from DB to be sure that workflowStatus
is removed
SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;
{
"id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
"notes": [
"Cataloging record service"
],
"owner": "Monographs",
"vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
"renewal": {
"cycle": "6 Months",
"interval": 182,
"renewalDate": "2019-04-09T00:00:00.000+0000",
"reviewPeriod": 30,
"manualRenewal": true
},
"approved": true,
"manualPo": true,
"poNumber": "38434",
"orderType": "Ongoing",
"reEncumber": false
}
1.3 Field becomes required
Required field means that it should be presented in DB. Namely, we need to check that required field exists. If yes - leave it as is, if no - add required field to JSONB with any default indicator that this value should be presented. RMB doesn't support this scenario but this can be provided by the following SQL queries:
UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${required_field}}' || jsonb_build_object('${required_field}', jsonb->'${required_field}');
UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb_set(jsonb, '{${required_field}}', '"REQUIRED"') WHERE jsonb->'${required_field}' = 'null';
Example of usage
1) Select one existing record from DB
SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;
{
"id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
"notes": [
"Cataloging record service"
],
"owner": "Monographs",
"owners": "required",
"vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
"renewal": {
"cycle": "6 Months",
"interval": 182,
"renewalDate": "2019-04-09T00:00:00.000+0000",
"reviewPeriod": 30,
"manualRenewal": true
},
"approved": true,
"manualPo": true,
"poNumber": "38434",
"orderType": "Ongoing",
"reEncumber": false
}
2) Add required field required_field
by executing query from Snippet 1.3:
UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb #- '{required_field}' || jsonb_build_object('required_field', jsonb->'required_field');
UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb_set(jsonb, '{required_field}', '"required"') WHERE jsonb->'required_field' = 'null';
3) Select one existing record from DB again to be sure that required_field
is added
SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;
{
"id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
"notes": [
"Cataloging record service"
],
"owner": "Monographs",
"owners": "required",
"vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
"renewal": {
"cycle": "6 Months",
"interval": 182,
"renewalDate": "2019-04-09T00:00:00.000+0000",
"reviewPeriod": 30,
"manualRenewal": true
},
"approved": true,
"manualPo": true,
"poNumber": "38434",
"orderType": "Ongoing",
"reEncumber": false,
"required_field": "required"
}
4) Execute query from Snippet 1.3 for existed field orderType
to be sure that there are no any changes in JSONB record
UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb #- '{orderType}' || jsonb_build_object('orderType', jsonb->'orderType');
UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb_set(jsonb, '{orderType}', '"required"') WHERE jsonb->'orderType' = 'null';
SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;
{
"id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
"notes": [
"Cataloging record service"
],
"owner": "Monographs",
"owners": "required",
"vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
"renewal": {
"cycle": "6 Months",
"interval": 182,
"renewalDate": "2019-04-09T00:00:00.000+0000",
"reviewPeriod": 30,
"manualRenewal": true
},
"approved": true,
"manualPo": true,
"poNumber": "38434",
"orderType": "Ongoing",
"reEncumber": false,
"required_field": "required"
}
1.4 Field type has been changed
Since the representation for a string value in the database is identical to the representation of the enumerator element, it suffices to do the same operations as for the string. That is, if the string value does not differ from the enum element, then leave the corresponding entry unchanged, if we need to change the string value to enum value then it is enough to update the value of this field with the following SQL query:
UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb_set(jsonb, '{${required_field}}', '"${new_value}"');
1.4.2 From single value to array or vice-versa
This transformation can be provided for single json value ↔ single element array
UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${changed_field}}' || jsonb_build_object('${changing_field}', json_build_array(jsonb -> '${changing_field}'));
UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${changed_field}}' || jsonb_build_object('${changing_field}', (jsonb -> '${changing_field}')::json ->> 0);
Example of usage
1) Select one existing po_line record from DB
SELECT * FROM diku_mod_orders_storage.po_line LIMIT 1;
{
"id": "774608cf-6e7f-498a-b0c6-3bd38d8b8f9e",
"cost": {
"currency": "USD",
"discount": 47.4,
"discountType": "percentage",
"listUnitPrice": 68,
"additionalCost": 0,
"quantityPhysical": 1,
"quantityElectronic": 0,
"poLineEstimatedPrice": 35.768,
"listUnitPriceElectronic": 0
},
"rush": false,
"tags": [],
"donor": "",
"title": "Bridget Jones's Baby: the diaries",
"alerts": [],
"claims": [
{
"grace": 90,
"claimed": false
}
],
"source": {
"code": "",
"description": "Manually entered"
},
"details": {
"productIds": [
{
"productId": "10407849",
"productIdType": "8261054f-be78-422d-bd51-4ed9f33c3422"
}
],
"receivingNote": "",
"subscriptionInterval": 0
},
"edition": "First edition",
"physical": {
"volumes": [
"vol. 1"
],
"receiptDue": "2018-09-29T00:00:00.000+0000",
"materialType": "d9acad2f-2aac-4b48-9097-e6ab85906b25",
"createInventory": "Instance",
"materialSupplier": "60fb4bfa-cdf1-11e8-a8d5-f2801f1b9fd1"
},
"selector": "Stevens",
"locations": [
{
"quantity": 1,
"locationId": "fcd64ce1-6995-48f0-840e-89ffa2288371",
"quantityPhysical": 1,
"quantityElectronic": 0
}
],
"publisher": "Alfred A. Knopf",
"requester": "",
"collection": false,
"instanceId": "7fbd5d84-62d1-44c6-9c45-6cb173998bbd",
"agreementId": "7324c8eb-5e81-4109-812b-411c26ef09f2",
"description": "",
"orderFormat": "Physical Resource",
"checkinItems": false,
"contributors": [
{
"contributor": "Fielding, Helen",
"contributorType": "2b94c631-fca9-4892-a730-03ee529ffe2a"
}
],
"poLineNumber": "268758-02",
"vendorDetail": {
"instructions": "",
"vendorAccount": "",
"noteFromVendor": ""
},
"paymentStatus": "Awaiting Payment",
"receiptStatus": "Awaiting Receipt",
"reportingCodes": [],
"publicationDate": "2016",
"purchaseOrderId": "efff3ffe-5469-1f8f-aa3a-7afc933f9eff",
"fundDistribution": [
{
"code": "EUROHIST",
"fundId": "e9285a1c-1dfc-4380-868c-e74073003f43",
"percentage": 100,
"encumbrance": "eb506834-6c70-4239-8d1a-6414a5b08003"
}
],
"acquisitionMethod": "Purchase",
"poLineDescription": "",
"cancellationRestriction": false,
"cancellationRestrictionNote": ""
}
2) Transform field 'title' of po_line to single-element array by executing query from Snippet 1.5 and verify that field 'title' has array type:
UPDATE diku_mod_orders_storage.po_line SET jsonb = jsonb #- '{title}' || jsonb_build_object('title', json_build_array(jsonb -> 'title'));
SELECT * FROM diku_mod_orders_storage.po_line LIMIT 1;
{
"id": "774608cf-6e7f-498a-b0c6-3bd38d8b8f9e",
"cost": {
"currency": "USD",
"discount": 47.4,
"discountType": "percentage",
"listUnitPrice": 68,
"additionalCost": 0,
"quantityPhysical": 1,
"quantityElectronic": 0,
"poLineEstimatedPrice": 35.768,
"listUnitPriceElectronic": 0
},
"rush": false,
"tags": [],
"donor": "",
"title": [
"Bridget Jones's Baby: the diaries"
],
"alerts": [],
"claims": [
{
"grace": 90,
"claimed": false
}
],
"source": {
"code": "",
"description": "Manually entered"
},
"details": {
"productIds": [
{
"productId": "10407849",
"productIdType": "8261054f-be78-422d-bd51-4ed9f33c3422"
}
],
"receivingNote": "",
"subscriptionInterval": 0
},
"edition": "First edition",
"physical": {
"volumes": [
"vol. 1"
],
"receiptDue": "2018-09-29T00:00:00.000+0000",
"materialType": "d9acad2f-2aac-4b48-9097-e6ab85906b25",
"createInventory": "Instance",
"materialSupplier": "60fb4bfa-cdf1-11e8-a8d5-f2801f1b9fd1"
},
"selector": "Stevens",
"locations": [
{
"quantity": 1,
"locationId": "fcd64ce1-6995-48f0-840e-89ffa2288371",
"quantityPhysical": 1,
"quantityElectronic": 0
}
],
"publisher": "Alfred A. Knopf",
"requester": "",
"collection": false,
"instanceId": "7fbd5d84-62d1-44c6-9c45-6cb173998bbd",
"agreementId": "7324c8eb-5e81-4109-812b-411c26ef09f2",
"description": "",
"orderFormat": "Physical Resource",
"checkinItems": false,
"contributors": [
{
"contributor": "Fielding, Helen",
"contributorType": "2b94c631-fca9-4892-a730-03ee529ffe2a"
}
],
"poLineNumber": "268758-02",
"vendorDetail": {
"instructions": "",
"vendorAccount": "",
"noteFromVendor": ""
},
"paymentStatus": "Awaiting Payment",
"receiptStatus": "Awaiting Receipt",
"reportingCodes": [],
"publicationDate": "2016",
"purchaseOrderId": "efff3ffe-5469-1f8f-aa3a-7afc933f9eff",
"fundDistribution": [
{
"code": "EUROHIST",
"fundId": "e9285a1c-1dfc-4380-868c-e74073003f43",
"percentage": 100,
"encumbrance": "eb506834-6c70-4239-8d1a-6414a5b08003"
}
],
"acquisitionMethod": "Purchase",
"poLineDescription": "",
"cancellationRestriction": false,
"cancellationRestrictionNote": ""
}
3) Transform single-element array to json field 'title' by executing query from Snippet 1.6 and verify that array was transformed to json field:
UPDATE diku_mod_orders_storage.po_line SET jsonb = jsonb #- '{title}' || jsonb_build_object('title', (jsonb -> 'title')::json->>0);
SELECT * FROM diku_mod_orders_storage.po_line LIMIT 1;
{
"id": "774608cf-6e7f-498a-b0c6-3bd38d8b8f9e",
"cost": {
"currency": "USD",
"discount": 47.4,
"discountType": "percentage",
"listUnitPrice": 68,
"additionalCost": 0,
"quantityPhysical": 1,
"quantityElectronic": 0,
"poLineEstimatedPrice": 35.768,
"listUnitPriceElectronic": 0
},
"rush": false,
"tags": [],
"donor": "",
"title": "Bridget Jones's Baby: the diaries",
"alerts": [],
"claims": [
{
"grace": 90,
"claimed": false
}
],
"source": {
"code": "",
"description": "Manually entered"
},
"details": {
"productIds": [
{
"productId": "10407849",
"productIdType": "8261054f-be78-422d-bd51-4ed9f33c3422"
}
],
"receivingNote": "",
"subscriptionInterval": 0
},
"edition": "First edition",
"physical": {
"volumes": [
"vol. 1"
],
"receiptDue": "2018-09-29T00:00:00.000+0000",
"materialType": "d9acad2f-2aac-4b48-9097-e6ab85906b25",
"createInventory": "Instance",
"materialSupplier": "60fb4bfa-cdf1-11e8-a8d5-f2801f1b9fd1"
},
"selector": "Stevens",
"locations": [
{
"quantity": 1,
"locationId": "fcd64ce1-6995-48f0-840e-89ffa2288371",
"quantityPhysical": 1,
"quantityElectronic": 0
}
],
"publisher": "Alfred A. Knopf",
"requester": "",
"collection": false,
"instanceId": "7fbd5d84-62d1-44c6-9c45-6cb173998bbd",
"agreementId": "7324c8eb-5e81-4109-812b-411c26ef09f2",
"description": "",
"orderFormat": "Physical Resource",
"checkinItems": false,
"contributors": [
{
"contributor": "Fielding, Helen",
"contributorType": "2b94c631-fca9-4892-a730-03ee529ffe2a"
}
],
"poLineNumber": "268758-02",
"vendorDetail": {
"instructions": "",
"vendorAccount": "",
"noteFromVendor": ""
},
"paymentStatus": "Awaiting Payment",
"receiptStatus": "Awaiting Receipt",
"reportingCodes": [],
"publicationDate": "2016",
"purchaseOrderId": "efff3ffe-5469-1f8f-aa3a-7afc933f9eff",
"fundDistribution": [
{
"code": "EUROHIST",
"fundId": "e9285a1c-1dfc-4380-868c-e74073003f43",
"percentage": 100,
"encumbrance": "eb506834-6c70-4239-8d1a-6414a5b08003"
}
],
"acquisitionMethod": "Purchase",
"poLineDescription": "",
"cancellationRestriction": false,
"cancellationRestrictionNote": ""
}
1.4.3 An enum list has changed (entry has been removed, or renamed)
Since the enumerator has the same representation DB as a string value, the queries from 1.1 and 1.2 are relevant to it. Enumerator element can be removed by analogy with the string representation with using query from Snippet 1.2. And the renaming of the enumerator element can be provided by using the query from Snippet 1.4.
1.5 A unique index has been created on a field
It might have that field which should become unique is repeated. An error will occur when trying to create a unique index for this field. That is why it is necessary to ensure uniqueness of this field.
UPDATE ${tenant}_${module}.${table}
SET jsonb = jsonb || jsonb_build_object(${required_field}, jsonb ->> '${required_field}' || ${unique_suffix})
WHERE jsonb ->> '${required_field}' IN (SELECT jsonb->>'${required_field}' FROM ${tenant}_${module}.${table} GROUP BY jsonb ->> '${required_field}' HAVING COUNT(*) > 1);
2. Sequences
RMB doesn't support sequence manipulations. Sequence creation/deletion/modification can be done by customSnippet element in the schema or directly in DB by the following SQL queries:
DROP SEQUENCE IF EXISTS ${tenant}_${module}.${sequence} CASCADE;
CREATE SEQUENCE IF NOT EXISTS ${university}_${module}.${sequence} NO MAXVALUE START WITH ${start_value} CACHE 1 NO CYCLE
It is clear that sequence changing can be done by consequent creation new modified sequence after initial its deletion
DROP SEQUENCE IF EXISTS ${university}_${module}.${sequence} CASCADE;
CREATE SEQUENCE IF NOT EXISTS ${university}_${module}.${sequence} NO MAXVALUE START WITH ${start_value} CACHE 1 NO CYCLE;
Example of usage
1) Retrieve sequence number
SELECT nextval('diku_mod_orders_storage.po_number');
2) Modify sequence (change start value) - delete sequence and create new one with the same name but the other characteristics, verify that start value is changed.
DROP SEQUENCE IF EXISTS diku_mod_orders_storage.po_number CASCADE;
CREATE SEQUENCE IF NOT EXISTS diku_mod_orders_storage.po_number NO MAXVALUE START WITH 22222 CACHE 1 NO CYCLE;
SELECT nextval('diku_mod_orders_storage.po_number');
3. Views
RMB supports view creation:
"views": [
{
"viewName": "view",
"mode": "new",
"joinType": "JOIN",
"join": [
{
"table": {
"tableName": "table_1",
"joinOnField": "table_field_1"
},
"joinTable": {
"tableName": "table_2",
"joinOnField": "table_field_2",
"jsonFieldAlias": "alias_jsonb"
}
}
]
}
]
This corresponds to the following SQL snippet:
CREATE OR REPLACE VIEW diku_mod_orders_storage.view AS select
table_1.id,
table_1.jsonb as jsonb,
table_2.jsonb as alias_jsonb from
diku_mod_orders_storage.table_1 table_1
JOIN
diku_mod_orders_storage.table_2 table_2
on
(table_1.jsonb->>'table_field_1')
=
(table_2.jsonb->>'table_field_2')
;
View can be deleted by execution of the following SQL query
DROP VIEW IF EXISTS ${tenant}_${module}.${view} CASCADE;
View changing can be provided as for sequences, i.e. by deletion of existing view and creation changed one with the same name.
Example of usage
1) Retrieve existing view
SELECT DISTINCT ON (lower(f_unaccent(metadata->>'poNumber'))) jsonb FROM diku_mod_orders_storage.orders_view LIMIT 1 OFFSET 0;
{
"id": "589a6016-3463-49f6-8aa2-dc315d0665fd",
"notes": [],
"owner": "Acquisitions - Serials",
"vendor": "14fb6608-cdf1-11e8-a8d5-f2801f1b9fd1",
"renewal": {
"cycle": "6 Months",
"interval": 182,
"renewalDate": "2019-04-09T00:00:00.000+0000",
"reviewPeriod": 30,
"manualRenewal": true
},
"approved": true,
"manualPo": true,
"poNumber": "101101",
"orderType": "Ongoing",
"reEncumber": false
}
2) Remove view
DROP VIEW IF EXISTS diku_mod_orders_storage.orders_view CASCADE;
3) Modify view (for example, change start value) - create new view with the same name but the other characteristics
CREATE OR REPLACE VIEW diku_mod_orders_storage.orders_view AS SELECT diku_mod_orders_storage.purchase_order.id AS id, diku_mod_orders_storage.purchase_order.jsonb AS jsonb, COALESCE(po_line.jsonb, '{}'::jsonb) || diku_mod_orders_storage.purchase_order.jsonb AS metadata FROM diku_mod_orders_storage.purchase_order LEFT JOIN diku_mod_orders_storage.po_line ON (po_line.jsonb -> 'purchaseOrderId'::text) = (purchase_order.jsonb -> 'id'::text);
4) Retrieve view and verify that it is changed.
SELECT DISTINCT ON (lower(f_unaccent(metadata->>'poNumber'))) jsonb FROM diku_mod_orders_storage.orders_view LIMIT 1 OFFSET 0;
{
"id": "589a6016-3463-49f6-8aa2-dc315d0665fd",
"notes": [],
"owner": "Acquisitions - Serials",
"vendor": "14fb6608-cdf1-11e8-a8d5-f2801f1b9fd1",
"renewal": {
"cycle": "6 Months",
"interval": 182,
"renewalDate": "2019-04-09T00:00:00.000+0000",
"reviewPeriod": 30,
"manualRenewal": true
},
"approved": true,
"manualPo": true,
"poNumber": "101101",
"orderType": "Ongoing",
"reEncumber": false
}
More details for PostgreSQL JSON functions can be founded here: https://www.postgresql.org/docs/10/functions-json.html.