Closed ghost closed 4 years ago
ZeldaZach
commented
4 years ago Hey @ajr-dev we're always accepting of PRs if you'd like to propose a change. This project is maintained by @omfgitsmark so he's probably the best one to weight in on how the operations take place.
I'm pretty sure we already account for prices within the SQL, but I could be wrong. Mark, any info?
ghost
commented
4 years ago I'm pretty sure we already account for prices within the SQL, but I could be wrong.
Yes I think you are storing a history of prices the problem is I can't open any of the json files so I don't know which prices are you storing, but I guess they are usd prices. I know scryfall has tix, usd, and eur. If mtgjson has those too then I can just use the sql from mtgjson and I won't need to adapt it for scryfall. Although I only need the last price and not the whole history but that's probably something I can easily fix.
ZeldaZach
commented
4 years ago We have online, paper, and European prices too. Online = CardHoarder pricing, Paper = TCGPlayer & Card Kingdom pricing, European = MKM Pricing.
We should pull together more docs on the SQL for better use cases.
FYI, You can expand the JSON with jsonlint and then it should load in sublime easily
ghost
commented
4 years ago Wow that's nice then I can just use your sql database. So I was going to use the 30 day average from MKM that scryfall uses as it's european pricing. Does mtgjson use that too? And how often is it updated? Thanks.
omfgitsmark
commented
4 years ago There's a lot to unpack here so I'll do my best.
The generate_sql_schema function loops through AllPrintings.json and builds an object specifying what columns and data types should be in the SQL tables, then uses that object to output an actual SQL query string to make the table. I know it's not the prettiest thing, but I've broken it down more than it was originally and added comments to at least give an idea of what it's doing. The problem is when I've tried to break it down even more, the amount of data I needed to pass back and forth between functions was so large it was eating resources and heavily increasing the runtime. It has to drill down so far into the json but be able to reference backwards that there is no good way to break it up (and I've tested a lot of ideas to no avail). We originally used a static table schema but every time we added/changed a property in the json it would break this script, so I created this function to automate the schema. If you just need to see what the json_data looks like it's simply AllPrintings.json and you can view what that contains on the documention website: http://www.mtgjson.com
The database only contains the current price (unless AllPrices.json is provided, more on that in a bit) no historical data, and is updated daily. Currently, the prices table has a type column which specifies whether the price is online (tix) or paper (usd). We are currently transitioning to v5 which has more robust price data, including eur prices, and I will be reworking the database to handle that as well.
The additional input stuff (like AllPrices.json) was an idea I had to make a download file that contains ALL information MTGJSON offers, but it never really came to fruition and is currently not used by our system.
If you want to get a better feel for the database and it's structure/relationships, I'd suggest downloading the compiled sqlite version (https://www.mtgjson.com/files/AllPrintings.sqlite.zip) and using a browser to look through it. (https://sqlitebrowser.org/ is my preferred viewer)
I hope this helps, I think I hit everything, but if you have any more specific questions I will do my best to answer them.
Hello thanks a lot for the time you've taken to do this script. I need to get a database with tix, usd and eur prices, so I can use this sql and add the prices from scryfall or convert the scryfall json directly without using the mtgjson file. For both I would need to know how the script works to modify it. The problem is I've understood everything except the generate_sql_schema function, because I can't open the json, it makes my computer freeze because it's too big, and I can't follow the logic of the function. Could you write down in a docstring for that function or maybe here or in the readme what's the "json data" look like and what's the sql result? (I don't know how to call the "json data", what I mean is the keys without the actual data, what would be the relations or table names in the sql database")
And if you could split that function and add more comments to it so it gets easier to follow it's logic, it would be really nice.
I've also refactored some of the code to better understand it, you probably may want to add some of the changes: https://pastebin.com/35Wqvdri I haven't tested it that's why I didn't make a pull request.