Add SQL bindings to Lua API

[madmaxoft]

There are more and more requests to have SQL bindings available to the plugins.

Although already solvable using LuaRocks (and possibly other Lua package managers), it would be much better to include the API already in Cuberite itself.

In my opinion the easiest way to do that would be to use ODBC - it is supported out of the box on Windows and is a single dependency on Linux. This would keep compilation easy, with only one additional dependency on Linux.

As for the API, the basic one should work about the same as the current SQLite API - open DB, prepare a statement, execute statement, iterate over results. However, since these DB engines may have a much higher lag than SQLite, it would be advisable to provide also asynchronous API - open DB, prepare a statement, schedule statement for execution, provide callback for the returned data. For this, Cuberite would need a scheduler thread(pool) onto which the SQL statements (and later possibly other stuff) could be scheduled.

[yangm97]

What about keeping sqlite for long term storage and adding redis for cache/in memory?

[ItsShadowCone]

Redis is complete overkill for 99% of all servers.

Maybe implement it as option if the respective server owner wants to use that.

[yangm97]

Why do you think redis would be a "overkill"? Redis is easy to work with, has great performance, scales well and has a very tiny memory footprint (only 1MB!).
Taken from Jedis wiki page on spigot:

It's great for scoreboards, stats, user accounts, and more.

Also, there's still sqlite for mundane tasks.

[sphinxc0re]

Sounds like a good idea. What other benefits would arise with the usage of Redis?

[madmaxoft]

The Redis site doesn't provide much of "integration developer" documentation - I have no idea on how to make Cuberite talk to Redis. As such, I already hate it :P

Does Redis provide ODBC driver? If so, that would be the easiest way.

[yangm97]

sphinxc0re: the server could have distributed online stats updated in real time, like a better implementation of dynmap than dynmap's own: currently, if you want to offload the webserver from the minecraft instance you end up losing the real time goodness, like chat and map updating. This could be easily fixed by having the minecraft server and the webserver hitting the same redis instance or cluster. Nginx proxying helps offload some of the functions by serving the map tiles to clients but won't do a thing about chatting, players location etc.

madmaxoft: I think you should look at the redis c++ clients...? http://redis.io/clients#c--
ODBC seem to be made for SQL-based databases only.

But hey each one has different use cases. I guess using ODBC would allow the user to switch from sqlite to mysql or any other SQL DBMS painlessly without the plugins even knowing, right? If so, this could replace the current sqlite API and let the users choose when they want to upgrade their database management software. That would be really awesome.

Unrelated not unrelated: a redis server or cluster would allow multiple cuberite instances to access the same world simultaneously. There are some MMO games making use of this already. Having redis support built in would allow for a possible scenario where high performance plugins would scale seamlessly with a cuberite server cluster.
I know this is not something the hosting-my-own-servers-for-friends-on-my-rasberry guy would need, but rather the I-sell-VIP-accounts-on-my-big-ass-server guy would want, and I think attracting these guys to cuberite could strengthen the project, please correct me if I'm wrong.

EDIT: Looking back, I think I drifted away the purpose of this issue. Sorry. I should've opened a forum thread instead.
I agree an ODBC driver should be added.

[yangm97]

I wonder if this would affect the way Cuberite handles MojangAPI, banlist, Ranks... sqlite databases.
Would it allow the server admin to switch the storage driver not only from plugins but also from core Cuberite data, like the ones mentioned above? Would this change be transparent for both, Cuberite and plugin data? If so, would Cuberite handle, let's say, a bungee cord network of Cuberite servers sharing the banlist, Ranks... databases?

[madmaxoft]

It won't be directly with this API. It's a bit separate change, but a related one indeed.

So far I've come to the conclusion that two things are needed:

• Provide Lua with access to ODBC (this issue)
• Provide a general "DataStore" API that would be abstracted away from the underlying engine and would be configurable globally for the server (separate issue, not yet created)

This way it will be possible to switch entire Cuberite to another data storage provider (including the built-in MojangAPI, banlist, ranks etc.) with a single configuration option.

[ItsShadowCone]

