downgrade DB from v1.4.x to v1.3.x (and v1.5.0)

[vomikan]

Solution:

1. extract this zip-file to any folder.
   downgrade_db_13_7_v4.zip

2. Run mmex 1.3.x / 1.5.x and open your DB
   

choose Yes
4) choose previously extracted downgrade_db_13_7_v4.mmdbg file

5) Close the app
6) Run the app and choose your DB file. It's already downgraded.
Happy end

--------------------------------8<-----------------------------------------------------
I have been on 1.4 alpha7 for ages and don't see any active developement on that.
I see 1.3.4 release round the corner. Can you just open up the 1.4 alpha7 file with 1.3.4 please?

MMEX version:

• 1.4.x
• 1.3.x
• 1.2.x
• 1.1 or older

[vomikan]

I was expecting this question. It need to develop a script to restore compatibility.

Yes, development 1.4.0 is suspended. Many new features were added, but there were not enough resources to support the development.
Version 1.3.4 contains many of the fixes from 1.4.0, but the database remains at version 7, like the most popular version 1.3.3.

[reddyshyam]

Thanks @vomikan for the prompt reply and this new ticket. I will keep an eye on this one and downgrade to 1.3.4

Really appreciate all your selfless efforts. 🤝 🙇 👏 🤗

[vomikan]

Seems it's just a patch needed. When the "MMEX database upgrade" warning were closed the following dialog appears:

I believe a proper prepared patch can downgrade DB.

[reddyshyam]

Hi @vomikan,

I can definitely help. Please let me have the patch. Will let you know how it goes.

[vomikan]

Required script would complex.
The following script does upgrade DB.
https://github.com/moneymanagerex/database/blob/master/incremental_upgrade/database_version_12.sql

To downgrade DB it should be the script like the provide by the link above but with reverse logic.
For example (fragment of a possible patch):

CREATE TABLE IF NOT EXISTS ACCOUNTLIST_V1
        (ACCOUNTID integer primary key
        , ACCOUNTNAME TEXT COLLATE NOCASE NOT NULL UNIQUE
        , ACCOUNTTYPE TEXT NOT NULL 
        , ACCOUNTNUM TEXT
        , STATUS TEXT NOT NULL /* Open, Closed */
        , NOTES TEXT, HELDAT TEXT
        , WEBSITE TEXT
        , CONTACTINFO TEXT
        , ACCESSINFO TEXT
        , INITIALBAL numeric
        , FAVORITEACCT TEXT NOT NULL
        , CURRENCYID integer NOT NULL
        , STATEMENTLOCKED integer
        , STATEMENTDATE TEXT
        , MINIMUMBALANCE numeric
        , CREDITLIMIT numeric
        , INTERESTRATE numeric
        , PAYMENTDUEDATE text
        , MINIMUMPAYMENT numeric
);

INSERT INTO ACCOUNTLIST_V1
SELECT
ACCOUNTID
, ACCOUNTNAME
, case when ACCOUNTTYPE in ('Cash','Checking','Credit Card','Loan','Term','Investment','Asset','Shares') then ACCOUNTTYPE else 'Cash' end ACCOUNTTYPE 
, ACCOUNTNUM
, STATUS
, NOTES 
, HELDAT
, WEBSITE
, CONTACTINFO
, ACCESSINFO
, INITIALBAL
, FAVORITEACCT
, CURRENCYID
, STATEMENTLOCKED
, STATEMENTDATE
, MINIMUMBALANCE
, CREDITLIMIT
, INTERESTRATE
, PAYMENTDUEDATE
, MINIMUMPAYMENT
FROM ACCOUNTLIST;

DROP TABLE ACCOUNTLIST;
--etc for all tables
.......
-- finish
PRAGMA user_version = 7;

[reddyshyam]

Hi @vomikan.

Please see if the attached helps. If you confirm, then we can test patching.

database_version_7.zip

[vomikan]

1.3.4	1.4.0

, case when ACCOUNTTYPE != 'Crypto' then ACCOUNTTYPE else 'Cash' end ACCOUNTTYPE 

[reddyshyam]

, case when ACCOUNTTYPE != 'Crypto' then ACCOUNTTYPE else 'Cash' end ACCOUNTTYPE
Hi @vomikan,

Hope you are just documenting the corrections. I have done the changes my end in the above sql.

[vomikan]

