Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Issues upgrading module from Edelweiss to Fameflower

Description

Upgraded this module from v4.1.1 to v4.2.0 on https://bugfest.folio.ebsco.com.
While comparing the database schemas between the upgraded version and a pure Fameflower released version, identified differences in schemas as in attached file. Upgrade scripts should be written for data migration to be handled by RMB.

Noticed that quite a few of these have "fromModuleVersion" as 4.0.0 - It should probably be greater than 4.1.1 for the migrations to be applied by RMB.

Also noticed that "temporary_invoice_payments" has mode "delete" from v4.0.0 but it has not been picked up by RMB - not sure if its a "FromModuleVersion" issue or whether RMB is not picking up mode of delete.

CSP Request Details

None

CSP Rejection Details

None

Potential Workaround

None

Attachments

5
  • 03 Apr 2020, 11:31 AM
  • 03 Apr 2020, 11:31 AM
  • 02 Apr 2020, 12:47 PM
  • 02 Apr 2020, 12:44 PM
  • 31 Mar 2020, 02:33 AM

Checklist

hide

TestRail: Results

Activity

Show:

Yauheni Kuzmianok April 3, 2020 at 11:32 AM

Verified locally .

  • Pure mod-finance-storage-4.2.1 (folioci/mod-finance-storage:4.3.0-SNAPSHOT.79)

  • Upgrade from mod-finance-storage-4.1.1 (folioci/mod-finance-storage:4.2.0-SNAPSHOT.58) to mod-finance-storage-4.2.1

Result as in comment

Aliaksandr Pautau April 2, 2020 at 12:59 PM

Verified locally.

  1. Pure mod-finance-storage-4.2.1 (folioci/mod-finance-storage:4.3.0-SNAPSHOT.79)

  1. Upgrade from mod-finance-storage-4.1.1 (folioci/mod-finance-storage:4.2.0-SNAPSHOT.58) to mod-finance-storage-4.2.1

During the check, I noticed that copies of foreign keys are created for all tables for which fromModuleVersion was updated.

Created a ticket for the issue https://folio-org.atlassian.net/browse/RMB-597#icft=RMB-597

Craig McNally March 31, 2020 at 6:20 PM

OK so I looked into this a bit and I think I understand what the issue is, let me try to explain.

Looking at the diff of schema.json between 4.1.1 and 4.2.0 I see a bunch of changes.

  • Some of these changes are related to moving migration scripts outside of the table block. In these cases I don't think we expect any DB changes to happen, it's just reorganization of the schema.json.

  • Some changes are legitimate changes which should result in RMB making changes to the DB. In at least one case the fromModuleVersion is still set to 4.0.0, which is incorrect.

    • One example of this is the temporary_invoice_payments table. This was marked as "mode": "delete" in 4.2.0, but the fromModuleVersion is still 4.0.0, but should be 4.2.0.

      • v4.1.1

        { "tableName": "temporary_invoice_payments", "fromModuleVersion": "mod-finance-storage-4.0.0", "ginIndex": [ { "fieldName": "sourceInvoiceId", "tOps": "ADD", "caseSensitive": false, "removeAccents": true } ], "uniqueIndex": [ { "fieldName": "amount, fromFundId, sourceInvoiceId, sourceInvoiceLineId, toFundId, transactionType", "tOps": "ADD" } ] },

      • v4.2.0

        { "tableName": "temporary_invoice_payments", "fromModuleVersion": "mod-finance-storage-4.0.0", "mode": "delete", "auditingTableName": "NOT_EXISTING_AUDITING_TABLE" }

      • in v4.2.1 we should have

        { "tableName": "temporary_invoice_payments", "fromModuleVersion": "mod-finance-storage-4.2.1", "mode": "delete", "auditingTableName": "NOT_EXISTING_AUDITING_TABLE" }

Info The bottom line is that fromModuleVersion should be set to the version that the changes was made in.

Aliaksandr Pautau March 31, 2020 at 3:45 PM

Thanks for the details!

Sobha Duvvuri March 31, 2020 at 3:08 PM

Sure. Here are some differences:
1. In Upgraded version:

