PostgreSQL Support Discussion

[BJPickles]

Hi Suri, I hope you are doing well. This is just a polite question to ask - whether you would consider supporting Postgres as well as MySQL?

@Callepo and I have been exploring ways to migrate data from Postgres -> MySQL but we have hit a few problems.

I have another friend looking to see if this is possible: https://github.com/EnterpriseDB/mysql_fdw

So I will report back his findings, as I write this he's currently experimenting with it.

So just wondering if having Postgres officially supported in the kit would be an option?

It would mean we wouldn't have to try and transfer data from Postgres to MySQL, but keep everything in Postgres natively.

If not, do you have any recommendations for getting Postgres to copy data to MySQL?

[BJPickles]

Currently using PostgreSQL 13.15 (with schemas).

[insthync]

Schema have differences such as, in the Postgres, I've store character items as a Json, so you have to check it carefully, I recommend you to write migrate codes which write from MySQL to PostgreSQL querying into a file by yourself, because some tables have differences structure as I mentioned above.

The PostgreSQL is an option, it have somef field not being used in the kit (but being used in my project)

[BJPickles]

Okay, that's great news.

We are using a blank / fresh MMOKit DB from mysql -> PostgreSQL which has been manually entered.

We were just worried about how to get the kits code working with PostgreSQL.

If you are happy to add PostgreSQL into the main core / unitypackage, that would be amazing but not a problem if not.

The aim of the workflow for us is to not use MySQL.

[insthync]

Hmm, I actually have plan to change database server system to use Friflo.Json.Fliox, because its server system can be used with Unity (as I tested, still have to test it more), and then gradually make changes to central-server, cluster-server, map-spawn server to get rid of LiteNetLibManager usage to use Friflo.Json.Fliox instead, so it will easier to shared server-side codes with .NET project (don't build all servers with Unity)

[BJPickles]

Sounds exciting! 😁

The main reason we are trying to get PostgreSQL working is because I am using a user registration web service that uses PostgreSQL 13. (Sadly it doesn't support MySQL)

When users register on my web service, I want to copy / use their login details so it can be used with the kit.

(Which was why we were looking at PostgreSQL -> MySQL foreign data wrapper).

[BJPickles]

Just coming back to this - After testing the FDW, I can confirm it works really well and is fantastic. Very quick when both DBs are local (as is our case).

Using this test code:

-- load extension first time after install
CREATE EXTENSION mysql_fdw;

-- create server object
CREATE SERVER mariadb_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host '172.20.0.2', port '3306');

-- create user mapping
CREATE USER MAPPING FOR root
    SERVER mariadb_server
    OPTIONS (username 'root', password 'root');

-- create foreign table
CREATE FOREIGN TABLE warehouse
    (
        warehouse_id int,
        warehouse_name text,
        warehouse_created timestamp
    )
    SERVER mariadb_server
    OPTIONS (dbname 'aegis', table_name 'warehouse');

-- insert new rows in table
INSERT INTO warehouse values (1, 'UPS', current_date);
INSERT INTO warehouse values (2, 'TV', current_date);
INSERT INTO warehouse values (3, 'Table', current_date);

Unfortunately the password hashing is different between MySQL and PostgreSQL and I'm not sure how to convert between the two. So users who register their account cannot login as the account password is hashed with PostgreSQL.

So this is just to say that if it's not too much work for you, and is in-line with your goals for the kit - I would kindly like to request a PostgreSQL integration for the kit (if you're happy to do it).

I am happy to turn this into a paid request to compensate your time also, if that would help.

If you don't want to, that's also not a problem. 😄

[BJPickles]

Just found out the account management system / registration service that I use utilises the PBKDF2 algorithm to encrypt SHA-256 salted passwords, so that means all password hashes are unique (and copying over the hash to the game database / MySQL doesn't work even when we attempt to re-encrypt the password entered by the user to compare hashes).

I'm going to close this as it's no longer relevant and MySQL serves the purpose fine. In the future I'll use something that connects to this directly rather than sharing databases.