Update database schema

[alensiljak]

Ref: https://github.com/moneymanagerex/moneymanagerex/issues/2131

The desktop application v1.4.0 uses a schema that has breaking changes to the current version of the Android app. Analysis of the changes is required, as well as implementation of any changes required by the new schema.

The latest schema is to be taken from https://github.com/moneymanagerex/database repository.

[reinhart1010]

Here's what can I summarize the changes

Changes in Table Names (v11 to v12)

• ~ACCOUNTLIST_V1~ → ACCOUNTLIST
• ~ASSETCLASS_V1~ → ASSETCLASS
• ~ASSETCLASS_STOCK_V1~ → ASSETCLASS_STOCK
• ~ASSETS_V1~ → ASSETS
• ~ATTACHMENT_V1~ → ATTACHMENT
• ~BILLSDEPOSITS_V1~ → BILLSDEPOSITS
• ~BUDGETSPLITTRANSACTIONS_V1~ → BUDGETSPLITTRANSACTIONS
• ~BUDGETTABLE_V1~ → BUDGETTABLE
• ~BUDGETYEAR_V1~ → BUDGETYEAR
• ~CHECKINGACCOUNT_V1~ → CHECKINGACCOUNT
• ~CURRENCYHISTORY_V1~ → CURRENCYHISTORY
• ~CURRENCYFORMATS_V1~ → CURRENCYFORMATS
• ~CUSTOMFIELD_V1~ → CUSTOMFIELD
• ~CUSTOMFIELDDATA_V1~ → CUSTOMFIELDDATA
• ~INFOTABLE_V1~ → INFOTABLE
• ~PAYEE_V1~ → PAYEE
• ~REPORT_V1~ → REPORT
• ~SHAREINFO_V1~ → SHAREINFO
• ~SPLITTRANSACTIONS_V1~ → SPLITTRANSACTIONS
• ~SPLITTRANSACTIONS_V2~ (completely removed)
• ~STOCK_V1~ → STOCK
• ~STOCKHISTORY_V1~ → STOCKHISTORY
• ~SUBCATEGORY_V1~ → SUBCATEGORY
• ~TRANSLINK_V1~ → TRANSLINK

Any _V1 suffix in other tables must be removed

Changes to CURRENCYFORMATS

• Added CURRENCY_TYPE (v7 to v8, v10 to v11)
  • Fiat for traditional/fiat currencies, e.g. USD, EUR
  • Crypto for cryptocurrencies, e.g. BTC, ETH
• Removed UNIT_NAME and CENT_NAME (v8 to v9)
• Added HISTORIC (v9 to v10) for deprecated currencies (see list in v8 to v9)
  • 0 for currencies in use, e.g. USD, EUR
  • 1 for deprecated currencies, e.g. legacy Zimbabwe Dollars (ZWN, ZWR)
• Removed BASECONVRATE (v12 to v13)
  • Before removing, make sure that existing values are moved to CURRENCYHISTORY table with CURRDATE set 1970-01-01

Changes to REFTYPE values in ATTACHMENT and CUSTOMFIELD (v10 to v11)

• ~BankAccount~ → Bank Account
• ~RecurringTransaction~ → Recurring Transaction

For migration process, I'd recommend to write a new SQL script to upgrade from v7 to v13 all at once.

[TobiasDev]

Any updates on this?

[github-actions[bot]]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 5 days.

[wolfsolver]

dB schemas update and aligned to 1.7.0 version desktop. Download latest version.