CREATE FUNCTION recalculate_totals () RETURNS trigger AS $$ DECLARE fromBudget jsonb; fromBudgetAllocated decimal; fromBudgetAvailable decimal; fromBudgetUnavailable decimal; fromBudgetEncumbered decimal; fromBudgetOverEncumbered decimal; fromLedgerFY jsonb; fromLedgerFYAllocated decimal; fromLedgerFYAvailable decimal; fromLedgerFYUnavailable decimal; toBudget jsonb; toBudgetAllocated decimal; toBudgetAvailable decimal; toLedgerFY jsonb; toLedgerFYAllocated decimal; toLedgerFYAvailable decimal; newBudgetValues text[]; newLedgerValues text[]; amount decimal; transactionType text; BEGIN amount = (NEW.jsonb->>'amount')::decimal; transactionType = NEW.jsonb->>'transactionType'; IF (transactionType = 'Allocation' OR transactionType = 'Transfer' OR transactionType = 'Encumbrance') THEN IF ((NEW.jsonb->'fromFundId' IS NULL) AND (transactionType = 'Encumbrance')) THEN RAISE EXCEPTION 'fromFundId is not specified for Encumbrance'; END IF; IF (NEW.jsonb->'fromFundId' IS NOT NULL) THEN SELECT INTO fromBudget (jsonb::jsonb) FROM fs09000000_mod_finance_storage.budget WHERE (fiscalYearId::text = NEW.jsonb->>'fiscalYearId' AND fundId::text = NEW.jsonb->>'fromFundId'); IF (fromBudget IS NULL) THEN RAISE EXCEPTION 'source budget not found'; END IF; fromBudgetAvailable = (fromBudget->>'available')::decimal - amount; fromBudgetUnavailable = (fromBudget->>'unavailable')::decimal + amount; IF (transactionType = 'Allocation') THEN fromBudgetAllocated = (fromBudget->>'allocated')::decimal - amount; newBudgetValues = '{allocated,' || fromBudgetAllocated || ', available, ' || fromBudgetAvailable ||'}'; ELSIF (transactionType = 'Transfer') THEN newBudgetValues = '{available, ' || fromBudgetAvailable || '}'; ELSIF (transactionType = 'Encumbrance') THEN fromBudgetEncumbered = (fromBudget->>'encumbered')::decimal + amount; newBudgetValues = '{available, ' || fromBudgetAvailable || ', unavailable, ' || fromBudgetUnavailable || ', encumbered, ' || fromBudgetEncumbered ||'}'; END IF; UPDATE fs09000000_mod_finance_storage.budget SET jsonb = jsonb || json_object(newBudgetValues)::jsonb WHERE (fiscalYearId::text = NEW.jsonb->>'fiscalYearId' AND fundId::text = NEW.jsonb->>'fromFundId'); SELECT INTO fromLedgerFY (jsonb::jsonb) FROM fs09000000_mod_finance_storage.ledgerFY AS ledgerFY WHERE (ledgerFY.ledgerId = (SELECT fund.ledgerId FROM fs09000000_mod_finance_storage.fund AS fund WHERE (fund.id::text = fromBudget->>'fundId'))) AND ledgerFY.fiscalYearId::text = fromBudget->>'fiscalYearId'; IF (fromLedgerFY IS NULL) THEN RAISE EXCEPTION 'Ledger fiscal year for source ledger not found'; END IF; fromLedgerFYAvailable = (fromLedgerFY->>'available')::decimal - amount; fromLedgerFYUnavailable = (fromLedgerFY->>'unavailable')::decimal + amount; IF (transactionType = 'Allocation') THEN fromLedgerFYAllocated = (fromLedgerFY->>'allocated')::decimal - amount; newLedgerValues = '{allocated,' || fromLedgerFYAllocated || ', available, ' || fromLedgerFYAvailable || '}'; ELSEIF (transactionType = 'Transfer') THEN newLedgerValues = '{available, ' || fromLedgerFYAvailable ||'}'; ELSIF (transactionType = 'Encumbrance') THEN newLedgerValues = '{available, ' || fromLedgerFYAvailable || ', unavailable, ' || fromLedgerFYUnavailable ||'}'; END IF; UPDATE fs09000000_mod_finance_storage.ledgerFY SET jsonb = jsonb || json_object(newLedgerValues)::jsonb WHERE (ledgerId::text = fromLedgerFY->>'ledgerId') AND (fiscalYearId::text = fromLedgerFY->>'fiscalYearId'); END IF; IF (transactionType = 'Allocation' OR transactionType = 'Transfer') THEN SELECT INTO toBudget (jsonb::jsonb) FROM fs09000000_mod_finance_storage.budget WHERE (fiscalYearId::text = NEW.jsonb->>'fiscalYearId' AND fundId::text = NEW.jsonb->>'toFundId'); IF (toBudget IS NULL) THEN RAISE EXCEPTION 'destination budget not found'; END IF; toBudgetAvailable = (toBudget->>'available')::decimal + amount; IF (transactionType = 'Allocation') THEN toBudgetAllocated = (toBudget->>'allocated')::decimal + amount; newBudgetValues = '{allocated,' || toBudgetAllocated || ', available, ' || toBudgetAvailable ||'}'; ELSIF (transactionType = 'Transfer') THEN newBudgetValues = '{available, ' || toBudgetAvailable ||'}'; END IF; UPDATE fs09000000_mod_finance_storage.budget SET jsonb = jsonb || json_object(newBudgetValues)::jsonb WHERE (fiscalYearId::text = NEW.jsonb->>'fiscalYearId' AND fundId::text = NEW.jsonb->>'toFundId'); SELECT INTO toLedgerFY (jsonb::jsonb) FROM fs09000000_mod_finance_storage.ledgerFY AS ledgerFY WHERE (ledgerFY.ledgerId = (SELECT ledgerId FROM fs09000000_mod_finance_storage.fund WHERE (id::text = toBudget->>'fundId'))) AND ledgerFY.fiscalYearId::text = toBudget->>'fiscalYearId'; IF (toLedgerFY IS NULL) THEN RAISE EXCEPTION 'Ledger fiscal year for destination ledger not found'; END IF; toLedgerFYAvailable = (toLedgerFY->>'available')::decimal + amount; IF (transactionType = 'Allocation') THEN toLedgerFYAllocated = (toLedgerFY->>'allocated')::decimal + amount; newLedgerValues = '{allocated,' || toLedgerFYAllocated || ', available, ' || toLedgerFYAvailable ||'}'; ELSIF (transactionType = 'Transfer') THEN newLedgerValues = '{available, ' || toLedgerFYAvailable ||'}'; END IF; UPDATE fs09000000_mod_finance_storage.ledgerFY SET jsonb = jsonb || json_object(newLedgerValues)::jsonb WHERE (ledgerId::text = toLedgerFY->>'ledgerId') AND (fiscalYearId::text = toLedgerFY->>'fiscalYearId'); END IF; END IF; RETURN NULL; END; $$ LANGUAGE 'plpgsql' COST 100; ALTER FUNCTION recalculate_totals () OWNER TO folio;

