Dantevg/WebStats

MySQL Compatible

MonzterDev opened this issue · 7 comments

MonzterDev commented

Hey, would you ever consider making this MySQL compatible? I love the idea here, but I don't use the Scoreboard and store all my Data in MySQL.

Dantevg commented

Hi, hello! Thanks for opening an issue here!
It sounds like an interesting idea. I think in principle the only thing that would be needed is a PHP script that puts the content of the MySQL database into JSON (and some small fixes). I'm not that experienced in PHP, but I can give it a try (or do you know PHP yourself maybe?)
I'm not sure how you have your database layed out, would you mind sharing how you store the objectives and entries?

MonzterDev commented

Sorry for the delayed response and thank you for your response!
I'm not familiar with any language besides slight Java, as I recently began learning. Although I do wish I could help.

Now considering how each Database can be stored completely differently, I think it might be very challenging to allow for easy configuration.
image
image
I attached an example of how one of the Plugins I use, store data.

If there is anything else I can do to assist, please let me know!

Dantevg commented

No worries!

Thank you for the screenshots, they help me understand it a bit better. It does indeed look to be a bit more challenging than I at first thought, because you can have multiple databases, each with a different layout, and they all have to combine into one table. (adding to that the fact that users are stored by UUID, not playername)

After having thought a bit, I think it can be done with a semi user-configurable setup with a JSON config file, so here is my idea. The following example is for 2 databases with 1 table each, the first one being in the format of your screenshot, the second one would be for when you just have the columns player, exp and money in the database:

[
    {
        "database": "db1",
        "table": "table1",
        "key": "uuid",
        "columns": ["value"],
        "convert": ["uuid", "json"],
        "renameColumns": { "uuid": "player" }
    },
    {
        "database": "db2",
        "table": "table2",
        "key": "player",
        "columns": ["exp", "money"]
    }
]

There would then be a couple of pre-defined mapping functions (each with the same signature) that convert an entire data row: (it might be better/easier to convert only a single item at a time instead of an entire row, but not sure)

  • uuid(row, config) would convert player UUIDs in the keys to usernames
  • json(row, config) would expand a JSON object in the values to multiple columns.

The PHP script would need to combine multiple rows, tables and databases into a single data object.
Example stages of data (using JSON terminology / display) for the first config item in the example above (corresponding to the layout of your screenshot):

  1. Array of objects, directly from database: 
    [
    {"uuid": "...da", "info_id": 1, "value": "{\"itemMap\": {...}}"},
    {"uuid": "...da", "info_id": 2, "value": "{\"exp\": 0.0, ...}"},
    {"uuid": "...0f", "info_id": 1, "value": "{\"itemMap\": {...}}"}
]
  2. After conversion function 1: uuid 
    [
    {"uuid": "Player1", "info_id": 1, "value": "{\"itemMap\": {...}}"},
    {"uuid": "Player1", "info_id": 2, "value": "{\"exp\": 0.0, \"offlineExp\": 0.0}"},
    {"uuid": "Player2", "info_id": 1, "value": "{\"itemMap\": {...}}"}
]
  3. After conversion function 2: json 
    [
    {"uuid": "Player1", "itemMap": {...}},
    {"uuid": "Player1", "exp": 0.0, "offlineExp": 0.0},
    {"uuid": "Player2", "itemMap": {...}}
]
  4. After column renaming 
    [
    {"player": "Player1", "itemMap": {...}},
    {"player": "Player1", "exp": 0.0, "offlineExp": 0.0},
    {"player": "Player2", "itemMap": {...}}
]
  5. (combine data from multiple config items (multiple SQL tables/databases) at this point)
  6. After combining rows with the same keys 
    [
    {"player": "Player1", "itemMap": {...}, "exp": 0.0, "offlineExp": 0.0},
    {"player": "Player2", "itemMap": {...}}
]
  7. After transposing (array of key+columns to object of columns of key+value) 
    {
    "itemMap": {
        "...da": "[], [], ...",
        "...0f": "[], [], ..."
    },
    "exp": {
        "...da": 0.0
    }
}
  8. Final output 
    {
    "entries": ["...da", "...0f"],
    "scores": {
        "itemMap": ...,
        "exp": ...
    }
}

