Atlassian uses cookies to improve your browsing experience, perform analytics and research, and conduct advertising. Accept all cookies to indicate that you agree to our use of cookies on your device. Atlassian cookies and tracking notice, (opens new window)
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.
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:
Snippet 1.3 - Required field
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';
1) Select one existing record from DB
SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;
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:
Snippet 1.4 - Transform from free-form String to enum
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
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.
Snippet 1.7 - Rename duplicates
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:
Snippet 2.1 - Sequence creating
DROP SEQUENCE IF EXISTS ${tenant}_${module}.${sequence} CASCADE;
Snippet 2.2 - Sequence removing
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
Snippet 2.3 - Sequence changing
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;
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');
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
Snippet 3.2 - View removing
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.
1) Retrieve existing view
SELECT DISTINCT ON (lower(f_unaccent(metadata->>'poNumber'))) jsonb FROM diku_mod_orders_storage.orders_view LIMIT 1 OFFSET 0;
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;
MODORDSTOR-89: Spike: Investigate data migration strategiesClosed