I'm curious your thoughts around SQL databases. On the one hand, it standardizes your database functions around what's been the norm in industry for decades, which is cool. But on the other hand, you're
adding a dependency and you'll ultimately open yourself up to "can we
use postgres" "can we use mongo" etc.
I'm curious what you see as the benefit?
I don't think there are too many benefits considering the effort it would take to switch over to it. The TLDR version is that if I were starting from scratch today I think I'd use SQLite3, but I am not sure if the effort required to switch over to it justifies the gains at this point.
If Mystic's data files were properly indexed I don't think there would be much or any performance gain, and it would add the first required dependancy for Mystic to function like you said.
It would also remove JAM message bases and support for all of the third party tossers. JAM itself has its own set of limitations though, and Mystic's tosser is probably good enough to support being the only tosser these days. This may not be as big of a con as it was in the recent past.
SQLITE has some issues with concurrancy and scaling since its not really designed for that, but its not like BBSes are huge. I've ran into some of this stuff in my real life job about 8(?) years ago when I was moving a massive DB from SQLITE3 to IBM DB2. But that was also 8 years ago and some of it could have been user error.
I've read of issues vaccuming (packing) large databases and also issues with inserting large amounts of small inserts on large indexed databases, so I would have to "proof of concept" those to see what that really looks like first.
(I'd be curious to see how mass uploading 10,000 new files compares to what MUTIL does today since that would need to scan for duplicates and insert 10,000 records. I'd probably start there)
As far as pros, it would simplify the code once everything is working since all of the file locking and disk writing would be left up to SQLITE.
It would make adding new columns into a database schema much easier since SQL does all the legwork for you and when I do it with direct data, I have to rebuild the file manually.
It would provide better access to the data outside of Mystic for writing utilities. This is both a pro and a con because its easier for people to experiment and mess up their data, but having SQL would be better for people to whip up utility functions in whatever script language they like.
It'd also make it easier for on-the-fly sorting of data for things like message listings and file listings, since Mystic today currently relies on sorting that data on disk. All of the internal paginating could probably be moved to SQL although that I've read is another thing that SQLite isn't amazing at doing.
Memory usage would probably increase significantly. Mystic is very lightweight when it comes to memory used when reading data, and SQLITE would require much more. I don't know the amount it would use offhand but I do know that if you deprive it of memory it will sometimes slow it to a crawl. On the flip, Mystic today doesn't use more than maybe 8-20 kilobytes when reading data files. If there were scaled across a decent number of nodes the difference might be pretty massive.
SQLite support would probably be best left for a Mystic 2 spin off given the amount of work it'd probably take to rewrite all the message base and FidoNet/QWK stuff. It seems like it'd be easier to just build something from scratch and that would also allow me to do things like change the directory structure, MCI code system, etc, without having to build an upgrade path for the data files.
--- Mystic BBS v1.12 A47 2020/09/27 (Windows/64)
* Origin: Sector 7 | Mystic WHQ (1:129/215)