As we mentioned this topic separately and inside other issues, I'd open a separate issue here.

On Android, in SQLite helper class, there is a method OnUpgrade which gets the database version value automatically. The actual value is stored in Pragmas as User_Version (ref).

I find it convenient to place the upgrade/migration scripts inside this onUpgrade method, as the db engine takes care of the rest (version upgrade, execution of the scripts).

The suggestion is to modify the current process to store the migration scripts (.sql) from version to version, as separate files that can be executed in sequence, instead of having everything in one create_db script.

This way we would get the ability to migrate any data between versions as well as modifications to existing tables.
If we decide to store file-per-version scripts, then you might apply the same logic and execute all "database_version_" + versionNumber + ".sql" scripts to catch up. I believe this would work well for all the platforms and would make the migrations easier in the future.

The change is not significantly different but would allow Android app to upgrade the schema for the users who have old versions or no desktop application at all.
I'm getting exceptions from users on Linux who are missing tables, for example.

Proposal: store top-level categories in the same table as subcategories but with NULL parent id value.

Two-step implementation:

1. Migrate categories definition into one table but still use separate CATEGID and SUBCATEGID in the related tables
2. Drop CATEGID from tables - store only leaf category in them and read upper level category from parent id relation.

This will allow implementing multi-level category hierarchy in the future too (GUI redesign will be required for this) - moneymanagerex/moneymanagerex#1477.

Upgrade script for first step:

drop table if exists CATEGORY_NEW;

create table CATEGORY_NEW(
CATEGID integer primary key
, CATEGNAME TEXT COLLATE NOCASE NOT NULL
, PARENTID integer references CATEGORY_NEW
);
create unique index IDX_CATEGORY_NEW_CATEGNAME_UNIQ on CATEGORY_NEW(CATEGNAME, ifnull(PARENTID,-1));

insert into CATEGORY_NEW select CATEGID, CATEGNAME, NULL from CATEGORY;
insert into CATEGORY_NEW select NULL, SUBCATEGNAME, CATEGID from SUBCATEGORY;

with map as (
  select s.SUBCATEGID as old, n.CATEGID as new
  from SUBCATEGORY as s
  left join CATEGORY_NEW as n on s.CATEGID=n.PARENTID and s.SUBCATEGNAME=n.CATEGNAME
)
update CHECKINGACCOUNT set SUBCATEGID = (select new from map where map.old=CHECKINGACCOUNT.SUBCATEGID)
where SUBCATEGID<>-1;

drop index IDX_CATEGORY_CATEGNAME;
drop index IDX_SUBCATEGORY_CATEGID;
drop index IDX_CATEGORY_NEW_CATEGNAME_UNIQ;
drop table SUBCATEGORY;
drop table CATEGORY;
alter table CATEGORY_NEW rename to CATEGORY;
create index IDX_CATEGORY_PARENTID on CATEGORY(PARENTID);
create index IDX_CATEGORY_CATEGNAME on CATEGORY(CATEGNAME);
create unique index IDX_CATEGORY_CATEGNAME_UNIQ on CATEGORY(CATEGNAME, ifnull(PARENTID,-1));

what about using this tool to generate img on each release

http://www.schemacrawler.com/diagramming.html

is it possible to add detailed description for schema? could be useful for developers, who wants to

• push some data directly
• read some values

Idea

• Treat all currencies, crypto-currencies and stock shares as a single entity used to describe transaction/account value.
• Reuse checking accounts for both investment and share accounts and store stock purchase/selling transactions as transfers with AMOUNT and TOAMOUNT values.
• Reuse currency history table for storing stock history too.

Benefits

• Model simplification, model code reuse
• Database simplification - consolidate similar entities, enabler for foreign keys implementation #23
• Possibility to reuse generic GUI panels, dialogs etc. moneymanagerex/moneymanagerex#1751
• No need to implement missing GUI elements for shares/investments accounts moneymanagerex/moneymanagerex#1752
• Eliminate separate implementation for commision and use the same implementation for both commision and payment of the dividend (and fee for transfers beetwen normal accounts)
• Ability to report foreign currency or cypto-currency buying as investments moneymanagerex/moneymanagerex#1834
• Eliminate current errors in investment/share implementations moneymanagerex/moneymanagerex#1691 moneymanagerex/moneymanagerex#1679
• Ability to define BILLDEPOSITS for shares and use to implement regular contributions to a pension scheme

Thank you for your amazing work!
Is there an example dataset with some (at least about 100) records to see it "in action"?

All transaction's details from BILLSDEPOSITS can be stored in CHECKINGACCOUNT with TRANSDATE set to NULL. Then BUDGETSPLITTRANSACTIONS can be migrated to SPLITTRANSACTIONS.

• All existing queries to CHECKINGACCOUNT should verify TRANSDATE is not null
• All existing queries to BILLSDEPOSITS should read details from linked CHECKINGACCOUNT and SPLITTRANSACTIONS
• There is no data lose as TRANSDATE is always equal to NEXTOCCURRENCEDATE in BILLSDEPOSITS

Schema changes (BILLSDEPOSITS simplified, BUDGETSPLITTRANSACTIONS removed):

I don't know if data migration could be implemented in pure SQL...

Must be issued DB v. 14 with these patches (updates for lowercase):

UPDATE REPORT SET SQLCONTENT = REPLACE(SQLCONTENT, '_v1', '' ) WHERE SQLCONTENT LIKE '%_v1%';
UPDATE REPORT SET LUACONTENT = REPLACE(LUACONTENT, '_v1', '' ) WHERE LUACONTENT LIKE '%_v1%';
UPDATE REPORT SET TEMPLATECONTENT = REPLACE(TEMPLATECONTENT, '_v1', '' ) WHERE TEMPLATECONTENT LIKE '%_v1%';

1. UI alighment mismatch on export and import
   
   
2. "Decimal char" parameter not used on export.

> select MMEXVERSION from INFOTABLE_V1;
1.4.0-Alpha.4

I think it means database file created with this version. After changing value to random text it's not overwritten nor used/validated...

Should we change this to CREATED_WITH?
What about upgraded db files? Should we add LAST_SAVED_WITH?

What is the current user version of the database schema?
Should we store upgrade scripts separately? Some users are not using the desktop version and I would need to upgrade their schema but I am not sure how to identify the schema versions. Some tables, like stock history, are missing.
Also, I would like to add a table for Asset Allocation and would like it to be synchronised with the desktop version of the application. What is the best way to do this?
Thanks for any tips.

There is a pull request with currency updates requested or submitted by the users (#6). Just a reminder to merge when convenient.

Following moneymanagerex/moneymanagerex#1208 we should provide a patch to set all previous transfers to 2 characters, otherwise we will have some transfers with two characters and some with just one.

Foreign key constraints will allow:

• auto-generate database schema diagrams and documentation
• detect errors in application logic implementation like moneymanagerex/moneymanagerex#1584
• enforce data consistency while modified outside application - mmdbg scripts, external SQL tools

https://www.currency-iso.org/en/home/tables/table-a1.html