Move to real database

[jermOSS]

As it is now, everything is stored in multiple JSON files.

This may seem ok for now but it is unsafe, slow and will eventually become even slower as amount of data increases.

Proposal

To change this I suggest picking as a community database solution. Then we can begin migration.

To preserve all data that is currently stored in the old format I suggest developing script to handle its migration.

[NoobJsPerson]

Personally I only worked with sqlite but its usually meant for small projects with little data. Maybe we could try postgresql with an ORM like sequelize to make it easier for people to write database code even if they dont know SQL.

[Naviary2]

I'm not too familiar with databases, but there's also MongoDB! When the need for a database increases we can discuss as a community what type is best, and compare them.

[Heinrich-XIAO]

SQLite is very good for small projects. Supabase is also supa good.

[optim-ally]

@Naviary2, how serious are you about supporting "truly infinite move distance"?

If you're happy with some very large but finite limit then any of these solutions will be fine.

If you want to be able to say, truthfully, that arbitrarily large move distances are supported, then SQLite won't work as it limits row sizes to SQLITE_MAX_LENGTH. That constant is configurable but still a finite upper bound. PostgreSQL is stuck at 65,535 bytes. MongoDB is better but still limits documents to 16MB. Supabase text type happily boasts no size limit, so that might be your key to infinity!

(I'm only partly serious. Unless you feel particularly strongly about this I wouldn't let it sway your choice of database solution.)

[Naviary2]

Depends on what data we are going to store. Realistically we'd only need to store numbers that are large enough that any player would never be able to zoom that far in a rated 6-hour game. If we have 65K bytes, that's around 20K digits. If you can reach 1000x farther every 1 second of zooming, that's 3 extra digits a second, so to reach 20K digits that's ~7,000 seconds of zooming or... 117 minutes which is 2 hours... hmm that's pushing it.

And that's not even asking if single-numbers can be stored in rows, or if objects with multiple numbers have to be. For example, the ICNs of completed games. Those could easily have hundreds of numbers in them.

[optim-ally]

That assumes all navigation has to been done by scrolling/zooming. I've opened #33 to challenge that!

I admit that numbers needing more than 65KB aren't realistic. It really comes down to whether you want to claim (on the website, README, news articles, whatever) that the game really does support moves of arbitrary distance, rather than acknowledging some finite bound of 2ⁿ-1 squares.

[Naviary2]

I think rated games should be done by scrolling/zooming. Others like like, or the board editor, could have teleportation. Is there any reason why a local game would ever be stored on the database?

[optim-ally]

Is there any reason why a local game would ever be stored on the database?

Probably not. People using the board editor may want to share their creations - as puzzles or just to showcase positions - but I'm sure they would understand if saving were disabled for too-large game files.

[this-is-not-available]

I don't know if it's an option, but MySQL supports up to 4,294,967,295 characters with the LONGTEXT data type.