Upstream changed database format and everything is broken

[maxpoulin64]

Lots of things are broken, and I'll probably have to start over to match theirs.

So, props where due, they finally moved away from a giant JSON of everything and now ship an individual file per month. However it's still compressed, and it still extracts to the same file name.

The new report format is still very inconsistent, and some of the important keys are gone. Most notably, the ratings are gone.

For example, the first report of the December 2019 set:

  {
    "app": {
      "steam": {
        "appId": "352620"
      },
      "title": "Porcunipine"
    },
    "responses": {
      "answerToWhatGame": "352620",
      "appSelectionMethod": "libraryLookup",
      "installs": "no",
      "notes": {
        "extra": "oh noes",
        "verdict": "is le borked"
      },
      "protonVersion": "Default",
      "type": "steamPlay",
      "verdict": "no"
    },
    "timestamp": 1572299227,
    "systemInfo": {
      "cpu": "Intel Core i5-6600K @ 3.50GHz",
      "gpu": "NVIDIA GeForce GTX 980 Ti",
      "gpuDriver": "NVIDIA 396.54",
      "kernel": "4.15.0-33-generic",
      "os": "Ubuntu 18.04.1 LTS",
      "ram": "16 GB"
    }
  }

A more complete report looks like this, but still lacks rating:

{
    "app": {
      "steam": {
        "appId": 637650
      },
      "title": "FINAL FANTASY XV WINDOWS EDITION"
    },
    "responses": {
      "answerToWhatGame": "637650",
      "audioFaults": "yes",
      "customizationsUsed": {
        "customProton": true
      },
      "duration": "aboutAnHour",
      "extra": "no",
      "followUp": {
        "audioFaults": {
          "crackling": true
        },
        "performanceFaults": "slightSlowdown"
      },
      "graphicalFaults": "no",
      "inputFaults": "no",
      "installs": "yes",
      "isImpactedByAntiCheat": "no",
      "isMultiplayerImportant": "no",
      "launcher": "steam",
      "localMultiplayerAttempted": "no",
      "notes": {
        "customizationsUsed": "https://github.com/GloriousEggroll/proton-ge-custom/releases/download/4.21-GE-1/Proton-4.21-GE-1.tar.gz",
        "protonVersion": "Proton-4.21-GE-1",
        "verdict": "Other then crackling audio and having to run the game on lowish settings 60fps gameplay with occasional stuttering the demo was playable"
      },
      "onlineMultiplayerAttempted": "no",
      "opens": "yes",
      "performanceFaults": "yes",
      "protonVersion": "Proton-4.21-GE-1",
      "saveGameFaults": "no",
      "significantBugs": "no",
      "stabilityFaults": "no",
      "startsPlay": "yes",
      "type": "tinker",
      "verdict": "yes",
      "windowingFaults": "no"
    },
    "timestamp": 1575290512,
    "systemInfo": {
      "cpu": "Intel Core i5-9600K @ 3.70GHz",
      "gpu": "NVIDIA GeForce GTX 980 Ti",
      "gpuDriver": "NVIDIA 430.64",
      "kernel": "5.3.12-1-MANJARO",
      "os": "Manjaro Linux",
      "ram": "32 GB"
    }
  }

Overall not pleased with the new format. It's as annoying as hell to work with, it's inconsistent and severely lacks documentation of what keys are available and what they mean. "yes" and "no" strings instead of booleans. This screams that there weren't any design work whatsoever on the database format or export format. I hadn't seen such a bad database dump in years, and that's having worked with weird government supplied "CSV"s. They just keep adding random fields at random places. For example, in the above example, there's two customizationsUsed keys at different places with different meanings.

I'm going to need some time to think about this and might give up on normalizing that completely insane data set and just use MongoDB instead. Garbage in, garbage out. I can't make a table schema capable of fitting all of these fields without some proper documentation of what fields can exist.

At this point I don't know if upstream is just very bad, or specifically making the dumps as unusable as possible so people can't build competing websites. There's clearly minimal effort being put into this, which in my opinion is extremely disrespectful of the open source community and the people that take their time to fill in that data.

cc @DanMan

[DanMan]

Sure looks different, but not that bad IMHO. You have app, responses, timestamp and systemInfo, of which only responses (plural but not an array? …) and its kids seem a bit messy. It reflects the new review format, I suppose. He's using Firestore which isn't relational, so the "randomness" could stem from that.

But the format he's using online does have a medalRating entry under responses, so that is weird. Omission or deliberately left out? Guess it's worth asking.

I shall take a closer look at a full dump.

[gmemstr]

@maxpoulin64 Curious if this is still being worked on - happy to pick up work on this since I think a proper API for this data is important, but I want to make sure my PRs would be picked up by upstream before contributing so I can decide whether to fork or just start something fresh :)

[maxpoulin64]

Hi!

Yes, I'm still around. I'm no longer actively maintaining this unfortunately. The database format just keeps changing, and I've kind of lost the will to keep up with such garbage inputs. Especially if I still want to keep the API as-is, it's just a nightmare to remap it all. Maybe their database format stabilized a bit now?

That said, I'll happily merge contributions! I think this definitely needs an API, and it's a shame upstream ProtonDB still hasn't made an official one.

[gmemstr]

Quick question @maxpoulin64 - what version of MySQL are you using for the API? I've been unsuccessful in using the provided .sql file and want to double check before I rebuild it by hand :p

[maxpoulin64]

Probably an old version of MariaDB. I think they changed InnoDB for something else recently, if you drop the engine parts from the .sql file it should work.

I don't use any fancy feature at all and the file itself is like 50 lines long, should be trivial to hand fix.