Consider switching to SQLite backend as the only storage type
Maintaining 2 separate types (or several, if you count all supported flavors of SQL databases) of storage is an overkill we should not afford due to lack workforce, as well as unnecessary confusion brought on our newbie users.
We already have an SqLite3 backend (together with SqlCipher encrypted version of it) that offer several advantages over the XML:
- Less confusion on the non-technical user end. But even for a techie user having to decide between the two is unnecessary mental overhead. Everyone just wants to use the app, not dig deep into "pros and cons" of each right from the beginning. I understand if both formats were maintained for the sake of transitioning users to a new one, but this status quo has been in place for several years.
- Simplified Application Development
- No custom code for handling SqLite file is needed
- XML code can be removed
- GnuPG code can be removed, since the SqLite database comes with its own, robust encryption.
- Single-File Documents We can allow to attach additional files to transactions and embed them in the database file
- Accessible Content
- SQLite database file is accessible using commonly available open-source command-line tools - tools that are installed by default on Mac and Linux systems and that are freely available as a self-contained EXE file on Windows.
- Atomic Transactions
- No danger of corrupting a document just because the power happened to go out at the same instant that a change was being written to disk.
- since SqLite3 is a transactional DB, multiple changes can be grouped together such that either all or none of them occur, and so that the changes can be rolled back if a problem is found prior to commit.
- Incremental And Continuous Updates
- When writing to an SQLite database file, only those parts of the file that actually change are written out to disk. This makes the writing happen faster and saves wear on SSDs. This is an enormous advantage over our gzipped XML file which requires a rewrite of the entire document in order to change a single byte.
- as mentioned above, the file doesn't have to be rewritten completely when saved.
- faster for raw read and writes, since the file is stored in binary format
- SQLite can dramatically improve start-up times because instead of having to read and parse the entire document into memory. In our case the XML file format is gzipped when stored on the filesystem, but is loaded in memory uncompressed.
- our Home Screen presents some summaries of user's Net Worth etc.
KHomeViewPrivate::showNetWorthGraphcontributes to 36% of the overall startup time, as measured by myself using a profiler. Those summaries are calculated using high-level objects, which could be drastically sped up by a dedicated SQL query. This also applies to all other places where we shows summaries and sums of transactions.
- additionally, an Analyzer and Indexing can be used to further optimize the most common and/or heavy queries.
- enables paging: the app only needs to load as much material as is needed to draw on the currently displayed screen and can discard information from prior screens that is no longer in use. This should be doable with the recently added support for models.
- Easily Extensible
- As an application grows, new features can be added to an SQLite application file format simply by adding new tables to the schema or by adding new columns to existing tables. We already have support in the code for upgrading the database.
- XML upgrades require rewriting substantial parts of the code, while SQL upgrades require rewriting the data access layer's low-level queries only.
- Concurrent Use By Multiple Processes
- Multiple Programming Languages
- some additional scripts can be provided externally, e.g for obfuscation/anonymization
- sqlite access can be exposed via API for plugins to optionally manipulate it directly
(Redacted using https://sqlite.org/appfileformat.html)
The potential advantage of XML is being able to
- ungzip the file and and inspect/edit the XML by hand
The potential disadvantages of supporting other SQL backends:
- KMyMoney is personal finance software, SqLite is perfectly capable of handling the amount of data it could ever generate
- Additional amount code to support, especially the SQL migration scripts
- Not many users can take advantage of it, really just other developers and/or DB admins
- Increases the size of the binary builds with all of the Qt SQL backends required included