Database user version about database HOT 8 CLOSED

hey, we determine the database version via a entry in table INFOTABLE_V1.INFONAME = 'DATAVERSION'

https://github.com/moneymanagerex/moneymanagerex/blob/3d9aeed1cc7d541a553a0b76f503a18593700f1e/src/model/Model_Infotable.cpp

from database.

Hi, thanks! Great, that's what I thought. On Android, in SQLite helper class, there is a method OnUpgrade which gets this value automatically. The actual value is stored in Pragmas as UserVersion.
I'm interested whether you are using this value anywhere?

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).
What I'm wondering is if I should have a parallel mechanism for upgrading the db schema for the users who are not using the desktop version, or the ones who do not upgrade regularly and are sometimes missing the new tables. So I'm just thinking out loud here.

That leads to another suggestion - 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.
I have not (yet) looked at the source to confirm how it is done currently and what would be the best way to organize this. Looking for thoughts and suggestions.

Perhaps I could re-use the same logic. I guess you just execute the whole script when the db version doesn't match?
This way we lose the ability to migrate any data between versions, though, 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.

from database.

There is no migration scripts/sql yet and we have latest version in one file. that's why we don't like to change the database by modification existing tables. we are ok to add some new tables

from database.

Yes, exactly, that's what I just figured out. I've updated the above post instead of creating new one. Please have a second look.
I'll apply the same logic for now, to update the db schema for the affected users.
We can think about the better migrations system, as suggested above.
Cheers

from database.

great.
BTW, the DAU of the desktop version is around 4k. how about the android version?

from database.

It's actually inside the Android system so no additional load on the app. Just a few custom inherited class that provide data access, and the data model.
But that sometimes creates a problem because I can't influence the SQLite version on a particular system.
There are some strange cases where the same database file works fine on one platform (i.e. Windows) but does not show any data on other (Android, certain versions). For example, I keep this one open to collect more info: moneymanagerex/android-money-manager-ex#517
I suspect the reasons could be bugs in SQLite versions but without further data can't figure it out.

from database.

Database schema version is stored in proper field of the sqlite database file header and is accessible using PRAGMA user_version;.

There are errors in same database files and workaround (see dbUpgrade::FixVersionStatus) must be used to read correct version.

Should this issue be closed?

from database.

Yes, I close the issue.

from database.