CREATE TABLE IF NOT EXISTS ASSETS_V1( ..
then not needed
CREATE TABLE ASSETS_V1( ..

PS Good job! Just delete not needed CREATE TABLE xxx

[vomikan]

it would not work. May be deleted.

-- rename table names used in GRM definitions

UPDATE REPORT SET SQLCONTENT = CONCAT(SQLCONTENT,'_V1') WHERE SQLCONTENT NOT LIKE  '%_V1%' COLLATE NOCASE;
UPDATE REPORT SET LUACONTENT = CONCAT(LUACONTENT,'_V1') WHERE LUACONTENT NOT LIKE '%_V1%' COLLATE NOCASE;
UPDATE REPORT SET TEMPLATECONTENT = CONCAT(TEMPLATECONTENT, '_V1') WHERE TEMPLATECONTENT NOT LIKE '%_V1%' COLLATE NOCASE;

[vomikan]

Finally, all unused tables should be dropped.

DROP TABLE ASSETCLASS;
.....
DROP TABLE SHAREINFO;
-- finish
PRAGMA user_version = 7;

[vomikan]

Impossible.

[vomikan]

PS 90% done!

[reddyshyam]

Impossible.

Except this, done with rest.

Commented out all drop and update sqls just in case needed again.

database_version_7.zip

[reddyshyam]

Impossible.

What are you proposing? drop the column from create ddl and insert sql statements?

[reddyshyam]

CREATE TABLE IF NOT EXISTS ASSETS_V1( ..
then not needed
CREATE TABLE ASSETS_V1( ..

PS Good job! Just delete not needed CREATE TABLE xxx

Thansk @vomikan . I am really glad could help a bit at the least. 😃

[vomikan]

CREATE TABLE IF NOT EXISTS CURRENCYFORMATS_V1(
CURRENCYID integer primary key, 
CURRENCYNAME TEXT COLLATE NOCASE NOT NULL UNIQUE, 
PFX_SYMBOL TEXT, 
SFX_SYMBOL TEXT, 
DECIMAL_POINT TEXT, 
GROUP_SEPARATOR TEXT, 
UNIT_NAME TEXT COLLATE NOCASE, 
CENT_NAME TEXT COLLATE NOCASE, 
SCALE integer, 
BASECONVRATE numeric, 
CURRENCY_SYMBOL TEXT COLLATE NOCASE NOT NULL UNIQUE
);

INSERT INTO CURRENCYFORMATS_V1 SELECT
  CURRENCYID
, CURRENCYNAME
, PFX_SYMBOL
, SFX_SYMBOL
, DECIMAL_POINT
, GROUP_SEPARATOR
, UNIT_NAME
, CENT_NAME
, SCALE
, BASECONVRATE
, CURRENCY_SYMBOL
FROM CURRENCYFORMATS;
DROP INDEX IF EXISTS IDX_CURRENCYFORMATS_SYMBOL;
DROP TABLE CURRENCYFORMATS;
CREATE INDEX IDX_CURRENCYFORMATS_SYMBOL ON CURRENCYFORMATS_V1(CURRENCY_SYMBOL);

[vomikan]

Here:
https://github.com/moneymanagerex/moneymanagerex/tree/v1.3.4/src/db
pieces of code where DB tables created.
https://github.com/moneymanagerex/moneymanagerex/blob/v1.3.4/src/db/DB_Table_Currencyformats_V1.h#L78
etc

[reddyshyam]

Replaced CURRENCYFORMATS_V1 changes listed above.

I am assuming above links were just for my reference.

database_version_7.zip

[vomikan]

Congratulations! This script is working.
I've used it in SQLiteBrowser
database_version_7.txt

Final step is to make mmdbg file from this one.

[reddyshyam]

That's great! Well done @vomikan yourself! 😄

Can't wait to get the patch and apply!

[vomikan]

mmex_DB_downgrade_v13_to_v7.zip
This patch would working with v1.3.4-rc.3

[vomikan]

this release may be used for downgrade DB v13 to V7 using the mmex_DB_downgrade_v13_to_v7.zip file provided in previous post. (The file should be extracted).
https://ci.appveyor.com/project/moneymanagerex/moneymanagerex/builds/31917370

[tosim91]

Been using MMEx since it first came out, on Windows, then on Linux. I've stuck with the 1.40 release all this time, since it worked real good for me all along. I've been in VA hospital for awhile, and got home thgis AM. Upon booting up MMEx, I'm finding it is now v1.5.1, and NOT ACCEPTING MY DB. Even though I requested the debug file, it DOES NOT send it. As I've got a consierable DB, I DO NOT wish to redo from scratch. Will somebody PLEASE send me a link to the 1.4.0deb download link, as I'm not able to find one. Thank you.

[tosim91]

I now have the debug file file, but, am totally lost re how to use it. Sure would just prefer the 1.40deb file. Thank you.

[whalley]

Follow the steps here: #2353

You can ignore step 1 if you have the debug file

[jeepcook]

Hello,
I have a database v7. I think I have to upgrade to V13 but I don't know how to do that. Is there an online method, or anybody here can convert it?
Thanks

[whalley]

@jeepcook - Why do you think you need to upgrade to v13. All the latest MMEX releases (e.g. v1.5.4) run on the v7 database. The v13 database was only applicable to the v1.4.x branch which is now obsolete.

So if you are using the latest MMEX releases with the v7 DB that is fine.

[jeepcook]

@jeepcook - Why do you think you need to upgrade to v13. All the latest MMEX releases (e.g. v1.5.4) run on the v7 database. The v13 database was only applicable to the v1.4.x branch which is now obsolete.

So if you are using the latest MMEX releases with the v7 DB that is fine.

Ok thanks, I didn't understand that !!

With the android version, I have errors when I chek the Bdd:
missing tables:
SPLITTRANSACTIONS_V2
SETTINGS_V1
USAGE_V1

The app works great so I don't know if I have to fix that?

Thanks

[whalley]

OK, if you have Android app related issues then please raise them here: https://github.com/moneymanagerex/android-money-manager-ex/issues

[jeepcook]

In fact, I use both android and pc version. If I have a bdd error on android, I have the same on pc version I think.

[whalley]

Sorry, what is a BDD error?

…

On Thu, 29 Jul 2021, at 10:44, jeepcook wrote: In fact, I use both android and pc version. If I have a bdd error on android, I have the same on pc version I think. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#2353 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADW7MOXATEYKJPJ52KKHCLT2EPHRANCNFSM4L2F2QQQ>.

[jeepcook]

Here is the screenshot

[whalley]

OK, got it. It's French for Database/Datastore....

Looks as if the tables_v1.sql file with the database definitions includes these tables but they are never used in the code anyway.

You can ignore the warning, but probably worth raising an issue with the Android application maintainers to have the offending entries removed. https://github.com/moneymanagerex/android-money-manager-ex/issues

[whalley]

Closing this now.