In pure FF version:

CREATE FUNCTION recalculate_totals () RETURNS trigger AS $$ DECLARE fromBudget jsonb; fromBudgetAllocated decimal; fromBudgetAvailable decimal; fromBudgetUnavailable decimal; fromBudgetEncumbered decimal; fromBudgetOverEncumbered decimal; fromLedgerFY jsonb; fromLedgerFYAllocated decimal; fromLedgerFYAvailable decimal; fromLedgerFYUnavailable decimal; toBudget jsonb; toBudgetAllocated decimal; toBudgetAvailable decimal; toLedgerFY jsonb; toLedgerFYAllocated decimal; toLedgerFYAvailable decimal; newBudgetValues text[]; newLedgerFYValues text[]; amount decimal; transactionType text; BEGIN amount = (NEW.jsonb->>'amount')::decimal; transactionType = NEW.jsonb->>'transactionType'; IF (transactionType = 'Allocation' OR transactionType = 'Transfer') THEN IF (NEW.jsonb->'fromFundId' IS NOT NULL) THEN SELECT INTO fromBudget (jsonb::jsonb) FROM fs09000000_mod_finance_storage.budget WHERE (fiscalYearId::text = NEW.jsonb->>'fiscalYearId' AND fundId::text = NEW.jsonb->>'fromFundId'); IF (fromBudget IS NULL) THEN RAISE EXCEPTION 'source budget not found'; END IF; fromBudgetAvailable = (fromBudget->>'available')::decimal - amount; fromBudgetUnavailable = (fromBudget->>'unavailable')::decimal + amount; IF (transactionType = 'Allocation') THEN fromBudgetAllocated = (fromBudget->>'allocated')::decimal - amount; newBudgetValues = '{allocated,' || fromBudgetAllocated || ', available, ' || fromBudgetAvailable ||'}'; ELSIF (transactionType = 'Transfer') THEN newBudgetValues = '{available, ' || fromBudgetAvailable || '}'; END IF; UPDATE fs09000000_mod_finance_storage.budget SET jsonb = jsonb || json_object(newBudgetValues)::jsonb WHERE (fiscalYearId::text = NEW.jsonb->>'fiscalYearId' AND fundId::text = NEW.jsonb->>'fromFundId'); SELECT INTO fromLedgerFY (jsonb::jsonb) FROM fs09000000_mod_finance_storage.ledgerFY AS ledgerFY WHERE (ledgerFY.ledgerId = (SELECT fund.ledgerId FROM fs09000000_mod_finance_storage.fund AS fund WHERE (fund.id::text = fromBudget->>'fundId'))) AND ledgerFY.fiscalYearId::text = fromBudget->>'fiscalYearId'; IF (fromLedgerFY IS NULL) THEN RAISE EXCEPTION 'Ledger fiscal year for source ledger not found'; END IF; fromLedgerFYAvailable = (fromLedgerFY->>'available')::decimal - amount; fromLedgerFYUnavailable = (fromLedgerFY->>'unavailable')::decimal + amount; IF (transactionType = 'Allocation') THEN fromLedgerFYAllocated = (fromLedgerFY->>'allocated')::decimal - amount; newLedgerFYValues = '{allocated,' || fromLedgerFYAllocated || ', available, ' || fromLedgerFYAvailable || '}'; ELSEIF (transactionType = 'Transfer') THEN newLedgerFYValues = '{available, ' || fromLedgerFYAvailable ||'}'; END IF; UPDATE fs09000000_mod_finance_storage.ledgerFY SET jsonb = jsonb || json_object(newLedgerFYValues)::jsonb WHERE (ledgerId::text = fromLedgerFY->>'ledgerId') AND (fiscalYearId::text = fromLedgerFY->>'fiscalYearId'); END IF; IF (transactionType = 'Allocation' OR transactionType = 'Transfer') THEN SELECT INTO toBudget (jsonb::jsonb) FROM fs09000000_mod_finance_storage.budget WHERE (fiscalYearId::text = NEW.jsonb->>'fiscalYearId' AND fundId::text = NEW.jsonb->>'toFundId'); IF (toBudget IS NULL) THEN RAISE EXCEPTION 'destination budget not found'; END IF; toBudgetAvailable = (toBudget->>'available')::decimal + amount; IF (transactionType = 'Allocation') THEN toBudgetAllocated = (toBudget->>'allocated')::decimal + amount; newBudgetValues = '{allocated,' || toBudgetAllocated || ', available, ' || toBudgetAvailable ||'}'; ELSIF (transactionType = 'Transfer') THEN newBudgetValues = '{available, ' || toBudgetAvailable ||'}'; END IF; UPDATE fs09000000_mod_finance_storage.budget SET jsonb = jsonb || json_object(newBudgetValues)::jsonb WHERE (fiscalYearId::text = NEW.jsonb->>'fiscalYearId' AND fundId::text = NEW.jsonb->>'toFundId'); SELECT INTO toLedgerFY (jsonb::jsonb) FROM fs09000000_mod_finance_storage.ledgerFY AS ledgerFY WHERE (ledgerFY.ledgerId = (SELECT ledgerId FROM fs09000000_mod_finance_storage.fund WHERE (id::text = toBudget->>'fundId'))) AND ledgerFY.fiscalYearId::text = toBudget->>'fiscalYearId'; IF (toLedgerFY IS NULL) THEN RAISE EXCEPTION 'Ledger fiscal year for destination ledger not found'; END IF; toLedgerFYAvailable = (toLedgerFY->>'available')::decimal + amount; IF (transactionType = 'Allocation') THEN toLedgerFYAllocated = (toLedgerFY->>'allocated')::decimal + amount; newLedgerFYValues = '{allocated,' || toLedgerFYAllocated || ', available, ' || toLedgerFYAvailable ||'}'; ELSIF (transactionType = 'Transfer') THEN newLedgerFYValues = '{available, ' || toLedgerFYAvailable ||'}'; END IF; UPDATE fs09000000_mod_finance_storage.ledgerFY SET jsonb = jsonb || json_object(newLedgerFYValues)::jsonb WHERE (ledgerId::text = toLedgerFY->>'ledgerId') AND (fiscalYearId::text = toLedgerFY->>'fiscalYearId'); END IF; END IF; RETURN NULL; END; $$ LANGUAGE 'plpgsql' COST 100; ALTER FUNCTION recalculate_totals () OWNER TO folio;

