Open maxpiva opened 1 month ago
Responding to documentation ping.
I don't mind advanced DB options being available but I don't think we should provide detailed documentation on how to use them. I don't want average users attempting to use PostgreSQL, SQL Server or MySQL and creating another layer of troubleshooting.
@ElementalCrisis
There's admittedly little extra troubleshooting required, other than mentioning maybe another piece of software.
Once we have a single DB schema that we are dealing with other than the mix of migrations and the hopes that the ORM works as expected like we have currently, it will more be logic than it is querying data.
That's fair.
As long as it doesn't add further complications, we can expand on it in the docs.
Added PostgreSQL support. One week without issues running on PostgreSQL. Will maintain and do appropriate migration patches are needed.
TODO by the Team:
MIGRATION options ATT: @ElementalCrisis for documentation update.
Migration of an existing database to PostgreSQL it should be done on the same versions of Shoko Server. (Since different versions may have different database schemas)
The below uses MySQL as an example, but PgLoader supports migrations from SQLite and SqlServer, the below script may need to be tweaked accordingly if you use other engine.
The migration is done with PgLoader in a docker, you could also download PgLoader binaries and do it directly.
Steps 1) Update settings-server.json with the appropriate user, pass, host, and types for the PostgreSQL connection to succeed.
2) run ShokoServer with the parameter createdatabaseonly, this will start Shoko, and quit after the database is created without seeding it (Might also work also from Shoko.CLI)
3) PgLoader (https://github.com/dimitri/pgloader)
Replace [MYSQLUSER], [MYSQLPASSOWORD], [MYSQLIP], [POSTGREUSER], [POSTGREPASSWORD], [POSTGREIP] with the appropriate values, this will migrate all your data.
4) run Shoko Server as usual with your new database config.
Code tidbits:
NHibernate PostgreSQL does not support direct conversion from bool to int, which automagically happens on the other database engines, for this a BoolToIntConverter was created and added to the offending properties in the database mappings.
Without adding sequence annotations to the mapping in NHibernate, PostgreSQL sequences do not work as intended (Autoincrement, etc). Adding them will affect the mappings on other engines. So a sloppy conversion was added to the SQL generation with a driver interceptor, that renames it to the appropriate sequence. [SequenceNpgsqlDriver ->PostgreSQL.cs].
Finally Uppercase letters on tables and columns require that the tables use quotes. To skip that requirement, a LowecaseNamingStrategy was created, and incorporated, that lowercase all tables and columns.