search

otland / forgottenserver

A free and open-source MMORPG server emulator written in C++
https://otland.net
GNU General Public License v2.0
1.57k stars 1.05k forks source link

SQLite save support? #4656

Open divinity76 opened 4 months ago

divinity76 commented 4 months ago

Explanation of what you want to do that is currently impossible

IIRC many years ago (2010 maybe?) SQLite was supported, seems it no longer is?

Desired functionality

run SQLite instead of MySQL

Available workarounds

run MySQL

Prior art

ranisalt commented 4 months ago

It was removed because it added a lot of complexity supporting multiple database backends - while queries are mostly interchangeable, connection, pooling, etc is not, especially since SQLite is embedded.

However, for our use case, SQLite nowadays is an excellent choice and moving to it could be considered. It needs better tools, but performance and features is on par or even better than MySQL/MariaDB/PgSQL, and it's much easier to write tests for since you can create a database in-memory on the fly

nekiro commented 4 months ago

If we only used ORM or at least query builder abstracting database support

gesior commented 3 months ago

SQLite nowadays is an excellent choice

IDK what they changed in SQLite database 'idea' since 2010, but isn't it like '1 file on HDD to store all data with SQL access'? File is 100% managed by database driver of your app. It is perfect for apps running on localhost (ex. accounting apps for small business), but when 2 apps try to access it in same time (ex. OTS + www or OTS + database manager tool) it goes crazy. Starting any transaction locks file on HDD and then no other app can write to it. IDK how could you deliver ACID ( https://www.sqlitetutorial.net/sqlite-transaction/ ) without locking file, so probably SQLite is the same as it was in 2010. I remember SQLite times, when you edited something in database manager (start transaction) and it made OTS not able to save players anymore - until you pressed 'commit' button in database manager.

Moving MySQL integration and all SQL queries to some interface - like ORM - would be great. Someone interested in storing data in SQLite could add SQLite driver. Someone (me) interested in storing players in MongoDB or other not relational database could adapt it to work with custom storage system. Some idiot would be even able to store data in .xml files on HDD like OTSes did in 2005.

If we only used ORM or at least query builder abstracting database support

ORMs and query builders are great.. and then you try to make SQL query from Lua :( Of course my idea of 'interface' would also break all Lua SQL compatibility, as all requests to load/store something would go thru C++ interface.. but it can be somehow handled by custom Lua data handler, which would work

ranisalt commented 3 months ago

@gesior you're right about that, SQLite is not suitable for multiple clients. Unless we make the server also act as a database server, with some public APIs, it is not feasible to pair with an AAC.