2. In Upgraded version: - This could be RMB that's doing this

CREATE INDEX fiscal_year_periodend_idx ON fiscal_year USING btree (lower(f_unaccent(jsonb ->> 'periodEnd'::text)))

In pure version

CREATE INDEX fiscal_year_periodend_idx ON fiscal_year USING btree ("left"(lower(f_unaccent(jsonb ->> 'periodEnd'::text)), 600))

3. This function below is present in pure FF deployment but not in upgraded version -

CREATE FUNCTION update_temporary_invoice_transactions_references () RETURNS trigger AS $$ BEGIN NEW.sourceInvoiceId = (NEW.jsonb->>'sourceInvoiceId'); NEW.paymentEncumbranceId = (NEW.jsonb->>'paymentEncumbranceId'); NEW.fromFundId = (NEW.jsonb->>'fromFundId'); NEW.toFundId = (NEW.jsonb->>'toFundId'); NEW.fiscalYearId = (NEW.jsonb->>'fiscalYearId'); RETURN NEW; END; $$ LANGUAGE 'plpgsql' COST 100 ALTER FUNCTION update_temporary_invoice_transactions_references () OWNER TO folio

4. This index below is present in FF but not in upgraded version:

CREATE INDEX temporary_invoice_transactions_fromfundid_idx ON temporary_invoice_transactions USING btree (fromfundid)

