[Feature Request]: Minor Versioning Support for Library Database

[CyanVoxel]

Checklist

• I am using an up-to-date version.
• I have read the documentation.
• I have searched existing issues.

Description

Currently, the SQLite database uses a single integer for its internal versioning format (DB_VERSION) that is only being used for major versioning. This means that if a change were to be made to the database that in theory could be backwards compatible, for example adding a new column to the tags table, it is instead always considered a breaking change and cannot be opened with previous versions of TagStudio due to the hard limit of the DB_VERSION number increasing.

Solution 1: Semver

Semantic versioning would allow there to be a major, minor, and patch version for each database change. This format is already widely used in the software world for programs themselves, including TagStudio's own versions.

While standard and useful, using semver for the database format would still introduce a couple of quirks and issues. One would be that the data type for the DB_VERSION column would have to change from an INTEGER/int to a VARCHAR/string, cementing some nasty type un-strictness. Semver is also kind of overkill for a use case like this, as it's pretty unlikely that we would ever need a patch version for the database format.

Solution 2: Custom "Hundreds" Versioning

Instead of worrying about changing the data type and anticipating loading older databases with a different column type, it would be possible to continue using a single encoded integer to represent both major and minor versions. My proposal here is to skip to DB_VERSION 100 and use the hundreds place for major versions, with the tens and ones digits for minor versions (no patch version, just 99 potential minor versions). Not only would this be adequate for storing more than enough minor version updates, it would also be completely backwards compatible with the existing versioning code. Personally I feel this is the better solution over semver for this particular use case.

Extra Considerations

When would this go into place?

A semver system would take more time and resources to roll out than a "hundreds" versioning system. The later could be introduced in the next patch release to more quickly make TagStudio forwards compatible with future minor database changes.

How would concurrent development of database major and minor versions work?

If we found ourselves in a situation where we wanted to introduce a minor update to the database for release in a minor update or patch while also having existing major in a major feature branch, it should be possible to achieve this intuitively enough. TagStudio versions that internally advertise themselves as being compatible with any major database version (i.e. int 200 or semver 9.2.0) should be compatible with any minor version from the previous major cycles, regardless of their release date.

For example if TagStudio v9.6.0 used DB_VERSION 200 and TagStudio v9.7 was in development with a major change bumping the version to 300, if a minor change to the database was made for the v9.6.1 patch cycle and brought that DB_VERSION to 201, then the working version of TagStudio v9.7 should still be able to be compatible with this 201 database, even though it includes a change that the program is not yet aware of. Let me be clear: this is not an implicit feature of minor versioning, but rather a logical step that would need to be taken to determine if a database change considered "breaking" or not. If a breaking major change needed to be introduced in a patch update while a future major version of TagStudio has already introduced a higher major version, I believe the TagStudio patch update would then need to "leapfrog" the in-development version, which would then leapfrog the released patch version. I can clarify this further if needed.

[RustyNova016]

I think there's a far simpler solution:

• DB_VERSION remains the major version. If the version don't match, prevent loading / Migrate
• Add DB_VERSION_MINOR: doesn't prevent loading the library, but keep migrating.

Then here:

TagStudio/src/tagstudio/core/library/alchemy/library.py

Lines 381 to 382 in e115443

	if db_result:
	db_version = db_result.value

Just load the minor version, then can be used here in something like:

TagStudio/src/tagstudio/core/library/alchemy/library.py

Lines 484 to 485 in e115443

	if db_version < 9:
	self.apply_db9_schema_changes(session)

                if db_version < 9 and db_version_minor < 2:
                    self.apply_db9_2_schema_changes(session)

[CyanVoxel]

I think there's a far simpler solution:

* `DB_VERSION` remains the major version. If the version don't match, prevent loading / Migrate

* Add `DB_VERSION_MINOR`: doesn't prevent loading the library, but keep migrating.

This was something I initially considered but ultimately ruled out due to several issues I feel make it more complex than the alternatives:

1. The library loading logic now has to always assume that any library it tries to open does not have a DB_VERSION_MINOR variable and has to include error handling for that case. This now becomes a persistent problem that all future loading and migration code has to account for.

2. Having two variables to represent one value I feel is bad practice, and will be confusing to anyone working with the database format or underlying code.

3. Since this solution is ultimately adding in a new variable anyway, it has the same overhead as adding in a new string-based semver variable but without the full benefits of having a single major.minor.path version stored in a single variable.

In comparison, the "hundreds" versioning solution is a drop-in solution that does not add additional legacy library considerations while still meeting the functionality of a major and minor version.

[purpletennisball]

Hundreds versioning makes most sense. We wouldn't have to change anything in the database to start implementing it, its backwards compatible with old versions of the program that would look for the DB_VERSION, and is easier for external programs that also use the TagStudio database to transition to.

[CyanVoxel]

Implemented in 74383e3 as an extension of #998