Feature: SQLite support

ReynoldsCahoon commented 7 months ago

Describe your feature request or suggestion in detail

SQLite is an alternative database solution that removes the need for a mysql service to run alongside the server. This can improve development workflows, decrease repack dependencies, and increase accessibility to a wider audience.

The CMaNGOS emulator recently implemented this and even includes a sqlite database with their databases releases.

Describe a possible solution to your feature or suggestion in detail

As mentioned, CMaNGOS has already implemented this. There will be some differences between the emulators, but it should act as a decent guide.

Relevant PRs

Additional context

Due to AzerothCore's modules and the popularity of its repacks, particularly those that aim at a single player or limited player experience, this removes an extra service that the user is required to run. Character and World databases become a single file. Easily backed up or transferred between servers. This can help developers who want to quickly swap between a stock database and a modified one (swap out the world.db file).

smellbee commented 7 months ago

please don’t do this，no need ， this will introduce new troubles， especially related to auto sql update，etc for a whole year，lot‘s of critical bugs still remains， new bugs arises alongside trival features，this project is already too heave to be maintainable

Elmsroth commented 7 months ago

@ReynoldsCahoon I think there are lots of things to fix before considering this feature. CMangos cannot be compared to AC because in CMangos most of the content and mechanics are working great. The expansion is pretty old ans the emulator is quite stable. There are more content to fix in Wotlk expansion since you add TBC+ Wotlk regions !

heyitsbench commented 7 months ago

There are more content to fix in Wotlk expansion since you add TBC+ Wotlk regions !

CMaNGOS also has a WotLK branch with SQLite support, as mentioned in the issue description.

Elmsroth commented 7 months ago

@heyitsbench Thanks for pointing it out. I have mainly worked on Vanilla and TBC for several years in MaNGOS teams (but no CMangos) and I thought their Wotlk content was not so active at the time event MaNGOS Wotlk content was not very active so we redirected players to AzerothCore or TrinityCore hwen they asked for Wotlk content. Nevertheless they probably have making common DB mathods in order to avoid too much codebase change for SQLite support. I would personally be very cautious with performance when relying on SQLite.

ReynoldsCahoon commented 7 months ago

It's probably not a good idea to confuse mangos and cmangos, as they're wholly separate projects.

Regardless, this is a unique and useful feature. CMaNGOS also offers Postgres support. The point is, CMaNGOS has taken steps to make their server database agnostic, meaning you can use whichever database engine you prefer.

Here are some reasons to consider SQLite:

Local development

No database service needs to be running, no users need to be configured, no permissions need to be granted. Simply point to the relevant database files and start the server process. I have a server configured at this moment and my database lines in the server config look like this

LoginDatabaseInfo     = "db/realm.db"
WorldDatabaseInfo     = "db/world.db"
CharacterDatabaseInfo = "db/character.db"
LogsDatabaseInfo      = "db/logs.db"

SQLite even distributes sqldiff, which is a tool that can compare and export the differences between two SQLite database files to SQL.

Portability

Currently, to transport a server to another location requires dumping a database and migrating it to another database service. With SQLite you simply transfer the .db file and point to it.

This functionality also benefits repacks, which currently rely on providing preconfigured database services to users. The perfect candidate for this implementation are Single Player Project repacks, or repacks specifically targeting a small number of players, as the database can be bundled in the repack and no extra services are required on the users end.

Extendability

Implementing SQLite will require making the core more database agnostic, which paves the way for other alternative database engines like PostgreSQL. CMaNGOS has already implemented this as well, but I'm not making that request here, simply pointing out that there are benefits beyond SQLite support to making the changes required to implement it.

sudlud commented 7 months ago

I would also appreciate this feature.

azerothcore / azerothcore-wotlk