For your screenshot, the above configuration should produce the following table. I hope this is about how that data should be displayed? I'm not sure what itemMap is storing and how to display it though:

Player itemMap exp offlineExp money offlineMoney ...
Player1 (...da) [], [], [], [], ... 0.0 0.0 0.0 0.0 ...
Player2 (...0f) [], [], [], [], ...

Do you think this would be flexible enough to use for the other plugins you use?
It is quite a challenge which requires some thinking, I'll see what I can do! 😄

Edit: a large part of this is notes to myself, thought I'd include it for completeness

MonzterDev commented 
.}}
]

Wow, after reading this, I'm am appalled! You went insane on planning this out! My previous example with itemMap was a bad example because that stores ItemStacks, which would be ugly to display on a Website.

The configuration certainly seems very flexible and could work with a variety of setups.

I've also included another Databse setup, to provide further usage possibilities:

image
image

Dantevg commented

Well, it has been some time (busy with exams, went on holiday, had other stuff to do, etc.) but I did something! The configuration format is even more powerful now (almost created an entire programming language 😄), although I think I missed some things still (like the uuid converter function, coming soon!). I hope it works for at least some of your databases/tables. I tested with these database configurations:

table1 is like your first screenshot (what it looks like in mysql)

uuid value
uu1 {"exp": 0.0}
uu1 {"money": 42}
uu2 {"exp": 1.0}

table2 is just plain

player exp money
p1 0.0 42
p2 1.0

table3 is like your last screenshot

playerID category count
uu1 mobs.ReaperMinion 1
uu2 mobs.Bees 1

This would be a possible configuration file for it:

[ // array of configuration items, each for one database-table combination
    {
        "database": "db1",
        "table": "table1",
        "convert": [ // array of conversion operations, performed in-order
            ["json", "value"], // json command takes a column argument; that column will be converted from json
            ["uuid", "uuid"], // uuid command also takes a column argument
            ["rename", "uuid", "player"] // rename command takes two column names
            // ("uuid" column will be renamed to "player" here)
        ]
    },
    {
        "database": "db1",
        "table": "table2"
        // convert array is optional
    },
    {
        "database": "db1",
        "table": "table3",
        "convert": [
            ["key-value", "category", "count"], // key-value unpack takes two columns,
            // the value of the first column is the key and will become a column,
            // the value of the second column will be its value
            ["rename", "playerID", "player"] // rename "playerID" column to "player"
        ]
    }
]

In order to merge the different tables into one, every table needs to end up with a "player" column.

Installation should be as easy as uploading the php files and pointing everything to each other. I added a configuration option to the JS part so you can manually set up the URLs like this: (this replaces the ip and port config options)

    new WebStats({
        connection: new Connection({
            scores: "path/to/stats.php",
            online: "http://ip:port/online.json"
        })
        // ... rest of config
    })

Also, the database.php file needs the username and password of course.

I hope this is of use to you. If you have trouble with anything, I would be very glad to help! I will write more extensive documentation in the readme soon, as well as work on one or two more converter functions - please let me know if you have other ideas!

  • ❤️1
MonzterDev commented

Woah, thank you so much for all your unbelievable hard work! The format is extremely flexible, and all the effort you put into this is greatly appreciated!

Dantevg commented

I realised last week that I could just as well have done it in Java, but somehow my brain immediately goes PHP when someone mentions mysql. So, I rewrote it in Java 😁 (and implemented the uuid converter), which has a couple advantages:

  • no need for a separate installation, everything is present in the main plugin
  • also no need for a separate config file
  • no extra language used -> easier maintenance
  • less requests to the Mojang API for UUID to playername conversion (can use the server's usercache.json)

I also wrote a small explanation for the conversions in the readme.
Closing this now, but if you have any questions, let me know!