Database API for mods

[ghost]

Summary:

1. When using minetest as a server, many files are at risk from dataloss and corruption, including: env_meta, player profiles, and all mods that store data in text files... (the map.sqlite is safe from dataloss, BTW).
2. Since datbases are safe from this particular problem, I would like to have a database API added to minetest LUA so mod developers (including myself) can start storing their data safely.

Details:
Understanding this issue requires a very particular particular knowledge in hardware, OS design, and programming, but I suspect some of the minetest developers should be familiar with the problem.

1. What files have this corruption problem?
   Basically, any file that minetest writes to while it is running. This includes player info, env_meta, as well as all data stored by mods. The map.sqlite is the only file without this problem because sqlite is specifically designed to prevent such dataloss.
2. How big of a deal is this?

I, personally, did not consider this to be a big issue until it happened multiple times on a machine we rent from a hosting company. Something happened a few times on the machine or VPS... and each time it happened, some of the files were corrupted, like env_meta and some of the player files.

Everytime a person complains on the forums about env_meta errors, this is another example of someone having dataloss due to a crash.

Someone once told me that proller (a former minetest dev) had it happen on his server where he lost his stargate locations data (however, I don't know if this is true).

Basically, the chances of it happening are rare, but it does happen (even on what should be a very stable server at a reliable hosting company). Sometimes the data is not even recoverable from backups (because the data would be too far out of date). Any competent person who programs for servers would tell you that you should never store important data in a text file due to this ... it's just too risky. However, until such dataloss has happened to you personally, sometimes it's hard to understand how important this really is.

What is going on?

• Servers crash sometimes... even the best designed *nix servers at a hosting company can crash.
• When a crash happens, and a file is being written to, the file become corrupted because it was in the middle of writing the data.
• databases, like sqlite, can recover from this particular issue using a rollback journal that fixes the data after the system recovers/restarts from the crash: https://www.sqlite.org/tempfiles.html

The solution:

The solution is pretty easy... store data in a database or some other system specifically designed to protect against dataloss like this.

---

What I am proposing

1. A VERY simple solution would be to add a database API to minetest that can be accessed in LUA. You would not even have to make your own... just include one of the pre-made database APIs that already exist for LUA. Then any mod developer can use it to store their data safely.
   Examples:
   Possible options:
   For leveldb:
   https://github.com/marcopompili/lua-leveldb -- leveldb is a key/value store which might be a lot easier for programmers to understand than something like sqlite
   For sqlite:
   http:https://lua.sqlite.org/index.cgi/home -- this one is well documented, so you could just refer people to that documentation.
   For reddis:
   https://github.com/nrk/redis-lua
2. Over time, the minetest developers could migrate minetest's features to use this database API instead of text files.

Personal note:
For years, I didn't think it was a big deal storing data in text files. After all, crashes on a server should be so rare it shouldn't matter. However, after it happened to me several times on a server at a proper hosting company and after seeing it happen to others, I don't think I can trust storing my mod data in text files anymore. I am hoping minetest can include a database API, otherwise I may have to waste time writing my own database system for minetest. :( Including one of the pre-made APIs would take very little time to implement, then mod developers could migrate to the new system at their own choosing over time.

Sidenote: I may be unable to reply back to this post if you have any follow up questions, so I will just leave it to the minetest devs to discuss it.

[est31]

minetest has a tradition of doing our own rewrite instead of taping together some unfitting third party "finished" solutions. Most times they don't fit our standard at second look, or don't fit our needs. For example, I would want to have a consistent API over all database types. Existing "finished" lua solutions can't provide this. Also, we have mod security. So they need to have some way to disable opening arbitrary files, which I guess they lack.

[est31]

Also there is the thing that I guess these APIs can't "serve two masters", meaning that we would have to have separate database files for the map and all the other files, or use those APIs for the database access. This is only a minor issue though.

[PilzAdam]

IIRC we have a "safe write to file" function somewhere in the engine utils. We could expose this to the Lua API.

[kahrl]

The corrupted env_meta.txt (etc.) files might be caused by #3084.

[RobertZenz]

A VERY simple solution would be to add a database API to minetest that can be accessed in LUA.

Wouldn't a simple key/value store in which you can put Lua tables be enough?

Something like this:

local data = {
    something = true,
    other = "value",
    nested = {
        value = "more"
    }
}

minetest.store("my key", data)

local loaded_data = minetest.retrieve("my key")

The mods don't need to know anything about the used database backend, and all you need in the database is another table with two columns and a primary key.

[0-afflatus]

That would be ideal @RobertZenz

[ShadowNinja]

I don't think the corruption argument is good, as there are ways to save files safely without needing a database (after all, the database has to do it somehow).
That said, I think this would be useful for large data sets (such as some data attached to each player) where you don't want to fill a directory for it.
Here's my idea of what an API could look like:

-- Argument is a database name in the modname:item format that's used
-- to generate a path/database and table for the mod.  For example:
--  For SQLite3: `"foomod:foobar"` table in a database at `world/mod_db.sqlite`.
--  For LevelDB: keys prefixed with `"foomod:foobar:"` in `world/mod_db.leveldb/`.
local store = KeyValueStore("foomod:foobar")
store.set("a", "A")
assert(store.get("a") == "A")
-- Additional methods to iterate over all keys/values should also be added.

Features like multiple fields and being able to query on arbitrary fields would make this much more useful, but then it would basically just work with SQLite3, and you might as well just give the mod full access to lua51-sqlite3.

[RealBadAngel]

http:https://wiki.mudlet.org/w/Manual:Lua_Functions#Database_Functions
others can do that properly :P
Cant we just merge that code?

[red-001]

@kahrl #3084 was fixed with 64c060e

[celeron55]

Is the only point of this issue the corruption of files? I think that's silly...

Well anyway, I made this commit finally today: 64c060e. I completely forgot that issue and now that I remembered and saw that #3084 had no comments, I went and made the fix.

Users should now keep an eye on whether the corruption continues to happen with versions that contain this fix and report somewhere whether or not that is the case.

[RobertZenz]

For me, having the possibility to easily persist data and data structures in a key/value store would be quite nice. That would allow to centralize how mods are persisting their data.

[Warr1024]

Some other potential benefits from unifying data storage into one backend:

• Some back-ends provide sane concurrency, such as sqlite (whole-db locking is cumbersome, but ostensibly atomic and consistent) or redis. This might provide a way to create consistent backups of the entire game state without having to shutdown the server, or even suspend disk writes. Database backends may also provide some snapshot capability to do this with minimal performance impact on the running game server.
• It might be possible to store the game state in a single file, in some cases (e.g. sqlite), which might make games easier to share, backup, etc.
• Understanding and applying the safe-writing semantics appropriate for a platform can be complex, and it might be nice to offload that instead of reinventing, and then having to support, the wheel.

Also, it should be feasible to store anything that used to go in files into a single key/value store; not even separate tables are strictly necessary. Use the filename (or a hash, url-encoding, or other sanitization scheme thereof) as the keys, and they can coexist without collision with mapblocks.

[neoascetic]

+1 on this, but I don't want to store my mods data using something low-level as selecting in which DB store the data, etc. All I want is fast persistent storage with simple API (key/value, as lua table or, worse, JSON is good enough), and I don't want to write the same load-from-file/write-to-file code from mod to mod.

[SmallJoker]

Just pointing out that there's still a demand for a database API. (Also as a self-reminder)
I know, we now have player and mod storage, but that's just JSON-like serialized and searching entries requires its own function. Having an API similar to lsqlite3, but for all supported database backends, would be great.
Not all data must be kept in memory there and search queries can executed easily.

local my_db = minetest.open_database(path, "sqlite3/leveldb/redis/postgresql")
my_db:exec("DELETE FROM playerstats WHERE password = 'hunter2'") -- probably not possible like this in leveldb
my_db:close()

[nerzhul]

i close this issue, we have meta on many objects to handle this now.

[rubenwardy]

Meta is inappropriate for lists of structured data. To store lists, you need to serialise the contents. This results in the exact same problem as before meta was added - difficulty in searching, performance, and memory use

[nerzhul]

ModMetadata is not sufficient for you ? it's the purpose of this object

[rubenwardy]

Duplicate of #10371 (yes, I know it's newer)

[rubenwardy]

Nevermind

[farooqkz]

Of note:
We in CTF need a relational database to store some data. It would be great if we could access to all features of sqlite. And modstorage is already using it for data storage. Otherwise, we would need to install a Lua sqlite library and use it from the mod which makes things awkward.

Furthermore, we are already using Redis for rankings storage and we use a library to access it and add the mod to trusted ones.