I would go without the odbc, because plugins at the moment can already use dependencies.... It's a bit of a hassle and may be improved but plugins should not be encouraged to make use of their own stuff while using some kind of sql through cuberite, either a complete datastore api or none.

[yangm97]

@Cl1608Ho's comment reminded me bukkit has something like this but nobody actually uses.
I do wonder: what went wrong, and how Cuberite's solution would be any different?

[sphinxc0re]

@Cl1608Ho Are you suggesting writing our own database adapter with plugin specific migrations? That would be much more work than implementing ODBC

[ItsShadowCone]

Sorry for the wrong wording.

Of course implementing ODBC, but rather not export it to lua because that would encourage plugins to use this exported function but their own database (and system) instead of letting cuberite handle the storage backend.

Cuberite should offer an api where plugins can store and get data, maybe an interface for sql and nosql storage, and cuberite handles the backend (via ODBC of course....)

[SafwatHalaby]

I thought Redis is primarily for in-memory cache? (no disk storage)

[yangm97]

@LogicParrot redis has persistency enabled by default but one may disable it. Take a look.

redis would be helpful if (and maybe only if) there was a way to split a cuberite server into multiple instances (clustering), to keep everything in sync et al. In this scenario persistence could be disabled, since data would be stored elsewhere and redis would be used to “glue" servers on runtime.

[yangm97]

Provide Lua with access to ODBC (this issue)

Of course implementing ODBC, but rather not export it to lua because that would encourage plugins to use this exported function but their own database (and system) instead of letting cuberite handle the storage backend.

So which one’s gonna be?

[yangm97]

I still think there are still some open questions about the implementation.

1. Can the admin set multiple database connections in config or only one?
   1.1. If so, do you expose every one of them to plugins, so each plugin can work differently depending on the db available (or not work at all unless you have, for instance, a postgres connection)?
   1.2. If not, do you restrict the available commands to the “least common denominator” (essentially making the db software of choice irrelevant but also making it impossible to use vendor specific functions)?
2. If a datastore API is indeed planned, would it try to work around the issues from 1.2?
3. Can’t we just add only one RDMS alongside SQLite in order to avoid complexity? Although I’m a fan of PostgreSQL I would be fine with Cuberite only supporting MySQL since this is the standard among Minecraft community.

I think this is really important so we can integrate Cuberite with existing spigot servers or other cuberite instances. For instance, one could make an authme-compatible plugin that reads and writes from the same database, make shared inventories, etc.

[SafwatHalaby]

Reddis? PostgreSQL? Overkill. Overkill. Overkill :P

In my humble opinion, keep it simple and stay with Sqlite. I think we should prioritize being on par with others in other aspects before adding bells and whistles.

[yangm97]

The lack of MySQL really kills it when you have a Bungeecord network where you want to sync permissions, login, economy and/or other player data across multiple servers (possibly mixing up Cuberite with other server software like Spigot, Glowstone…).

A simple lobby sitting there doing “nothing” uses almost no resources on Cuberite (as you would expect) but god help if you dare use Spigot.

IMO people would transition gradually to Cuberite if there are conditions to do so.

I have to agree with Redis and PostgreSQL being overkill though.

[yangm97]

Using priority labels would be a good deal perhaps?

[ItsShadowCone]

My opinion would be to implement the database with SQL (vs nosql) and let the config file decide whether to use postgres, mysql or sqlite (default) using an ODBC string.

About your questions @yangm97

1. No
   1.2 I would export methods for the most common stuff but also let the plugin specify own sql which is just passed to the c++ sql lib. I would not limit the available functionality (other than SQL only) but export a function or variable that resolved to the type of database (the stuff at the beginning of the ODBC string). So plugins can decide whether to limit functionality or not work at all.
2. I guess not 😮
3. Mysql, postgres, sqlite, doesn't really matter if you only use standard sql...

[yangm97]

@Cl1608Ho I agree mostly with what you said but:

1.2. I think this essentially blurs the line between the “datastore API” and #1395. I know the datastore API was meant to be way more complex than that (abstracting SQL completely) but I’m starting to think this is a way more viable solution (mostly due its simplicity).

So I guess there is no need to open an issue for the “datastore API”, just renaming the helper SQLite issue to just SQL.