5. This trigger is present in pure FF but not in upgraded:

CREATE TRIGGER set_id_in_jsonb BEFORE INSERT OR UPDATE ON temporary_invoice_transactions FOR EACH ROW EXECUTE PROCEDURE set_id_in_jsonb()

6. The following indices, constraints and tables are in upgraded version but have been dropped in pure FF version:

DROP TRIGGER set_id_in_jsonb ON tenantId_mod_finance_storage.temporary_invoice_payments DROP INDEX tenantId_mod_finance_storage.temporary_invoice_payments_sourceinvoiceid_idx_gin DROP INDEX tenantId_mod_finance_storage.temporary_invoice_payments_amount_fromfundid_sourceinvoiceid_so ALTER TABLE tenantId_mod_finance_storage.temporary_invoice_payments DROP CONSTRAINT temporary_invoice_payments_pkey CASCADE DROP TABLE tenantId_mod_finance_storage.temporary_invoice_payments

The above are few examples that we expected RMB to seamlessly handle but it isn't for some reason. Please investigate if something needs to be corrected like "fromModuleVersion" or "mode" in the module itself or if its an issue with RMB. Thanks!

Done

Details

Assignee

Reporter

Tester Assignee

Labels

Priority

Story Points

Sprint

Development Team

Thunderjet

Parent

Fix versions

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs

Created March 31, 2020 at 2:29 AM
Updated July 13, 2020 at 4:40 PM
Resolved April 3, 2020 at 11:34 AM
TestRail: Cases
TestRail: Runs

Flag notifications