Has this been suggested before? Optional support for a databases other than sqlite, especially for server (docker) installs. Mariadb, mysql are popular options amongst more advanced users (like myself) that self-host applications like Home Assistant, etc.
This might open the door to a whole new set of future capabilities, including perhaps concurrent access to the database from multiple instances of photostructure, or even new third-party capabilities to display library stats, etc. ( I understand these things could also be achieved through REST APIs)
It should certainly take care of several recurring issues with locks, slow fs (and workarounds like the force replica feature).
FWIW, I’m using knex, which helps with cross-db compatibility, but PhotoStructure has to use a lot of CTE and other vendor-specific query generation to support the hierarchical tags.
So, you’re right, it’d open up multi-machine parallelism, which would be hecka nifty, but it’s not just a quick db driver change. It’s at least a couple weeks of work to both do the code updates, stabilize everything, and then set up CI jobs to exercise everything.
Based on my personal experience, but also the last several release notes… It seems to me that sqlite continues to be an issue. I am certainly completely willing to be patient and work through them, but at the same time I was wondering if this “enhancement” request (with only my vote ) could get some additional considerations?
Thanks for the reminder: SQLite has been the most problematic technology in the PhotoStructure stack, but I’m also bending it hard against its intended use.
I personally would love to run PostgreSQL: it’s super stable (I use it for account.photostructure.com!)
I’d have to figure out how to make libraries be portable, though.
I just realized I could run postgres as the “working copy”, and use the db.sqlite3 file more like a “backup” of the database state. I’ll think more about that.
Well, the traditional approach to portability would be to provide a backup and restore function on a settings page (with optionally automated backups options). The backup could create a sqlite representation of the database, while the restore would read the sqlite and (re)load a better database. I don’t know if there are other things beside the db to backup - perhaps one big tarball with all the preview pics?
